/*
Peti susret sa MS SQLSERVERom (2008 R2)

SELECT-i u PPortal bazi
1. čas - 7. nedelja - 2020.11.19.

Četvrtak - 14:00-15:35
*/

use PPortal

select * from dbo.LICE;

select 2*2

select GETDATE()

select GETDATE() as "Tacan datum i vreme"

select GETDATE() + 1 as "Sutrasnji datum u ovom trenutku"


select GETDATE() + 300 as "Sutrasnji datum u ovom trenutku"

select LOG(GETDATE() + 300)

/*
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.
*/

select LOG(300)


select L_IME as "Ime",
		L_PREZIME as "Prezime",
		L_IZ_VOJVODINE as "iz Vojvodine-1, van Vojvodine-0"
		L_BIOGRAFIJA as "Biografija lica"
	from dbo.LICE;

/*
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'L_BIOGRAFIJA'.
*/

select L_IME as "Ime",
		L_PREZIME as "Prezime",
		L_IZ_VOJVODINE as "iz Vojvodine-1, van Vojvodine-0",
		L_BIOGRAFIJA as "Biografija lica"
	from dbo.LICE;

select * from dbo.POZORISTE

select NAZIV_POZORISTA as "Pozoriste",
		P_MESTO as "Mesto",
		P_POSTANSKI_BROJ as "Postanski broj"
	from dbo.POZORISTE;
	

select NAZIV_POZORISTA as "Pozoriste",
		P_MESTO as "Mesto",
		P_POSTANSKI_BROJ as "Postanski broj"
	from dbo.POZORISTE
	where P_POSTANSKI_BROJ<10000;

select NAZIV_POZORISTA as "Pozoriste",
		P_MESTO as "Mesto",
		P_POSTANSKI_BROJ as "Postanski broj"
	from dbo.POZORISTE
	where P_POSTANSKI_BROJ<10000 or P_POSTANSKI_BROJ>20000;
	
select NAZIV_POZORISTA as "Pozoriste",
		P_MESTO as "Mesto",
		P_POSTANSKI_BROJ as "Postanski broj"
	from dbo.POZORISTE
	where P_POSTANSKI_BROJ<10000 or P_POSTANSKI_BROJ>20000
	order by NAZIV_POZORISTA;

select NAZIV_POZORISTA as "Pozoriste",
		P_MESTO as "Mesto",
		P_POSTANSKI_BROJ as "Postanski broj"
	from dbo.POZORISTE
	
	order by NAZIV_POZORISTA;

select NAZIV_POZORISTA as "Pozoriste",
		P_MESTO as "Mesto",
		P_POSTANSKI_BROJ as "Postanski broj",
		COUNT(*)
	from dbo.POZORISTE
	group by P_MESTO, NAZIV_POZORISTA, P_POSTANSKI_BROJ;
	
/*
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.POZORISTE.NAZIV_POZORISTA' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
*/

select 	P_MESTO as "Mesto",
		COUNT(*) "Broj"
	from dbo.POZORISTE
	group by P_MESTO;
	

select Min(NAZIV_POZORISTA) as "Pozoriste prvo po abecedi",
		Max(P_MESTO) as "Mesto yadnje po abecedi",
		avg(P_POSTANSKI_BROJ) as "prosecan Postanski broj",
		COUNT(*)
	from dbo.POZORISTE
	
select P_IZ_VOJVODINE,
		Min(NAZIV_POZORISTA) as "Pozoriste prvo po abecedi",
		Max(P_MESTO) as "Mesto zadnje po abecedi",
		avg(P_POSTANSKI_BROJ) as "prosecan Postanski broj",
		COUNT(*)
	group by P_IZ_VOJVODINE
	from dbo.POZORISTE
	
/*
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'from'.
greska je nastala zbog (moje) greske posto se nisam 
drzao obaveznog redosleda klauzula!!!
ne moze from posle group by, tj. ne moze group by pre from klauzule!!!
*/

