/*
Rad sa Pozorisnim portalom 
1 cas - 8. nedelja -23.11.2016.

Prva grupa

*/

use PPortal;
go

sp_helpdb PPortal;
go
sp_help ;
go

select * from dbo.LICE;
go

select * from dbo.PREDSTAVA_PROJEKT;
go

select * from dbo.PREDSTAVA_NA_REPERTOARU;
go



/*
Rad sa Pozorisnim portalom 
2 cas - 9. nedelja - 30.11.2016.

Prva grupa

*/

use PPortal;
go

/*
Listati podatke o drzavama iz tabele Zemlja sa zaglavljem
"Naziv drzave", "Oznaka drzave", "Sifra u tabeli Zemlja"

*/
select *
	from dbo.ZEMLJA;
go

select drzava as "Naziv drzave", OZNAKA_DRZAVE as "Oznaka drzave",
		SIF_ZEMLJE as "Sifra u tabeli Zemlja"
	from dbo.ZEMLJA;
go 

/* !!!
Listati podatke o drzavama iz tabele Zemlja sa zaglavljem
Naziv drzave, Objasnjenje, Oznaka drzave, Sifra u tabeli Zemlja,
a objasnjenje treba da ima sadrzaj "ima oznaku"
*/

select drzava as "Naziv drzave", 'ima oznaku' as "Objasnjenje",
		OZNAKA_DRZAVE as "Oznaka drzave",
		SIF_ZEMLJE as "Sifra u tabeli Zemlja"
	from dbo.ZEMLJA;
go 

/*
Koliko imamo evidentiranih drzava?
*/

select COUNT(SIF_ZEMLJE) as "Broj drzava u tabeli Zemlja"
	from dbo.ZEMLJA;
	go
	
insert ZEMLJA
	values(5,'Mongolija',null);
go
select * from ZEMLJA;
go

select COUNT(OZNAKA_DRZAVE) as "Broj drzava u tabeli Zemlja"
	from dbo.ZEMLJA;
	go



/*
Koliko imamo evidentiranih drzava ciji su nazivi duzi od 12 znakova?
*/

select COUNT(SIF_ZEMLJE) as "Broj drzava u tabeli Zemlja"
	from dbo.ZEMLJA
	where LEN(DRZAVA)>12;
	go


	
	
/*
Koje su one drzave ciji su nazivi duzi od 12 znakova?
*/
select DRZAVA as "Naziv drzave sa brojem znakova vecim od 12"
	from dbo.ZEMLJA
	where LEN(DRZAVA)>12;
	go
	

/*
Koje su one drzave ciji su nazivi duzine od 8 do 12 znakova?
*/

select DRZAVA as "Naziv drzave sa brojem znakova od 8 do 12"
	from dbo.ZEMLJA
	where LEN(DRZAVA) between 8 and 12;
	go

select DRZAVA as "Naziv drzave sa brojem znakova od 8 do 12"
	from dbo.ZEMLJA
	where LEN(DRZAVA)>7 and  LEN(DRZAVA)<13;
	go

/*
Listati nazive drzava velikim slovima, danasnji datum i samo
pocetna dva slova naziva drzave
*/
select  upper(DRZAVA) as "Naziv drzave velikim slovima",
		GETDATE() as "Datum i vreme",
		SUBSTRING(DRZAVA,1,2) as "Prva dva slova naziva drzave"
	from dbo.ZEMLJA;
go

/*
select  upper(DRZAVA) as "Naziv drzave velikim slovima",
		substring(str(getdate(),1,10)) as "Datum",
		SUBSTRING(DRZAVA,1,2) as "Prva dva slova naziva drzave"
	from dbo.ZEMLJA;
go
*/

/*
Koriscenjem funkcije left resiti prethodni zadatak
Listati nazive drzava velikim slovima, danasnji datum i samo
pocetna dva slova naziva drzave
*/

select  upper(DRZAVA) as "Naziv drzave velikim slovima",
		GETDATE() as "Datum i vreme",
		left(DRZAVA,2) as "Prva dva slova naziva drzave"
	from dbo.ZEMLJA;
go


