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

Druga 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

/*
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"
	from dbo.ZEMLJA
	where LEN(DRZAVA)>12;
	go
	
	
	
	
	
/*
Rad sa Pozorisnim portalom 
2 cas - 9. nedelja - 30.11.2016.

Druga grupa

*/

use PPortal;
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)
	from ZEMLJA;
go

insert ZEMLJA
	values(5,'Mongolija',null);
go

select COUNT(OZNAKA_DRZAVE)
	from ZEMLJA;
go

/*
Kod kojeg broja drzava nemamo evidentiranu oznaku
*/

select COUNT(SIF_ZEMLJE)
	from ZEMLJA
	where OZNAKA_DRZAVE is null;
go

select COUNT(OZNAKA_DRZAVE) as "Broj drzava u tabeli Zemlja sa evidentiranom oznakom"
	from dbo.ZEMLJA;
	go
	
/*
Koliko imamo evidentiranih drzava ciji su nazivi duzi od 12 znakova?
*/

select COUNT(SIF_ZEMLJE)
	from ZEMLJA
	where LEN(DRZAVA) > 12;
go

/*
Koje su one drzave ciji su nazivi duzi od 12 znakova?
*/
select DRZAVA
	from ZEMLJA
	where LEN(DRZAVA) > 12;
go

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

select DRZAVA
	from ZEMLJA
	where len(DRZAVA) between 8 and 12;
go

select DRZAVA
	from 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), GETDATE(), LEFT(DRZAVA,2)
	from ZEMLJA;
go

select UPPER(DRZAVA), left(GETDATE(),11), LEFT(DRZAVA,2)
	from ZEMLJA;
go

select UPPER(DRZAVA), convert(date,GETDATE()), LEFT(DRZAVA,2)
	from ZEMLJA;
go

select UPPER(DRZAVA), convert(date,GETDATE()), substring(DRZAVA,1,2)
	from ZEMLJA;
go

/*
Primer za ilustraciju sadrzaja char u varchar tipa polja
listati nazive drzava, zadnja tri znaka nezive drzava
 i zadnja tri zanaka oznake drzave
*/

select DRZAVA, right(DRZAVA,3), RIGHT(OZNAKA_DRZAVE,3)
	from ZEMLJA;
go


/*
U koliko (razlicitih) gradova su evidentirana pozorista? 
*/
select * from POZORISTE;
go

select COUNT(P_MESTO)
	from POZORISTE;
go


select COUNT(distinct P_MESTO)
	from POZORISTE;
go


/*
Koliko ima upisanih gradova pozorista koji pocinju slovom B
- left, substring, like
*/
select COUNT(P_MESTO)
	from dbo.POZORISTE
	where left(P_MESTO,1)='B'
go

select COUNT(P_MESTO)
	from dbo.POZORISTE
	where substring(P_MESTO,1,1)='B'
go

select COUNT(P_MESTO)
	from dbo.POZORISTE
	where P_MESTO like 'B%'
go

/*
Koja su ta mesta u kojima imamo pozorista, a pocinju slovom B
*/
select distinct P_MESTO
	from dbo.POZORISTE
	where P_MESTO like 'B%'
go

/*
Koliko ima upisanih gradova pozorista koji pocinju slovom B,
a duzina im je 5 (7)
*/

select distinct P_MESTO
	from dbo.POZORISTE
	where P_MESTO like 'B____';
go

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



/*
Izbrojati razlicite nazive gradova pozorista koji pocinju slovom B
*/



/*
Izbrojati razlicite nazive gradova pozorista koji pocinju slovom B,
a duzine su 5 (7)
*/



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

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

/*
GROUP BY klauzula
*/

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

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=1
	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 30000
	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




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

Druga grupa

*/