select P_IZ_VOJVODINE,
		Min(NAZIV_POZORISTA) as "Pozoriste prvo po abecedi",
		Max(P_MESTO) as "Mesto zadnje po abecedi",
		avg(P_POSTANSKI_BROJ) as "prosecan Postanski broj",
		COUNT(*)
	from dbo.POZORISTE
	group by P_IZ_VOJVODINE
	

/*
prethodna, tacno zapisana sql naredba daje sledecu tabelu rezultata:
P_IZ_VOJVODINE Pozoriste prvo po abecedi Mesto zadnje po abecedi prosecan Postanski broj COUNT(*)
0	           Budva grad teatar	     Budva	                 8205.000000	           3
1	           Novosadsko pozoriste	     Novi Sad	             21000.000000	           2
*/



/*
Šesti susret sa MS SQLSERVERom (2008 R2)

SELECT-i u PPortal bazi
2. čas - 8. nedelja - 2020.11.26.

Četvrtak - 14:00-15:35
*/

use PPortal

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


select GETDATE() as "Danasnji datum", 
		GETDATE()+5 as "datum danasnji+5dana",
		DATEADD(dd,5,getdate()) as "datum za 5 dana-dateadd",
		DATEADD(mm,2,getdate()) as "datum za dva meseca"



select GETDATE() as "Danasnji datum", 
		GETDATE()+5 as "datum danasnji+5dana",
		year(getdate()) as "godina danasnjeg datuma",
		month(getdate()) as "mesec danasnjeg datuma",
		day(getdate()) as "redni broj dana u mesecu danasnjeg datuma"
	
/*

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

*/	

select GETDATE() as "Danasnji datum", 
		GETDATE()+5 as "datum danasnji+5dana",
		year(getdate()) as "godina year",
		month(getdate()) as "mesec month",
		day(getdate()) as "redni broj dana day",
		datepart(yy,getdate()) as "godina yy datuma",
		datepart(mm,getdate()) as "mesec mm datuma",
		datepart(dd,getdate()) as "redni broj dana dd datuma"


select GETDATE() as "Danasnji datum", 
		len(GETDATE()+5) as "duzina datum",
		len(year(getdate())) as "duzina godine year",
		len(datepart(mm,getdate())) as "duzina za mesec mm datuma"
		

select GETDATE() as "Danasnji datum", 
		len(GETDATE()+5) as "duzina datum",
		left(getdate(),7) as "prvih 7 simbola datuma",
		right(getdate(),9) as "zadnjih 9 simbola datuma"
		
select GETDATE() as "Danasnji datum", 
		len(GETDATE()+5) as "duzina datum",
		left(getdate(),19) as "prvih 7 simbola datuma",
		right(getdate(),9) as "zadnjih 9 simbola datuma"
		
/*
CAST(expression AS datatype(length))
Parameter Values
Value	Description
expression	Required. The value to convert
	datatype	Required. The datatype to convert expression to. Can be one of the following: bigint, int, 
	smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, 
	char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image
(length)	Optional. The length of the resulting data type (for char, varchar, nchar, nvarchar, 
	binary and varbinary)
*/		
		
		
select GETDATE() as "Danasnji datum", 
		len(GETDATE()+5) as "duzina datum",
		left(getdate(),19) as "prvih 7 simbola datuma",
		right(getdate(),9) as "zadnjih 9 simbola datuma",
		CAST(getdate() as character) as "cast-dfatum",
		substring(CAST(getdate() as character),1,7) as "prvih 7 simbola datuma",
		substring(CAST(getdate() as character),10,9) as "zadnjih 9 simbola datuma"
	
/*
Msg 8116, Level 16, State 1, Line 1
Argument data type datetime is invalid for argument 1 of substring function.
*/

select GETDATE() as "Danasnji datum", 
		len(GETDATE()+5) as "duzina datum",
		left(getdate(),19) as "prvih 7 simbola datuma",
		right(getdate(),9) as "zadnjih 9 simbola datuma",
		CAST(getdate() as character) as "cast-dfatum",
		substring(CAST(getdate() as character),1,7) as "prvih 7 simbola datuma",
		substring(CAST(getdate() as character),len(GETDATE())-8,9) as "zadnjih 9 simbola datuma"


