/*
SELECT-ek a SzinhPortal adatbazisban 
1. ora - 7. het - 2019.11.14.
*/
use SzinhPortal;
go

/*
Listazzuk a SZINDARAB tabla osszes  adatat (sorat)
*/
select *
	from dbo.SZINDARAB

/*
Az SQLSERVER mint kalkulator
*/
select 2*sin(2+2)


/*
Listazzuk a SZINDARAB tabla szindarab cimeit a felvonasszamaikkal egyutt
*/
select SZINDARAB_CIME, SZI_FELVONAS_SZAMA
	from dbo.SZINDARAB

/*
Listazzuk a SZINDARAB tabla szindarab cimeit a felvonasszamaikkal egyutt.
Adjunk sajat neveket az eredmenytabla oszlopainak
*/
select SZINDARAB_CIME as "Darab cime", 
		SZI_FELVONAS_SZAMA as "Felvonasainak szama"
	from dbo.SZINDARAB


/*
Listazzuk a SZINDARAB tabla szindarab cimeit a felvonasszamaikkal egyutt.
Adjunk sajat neveket az eredmenytabla oszlopainak. 
Matematikai kifejezes a SELECT zaradekban
*/	
select SZINDARAB_CIME as "Darab cime", 
		SZI_FELVONAS_SZAMA as "Felvonasainak szama",
		2*SZI_FELVONAS_SZAMA as "Dupla felvonasszam"
	from dbo.SZINDARAB

/*
Listazzuk a SZINDARAB tabla szindarab cimeit a felvonasszamaikkal egyutt.
Adjunk sajat neveket az eredmenytabla oszlopainak. 
Fuggveny a SELECT zaradekban
*/		
select SZINDARAB_CIME as "Darab cime", 
		SZI_FELVONAS_SZAMA as "Felvonasainak szama",
		log(SZI_FELVONAS_SZAMA) as "Felvonasszam logaritmus erteke"
	from dbo.SZINDARAB

/*
Listazzuk a SZINDARAB tabla szindarab cimeit a felvonasszamaikkal egyutt.
Adjunk sajat neveket az eredmenytabla oszlopainak. 
Fuggveny SELECT zaradekban. 
Tetszoleges tartalom/szoveg az eredmenytabla soraiban 
*/	
select SZINDARAB_CIME as "Darab cime", 
		SZI_FELVONAS_SZAMA as "Felvonasainak szama",
		'annak logaritmusa',
		log(SZI_FELVONAS_SZAMA) as "Felvonasszam logaritmus erteke"
	from dbo.SZINDARAB

/*
Listazzuk a SZEMELY tabla sorait, amelyekben az eletrajz kitoltetlen
*/	
select * 
	from dbo.SZEMELY
	where SZE_ELETRAJZ is null

/*
Hozzunk letre kitoltetlen eletrajzokat adatmodositassal
*/
update dbo.SZEMELY
	set SZE_ELETRAJZ=null
	where SZE_ELETRAJZ='???'

/*
Szamlaljuk meg hany adat van az eletrajz oszlopban
*/
select COUNT(SZE_ELETRAJZ)
	from dbo.SZEMELY

/*
Szamlaljuk meg hany sor van a SZEMELY tablaban
*/
select COUNT(SZEMELY_AZ)
	from dbo.SZEMELY

/*
Szamlaljuk meg hany adat van az aktiv oszlopban
*/	
select COUNT(SZE_AKTIV)
	from dbo.SZEMELY

/*
Szamlaljuk meg hany kulonbozo adat van az aktiv oszlopban
*/
select COUNT(distinct SZE_AKTIV)
	from dbo.SZEMELY

select COUNT(distinct(SZE_AKTIV))
	from dbo.SZEMELY
	
select * 
	from dbo.HELYISEG

/*
Aggregacios fuggvenyek a SELECT zaradekban
*/
select COUNT(*) as "Helyisegek szama", 
		MIN(H_IRANYITOSZAM),MAX(H_IRANYITOSZAM),
		MIN(HELYISEGNEV), MAX(HELYISEGNEV),
		AVG(H_IRANYITOSZAM), SUM(ORSZAG_AZ)
	from dbo.HELYISEG

/*
Aggregacios fuggvenyek a SELECT zaradekban
Ha oszlopnev is szerepel a SELECT zaradekban, az mar 
Group by zaradekot igenyel
*/
select HELYISEGNEV, COUNT(*) as "Helyisegek szama", 
		MIN(H_IRANYITOSZAM),MAX(H_IRANYITOSZAM),
		MIN(HELYISEGNEV), MAX(HELYISEGNEV),
		AVG(H_IRANYITOSZAM), SUM(ORSZAG_AZ)
	from dbo.HELYISEG

/*
Aggregacios fuggvenyek a SELECT zaradekban
az egyszeru fuggveny viszont nem
*/
select power(5,2) as "Ot a negyzeten", COUNT(*), 
		MIN(H_IRANYITOSZAM),MAX(H_IRANYITOSZAM),
		MIN(HELYISEGNEV), MAX(HELYISEGNEV),
		AVG(H_IRANYITOSZAM), SUM(ORSZAG_AZ)
	from dbo.HELYISEG

