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

/*
Listati sve podatke iz tabele ZEMLJA
*/

use PPortal

select * 
	from ZEMLJA;
go

/*
Listati sve podatke iz kolone drzava u tabeli ZEMLJA
*/

select drzava 
	from ZEMLJA;
go

/*
Listati trenutni datum (i vreme)
*/

select GETDATE()+30

/*
Listati sve podatke iz izabranih kolona u tabeli POZORISTE
*/

select NAZIV_POZORISTA, P_MESTO, P_POSTANSKI_BROJ
	from dbo.POZORISTE

/*
Listati sve podatke iz izabranih kolona u tabeli POZORISTE sa izmenjenim
nazivom kolone u tabeli rezultata
*/

select NAZIV_POZORISTA as "Pozorište", P_MESTO, P_POSTANSKI_BROJ
	from dbo.POZORISTE

-- Agregacione funkcije: sum, count, avg, min, max

/*
Koliko ima evidentiranih zemalja u tabeli zemlja
*/

select COUNT(*)
	from zemlja

select * 
	from pozoriste

select COUNT (*)
	from pozoriste

/*
Koliko ima različitih poštanskih brojeva u tabeli pozorište
*/
	
select COUNT (distinct(P_POSTANSKI_BROJ))
	from pozoriste

/*
Koliko ima poštanskih brojeva u tabeli pozorište
*/

select COUNT (P_POSTANSKI_BROJ)
	from pozoriste
	
select *
	from dbo.POZORISNO_DELO

/*
Koliko ima prevodilaca u tabeli pozorišno delo
*/

select COUNT(SIF_LICA_PREVOD)
	from dbo.POZORISNO_DELO

/*
Ubacivanje komentara u tabelu rezultata
*/

select NASLOV_POZ_DELA, POZ_DELO_BR_CINOVA,
		'A onda je to udvostručeno' as "Kolona za objašnjavanje",
		2*POZ_DELO_BR_CINOVA as "Dvostruki broj činova"
	from dbo.POZORISNO_DELO
	
select NASLOV_POZ_DELA, POZ_DELO_BR_CINOVA,
		'A onda je logaritam' as "Kolona za objašnjavanje",
		log(POZ_DELO_BR_CINOVA) as "Dvostruki broj činova"
	from dbo.POZORISNO_DELO


/*
Agregacione funkcije u tabeli rezultata nemaju naziv kolona (samo sistemski dodeljen)
*/

select AVG(POZ_DELO_BR_CINOVA) as "Prosečan broj činova"
	from dbo.POZORISNO_DELO

/*
MSSQL SERVER kao kalkulator
*/
	
select 2*sin(2)

select 2*power(2,10)


/*
Zajedničko korišćenje agregacionih funkcija
*/

select COUNT(SIF_POZORISTA), COUNT(P_MESTO),
		COUNT(distinct(P_MESTO)), COUNT (P_POSTANSKI_BROJ),
		COUNT(distinct P_POSTANSKI_BROJ),
		MIN(P_POSTANSKI_BROJ), MAX(P_POSTANSKI_BROJ)
	from dbo.POZORISTE



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

/*
WHERE klauzula
*/

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

select P_MESTO
	from dbo.POZORISTE
	where P_MESTO='Beograd';
go

select P_MESTO
	from dbo.POZORISTE
	where P_MESTO in ('Beograd');
go

/*
Koliko ima redova sa sadrzajem Beograd u tabeli Pozoriste
*/

select count (P_MESTO)
	from dbo.POZORISTE
	where P_MESTO='Beograd';
go

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

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


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

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

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


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

select * from dbo.POZORISNO_DELO

/*
Listati nazive pozorista sa veb adresom 'www' iz tabele Pozoriste
*/

select NAZIV_POZORISTA
	from dbo.POZORISTE
	where P_WWW_ADRESA like 'www%';
	
select NAZIV_POZORISTA
	from dbo.POZORISTE
	where left(P_WWW_ADRESA,3)='www';
	
select NAZIV_POZORISTA
	from dbo.POZORISTE
	where substring(P_WWW_ADRESA, 1, 3) = 'www';

