-- első kollokvium - MINTA - 2014-11-16

create database Kereskedőház;
go

use Kereskedőház;
go
-- Adatbázis és tábláinak létrehozása

create table ARU (
   ARU_AZON             int                  not null	primary key,
   ARUNEV               char(25)             not null,
   ARU_LEIRAS           varchar(500)         null
)
go
create table DOLGOZO (
   DOLGOZO_AZONOSITO    int                  not null	primary key,
   JMBG                 numeric(13)          not null,
   VEZETEKNEV           char(15)             not null,
   KERESZTNEV           char(15)             not null,
   SZULETETT            datetime             not null,
   MIOTA_DOLGOZONK      datetime             not null,
   LAKCIM_VAROS         char(15)             null,
   LAKCIM_UTCA_HSZ      char(15)             null
)
go
create table SZAMLA (
   SZLA_SZAM            int                  not null	primary key,
   DOLGOZO_AZONOSITO    int                  not null	foreign key references DOLGOZO (DOLGOZO_AZONOSITO),
   SZLA_KELTE           datetime             not null,
   SZLA_VEGOSSZEGE      money                not null,
   FIZETESI_HATARIDO    datetime             not null,
   KAPCSOLATTARTO       char(30)             null,
   TELEFON              char(20)             null
)
go
create table SZAMLA_TETEL (
   SZLA_SZAM            int                  not null	foreign key references SZAMLA (SZLA_SZAM),
   SZLA_TETEL_SZAM      int                  not null,
   ARU_AZON             int                  not null	foreign key references ARU (ARU_AZON),
   MENNYISEG            decimal(12,2)        not null,
   EGYSEG               char(15)             not null,
   EGYSEGAR             decimal(12,2)        not null,
   constraint PK_SZAMLA_TETEL primary key nonclustered (SZLA_SZAM, SZLA_TETEL_SZAM)
)
go
-- Adatbázis és tábláinak létrehozása

-- 2./
alter table aru
	add aru_hasznalhatosagi_datuma date default getdate() check (aru_hasznalhatosagi_datuma>getdate());
	go

-- 3./	
alter table szamla
	alter column kapcsolattarto varchar(25) not null;
go

-- 4./
alter table szamla_tetel
add constraint uc_egysegar_kontroll
	check (egysegar>=10);
go

-- 5./
insert DOLGOZO
	values (1,1411955820030,'Imre', 'Petkovics','1955.11.14', '1993.09.01', 'Subotica', 'Rumska 17');
go

insert DOLGOZO
	values (3,1411955820030,'Lővi', 'József','1963.03.05', '2009.01.10', 'Subotica', 'Rumska 17');
go

insert DOLGOZO
	values (2,1411955820030,'Kövér', 'Hümér','1985.10.01', '2011.10.01', 'Subotica', 'Rumska 17');
go

select * from DOLGOZO;
go

update DOLGOZO
	set VEZETEKNEV='Petkovics',
		keresztnev='Imre';
		go
		
use Kereskedőház;
go

insert ARU
	values (1,'Gyömbér','Erősítőszer');
go

insert ARU
	values (2,'Cimet','Fűszer');
go

insert ARU
	values (3,'Tej','Élelmiszer');
go

insert ARU
	values (4,'Kenyér','Élelmiszer');
go

insert ARU
	values (5,'Joghurt','Élelmiszer');
go

insert ARU
	values (6,'Mr. Proper','Tisztítószer');
go

insert ARU
	values (7,'Ajax','Tisztítószer');
go

insert ARU
	values (8,'Brise','Légfrissítő');
go

insert ARU
	values (9,'Ambi Pur','Tisztítószer');
go

insert ARU
	values (10,'Air Wick','Tisztítószer', '2015.02.13');
go

update ARU
	set aru_hasznalhatosagi_datuma='2015.02.13'
	where ARU_LEIRAS='Tisztítószer';
go