/*
Primer za ilustraciju sadrzaja char u varchar tipa polja
*/
select  upper(DRZAVA) as "Naziv drzave velikim slovima",
		GETDATE() as "Datum i vreme",
		right(DRZAVA,2) as "Zadnja dva slova naziva drzave",
		RIGHT(OZNAKA_DRZAVE,3) as "Zadnja tri znaka oznake" 
	from dbo.ZEMLJA;
go

select * from POZORISTE;
go

/*
U koliko (razlicitih) gradova su evidentirana pozorista? 
*/

select COUNT(P_MESTO)
	from POZORISTE;
go

select COUNT(distinct P_MESTO)
	from POZORISTE;
go

/*
Izbrojati razlicite nazive gradova pozorista koji pocinju slovom B
- left, substring, like
*/

select COUNT(distinct P_MESTO)
	from POZORISTE
	where LEFT(P_MESTO,1)='B';
go

select COUNT(distinct P_MESTO)
	from POZORISTE
	where substring(P_MESTO,1,1)='B';
go

select COUNT(distinct P_MESTO)
	from POZORISTE
	where P_MESTO like 'B%';
go

/*
Izbrojati razlicite nazive gradova pozorista koji pocinju slovom B, 
a ukupan broj znakova je 5
*/
select COUNT(distinct P_MESTO)
	from POZORISTE
	where P_MESTO like 'B____';
go

select COUNT(distinct P_MESTO)
	from POZORISTE
	where substring(P_MESTO,1,1)='B' and
			LEN(P_MESTO)=5;
go

select COUNT(distinct P_MESTO)
	from POZORISTE
	where LEFT(P_MESTO,1)='B' and
			LEN(P_MESTO)=5;
go

/*
Listati nazive gradova pozorista koji se nalaze na sledecoj
listi (Subotica, Beograd, Sombor, Novi Sad)
*/

select distinct P_MESTO
	from dbo.POZORISTE
	where P_MESTO in ('Subotica', 'Beograd', 'Sombor', 'NOVi Sad');
go

/*
select distinct(), date(), left(), upper(), lower()
	substring(), sum(distinct imepolja),
where between, in, like, is null, is not null
group by	
*/

/*
Rad sa Pozorisnim portalom 
3 cas - 10. nedelja - 07.12.2016.

Prva grupa

*/

use PPortal;
go

/*
GROUP BY klauzula
*/

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

select COUNT(SIF_POZORISTA)
	from dbo.POZORISTE;
	go

/*
Koliko imamo pozorista u pojedinim gradovima
- koristiti tabelu pozoriste
*/

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

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

/*	
Koliko imamo pozorista u pojedinim gradovima van Vojvodine
- koristiti tabelu pozoriste
*/

select P_MESTO, COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0
	group by P_MESTO;
	go

select P_MESTO, COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	where P_IZ_VOJVODINE = 'fAlSe'
	group by P_MESTO;
	go


select P_MESTO, COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	where P_POSTANSKI_BROJ not between 21000 and 29999
	group by P_MESTO;
	go
	
select * from dbo.POZORISTE
go

select COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0
	group by P_MESTO;
	go


/*
Grupisanje je moguce sprovoditi i bez navodjenja osnove grupisanja
u Select klauzuli, ali odna ona u tabeli rezultata ne figurise
i tabela rezultata nece biti razumljiva, tj. za sta se odnose
rezultati agregacione funkcije
*/

select COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0
	group by P_MESTO;
	go
	
select * from dbo.POZORISTE;
go

/*	
Koliko imamo pozorista u pojedinim gradovima van Vojvodine,
a koji je najmanji i najveci postanski broj evidentiran u Vojvodini
- koristiti tabelu pozoriste
*/

select COUNT(SIF_POZORISTA),
		min(P_POSTANSKI_BROJ),
		max(P_POSTANSKI_BROJ)		
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0;
	go
select * from dbo.POZORISTE;

/*	
Koliko imamo pozorista u pojedinim gradovima van Vojvodine,
a koji je najmanji i najveci postanski broj-naveden u adresama
za pojedina pozorista- u tim gradovima
- koristiti tabelu pozoriste
*/

select COUNT(SIF_POZORISTA) as "Broj pozorista",
		min(P_POSTANSKI_BROJ) as "Najmanji postanski broj",
		max(P_POSTANSKI_BROJ) as "Najveci postanski broj"		
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0
	group by P_MESTO;
	go
	

