/*  DML - Data Manipulation Language

	SELECT [{ALL | DISTINCT}] select_item [AS alias] [,...]
	FROM { table_name [[AS] alias] | view_name [[AS] alias]} [,...]
	[ [join_type] JOIN join_condition ]
	[WHERE search_condition] [ {AND | OR | NOT} search_condition [...] ]
	[GROUP BY group_by_expression{group_by_columns}
	[HAVING search_condition] ]
	[ORDER BY {order_expression [ASC | DESC]} [,...] ]

*/

/********* SELECT *************

	SELECT column_name,column_name as 'alias'
	FROM table_name;
*/

use STUDIJE

-- 1.  Prikazati spisak svih studenata

select *
from studenti

-- 2.  Prikazati Imes, indeks, upisan, mesto iz tabele STUDENTI

select Imes, indeks, upisan, mesto
from studenti

-- 3.  Prikazati Imes, Indeks, Upisan, Datr iz tabele studenti, gde je Datr naslovljena kao 'Datum rodjenja'

select Imes, indeks, upisan, mesto, Datr as 'Datum rodjenja'
from studenti

/********* DISTINCT *************

	SELECT DISTINCT column_name,column_name
    FROM table_name;
*/


-- 4.  Selektovati razli?ita mesta iz tabele Studenti, i dopisati kolonu sa NULL vrednostima

select distinct mesto
from studenti

/********* WHERE *************

	SELECT column_name,column_name
	FROM table_name
	WHERE predikat;
*/

-- 5.  Prikazati podatke o studentima koji su upisani 2000 godine

select *
from studenti
where upisan = 2000


/******** BETWEEN **********

	SELECT column_name(s)
	FROM table_name
	WHERE column_name BETWEEN value1 AND value2;

*/

-- 6.  Prikazati sve studente koji su upisani izmedju 2000 i 2005

select *
from studenti
where upisan between 2000 and 2005

/******** IN **********

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

*/

-- 7.  Prikazati Studente koji su iz Kragujevca ili Kraljeva

select *
from studenti
where mesto in ('Kragujevac', 'Kraljevo')

-- 8.  Prikazati Studente koji NISU iz Kragujevca ili Kraljeva

select *
from studenti
where mesto not in ('Kragujevac', 'Kraljevo')

/******** LIKE **********

	SELECT column_name(s)
	FROM table_name
	WHERE column_name LIKE pattern;

*/

-- 9.  Prikazati sve studente koji dolaze iz grada ?ije ime po?inje na "K"

select *
from studenti
where mesto like 'K%'

-- 10. Prikazati studente za koje je nepoznat datum rodjenja

select *
from studenti
where datr is null

/********* ORDER BY *************

	SELECT column_name, column_name
	FROM table_name
	ORDER BY column_name ASC|DESC, column_name ASC|DESC;

*/

-- 11. Prikazati Studente koji NISU iz Kragujevca ili Kraljeva sortirane po imenu (od najmanjeg ka najve?em)

select *
from studenti
where mesto not in ('Kragujevac', 'Kraljevo')
order by Imes desc

/********* CONCAT && CAST *************

	SELECT CAST(column_name as TYPE)
	FROM table_name
	
	SELECT CONCAT(column_name, value, column_name, ...)
	FROM table_name

*/

-- 12. Za svako ime studenta kreirati kolonu u kojoj ?e pisati njegov Indeks kao "br_indeksa/godina" (CONCAT i CAST)

--I nacin

select Imes, CONCAT(Indeks, '/', Upisan) as Indeks
from Studenti

--II nacin

select Imes, CAST(Indeks as varchar(5)) + '/' + CAST(Upisan as varchar(5))
from Studenti

/********* CASE ********
  
  I)

	Simple CASE expression:   
	CASE input_expression   
		 WHEN when_expression THEN result_expression [ ...n ]   
		 [ ELSE else_result_expression ]   
	END   

  II)

	Searched CASE expression:  
	CASE  
		 WHEN Boolean_expression THEN result_expression [ ...n ]   
		 [ ELSE else_result_expression ]   
	END 

*/

-- 13. Prona?i smeštaj - Prikazati sve studente i dodati kolonu u kojoj za studente koji nisu iz kragujevca piše vrednost "Potraban smestaj", u suprotnom piše "Lokalno"

select Imes, 
case Mesto
	when 'Kragujevac' then 'LOKALNO'
	else 'POTREBAN SMESTAJ'
end as 'Smestaj'
from Studenti

-- 14. Selektovati sve ocene iz prijava, i dodati kolonu koja za svaku ocenu ispisuje da li je ocena 
--		"Ispod proseka" (5.6), 
--		"Prosek" (7,8), 
--		"Odlicna" (9)
--		"Izuzetna" (10)

