/*
Rad sa Pozorisnim portalom 
1. cas - 7. nedelja -09.11.2018.

Druga grupa

*/
use PPortal;
go


select * from ZEMLJA;
go

select * from MESTO

/*
Listati postanske brojeve i nazive mesta iz tabele MESTO
*/

select POST_BROJ, NAZIV_MESTA
	from MESTO;
go

/*
Listati sifre zemlje u tabeli mesto
*/

select SIF_ZEMLJE
	from MESTO;
go

/*
Listati sifre zemlje bez ponavljanja u tabeli mesto
*/
select distinct SIF_ZEMLJE
	from MESTO;
go

/*
Koliko ima sifara zemlje u tabeli MESTO
*/
select count(SIF_ZEMLJE)
	from MESTO;
go

/*
Koliko ima sifara zemlje u tabeli MESTO, sa oznacenim nazivom kolone u TR
	Br. sifara zemlje u tab. MESTO
*/
select count(SIF_ZEMLJE) as "Br. sifara zemlje u tab. MESTO"
	from MESTO;
go

/*
Listati post. brojeve /naziv kolone Postanski broj/ kolonu koja ce sadrzati
vrednost "je postanski broj za mesto:" /naziv kolone Oznaka pripadnosti/ i 
naziv mesta /naziv kolone Mesto/
*/

select POST_BROJ as "Postanski broj",
		'je postanski broj za mesto:' as "Oznaka pripadnost:",
		NAZIV_MESTA as "Mesto"
	from MESTO;
go
		
/*
Koliko ima sifara zemlje bez ponavljanja u tabeli MESTO, sa oznacenim 
nazivom kolone u TR Br. jedinstvenih sifara zemlje u tab. MESTO
*/
select count(distinct SIF_ZEMLJE) as 
		"Br. jedinstvenih sifara zemlje u tab. MESTO"
	from MESTO;
go

/*
Navesti jedinstvene nazive mesta iz tab. MESTO
*/
select distinct NAZIV_MESTA
	from MESTO;
go

/*
Navesti jedinstvene post brojeve i nazive mesta iz tab. MESTO
*/
select distinct POST_BROJ, NAZIV_MESTA
	from MESTO;
go

/*
select distinct POST_BROJ, distinct NAZIV_MESTA
	from MESTO;
go
*/


/*
Rad sa Pozorisnim portalom 
2. cas - 8. nedelja -16.11.2018.

Druga grupa

*/


/*
Listati broj sifara mesta, broj sifara zemlje,
	najmanji i najveci postanski broj, prvi i zadnji naziv mesta po
	abecedi iz tabele MESTO
*/

select COUNT(SIF_MESTA), COUNT(SIF_ZEMLJE), MIN(POST_BROJ), MAX(POST_BROJ),
		MIN(NAZIV_MESTA), MAX(NAZIV_MESTA)
	from MESTO;
go

select * 
	from MESTO
	order by POST_BROJ;
go

select * 
	from MESTO
	order by POST_BROJ desc;
go

/*
Listati broj sifara mesta sa nazivom kolone..., 
		broj sifara zemlje sa nazivom kolone...
		najmanji sa naz... i najveci sa naz.. postanski broj, 
		prvi i zadnji naziv mesta po
	abecedi iz tabele MESTO
*/






/*
Listati naziv mesta, broj sifara mesta sa nazivom kolone..., 
		broj sifara zemlje sa nazivom kolone...
		najmanji sa naz... i najveci sa naz.. postanski broj, 
		prvi i zadnji naziv mesta po
	abecedi iz tabele MESTO
*/

select 	COUNT(SIF_MESTA), COUNT(SIF_ZEMLJE), MIN(POST_BROJ), MAX(POST_BROJ),
		MIN(NAZIV_MESTA), MAX(NAZIV_MESTA)
	from MESTO
	group by NAZIV_MESTA;
go

select * from MESTO

/*
Odrediti broj znakova (duzinu unesene vrednosti) u kolomu maziv mesta
u tabeli MESTO
*/
select NAZIV_MESTA,LEN(NAZIV_MESTA) as "Broj slova"
	FROM MESTO;
GO

/*
Listati nazive mesta, prva slovo naziva mesta, zadnje slovo naziva mesta
iz tabele MESTO
*/