select * from dbo.LICE

select COUNT(*)
	from dbo.LICE

select COUNT(distinct SIF_LICA)
	from LICE
	
select COUNT(distinct SIF_LICA)
	from LICE
	
select COUNT(SIF_MESTA)
	from LICE

select COUNT (distinct SIF_MESTA)
	from LICE

update dbo.LICE
	set L_BIOGRAFIJA = null
	where L_IME='Ivo'
	
select COUNT(L_BIOGRAFIJA)
	from LICE
	
select COUNT(L_BIOGRAFIJA)
	from LICE
	where L_BIOGRAFIJA = null

/*
0
*/

select COUNT(L_BIOGRAFIJA)
	from LICE
	where L_BIOGRAFIJA is not null
	
select COUNT(*)
	from LICE
	where L_BIOGRAFIJA is null
--- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
select * from dbo.LICE


select P_IZ_VOJVODINE,
		Min(NAZIV_POZORISTA) as "Pozoriste prvo po abecedi",
		Max(P_MESTO) as "Mesto zadnje po abecedi",
		avg(P_POSTANSKI_BROJ) as "prosecan Postanski broj",
		COUNT(*) as "Br.pozorista"
	from dbo.POZORISTE
	group by P_IZ_VOJVODINE
	
select P_IZ_VOJVODINE,P_AMATERSKO,
		Min(NAZIV_POZORISTA) as "Pozoriste prvo po abecedi",
		Max(P_MESTO) as "Mesto zadnje po abecedi",
		avg(P_POSTANSKI_BROJ) as "prosecan Postanski broj",
		COUNT(*)
	from dbo.POZORISTE
	group by P_IZ_VOJVODINE,P_AMATERSKO
	
select L_IZ_VOJVODINE,L_AMATER,L_AKTIVNO,
		Min(SIF_LICA) as "Lice-sifra minimalna",
		Max(SIF_MESTA) as "Sif Mesto maksimalna",
		avg(SIF_LICA) as "prosecna sif lica",
		COUNT(*) as "Br.lica"
	from dbo.LICE
	group by L_IZ_VOJVODINE, L_AMATER,L_AKTIVNO
	




/*
Sedmi susret sa MS SQLSERVERom (2008 R2)

SELECT-i u PPortal bazi
3. čas - 9. nedelja - 2020.12.03.

Četvrtak - 14:00-15:35
*/

-- where between, in, like
-- having, order by
-- visetablicni upiti

use PPortal



/*
Osmi susret sa MS SQLSERVERom (2008 R2)

SELECT-i u PPortal bazi
4. čas - 10. nedelja - 2020.12.10.

Četvrtak - 14:00-15:35
*/

-- visetablicni upiti, ugradjeni select-i u where, having
-- podselekti koji se izvrsavaju vise puta
-- podselekti u FROM-u

use PPortal


/*
Iz koje drzave po nazivu (DRZAVA) potice i kako se zove (L_IME,L_PREZIME)
autor pozorisnog dela (NASLOV_POZ_DELA) sa naslovom Violinista na krovu?
*/
--JOIN

select DRZAVA,L_IME,L_PREZIME
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
	join dbo.AUTOR a on a.SIF_LICA=l.SIF_LICA
	join dbo.POZORISNO_DELO pd on pd.SIF_POZ_DELA=a.SIF_POZ_DELA
	where NASLOV_POZ_DELA='Violinista na krovu';
go

-- u ovom select-u sa join-ovima se moze smestiti bilo koja kolona 
-- (cak i sve kolone kao sto se vidi u sledecem primeru) u tabelu 
-- rezultata jer imamo sve kolone iz svih pet tabela na raspolaganju:

