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

Prva grupa

*/
use PPortal;
go

select * from dbo.ZEMLJA;
go

select * from MESTO

/*
Listati sve sifre zemlje iz tabele MESTO
*/

select sif_zemlje
	from MESTO;
go

/*
Koliko ima sifara zemlje u tabeli MESTO
*/
select COUNT(sif_zemlje)
	from MESTO;
go

/*
Listati sve sifre zemlje iz tabele MESTO bez ponavljanja
*/

select distinct sif_zemlje
	from MESTO;
go

/*
Koliko ima sifara zemlje bez ponavljanja  u tabeli MESTO
*/

select COUNT(distinct sif_zemlje)
	from MESTO;
go

/*
Listati sifre mesta i dvostruku vrednost sifre zemlje za ta mesta
*/

select SIF_MESTA, 2*SIF_ZEMLJE
	from MESTO;
go


/*
Listati sifre mesta (sa nazivom kolone u tabeli rezultata Sifra mesta)
i dvostruku vrednost sifre zemlje za ta mesta (sa nazivom kolone u 
tabeli rezultata kao Dvostruka vrednost sifre zemlje)
*/
select SIF_MESTA as "Sifra mesta", 
		2*SIF_ZEMLJE as "Dvostruka vrednost sifre zemlje"
	from MESTO;
go


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

Prva grupa

*/


// Koriscenje agregacionih funkcija u select klauzuli

select * from MESTO

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

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

select COUNT(SIF_MESTA) as "Broj sifrara mesta", 
		COUNT(SIF_ZEMLJE) as "Broj sifara zemlje",
		MIN(POST_BROJ), MAX(POST_BROJ),
		MIN(NAZIV_MESTA), MAX(NAZIV_MESTA)
	from MESTO;
go

/*
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 NAZIV_MESTA, COUNT(SIF_MESTA) as "Broj sifrara mesta", 
		COUNT(SIF_ZEMLJE) as "Broj sifara zemlje",
		MIN(POST_BROJ), MAX(POST_BROJ),
		MIN(NAZIV_MESTA), MAX(NAZIV_MESTA)
	from MESTO
	group by NAZIV_MESTA;
go

select COUNT(SIF_MESTA) as "Broj sifrara mesta", 
		COUNT(SIF_ZEMLJE) as "Broj sifara zemlje",
		MIN(POST_BROJ), MAX(POST_BROJ),
		MIN(NAZIV_MESTA), MAX(NAZIV_MESTA)
	from MESTO
	group by NAZIV_MESTA;
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 
*/

select NAZIV_MESTA, LEN(NAZIV_MESTA) as "duzina u znakovima"
	from MESTO;
go

select NAZIV_MESTA, LEN(NAZIV_MESTA) as "duzina u znakovima",
		LEFT(NAZIV_MESTA,1) as "Prvo slovo-Left",
		SUBSTRING(NAZIV_MESTA,1,1)as "Prvo slovo-Substr",
		RIGHT(NAZIV_MESTA,3) as "Zadnje slovo-Right",
		SUBSTRING(NAZIV_MESTA, LEN(NAZIV_MESTA)-2,3),
		SUBSTRING(NAZIV_MESTA, 4,5)
	from MESTO;
go

/*
Listati naziv predstave, 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) as "Godine-prem",
		month(PREDST_DAT_I_VREME_PREMIJERE) as "Mesec-prem",
		DAY (PREDST_DAT_I_VREME_PREMIJERE) as "Dan-prem",
		CAST(PREDST_DAT_I_VREME_PREMIJERE as time),
		CAST(PREDST_DAT_I_VREME_PREMIJERE as CHAR),
		substring(CAST(PREDST_DAT_I_VREME_PREMIJERE as CHAR),1,2)
	from dbo.PREDSTAVA_PROJEKT;
go



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

Prva grupa

*/

-- WHERE klauzula

use PPortal;
go


/*
Listati sve nazive mesta sa pocetnim slovom 'B' iz tabele Mesto 
*/

select NAZIV_MESTA
	from dbo.MESTO
	where 
--		left(NAZIV_MESTA,1)='B'
--		substring(NAZIV_MESTA,1,1)='B'
		NAZIV_MESTA like 'B%'		
