/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     10/12/2015 7:21:31 PM                        */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('RENDELES') and o.name = 'FK_RENDELES_RENDEL_VEVO')
alter table RENDELES
   drop constraint FK_RENDELES_RENDEL_VEVO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('RENDELES_TETEL') and o.name = 'FK_RENDELES_ARU_SZERE_ARU_ARLI')
alter table RENDELES_TETEL
   drop constraint FK_RENDELES_ARU_SZERE_ARU_ARLI
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('RENDELES_TETEL') and o.name = 'FK_RENDELES_RENDELESH_RENDELES')
alter table RENDELES_TETEL
   drop constraint FK_RENDELES_RENDELESH_RENDELES
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ARU_ARLISTA')
            and   type = 'U')
   drop table ARU_ARLISTA
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('RENDELES')
            and   name  = 'RENDEL_FK'
            and   indid > 0
            and   indid < 255)
   drop index RENDELES.RENDEL_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RENDELES')
            and   type = 'U')
   drop table RENDELES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('RENDELES_TETEL')
            and   name  = 'ARU_SZEREPEL_A_TETELBEN_FK'
            and   indid > 0
            and   indid < 255)
   drop index RENDELES_TETEL.ARU_SZEREPEL_A_TETELBEN_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('RENDELES_TETEL')
            and   name  = 'RENDELESHEZ_TARTOZIK_FK'
            and   indid > 0
            and   indid < 255)
   drop index RENDELES_TETEL.RENDELESHEZ_TARTOZIK_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RENDELES_TETEL')
            and   type = 'U')
   drop table RENDELES_TETEL
go

if exists (select 1
            from  sysobjects
           where  id = object_id('VEVO')
            and   type = 'U')
   drop table VEVO
go

/*==============================================================*/
/* Table: ARU_ARLISTA                                           */
/*==============================================================*/
create table ARU_ARLISTA (
   ARU_KOD              smallint             not null,
   MERTEKEGYSEG         char(10)             not null,
   ARU_MEGNEVEZES       varchar(20)          null,
   EGYSEGAR             numeric(15,2)        null,
   constraint PK_ARU_ARLISTA primary key nonclustered (ARU_KOD, MERTEKEGYSEG)
)
go

/*==============================================================*/
/* Table: RENDELES                                              */
/*==============================================================*/
create table RENDELES (
   RENDELES_SZ          smallint             not null,
   VEVO_KOD             smallint             not null,
   KELT_DATUM_          datetime             null,
   constraint PK_RENDELES primary key nonclustered (RENDELES_SZ)
)
go

/*==============================================================*/
/* Index: RENDEL_FK                                             */
/*==============================================================*/
create index RENDEL_FK on RENDELES (
VEVO_KOD ASC
)
go

/*==============================================================*/
/* Table: RENDELES_TETEL                                        */
/*==============================================================*/
create table RENDELES_TETEL (
   ARU_KOD              smallint             not null,
   MERTEKEGYSEG         char(10)             not null,
   RENDELES_SZ          smallint             not null,
   RENDELT_MENNY        numeric(12,2)        null,
   constraint PK_RENDELES_TETEL primary key nonclustered (ARU_KOD, MERTEKEGYSEG, RENDELES_SZ)
)
go

/*==============================================================*/
/* Index: RENDELESHEZ_TARTOZIK_FK                               */
/*==============================================================*/
create index RENDELESHEZ_TARTOZIK_FK on RENDELES_TETEL (
RENDELES_SZ ASC
)
go

/*==============================================================*/
/* Index: ARU_SZEREPEL_A_TETELBEN_FK                            */
/*==============================================================*/
create index ARU_SZEREPEL_A_TETELBEN_FK on RENDELES_TETEL (
ARU_KOD ASC,
MERTEKEGYSEG ASC
)
go

/*==============================================================*/
/* Table: VEVO                                                  */
/*==============================================================*/
create table VEVO (
   VEVO_KOD             smallint             not null,
   VEVO_NEV             varchar(30)          null,
   IR_SZAM              numeric(5)           not null default 24000
      constraint CKC_IR_SZAM_VEVO check (IR_SZAM between 20000 and 29999),
   HELYSEG              varchar(20)          null,
   UTCA_HSZ             varchar(20)          null,
   constraint PK_VEVO primary key nonclustered (VEVO_KOD)
)
go

alter table RENDELES
   add constraint FK_RENDELES_RENDEL_VEVO foreign key (VEVO_KOD)
      references VEVO (VEVO_KOD)
go

alter table RENDELES_TETEL
   add constraint FK_RENDELES_ARU_SZERE_ARU_ARLI foreign key (ARU_KOD, MERTEKEGYSEG)
      references ARU_ARLISTA (ARU_KOD, MERTEKEGYSEG)
go

alter table RENDELES_TETEL
   add constraint FK_RENDELES_RENDELESH_RENDELES foreign key (RENDELES_SZ)
      references RENDELES (RENDELES_SZ)
go