select P_MESTO,
		COUNT(SIF_POZORISTA) as "Broj pozorista",
		min(P_POSTANSKI_BROJ) as "Najmanji postanski broj",
		max(P_POSTANSKI_BROJ) as "Najveci postanski broj" 		
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0
	group by P_MESTO;
	go
	
--
--
-- HAVING klauzula
--
--

/*	
Koliko imamo pozorista u pojedinim gradovima van Vojvodine,
a koji je najmanji i najveci postanski broj-naveden u adresama
za pojedina pozorista- u tim gradovima.
Zadrzati samo one redove za koje p_mesto pocinje sa 'Be'
- koristiti tabelu pozoriste
*/

select P_MESTO,
		COUNT(SIF_POZORISTA) as "Broj pozorista",
		min(P_POSTANSKI_BROJ) as "Najmanji postanski broj",
		max(P_POSTANSKI_BROJ) as "Najveci postanski broj" 		
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0
	group by P_MESTO
	having P_MESTO like 'Be%';
	go

/*	
Koliko imamo pozorista u pojedinim gradovima van Vojvodine,
a koji je najmanji i najveci postanski broj-naveden u adresama
za pojedina pozorista- u tim gradovima.
Zadrzati samo one redove za koje je broj pozorista veci od 1
- koristiti tabelu pozoriste
*/

select P_MESTO,
		COUNT(SIF_POZORISTA) as "Broj pozorista",
		min(P_POSTANSKI_BROJ) as "Najmanji postanski broj",
		max(P_POSTANSKI_BROJ) as "Najveci postanski broj" 		
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0
	group by P_MESTO
	having COUNT(SIF_POZORISTA)>1;
	go

/*	
Koliko imamo pozorista u pojedinim gradovima van Vojvodine,
a koji je najmanji i najveci postanski broj-naveden u adresama
za pojedina pozorista- u tim gradovima.
Zadrzati samo one redove za koje vazi da je najmanji postanski broj
jednak najvecem postanskom broju
- koristiti tabelu pozoriste
*/

select P_MESTO,
		COUNT(SIF_POZORISTA) as "Broj pozorista",
		min(P_POSTANSKI_BROJ) as "Najmanji postanski broj",
		max(P_POSTANSKI_BROJ) as "Najveci postanski broj" 		
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0
	group by P_MESTO
	having min(P_POSTANSKI_BROJ)=max(P_POSTANSKI_BROJ);
	go

/*	
Listati nazive gradova iz evidencije za koje gradove u tabeli
pozoriste najmanji postanski broj
jednak najvecem postanskom broju
- koristiti tabelu pozoriste
*/

select P_MESTO,
		min(P_POSTANSKI_BROJ) as "Najmanji postanski broj",
		max(P_POSTANSKI_BROJ) as "Najveci postanski broj" 		
	from dbo.POZORISTE
	group by P_MESTO
	having min(P_POSTANSKI_BROJ)=max(P_POSTANSKI_BROJ);
	go

select P_MESTO as "Gradovi kod kojih min.post.broj=maks.post.broju"	
	from dbo.POZORISTE
	group by P_MESTO
	having min(P_POSTANSKI_BROJ)=max(P_POSTANSKI_BROJ);
	go

select P_MESTO as 'Gradovi kod kojih min.post.broj=maks.post.broju',
		'Naziv kolone' as "Naziv kolone"	
	from dbo.POZORISTE
	group by P_MESTO
	having min(P_POSTANSKI_BROJ)=max(P_POSTANSKI_BROJ);
	go
	
--
--
-- where kontra having
--
--

/*
Uslov za where semsten u having - NE RADI, NE MOZE SE PREMESTITI
Listati nazive gradova izvan Vojvodine
*/

select P_MESTO
	from dbo.POZORISTE
	where P_IZ_VOJVODINE=0;
go

select P_MESTO
	from dbo.POZORISTE
	having P_IZ_VOJVODINE=0;
go

select P_MESTO
	from dbo.POZORISTE
	having P_IZ_VOJVODINE=0;
go

/*
Uslov za having semsten u where - NE RADI, NE MOZE SE PREMESTITI
*/

/*	
Listati nazive gradova iz evidencije za koje gradove u tabeli
pozoriste najmanji postanski broj
jednak najvecem postanskom broju
- koristiti tabelu pozoriste
*/