/*
Listazzuk a helysegneveket es azt, hogy hany betu hosszuak (LEN fuggveny)
*/
select HELYISEGNEV, 'karakterszama', LEN(HELYISEGNEV)
	from dbo.HELYISEG

/*
Listazzuk a helysegneveket es azt, hogy hany betu hosszuak (LEN fuggveny),
valamint azt is, hogy milyen betuvel kezdodik az adott helysegnev 
(LEFT fuggveny)
*/	
select HELYISEGNEV, 'karakterszama', LEN(HELYISEGNEV),
		'elso betuje', LEFT(HELYISEGNEV,1)
	from dbo.HELYISEG




/*
SELECT-ek a SzinhPortal adatbazisban 
2. ora - 8. het - 2019.11.21.
*/

/*
WHERE zaradek
*/

select * from dbo.ELOADAS_PROJEKT
/*
Listazzuk a 140 percnel hosszabb eloadascimeket
*/

select ELOADAS_CIME, EL_HOSSZA_PERC
	from dbo.ELOADAS_PROJEKT
	where EL_HOSSZA_PERC>140;

/*
Listazzuk a 140 percnel hosszabb eloadascimeket, amelyeknek
2014-ben volt a bemutatoja
*/

select ELOADAS_CIME, EL_HOSSZA_PERC, YEAR(EL_BEMUTATO_DATUM)
	from dbo.ELOADAS_PROJEKT
	where EL_HOSSZA_PERC>140 and
		YEAR(EL_BEMUTATO_DATUM)=2014;

select * from dbo.ELOADAS_PROJEKT

/*
Listazzuk a 139 percnel hosszabb eloadascimeket, amelyeknek
decemberben volt a bemutatoja
*/

select ELOADAS_CIME, EL_HOSSZA_PERC, month(EL_BEMUTATO_DATUM)
	from dbo.ELOADAS_PROJEKT
	where EL_HOSSZA_PERC>=140 and
		month(EL_BEMUTATO_DATUM)=12;
		
/*
Listazzuk a 140 percnel hosszabb eloadascimeket, amelyeknek
a honap harmadik napjan volt a bemutatoja
*/

select ELOADAS_CIME, EL_HOSSZA_PERC, day(EL_BEMUTATO_DATUM)
	from dbo.ELOADAS_PROJEKT
	where EL_HOSSZA_PERC>=140 and
		day(EL_BEMUTATO_DATUM)=3;
		
/*
Listazzuk a 140 percnel hosszabb eloadascimeket, amelyeknek
a felvonasszama nagyobb mint 1
*/

select ELOADAS_CIME, EL_HOSSZA_PERC, EL_FELVONAS_SZAMA
	from dbo.ELOADAS_PROJEKT
	where EL_FELVONAS_SZAMA>1 and
		EL_HOSSZA_PERC>140;
	
/*
???Listazzuk a 140 percnel hosszabb eloadascimeket, amelyeknek
a felvonasszama nagyobb mint 1 es ot evvel ezelotti evben volt a bemutatojuk
*/

select ELOADAS_CIME, EL_HOSSZA_PERC, EL_FELVONAS_SZAMA, getdate()-(5*360)
	from dbo.ELOADAS_PROJEKT
	where EL_FELVONAS_SZAMA>1 and
		EL_HOSSZA_PERC>140 and
		EL_BEMUTATO_DATUM=getdate()-(5*360);

select getdate()-(5*365)

select * from dbo.SZINHAZ

/*
Listazzuk a budapesti es ujvideki szinhazakat
*/

select SZINHAZNEV, SZ_H_CIM_HELYSEG
	from dbo.SZINHAZ
	where SZ_H_CIM_HELYSEG='Budapest' or
	SZ_H_CIM_HELYSEG='Újvidék';
	
select SZINHAZNEV, SZ_H_CIM_HELYSEG
	from dbo.SZINHAZ
	where SZ_H_CIM_HELYSEG in ('Budapest','Újvidék');
	
select SZINHAZNEV, SZ_H_CIM_HELYSEG
	from dbo.SZINHAZ
	where SZ_H_CIM_HELYSEG between 'Budapest' and 'Újvidék';
	

/*
Listazzuk a budapesti es ujvideki szinhazakat
*/

select SZINHAZNEV, SZ_H_CIM_HELYSEG
	from dbo.SZINHAZ
	where SZ_H_CIM_HELYSEG in ('Budapest','Újvidék');


select SZINHAZNEV, SZ_H_CIM_HELYSEG
	from dbo.SZINHAZ
	where HELYISEG_AZ in (select HELYISEG_AZ
							from dbo.HELYISEG
							where HELYISEGNEV in ('Budapest','Újvidék'));
							
/* 
Listazzuk azokat a szindarabokat, amelyeknek nincs forditojuk
*/

select SZINDARAB_CIME
	from SZINDARAB
	where SZEMELY_AZ_FORD is null;
	
select * from SZINDARAB

select * from SZINHAZ


/*
SELECT-ek a SzinhPortal adatbazisban 
3. ora - 9. het - 2019.11.28.
*/


-- GROUP BY zaradek