/*
Listati nazive pozorista iz Vojvodine i budvanska pozorista 
- tabela Pozoriste
*/

select NAZIV_POZORISTA
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=1 or P_MESTO='Budva';

/*
Listati naslove pozorisnih dela koja nemaju prevodioca 
- tabela Pozorisno delo
*/
 select NASLOV_POZ_DELA
	from dbo.POZORISNO_DELO
	where SIF_LICA_PREVOD is null;

/*
Listati nazive pozorista iz ulica koje imaju prvo slovo naziva P
i imaju telefon ili veb adresu - tabela Pozoriste
*/
-- pogresno resenje
select NAZIV_POZORISTA, P_ULICA_BROJ, P_WWW_ADRESA
	from dbo.POZORISTE
	where P_ULICA_BROJ like 'P%' and
		P_TELEFON is not null or
		P_WWW_ADRESA is not null;

--dobro resenje

select NAZIV_POZORISTA, P_ULICA_BROJ, P_WWW_ADRESA
	from dbo.POZORISTE
	where P_ULICA_BROJ like 'P%' and
		(P_TELEFON is not null or
		P_WWW_ADRESA is not null);
		
/*
Listati nazive pozorista iz ulica koje imaju prvo slovo naziva P
i imaju i telefon i veb adresu - tabela Pozoriste
*/

select NAZIV_POZORISTA, P_ULICA_BROJ, P_WWW_ADRESA
	from dbo.POZORISTE
	where P_ULICA_BROJ like 'P%' and
		P_TELEFON is not null and
		P_WWW_ADRESA is not null;
		
/*
Listati nazive pozorista iz Beograda i Novog Sada koja imaju i telefon 
i veb adresu - tabela Pozoriste
*/

select NAZIV_POZORISTA
	from dbo.POZORISTE
	where P_MESTO in ('Beograd', 'Novi Sad')
		and P_TELEFON is not null and
		P_WWW_ADRESA is not null;
		
select NAZIV_POZORISTA
	from dbo.POZORISTE
	where SIF_MESTA in (select SIF_MESTA
							from dbo.MESTO
							where NAZIV_MESTA in ('Beograd', 'Novi Sad'))
		and P_TELEFON is not null and
		P_WWW_ADRESA is not null;
		
		
		
/*
Rad sa Pozorisnim portalom 
3. cas - 9. nedelja -28.11.2019.
*/

/*
GROUP BY klauzula
*/

select * from dbo.POZORISTE

/*
Koliko ima postanskih brojeva u tabeli pozoriste?
*/

select COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli'
	from dbo.POZORISTE;
go

/*
Koliko ima postanskih brojeva i mesta u tabeli pozoriste?
*/

select COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE;
go

/*
Koliko ima jedinstvenih postanskih brojeva i jedinstvenih naziva mesta 
u tabeli pozoriste?
*/

select COUNT(distinct P_POSTANSKI_BROJ) as 'Broj razlicitih postanskih brojeva u tabeli',
		COUNT(distinct P_MESTO) as 'Broj razlicitih naziva mesta u tabeli'
	from dbo.POZORISTE
	group by P_TELEFON;

/*
Koliko ima jedinstvenih postanskih brojeva i jedinstvenih naziva mesta po 
telefonskim brojevima u tabeli pozoriste?
*/
	
select P_TELEFON,
		COUNT(distinct P_POSTANSKI_BROJ) as 'Broj razlicitih postanskih brojeva u tabeli',
		COUNT(distinct P_MESTO) as 'Broj razlicitih naziva mesta u tabeli'
	from dbo.POZORISTE
	group by P_TELEFON;

-- Ovo ne moze da prodje	
select P_TELEFON,
		COUNT(distinct P_POSTANSKI_BROJ) as 'Broj razlicitih postanskih brojeva u tabeli',
		COUNT(distinct P_MESTO) as 'Broj razlicitih naziva mesta u tabeli'
	from dbo.POZORISTE;