/* II - koll- MINTA -2016-01-13  */
/* II - koll- MINTA -2016-01-13  */
/* II - koll- MINTA -2016-01-13  */


use Arurendeles;
go


/*

1. Nev szerint a vevoink egyenkent milyen ertekben rendeltek
	a 2014-es ev december honapjaban? Rendezze az eredménytáblát
	ertekek szerint csokkeno sorrendbe!*/
	
select vevo_nev, sum(rendelt_mennyiseg*egysegar) as 'Osszrendeles'
	from dbo.vevo v
	join dbo.Rendeles r on r.vevo_kod=v.vevo_kod
	join dbo.Rendeles_tetel rt on rt.rendeles_sz=r.rendeles_sz
	join dbo.aru_arlista a on a.aru_kod=rt.aru_kod
	where YEAR(kelt_datum)=2014 and MONTH(kelt_datum)=12
	group by vevo_nev
	order by sum(rendelt_mennyiseg*egysegar) desc;
go


/*

2-1. Listazza azokat a vevoket, akiknel ismert az iranyitoszam, de ismeretlen 
	az utca_hsz mezo erteke */
	
select vevo_nev
	from dbo.Vevo
	where ir_szam is not null and utca_hsz is null;
go	


/*

2-2. Listazza azokat a 2014/ben rendeleseket kuldo vevoket, akiknel
	ismert az iranyitoszam, de ismeretlen az utca_hsz mezo erteke */
	
select vevo_nev
	from dbo.Vevo v
	join dbo.Rendeles r on r.vevo_kod=v.vevo_kod
	where YEAR(kelt_datum)=2014 and ir_szam is not null and utca_hsz is null;
go

/*

3. Kik azok a vevok akik eddig meg egyszer sem rendeltek? - MANAGEMENTESEKNEK */

select vevo_nev
	from dbo.Vevo v
	left join dbo.Rendeles r on r.vevo_kod=v.vevo_kod
	where kelt_datum is null;
go

select vevo_nev
	from dbo.Vevo
	where vevo_kod not in (select vevo_kod
								from Rendeles);
go


/*
  
 4. Helysegenkent hany olyan vevonk van, amelyik eddig meg egyszer sem rendelt
	tolunk? Csak azokat a sorokat kell megtartani, amelyekben a vevok szama
	nagyobb egynel. Az eredménytáblát a vevok össz száma szerint fordított
	sorrendbe kell rendezni! - INFORMATIKUSOKNAK  */
	
select helyseg, COUNT(vevo_kod)
	from dbo.Vevo
	where vevo_kod not in (select vevo_kod
							from dbo.Rendeles)
	group by helyseg
	having COUNT(vevo_kod)>1
	order by COUNT(vevo_kod) desc;
go

select helyseg, count(v.vevo_kod)
	from dbo.Vevo v
	left join dbo.Rendeles r on r.vevo_kod=v.vevo_kod
	where kelt_datum is null
	group by helyseg
	having COUNT(v.vevo_kod)>1
	order by COUNT(v.vevo_kod) desc;
go

/*
5. Milyen nevő árucikkeket (aru_megnevezes - ismétlodés nélkül) 
	rendelt Kövér Hümér(Vevonev) nevu vevo? A listát ABC sorrendbe rendezni
	az Árunév szerint!
*/

select distinct(aru_megnevezes)
from dbo.Aru_arlista
where aru_kod in (select aru_kod
				 from dbo.Rendeles_tetel
				 where rendeles_sz in (select rendeles_sz
									   from dbo.Rendeles
									   where vevo_kod in (select vevo_kod
														  from dbo.Vevo
														  where vevo_nev='Kover Humer')))
order by aru_megnevezes;
go

--

select distinct(aru_megnevezes)
	from dbo.vevo v
	join dbo.Rendeles r on r.vevo_kod=v.vevo_kod
	join dbo.Rendeles_tetel rt on rt.rendeles_sz=r.rendeles_sz
	join dbo.aru_arlista a on a.aru_kod=rt.aru_kod
	where vevo_nev='Kover Humer'
	order by aru_megnevezes;
go


/*

6.	Listázza ki, hogy melyik vevo(vevo_nev), melyik rendeles(rendeles_sz),
	melyik tételén (aru_kod,mertekegyseg) milyen nevű árucikkeket
	(aru_megnevezes)számlázott! Az eredménytáblát rendezze a vevo(vevo_nev),
	a rendeles(rendeles_sz) és a rendelestétel (aru_kod,mertekegyseg)
	szerint! 
*/

select vevo_nev, r.rendeles_sz, rt.aru_kod, rt.mertekegyseg,aru_megnevezes
	from dbo.vevo v
	join dbo.Rendeles r on r.vevo_kod=v.vevo_kod
	join dbo.Rendeles_tetel rt on rt.rendeles_sz=r.rendeles_sz
	join dbo.aru_arlista a on a.aru_kod=rt.aru_kod
	order by vevo_nev, r.rendeles_sz, rt.aru_kod, rt.mertekegyseg;
go