;
go

/*
Listati sve nazive mesta sa pocetnim slovom 'B' 
za koja mesta su postanski brojevi manji od 10000 iz tabele Mesto 
*/

select NAZIV_MESTA
	from dbo.MESTO
	where 
--		left(NAZIV_MESTA,1)='B'
--		substring(NAZIV_MESTA,1,1)='B'
		NAZIV_MESTA like 'B%' and 
--		POST_BROJ<10000
--		POST_BROJ between 1 and 9999
		POST_BROJ=10000 or POST_BROJ<10000						
;
go

select * from MESTO

update MESTO
	set POST_BROJ=215
	where SIF_MESTA=10;
go

select NAZIV_MESTA
	from dbo.MESTO
	where 
--		left(NAZIV_MESTA,1)='B'
--		substring(NAZIV_MESTA,1,1)='B'
		NAZIV_MESTA like 'B%' and 
--		POST_BROJ<10000
--		POST_BROJ between 1 and 9999
		(POST_BROJ=10000 or POST_BROJ<10000)						
;
go

/*
Listati sve postanske brojeve za mesta Subotica i Novi Sad
iz tabele Mesto 
*/

select POST_BROJ
	from MESTO
	where NAZIV_MESTA in ('Subotica','Novi Sad');
go 


select * from ZEMLJA

/*
Listati sve postanske brojeve za mesta sa siframa zemlje koje u svom nazivu
imaju prvo slovo C i R iz tabele Mesto 
*/

select POST_BROJ
	from MESTO
	where SIF_ZEMLJE in 
		(select SIF_ZEMLJE
			from ZEMLJA
			where LEFT(DRZAVA,1)='C' or
					LEFT(DRZAVA,1)='R');
go


-- Group by klauzula

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

select COUNT(SIF_POZORISTA), COUNT(P_WWW_ADRESA)
	from POZORISTE;
go

select * from POZORISTE

update POZORISTE
	set P_WWW_ADRESA = null
	where SIF_POZORISTA=7;
go

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

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

/*
Koliko imamo pozorista po mestima, a unutar mesta po postanskim
brojevima u evidenciji - koristiti tabelu pozoriste
*/
select P_MESTO, P_POSTANSKI_BROJ,COUNT(SIF_POZORISTA) as "Broj sifara pozorista",
		COUNT(P_WWW_ADRESA) as "Broj www adresa pozorista"
	from POZORISTE
	group by P_MESTO,P_POSTANSKI_BROJ ;
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. varijanta resenja 

select P_MESTO, P_POSTANSKI_BROJ,COUNT(SIF_POZORISTA) as "Broj sifara pozorista",
		COUNT(P_WWW_ADRESA) as "Broj www adresa pozorista"
	from POZORISTE
	group by P_MESTO,P_POSTANSKI_BROJ
	having P_MESTO='Beograd' ;
go

-- II. varijanta resenja - bez HAVING-a

select P_MESTO, P_POSTANSKI_BROJ,COUNT(SIF_POZORISTA) as "Broj sifara pozorista",
		COUNT(P_WWW_ADRESA) as "Broj www adresa 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 pozorista veci od broja www adresa iz Beograda
*/

-- I. varijanta resenja 

select P_MESTO, P_POSTANSKI_BROJ,COUNT(SIF_POZORISTA) as "Broj sifara pozorista",
		COUNT(P_WWW_ADRESA) as "Broj www adresa pozorista"
	from POZORISTE
	group by P_MESTO,P_POSTANSKI_BROJ
	having COUNT(SIF_POZORISTA)> COUNT(P_WWW_ADRESA) and 
		P_MESTO='Beograd';
go

-- II. varijanta resenja - bez HAVING-a -- POGRESNO RESENJE!!!

select P_MESTO, P_POSTANSKI_BROJ,COUNT(SIF_POZORISTA) as "Broj sifara pozorista",
		COUNT(P_WWW_ADRESA) as "Broj www adresa pozorista"
	from POZORISTE
	where P_MESTO='Beograd' and COUNT(SIF_POZORISTA)>COUNT(P_WWW_ADRESA)
	group by P_MESTO,P_POSTANSKI_BROJ ;
