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

Druga 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.

Druga grupa

*/

use PPortal;
go

-- select bez from klauzule

select 2*8;
go

select GETDATE();
go

select *
	from dbo.POZORISNO_DELO;
go

select *
	from dbo.POZORISTE;
go

select SIF_MESTA
	from dbo.POZORISTE;
go

select distinct SIF_MESTA
	from dbo.POZORISTE;
go

select distinct P_POSTANSKI_BROJ
	from dbo.POZORISTE;
go

select P_POSTANSKI_BROJ
	from dbo.POZORISTE;
go

select distinct P_POSTANSKI_BROJ, SIF_MESTA
	from dbo.POZORISTE;
go

select distinct P_IZ_VOJVODINE, P_POSTANSKI_BROJ, SIF_MESTA
	from dbo.POZORISTE;
go

select distinct P_POSTANSKI_BROJ, SIF_MESTA, P_IZ_VOJVODINE
	from dbo.POZORISTE;
go


select * from POZORISTE;


select P_POSTANSKI_BROJ, SIF_MESTA, P_IZ_VOJVODINE
	from dbo.POZORISTE;
go


select P_POSTANSKI_BROJ as "Поштански број",
	 SIF_MESTA as "Шифра места", 
	 P_IZ_VOJVODINE as "Pozotište je iz Vojvodine:DA/1, NE/0"
	from dbo.POZORISTE;
go

select NAZIV_POZORISTA as "Pozorište",
		len(NAZIV_POZORISTA) as "Dužina sadržaja u polju POZORIŠTE"
	from dbo.POZORISTE;
go

select NAZIV_POZORISTA,
		LEFT(NAZIV_POZORISTA,4) as "prva četiri znaka",
		RIGHT(NAZIV_POZORISTA,4) as "Zadnja četiri znaka"
	from POZORISTE;
go


select OZNAKA_DRZAVE,
		LEFT(OZNAKA_DRZAVE,2) as "Prva dva znaka Oznake države",
		RIGHT(OZNAKA_DRZAVE,3) as "Zadnja tri znaka Oznake države"
	from ZEMLJA;
go

select NAZIV_POZORISTA,
		LEFT(NAZIV_POZORISTA,4) as "prva četiri znaka",
		RIGHT(NAZIV_POZORISTA,4) as "Zadnja četiri znaka",
		SUBSTRING(NAZIV_POZORISTA,1,4) as "prva četiri znaka-Substr",
		SUBSTRING(NAZIV_POZORISTA,len(NAZIV_POZORISTA)-3,4) as 
			"zadnja četiri znaka-Substr",
		SUBSTRING(NAZIV_POZORISTA,97,4) as 
			"Simboli od 97-og do 100-tog znaka-Substr"
	from POZORISTE;
go


select OZNAKA_DRZAVE,
		LEFT(OZNAKA_DRZAVE,2) as "prva dva znaka",
		RIGHT(OZNAKA_DRZAVE,2) as "Zadnja dva znaka",
		SUBSTRING(OZNAKA_DRZAVE,1,2) as "prva dva znaka-Substr",
		SUBSTRING(OZNAKA_DRZAVE,len(OZNAKA_DRZAVE)-1,2) as 
			"zadnja dva znaka-Substr",
		SUBSTRING(OZNAKA_DRZAVE,4,2) as 
			"Simboli od 4-og do 5-tog znaka-Substr"
	from dbo.ZEMLJA;
go

select LEFT(P_POSTANSKI_BROJ,3)
	from dbo.POZORISTE;
go

-- Funkcije za agregaciju: COUNT, SUM, AVG, MIN, MAX

select COUNT(P_MESTO) as "Broj mesta"
	from dbo.POZORISTE;
go

select COUNT(distinct P_MESTO) as "Broj različitih mesta"
	from dbo.POZORISTE;
go

select * from dbo.POZORISTE

select SUM(PREDST_DUZINA_U_MIN) as 
		"Ukupno vreme trajanja svih predstava u minutima"
	from dbo.PREDSTAVA_PROJEKT;
go