/*
Koliko ima jedinstvenih postanskih brojeva i jedinstvenih naziva mesta po 
mestima i postanskim brojevima u tabeli pozoriste?
*/

select P_MESTO, P_POSTANSKI_BROJ,
		COUNT(distinct P_POSTANSKI_BROJ) as 'Broj razlicitih postanskih brojeva u tabeli',
		COUNT(distinct P_MESTO) as 'Broj razlicitih naziva mesta u tabeli'
	from dbo.POZORISTE
	group by P_MESTO,P_POSTANSKI_BROJ;

/*
Koliko ima jedinstvenih postanskih brojeva i jedinstvenih naziva mesta po 
mestima u tabeli pozoriste?
*/

select P_MESTO, 
		COUNT(distinct P_POSTANSKI_BROJ) as 'Broj razlicitih postanskih brojeva u tabeli',
		COUNT(distinct P_MESTO) as 'Broj razlicitih naziva mesta u tabeli'
	from dbo.POZORISTE
	group by P_MESTO;

/*
Koliko ima postanskih brojeva i naziva mesta po 
mestima u tabeli pozoriste?
*/
select P_MESTO, 
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	group by P_MESTO;

/*
Izbrojati postanske brojeve i nazive mesta za pozorista iz Vojvodine
*/

--1
select 	COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	where P_IZ_VOJVODINE='true';

--2
select 	P_IZ_VOJVODINE,
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	where P_IZ_VOJVODINE='true'
	group by P_IZ_VOJVODINE;
	
--3 tu su brojevi postanskih mesta i naziva mesta i za pozorista
--  iz Vojvodine (P_IZ_VOJVODINE=1)i za pozorista van Vojvodine
--  (P_IZ_VOJVODINE=0)
select 	P_IZ_VOJVODINE,
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	group by P_IZ_VOJVODINE;

--4 resenje: resenje 3 + izbor grupe u HAVING-u
select 	P_IZ_VOJVODINE,
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	group by P_IZ_VOJVODINE
	having P_IZ_VOJVODINE='true';

/*
Izbrojati postanske brojeve i nazive mesta za pozorista iz Vojvodine i 
ostaviti samo one redove u kojima je broj mesta veci od 1
*/

--ovako ne ide (redosled izvrsenja klauzula SELECT naredbe!)
select 	P_IZ_VOJVODINE,
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	where P_IZ_VOJVODINE='true' and COUNT(P_MESTO)>1
	group by P_IZ_VOJVODINE;
	
-- ovako je o.k. (redosled izvrsenja klauzula SELECT naredbe!) 
select 	P_IZ_VOJVODINE,
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	where P_IZ_VOJVODINE='true' 
	group by P_IZ_VOJVODINE
	having COUNT(P_MESTO)>1;
	
/*
Koliko ima evidentiranih postanskih brojeva i naziva mesta u Vojvodini
u kojima rade profesionalna pozorista u tabeli pozoriste? 
Zadrzati samo one redove u kojima je broj mesta veci od 1.
*/

--1 filtriranje profesionalnih pozorista iz Vojvodine u where-u
select 	P_IZ_VOJVODINE,
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	where P_IZ_VOJVODINE='true' and  P_AMATERSKO='false'
	group by P_IZ_VOJVODINE
	having COUNT(P_MESTO)>1;

--2 filtriranje profesionalnih pozorista iz Vojvodine u where-u
--  izbor pozorista iz Vojvodine u HAVING-u
select 	P_IZ_VOJVODINE,
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	where P_AMATERSKO='false'
	group by P_IZ_VOJVODINE
	having P_IZ_VOJVODINE='true' and COUNT(P_MESTO)>1;

/*
Koliko ima evidentiranih postanskih brojeva i naziva mesta
u kojima rade profesionalna pozorista po nazivima mesta u tabeli pozoriste? 
*/
 
select 	P_MESTO,
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	where P_AMATERSKO='false'
	group by P_MESTO;


/*

Koliko ima evidentiranih postanskih brojeva i naziva mesta
u kojima rade profesionalna pozorista po nazivima mesta u tabeli pozoriste? 
Zadrzati samo one redove u kojima je broj mesta iznosi (jednak) 1.
*/
 