update ARU
	set aru_hasznalhatosagi_datuma='2015.01.17'
	where ARU_LEIRAS='Élelmiszer';
go

update ARU
	set aru_hasznalhatosagi_datuma='2015.08.15'
	where aru_hasznalhatosagi_datuma is null;
go

select * from ARU;
go

insert SZAMLA
	values(1,1,'2014.12.09',237.54,'2014.12.24','Petkovics Györgyi','+381656557498');
go

insert SZAMLA
	values(4,2,'2014.12.27',600.54,'2015.01.14','Petkovics Györgyi','+381656557498');
go

insert SZAMLA
	values(5,2,'2015.01.02',1500.69,'2015.01.17','Petkovics Györgyi','+381656557498');
go

insert SZAMLA
	values(6,3,'2015.01.03',900.70,'2015.01.18','Petkovics Györgyi','+381656557498');
go

select * from SZAMLA;
go

insert SZAMLA_TETEL
	values(1,1,1,1,'Kg',890.00);
go

insert SZAMLA_TETEL
	values(4,1,2,10,'dkg',89.00);
go

insert SZAMLA_TETEL
	values(4,2,8,1,'db',389.00);
go

insert SZAMLA_TETEL
	values(5,2,3,1,'lit.',98.00);
go

select * from SZAMLA_TETEL;
go

-- 6./
alter table szamla_tetel
	nocheck constraint uc_egysegar_kontroll;
go

update SZAMLA_TETEL
	set EGYSEGAR=5;
go

alter table szamla_tetel
	check constraint uc_egysegar_kontroll;
go


update SZAMLA_TETEL
	set EGYSEGAR=3;
go

--7./
delete SZAMLA_TETEL
	where EGYSEGAR<10;
go

select * from SZAMLA_TETEL;
go

-- 8./
alter table szamla_tetel
drop constraint uc_egysegar_kontroll;
go

-- 9./
create view dolgozonezet as
	(select vezeteknev, keresztnev, jmbg, dolgozo_azonosito, lakcim_varos
		from DOLGOZO);
go

select * from dolgozonezet;
go

/*************************************************************************************/
/* Második kollokvium feladatainak megoldása */

/* Második kollokvium feladatainak megoldása */

/* Második kollokvium feladatainak megoldása */
/*************************************************************************************/


use Kereskedőház;
go

insert DOLGOZO
	values (1,1411955820030,'Imre', 'Petkovics','1955.11.14', '1993.09.01', 'Subotica', 'Rumska 17');
go
select * from DOLGOZO;
go

insert SZAMLA
	values(1,1,'2014.12.09',237.54,'2014.12.24','Petkovics Györgyi','+381656557498');
go
insert SZAMLA
	values(2,1,'2014.12.13',237.54,'2014.12.28','Petkovics Györgyi','+381656557498');
go
insert SZAMLA
	values(3,1,'2014.12.21',237.54,'2015.01.05','Petkovics Györgyi','+381656557498');
go
select * from SZAMLA;
go

-- 1./
/*
1.	Készítsen listát a dolgozók teljesítményéről:
	 Mennyi az egyes dolgozók (dolgozoazonosito) által decemberben kiállított és
	 decemberben fizetendő (fizetési határidő) számlák végösszege (a végösszegek összege)?
	 Rendezze az eredménytáblát a  teljesítmény (a számlák végösszegének összege) szerint! */
	 
select CAST(DATEPART(mm,Szla_kelte) AS char(12)), DATEPART(mm,Szla_kelte)
	from SZAMLA;
go

select DOLGOZO_AZONOSITO, sum(SZLA_VEGOSSZEGE) as 'Számlák végösszegének összege'
	from szamla
	where DATEPART(mm,Szla_kelte)= 12 and DATEPART(mm,Szla_kelte)= DATEPART(mm,Fizetesi_hatarido)
	group by DOLGOZO_AZONOSITO
	order by sum(SZLA_VEGOSSZEGE) desc;