use PPortal;
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-naveden u adresama
za pojedina pozorista- u tim gradovima
- koristiti tabelu pozoriste
*/
select P_MESTO, COUNT(SIF_POZORISTA),
		min(P_POSTANSKI_BROJ),
		MAX(P_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
	

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
	
/*
Koliko imamo pozorista ,
potraziti najmanji i najveci postanski broj u evidenciji 
- 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;
	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 je najmanji postanski broj veci
od 20000
- 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)>20000;
	go		
	
update 	dbo.POZORISTE
	set P_POSTANSKI_BROJ=25987
	where P_MESTO='Budva';
go
select * from dbo.POZORISTE;
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	
	
/*	
Koliko imamo pozorista u pojedinim gradovima,
a koji je najmanji i najveci postanski broj-naveden u adresama
za pojedina pozorista 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
	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 min(P_POSTANSKI_BROJ)<20000;
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	
	from dbo.POZORISTE
	group by P_MESTO
	having min(P_POSTANSKI_BROJ)=max(P_POSTANSKI_BROJ);
	go

select P_MESTO	
	from dbo.POZORISTE
	having min(P_POSTANSKI_BROJ)=max(P_POSTANSKI_BROJ);
	go

select P_MESTO	
	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 distinct P_MESTO
	from dbo.POZORISTE
	order by P_MESTO desc;
go


/*
Listati nazive gradova i postanske brojeve 
iz evidencije u obrnutom abecednom redosledu naziva gradova,
a u rastucem redosledu postanske brojeve
*/
select P_MESTO, P_POSTANSKI_BROJ
	from dbo.POZORISTE
	order by P_MESTO desc, P_POSTANSKI_BROJ asc;
go

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


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




/*
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.
*/	
	
select P_MESTO, P_POSTANSKI_BROJ
	from dbo.POZORISTE
	order by P_POSTANSKI_BROJ desc;
go
	
	
/*
Rad sa Pozorisnim portalom 
4 cas - 11. nedelja - 14.12.2016.

Druga grupa

*/

use PPortal;
go



/*Prikazati trenutni datum i trenutno vreme  */

SELECT
substring(cast(CURRENT_TIMESTAMP as character), 1, 11) AS "Date",
substring(cast(CURRENT_TIMESTAMP as character), 12,8) 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


/*
-- 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
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';
go

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

-- 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('2000-02-29')
go

select GETDATE(),ISDATE(GETDATE())
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

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

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


/*

Visetablicni upiti

*/

/* Koliko ima pozorista po nazivima mesta? */
select P_MESTO, COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	group by P_MESTO;
go


select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA
	group by NAZIV_MESTA;
	go

select m.NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from POZORISTE p
	join MESTO m on p.SIF_MESTA=m.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'
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Beograd'
	group by NAZIV_MESTA;
	go
	

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 NAZIV_MESTA='Beograd';
	go

select COUNT(SIF_POZORISTA) as 'Broj pozorista u Beogradu'
	from POZORISTE p
	join MESTO m on m.SIF_MESTA=p.SIF_MESTA
	where NAZIV_MESTA='Beograd';
	go

select COUNT(SIF_POZORISTA) as 'Broj pozorista u Beogradu'
	from POZORISTE p, MESTO m
	where m.SIF_MESTA=p.SIF_MESTA and NAZIV_MESTA='Beograd';
	go

-- Listati lica koja pripadaju okrugu Novi Sad, a nisu vise aktivna
-- (aktivna je sledeci upit)
select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and (POST_BROJ>=21000 and POST_BROJ<22000)
			and L_AKTIVNO='false';
go

select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and (POST_BROJ>=21000 and POST_BROJ<22000)
			and L_AKTIVNO='true';
go


select * from LICE;
go
select * from MESTO;
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

select L_IME, L_PREZIME
	from dbo.LICE l
	join dbo.MESTO m on m.SIF_MESTA=l.SIF_MESTA
	where 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 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

-- ugradjeni select

select L_IME, L_PREZIME
	from dbo.LICE
	where L_AKTIVNO=1 and L_AMATER=0 and SUBSTRING(L_IME,1,1)='A'
		and SIF_MESTA = (Select SIF_MESTA
							from  MESTO
							where NAZIV_MESTA='Novi Sad') ;
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 znakova

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)>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 in ('Novi Sad', 'Beograd')
			and L_AKTIVNO=1 and L_AMATER=0 and LEN(L_BIOGRAFIJA)>5;
go



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

Druga grupa

*/
-- 21.12.2016.


-- 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='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
	where SIF_MESTA in (select SIF_MESTA
						from dbo.MESTO m
						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
	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 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

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


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

-- Listati imena i prezimena lica i naziv drzave ako je ona Srbija

select L_IME, L_PREZIME, NAZIV_MESTA
	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





-- 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 
 
 
 
 
 
 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.SIF_LICA, L_IME, L_PREZIME, count(a.SIF_LICA)
	from dbo.LICE l, dbo.AUTOR a
	where a.SIF_LICA=l.SIF_LICA
	group by l.SIF_LICA, L_IME, L_PREZIME;
go
	

 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

	

/*
Ugradjeni SELECT u WHERE - u
*/




/*
Ugradjeni SELECT u HAVING-u ----- RESENJA SU POGRESNA - POCETAK
*/

select * from MESTO;

/* Listati nazive drzava koje imaju broj gradova veci
od prosecnog broja gradova po drzavama */

select DRZAVA, COUNT(SIF_MESTA)
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	group by DRZAVA
	having COUNT(SIF_MESTA) > (select AVG(SIF_MESTA)
								from dbo.MESTO);
go

select AVG(SIF_MESTA)
	from dbo.MESTO;

/* Listati nazive mesta koja imaju postanski broj gradova veci
od prosecnog postanskog broja (gradova iz evidencije) */

select NAZIV_MESTA
	from dbo.MESTO
	having COUNT(SIF_MESTA) > (select AVG(SIF_MESTA)
								from dbo.MESTO);
go

/*
Ugradjeni SELECT u HAVING-u ----- RESENJA SU POGRESNA - KRAJ
*/


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

-- Koja lica (imenom i prezimenom) 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, SIF_POZ_DELA
	from dbo.LICE l
	left join dbo.AUTOR a on a.SIF_LICA=l.SIF_LICA;
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
	from dbo.AUTOR a
	right join dbo.LICE l on a.SIF_LICA=l.SIF_LICA
	where a.SIF_LICA is null;
go


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

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