select NAZIV_MESTA, LEFT(NAZIV_MESTA,1), RIGHT(NAZIV_MESTA,1)
	from MESTO;
go

/*
Listati nazive mesta, i prikazati od treceg slova tri znakova iz
naziva mesta iz tabele MESTO
*/

select NAZIV_MESTA,SUBSTRING(NAZIV_MESTA,3,3)
	from MESTO;
go

/*
Listati naziv mesta, duzinu naziva mesta (broj znakova u nazivu mesta)
		prvo slovo naziva mesta, zadnji znak u nazivu mesta
		prva tri znaka u nazivu mesta, zadnja tri znaka u nazivu mesta
		od cetvrtog znaka u nazivu ukupno 5 znakova iz naziva mesta -
		tabela je MESTO
*/

select NAZIV_MESTA, LEFT(NAZIV_MESTA,1) as "Prvo slovo-left",
		SUBSTRING(NAZIV_MESTA,1,1) as "Prvo slovo-substr", 
		RIGHT(NAZIV_MESTA,1) as "Zadnje slovo-right",
		SUBSTRING(NAZIV_MESTA,len(NAZIV_MESTA),1) as "Zadnje slovo-substr",
		LEFT(NAZIV_MESTA,3) as "Prva tri slova-left",
		SUBSTRING(NAZIV_MESTA,1,3) as "Prva tri slova-substr",
		SUBSTRING(NAZIV_MESTA,len(NAZIV_MESTA)-2,3) as "Zadnja tri slovo-substr",
		substring(NAZIV_MESTA,4,5) as "Od cetvrtog-5 znakova" 
		
	from MESTO;
go

/*
Listati naziv predstave, PREDST_DAT_I_VREME_PREMIJERE, godinu premijere,
		mesec premijere, dan premijere, vreme pocetka premijere
		iz tabele dbo.PREDSTAVA_PROJEKT 
*/

select NASLOV_PREDSTAVE, PREDST_DAT_I_VREME_PREMIJERE,	
		YEAR(PREDST_DAT_I_VREME_PREMIJERE),
		MONTH(PREDST_DAT_I_VREME_PREMIJERE),
		DAY(PREDST_DAT_I_VREME_PREMIJERE)
	from PREDSTAVA_PROJEKT;
go

select NASLOV_PREDSTAVE, PREDST_DAT_I_VREME_PREMIJERE,	
		YEAR(PREDST_DAT_I_VREME_PREMIJERE),
		MONTH(PREDST_DAT_I_VREME_PREMIJERE),
		DAY(PREDST_DAT_I_VREME_PREMIJERE),
		CAST(PREDST_DAT_I_VREME_PREMIJERE as CHAR),
		substring(CAST(PREDST_DAT_I_VREME_PREMIJERE as CHAR),12,8)
	from PREDSTAVA_PROJEKT;
go



/*
Rad sa Pozorisnim portalom 
3 cas - 9. nedelja -23.11.2018.

Druga grupa

*/

-- WHERE klauzula

/*
Listati nazive pozorista izBeograda - tabela Pozoriste
*/

select NAZIV_POZORISTA, P_MESTO
	from POZORISTE
	where P_MESTO='Beograd';
go

/*
Listati nazive amaterskih pozorista iz Beograda - tabela Pozoriste
*/

select NAZIV_POZORISTA, P_MESTO, P_AMATERSKO
	from POZORISTE
	where P_MESTO='Beograd' and P_AMATERSKO=1;
go

select NAZIV_POZORISTA, P_MESTO, P_AMATERSKO
	from POZORISTE
	where P_MESTO='Beograd' and P_AMATERSKO='true';
go


/*
Listati nazive profesionalnih pozorista iz Beograda - tabela Pozoriste
*/

select NAZIV_POZORISTA, P_MESTO, P_AMATERSKO
	from POZORISTE
	where P_MESTO='Beograd' and P_AMATERSKO=0;
go

select NAZIV_POZORISTA, P_MESTO, P_AMATERSKO
	from POZORISTE
	where P_MESTO='Beograd' and P_AMATERSKO='false';
go


/*
Listati nazive profesionalnih pozorista koja imaju telefon a 
nemaju veb adresu - tabela Pozoriste
*/