go


-- 2./
/*
2.	Listázza azokat a 2014/ben kiállított számlákat (Szla_szám), amelyeknél ismert a Telefon (szám),
	de ismeretlen a Kapcsolattartó (személye).  */
	
select szla_szam, KAPCSOLATTARTO, TELEFON, SZLA_KELTE
	from SZAMLA
	where TELEFON is not null and KAPCSOLATTARTO is null and DATEPART(yy, szla_kelte)= 2014;
go


-- 3./ MANAGEMENTESEKNEK
/*
3.	Kik azok a dolgozók (Vezetéknév, Keresztnév), akik még nem állítottak ki számlát?  */

select vezeteknev, keresztnev
	from dolgozo
	where DOLGOZO_AZONOSITO not in (select DOLGOZO_AZONOSITO
										from SZAMLA);
go


-- 3./ INFORMATIKUSOKNAK
/*
4.	Városonként hány olyan dolgozó szerepel a nyilvántartásban (adatbázisban), 
	akik még nem állítottak ki számlát? Csak azokat a sorokat kell megtartani, amelyekben
	a számlát nem kiállított dolgozók száma nagyobb, mint 1. Az eredménytáblát a 
	dolgozók össz száma szerint fordított sorrendben kell rendezni!  */
	
select lakcim_varos, COUNT(dolgozo_azonosito)
	from dolgozo
	where DOLGOZO_AZONOSITO not in (select DOLGOZO_AZONOSITO
										from SZAMLA)
	group by LAKCIM_VAROS
	having COUNT(dolgozo_azonosito) > 1
	order by COUNT(dolgozo_azonosito) desc;
go

-- 4./
/*
5.	Milyen nevű árucikkeket (ismétlődés nélkül) számlázott Kövér Hümér (Vezetéknév, Keresztnév) nevű dolgozó?
	A listát ABC sorrendbe rendezni az Árunév szerint! */

select distinct(arunev)
	from ARU a
	join SZAMLA_TETEL sz_t on sz_t.ARU_AZON = a.ARU_AZON
	join SZAMLA sz on sz.SZLA_SZAM = sz_t.SZLA_SZAM
	join dolgozonezet d on d.DOLGOZO_AZONOSITO = sz.DOLGOZO_AZONOSITO
	where d.VEZETEKNEV = 'Kövér' and d.KERESZTNEV = 'Hümér'
	order by a.ARUNEV;
go


select distinct(arunev)
	from ARU
	where ARU_AZON in (select ARU_AZON
						from SZAMLA_TETEL
						where SZLA_SZAM in (select SZLA_SZAM
												from SZAMLA
												where DOLGOZO_AZONOSITO in (select DOLGOZO_AZONOSITO
																				from dolgozo
																				where dolgozo.VEZETEKNEV='Kövér' and
																					  dolgozo.KERESZTNEV='Hümér')));
go

-- 6./
/*
6.	Listázza ki, hogy ki (dolgozó: Vezetéknév, Keresztnév), melyik számla (szla_szam),
	melyik tételén milyen nevű árucikkeket számlázott!  Az eredménytáblát rendezze a vezetéknév,
	a keresztnév, a számlaszám és a számlatétel száma szerint! */
	
select d.vezeteknev, d.keresztnev, sz.SZLA_SZAM, sz_t.SZLA_TETEL_SZAM, arunev
	from ARU a
	join SZAMLA_TETEL sz_t on sz_t.ARU_AZON = a.ARU_AZON
	join SZAMLA sz on sz.SZLA_SZAM = sz_t.SZLA_SZAM
	join dolgozonezet d on d.DOLGOZO_AZONOSITO = sz.DOLGOZO_AZONOSITO
	order by d.VEZETEKNEV, d.KERESZTNEV, sz.SZLA_SZAM, sz_t.SZLA_TETEL_SZAM;
go