select Indeks, Upisan, Spred, 
	case 
		when Ocena > 5 and Ocena < 7 then 'Ispod proseka'
		when Ocena >= 7 and Ocena < 9 then 'Proseck'
		when Ocena = 9 then 'Odlicana'
		else 'Izuzetana' 
	end as Status
from Prijave

/******** DATE FUNCTIONS *********

	DATEDIFF ( datepart , startdate , enddate )
	DATENAME ( datepart , date )
	DATEPART ( datepart , date )
	GETDATE  ( )
*/


-- 15. Prikazati godišta i starost, svih studenata koji imaju više od 25 godina

select Imes, Indeks, Upisan, DATEPART(year, datr) as 'Godina', DATEDIFF(year, datr, GETDATE()) as Starost
from Studenti
where DATEDIFF(year, datr, GETDATE()) > 25


/**************** AGREGATNE FUNKCIJE ****************

	COUNT , MIN, MAX, SUM, AVG

*/

-- 16. Prikazati broj studenata koji su upisani na PMF-u

select COUNT(*)
from Studenti

-- 17. Prikazati broj studenata koji su upisani na PMF-u, a imaju poznat datum rodjenja

select COUNT(datr)
from Studenti

-- 18. Prikazati prosecnu, minimalnu i maksimalnu ocenu na predmetu sa sifrom 23

select MAX(ocena) as minimalna, MIN(ocena) as maksimalna, AVG(cast(ocena as real)) as prosecna
from Prijave
where Spred = 23

select MAX(ocena) as minimalna, MIN(ocena) as maksimalna, AVG(ocena * 1.0) as prosecna
from Prijave
where Spred = 23

/**************** GROUP BY ****************

	SELECT column_name, aggregate_function(column_name)
	FROM table_name
	WHERE column_name operator value
	GROUP BY column_name;

*/

-- 19. Za svako mesto ispisati koliko studenata dolazi iz njega.

select Mesto,count(*) as 'broj studenata'
from Studenti
group by Mesto

-- 20. Za svako godište studenata koje se pojavljuje u tabeli Studenti, ispisati broj studenata koji su ro?eni te godine.

select datepart(year, Datr) godiste, COUNT(*) as 'broj studenata'
from studenti
group by datepart(year, Datr)

-- 21. Sa svakog studenta (indeks, upisan) ispisati koliko ispita je polozio i njegovu prose?nu ocenu

select Indeks, Upisan, count(*) 'broj polozenih', Avg(cast(Ocena as real)) as Prosek
from Prijave
group by Indeks, Upisan

/**************** HAVING ****************

	SELECT column_name, aggregate_function(column_name)
	FROM table_name
	WHERE column_name operator value
	GROUP BY column_name
	HAVING aggregate_function(column_name) operator value;

*/

-- 22. Prikazati sve studente koji imaju prosek ve?i od 6

select Indeks, Upisan, Avg(cast(Ocena as real)) as Prosek
from Prijave
group by Indeks, Upisan
having Avg(cast(Ocena as real)) > 6.0

-- 23. Koji predmeti se drže na više razli?itih smerova (group by + having)

select Spred, count(*)
from Planst
group by Spred
having count(Ssmer) > 1

-- 24. Koliko ima predmeta na svakoj godini?

select (semestar + 1) / 2 as Godina, count(*) as Br
from Planst
group by (semestar + 1) / 2

-- 25. Za svaki predmet ispisati kada je poslednji put polagan?

select spred, max(datump)
from Prijave
group by spred

-- 26. Poslednji polozen predmet za svakog studenta?

select indeks, upisan, max(datump)
from Prijave
where ocena > 5
group by Indeks, Upisan

-- 27. Za svaki ispitni rok ispisati koliko se u njemu položilo ispita do sada?

		/*  
		    rbr_meseca, rok
			1, Jan
			2, Feb
			6, Jun
		    8, Avg
		    9, Sept
		    10 Okt
		*/

select DATEPART(m, datump) as mesec, count(*) as Broj, 
	case datepart(m, datump)
		when 1 then 'Januarski'
		when 2 then 'Feb'
		when 6 then 'Jun'
		when 8 then 'Avg'
		when 9 then 'Sept'
		when 10 then 'Okt'
	end as Rok
from Prijave
where ocena > 5
group by datepart(m, datump)

-- 28. Prikazati uspeh (prosecnu ocenu) svake generacije

select upisan generacija, avg(ocena * 1.0)
from prijave
where ocena > 5
group by upisan