select 	P_MESTO,
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	where P_AMATERSKO='false'
	group by P_MESTO
	having COUNT(P_MESTO)=1;


select * from POZORISTE

/*
-- ORDER BY klauzula
*/

/*
Prikazati nazive pozorista sa njihovim telefonima po abecednom redosledu
*/

select NAZIV_POZORISTA, P_TELEFON
	from POZORISTE
	order by NAZIV_POZORISTA

/*
Prikazati nazive mesta, nazive pozorista sa njihovim telefonima 
po abecednom redosledu (po nazivu mesta)
*/

select P_MESTO, NAZIV_POZORISTA, P_TELEFON
	from POZORISTE
	order by P_MESTO

/*
Prikazati nazive mesta, nazive pozorista sa njihovim telefonima 
po obrnutom abecednom redosledu (po nazivu mesta)
*/

select P_MESTO, NAZIV_POZORISTA, P_TELEFON
	from POZORISTE
	order by P_MESTO desc

/*
Prikazati nazive mesta, nazive pozorista sa njihovim telefonima iz Vojvodine
po abecednom redosledu (po nazivu mesta)
*/
	
select P_MESTO, NAZIV_POZORISTA, P_TELEFON
	from POZORISTE
	where P_IZ_VOJVODINE=0
	order by P_MESTO

/*
Koliko ima postanskih brojeva i naziva mesta za profesionalna pozorista 
po mestima u tabeli pozoriste? Zadrzati samo redove u kojima je broj mesta 
veci od 1. Urediti tabelu rezultata po nazivima mesta u opadajucen redosledu.
*/
select 	P_MESTO,
		COUNT(P_POSTANSKI_BROJ) as 'Broj postanskih brojeva u tabeli',
		COUNT(P_MESTO) as 'Broj naziva mesta u tabeli'
	from dbo.POZORISTE
	where P_AMATERSKO='false'
	group by P_MESTO
	having COUNT(P_MESTO)>1
	order by P_MESTO desc;


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

/*
VISETABLICNI UPITI
*/

/*
Listati zanrove i pripadajuca pozorisna dela po nazivima
*/
--I
select ZANR, NASLOV_POZ_DELA
	from ZANR, POZORISNO_DELO
	where ZANR.SIF_ZANRA=POZORISNO_DELO.SIF_ZANRA;
go

select * from ZANR;
select * from POZORISNO_DELO;

--II
select ZANR, NASLOV_POZ_DELA 
	from ZANR z, POZORISNO_DELO pd
	where z.SIF_ZANRA=pd.SIF_ZANRA;
go

--III
select ZANR, NASLOV_POZ_DELA 
	from ZANR z
	join POZORISNO_DELO pd on z.SIF_ZANRA=pd.SIF_ZANRA;
go

--IV
select ZANR, NASLOV_POZ_DELA 
	from ZANR
	join POZORISNO_DELO on ZANR.SIF_ZANRA=POZORISNO_DELO.SIF_ZANRA;
go

/*
Listati zanrove, pozorisna dela po naslovima i njihove 
autore po imenima i prezimenima iz baze podataka.
*/
select ZANR, NASLOV_POZ_DELA, L_IME, L_PREZIME 
	from ZANR
	join POZORISNO_DELO pd on ZANR.SIF_ZANRA=pd.SIF_ZANRA
	join AUTOR a on a.SIF_POZ_DELA=pd.SIF_POZ_DELA
	join LICE l on l.SIF_LICA=a.SIF_LICA;
go

/*
Koliko ima pozorisnih dela po zanrovima.
*/

select SIF_ZANRA, count(SIF_POZ_DELA) as 'Broj pozorisnih dela'
	from POZORISNO_DELO
	group by SIF_ZANRA;
go

/*
Koliko ima pozorisnih dela po nazivima zanrova.
*/

select ZANR, count(SIF_POZ_DELA) as 'Broj pozorisnih dela'
	from POZORISNO_DELO pd
	join ZANR z on z.SIF_ZANRA=pd.SIF_ZANRA
	group by ZANR;