select NAZIV_POZORISTA, P_MESTO, P_AMATERSKO, P_TELEFON, P_WWW_ADRESA
	from POZORISTE
	where P_TELEFON is not null  and P_AMATERSKO='false'
		and P_WWW_ADRESA is null;
go

/*
Listati nazive pozorista koja su profesionalna iz Vojvodine ili su iz Beograda 
i nemaju veb adresu - tabela Pozoriste
*/

select NAZIV_POZORISTA, P_MESTO, P_AMATERSKO, P_IZ_VOJVODINE, P_TELEFON, P_WWW_ADRESA
	from POZORISTE
	where (P_AMATERSKO='false' and P_IZ_VOJVODINE='true') or
			(P_MESTO='Beograd' and P_WWW_ADRESA is null);
go

select * from POZORISTE


/*
Listati nazive pozorista iz ulica koje imaju prvo slovo naziva P
i imaju i telefon i veb adresu - tabela Pozoriste
*/

select NAZIV_POZORISTA, P_MESTO, P_ULICA_BROJ, P_TELEFON, P_WWW_ADRESA
	from POZORISTE
	where 
		P_ULICA_BROJ like 'P%'
		and P_TELEFON is not null
		and P_WWW_ADRESA is not null;
go

select NAZIV_POZORISTA, P_MESTO, P_ULICA_BROJ, P_TELEFON, P_WWW_ADRESA
	from POZORISTE
	where 
--		P_ULICA_BROJ like 'P%'
		left(P_ULICA_BROJ,1)='P'
		and P_TELEFON is not null
		and P_WWW_ADRESA is not null;
go

/*
Listati nazive pozorista iz Beograda i Novog Sada koja imaju i telefon 
i veb adresu - tabela Pozoriste
*/

select NAZIV_POZORISTA, P_MESTO, P_ULICA_BROJ, P_TELEFON, P_WWW_ADRESA
	from POZORISTE
	where 
		(P_MESTO='Beograd' or  P_MESTO='Novi Sad')
		and P_TELEFON is not null
		and P_WWW_ADRESA is not null;
go

select NAZIV_POZORISTA, P_MESTO, P_ULICA_BROJ, P_TELEFON, P_WWW_ADRESA
	from POZORISTE
	where 
		P_MESTO in ('Beograd','Novi Sad')
		and P_TELEFON is not null
		and P_WWW_ADRESA is not null;
go

select NAZIV_POZORISTA, P_MESTO, P_ULICA_BROJ, P_TELEFON, P_WWW_ADRESA
	from POZORISTE
	where 
		SIF_MESTA in (select SIF_MESTA
						from mesto 
						where NAZIV_MESTA in ('Beograd','Novi Sad'))
		and P_TELEFON is not null
		and P_WWW_ADRESA is not null;
go


-- Group by klauzula

/*
Koliko imamo pozorista u evidenciji
- koristiti tabelu pozoriste
*/

select COUNT(SIF_POZORISTA) as "Broj pozorista",
		 COUNT(P_WWW_ADRESA) as "Broj www adresa pozorista"
	from POZORISTE;
go

select * from POZORISTE


/*
Koliko imamo pozorista u evidenciji u pojedinim mestima
- koristiti tabelu pozoriste
*/

select P_MESTO, COUNT(SIF_POZORISTA) as "Broj pozorista",
		 COUNT(P_WWW_ADRESA) as "Broj www adresa pozorista"
	from POZORISTE
	group by P_MESTO;
go

/*
Koliko imamo pozorista u evidenciji u pojedinim mestima i po statusu pozorista
(amatersko, profesionalno - koristiti tabelu pozoriste
*/

select P_MESTO, P_AMATERSKO, COUNT(SIF_POZORISTA) as "Broj pozorista",
		 COUNT(P_WWW_ADRESA) as "Broj www adresa pozorista"
	from POZORISTE
	group by P_MESTO, P_AMATERSKO;
go

/*
Koliko imamo pozorista u evidenciji u pojedinim mestima i po geografskim epitetima
(iz Vojvodine i izvan Vojvodine - koristiti tabelu pozoriste
*/

select P_MESTO, P_IZ_VOJVODINE, COUNT(SIF_POZORISTA) as "Broj pozorista",
		 COUNT(P_WWW_ADRESA) as "Broj www adresa pozorista"
	from POZORISTE
	group by P_MESTO, P_IZ_VOJVODINE;