/*
Szamlaljuk meg a szindarabok szamat, es azt is melyek vannak meg a 
repertoaron
*/

select * from ELOADAS_PROJEKT

select COUNT(SZINDARAB_AZ), COUNT (*), COUNT(REPERTOARON)
	from ELOADAS_PROJEKT;
go

select COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A kitoltott repertoaron mezo szama'
	from ELOADAS_PROJEKT;
go

select COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	where REPERTOARON='true';
	
select COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron kivuli szindarabok szama'
	from ELOADAS_PROJEKT
	where REPERTOARON=0;
go

select COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	group by REPERTOARON;
	
select REPERTOARON, COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	group by REPERTOARON;

select * from ELOADAS_PROJEKT

/*
Listazzuk a szindarabok szamat, a sorok szamat es a kitoltott REPERTOARON
mezok szamat a repertoaron levo es a repertoarrol lekerult eloadas_projektekre. 
*/

--1 nem fog menni hiba nelkul, mert az el_hossza_perc nincs a GROUP BY-ban
select el_hossza_perc, REPERTOARON, COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	group by REPERTOARON;

--2 nem fog menni hiba nelkul, mert az el_hossza_perc nincs a GROUP BY-ban	
select el_hossza_perc, COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	group by REPERTOARON;

--3	
select COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	group by REPERTOARON;

--4	
select el_hossza_perc, COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	group by el_hossza_perc;

--5 ez nem megy, mert a YEAR(EL_BEMUTATO_DATUM) nem aggregacios fuggveny
--  es akkor szerepelnie kell a GROUP BY-ban
select YEAR(EL_BEMUTATO_DATUM), COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	group by el_hossza_perc;

/*
Listazzuk a bemutato evet, az eloadashosszakat percekben, szindarabok szamat, 
a sorok szamat es a kitoltott REPERTOARON mezok szamat bemutato ev(ek)re
es eloadashosszokra csoportositva. 
*/

select  YEAR(EL_BEMUTATO_DATUM) as 'Bemutato eve',
		el_hossza_perc, 
		COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	group by YEAR(EL_BEMUTATO_DATUM), el_hossza_perc;

/*
Listazzuk a bemutato evet, az eloadashosszakat percekben, szindarabok szamat, 
a sorok szamat es a kitoltott REPERTOARON mezok szamat bemutato ev(ek)re
es eloadashosszokra csoportositva. Ne jelenjenek meg a 2014-ben bemutatott
eloadasokrol szolo osszegzesek.
*/

--1
select  YEAR(EL_BEMUTATO_DATUM) as 'Bemutato eve',
		el_hossza_perc, 
		COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	where YEAR(EL_BEMUTATO_DATUM)<>2014
	group by YEAR(EL_BEMUTATO_DATUM), el_hossza_perc;

--2	
select  YEAR(EL_BEMUTATO_DATUM) as 'Bemutato eve',
		el_hossza_perc, 
		COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	group by YEAR(EL_BEMUTATO_DATUM), el_hossza_perc
	having YEAR(EL_BEMUTATO_DATUM)<>2014;

--3 ez nem megy - WHERE-ben COUNT...
select  YEAR(EL_BEMUTATO_DATUM) as 'Bemutato eve',
		el_hossza_perc, 
		COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	where COUNT(SZINDARAB_AZ)>0
	group by YEAR(EL_BEMUTATO_DATUM), el_hossza_perc
	having YEAR(EL_BEMUTATO_DATUM)<>2014;

--4 WHERE-ben hulyeseg elmegy (de tessek vigyazni vele-ures eredmenytabla!!!)
select  YEAR(EL_BEMUTATO_DATUM) as 'Bemutato eve',
		el_hossza_perc, 
		COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	where 1>2
	group by YEAR(EL_BEMUTATO_DATUM), el_hossza_perc
	having YEAR(EL_BEMUTATO_DATUM)<>2014;	

--5
select  YEAR(EL_BEMUTATO_DATUM) as 'Bemutato eve',
		el_hossza_perc, 
		COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	where EL_TARTALOM is not null
	group by YEAR(EL_BEMUTATO_DATUM), el_hossza_perc
	having YEAR(EL_BEMUTATO_DATUM)<>2014;

/*	
-- ORDER BY zaradek
*/

/*
Listazzuk az ELOADAS_PROJEKT tabla tartalmat. Rendezzuk a sorokat 
az eloadascimek szerint ABC sorrendbe
*/

select * 
	from ELOADAS_PROJEKT
	order by  ELOADAS_CIME;
go

/*
Listazzuk az ELOADAS_PROJEKT tabla tartalmat. Rendezzuk a sorokat 
az eloadascimek, majd azon belul a felvonasszamok szerint ABC sorrendbe
*/

select * 
	from ELOADAS_PROJEKT
	order by  ELOADAS_CIME, EL_FELVONAS_SZAMA;
go

/*
Listazzuk az ELOADAS_PROJEKT tabla tartalmat. Rendezzuk a sorokat 
a felvonasszamok, majd azon belul az eloadascimek szerint ABC sorrendbe
*/