select P_MESTO as "Gradovi kod kojih min.post.broj=maks.post.broju"	
	from dbo.POZORISTE
	where min(P_POSTANSKI_BROJ)=max(P_POSTANSKI_BROJ)
	group by P_MESTO;
	go
	
select P_MESTO as "Gradovi kod kojih min.post.broj=maks.post.broju"	
	from dbo.POZORISTE
	where min(P_POSTANSKI_BROJ)=max(P_POSTANSKI_BROJ);
	go
	
--
--
-- ORDER BY klauzula
--
--

/*
Listati nazive gradova iz evidencije u obrnutom abecednom redosledu
*/

select P_MESTO
	from dbo.POZORISTE
	order by P_MESTO desc;
go

/*
Listati nazive gradova bez ponavljanja
iz evidencije u obrnutom abecednom redosledu
*/

select distinct P_MESTO
	from dbo.POZORISTE
	order by P_MESTO desc;
go

/*
Listati nazive gradova
iz evidencije uredjenih po postanskim brojevima u obrnutom redosledu
*/

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

/*
Listati nazive gradova bez ponavljanja
iz evidencije uredjenih po postanskim brojevima u obrnutom redosledu
*/

select distinct P_MESTO, P_POSTANSKI_BROJ
	from dbo.POZORISTE
	order by P_POSTANSKI_BROJ desc;
go


select distinct P_MESTO
	from dbo.POZORISTE
	order by P_POSTANSKI_BROJ desc;
go

/*
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list 
if SELECT DISTINCT is specified.
*/


/*
Rad sa Pozorisnim portalom 
4 cas - 11. nedelja - 14.12.2016.

Prva grupa

*/

use PPortal;
go



/*Prikazati trenutni datum i trenutno vreme  */

select GETDATE()
go



SELECT
substring(cast(CURRENT_TIMESTAMP as character), 1, 11) AS "Date",
substring(cast(CURRENT_TIMESTAMP as character), 12,12) AS "Time",
CURRENT_TIMESTAMP AS "Timestamp"
go


SELECT
substring(cast(GETDATE() as character), 1, 11) AS "Date",
substring(cast(GETDATE() as character), 12,8) AS "Time",
GETDATE() AS "GetDate";
go


SELECT
substring(cast(Sysdatetime() as character), 1, 11) AS "Date",
substring(cast(Sysdatetime() as character), 12,8) AS "Time",
Sysdatetime() AS "Sysdatetime";
go

SELECT
substring(cast(GETDATE() as character), 1, 11) AS "Date",
substring(cast(GETDATE() as character), 12,8) AS "Time",
GETDATE() AS "GetDate";
go

/*
-- DATEADD()
If you want to add or subtract an amount of time to a column or a variable, then display a new value
in a rowset or set a variable with that new value, DATEADD() will do this. The syntax for DATEADD() is
DATEADD(datepart, number, date)
The datepart option applies to all of the date functions and details what you want to add from
milliseconds to years. These are defined as reserved words and therefore are not surrounded by
quotation marks. There are a number of possible values, as detailed in Table 11-1.
Taking the second option of the datepart function, to add the value, make the number positive,
and to subtract a number, make it negative. Moving to the final option of the datepart function, this
can be either a value, a variable, or a column date type holding the date and time you wish to change.
Table 11-1. Potential Values for datepart
datepart Definition Meaning
isowk, isoww ISOWeek is a numbering system used to give every week in the calendar
a unique, ascending number. An ISO week starts on a Monday, and
Week 1 is the week containing the first Thursday of that year. For example,
in 2008, the first Thursday occurred on January 3, so Week 1 ran from
December 31, 2007, through January 6, 2008.
tz Timezone offset
ns Nanosecond
mcs Microsecond
ms Millisecond
ss, s Second
mi, n Minute
hh Hour
dw, w Weekday
wk, ww Week
dd, d Day
dy, y Day of year
mm, n Month
qq, q Quarter
yy, yyyy Year
*/

-- Timestamp za 50 casova od trenutne vrednosti
select GETDATE(), DATEADD(hh, 50, getdate()) as 'Timestamp za 50 casova';
go

-- Timestamp za trenutak od pre 50 casova
select GETDATE() as 'Trenutni Timestamp',
		 DATEADD(hh, -50, getdate()) as 'Timestamp pre 50 casova';
