/*
Rad sa Pozorisnim portalom 
1 cas - 6. nedelja -31.10.2017.

Prva grupa

*/
use PPortal;
go

select * from dbo.POZORISTE;
go

select * from dbo.MESTO;
go


/*
Rad sa Pozorisnim portalom 
2 cas - 7. nedelja -07.11.2017.

Prva grupa

*/

use PPortal;
go

-- select bez from klauzule

select 2*8;
go

--svi podaci tabele zanr
select *
	from ZANR;
go

select *
	from POZORISTE;
go

-- podaci iz tabele za izabrane kolone

select NAZIV_POZORISTA, P_MESTO, P_ULICA_BROJ, P_POSTANSKI_BROJ
	from dbo.POZORISTE;
go

-- podaci iz tabele za izabrane kolone sa novim nazivima kolone u R-tabeli

select NAZIV_POZORISTA as "Pozorište",
		P_MESTO as "Sedište", 
		P_ULICA_BROJ as "Adresa",
		P_POSTANSKI_BROJ as "Poštanski broj"
	from dbo.POZORISTE;
go

-- iz kojih mesta su pozorišta evidentirana u BP

select P_MESTO as "Mesta"
	from POZORISTE;
go

-- iz kojih mesta-bez ponavljanja vrednosti 
-- su pozorišta evidentirana u BP

select distinct P_MESTO as "Mesta"
	from POZORISTE;
go

select * from POZORISTE;


select distinct P_MESTO, P_POSTANSKI_BROJ
	from POZORISTE;
go

-- odredjivanje broja znakova u nekom polju

select LEN(NAZIV_POZORISTA) as "Broj karaktera u nazivu pozorišta",
		 LEN(P_ULICA_BROJ) as "Broj znakova u adresi pozorišta"
	from POZORISTE;
go

-- Modifikacija sadržaja polja za R-tabelu

select UPPER(NAZIV_POZORISTA), LOWER(P_MESTO),
		LEFT(P_ULICA_BROJ,5), RIGHT(P_ULICA_BROJ,8)
	from POZORISTE;
go

select SUBSTRING(P_ULICA_BROJ,5,5) as 
		"Delovi adrese poz. od 5-og do 9-og znaka"
	from POZORISTE;
go

select * from ZEMLJA;
go

select SUBSTRING(OZNAKA_DRZAVE,3,3) as 
		"Zadnja tri simbola u oznaci države"
	from dbo.ZEMLJA;
go

select len(OZNAKA_DRZAVE),SUBSTRING(OZNAKA_DRZAVE,len(OZNAKA_DRZAVE)-3,4) as 
		"Zadnja tri simbola u oznaci države"
	from dbo.ZEMLJA;
go

-- Agregacione funkcije

select COUNT(SIF_POZ_DELA) as "Broj redova u tabeli Predstava-Projekat"
	from dbo.PREDSTAVA_PROJEKT;
go

select * from dbo.PREDSTAVA_PROJEKT

select MIN(PREDST_DUZINA_U_MIN) as "Najkraća predstava"
	from dbo.PREDSTAVA_PROJEKT;
go

select Max(PREDST_DUZINA_U_MIN) as "Najduža predstava"
	from dbo.PREDSTAVA_PROJEKT;
go

select AVG(PREDST_DUZINA_U_MIN) as "Srednja dužina predstave"
	from dbo.PREDSTAVA_PROJEKT;
go

select SUM(PREDST_DUZINA_U_MIN) as "Uk.vreme svih predstava"
	from dbo.PREDSTAVA_PROJEKT;
go

-- Kolikao traje najkraća i najduža predstava, koliki je prosek
-- trajanja predstava i koliko ima ukupno predstave

select MIN(PREDST_DUZINA_U_MIN) as "Najkraća predstava",
		MAX(PREDST_DUZINA_U_MIN) as "Najduža predstava",
		AVG(PREDST_DUZINA_U_MIN) as "Prosečna predstava",
		COUNT(PREDST_DUZINA_U_MIN) as "Broj predstava",
		COUNT(PREDST_SADRZAJ) as "Broj/nullable kolone"
	from dbo.PREDSTAVA_PROJEKT;
go

select COUNT(P_POSTANSKI_BROJ) as "Ukupno poštanskih brojeva",
		COUNT(distinct P_POSTANSKI_BROJ) as
			"Ukupno različitih poštanskih brojeva"
	from POZORISTE;
go


/*
Rad sa Pozorisnim portalom 
3. cas - 8. nedelja -14.11.2017.

Prva grupa

*/
--Časovi nisu održani zbog bolesti


/*
Rad sa Pozorisnim portalom - rešavanje prvog kolokvijuma na računarima

4. cas - 9. nedelja -21.11.2017.

Prva grupa

*/


--Rešenje prvog kolokvijuma -10-11-2017 - početak
                       */
/*==============================================================*/