select * 
	from ELOADAS_PROJEKT
	order by  EL_FELVONAS_SZAMA, ELOADAS_CIME;
go

/*
Listazzuk az ELOADAS_PROJEKT tabla tartalmat. Rendezzuk a sorokat 
a felvonasszamok szerint csokkeno sorrendbe, majd azon belul 
az eloadascimek szerint ABC sorrendbe
*/

select * 
	from ELOADAS_PROJEKT
	order by  EL_FELVONAS_SZAMA desc, ELOADAS_CIME;
go

/*
Listazzuk az ELOADAS_PROJEKT tabla tartalmat. Rendezzuk a sorokat 
a felvonasszamok szerint csokkeno sorrendbe, majd azon belul 
az eloadascimek szerint forditott ABC sorrendbe
*/

select * 
	from ELOADAS_PROJEKT
	order by  EL_FELVONAS_SZAMA desc, ELOADAS_CIME desc;
go


/*
Listazzuk a bemutato evet, az eloadashosszakat percekben, szindarabok szamat, 
a sorok szamat es a kitoltott REPERTOARON mezok szamat bemutato ev(ek)re
es eloadashosszokra csoportositva. Csak azokat a sorokat kell bekapcsolni
a szamlalasba, amelyekben a tartalomleiras kitoltott (nem NULL ereteku).
Ne jelenjenek meg a 2014-ben bemutatott eloadasokrol szolo osszegzesek.
Rendezzuk az eredmenytablat a bemutato-evek szerint novekvo sorrendbe!
*/

select  YEAR(EL_BEMUTATO_DATUM) as 'Bemutato eve',
		el_hossza_perc, 
		COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	where EL_TARTALOM is not null
	group by YEAR(EL_BEMUTATO_DATUM), el_hossza_perc
	having YEAR(EL_BEMUTATO_DATUM)<>2014
	order by YEAR(EL_BEMUTATO_DATUM);

/*
Listazzuk a bemutato evet, az eloadashosszakat percekben, szindarabok szamat, 
a sorok szamat es a kitoltott REPERTOARON mezok szamat bemutato ev(ek)re
es eloadashosszokra csoportositva. Csak azokat a sorokat kell bekapcsolni
a szamlalasba, amelyekben a tartalomleiras kitoltott (nem NULL ereteku).
Ne jelenjenek meg a 2014-ben bemutatott eloadasokrol szolo osszegzesek.
Rendezzuk az eredmenytablat a bemutato-evek szerint csokkeno sorrendbe!
*/

select  YEAR(EL_BEMUTATO_DATUM) as 'Bemutato eve',
		el_hossza_perc, 
		COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	where EL_TARTALOM is not null
	group by YEAR(EL_BEMUTATO_DATUM), el_hossza_perc
	having YEAR(EL_BEMUTATO_DATUM)<>2014
	order by YEAR(EL_BEMUTATO_DATUM) desc;

/*
Listazzuk a bemutato evet, az eloadashosszakat percekben, szindarabok szamat, 
a sorok szamat es a kitoltott REPERTOARON mezok szamat bemutato ev(ek)re
es eloadashosszokra csoportositva. Csak azokat a sorokat kell bekapcsolni
a szamlalasba, amelyekben a tartalomleiras kitoltott (nem NULL ereteku).
Csak az 1914-es evtol kesobbi evekben bemutatott eloadasokrol szolo osszegzesek
jelenjenek meg az eredmenytablaban. Rendezzuk az eredmenytablat a bemutato-evek 
szerint novekvo sorrendbe!
*/
	
select  YEAR(EL_BEMUTATO_DATUM) as 'Bemutato eve',
		MONTH(EL_BEMUTATO_DATUM), 
		COUNT(SZINDARAB_AZ) as 'Szindarabok szama', 
		COUNT (*) as ' Sorok szama', 
		COUNT(REPERTOARON) as 'A repertoaron levo szindarabok szama'
	from ELOADAS_PROJEKT
	where EL_TARTALOM is not null
	group by YEAR(EL_BEMUTATO_DATUM), MONTH(EL_BEMUTATO_DATUM)
	having YEAR(EL_BEMUTATO_DATUM)>1914
	order by month(EL_BEMUTATO_DATUM) ;

select * from ELOADAS_PROJEKT

/*
Szamlaljuk meg, hogy felvonasszamonkent hany szindarab van a 
nyilvantartasban (eloadas-projekt)
*/

select EL_FELVONAS_SZAMA, COUNT(SZINDARAB_AZ) as 'Szindarabok szama'
	from ELOADAS_PROJEKT
	group by EL_FELVONAS_SZAMA;
go

/*
Szamlaljuk meg HELYISEG_AZ-okat, adjuk ossze a helyisegazonositokat
szamoljuk ki az atlagos helysegazonositot-gyalog es 
szamittassuk ki az atlag helyiseg_az-t orszagazonositonkent
a helyiseg tablabol. Csak azok a sorok maradhatnak az eredmenytablaban
amelyekben az orszagazonosito nagyobb, mint 3
*/