select AVG(PREDST_DUZINA_U_MIN) as 
		"Srednje vreme trajanja predstava u minutima"
	from dbo.PREDSTAVA_PROJEKT;
go

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

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

-- Koliko minuta traje najkraća predstava, koliko najduža predstava,
-- koliko je srednje vreme trajanja predstava, koliko ima ukupno 
-- predstava, koliko je ukupno vreme trajanja predstava,
-- odnos uk.vr.traj.predst/uk br predst.

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 "Srednje dužina predstava",
		COUNT(PREDST_DUZINA_U_MIN) as "Uk.br.predst.",
		SUM(PREDST_DUZINA_U_MIN) as "Uk.vr.trajanja predst.",
		SUM(PREDST_DUZINA_U_MIN)/COUNT(PREDST_DUZINA_U_MIN) as
			"odnos uk.vr.predst./uk.vr.predst - Srednja dužina"
	from dbo.PREDSTAVA_PROJEKT;
go

select MIN(distinct P_POSTANSKI_BROJ)
	from dbo.POZORISTE;
go

select MIN(P_POSTANSKI_BROJ)
	from dbo.POZORISTE;
go

select P_POSTANSKI_BROJ
	from dbo.POZORISTE;
go



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

Druga 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.

Druga 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 - rešavanje prvog kolokvijuma na računarima

5. cas - 10. nedelja -28.11.2017.

Druga grupa

*/

-- WHERE klauzula

/*
Listati imena i prezimena lica koja su iz Vojvodine
*/
use PPortal

select L_IME, L_PREZIME
	from dbo.LICE
	where L_IZ_VOJVODINE='true';
go

/*
Listati imena i prezimena lica koja imaju prezimena sa pocetnim
slovom 'B'
*/

select L_IME, L_PREZIME
	from dbo.LICE
	where L_PREZIME like 'B%';
go

select L_IME, L_PREZIME
	from dbo.LICE
	where left(L_PREZIME,1)='B';
go

select L_IME, L_PREZIME
	from dbo.LICE
	where substring(L_PREZIME,1,1)='B';
go

/*
Listati imena i prezimena lica koja imaju prezimena sa pocetnim
slovom 'B', a duzina prezimena ne prelazi 4 znakova
*/

select L_IME, L_PREZIME
	from dbo.LICE
	where substring(L_PREZIME,1,1)='B' and
		LEN(L_PREZIME)<=4;
go

/*
Listati imena i prezimena lica koja imaju prezimena sa pocetnim
slovom 'B', a duzina prezimena ne prelazi 4 znakova ili imaju imena 
duzine do 4 znaka
*/

select L_IME, L_PREZIME
	from dbo.LICE
	where (substring(L_PREZIME,1,1)='B' and
		LEN(L_PREZIME)<=4) or LEN(L_IME)<=4;
go

/*
Listati imena i prezimena lica koja imaju prezimena sa pocetnim
slovom 'B' , a istovremeno duzina prezimena i duzina imena
ne prelazi 4 znaka
*/

select L_IME, L_PREZIME
	from dbo.LICE
	where substring(L_PREZIME,1,1)='B' and
		(LEN(L_PREZIME)<=4 or LEN(L_IME)<=4);
go

/*
Listati nazive mesta i postanske brojeve mesta ako postanski
brojevi spadaju u skup od 11000 do 24000
*/

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


/*
Listati nazive mesta i postanske brojeve mesta ako postanski
brojevi spadaju u skup od 11000 do 24000,  i nazivi mesta pocinju 
slovom 'N', ili ako je duzina naziva mesta ne prelazi 5 znakova.
*/

select NAZIV_MESTA, POST_BROJ
	from dbo.MESTO
	where (POST_BROJ between 11000 and 24000 and 
		LEFT(NAZIV_MESTA,1)='N') or
		LEN(NAZIV_MESTA)<=5;
go

--GRUP BY klauzula

-- agregaciona funkcija bez group by klauzule

/*
koliko ima pozorista u evidenciji
*/
select COUNT(SIF_POZORISTA)
	from dbo.POZORISTE;
go

/*
koliko ima pozorista po mestima u evidenciji
*/