go

/*
Koliko ima pozorisnih dela po nazivima zanrova koja dela je Premovic pisao.
*/

select ZANR, count(pd.SIF_POZ_DELA) as 'Broj pozorisnih dela'
	from POZORISNO_DELO pd
	join ZANR z on z.SIF_ZANRA=pd.SIF_ZANRA
	join AUTOR a on a.SIF_POZ_DELA=pd.SIF_POZ_DELA
	join LICE l on l.SIF_LICA=a.SIF_LICA
	where L_PREZIME='Premovic'
	group by ZANR;
go

/*
Koliko ima pozorisnih dela po nazivima zanrova koja dela 
je prevodio Premovic.
*/
select ZANR, count(pd.SIF_POZ_DELA) as 'Broj pozorisnih dela'
	from POZORISNO_DELO pd
	join ZANR z on z.SIF_ZANRA=pd.SIF_ZANRA
	join LICE l on l.SIF_LICA=pd.SIF_LICA_PREVOD
	where L_PREZIME='Premovic'
	group by ZANR;
go

/*
Ko je prevodio koje pozorisno delo?
*/
select  NASLOV_POZ_DELA, L_IME, L_PREZIME
	from POZORISNO_DELO pd
	join LICE l on l.SIF_LICA=pd.SIF_LICA_PREVOD;
	
	
	/*
Ko je prevodio koje pozorisno delo Violinista na krovu?
*/
select  NASLOV_POZ_DELA, L_IME, L_PREZIME
	from POZORISNO_DELO pd
	join LICE l on l.SIF_LICA=pd.SIF_LICA_PREVOD
	where NASLOV_POZ_DELA='Violinista na krovu';
	
/*
Listati sve nazive pozorisnih dela i naznaciti njihove
prevodioce. Ako nema prevodioca, ime i prezime da bude null.
*/
select  NASLOV_POZ_DELA, L_IME, L_PREZIME
	from POZORISNO_DELO pd
	left join LICE l on l.SIF_LICA=pd.SIF_LICA_PREVOD;
	

/*
Listati sve nazive pozorisnih dela koja nemaju prevodioca.
*/
--I
select  NASLOV_POZ_DELA, L_IME, L_PREZIME
	from POZORISNO_DELO pd
	left join LICE l on l.SIF_LICA=pd.SIF_LICA_PREVOD
	where L_IME is null;
	
--II
select  NASLOV_POZ_DELA, L_IME, L_PREZIME
	from LICE l
	right join POZORISNO_DELO pd on l.SIF_LICA=pd.SIF_LICA_PREVOD
	where L_IME is null;

/*
Listati sve nazive pozorisnih dela i sva lica, a u vezi prevodjenja
pozorisnih dela: listati imena i onih koji nisu prevodili ni jedno pd, 
a takodje i ona pozorisna dela koja nemaju prevodioca.
*/
--I
select  NASLOV_POZ_DELA, L_IME, L_PREZIME
	from POZORISNO_DELO pd
	full join LICE l on l.SIF_LICA=pd.SIF_LICA_PREVOD; 
	
	
	
/*
Rad sa Pozorisnim portalom 
5. cas - 11. nedelja -12.12.2019.
*/	


/*
Listati one nazive pozorisnih dela koja nemaju prevodioca i ona lica
koja nisu prevodila ni jedno pozorisno delo.
*/
--I
select  NASLOV_POZ_DELA, L_IME, L_PREZIME
	from POZORISNO_DELO pd
	full join LICE l on l.SIF_LICA=pd.SIF_LICA_PREVOD
	where NASLOV_POZ_DELA is null or L_PREZIME is null; 
	

/*
Listati one nazive pozorisnih dela koja nemaju prevodioca i ona lica
koja nisu prevodila ni jedno pozorisno delo. Tabelu rezultata urediti
po prezimenu po abesednom redosledu
*/
--I
select  NASLOV_POZ_DELA, L_IME, L_PREZIME
	from POZORISNO_DELO pd
	full join LICE l on l.SIF_LICA=pd.SIF_LICA_PREVOD
	where NASLOV_POZ_DELA is null or L_PREZIME is null
	order by L_PREZIME desc; 
	
	