go

/*
Koliko imamo pozorista u evidenciji po geografskim epitetima
(iz Vojvodine i izvan Vojvodine) a unutar toga po pojedinim mestima 
- koristiti tabelu pozoriste
*/

select P_IZ_VOJVODINE, P_MESTO,  COUNT(SIF_POZORISTA) as "Broj pozorista",
		 COUNT(P_WWW_ADRESA) as "Broj www adresa pozorista"
	from POZORISTE
	group by P_IZ_VOJVODINE, P_MESTO ;
go

--HAVING klauzula

/*
Koliko imamo pozorista po mestima, a unutar mesta po postanskim
brojevima u evidenciji - koristiti tabelu pozoriste. Zadrzati one redove u TR
koji pokazuju broj pozorista iz Beograda
*/

--I.resenje pomocu HAVING-a
select P_MESTO, P_POSTANSKI_BROJ, COUNT(SIF_POZORISTA)
	from POZORISTE
	group by P_MESTO, P_POSTANSKI_BROJ
	having P_MESTO='Beograd' ;
go

--II.resenje pomocu WHERE-a
select P_MESTO, P_POSTANSKI_BROJ, COUNT(SIF_POZORISTA)
	from POZORISTE
	where P_MESTO='Beograd'
	group by P_MESTO, P_POSTANSKI_BROJ;
go


/*
Koliko imamo pozorista po mestima, a unutar mesta po postanskim
brojevima u evidenciji - koristiti tabelu pozoriste. Zadrzati one redove u TR
u kojima je broj sifara pozorista veci od 1
*/

--I.resenje pomocu HAVING-a
select P_MESTO, P_POSTANSKI_BROJ, COUNT(SIF_POZORISTA)
	from POZORISTE
	group by P_MESTO, P_POSTANSKI_BROJ
	having COUNT(SIF_POZORISTA)>1 ;
go

--II.resenje pomocu WHERE-a
select P_MESTO, P_POSTANSKI_BROJ, COUNT(SIF_POZORISTA)
	from POZORISTE
	where COUNT(SIF_POZORISTA)>1
	group by P_MESTO, P_POSTANSKI_BROJ;
go

/*
Listati nazive profesionalnih pozorista iz Beograda - tabela Pozoriste
*/

select NAZIV_POZORISTA, P_MESTO, P_AMATERSKO
	from POZORISTE
	where P_MESTO='Beograd' and P_AMATERSKO=0;
go

-- pogresan pristup
select NAZIV_POZORISTA, P_MESTO, P_AMATERSKO
	from POZORISTE
	having P_MESTO='Beograd' and P_AMATERSKO=0;
go



-- ORDER BY klauzula

/*
Listati nazive pozorista i urediti listu po mestima
*/

select P_MESTO, NAZIV_POZORISTA
	from POZORISTE
	order by P_MESTO;
go

/*
Listati nazive pozorista i urediti listu po mestima 
i po statusu pozorista (amatersko, profesionalno)
*/

select P_MESTO, P_AMATERSKO, NAZIV_POZORISTA
	from POZORISTE
	order by P_MESTO, P_AMATERSKO;
go

/*
Listati nazive pozorista i urediti po geografskim epitetima
(iz Vojvodine i izvan Vojvodine) a unutar toga po pojedinim mestima
*/

select P_IZ_VOJVODINE, P_MESTO, NAZIV_POZORISTA
	from POZORISTE
	order by P_IZ_VOJVODINE, P_MESTO;
go


/*
Rad sa Pozorisnim portalom 
4 cas - 10. nedelja -30.11.2018.

Druga grupa

*/

-- VISETABLICNI UPITI



select * from POZORISNO_DELO
select * from ZANR

/*
Listati zanrove (ZANR) i pripadajuca pozorisna dela
*/

--I Dekartov proizvod
select ZANR, NASLOV_POZ_DELA
	from dbo.ZANR z, dbo.POZORISNO_DELO pd
	where z.SIF_ZANRA=pd.SIF_ZANRA
	
	--II JOIN
select ZANR, NASLOV_POZ_DELA
	from dbo.ZANR z
	join dbo.POZORISNO_DELO pd on z.SIF_ZANRA=pd.SIF_ZANRA