go




/*

-- DATEDIFF()
To find the difference between two dates, you would use the function DATEDIFF(). The syntax for this
function is
DATEDIFF(datepart, startdate, enddate)
The first option contains the same options as for DATEADD(), and startdate and enddate are the
two days you wish to compare. A negative number shows that the enddate is before the startdate.
*/
-- Timestamp za 50 dana, a razlika u nedeljama

select GETDATE() as 'Trenutni Timestamp',
	   DATEADD(DD, 55, getdate()) as 'Timestamp za 50 dana',
	   DATEDIFF(WW, getdate(), DATEADD(DD, 55, getdate())) as 'Razlika u nedeljama',
	   CONVERT(decimal(5,2),DATEDIFF(WW, getdate(), DATEADD(DD, 55, getdate())));
go

-- videti to kako se moze dobiti prava vrednost za 55/7

/*
-- DATEPART()
If you wish to achieve returning part of a date from a date variable, column, or value, you can use
DATEPART() within a SELECT statement.
As you may be expecting by now, the syntax has datepart as the first option, and then the
datetoinspect as the second option, which returns the numerical day of the week from the date
inspected.
DATEPART(datepart, datetoinspect)
1. We need to set only one local variable to a date and time. After that, we find the day of the month.
DECLARE @WhatsTheDay datetime
SET @WhatsTheDay = '24 March 2008 3:00 PM'
SELECT DATEPART(dd, @WhatsTheDay)
Figure 11-24 shows the results after executing this code.
Figure 11-24. Finding part of a date
2. To produce a more pleasing date and time for a statement, we can combine DATEPART() and DATENAME() to
have a meaningful output. The function CAST(), which we will look at in detail shortly, is needed here, as it is a
data type conversion function.
DECLARE @WhatsTheDay datetime
SET @WhatsTheDay = '24 March 2008 3:00 PM'
SELECT DATENAME(dw, @WhatsTheDay) + ', ' +
CAST(DATEPART(dd,@WhatsTheDay) AS varchar(2)) + ' ' +
DATENAME(mm,@WhatsTheDay) + ' ' +
CAST(DATEPART(yyyy,@WhatsTheDay) AS char(4))
3. When this is executed, it will produce the more meaningful date shown in Figure 11-25.
Figure 11-25. Finding and concatenating to provide a useful date
*/
-- Ispisati timestamp, koji je dan u nedelji u trenutnom datumu
-- naziv dana nedelje u trenutnom datumu
-- Timestamp za 50 dana, redni broj dana u nedelji u tom datumu i naziv dana

select GETDATE() as 'Trenutni Timestamp',
	   datepart(DW, GETDATE())-1 as 'Redni broj dana u nedelji',
	   DATENAME(dw, GETDATE()) as 'Naziv dana',
		DATEADD(DD, 50, getdate()) as 'Timestamp za 50 dana', 
		datepart(DW, DATEADD(DD, 50, getdate())) as 'Redni br. dana iz Timestampa+50',
		DATENAME(dw, DATEADD(DD, 50, getdate())) as 'Naziv dana za Timestampa+50',
		DATEDIFF(WW, getdate(), DATEADD(DD, 50, getdate())) as 'Razlika u nedeljama';
go

-- Prikazati godinu danasnjeg datuma
select DATEPART(yy,getdate()) as 'Rezultat za godinu pom. datepart-a',
		year(GETDATE()) as 'Rezultat za godinu pom. year-a';
go

-- Prikazati mesec danasnjeg datuma
select DATEPART(mm,getdate()) as 'Rezultat za mesev pom. datepart-a',
		month(GETDATE()) as 'Rezultat za mesec pom. month-a';
go

-- Prikazati dan danasnjeg datuma
select DATEPART(dd,getdate()) as 'Rezultat za dan pom. datepart-a',
		day(GETDATE()) as 'Rezultat za dan pom. month-a';
go

-- ISDATE()
Although ISDATE() is a function that works with dates and times, this system function takes a value
in a column or a variable and confirms whether it contains a valid date or time. The value returned
is 0, or false, for an invalid date, or 1 for true if the date is okay. The formatting of the date for testing within
the ISDATE() function has to be in the same regional format as you have set with SET DATEFORMAT or SET
LANGUAGE. If you are testing in a European format but have your database set to US format, then you
will get a false value returned.

