/*
Második kollokvium Adatbázisokból - 2018.01.10.
*/

/*
1.	Listázza a repertoáron lévő Előadások címeit (amelyek pillanatnyilag a 
	repertoáron vannak), amelyek hosszabbak két óránál vagy legalább 
	háromfelvonásosak és emellett a múlt évben (nem fixen 2017-ben!) volt a 
	bemutatójuk, a tartalom-leírásuk pedig hosszabb, mint 50 jel. Az 
	eredménytáblát rendezze ABC-sorrendbe az előadáscímek szerint! 
*/


use Szinhportal;
go

select * from dbo.ELOADAS_PROJEKT

select ELOADAS_CIME
	from dbo.ELOADAS_PROJEKT
	where REPERTOARON='true' and (EL_HOSSZA_PERC>120 or 
			EL_FELVONAS_SZAMA>=3)and 
			year(EL_BEMUTATO_DATUM)=year(getdate())-1 and 
			LEN(EL_TARTALOM)>50
	order by ELOADAS_CIME;
go


/*
2.	Mutassa ki, hogy évente hány olyan háromtól kevesebb felvonású 
	előadásnak volt bemutatója, amelyek hosszabbak voltak egy óránál, 
	de már nincsenek a repertoáron! Rendezze az eredménytáblát a 
	bemutatók száma szerint csökkenő sorrendbe!
 */

	
select year(EL_BEMUTATO_DATUM) as "Bemutato ev", 
	   COUNT(SZINDARAB_AZ) as "Bemutatok szama"
	from dbo.ELOADAS_PROJEKT
	where EL_FELVONAS_SZAMA<3 and EL_HOSSZA_PERC>60 and
		REPERTOARON='false'
	group by year(EL_BEMUTATO_DATUM)
	order by COUNT(SZINDARAB_AZ) desc;
go


/*
3.	Melyek azok a színházak (név szerint), amelyekhez nem tartozik 
	előadás a nyilvántartásban? - MANAGEMENTESEKNEK
*/
	 
select SZINHAZNEV 
	from dbo.SZINHAZ
	where SZINHAZ_AZ not in (select SZINHAZ_AZ
										from dbo.SZ_H_E_P);
go


select SZINHAZNEV
	from dbo.SZINHAZ sz
	left join dbo.SZ_H_E_P szhep on szhep.SZINHAZ_AZ=sz.SZINHAZ_AZ
	where ELOADAS_CIME is null;
go


/*

4.	Listázza név szerint azokat a web címmel nem redelkező,vajdasági, 
	profi színházakat amelyek legalább két előadással (bemutatóval) 
	kapcsolatosak! - INFORMATIKUSOKNAK
*/


select SZINHAZNEV, COUNT(ELOADAS_CIME) as "Bemutatok szama"
	from dbo.SZINHAZ szh
	join dbo.SZ_H_E_P szhep on szhep.SZINHAZ_AZ=szh.SZINHAZ_AZ
	where SZ_H_WWW_CIM is not null and SZ_H_VAJDASAGI=1 
		and SZ_H_AMATOR=0
	group by SZINHAZNEV
	having COUNT(ELOADAS_CIME)>=1;
go

--2.
select SZINHAZNEV, bemszam
	from (select SZINHAZNEV, COUNT(ELOADAS_CIME) as bemszam
			from dbo.SZINHAZ szh
			join dbo.SZ_H_E_P szhep on szhep.SZINHAZ_AZ=szh.SZINHAZ_AZ
			where SZ_H_WWW_CIM is not null and SZ_H_VAJDASAGI=1 
				  and SZ_H_AMATOR=0
			group by  SZINHAZNEV) as A
		where bemszam>=1;
go

--3.
select SZINHAZNEV
	from dbo.SZINHAZ szhk
		where SZ_H_WWW_CIM is not null and SZ_H_VAJDASAGI=1 
			and SZ_H_AMATOR=0
			and (select COUNT(ELOADAS_CIME)
				from dbo.SZINHAZ szhb
			join dbo.SZ_H_E_P szhep on szhep.SZINHAZ_AZ=szhb.SZINHAZ_AZ
			where szhb.SZINHAZ_AZ=szhk.SZINHAZ_AZ 
			group by  SZINHAZNEV)=1;
go


/*

5.	Listázza cím szerint azokat a színdarabokat, bemutatóik dátumát, 
	és a színázakat (Színháznév), amelyek megvalósították az előadásokat. 
	Csak a “Vígjáték” műfajban idei évre ütemezett bemutató dátumú előadások
	kerüljenek az eredménytáblába. A táblát rendezze ABC sorrendbe a 
	színdarabcímek szerint, a bemutató dátumok alapján növekvő sorrendbe, 
	majd a sznhaznevek szerint fordított ABC sorrendbe!
*/


select  SZINDARAB_CIME, EL_BEMUTATO_DATUM, SZINHAZNEV
	from dbo.SZINDARAB sz
	join dbo.ELOADAS_PROJEKT ep on ep.SZINDARAB_AZ=sz.SZINDARAB_AZ
	join dbo.SZ_H_E_P szhep on szhep.SZINDARAB_AZ=ep.SZINDARAB_AZ and
							ep.ELOADAS_CIME=szhep.ELOADAS_CIME
	join dbo.SZINHAZ szh on szh.SZINHAZ_AZ=szhep.SZINHAZ_AZ	
	where YEAR(EL_BEMUTATO_DATUM)=year(getdate()) and
			sz.MUFAJ_AZON=(select MUFAJ_AZON
							from dbo.MUFAJ
							where MUFAJ_NEVE='Vígjáték')
	order by SZINDARAB_CIME, EL_BEMUTATO_DATUM, SZINHAZNEV desc;
go



/*
6.	Hány bemutatójuk volt évente az elmúlt két (aktuális év mínusz egy és 
	aktuális év mínusz kettő) évben azoknak a színdaraboknak amelyek 
	műfaj-azonosítója nagyobb, mint az átlagos műfaj-azonosító a Műfaj 
	táblában? Csak olyan sorok jelenjenek meg, ahol a bemutatók száma 
	kisebb négynél. Az eredménytáblát rendezze a bemutatók száma szerint 
	csökkenő sorrendbe!
*/


select year(EL_BEMUTATO_DATUM) as "Evek (aktualis-1 es aktualis-2)", 
		COUNT(EL_BEMUTATO_DATUM) as "Bemutatok szama"
	from dbo.ELOADAS_PROJEKT ep
	join dbo.SZINDARAB sz on sz.SZINDARAB_AZ=ep.SZINDARAB_AZ 
		where (year(EL_BEMUTATO_DATUM)=year(getdate())-1 or
			year(EL_BEMUTATO_DATUM)=year(getdate())-2) and
			MUFAJ_AZON > (select AVG(MUFAJ_AZON)
										from dbo.MUFAJ)  
	group by year(EL_BEMUTATO_DATUM)
	having COUNT(EL_BEMUTATO_DATUM)< 4
	order by COUNT(EL_BEMUTATO_DATUM) desc;
go