/*
1.	Realizovati bazu podataka RezultatiStudenata sa gornjim
    tabelama na levoj strani modela. Baza podataka ne postoji. 
    Zabraniti bisanje ispitnog roka ako za njega postoji 
    evidentiran rezultat, a izmena vrednosti ključa ispitnog
    roka treba da se propagira u njegovoj tabeli-dete. 
    Brisanje studenta treba da izazove automatsko brisanje 
    njegovih rezultata, a izmena BrIndeksa treba da se dozvoli 
    samo u slučaju ako za njega nije povezana ni jedna ocena. 
    DatUpisaOcene ima predefinisanu/podrazumevanu vrednost 
    aktuelnog datuma, a Ocena ne sme imati vrednost veću od 10.
    DatZavrsIspRoka mora imati veću vrednost od vrednosti polja
    DatPocIspRoka. (4 poena)
*/

use master
drop database RezultatiStudenata;

create database RezultatiStudenata;
go

use RezultatiStudenata;
go

/*==============================================================*/
/* Table: STUDENT                                               */
/*==============================================================*/
create table STUDENT (
   BRINDEKSA            char(6)              not null,
   IMESTUD              varchar(15)          not null,
   PREZIMESTUD          varchar(15)          not null,
   DATRODJSTUD          date             not null,
   constraint SifStud primary key nonclustered (BRINDEKSA)
)
go

-- drop table dbo.ISPITNIROK;

/*==============================================================*/
/* Table: ISPITNIROK                                            */
/*==============================================================*/
create table ISPITNIROK (
   SKOLSKAGOD           char(5)              not null,
   ISPROK               smallint             not null,
   DATPOCISPROKA        datetime             not null,
   DATZAVRSISPROKA      datetime             not null,
   constraint SifIspRoka primary key nonclustered (SKOLSKAGOD, ISPROK),
   constraint uc_KontrPocKrajIspRoka 
	check (datzavrsisproka>datpocisproka)
);
go


/*==============================================================*/
/* Table: STUDIJSKIPROGRAM                                      */
/*==============================================================*/
create table STUDIJSKIPROGRAM (
   SIFSTUDPROGRAMA      smallint             not null,
   NAZIVSTUDPROGRAMA    varchar(50)          not null,
   GODUVODJSTUDPROGR    numeric(4,0)         not null,
   GODVAZENJASTUDPROG   decimal(4,0)         not null,
   constraint SifStPrg primary key nonclustered (SIFSTUDPROGRAMA)
)
go


/*==============================================================*/
/* Table: PREDMET                                               */
/*==============================================================*/
create table PREDMET (
   SIFSTUDPROGRAMA      smallint             not null,
   SIFPREDMETA          int                  not null,
   NAZIVPREDMETA        varchar(30)          not null,
   SEMESTAR             smallint             not null,
   BRCASNASTNEDELJNO    smallint             not null,
   BRCASLABNEDELJNO     smallint             null,
   constraint SifPredm primary key 
		nonclustered (SIFSTUDPROGRAMA, SIFPREDMETA),
   constraint Sadrzi foreign key (SIFSTUDPROGRAMA)
      references STUDIJSKIPROGRAM (SIFSTUDPROGRAMA)
)
go


/*==============================================================*/
/* Table: REZULTAT                                              */
/*==============================================================*/
create table REZULTAT (
   BRINDEKSA            char(6)              not null,
   SKOLSKAGOD           char(5)              not null,
   ISPROK               smallint             not null,
   SIFSTUDPROGRAMA      smallint             not null,
   SIFPREDMETA          int                  not null,
   OCENA                numeric(2,0)         not null
		constraint uc_OgrMaksVrednOcene check (ocena<=10),
   DATUPISAOCENE        datetime             not null 
		default getdate(),
   constraint SifRezultata primary 
		key nonclustered (BRINDEKSA, SKOLSKAGOD, ISPROK, SIFSTUDPROGRAMA, SIFPREDMETA),
   constraint Polaze foreign key (BRINDEKSA)
      references STUDENT (BRINDEKSA)
      on delete cascade on update no action,
   constraint OcenaUIspRoku foreign key (SKOLSKAGOD, ISPROK)
      references ISPITNIROK (SKOLSKAGOD, ISPROK)
      on delete no action on update cascade,
   constraint OcenaZaPredmet foreign key (SIFSTUDPROGRAMA, SIFPREDMETA)
      references PREDMET (SIFSTUDPROGRAMA, SIFPREDMETA)
)
go

/*
2./Ubacite novu alfanumeričku kolonu (polje) dužine 1 sa nazivom 
	Pol u tabelu Student, za koju je podrazumevana vrednost ’M’. 
	Kolona treba da prima sledeće vrednosti: ’M’, ’m’, ’Z’ i ’z’. 
	(1 poen).
*/

--Resenje br.1
alter table dbo.STUDENT
	add Pol char(1) default 'F',
	constraint ck_VrednostiZaPol check (Pol in ('M','m','Z','z'));
go


--Resenje br.2
alter table dbo.STUDENT
	add Pol char(1) default 'F',
	constraint ck_VrednostiZaPol 
	check (Pol='M' or pol='m' or pOl='Z' or poL='z');
go