-- 1. The first example demonstrates where a date is invalid. There are only 30 days in September.
DECLARE @IsDate char(15)
SET @IsDate = '31 Sep 2008'
SELECT ISDATE(@IsDate)    - FALSE
*/

select ISDATE('2001-02-31');
go

select GETDATE(),ISDATE(GETDATE())
go

/* Kolika je duzina vracene vrednosti funkcije GETDATE()? */

select GETDATE(), len(GETDATE())
go


/*

Visetablicni upiti

*/

/* Koliko ima pozorista po nazivima mesta? */

-- Jednotablicni upit
select P_MESTO, COUNT(SIF_POZORISTA) as 'Broj pozorista iz dbo.Pozoriste'
	from dbo.POZORISTE
	group by P_MESTO;
go

-- Upit koriscenjem dve tabele - Dekartovim proizvodom
select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA
	group by NAZIV_MESTA;
	go

-- Upit koriscenjem dve tabele - JOIN resenje
select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from POZORISTE p
	join MESTO m on m.SIF_MESTA=p.SIF_MESTA
	group by NAZIV_MESTA;
	go

/* Koliko ima pozorista po nazivima mesta? 
	Zadrzati samo one redove u kojima je taj broj veci od 1 */

select NAZIV_MESTA, COUNT(SIF_POZORISTA)as 'Broj pozorista u mestu'
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA
	group by NAZIV_MESTA
	having COUNT(SIF_POZORISTA)>1;
	go

select NAZIV_MESTA, COUNT(SIF_POZORISTA)as 'Broj pozorista u mestu'
	from POZORISTE p
	join MESTO m on m.SIF_MESTA=p.SIF_MESTA
	group by NAZIV_MESTA
	having COUNT(SIF_POZORISTA)>1;
	go

/* Koliko ima evidentiranih pozorista u Beogradu? */


select NAZIV_MESTA, COUNT(SIF_POZORISTA)as 'Broj pozorista u mestu Beograd'
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Beograd'
	group by NAZIV_MESTA;
	go
	
-- ako se ukine group by, select ce zavrsiti u gresci	
select NAZIV_MESTA, COUNT(SIF_POZORISTA)as 'Broj pozorista u mestu Beograd'
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Beograd';
	go

-- ne mora group by, ali onda se ne sme pojaviti ni NAZIV_MESTA u SELECT-u
select COUNT(SIF_POZORISTA)as 'Broj pozorista u mestu Beograd'
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Beograd';
	go


select NAZIV_MESTA, COUNT(SIF_POZORISTA)as 'Broj pozorista u mestu'
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Beograd'
	group by NAZIV_MESTA
	having COUNT(SIF_POZORISTA)>1;
	go




/*
Rad sa Pozorisnim portalom 
5 cas - 12. nedelja - 21.12.2016.

Prva grupa

*/


-- 21.12.2016.


-- Listati lica koja pripadaju Subotickom okrugu, a nisu vise aktivna
-- resiti pom. dvotablicnog upita

select *
	from LICE l
	join MESTO m on m.SIF_MESTA=l.SIF_MESTA
	where POST_BROJ between 24000 and 24999 and
		L_AKTIVNO=0;
go
select * from MESTO;
go
	

select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and (POST_BROJ>=24000 and POST_BROJ<25000)
			and L_AKTIVNO='false';
go
select * from LICE;
go

-- Listati lica iz Novog Sada koja su aktivna, a bave se profesionalno
-- pozoristem


select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Novi Sad'
			and L_AKTIVNO=1 and L_AMATER=0;
go

-- Listati lica iz Novog Sada cije ime pocinje slovom 'A', koja su aktivna,
-- a bave se profesionalno pozoristem

select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Novi Sad'
			and L_AKTIVNO=1 and L_AMATER=0 and SUBSTRING(L_IME,1,1)='A';
go

select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Novi Sad'
			and L_AKTIVNO=1 and L_AMATER=0 and L_IME like 'A%';
go

--Sledece resenje je pogresno i ne daje dobar rezultat 
select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Novi Sad'
			and L_AKTIVNO=1 and L_AMATER=0 and L_IME='A';
go

