-- Rešenje II. kolokvijuma - popravni 19-01-2018

use PPortal;
go

/*
1.	Listati nazive profesionalnih pozorišta izvan Vojvodine, sedišta 
	pozorišta (mesta) i njihove adrese. U tabeli rezultata treba da se 
	nađu pozorišta koja nemaju web adresu, a imaju ili telefon ili e-mail, 
	a naziv im počinje slovom B iz mesta sa početnim slovom takođe B. 
	Tabelu rezultata urediti po nazivu mesta, a u okviru mesta po nazivu 
	pozorišta po ABC-nom redosledu!
*/


select NAZIV_POZORISTA,P_MESTO,P_ULICA_BROJ
	from dbo.POZORISTE
	where P_AMATERSKO='false' and P_WWW_ADRESA is not null and
			(P_TELEFON is not null or P_EMAIL is not null) and
			LEFT(NAZIV_POZORISTA,1)='B' and LEFT(P_MESTO,1)='B'
	order by 2,1;
go



/*
2.	Iskazati koliko ima pozorišta po nazivima mesta, i po istim početnim 
	slovima naziva pozorišta unutar mesta. Uzimati u obzir samo amaterska 
	pozorišta koja nemaju Internet kanal. U tabelu rezultata treba da se 
	pojave samo mesta sa početnim slovom B, i pozorišta sa početnim slovom 
	B ili D! Tabelu rezultata urediti po nazivima mesta, a u okviru mesta po 
	pocetnim slovima naziva pozorišta po ABC-nom redosledu!
*/


select P_MESTO,LEFT(NAZIV_POZORISTA,1) as "Prvo slovo naziva pozorišta",
		COUNT(SIF_POZORISTA) as 'Broj pozorišta'
	from dbo.POZORISTE
	where P_AMATERSKO=0 and P_WWW_ADRESA is not null and P_EMAIL is not null and
			(LEFT(NAZIV_POZORISTA,1)='B' or LEFT(NAZIV_POZORISTA,1)='D') 
			and LEFT(P_MESTO,1)='B'
	group by P_MESTO,LEFT(NAZIV_POZORISTA,1)
	order by P_MESTO,LEFT(NAZIV_POZORISTA,1);
go


/*
4.	Listati nazive svih onih profesionalnih pozorišta iz Vojvodine 
	bez web adrese koja nisu imala ni jednu predstavu u prošloj godini! 
	– ZA INFORMATIČARE
*/

select NAZIV_POZORISTA
	from dbo.POZORISTE p
	left join (select SIF_POZORISTA,PREDST_DAT_I_VREME_PREMIJERE
				from dbo.POZ_PREDST_PROJ ppp
				join dbo.PREDSTAVA_PROJEKT pp 
					on pp.SIF_POZ_DELA=ppp.SIF_POZ_DELA and
					pp.NASLOV_PREDSTAVE=ppp.NASLOV_PREDSTAVE
				where YEAR(PREDST_DAT_I_VREME_PREMIJERE)=YEAR(getdate()-1)) a
				on a.SIF_POZORISTA=p.SIF_POZORISTA
	where P_IZ_VOJVODINE=1 and P_WWW_ADRESA is null and
			a.PREDST_DAT_I_VREME_PREMIJERE is null;
go


/*
5.	Listati pozorišta (Naziv_Pozorišta), godinu premijere, 
	Naslov_Predstave, pripadajućeg Naslova_Poz_dela i žanr 
	pozorišnog dela (Zanr). Intertesuju nas samo pozorišta iz Beograda, 
	pozorišna dela i žanrovi za koje su u tekućoj i u prethodne 
	dve godine bile premijere od domaćih autora (nema prevodioca pozorišnog 
	dela). Tabelu rezultata urediti po nazivu pozorišta po ABC-dnom, po 
	godini premijere po opadajućem, a po naslovu predstave po ABD-dnom
	redosledu!
*/


select NAZIV_POZORISTA,YEAR(PREDST_DAT_I_VREME_PREMIJERE) as "Godina premijere",
		pp.NASLOV_PREDSTAVE,NASLOV_POZ_DELA,ZANR,SIF_LICA_PREVOD
	from dbo.POZORISTE p
	join dbo.POZ_PREDST_PROJ ppp on ppp.SIF_POZORISTA=p.SIF_POZORISTA
	join dbo.PREDSTAVA_PROJEKT pp on pp.SIF_POZ_DELA=ppp.SIF_POZ_DELA and
			pp.NASLOV_PREDSTAVE=ppp.NASLOV_PREDSTAVE
	join dbo.POZORISNO_DELO pd on pd.SIF_POZ_DELA=pp.SIF_POZ_DELA
	join dbo.ZANR z on z.SIF_ZANRA=pd.SIF_ZANRA
	where P_POSTANSKI_BROJ between 11000 and 11999 and
		YEAR(PREDST_DAT_I_VREME_PREMIJERE) in
		(YEAR(getdate()), YEAR(getdate())-1,YEAR(getdate())-2) and 
		SIF_LICA_PREVOD is null
	order by 1,2,3;
go

/*
6.	Godišnje koliko ima premijera po pojedinim pozorištima 
	(Naziv_Pozorišta) iz pojedinih žanrova (Zanr). Premijere se 
	pripremaju na osnovu pozorišnih dela koja su opisana obeležjem 
	žanr (Zanr). U sastavljanju izveštaja uzeti u obzir samo pozorišta 
	izvan Vojvodine iz Beogradskog i Niškog okruga!
*/


select YEAR(PREDST_DAT_I_VREME_PREMIJERE) as "Godina",NAZIV_POZORISTA,ZANR,	
			COUNT(PREDST_DAT_I_VREME_PREMIJERE)
	from dbo.POZORISTE p
	join dbo.POZ_PREDST_PROJ ppp on ppp.SIF_POZORISTA=p.SIF_POZORISTA
	join dbo.PREDSTAVA_PROJEKT pp on pp.SIF_POZ_DELA=ppp.SIF_POZ_DELA and
			pp.NASLOV_PREDSTAVE=ppp.NASLOV_PREDSTAVE
	join dbo.POZORISNO_DELO pd on pd.SIF_POZ_DELA=pp.SIF_POZ_DELA
	join dbo.ZANR z on z.SIF_ZANRA=pd.SIF_ZANRA
	where P_IZ_VOJVODINE=0 and (P_POSTANSKI_BROJ between 11000 and 11999 or
			P_POSTANSKI_BROJ between 18000 and 18999)
	group by YEAR(PREDST_DAT_I_VREME_PREMIJERE),NAZIV_POZORISTA,ZANR;
go