/*
Listati sifre i nazive zanrova koji nisu vezani ni sa jednim 
pozorisnim delom
*/

-- I. OUTER (LEFT) JOIN

select z.SIF_ZANRA, ZANR,NASLOV_POZ_DELA 
	from dbo.ZANR z
	left join dbo.POZORISNO_DELO pd on pd.SIF_ZANRA=z.SIF_ZANRA
	where POZ_DELO_BR_CINOVA is null

-- II. OUTER (RIGHT) JOIN

select z.SIF_ZANRA, ZANR,NASLOV_POZ_DELA 
	from dbo.POZORISNO_DELO pd
	right join dbo.ZANR z on pd.SIF_ZANRA=z.SIF_ZANRA
	where POZ_DELO_BR_CINOVA is null

-- III. Unutrasnji SELECT

select SIF_ZANRA, ZANR
	from dbo.ZANR
	where SIF_ZANRA not in (select SIF_ZANRA
								from dbo.POZORISNO_DELO)

/*
Listati nazive drzava, nazive mesta i pripadajuca pozorista po nazivima
*/
--I. Dekartov proizvod
select DRZAVA,NAZIV_MESTA, NAZIV_POZORISTA
	from ZEMLJA z, MESTO m,POZORISTE p
	where z.SIF_ZEMLJE=m.SIF_ZEMLJE and m.SIF_MESTA=p.SIF_MESTA
	
	--II. INNER JOIN
select DRZAVA,NAZIV_MESTA, NAZIV_POZORISTA
	from ZEMLJA z
	join MESTO m on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	join POZORISTE p on m.SIF_MESTA=p.SIF_MESTA
	
	--II. OUTER (LEFT) JOIN
select DRZAVA,NAZIV_MESTA, NAZIV_POZORISTA
	from ZEMLJA z
	left join MESTO m on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	left join POZORISTE p on m.SIF_MESTA=p.SIF_MESTA
	where POST_BROJ is not null and P_ULICA_BROJ is not null
	

/*
Listati nazive drzava, nazive mesta i pripadajuca pozorista po nazivima 
zadrzavajuci samo mesta i pozorista iz Crne Gore
*/

select DRZAVA,NAZIV_MESTA, NAZIV_POZORISTA
	from ZEMLJA z
	left join MESTO m on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	left join POZORISTE p on m.SIF_MESTA=p.SIF_MESTA
	where POST_BROJ is not null and P_ULICA_BROJ is not null
		and drzava='Crna Gora'
		
/*
Listati nazive drzava, nazive mesta i broj pripadajucih pozorista 
zadrzavajuci samo mesta i pozorista iz Republike Srbije
*/

select DRZAVA,NAZIV_MESTA, count(NAZIV_POZORISTA)
	from ZEMLJA z
	join MESTO m on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	join POZORISTE p on m.SIF_MESTA=p.SIF_MESTA
	where drzava='Republika Srbija'
	group by DRZAVA,NAZIV_MESTA


/*
Listati nazive drzava, nazive mesta i broj pripadajucih pozorista 
zadrzavajuci samo mesta i pozorista.
*/

select DRZAVA,NAZIV_MESTA, count(NAZIV_POZORISTA)
	from ZEMLJA z
	join MESTO m on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	join POZORISTE p on m.SIF_MESTA=p.SIF_MESTA
	group by DRZAVA,NAZIV_MESTA


/*
Listati nazive drzava, nazive mesta i broj pripadajucih pozorista 
zadrzavajuci samo mesta i pozorista. Urediti TR po rastucim/opadajucim 
vrednostima broja pozorista
*/

select DRZAVA,NAZIV_MESTA, count(NAZIV_POZORISTA)
	from ZEMLJA z
	join MESTO m on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	join POZORISTE p on m.SIF_MESTA=p.SIF_MESTA
	group by DRZAVA,NAZIV_MESTA
	order by count(NAZIV_POZORISTA) desc

/*
Listati nazive drzava, nazive mesta i broj pripadajucih pozorista 
. Zadravati samo one redove iz TR
u kojima je broj pozorista veci od 1. Urediti TR po rastucim/opadajucim 
vrednostima broja pozorista
*/