-- Listati lica iz Novog Sada koja su aktivna, a bave se profesionalno
-- pozoristem


select L_IME, L_PREZIME
	from dbo.LICE l
	join dbo.MESTO m on l.SIF_MESTA=m.SIF_MESTA
	where NAZIV_MESTA='Novi Sad'
			and L_AKTIVNO=1 and L_AMATER=0;
go

select * from LICE l, MESTO m
	where l.SIF_MESTA=m.SIF_MESTA;
go

-- Listati lica iz Novog Sada i Beograda koja su aktivna, 
-- bave se profesionalno pozoristem a duzina biografije prevazilazi
-- 5(00) znakova

select *
	from LICE l
	join MESTO m on m.SIF_MESTA=l.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
	



select L_IME, L_PREZIME
	from dbo.LICE l
	join dbo.MESTO m on l.SIF_MESTA=m.SIF_MESTA
	where (NAZIV_MESTA='Novi Sad' or NAZIV_MESTA='Beograd')
			and L_AKTIVNO=1 and L_AMATER=0 and LEN(L_BIOGRAFIJA)>500;
go

select L_IME, L_PREZIME
	from dbo.LICE l
	join dbo.MESTO m on l.SIF_MESTA=m.SIF_MESTA
	where NAZIV_MESTA in ('Novi Sad', 'Beograd')
			and L_AKTIVNO=1 and L_AMATER=0 and LEN(L_BIOGRAFIJA)>5;
go

-- Listati imena i prezimena lica iz Srbije
select L_IME, L_PREZIME
	from dbo.LICE l
	join dbo.MESTO m on m.SIF_MESTA=l.SIF_MESTA
	join dbo.ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	where DRZAVA like '%Srbija';
go


select L_IME, L_PREZIME
from dbo.LICE l, ZEMLJA z, MESTO m
where l.SIF_MESTA=m.SIF_MESTA and m.SIF_ZEMLJE=z.SIF_ZEMLJE 
		and DRZAVA='Republika Srbija';
go

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

-- Listati imena i prezimena lica iz Srbije
-- Resenje pomocu ugradjenog select-a
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 l
	join dbo.MESTO m on m.SIF_MESTA=l.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
						where SIF_ZEMLJE=(select SIF_ZEMLJE
											from dbo.ZEMLJA
											where DRZAVA='Republika Srbija'));
go


-- 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 l
join dbo.MESTO m on m.SIF_MESTA=l.SIF_MESTA
	where SIF_ZEMLJE=(select SIF_ZEMLJE
						from dbo.ZEMLJA
						where DRZAVA='Republika Srbija');
go

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

-- Listati imena i prezimena lica kao i naziv pripadajuceg grada
-- takodje i ime/naziv zemlje iz Srbije

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

/* Da li postoji lice koje se pojavljuje dva ili vise puta
 kao autor pozorisnog dela? */
 
 select SIF_LICA, COUNT(SIF_LICA)
	from dbo.AUTOR
	group by SIF_LICA
	having COUNT(SIF_LICA)>1;
go

 select SIF_LICA, COUNT(SIF_LICA)
	from dbo.AUTOR
	group by SIF_LICA;
go

/* Koja lica se (po imenu i prezimenu) pojavljuju po koliko 
puta kao autori pozorisnog dela? */

 select L_IME, L_PREZIME, COUNT(a.SIF_LICA)
	from dbo.AUTOR a,dbo.LICE l
	where a.SIF_LICA=l.SIF_LICA 
	group by L_IME, L_PREZIME;
go

 select L_IME, L_PREZIME, COUNT(a.SIF_LICA)
	from dbo.AUTOR a
	join dbo.LICE l on a.SIF_LICA=l.SIF_LICA
	group by L_IME, L_PREZIME;
go

/* Koja lica se (po imenu i prezimenu) pojavljuju po koliko 
puta kao autori pozorisnog dela? Tabelu rezultata urediti po imenu u 
ABC-dnom redosledu! */

 select L_IME, L_PREZIME, COUNT(a.SIF_LICA)
	from dbo.AUTOR a
	join dbo.LICE l on a.SIF_LICA=l.SIF_LICA
	group by L_IME, L_PREZIME
	order by L_IME;
go

/* Koliko ima razlicitih pozorisnih dela po nazivima zanra (ZANR)?*/