--1
select ORSZAG_AZ as 'Orszagazonosito',
		sum(HELYISEG_AZ) 'A helysegazonositok osszege',
		sum(HELYISEG_AZ)/COUNT(HELYISEG_AZ) as 'Gyalogos atlag',
		AVG(HELYISEG_AZ) as 'Gepi atlag'
	from HELYISEG
	group by ORSZAG_AZ
	having ORSZAG_AZ>3;

--2	
select ORSZAG_AZ as 'Orszagazonosito',
		sum(HELYISEG_AZ) 'A helysegazonositok osszege',
		sum(HELYISEG_AZ)/COUNT(HELYISEG_AZ) as 'Gyalogos atlag',
		AVG(HELYISEG_AZ) as 'Gepi atlag'
	from HELYISEG
	where ORSZAG_AZ>3
	group by ORSZAG_AZ;


/*
SELECT-ek a SzinhPortal adatbazisban 
4. ora - 10. het - 2019.12.05.
*/

/*
TOBBTABLAS LEKERDEZESEK
*/

/*
Listazzuk ki a mufajokat es a hozza tartozo szindarabokat 
*/

select MUFAJ_AZON, SZINDARAB_AZ,SZINDARAB_CIME
	from SZINDARAB
	
/*
Listazzuk ki nev szerint a mufajokat es cim szerint a hozzajuk tartozo 
szindarabokat 
*/

--I
select MUFAJ_NEVE, SZINDARAB_CIME
	from SZINDARAB, MUFAJ
	where MUFAJ.MUFAJ_AZON=SZINDARAB.MUFAJ_AZON;
go

--II
select MUFAJ_NEVE, SZINDARAB_CIME
	from SZINDARAB sz, MUFAJ m
	where m.MUFAJ_AZON=sz.MUFAJ_AZON;
go

--III
select MUFAJ_NEVE, SZINDARAB_CIME
	from SZINDARAB
	join MUFAJ on MUFAJ.MUFAJ_AZON=SZINDARAB.MUFAJ_AZON;
go

--II
select MUFAJ_NEVE, SZINDARAB_CIME
	from SZINDARAB sz
	join MUFAJ m on m.MUFAJ_AZON=sz.MUFAJ_AZON;
go

/*
Listazzuk nev szerint a mufajokat, a hozzajuk tartozo szindarabokat
cim szerint es a szindarabok forditoit vezeteknev es keresznevvel.
*/

select MUFAJ_NEVE, SZINDARAB_CIME, SZE_VEZETEKNEV, SZE_NEV
	from MUFAJ m
	join SZINDARAB szi on szi.MUFAJ_AZON=m.MUFAJ_AZON
	join SZEMELY sze on sze.SZEMELY_AZ=szi.SZEMELY_AZ_FORD;
go

/*
Listazzuk nev szerint a mufajokat, a hozzajuk tartozo szindarabokat
cim szerint es a szindarabok szerzoit vezeteknev es keresznevvel.
*/

select MUFAJ_NEVE, SZINDARAB_CIME, SZE_VEZETEKNEV, SZE_NEV
	from MUFAJ m
	join SZINDARAB szi on szi.MUFAJ_AZON=m.MUFAJ_AZON
	join SZERZO sz on sz.SZINDARAB_AZ=szi.SZINDARAB_AZ
	join SZEMELY sze on sze.SZEMELY_AZ=sz.SZEMELY_AZ;
go

select * from SZINDARAB;

update SZINDARAB
	set SZEMELY_AZ_FORD=null
	where SZINDARAB_CIME= 'A patkányok'; 
	
/*
Listazzuk a szemelyeket nev szerint, es azokat a szindarabokat, ameleket
ezek a szemelyek forditottak.
*/

select SZE_VEZETEKNEV, SZE_NEV, SZINDARAB_CIME
	from dbo.SZEMELY sze
	join SZINDARAB szi on szi.SZEMELY_AZ_FORD=sze.SZEMELY_AZ;
go

/*
Listazzuk a szemelyeket nev szerint, szindarabokat cim szerint. Ha a szemely 
forditott szindarabot, akkor irja oda a szindarab nevet,
ha nem forditott egyetlen szindarabot sem, akkor a szindarabnev helyere
NULL-t irjon ki.
*/

--I
select SZE_VEZETEKNEV, SZE_NEV, SZINDARAB_CIME
	from dbo.SZEMELY sze
	left join SZINDARAB szi on szi.SZEMELY_AZ_FORD=sze.SZEMELY_AZ;
go


--II
select SZE_VEZETEKNEV, SZE_NEV, SZINDARAB_CIME
	from SZINDARAB szi
	right join dbo.SZEMELY sze on szi.SZEMELY_AZ_FORD=sze.SZEMELY_AZ;
go

/*
Listazzuk a szemelyeket nev szerint, akik nem forditottak szindarabot.
*/

--I
select SZE_VEZETEKNEV, SZE_NEV, SZINDARAB_CIME
	from dbo.SZEMELY sze
	left join SZINDARAB szi on szi.SZEMELY_AZ_FORD=sze.SZEMELY_AZ
	where SZI_FELVONAS_SZAMA is null;
go

--II
select distinct SZE_VEZETEKNEV, SZE_NEV, SZINDARAB_CIME
	from SZINDARAB szi 
	right join dbo.SZEMELY sze on szi.SZEMELY_AZ_FORD=sze.SZEMELY_AZ
	where SZI_FELVONAS_SZAMA is null;