select P_MESTO, COUNT(SIF_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	group by P_MESTO;
go

-- Ne valja, greska!
select SIF_MESTA,P_MESTO,NAZIV_POZORISTA
	from dbo.POZORISTE
	group by SIF_MESTA;
go

/*
koliko ima pozorista po mestima ciji naziv pocinje slovom 'B'
u evidenciji
*/

select P_MESTO, count(NAZIV_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	where LEFT(P_MESTO,1)='B'
	group by P_MESTO;
go

select P_MESTO, count(NAZIV_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	group by P_MESTO
	having LEFT(P_MESTO,1)='B';
go

/*
koliko ima pozorista po mestima ciji naziv pocinje slovom 'B'
*/

select P_MESTO, count(NAZIV_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	group by P_MESTO
	having LEFT(P_MESTO,1)='B';
go


/*
koliko ima pozorista po mestima ciji naziv pocinje slovom 'B', a 
postanski broj ne prelazi 111010
*/
select P_MESTO, count(NAZIV_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	where LEFT(P_MESTO,1)='B' and P_POSTANSKI_BROJ<=11110
	group by P_MESTO;
go

select P_MESTO, count(NAZIV_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	where P_POSTANSKI_BROJ<=11110
	group by P_MESTO
	having LEFT(P_MESTO,1)='B';
go

--Pogreno, ne radi
select P_MESTO, count(NAZIV_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	group by P_MESTO
	having LEFT(P_MESTO,1)='B'  and P_POSTANSKI_BROJ<=11110;
go

/*
koliko ima pozorista po mestima ciji naziv pocinje slovom 'B';
u tabeli rezultata zadrati samo one redove u kojima je broj
pozorista veci od 1
*/

select P_MESTO, count(NAZIV_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	group by P_MESTO
	having LEFT(P_MESTO,1)='B'  and count(NAZIV_POZORISTA)>1;
go

select P_MESTO, count(NAZIV_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	where LEFT(P_MESTO,1)='B'
	group by P_MESTO
	having count(NAZIV_POZORISTA)>1;
go

--Pogresno, ne radi
select P_MESTO, count(NAZIV_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	where count(NAZIV_POZORISTA)>1
	group by P_MESTO
	having LEFT(P_MESTO,1)='B';
go

/*
koliko ima pozorista po mestima i po postanskim brojevima 
ciji naziv pocinje slovom 'B'
*/

select P_MESTO, P_POSTANSKI_BROJ,count(NAZIV_POZORISTA) as "Broj pozorista po mestima"
	from dbo.POZORISTE
	group by P_MESTO,P_POSTANSKI_BROJ;
go 


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

6. cas - 11. nedelja -05.12.2017.

Druga grupa

*/

use PPortal;

/*
1. Koliko ima mesta evidentiranih u bazi podataka?
*/
select COUNT(SIF_MESTA)
	from dbo.MESTO;
go

/*
2. Koliko slova sadrži država na najdužim imenom?
*/
select MAX(len(DRZAVA))
	from dbo.ZEMLJA;
go

/*
3. Koliko ima pozorišta po nazivima mesta?
*/
select P_MESTO, COUNT(SIF_POZORISTA) as "Broj pozorista po nazivima mesta"
	from dbo.POZORISTE
	group by P_MESTO;
go


/*
4. Listati pozorišta van Vojvodine koja nemaju veb adresu, 
	a imaju telefon (TR urediti po nazivu pozorišta
	u obrnutom ABC-dnom redosledu)!
*/
select NAZIV_POZORISTA
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0 and P_WWW_ADRESA is null
		and P_TELEFON is not null
	order by NAZIV_POZORISTA desc;
go

/*
5. Listati pozorišta van Vojvodine koja nemaju veb adresu, 
	a imaju telefon ili im je naziv duži od 10 znakova!
*/
select NAZIV_POZORISTA, P_IZ_VOJVODINE, P_WWW_ADRESA, P_TELEFON, LEN(NAZIV_POZORISTA)
	from dbo.POZORISTE
	where (P_IZ_VOJVODINE=0 and P_WWW_ADRESA is null
		and P_TELEFON is not null) or LEN(NAZIV_POZORISTA)>10;
go

/*
6. Listati pozorišta van Vojvodine koja nemaju veb adresu, 
	ili pozorista koja imaju telefon, a naziv im duži od 10 znakova!
*/
select NAZIV_POZORISTA, P_IZ_VOJVODINE, P_WWW_ADRESA, P_TELEFON, LEN(NAZIV_POZORISTA)
	from dbo.POZORISTE
	where (P_IZ_VOJVODINE=0 and P_WWW_ADRESA is null) or
		(P_TELEFON is not null and LEN(NAZIV_POZORISTA)>10);
go

/*	
7. Koliko ima pozorišta po nazivima mesta? Zadržati samo one redove
	u kojima je broj pozorišta veći od 1!
*/
select P_MESTO, COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	group by P_MESTO
	having COUNT(SIF_POZORISTA)>1;
go

/*	
8. Koliko ima pozorišta sa prvim slovom naziva S po nazivima mesta? 
	Zadržati samo one redove u kojima nazivi mesta počinju slovom B!
*/
select P_MESTO, COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	where LEFT(NAZIV_POZORISTA,1)='S'
	group by P_MESTO
	having left(P_MESTO,1)='B';
go

/*
9. Listati lica sa početnim slovom prezimena J, a da šifra mesta
	(stanovanja) bude jednaka sadržaju polja l_aktivno
*/
select L_IME,L_PREZIME,SIF_MESTA,L_AKTIVNO 
	from dbo.LICE
	where LEFT(L_PREZIME,1)='J' and SIF_MESTA=L_AKTIVNO;
go



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

7. cas - 12. nedelja -12.12.2017.

Druga grupa

*/
use PPortal
go
/*
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()) as "Godina",
		month(getdate()) as "Mesec", 
		day(getdate())as "Dan u mesecu";
go

select datepart(yy,getdate());
go

select month(getdate());
go

/*
Koje premijere su bile pre 5 godina
*/
select * from dbo.PREDSTAVA_PROJEKT;
go

select NASLOV_PREDSTAVE
	from dbo.PREDSTAVA_PROJEKT
	where YEAR(PREDST_DAT_I_VREME_PREMIJERE)=YEAR(getdate())-5;
go


-- VISETABLICNI UPITI

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

/*
3. Koliko ima pozorista po koloni NAZIV_MESTA? 
*/
-- Dekartov proizvod
select NAZIV_MESTA, COUNT(SIF_POZORISTA) as "Broj 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) as "Broj 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) as "Broj pozorista"
	from dbo.POZORISTE
	where P_MESTO='Beograd'
	group by P_MESTO;
go

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

select NAZIV_MESTA, COUNT(SIF_POZORISTA) as "Broj 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) as "Broj 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 LICE.SIF_MESTA=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 LICE.SIF_MESTA=MESTO.SIF_MESTA and
		POST_BROJ>=21000 and POST_BROJ<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 znakova
*/

select L_IME,L_PREZIME 
	from dbo.LICE
	join dbo.MESTO on LICE.SIF_MESTA=MESTO.SIF_MESTA
	where (NAZIV_MESTA='Beograd' or NAZIV_MESTA='Novi Sad') and 
		L_AKTIVNO=1 and L_AMATER='false';
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 LICE.SIF_MESTA=MESTO.SIF_MESTA
	join dbo.ZEMLJA on ZEMLJA.SIF_ZEMLJE=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 LICE.SIF_MESTA=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 ZEMLJA.SIF_ZEMLJE=MESTO.SIF_ZEMLJE
						where DRZAVA='Republika Srbija');
go

/*
7.1. Listati imena i prezimena lica iz Srbije i njihove gradove
*/

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

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

go


/*
7.2. Listati imena i prezimena lica iz Srbije,njihove gradove
	kao i oznaku drzave
*/

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


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




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



/*
10. Koji su nazivi mesta koji imaju postanski broj veci od prosecnog 
postanskog broja? (mesto, pozoriste)
*/


/*
11. Koja lica (imenom i prezimenom) koja nisu autori(prevodioci)?
  ugradjenim SELECT-om ili outer join-om
*/



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