go



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

Prva grupa

*/

use PPortal

-- ORDER BY klauzula

/*
Listati nazive mesta iz tabele POZORISTE po abecednom (po obrnutom abecednom) 
redosledu (bez ponavljanja naziva mesta).
*/

select P_MESTO
	from dbo.POZORISTE
	order by P_MESTO desc
	
select distinct P_MESTO
	from dbo.POZORISTE
	order by P_MESTO desc
	
/*
Listati nazive mesta iz tabele POZORISTE po abecednom (po obrnutom abecednom) 
redosledu (bez ponavljanja naziva mesta), a posle po postanskim brojevima.
*/

select P_MESTO, P_POSTANSKI_BROJ
	from dbo.POZORISTE
	order by P_MESTO desc, P_POSTANSKI_BROJ desc
	


-- VISETABLICNI UPITI

/*
Listati Nazive drzava i njoj pripadajuca mesta po nazivu
*/

--I. Dekartov proizvod
select DRZAVA, NAZIV_MESTA
	from ZEMLJA z,MESTO m
	where z.SIF_ZEMLJE=m.SIF_ZEMLJE
	
-- JOIN
select DRZAVA, NAZIV_MESTA
	from ZEMLJA z
	join MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE


/*
Izbrojati pozorista po nazivima gradova
*/
--Jednotablicni upit

select P_MESTO, COUNT(SIF_POZORISTA)
	from POZORISTE
	group by P_MESTO
	
-- Dvotablicni upit

select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from POZORISTE
	join MESTO m on m.SIF_MESTA=pozoriste.SIF_MESTA
	group by NAZIV_MESTA
	

/*
Izbrojati lica po nazivima gradova
*/
select NAZIV_MESTA, COUNT(SIF_LICA)
	from LICE l
	join MESTO m on m.SIF_MESTA=l.SIF_MESTA
	group by NAZIV_MESTA
	
/*
Izbrojati lica po nazivima drzave, a unutar toga po nazivima gradova
*/

select DRZAVA, NAZIV_MESTA, COUNT(SIF_LICA)
	from LICE l
	join MESTO m on m.SIF_MESTA=l.SIF_MESTA
	join ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	group by DRZAVA,NAZIV_MESTA
	
/*
Izbrojati lica po nazivima drzave, a unutar toga po nazivima gradova.
Tabela rezultata treba da je uredjena po nazivima zemlje, a posle (unutar toga)
po nazivima mesta.
*/

select DRZAVA, NAZIV_MESTA, COUNT(SIF_LICA)
	from LICE l
	join MESTO m on m.SIF_MESTA=l.SIF_MESTA
	join ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	group by DRZAVA,NAZIV_MESTA
	order by DRZAVA,NAZIV_MESTA
	
/*
Izbrojati lica po nazivima drzave, a unutar toga po nazivima gradova. U tabeli 
rezultata treba da ostanu redovi u kojima je broj mesta veci ili jednak 4.
Tabela rezultata treba urediti po nazivima zemlje, a posle (unutar toga)
po brojevima lica u mestima u opadajucem redosledu.
*/

select DRZAVA, NAZIV_MESTA, COUNT(SIF_LICA) as "Broj lica"
	from LICE l
	join MESTO m on m.SIF_MESTA=l.SIF_MESTA
	join ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	group by DRZAVA,NAZIV_MESTA
	having COUNT(SIF_LICA)>=4
	order by DRZAVA,COUNT(SIF_LICA) desc
	
--*- OUTER JOIN

/*
Listati po nazivu ona mesta iz Srbije u kojima nema pozorista
*/
-- I Ugradjeni SELECT

select SIF_MESTA, NAZIV_MESTA
	from dbo.MESTO m
	join ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	where DRZAVA='Republika Srbija' and 
		m.SIF_MESTA not in (select SIF_MESTA
								from dbo.POZORISTE)


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

Prva grupa

*/


/* Ispisati sifru pozorista i sifru prostorije u kojima ucestvuje
 Branislav sortirano po sifri pozorista u opadajucem redosledu 

Stefan Gasparic, 12117112*/