/*
Rad sa Pozorisnim portalom 
5. cas - 11. nedelja -12.12.2019.
*/

/*
VISETABLICNI UPITI
*/


/*
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(SIF_POZORISTA)
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	group by DRZAVA, NAZIV_MESTA
	having COUNT(SIF_POZORISTA)>1
	order by COUNT(SIF_POZORISTA) desc;
go

select * from POZORISTE
select * from MESTO
select * from ZEMLJA
select * from LICE
/*
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(SIF_POZORISTA)
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where DRZAVA<>'Republika Srbija'
	group by DRZAVA, NAZIV_MESTA
	having COUNT(SIF_POZORISTA)>1
	order by COUNT(SIF_POZORISTA) desc;
go

/*
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 0. Urediti TR po rastucim/opadajucim 
vrednostima broja pozorista
*/

select DRZAVA, NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where DRZAVA<>'Republika Srbija'
	group by DRZAVA, NAZIV_MESTA
	having COUNT(SIF_POZORISTA)>0
	order by COUNT(SIF_POZORISTA) desc;
go

select DRZAVA, NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	group by DRZAVA, NAZIV_MESTA
	having DRZAVA<>'Republika Srbija' and COUNT(SIF_POZORISTA)>0
	order by COUNT(SIF_POZORISTA) desc;
go

-- ne ide:
select DRZAVA, NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where COUNT(SIF_POZORISTA)>0
	group by DRZAVA, NAZIV_MESTA
	having DRZAVA<>'Republika Srbija'
	order by COUNT(SIF_POZORISTA) desc;
go


/* 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 */

select L_IME, L_PREZIME, NAZIV_MESTA
	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='false' 
		and LEN(L_BIOGRAFIJA)<=7;
go


select L_IME, L_PREZIME
	from dbo.LICE 
	where L_AKTIVNO='true' and L_AMATER='false' 
		and LEN(L_BIOGRAFIJA)<=100
		and SIF_MESTA in (select SIF_MESTA
				from MESTO
				where NAZIV_MESTA='Budimpesta' or NAZIV_MESTA='Beograd');
go


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

select L_IME, L_PREZIME, NAZIV_MESTA
	from dbo.LICE l 
	join dbo.MESTO m on m.SIF_MESTA=l.SIF_MESTA
	where NAZIV_MESTA<>'Budimpesta' and NAZIV_MESTA<>'Beograd'
		and L_AKTIVNO='true' and L_AMATER='false' 
		and LEN(L_BIOGRAFIJA)<=100;
go


select L_IME, L_PREZIME
	from dbo.LICE 
	where L_AKTIVNO='true' and L_AMATER='false' 
		and LEN(L_BIOGRAFIJA)<=100
		and SIF_MESTA in (select SIF_MESTA
				from MESTO
				where NAZIV_MESTA<>'Budimpesta' and NAZIV_MESTA<>'Beograd');
go

/*
Listati nazive mesta u kojima nema (ne stanuju)evidentirana lica
*/

select NAZIV_MESTA, L_IME, L_PREZIME
	from dbo.LICE l
	right join dbo.MESTO m on m.SIF_MESTA=l.SIF_MESTA
	where L_IME is null;
go

select NAZIV_MESTA, L_IME, L_PREZIME
	from dbo.MESTO m
	left join  dbo.LICE l on m.SIF_MESTA=l.SIF_MESTA
	where L_IME is null;
go

select NAZIV_MESTA
	from MESTO
	where SIF_MESTA not in (select SIF_MESTA
								from LICE);
go


