-- Rešenje ispita Januar-1-1 i Januar-1-2- 15. i 22. januar 2018.

use PPortal;
go

/*
1.	Listati po nazivu ona profesionalna pozorišta van Vojvodine 
	(koja nisu iz Vojvodine) čiji naziv Naziv_Pozorista) nije duži 
	od 25 slova (karaktera), a imaju barem jedan naveden internet kanal. 
	Tabelu rezultata urediti po atributu Naziv_Pozorista 
	(po abecednom redosledu)! (10 poena)
*/


select NAZIV_POZORISTA
	from dbo.POZORISTE
	where P_AMATERSKO='false' and P_IZ_VOJVODINE=0 and
			LEN(NAZIV_POZORISTA)<=25 and
			(P_WWW_ADRESA is not null or P_EMAIL is not null)
	order by 1;
go



/*
3.	Koja su ona mesta (Naziv_Mesta bez ponavljanja) iz Srbije (Drzava) 
	u kojima borave lica, a u kojima nema pozorišta (ne radi ni jedno 
	pozorište) po evidenciji? – ZA INFORMATIČARE (unutrašnji 
	select-6 poena, join-10 poena – menadžeri ne dobijaju poene za 
	rešenje ovog zadatka).
*/


select DRZAVA, NAZIV_MESTA
	from dbo.MESTO m
	join dbo.ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
	left join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where DRZAVA='Republika Srbija' and NAZIV_POZORISTA is null;
go


/*
4.	Po mestima (Naziv_Mesta) u kojima borave i evidentirana aktivna 
	profesionalna lica koliko ima pozorišta po evidenciji 
	(u bazi podataka)? U tabeli rezultata treba da se pojave samo oni 
	redovi u kojima taj broj nije manji od pet. Tabelu rezultata urediti 
	po broju pozorišta u opadajućem redosledu! (10 poena)
*/

--1.
select NAZIV_MESTA, COUNT(distinct SIF_POZORISTA)
	from dbo.MESTO m
	join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
	join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where L_AKTIVNO=1 and L_AMATER=0
	group by NAZIV_MESTA
	having COUNT(distinct SIF_POZORISTA)>=1 -- ovde naravno treba 5
	order by COUNT(SIF_POZORISTA);
go
-- 2.
select NAZIV_MESTA, COUNT(distinct SIF_POZORISTA)
	from dbo.MESTO m
	join dbo.LICE ls on ls.SIF_MESTA=m.SIF_MESTA
	join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where ls.SIF_MESTA in (
			select SIF_MESTA 
				from dbo.LICE lu
				where lu.SIF_MESTA=ls.SIF_MESTA and
					L_AKTIVNO=1 and L_AMATER=0)
	group by NAZIV_MESTA
	having COUNT(distinct SIF_POZORISTA)>=1 -- ovde naravno treba 5
	order by COUNT(SIF_POZORISTA);
go



/*
5.	Koliko ima evidentiranih profesionalnih lica i evidentiranih 
	amaterskih pozorišta po mestima (Naziv_Mesta) iz Srbije (Drzava). 
	U tabeli rezultata treba da se pojave samo oni redovi u kojima broj 
	lica veći od broja pozorišta. Tabelu rezultata urediti po broju 
	pozorišta u opadajućem redosledu! (10 poena)
*/

--1.
select l.NAZIV_MESTA, BrojLicaUMestu, BrojAmaterskihPozorista
	from (select NAZIV_MESTA,COUNT(SIF_LICA) as "BrojLicaUMestu"
			from dbo.MESTO m
			join dbo.ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
			join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
			where L_AMATER=0 and DRZAVA='Republika Srbija'
			group by NAZIV_MESTA) as l
	full outer join (select NAZIV_MESTA, COUNT(SIF_POZORISTA) as "BrojAmaterskihPozorista"
						from dbo.MESTO m
						join dbo.ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
						join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
						where P_AMATERSKO=0 and DRZAVA='Republika Srbija'
						group by NAZIV_MESTA) as p 
		on p.NAZIV_MESTA=l.NAZIV_MESTA
	where (BrojLicaUMestu>BrojAmaterskihPozorista or 
		BrojAmaterskihPozorista is null)
	order by BrojAmaterskihPozorista desc;
go

--2.
select NAZIV_MESTA,COUNT(SIF_LICA) as "BrojLicaUMestu"
	into NazMestaBrProfLica
	from dbo.MESTO m
	join dbo.ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
	where L_AMATER=0 and DRZAVA='Republika Srbija'
	group by NAZIV_MESTA
	order by NAZIV_MESTA;
go

select NAZIV_MESTA, COUNT(SIF_POZORISTA) as "BrojAmaterskihPozorista"
	into NazMestaBrProfPozorista
	from dbo.MESTO m
	join dbo.ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where P_AMATERSKO=0 and DRZAVA='Republika Srbija'
	group by NAZIV_MESTA
	order by NAZIV_MESTA;
go

select l.NAZIV_MESTA, BrojLicaUMestu, BrojAmaterskihPozorista
	from NazMestaBrProfLica l
	full outer join NazMestaBrProfPozorista p 
		on p.NAZIV_MESTA=l.NAZIV_MESTA
	where BrojLicaUMestu>BrojAmaterskihPozorista or 
		BrojAmaterskihPozorista is null
	order by BrojAmaterskihPozorista desc;
go