go

/*
Listazzuk a szemelyeket nev szerint, akik nem forditottak szindarabot.
Rendezzuk az eredmenytabla sorait a keresztnev szerint ABC sorrendbe.
*/

--I
select distinct SZE_VEZETEKNEV, SZE_NEV, SZINDARAB_CIME
	from dbo.SZEMELY sze
	left join SZINDARAB szi on szi.SZEMELY_AZ_FORD=sze.SZEMELY_AZ
	where SZI_FELVONAS_SZAMA is null
	order by SZE_NEV;
go


/*
Listazzuk a szindarabokat cim szerint es a szindarabok szerzoit 
vezeteknev es keresznevvel. A szemelyek, akik nem szerzok azoknal
a szindarabcimek null erteket vegyenek fel
*/

select SZINDARAB_CIME, SZE_VEZETEKNEV, SZE_NEV
	from SZINDARAB szi
	join SZERZO sz on sz.SZINDARAB_AZ=szi.SZINDARAB_AZ
	right join SZEMELY sze on sze.SZEMELY_AZ=sz.SZEMELY_AZ;
go

/*
Listazzuk a szindarabokat cim szerint es a szindarabok szerzoit 
vezeteknev es keresznevvel. A szindarabok, amelyeknek nincsenek szerzoik
a null erteket vegyen fel
*/

select SZINDARAB_CIME, SZE_VEZETEKNEV, SZE_NEV
	from SZINDARAB szi
	join SZERZO sz on sz.SZINDARAB_AZ=szi.SZINDARAB_AZ
	left join SZEMELY sze on sze.SZEMELY_AZ=sz.SZEMELY_AZ;
go

select * from TEREM
select * from ELOADAS_A_JATEKRENDBEN



/*
SELECT-ek a SzinhPortal adatbazisban 
5. ora - 11. het - 2019.12.12.
*/

/*
Listazzuk az orszagneveket, helyisegneveket es a bennuk mukodo
szinhazak szamait. Csak azok a sorok maradjanak az ET, amelyekben
a szinhazak szama nagyobb 1-nel (0-nal). Rendezzuk az ET-t a szinhazszamok szerint 
novekvo/csokkeno sorrendbe.
*/

select ORSZAGNEV, HELYISEGNEV, COUNT(SZINHAZ_AZ) as "Szinhazak szama"
	from ORSZAG o
	join HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
	join SZINHAZ sz on sz.HELYISEG_AZ=h.HELYISEG_AZ
	group by ORSZAGNEV, HELYISEGNEV
	having COUNT(SZINHAZ_AZ)>0
	order by COUNT(SZINHAZ_AZ);
go


/*
Listazzuk az orszagneveket, helyisegneveket es a bennuk mukodo
szinhazak szamait, a Szerbian kivuli szinhazakat figyelembe veve. Csak azok a sorok maradjanak az ET, amelyekben
a szinhazak szama nagyobb 1-nel (0-nal). Rendezzuk az ET-t a szinhazszamok szerint 
novekvo/csokkeno sorrendbe.
*/
--I
select ORSZAGNEV, HELYISEGNEV, COUNT(SZINHAZ_AZ) as "Szinhazak szama"
	from ORSZAG o
	join HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
	join SZINHAZ sz on sz.HELYISEG_AZ=h.HELYISEG_AZ
	where ORSZAGNEV<>'Szerbia'
	group by ORSZAGNEV, HELYISEGNEV
	having COUNT(SZINHAZ_AZ)>0
	order by COUNT(SZINHAZ_AZ);
go

--II
select ORSZAGNEV, HELYISEGNEV, COUNT(SZINHAZ_AZ) as "Szinhazak szama"
	from ORSZAG o
	join HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
	join SZINHAZ sz on sz.HELYISEG_AZ=h.HELYISEG_AZ
	group by ORSZAGNEV, HELYISEGNEV
	having ORSZAGNEV<>'Szerbia' and COUNT(SZINHAZ_AZ)>0
	order by COUNT(SZINHAZ_AZ);
go

--II - helytelen-HIBAS
select ORSZAGNEV, HELYISEGNEV, COUNT(SZINHAZ_AZ) as "Szinhazak szama"
	from ORSZAG o
	join HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
	join SZINHAZ sz on sz.HELYISEG_AZ=h.HELYISEG_AZ
	where COUNT(SZINHAZ_AZ)>0
	group by ORSZAGNEV, HELYISEGNEV
	having ORSZAGNEV<>'Szerbia'
	order by COUNT(SZINHAZ_AZ);
go

select * from HELYISEG
select * from dbo.SZEMELY

/*
Listazzuk a budapesti es belgradi szemelyeket, akik aktivak es amatorok
, es az eletrajzuk nem hosszabb, mint 7 jel.
*/
--I
select SZE_VEZETEKNEV, SZE_NEV, HELYISEGNEV
	from dbo.SZEMELY sz
	join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
	where (HELYISEGNEV='Budapest' or HELYISEGNEV='Beograd') and
		SZE_AKTIV=1 and SZE_AMATOR=0 
			and LEN(SZE_ELETRAJZ)<=7;