select p.SIF_POZORISTA, p.SIF_PROSTORIJE
	from dbo.PROSTORIJA p
	join dbo.PREDSTAVA_NA_REPERTOARU pr on pr.SIF_PROSTORIJE=p.SIF_PROSTORIJE
	join dbo.UCESTVUJE_U_PREDSTAVI up on up.NASLOV_PREDSTAVE=pr.NASLOV_PREDSTAVE and
										up.POCETAK_PREDSTAVE=pr.POCETAK_PREDSTAVE and
										up.SIF_POZORISTA=pr.SIF_POZORISTA and
										up.SIF_POZ_DELA=pr.SIF_POZ_DELA and
										up.SIF_PROSTORIJE=pr.SIF_PROSTORIJE
	join dbo.LICE_U_ULOZI_PREDST_PROJ luupp on luupp.NASLOV_PREDSTAVE=up.NASLOV_PREDSTAVE and
										luupp.SIF_LICA=up.SIF_LICA and
										luupp.SIF_POZ_DELA=up.SIF_POZ_DELA and
										luupp.SIF_ULOGE=up.SIF_ULOGE
	join dbo.LICE l on l.SIF_LICA=luupp.SIF_LICA
	where L_IME='Branislav'
	order by p.SIF_POZORISTA desc
	

/* Ispisati NAZIV pozorista i NAZIV prostorije u kojima glumi Branislav 
sortirano po sifri pozorista u opadajucem redosledu 

Stefan Gasparic-IP, 12117112*/

select distinct NAZIV_POZORISTA, NAZIV_PROSTORIJE, L_IME, L_PREZIME
	from dbo.PROSTORIJA p
	join POZORISTE poz on poz.SIF_POZORISTA=p.SIF_POZORISTA
	join dbo.PREDSTAVA_NA_REPERTOARU pr on pr.SIF_PROSTORIJE=p.SIF_PROSTORIJE
	join dbo.UCESTVUJE_U_PREDSTAVI up on up.NASLOV_PREDSTAVE=pr.NASLOV_PREDSTAVE and
										up.POCETAK_PREDSTAVE=pr.POCETAK_PREDSTAVE and
										up.SIF_POZORISTA=pr.SIF_POZORISTA and
										up.SIF_POZ_DELA=pr.SIF_POZ_DELA and
										up.SIF_PROSTORIJE=pr.SIF_PROSTORIJE
	join dbo.LICE_U_ULOZI_PREDST_PROJ luupp on luupp.NASLOV_PREDSTAVE=up.NASLOV_PREDSTAVE and
										luupp.SIF_LICA=up.SIF_LICA and
										luupp.SIF_POZ_DELA=up.SIF_POZ_DELA and
										luupp.SIF_ULOGE=up.SIF_ULOGE
	join dbo.LICE l on l.SIF_LICA=luupp.SIF_LICA
	where L_IME='Branislav'
	--order by p.SIF_POZORISTA desc
	

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

--I. jednotablicni upit

select distinct P_MESTO
	from dbo.POZORISTE
	where P_AMATERSKO=0

select P_MESTO, SIF_POZORISTA, NAZIV_POZORISTA
	from dbo.POZORISTE
	where P_AMATERSKO=0
	order by SIF_POZORISTA desc

select P_MESTO, SIF_POZORISTA, NAZIV_POZORISTA
	from dbo.POZORISTE
	where P_AMATERSKO=0
	order by P_MESTO, SIF_POZORISTA desc





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

Prva 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, m.SIF_ZEMLJE, NAZIV_MESTA, 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 MESTO mu
						where mu.SIF_ZEMLJE=m.SIF_ZEMLJE);
go


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

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


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

-- Resenje - I
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

-- Resenje - II

select DRZAVA
	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




/*
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 m.NAZIV_MESTA, m.POST_BROJ, p.P_MESTO, p.P_POSTANSKI_BROJ
	from MESTO m
	join POZORISTE p on p.P_MESTO=m.NAZIV_MESTA


/*
Izracunati koliko prosecno ima pozorista u mestima 
*/
select AVG(uk_br_poz_po_mestu)
	from (select P_MESTO, convert(decimal,COUNT(SIF_POZORISTA)) as "uk_br_poz_po_mestu"
			from dbo.POZORISTE
			group by P_MESTO) as a