select DRZAVA,NAZIV_MESTA, count(NAZIV_POZORISTA)
	from ZEMLJA z
	join MESTO m on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	join POZORISTE p on m.SIF_MESTA=p.SIF_MESTA
	group by DRZAVA,NAZIV_MESTA
	having count(NAZIV_POZORISTA)>1
	order by count(NAZIV_POZORISTA) desc


/*
Listati nazive drzava, nazive mesta i broj pripadajucih pozorista izvan teritorije 
Republike Srbije. Zadravati samo one redove iz TR
u kojima je broj pozorista veci od 1. Urediti TR po rastucim/opadajucim 
vrednostima broja pozorista
*/

select DRZAVA,NAZIV_MESTA, count(NAZIV_POZORISTA)
	from ZEMLJA z
	join MESTO m on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	join POZORISTE p on m.SIF_MESTA=p.SIF_MESTA
	group by DRZAVA,NAZIV_MESTA
	having DRZAVA<>'Republika Srbija' and count(NAZIV_POZORISTA)>0
	order by count(NAZIV_POZORISTA) desc
	
select DRZAVA,NAZIV_MESTA, count(NAZIV_POZORISTA)
	from ZEMLJA z
	join MESTO m on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	join POZORISTE p on m.SIF_MESTA=p.SIF_MESTA
	where DRZAVA<>'Republika Srbija'
	group by DRZAVA,NAZIV_MESTA
	having count(NAZIV_POZORISTA)>0
	order by count(NAZIV_POZORISTA) desc
	


/*
Rad sa Pozorisnim portalom 
5. cas - 11. nedelja -07.12.2018.

Druga grupa

*/

/* Listati lica iz Budimpešte i Beograda koja su aktivna i amaterski se bave 
pozorištem, biografija ne sme biti duža od 7 znakova.
Jelena Petrik */

--I. JOIN
select L_IME, L_PREZIME
	from dbo.LICE l
	join dbo.MESTO m on m.SIF_MESTA=l.SIF_MESTA
	where (NAZIV_MESTA='Budimpesta'or NAZIV_MESTA='Beograd') and
		L_AKTIVNO='true' and L_AMATER=1 and LEN(L_BIOGRAFIJA)<8

select * from LICE
select * from MESTO

--I. Unutrasnji SELECT
select L_IME, L_PREZIME
	from dbo.LICE 
	where L_AKTIVNO='true' and L_AMATER=1 and LEN(L_BIOGRAFIJA)<8
		and SIF_MESTA in (select SIF_MESTA
							from dbo.MESTO
							where NAZIV_MESTA='Budimpesta'or NAZIV_MESTA='Beograd')
							
select L_IME, L_PREZIME
	from dbo.LICE 
	where L_AKTIVNO='true' and L_AMATER=1 and LEN(L_BIOGRAFIJA)<8
		and SIF_MESTA in (select SIF_MESTA
							from dbo.MESTO
							where NAZIV_MESTA in('Budimpesta', 'Beograd'))

/* Ispisati šifru pozorišnog dela i ulogu, gde je uloga Reditelj i 
naslov pozorišnog dela Indigo. Ispisati takođe dužinu uloge.
Nenad Vojnić*/

--I. JOIN
select pd.SIF_POZ_DELA, NASLOV_POZ_DELA, ULOGA, LEN(ULOGA) as "Br. karaktera uloge"
	from dbo.PREDST_PROJ_ULOGA ppu
	join dbo.ULOGA u on u.SIF_ULOGE=ppu.SIF_ULOGE
	join dbo.PREDSTAVA_PROJEKT pp on pp.SIF_POZ_DELA=ppu.SIF_POZ_DELA and
									pp.NASLOV_PREDSTAVE=ppu.NASLOV_PREDSTAVE
	join dbo.POZORISNO_DELO pd on pd.SIF_POZ_DELA=pp.SIF_POZ_DELA
	where ULOGA='Reditelj' and NASLOV_POZ_DELA='Indigo'
	