/* 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
select pp.SIF_POZ_DELA, ULOGA, len(ULOGA), NASLOV_POZ_DELA
	from dbo.PREDSTAVA_PROJEKT pp
	join dbo.PREDST_PROJ_ULOGA ppu on
				ppu.SIF_POZ_DELA=pp.SIF_POZ_DELA and
				ppu.NASLOV_PREDSTAVE=pp.NASLOV_PREDSTAVE
	join dbo.POZORISNO_DELO pd on pd.SIF_POZ_DELA=pp.SIF_POZ_DELA
	join ULOGA u on u.SIF_ULOGE=ppu.SIF_ULOGE
--
	where ULOGA='Reditelj' and NASLOV_POZ_DELA='Indigo';
	
	
/*
Rad sa Pozorisnim portalom 
6. cas - 12. nedelja -19.12.2019.
*/

/*
VISETABLICNI UPITI
*/
select * from mesto
select * from POZORISTE

/*
Listati nazive pozorista, nazive mesta i postanske brojeve iz tabele pozoriste
za ona pozorista koja imaju najveci ptt broj u mestu u kojem funkcionisu
*/

select NAZIV_POZORISTA, P_MESTO, P_POSTANSKI_BROJ
	from dbo.POZORISTE psp
	where P_POSTANSKI_BROJ=(select MAX(P_POSTANSKI_BROJ)
								from dbo.POZORISTE pun
								where pun.P_MESTO=psp.P_MESTO);

/* 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ć*/

--II
select SIF_POZ_DELA, NASLOV_POZ_DELA -- , ULOGA, len(ULOGA)
	from dbo.POZORISNO_DELO
	where NASLOV_POZ_DELA='Indigo' and
		SIF_POZ_DELA in (select SIF_POZ_DELA
	                        from dbo.PREDSTAVA_PROJEKT
	                        where SIF_POZ_DELA in (select SIF_POZ_DELA
													from dbo.PREDST_PROJ_ULOGA 
													where SIF_ULOGE=(select SIF_ULOGE
																		from dbo.ULOGA
																		where ULOGA='Reditelj')
								and NASLOV_PREDSTAVE in (select NASLOV_PREDSTAVE
														from dbo.PREDST_PROJ_ULOGA  												
														where SIF_ULOGE=(select SIF_ULOGE
																		from dbo.ULOGA
																		where ULOGA='Reditelj'))))
	
	
/* Ispisati sve nazive mesta gde nema amaterskih pozorista gde je 
sif pozorista sortirana u opadajucem nizu 
Luka Patarcic  12118201*/

select 	NAZIV_MESTA--, SIF_POZORISTA
	from dbo.MESTO
	where SIF_MESTA not in (select SIF_MESTA
							from dbo.POZORISTE
							where P_AMATERSKO=1);

select 	NAZIV_MESTA, SIF_POZORISTA, P_AMATERSKO
	from dbo.MESTO m
	left join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where P_AMATERSKO=0 or P_AMATERSKO is null
	order by SIF_POZORISTA;						
							
							
/* Ispisati sve nazive mesta gde uopste nema pozorista (nema ni
amaterskih , a nema ni profesionalnih pozorista)
Luka Patarcic  12118201*/

--I
select 	NAZIV_MESTA--, SIF_POZORISTA
	from dbo.MESTO
	where SIF_MESTA not in (select SIF_MESTA
							from dbo.POZORISTE
							where P_AMATERSKO=1)
		and
		SIF_MESTA not in (select SIF_MESTA
							from dbo.POZORISTE
							where P_AMATERSKO=0);
--II
select 	NAZIV_MESTA
	from dbo.MESTO
	where SIF_MESTA not in (select SIF_MESTA
							from dbo.POZORISTE);
		
/*
Listati nazive onih predstava koje imaju broj cinova jednak 
maksimalnom broju cinova iz tabele Predstava_projekat
*/

select NASLOV_PREDSTAVE, PREDST_DUZINA_U_MIN
	from dbo.PREDSTAVA_PROJEKT
	where PREDST_DUZINA_U_MIN = (select MAX(PREDST_DUZINA_U_MIN)
									from dbo.PREDSTAVA_PROJEKT);

/*
Rad sa Pozorisnim portalom 
7. cas - 13. nedelja -16.01.2020.
*/