go

select SZE_VEZETEKNEV, SZE_NEV, HELYISEGNEV
	from dbo.SZEMELY sz
	join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
	where (HELYISEGNEV='Budapest' or HELYISEGNEV='Beograd') and
		SZE_AKTIV='false' and SZE_AMATOR=0 
			and (LEN(SZE_ELETRAJZ)<=100 or SZE_ELETRAJZ is null);
go


--II
select SZE_VEZETEKNEV, SZE_NEV
	from dbo.SZEMELY sz
	where SZE_AKTIV=1 and SZE_AMATOR=1 
		and LEN(SZE_ELETRAJZ)<=7
		and HELYISEG_AZ in (select HELYISEG_AZ
						from HELYISEG
						where (HELYISEGNEV='Budapest' or HELYISEGNEV='Beograd'));
go

select SZE_VEZETEKNEV, SZE_NEV
	from dbo.SZEMELY sz
	where SZE_AKTIV=1 and SZE_AMATOR=0 
		and (LEN(SZE_ELETRAJZ)<=7 or SZE_ELETRAJZ is null)
		and HELYISEG_AZ in (select HELYISEG_AZ
						from HELYISEG
						where (HELYISEGNEV='Budapest' or HELYISEGNEV='Beograd'));
go

/*
Listazzuk azokat a szemelyeket, akik nem budapestiek es nem belgradiak,
es akik aktivak, amatorok, es az eletrajzuk nem hosszabb, mint 7 jel.
*/

select SZE_VEZETEKNEV, SZE_NEV, HELYISEGNEV
	from dbo.SZEMELY sz
	join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
	where HELYISEGNEV<>'Budapest' and HELYISEGNEV<>'Beograd' and
		SZE_AKTIV=1 and SZE_AMATOR=1 
			and LEN(SZE_ELETRAJZ)<=7;
go
-- Hazi feladat - Alfoldi miert nem jelenik meg?
select SZE_VEZETEKNEV, SZE_NEV, HELYISEGNEV
	from dbo.SZEMELY sz
	join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
	where HELYISEGNEV<>'Budapest' and HELYISEGNEV<>'Beograd' and
		SZE_AKTIV=1 and SZE_AMATOR=0 
			and (LEN(SZE_ELETRAJZ)<=7 or SZE_ELETRAJZ is null);
go

/*
Listazzuk azokat a helyisegeket, amelyekben nem tartozkodnak szemelyek
*/
--I
select HELYISEGNEV
	from HELYISEG
	where HELYISEG_AZ not in (select HELYISEG_AZ
								from SZEMELY)
--II								
select HELYISEGNEV, SZE_VEZETEKNEV
	from HELYISEG h
	left join dbo.SZEMELY sz on sz.HELYISEG_AZ=h.HELYISEG_AZ
	where SZE_NEV is null

--III								
select HELYISEGNEV, SZE_VEZETEKNEV
	from dbo.SZEMELY sz
	right join HELYISEG h on sz.HELYISEG_AZ=h.HELYISEG_AZ
	where SZE_NEV is null
	
	
/*
SELECT-ek a SzinhPortal adatbazisban 
6. ora - 12. het - 2019.12.19.
*/

select * from dbo.ELOADAS_PROJEKT
select * from dbo.SZEREPKOR
/* Listazzuk a szindarabazonositot, a szerepkort, amelynek a tartalma 
Rendezo es az eloadas cime Ahogy tetszik/A patkányok/Rómeó és Júlia.
 Irjuk ki a szerepkor betuinek szamat.
Nenad Vojni?*/

--I
select ep.SZINDARAB_AZ, ep.ELOADAS_CIME, SZEREPKOR, LEN(SZEREPKOR)
	from dbo.ELOADAS_PROJEKT ep
	join dbo.E_P_HEZ_TARTOZO_SZEREPKOR epszk on epszk.SZINDARAB_AZ=ep.SZINDARAB_AZ
											and epszk.ELOADAS_CIME=ep.ELOADAS_CIME
	join dbo.SZEREPKOR szk on szk.SZEREPKOR_AZ=epszk.SZEREPKOR_AZ
	where SZEREPKOR='Rendezo' and ep.ELOADAS_CIME='Ahogy tetszik';

--II
select SZINDARAB_AZ, ELOADAS_CIME --, SZEREPKOR, LEN(SZEREPKOR)
	from dbo.ELOADAS_PROJEKT
	where ELOADAS_CIME='Ahogy tetszik' and 
	SZINDARAB_AZ in (select szindarab_az
					from dbo.E_P_HEZ_TARTOZO_SZEREPKOR 
					where SZEREPKOR_AZ in (select SZEREPKOR_AZ
											from dbo.SZEREPKOR
											where SZEREPKOR='Rendezo'))
	and ELOADAS_CIME in (select eloadas_cime
						from dbo.E_P_HEZ_TARTOZO_SZEREPKOR
						where SZEREPKOR_AZ in (select SZEREPKOR_AZ
												from dbo.SZEREPKOR
												where SZEREPKOR='Rendezo'));