select * --DRZAVA,L_IME,L_PREZIME
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
	join dbo.AUTOR a on a.SIF_LICA=l.SIF_LICA
	join dbo.POZORISNO_DELO pd on pd.SIF_POZ_DELA=a.SIF_POZ_DELA
--	where NASLOV_POZ_DELA='Violinista na krovu';
go


select DRZAVA,L_IME,L_PREZIME,NASLOV_POZ_DELA
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
	join dbo.AUTOR a on a.SIF_LICA=l.SIF_LICA
	join dbo.POZORISNO_DELO pd on pd.SIF_POZ_DELA=a.SIF_POZ_DELA
	where NASLOV_POZ_DELA='Violinista na krovu';
go



-- ugradjeni select

select DRZAVA,L_IME,L_PREZIME,NASLOV_POZ_DELA
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
	join dbo.AUTOR a on a.SIF_LICA=l.SIF_LICA
	where a.SIF_POZ_DELA=(select SIF_POZ_DELA
						from dbo.POZORISNO_DELO
						where NASLOV_POZ_DELA='Violinista na krovu');
go

/*
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NASLOV_POZ_DELA'.

Kolona NASLOV_POZ_DELA se nalazi u tabeli unutrasnjeg select-a
i ne moze se prikazati u R (tabeli rezultata)
*/

select DRZAVA,L_IME,L_PREZIME --,NASLOV_POZ_DELA
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
	join dbo.AUTOR a on a.SIF_LICA=l.SIF_LICA
	where a.SIF_POZ_DELA=(select SIF_POZ_DELA
						from dbo.POZORISNO_DELO
						where NASLOV_POZ_DELA='Violinista na krovu');
go


select DRZAVA,L_IME,L_PREZIME --,NASLOV_POZ_DELA
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	join dbo.LICE l on l.SIF_MESTA=m.SIF_MESTA
	where l.SIF_LICA in (select SIF_LICA
					from dbo.AUTOR
					where SIF_POZ_DELA=(select SIF_POZ_DELA
										from dbo.POZORISNO_DELO
										where NASLOV_POZ_DELA='Violinista na krovu'));
go


select DRZAVA,L_IME,L_PREZIME --,NASLOV_POZ_DELA
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	where m.SIF_MESTA=(select SIF_MESTA
					from dbo.LICE
					where SIF_LICA in (select SIF_LICA
									from dbo.AUTOR
									where SIF_POZ_DELA=(select SIF_POZ_DELA
													from dbo.POZORISNO_DELO
													where NASLOV_POZ_DELA='Violinista na krovu')));
go

/*Msg 207, Level 16, State 1, Line 1
Invalid column name 'L_IME'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'L_PREZIME'.

Kolone L_IME i L_PREZIME se nalaze u tabeli unutrasnjeg select-a
i ne mogu se prikazati u R (tabeli rezultata)
*/

select DRZAVA    --,L_IME,L_PREZIME --,NASLOV_POZ_DELA
	from dbo.ZEMLJA z
	join dbo.MESTO m on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	where m.SIF_MESTA in(select SIF_MESTA
					from dbo.LICE
					where SIF_LICA in (select SIF_LICA
									from dbo.AUTOR
									where SIF_POZ_DELA=(select SIF_POZ_DELA
													from dbo.POZORISNO_DELO
													where NASLOV_POZ_DELA='Violinista na krovu')));
go


select DRZAVA    --,L_IME,L_PREZIME --,NASLOV_POZ_DELA
	from dbo.ZEMLJA z
	where z.SIF_ZEMLJE in (select SIF_ZEMLJE
						from dbo.MESTO
						where SIF_MESTA in(select SIF_MESTA
										from dbo.LICE
										where SIF_LICA in (select SIF_LICA
														from dbo.AUTOR
														where SIF_POZ_DELA=(select SIF_POZ_DELA
																		from dbo.POZORISNO_DELO
																		where NASLOV_POZ_DELA='Violinista na krovu'))));
go



/*
Koja su ona mesta (NAZIV_MESTA) u kojima nema pozorista?
*/

-- Resenje ugradjenim select-om