-- Ne moze se definisati novi default constraint na kolonu
-- koja vec ima default vrednost
alter table dbo.STUDENT
	add constraint DefZaPol
	default 'z' for pol;
go 

/*
3./Modifikujte kolonu DatRodjStud u tabeli student da bude 
	tipa datum-vreme bez obaveze popunjavanja. (1 poen).
*/

alter table dbo.STUDENT
	alter column DATRODJSTUD datetime null;
go

--  alter table ne može imati dve alter column klauzule
alter table dbo.STUDENT
	alter column DATRODJSTUD date null
	alter column imestud varchar(16;
go



/*
4./Dodajte naknadno ograničenje za kolonu Ocena sa nazivom 
	uc_donjagranicaocene: najmanja vrednost kolone Ocena je 5. 
	(1 poen).
*/

alter table dbo.REZULTAT
	add constraint uc_donjagranicaocene
	check (OCENA>=5);
go


/*
5./Modifikujte ograničenje za najveću vrednost kolone Ocena 
	sa 10 na 9. (1 poen).
*/

alter table dbo.REZULTAT
	drop constraint uc_OgrMaksVrednOcene;
go

alter table dbo.REZULTAT
	add constraint uc_OgrMaksVrednOcene check (OCENA <= 9);
go

-- Dodavanje novog ogranicenja za istu kolonu tabele
alter table dbo.REZULTAT
	add constraint uc_MaksOcena check (OCENA <= 10);
go


/*
6./Popunite generisane tabele (one na levoj strani modela) 
	sa vrednostima (po jedan red za svaku tabelu, pri čemu 
	uzeti SifStudPrograma=1 i SifPredmeta=1). 
	Referencijalni integritet ne sme biti oštećen! 
	Pripazite i na to da su tabele možda izmenjene od trenutka 
	njihovog generisanja! (2 poena).
*/

insert dbo.STUDENT
	values ('E-495','Marko','Marković','1994.aug.08','M');
go
delete dbo.STUDENT
select * from dbo.STUDENT


insert dbo.ISPITNIROK
	values ('17/18',1,'2017-10-25','2017-11-03');
go

select * from dbo.ISPITNIROK	

/* sledece dve tabele se ne moraju popunjavati na kolokvijumu-pocetak*/
insert dbo.STUDIJSKIPROGRAM
	values (1,'Studijski program-1',2014,2021);
go

insert dbo.PREDMET
	values (1,1,'Baze podataka',3,2,2);
go

/* sledece dve tabele se ne moraju popunjavati na kolokvijumu-kraj*/

insert dbo.REZULTAT
	values ('E-495','17/18',1,1,1,9,default);
go

select * from dbo.REZULTAT

delete dbo.REZULTAT

insert dbo.REZULTAT (BRINDEKSA,SKOLSKAGOD,ISPROK,SIFSTUDPROGRAMA,
			SIFPREDMETA,OCENA)
	values ('E-495','17/18',1,1,1,9);
go

/*
7./Upišite novi red u tabelu Rezultat sa ocenom 10, i to 
	bez brisanja i/ili modifikacije postojećih ograničenja! 
	Šta je uslov za upis novog reda u tabelu Rezultat? (2 poena)
*/

/* 
	Za upis novog reda u tabelu Rezultat zahteva 
	a) upis novog reda u tabelu Student
	ILI/I
	b) upis novog reda u tabeli Ispitnirok
	ILI/I
	c) upis novog reda u tabelu Predmet
*/

insert dbo.STUDENT
values ('E-494','Milovan','Ilic Minimaks','1938-11-05','M');
go
alter table dbo.REZULTAT
	nocheck constraint uc_OgrMaksVrednOcene;
insert dbo.REZULTAT
	values ('E-494','17/18',1,1,1,10,default);
go	
alter table dbo.REZULTAT
	check constraint uc_OgrMaksVrednOcene;
go

select * from dbo.REZULTAT	

/*
8./Izbrišite redove iz tabele Rezultat koji sadrže vrednost 
	ocene 10! (1 poen)
*/

delete dbo.REZULTAT
	where OCENA=10;
go

/*
9./Izmenite vrednost datuma na aktuelni datum, a ocenu na 10 
	u redovima tabele Rezultat u kojima je vrednost ocene 9! 
	(1 poen)
*/

alter table dbo.REZULTAT
	nocheck constraint uc_OgrMaksVrednOcene;
go

update dbo.REZULTAT
	set DATUPISAOCENE=GETDATE(),
		OCENA=10
	where OCENA=9;
go

alter table dbo.REZULTAT
	check constraint uc_OgrMaksVrednOcene;
	
/*
10./Generišite pogled sa nazivom Pogled_Student sa sledećim 
	poljima (redosled kolona je takođe bitan): Prezime Studenta, 
	Ime Studenta, Broj Indeksa, Datum Rođenja Studenta. (1 poen)
*/

create view Pogled_Student("Prezime Studenta","Ime Studenta",
				"Broj indeksa","Datum Rođenja Studenta") as
	select PREZIMESTUD,IMESTUD,BRINDEKSA,DATRODJSTUD 
		from  dbo.STUDENT;
go

select * from Pogled_Student;

--Rešenje prvog kolokvijuma -10-11-2017 - kraj


/*
Rad sa Pozorisnim portalom

5. cas - 10. nedelja -28.11.2017.

Prva grupa

*/

--Where klauzula

use PPortal;
go

/*
Listati sve nazive mesta sa pocetnim slovom 'B'
*/

select NAZIV_MESTA
	from dbo.MESTO
	where NAZIV_MESTA like 'B%';
go

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

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

/*
Listati sve nazive mesta u kojima je drugo slovo 'u'
*/

select NAZIV_MESTA
	from dbo.MESTO
	where substring(NAZIV_MESTA,2,1)='u';
go

/*
Listati sve nazive mesta koji pocinju slovom 'B',
a postanski broj im je manji od 2000 
*/

select NAZIV_MESTA
	from dbo.MESTO
	where substring(NAZIV_MESTA,1,1)='B' and
		POST_BROJ<2000;
go

/*
Listati sve nazive mesta koji pocinju slovom 'B' ili imaju
postanski broj veci od 20000 
*/

select NAZIV_MESTA
	from dbo.MESTO
	where substring(NAZIV_MESTA,1,1)='B' or
		POST_BROJ>20000;
go

/*
Listati sve nazive mesta za koja su postanski brojevi izmedju
vrednosti (zakljucno sa tim vrednostima) 11000 i 25000 
*/

select NAZIV_MESTA,POST_BROJ
	from dbo.MESTO
	where POST_BROJ between 11000 and 25000;
go

/*
Listati sve nazive mesta koji pocinju slovom 'B',
a postanski brojevi su im izmedju vrednosti (zakljucno sa tim 
vrednostima) 1000 i 5000, ili im je broj znakova u nazivu veci (manji)
ili jednak 8(5)
*/
select NAZIV_MESTA,POST_BROJ
	from dbo.MESTO
	where (left(NAZIV_MESTA,1)='B' and 
	POST_BROJ between 1000 and 5000) or
	LEN(NAZIV_MESTA)>=8;
go

select NAZIV_MESTA,POST_BROJ
	from dbo.MESTO
	where (left(NAZIV_MESTA,1)='B' and 
	POST_BROJ between 1000 and 5000) or
	LEN(NAZIV_MESTA)<=5;
go


-- Group by klauzula

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

select COUNT(SIF_POZORISTA)
	from dbo.POZORISTE;
go

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

select P_MESTO, COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	group by P_MESTO;
go

/*
Koliko imamo amaterskih, a koliko profesionalnih pozorista 
u evidenciji po evidentiranim mestima
- koristiti tabelu pozoriste
*/
select P_MESTO, P_AMATERSKO as "Amat-1,Profes-0", 
		COUNT(SIF_POZORISTA) as "Broj pozorista"
	from dbo.POZORISTE
	group by P_MESTO, P_AMATERSKO;
go

/*
Koliko imamo amaterskih, a koliko profesionalnih pozorista 
iz Vojvodine i izvan nje u evidenciji po evidentiranim mestima
- koristiti tabelu pozoriste
*/

select P_MESTO, P_IZ_VOJVODINE as "Vojvodjansko-1, izvan Vojv-0",
		P_AMATERSKO as "Amat-1,Profes-0", 
		COUNT(SIF_POZORISTA) as "Broj pozorista"
	from dbo.POZORISTE
	group by P_MESTO, P_IZ_VOJVODINE, P_AMATERSKO;
go

--HAVING klauzula

/*
Listati broj pozorista po gradovima izvan Vojvodine za mesta 
ciji nazivi pocinju slovima 'Be'
*/

select P_MESTO, COUNT(SIF_POZORISTA) as "Broj pozorista"
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0 and LEFT(P_MESTO,2)='Be'
	group by P_MESTO;
go

select P_MESTO, COUNT(SIF_POZORISTA) as "Broj pozorista"
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0
	group by P_MESTO
	having LEFT(P_MESTO,2)='Be';
go

-- Ne moze:
select P_MESTO, COUNT(SIF_POZORISTA) as "Broj pozorista"
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0 and COUNT(SIF_POZORISTA)>0
	group by P_MESTO
	having LEFT(P_MESTO,2)='Be';
go


/*
Rad sa Pozorisnim portalom-samostalni rad studenata  na upitima

6. cas - 11. nedelja -05.12.2017.

Prva grupa

*/

--SQL upiti

use PPortal;
go

/*
1. Koliko ima žanrova u evidenciji?
*/
select COUNT(SIF_ZANRA)
	from dbo.ZANR;
go


/*
2. Listati nazive onih država koje imaju oznake države dužine 3!
*/
select DRZAVA
	from dbo.ZEMLJA
	where LEN(OZNAKA_DRZAVE)=3;
go


/*
3. Koliko ima uloga u tabeli uloga koje uloge počinju slovom T?
*/
select COUNT(SIF_ULOGE)
	from dbo.ULOGA
	where LEFT(ULOGA, 1)='T';
go


/*
4. Koje pozorišno delo nema prevodioca?
*/
select NASLOV_POZ_DELA
	from dbo.POZORISNO_DELO
	where SIF_LICA_PREVOD is null;
go

/*

5. Koja su ona pozorišta izvan Vojvodine čiji poštanski broj ne prelazi
	18000, a veći je od 10000 ili adresa (ulica) počinje slovom P?
*/
select NAZIV_POZORISTA, P_IZ_VOJVODINE, P_POSTANSKI_BROJ, P_ULICA_BROJ
	from dbo.POZORISTE
	where (P_IZ_VOJVODINE='false' and P_POSTANSKI_BROJ<=18000
		and P_POSTANSKI_BROJ>10000) or left(P_ULICA_BROJ,1)='P';
go


/*
6. Koliko ima evidentiranih lica po šiframa mesta?
*/
select SIF_MESTA, COUNT(SIF_LICA) as "Broj lica po siframa mesta" 
	from dbo.LICE
	group by SIF_MESTA;
go

/*
7. Koliko ima evidentiranih lica po šiframa mesta? Zadržati samo redove
	u kojima je taj broj veći od 2!
*/
select SIF_MESTA, COUNT(SIF_LICA) as "Broj lica po siframa mesta gde je taj broj veci od 2" 
	from dbo.LICE
	group by SIF_MESTA
	having COUNT(SIF_LICA)>2;
go

/*
8. Koliko ima evidentiranih lica sa početnim slovom imena D 
	po šiframa mesta? Zadržati samo redove u kojia je taj broj veći od 1!
*/
select SIF_MESTA, COUNT(SIF_LICA) as "Broj lica sa pocetnim slovom D po siframa mesta gde je taj broj veci od 1" 
	from dbo.LICE
	where LEFT(L_IME, 1)='D'
	group by SIF_MESTA
	having COUNT(SIF_LICA)>1;
go


/*	
9. Koliko ima evidentiranih lica po šiframa mesta? Zadržati samo redove
	u kojima je šifra mesta manja ili jednaka 3!
*/
select SIF_MESTA, COUNT(SIF_LICA) as "Broj lica po siframa mesta manjim ili jednakim 3" 
	from dbo.LICE
	group by SIF_MESTA
	having COUNT(SIF_LICA)>2;
go
 



/*
Rad sa Pozorisnim portalom-samostalni rad studenata  na upitima

7. cas - 12. nedelja -12.12.2017.

Prva grupa

*/
use PPortal
/*
1. Prikazati trenutni datum i trenutno vreme
*/
select GETDATE();
go


/*
2. Prikazati godinu, mesec i dan danasnjeg datuma (datepart i 
posebnim funkcijama-year, month i day)
*/
select YEAR(getdate());
go
select DATEPART(yy,getdate());
go
select MONTH(getdate());
go
select DATEPART(mm,getdate());
go
select day(getdate());
go
select DATEPART(dd,getdate());
go

-- VISETABLICNI UPITI

/*
3. Koliko ima pozorista po nazivima mesta? - jednotablicni i 
dvotablicni upit (dekartov proizvod i join)
*/
select P_MESTO, COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	group by P_MESTO;
go

--Dekartov proizvod / Cartesian product
select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from dbo.MESTO, dbo.POZORISTE
	where dbo.MESTO.SIF_MESTA=dbo.POZORISTE.SIF_MESTA
	group by NAZIV_MESTA;
go

-- JOIN
select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from dbo.MESTO 
	join dbo.POZORISTE on dbo.MESTO.SIF_MESTA=dbo.POZORISTE.SIF_MESTA
	group by NAZIV_MESTA;
go

/*
4. Koliko ima pozorista u Beogradu? - jednotablicni i 
dvotablicni upit (dekartov proizvod i join)
*/
select P_MESTO,COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	where P_MESTO='Beograd'
	group by P_MESTO;
go

select P_MESTO,COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	group by P_MESTO
	having P_MESTO='Beograd';
go

select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from dbo.MESTO, dbo.POZORISTE
	where dbo.MESTO.SIF_MESTA=dbo.POZORISTE.SIF_MESTA and
	NAZIV_MESTA='Beograd'
	group by NAZIV_MESTA;
go

select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from dbo.MESTO, dbo.POZORISTE
	where dbo.MESTO.SIF_MESTA=dbo.POZORISTE.SIF_MESTA
	group by NAZIV_MESTA
	having NAZIV_MESTA='Beograd';
go

select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from dbo.MESTO 
	join dbo.POZORISTE on dbo.MESTO.SIF_MESTA=dbo.POZORISTE.SIF_MESTA
	where NAZIV_MESTA='Beograd'
	group by NAZIV_MESTA;
go

select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from dbo.MESTO 
	join dbo.POZORISTE on dbo.MESTO.SIF_MESTA=dbo.POZORISTE.SIF_MESTA
	group by NAZIV_MESTA
	having NAZIV_MESTA='Beograd';
go

/*
5. Listati lica iz Novosadaskog okruga (post broj>=21000 i <22000)
cije ime pocinje slovom 'A', koja su aktivna, a bave se profesionalno 
pozoristem
*/
select L_IME, L_PREZIME
	from dbo.LICE,dbo.MESTO
	where dbo.LICE.SIF_MESTA=dbo.MESTO.SIF_MESTA and 
		(POST_BROJ>=21000 and  POST_BROJ<22000) and
		LEFT(L_IME,1)='A' and L_AKTIVNO=1 and L_AMATER='false';
go

select L_IME, L_PREZIME
	from dbo.LICE,dbo.MESTO
	where dbo.LICE.SIF_MESTA=dbo.MESTO.SIF_MESTA and 
		POST_BROJ between 21000 and 21999 and
		LEFT(L_IME,1)='A' and L_AKTIVNO=1 and L_AMATER='false';
go

select L_IME, L_PREZIME
	from dbo.LICE,dbo.MESTO
	where dbo.LICE.SIF_MESTA=dbo.MESTO.SIF_MESTA and 
		POST_BROJ between 21000 and 21999 and
		substring(L_IME,1,1)='A' and L_AKTIVNO='true' and 
		L_AMATER='false';
go

select L_IME, L_PREZIME
	from dbo.LICE
	join dbo.MESTO on dbo.LICE.SIF_MESTA=dbo.MESTO.SIF_MESTA
	where POST_BROJ between 21000 and 21999 and
		LEFT(L_IME,1)='A' and L_AKTIVNO=1 and L_AMATER='false';
go
	
	
	
/*
6. Listati lica iz Novog Sada i Beograda koja su aktivna, bave se 
profesionalno pozoristem a duzina biografije prevazilazi
5(500) znakova
*/
select L_IME, L_PREZIME,L_AMATER, L_AKTIVNO, NAZIV_MESTA,len(L_BIOGRAFIJA)
	from dbo.LICE
	join dbo.MESTO on dbo.LICE.SIF_MESTA=dbo.MESTO.SIF_MESTA
	where (NAZIV_MESTA='Beograd' or  NAZIV_MESTA='Novi Sad') and 
	L_AKTIVNO=1 and L_AMATER=0 and len(L_BIOGRAFIJA)>5;
go



-- Ugradjeni SELECT kao alternativa

/*
7. Listati imena i prezimena lica iz Srbije
*/
select L_IME, L_PREZIME
	from dbo.LICE
	join dbo.MESTO on dbo.LICE.SIF_MESTA=dbo.MESTO.SIF_MESTA
	join dbo.ZEMLJA on dbo.ZEMLJA.SIF_ZEMLJE=dbo.MESTO.SIF_ZEMLJE
	where DRZAVA='Republika Srbija';
go


select L_IME, L_PREZIME
	from dbo.LICE
	where SIF_MESTA in (SELECT SIF_MESTA
						FROM dbo.MESTO
						WHERE SIF_ZEMLJE=(SELECT SIF_ZEMLJE
											FROM dbo.ZEMLJA
											WHERE DRZAVA='Republika Srbija'));
go

select L_IME, L_PREZIME
	from dbo.LICE
	join dbo.MESTO on dbo.LICE.SIF_MESTA=dbo.MESTO.SIF_MESTA
	WHERE SIF_ZEMLJE=(SELECT SIF_ZEMLJE
						FROM dbo.ZEMLJA
						where DRZAVA='Republika Srbija');
go

select L_IME, L_PREZIME
	from dbo.LICE
	WHERE SIF_MESTA in (select SIF_MESTA
						from dbo.MESTO
						join dbo.ZEMLJA on 
							dbo.ZEMLJA.SIF_ZEMLJE=dbo.MESTO.SIF_ZEMLJE
						where DRZAVA='Republika Srbija');
go




/*
Rad sa Pozorisnim portalom-samostalni rad studenata  na upitima

8. cas - 13. nedelja -19.12.2017.

Prva grupa

*/


/*
8. Listati imena i prezimena lica kao i naziv pripadajuceg grada iz 
	Srbije. Sta se moze smestiti u unutrasnji select? 
	Tabelu/tabele iz koje se ne pojavljuje podatak/polje/kolona 
	u tabeli rezultata
*/

select L_IME,L_PREZIME,NAZIV_MESTA
	from dbo.LICE
	join dbo.MESTO on mesto.SIF_MESTA=lice.SIF_MESTA
	where SIF_ZEMLJE=(select SIF_ZEMLJE
						from dbo.ZEMLJA
						where DRZAVA='Republika Srbija');
go


/*
9. Koji je naziv mesta (bez ponavljanja)sa najvecim postanskim brojem? 
	(mesto, pozoriste)
*/

select distinct P_MESTO
	from dbo.POZORISTE
	where P_POSTANSKI_BROJ=(select MAX(P_POSTANSKI_BROJ)
								from dbo.POZORISTE);
go


select  NAZIV_MESTA, POST_BROJ
	from dbo.MESTO
	where POST_BROJ=(select MAX(POST_BROJ)
								from dbo.MESTO);
go


/*
10. Koji su nazivi mesta koji imaju postanski broj veci od prosecnog 
postanskog broja? (mesto, pozoriste)
*/
select distinct P_MESTO
	from dbo.POZORISTE
	where P_POSTANSKI_BROJ>(select AVG(P_POSTANSKI_BROJ)
								from dbo.POZORISTE);
go

select  NAZIV_MESTA, POST_BROJ
	from dbo.MESTO
	where POST_BROJ>(select AVG(POST_BROJ)
								from dbo.MESTO);
go

select AVG(POST_BROJ)
								from dbo.MESTO

/*
11. Koja lica (imenom i prezimenom) koja nisu autori(prevodioci)?
  ugradjenim SELECT-om ili outer join-om
*/
select L_IME,L_PREZIME
	from dbo.LICE
	where SIF_LICA not in (select SIF_LICA
							from dbo.AUTOR);
go

select L_IME,L_PREZIME
	from dbo.LICE
	left join dbo.AUTOR on autor.SIF_LICA=lice.SIF_LICA
	where autor.SIF_LICA is null;
go

/*
12. Izbrojati pozorista po postanskim brojevima. Zadrzari samo one redove
u kojima su postanski brojevi veci od prosecne vrednosti evidentiranih 
postanskih brojeva u bazi podataka.
*/ 

select P_POSTANSKI_BROJ,COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	group by P_POSTANSKI_BROJ
	having P_POSTANSKI_BROJ>(select AVG(P_POSTANSKI_BROJ)
								from dbo.POZORISTE);
go

select P_POSTANSKI_BROJ,COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	where P_POSTANSKI_BROJ>(select AVG(P_POSTANSKI_BROJ)
								from dbo.POZORISTE)
	group by P_POSTANSKI_BROJ;
go



/*
Rad sa Pozorisnim portalom-samostalni rad studenata  na upitima

9. cas - 14. nedelja -26.12.2017.

Prva grupa

*/

/*
12a. Izbrojati postanske brojeve po nazivima mesta. Zadrzati samo one 
	redove u kojima ima (1)vise((2)manje,(3)jednak broj) evidentiranih lica od 
	evidentiranih postanskih brojeva.
*/

-- Modifikovano, ispravno resenje:
-- (1)
select NAZIV_MESTA,COUNT(POST_BROJ) as "Bro.post.brojeva"
	from dbo.MESTO m
	group by NAZIV_MESTA
	having COUNT(POST_BROJ)<(select COUNT(SIF_LICA)
							from dbo.LICE l
							join dbo.MESTO mu on mu.SIF_MESTA=l.SIF_MESTA
							where mu.NAZIV_MESTA=m.NAZIV_MESTA
							group by NAZIV_MESTA);
go


-- (2)
select NAZIV_MESTA,COUNT(POST_BROJ) as "Bro.post.brojeva"
	from dbo.MESTO m
	group by NAZIV_MESTA
	having COUNT(POST_BROJ)>(select COUNT(SIF_LICA)
							from dbo.LICE l
							join dbo.MESTO mu on mu.SIF_MESTA=l.SIF_MESTA
							where mu.NAZIV_MESTA=m.NAZIV_MESTA
							group by NAZIV_MESTA);
go

-- (3)
select NAZIV_MESTA,COUNT(POST_BROJ) as "Bro.post.brojeva"
	from dbo.MESTO m
	group by NAZIV_MESTA
	having COUNT(POST_BROJ)=(select COUNT(SIF_LICA)
							from dbo.LICE l
							join dbo.MESTO mu on mu.SIF_MESTA=l.SIF_MESTA
							where mu.NAZIV_MESTA=m.NAZIV_MESTA
							group by NAZIV_MESTA);
go



select NAZIV_MESTA,COUNT(POST_BROJ) "Broj post.brojeva po mestima"
	from dbo.MESTO
	group by NAZIV_MESTA;

select NAZIV_MESTA, COUNT(SIF_LICA) as "Broj lica po mestima"
	from dbo.MESTO m
	join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
	group by NAZIV_MESTA;

/*12b. Izbrojati postanske brojeve po nazivima mesta. Zadrzati samo one 
	redove u kojima naziv mesta pocinje slovom B.
*/
select NAZIV_MESTA,COUNT(POST_BROJ)
	from dbo.MESTO
	group by NAZIV_MESTA
	having LEFT(NAZIV_MESTA,1)='b';

select NAZIV_MESTA,COUNT(POST_BROJ)
	from dbo.MESTO
	where LEFT(NAZIV_MESTA,1)='b'
	group by NAZIV_MESTA;
	

/* 
13. Koliki je prosecan broj pozorista u gradovima
 - ugradjenim SELECTOM u FROM
*/

-- Resenje br.1
select AVG(A.brpozpogradu)
	from (select P_MESTO, convert(decimal(6,2),COUNT(SIF_POZORISTA)) as brpozpogradu
			from dbo.POZORISTE
			group by P_MESTO) as A;


select P_MESTO, convert(decimal(6,2),COUNT(SIF_POZORISTA)) as brpozpogradu
			from dbo.POZORISTE
			group by P_MESTO
			

-- Resenje br.2
create view pomocnipogled ("Mesto", "BrpozUMestu") as
	select P_MESTO, convert(decimal(6,2),COUNT(SIF_POZORISTA)) as brpozpogradu
		from dbo.POZORISTE
		group by P_MESTO;
go

select AVG(BrpozUMestu)
	from pomocnipogled;
go

drop view pomocnipogled;
go

-- Resenje br.3
select P_MESTO, convert(decimal(6,2),COUNT(SIF_POZORISTA)) as brpozpogradu
	into pomocnatabela
	from dbo.POZORISTE
	group by P_MESTO;
go

select AVG(brpozpogradu)
	from pomocnatabela;
go

drop table pomocnatabela;
go

/*
14. Listati nazive pozorista koji imaju više od 5 znakova
*/
select NAZIV_POZORISTA, LEN(NAZIV_POZORISTA)
	from dbo.POZORISTE
	where LEN(NAZIV_POZORISTA)>5;
/*
15. Koliko ima (različitih) naziva gradova u kojima ima evidentiranih 
	pozorišta? (1-iz tabele pozorište, 2-iz tabele mesto i tabele 
	pozorište)
*/
select COUNT(distinct P_MESTO)
	from POZORISTE;


select COUNT(distinct NAZIV_MESTA)
	from dbo.MESTO
	where SIF_MESTA in (select SIF_MESTA
						from dbo.POZORISTE);


/*
16. Koji su oni gradovi po nazivu koji imaju šifru zemlje 1 ili 4,
	naziv im je duži od 5 znakova, a poštanski broj je veći od
	10000, a manji od 20000?
*/	
select NAZIV_MESTA
	from dbo.MESTO
	where (SIF_ZEMLJE=1 or SIF_ZEMLJE=4) and LEN(NAZIV_MESTA)>5 and
		(POST_BROJ>10000 and POST_BROJ<20000);


/*
17. Izbrojati ona aktivna profesionalna lica izvan Vojvodine, za koja 
	su šifra lica veća od šifre mesta
*/
select COUNT(SIF_LICA)as "Broj lica"
	from dbo.LICE
	where L_AKTIVNO=1 and L_AMATER=0 and L_IZ_VOJVODINE='false' and
		SIF_LICA>SIF_MESTA;

/*
18. Izbrojati ona aktivna profesionalna lica izvan Vojvodine po prvom
	slovu prezimena, za koja su šifra lica veća od šifre mesta
*/
select LEFT(L_PREZIME,1) as "Prvo slovo prezimena", COUNT(SIF_LICA)
	from dbo.LICE
	where L_AKTIVNO=1 and L_AMATER=0 and L_IZ_VOJVODINE='false' and
		SIF_LICA>SIF_MESTA
	group by LEFT(L_PREZIME,1);

/*
19. Izbrojati ona aktivna profesionalna lica izvan Vojvodine po prvom
	slovu prezimena, za koja su šifra lica veća od šifre mesta.
	Zadržati samo one redove u kojima je taj broj veći od 1.
*/
select LEFT(L_PREZIME,1) as "Prvo slovo prezimena", 
		COUNT(SIF_LICA) as "Broj lica"
	from dbo.LICE
	where L_AKTIVNO=1 and L_AMATER=0 and L_IZ_VOJVODINE='false' and
		SIF_LICA>SIF_MESTA
	group by LEFT(L_PREZIME,1)
	having COUNT(SIF_LICA)>1;

/*
20. Izbrojati ona aktivna profesionalna lica izvan Vojvodine po prvom
	slovu prezimena, za koja su šifra lica veća od šifre mesta.
	Zadržati samo one redove u kojima prezimena počinju slovima
	do E po abecedi.
*/

select LEFT(L_PREZIME,1) as "Prvo slovo prezimena", 
		COUNT(SIF_LICA) as "Broj lica"
	from dbo.LICE
	where L_AKTIVNO=1 and L_AMATER=0 and L_IZ_VOJVODINE='false' and
		SIF_LICA>SIF_MESTA
	group by LEFT(L_PREZIME,1)
	having LEFT(L_PREZIME,1) between 'A' and 'E';
	
select LEFT(L_PREZIME,1) as "Prvo slovo prezimena", 
		COUNT(SIF_LICA) as "Broj lica"
	from dbo.LICE
	where L_AKTIVNO=1 and L_AMATER=0 and L_IZ_VOJVODINE='false' and
		SIF_LICA>SIF_MESTA and LEFT(L_PREZIME,1) between 'A' and 'E'
	group by LEFT(L_PREZIME,1);
	
/*
21. Listati sifre mesta, nazive mesta i nazive pozorista koji imaju 
	više od 5 znakova. Urediti tabelu rezultata po siframa mesta (opadajuca
	vrednost) i po abecedi, prvo po nazivima mesta a posle po nazivima 
	pozorista.
*/

select SIF_MESTA, P_MESTO, NAZIV_POZORISTA
	from dbo.POZORISTE
	where LEN(NAZIV_POZORISTA)>5
	order by SIF_MESTA desc, P_MESTO, NAZIV_POZORISTA;
go