/*
Listati nazive mesta koja imaju postanski broj veci od 22000
*/
select NAZIV_MESTA, POST_BROJ
	from MESTO
	where POST_BROJ>22000;

-- ne moze
select NAZIV_MESTA, POST_BROJ
	from MESTO
	having POST_BROJ>22000;

select NAZIV_MESTA, count(POST_BROJ)
	from MESTO
	--where POST_BROJ>22000
	group by NAZIV_MESTA
	having LEFT(NAZIV_MESTA,1)='B';
	
select NAZIV_MESTA, count(POST_BROJ)
	from MESTO
	where LEFT(NAZIV_MESTA,1)='B'
	group by NAZIV_MESTA;

select NAZIV_MESTA, count(POST_BROJ)
	from MESTO
	--where LEFT(NAZIV_MESTA,1)='B'
	group by NAZIV_MESTA
	having count(POST_BROJ)>0;	

select NAZIV_MESTA, count(POST_BROJ)
	from MESTO
	where count(POST_BROJ)>0
	group by NAZIV_MESTA;	

/*
Listati ona mesta koja imaju postanske brojeve vece od srednjeg postanskog
broja u tabeli Mesto
*/
select NAZIV_MESTA, POST_BROJ
	from MESTO
	where POST_BROJ>(select avg(POST_BROJ)
						from dbo.MESTO);

select avg(POST_BROJ)
						from dbo.MESTO

-- Ugradjeni SELECT u FROM klauzuli

/*
Izracunati koliko prosecno ima pozorista u mestima 
*/
select * from POZORISTE

select AVG(BrPoz)
	from (select P_MESTO, convert(numeric,COUNT(SIF_MESTA)) as "BrPoz"
			from POZORISTE
			group by P_MESTO) a;
			
select AVG(BrPoz)
	from (select convert(numeric,COUNT(SIF_MESTA)) as "BrPoz"
			from POZORISTE
			group by P_MESTO) a;

select P_MESTO, convert(numeric,COUNT(SIF_MESTA)) as "BrPoz"
			from POZORISTE
			group by P_MESTO
use PPortal
		
		select convert(numeric,COUNT(SIF_MESTA)) as "BrPoz"
			from POZORISTE
			--group by P_MESTO

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

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

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

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

/*
Upit se odnosi na BP PPortal16012020
Koliko ima pozorišnih dela po žanrovima (Zanr) u evidenciji, 
koja pozorišna dela imaju tacno jednog (dva) prevodioca?
*/

use PPortal16012020;

select ZANR, count(pd.SIF_POZ_DELA)
	from ZANR z
	join POZORISNO_DELO pd on pd.SIF_ZANRA=z.SIF_ZANRA
	join (select pdu.SIF_POZ_DELA,COUNT(SIF_LICA) as "A"
			from dbo.POZORISNO_DELO pdu
			join  dbo.PREVODILAC p on p.SIF_POZ_DELA=pdu.SIF_POZ_DELA
			group by pdu.SIF_POZ_DELA
			having COUNT(SIF_LICA)=2) pd1p on pd1p.SIF_POZ_DELA=pd.SIF_POZ_DELA
	group by ZANR;




select ZANR, COUNT(pd.SIF_POZ_DELA)
	from ZANR z 
	join POZORISNO_DELO pd on pd.SIF_ZANRA=z.SIF_ZANRA
	join (select sif_poz_dela, count(sif_lica) as "Broj_prevodilaca"
			from PREVODILAC
			group by SIF_POZ_DELA
			having count(sif_lica)=1) a on a.SIF_POZ_DELA=pd.SIF_POZ_DELA
	group by ZANR
go

select ZANR, COUNT(pd.SIF_POZ_DELA)
	from ZANR z 
	join POZORISNO_DELO pd on pd.SIF_ZANRA=z.SIF_ZANRA
	join (select sif_poz_dela, count(sif_lica) as "Broj_prevodilaca"
			from PREVODILAC
			group by SIF_POZ_DELA
			having count(sif_lica)=2) a on a.SIF_POZ_DELA=pd.SIF_POZ_DELA
	group by ZANR
go