select NAZIV_MESTA
	from dbo.MESTO
	where SIF_MESTA not in (select SIF_MESTA
								from dbo.POZORISTE)

-- kontrola:

select * from POZORISTE


-- Resenje outer join-om

--1. korak - redjaju se svi redovi "leve" tabele (mesta)

select NAZIV_MESTA
	from dbo.MESTO m
	left join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA 

--2. korak - redjaju se svi redovi "leve" tabele (mesta)
--  null vrednosti se pojavljuju tamo gde se za red u levoj
--  ne moze naci odgovarajuci red u desnoj tabeli

select *   --NAZIV_MESTA
	from dbo.MESTO m
	left join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA 

select NAZIV_MESTA, NAZIV_POZORISTA
	from dbo.MESTO m
	left join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	
-- 3. korak

 select NAZIV_MESTA, NAZIV_POZORISTA
	from dbo.MESTO m
	left join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where NAZIV_POZORISTA is null
 
 -- tj.
 
  select NAZIV_MESTA   --, NAZIV_POZORISTA
	from dbo.MESTO m
	left join dbo.POZORISTE p on p.SIF_MESTA=m.SIF_MESTA
	where NAZIV_POZORISTA is null
 
 
 /*
Po nazivima mesta koji su najveci postanski brojevi u tabeli pozoriste?

unutrasnji select se izvrsava vise puta!!!
*/
 
 select P_MESTO, P_POSTANSKI_BROJ
	from dbo.POZORISTE psp
	where P_POSTANSKI_BROJ=(select MAX(P_POSTANSKI_BROJ)
							from dbo.POZORISTE pun
							where pun.P_MESTO=psp.P_MESTO)
							
-- kotrola:

select P_MESTO, P_POSTANSKI_BROJ
	from dbo.POZORISTE
	

/*
--Kolika je prosecan broj pozorista po mestima (SIF_MESTA, tabela POZORISTE)?
--Prosecno koliko ima pozorista po mestima?

Trebala bi nam tabela u kojoj je naznaceno koliko ima pozorista
u pojedinim msetima - posto takvu fizicku tabelu nemamo
stvoricemo je pom. unutrasnjem select-a u FROM klauzuli!!!
*/

select AVG(br_pozorista_po_mestu)
	from (select SIF_MESTA, COUNT(SIF_POZORISTA) as "br_pozorista_po_mestu"
			from dbo.POZORISTE
			group by SIF_MESTA) y
 
 -- mora se dodeliti skraceni naziv za tabelu generisani ugradjenim
 -- select-om (skraceni naziv je y) bez obzira sto se ne mora koristiti
 -- u spoljasnjem select-u
 
 select AVG(y.br_pozorista_po_mestu)
	from (select SIF_MESTA, COUNT(SIF_POZORISTA) as "br_pozorista_po_mestu"
			from dbo.POZORISTE
			group by SIF_MESTA) y
 
 
 -- br_pozorista_po_mestu je celobrojna vrednost, a avg je ne pretvara
 -- u numericku tj. odbacuje decimale
 -- primenicemo cast() funkciju za pretvaranje
 
 select AVG(cast(y.br_pozorista_po_mestu as decimal))
	from (select SIF_MESTA, COUNT(SIF_POZORISTA) as "br_pozorista_po_mestu"
			from dbo.POZORISTE
			group by SIF_MESTA) y  
			
 select AVG(cast(y.br_pozorista_po_mestu as numeric))
	from (select SIF_MESTA, COUNT(SIF_POZORISTA) as "br_pozorista_po_mestu"
			from dbo.POZORISTE
			group by SIF_MESTA) y  
			


--Kolika je prosecan broj lica po mestima (SIF_MESTA, tabela POZORISTE)?
--Prosecno koliko lica boravi po mestima?

select AVG(cast(br_lica_u_mestu as decimal))
	from (select SIF_MESTA, count(SIF_LICA) as "br_lica_u_mestu"
			from dbo.LICE
			group by SIF_MESTA) w