select ZANR, COUNT(SIF_POZ_DELA)
	from dbo.ZANR z
	join dbo.POZORISNO_DELO pd on pd.SIF_ZANRA=z.SIF_ZANRA
	group by ZANR;
go





/* Koriscenje ugradjenog select-a kontra outer join*/

-- Koja lica (imenom i prezimenom) koja nisu autori?
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 l
	left join dbo.AUTOR a on a.SIF_LICA=l.SIF_LICA
	where a.SIF_LICA is null;
go

select L_IME, L_PREZIME, SIF_POZ_DELA
	from dbo.LICE l
	left join dbo.AUTOR a on a.SIF_LICA=l.SIF_LICA;
go



/*
Rad sa Pozorisnim portalom 
6 cas - 13. nedelja - 11.01.2017.

Prva grupa

*/



/*
Ugradjeni SELECT u HAVING-u
*/




/*
Ugradjeni select u FROM klauzuli
*/


/* !!!!! - pocetak
Koliko ima upisanih gradova pozorista koji pocinju slovom B,
a duzina im je 7 - ugradjenim SELECTOM u FROM
*/

-- Obicno resenje

select COUNT(P_MESTO)
	from dbo.POZORISTE
	where left(P_MESTO,1)='B' and LEN(P_MESTO)=7;
go

-- Resenje ugradjenim SELECT-om

select COUNT(mestasapocslovomBduzine7)
	from (select P_MESTO as mestasapocslovomBduzine7
			from dbo.POZORISTE
			where left(P_MESTO,1)='B' and LEN(P_MESTO)=7) as A;
go

/* !!!!! - kraj
Koliko ima upisanih gradova pozorista koji pocinju slovom B,
a duzina im je 7 - ugradjenim SELECTOM u FROM
*/



/* !!!!! - pocetak
Koliki je prosecan broj pozorista u gradovima
 - ugradjenim SELECTOM u FROM
*/
select * from POZORISTE;
go

select SUM(SIF_POZORISTA)
	from dbo.POZORISTE;
go

select convert(numeric(5,2), AVG(SIF_POZORISTA))
	from dbo.POZORISTE;
go

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

select count(upmesto) as "Uk.br.mesta",
		sum(brpozugradu) as "Uk.br.pozorista",
		sum(brpozugradu)/count(upmesto) as
			"Uk.br.pozorista / Uk.br.mesta",
		convert(numeric(5,2), AVG(brpozugradu)) as
			"Convert N2 - AVG broj pozorista"
	from (select P_MESTO as upmesto, 
			convert(numeric(5,2),COUNT(SIF_POZORISTA)) as brpozugradu
				from dbo.POZORISTE
				group by P_MESTO) as A;
go

select count(upmesto) as "Uk.br.mesta",
		sum(brpozugradu) as "Uk.br.pozorista",
		sum(brpozugradu)/count(upmesto) as
			"Uk.br.pozorista / Uk.br.mesta",
		AVG(brpozugradu) as "AVG broj pozorista"
	from (select P_MESTO as upmesto, 
			convert(numeric(5,2),COUNT(SIF_POZORISTA)) as brpozugradu
				from dbo.POZORISTE
				group by P_MESTO) as A;
go

--konacno:

select AVG(brpozugradu) as "Prosecan broj pozorista u gradovima"
	from (select P_MESTO as upmesto, 
			convert(numeric(5,2),COUNT(SIF_POZORISTA)) as brpozugradu
				from dbo.POZORISTE
				group by P_MESTO) as A;
go

-- ili:

select convert(numeric(5,2), AVG(brpozugradu))
			 as "Prosecan broj pozorista u gradovima"
	from (select P_MESTO as upmesto, 
			convert(numeric(5,2),COUNT(SIF_POZORISTA)) as brpozugradu
				from dbo.POZORISTE
				group by P_MESTO) as A;
go

-- ali nikako:

select AVG(brpozugradu) as "Prosecan broj pozorista u gradovima"
	from (select P_MESTO as upmesto, 
			count(SIF_POZORISTA) as brpozugradu
				from dbo.POZORISTE
				group by P_MESTO) as A;
go


/* !!!!! - kraj
Koliki je prosecan broj pozorista u gradovima
 - ugradjenim SELECTOM u FROM
*/