--II. Unutrasnji select
select SIF_POZ_DELA, ULOGA, LEN(ULOGA) as "Br. karaktera uloge"
	from dbo.PREDST_PROJ_ULOGA ppu
	join dbo.ULOGA u on u.SIF_ULOGE=ppu.SIF_ULOGE
	where ULOGA='Reditelj' and SIF_POZ_DELA in (select SIF_POZ_DELA
												from dbo.PREDSTAVA_PROJEKT
												where SIF_POZ_DELA=(select SIF_POZ_DELA
																	from POZORISNO_DELO
																	where NASLOV_POZ_DELA='Indigo')) 
							and NASLOV_PREDSTAVE in (select NASLOV_PREDSTAVE
												from dbo.PREDSTAVA_PROJEKT
												where SIF_POZ_DELA=(select SIF_POZ_DELA
																	from POZORISNO_DELO
																	where NASLOV_POZ_DELA='Indigo'))
	

/* Ispisati sve nazive mesta gde nema amaterskih pozorista gde je 
sif pozorista sortirana u opadajucem nizu 
Luka Patarcic  12118201*/

select distinct NAZIV_MESTA, SIF_POZORISTA
	from dbo.MESTO m
	left join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where P_AMATERSKO=0
	order by SIF_POZORISTA desc

select distinct NAZIV_MESTA
	from dbo.MESTO
	where SIF_MESTA not in (select SIF_MESTA
								from dbo.POZORISTE
								where P_AMATERSKO=1)
								


/*
Rad sa Pozorisnim portalom 
6. cas - 12. nedelja -14.12.2018.

Druga grupa

*/

-- unutrasnji SELECT se izvrsava vise puta
/*
Listati nazive drzava i njima pripadajuce nazive onih mesta koja imaju
najveci postanski broj u doticnoj drzavi
*/

select DRZAVA, NAZIV_MESTA,POST_BROJ, m.SIF_ZEMLJE
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	where  POST_BROJ=(select MAX(POST_BROJ)
						from dbo.MESTO mu
						where mu.SIF_ZEMLJE=m.SIF_ZEMLJE);
go

select DRZAVA, NAZIV_MESTA,POST_BROJ, m.SIF_ZEMLJE
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	where  POST_BROJ IN (select MAX(POST_BROJ)
						from dbo.MESTO mu
						where mu.SIF_ZEMLJE=m.SIF_ZEMLJE
						group by mu.SIF_ZEMLJE);
go




/*
Listati nazive onih predstava koje imaju broj cinova jednak 
maksimalnom broju cinova iz tabele Predstava_projekat
*/

select NASLOV_PREDSTAVE
	from dbo.PREDSTAVA_PROJEKT
	where PREDST_BR_CINOVA=(select MAX(PREDST_BR_CINOVA)
								from dbo.PREDSTAVA_PROJEKT);
go

select * from dbo.PREDSTAVA_PROJEKT

/*
Listati nazive drzava koje imaju postanski broj jednak
najvecem postanskom broju iz tabele MESTO
*/

select DRZAVA, POST_BROJ
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	where POST_BROJ=(select MAX(POST_BROJ)
						from dbo.MESTO);
go


select DRZAVA
	from dbo.ZEMLJA
	where SIF_ZEMLJE=(select SIF_ZEMLJE
						from dbo.MESTO
						where POST_BROJ=(select MAX(POST_BROJ)
											from dbo.MESTO));
go

/*
Listati nazive mesta i postanskih brojeva iz tabele MESTO i nazive mesta i
postanskih brojeva iz tabele Pozoriste. Dve tabele povezati na osnovu
naziva mesta
*/

select NAZIV_MESTA, POST_BROJ, P_MESTO, P_POSTANSKI_BROJ
	from dbo.MESTO
	join dbo.POZORISTE on P_MESTO=NAZIV_MESTA;
go

/*
Listati nazive mesta i postanskih brojeva iz tabele MESTO i nazive 
pozorista, nazive mesta i
postanskih brojeva iz tabele Pozoriste za postanske brojeve koje su razlicite
u dve tabele. Dve tabele povezati na osnovu naziva mesta
*/

select NAZIV_MESTA, POST_BROJ, P_MESTO, P_POSTANSKI_BROJ
	from dbo.MESTO
	join dbo.POZORISTE on P_MESTO=NAZIV_MESTA
	where POST_BROJ<>P_POSTANSKI_BROJ;
go


/*
Izracunati koliko prosecno ima pozorista u mestima 
*/

select AVG(br_poz_u_mestu)
	from (select convert(numeric,COUNT(SIF_POZORISTA)) as "br_poz_u_mestu"
			from dbo.POZORISTE
			group by SIF_MESTA) a;
go