/*
Listazzuk a szinhazneveket es a helysegneveket a szinhaz tabla azon
soraiban, amelyekben az iranyitoszam legnagyobb erteku
*/

select SZINHAZNEV, SZ_H_CIM_HELYSEG, SZ_H_CIM_IRANYITOSZAM
	from dbo.SZINHAZ
	where SZ_H_CIM_IRANYITOSZAM=(select MAX(SZ_H_CIM_IRANYITOSZAM)
									from dbo.SZINHAZ);
select * from dbo.SZINHAZ							

/*
Listazzuk a szinhazneveket es a helysegneveket a szinhaz tablabol ugy,
hogy azok a sorok ker-ljenek az eredmenytablaba, amelyek
a legnagyobb iranyitoszamal rendelkeznek a mukodesi varosukban
*/

select SZINHAZNEV, SZ_H_CIM_HELYSEG, SZ_H_CIM_IRANYITOSZAM
	from dbo.SZINHAZ szhk
	where SZ_H_CIM_IRANYITOSZAM=(select MAX(SZ_H_CIM_IRANYITOSZAM)
						from dbo.SZINHAZ szhb
						where szhb.SZ_H_CIM_HELYSEG=szhk.SZ_H_CIM_HELYSEG);
						
						
select * from dbo.HELYISEG

/*
Orszagazonositokent melyek a legnagyobb iranyitoszammal rendelkezo
helyisegek nev szerint
*/

select ORSZAG_AZ, HELYISEGNEV, H_IRANYITOSZAM
	from dbo.HELYISEG hk
	where H_IRANYITOSZAM=(select MAX(H_IRANYITOSZAM)
						from dbo.HELYISEG hb
						where hb.ORSZAG_AZ=hk.ORSZAG_AZ)
						


/*
SELECT-ek a SzinhPortal adatbazisban 
7. ora - 13. het - 2020.01.16.
*/

-- A szuresi lehetoseg csereszabatossaga 
--a WHERE es a HAVING kozott

/*
Listazzuk azokat a helyisegeket amelyek iranyitoszama
nagyobb mint 21000
*/
select HELYISEGNEV
	from dbo.HELYISEG
	where H_IRANYITOSZAM>21000;

--helytelen
select HELYISEGNEV
	from dbo.HELYISEG
	having H_IRANYITOSZAM>21000;
	
/*
Listazzuk azokat a helyisegeket amelyek iranyitoszama
nagyobb mint az atlagos iranyitoszam a helyiseg tablaban
*/

select HELYISEGNEV
	from dbo.HELYISEG
	where H_IRANYITOSZAM>(select AVG(H_IRANYITOSZAM)
						from dbo.HELYISEG);

select AVG(H_IRANYITOSZAM)
						from dbo.HELYISEG


-- Beepitett SELECT A from zaradekban
a
/*
Atlagosan hany szinhaz van a helysegekben? 
*/
select AVG(szinhazszam)
	from (select SZ_H_CIM_HELYSEG, COUNT(SZINHAZ_AZ) as "szinhazszam"
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG) q;
go

select AVG(szinhazszam)
	from (select COUNT(SZINHAZ_AZ) as "szinhazszam"
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG) q;
go

select AVG(convert(numeric,szinhazszam))
	from (select SZ_H_CIM_HELYSEG, COUNT(SZINHAZ_AZ) as "szinhazszam"
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG) q;
go

select AVG(szinhazszam)
	from (select SZ_H_CIM_HELYSEG, convert(numeric,COUNT(SZINHAZ_AZ)) as "szinhazszam"
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG) q;
go



/*
Hany olyan szindarab van mufajonkent (Mufaj_neve) a nyilvantartasban, 
amelyeknek pontosan (ketto) egy szerzoje van?
*/

select MUFAJ_NEVE, count(sz.SZINDARAB_AZ)
	from dbo.MUFAJ m
	join dbo.SZINDARAB sz on sz.MUFAJ_AZON=m.MUFAJ_AZON
	join (select szb.SZINDARAB_AZ, count(SZEMELY_AZ) as "szerzoszam"
			from dbo.SZINDARAB szb
			join dbo.SZERZO sze on sze.SZINDARAB_AZ=szb.SZINDARAB_AZ
			group by szb.SZINDARAB_AZ
			having count(SZEMELY_AZ)=1) w on w.SZINDARAB_AZ=sz.SZINDARAB_AZ
	group by MUFAJ_NEVE
	
select MUFAJ_NEVE, count(sz.SZINDARAB_AZ) as "Szindarabszam"
	from dbo.MUFAJ m
	join dbo.SZINDARAB sz on sz.MUFAJ_AZON=m.MUFAJ_AZON
	join (select szb.SZINDARAB_AZ, count(SZEMELY_AZ) as "szerzoszam"
			from dbo.SZINDARAB szb
			join dbo.SZERZO sze on sze.SZINDARAB_AZ=szb.SZINDARAB_AZ
			group by szb.SZINDARAB_AZ
			having count(SZEMELY_AZ)=2) w on w.SZINDARAB_AZ=sz.SZINDARAB_AZ
	group by MUFAJ_NEVE

	
select * from dbo.SZERZO