-- merge
-- 1.
--- a) kreirati tabelu predmetstudenti koja
--     sadrzi informacije o broju studenata koji su 
--     uspesno polozili ispit iz tog predmeta
--     idPredmeta brojstudenata 
---    primarni i kljuc i strani kljuc na tabelu predmet

CREATE TABLE predmetstudenti(
	idpredmeta INTEGER NOT NULL PRIMARY KEY,
	brstudenata INTEGER,
	FOREIGN KEY fk_ps_predmet(idpredmeta) REFERENCES predmet
	);
	
-- b) poupiniti tabelu predmetstudenti sa obaveznim
--    predmetima sa osnovnih akademskih studija Informatike
--    prilikom popunjavanja vrednost u koloni brStudenata postaviti na 5

INSERT INTO predmetstudenti(idpredmeta, brstudenata)
SELECT pp.idpredmeta, 5
FROM predmetprograma pp JOIN studijskiprogram sp
		ON pp.idprograma = sp.id
	 JOIN nivokvalifikacije nk
	 	ON sp.idnivoa = nk.id
WHERE sp.naziv = 'Informatika' AND nk.naziv = 'Osnovne akademske studije'
	AND pp.vrsta = 'obavezan';

SELECT * FROM predmetstudenti;
	
-- c) dopuniti sadrzaj tabelel predmemt studenti sa validniim
--    informacijam aiz baze, azurirati podatke za vec postojece
--    podatke i dodajemo nove redove za ostale

MERGE INTO predmetstudenti ps
USING (
	SELECT p.id, COUNT(i.indeks) br
	FROM predmet p LEFT JOIN ispit i
			ON p.id = i.idpredmeta
			 AND i.ocena > 5 AND i.status = 'o'
	GROUP BY p.id
	) AS real_data
ON ps.idpredmeta = real_data.id
WHEN MATCHED THEN UPDATE SET ps.brstudenata = real_data.br
WHEN NOT MATCHED THEN INSERT VALUES (real_data.id, real_data.br)
ELSE IGNORE;

SELECT * FROM predmetstudenti;

-- d) obrisati tabelu predmetstudenti

DROP TABLE predmetstudenti;


-- 2.
-- a)Napraviti tabelu studentPodaci u kojoj se cuvaju sledece informacije:
--   indeks, br polozenih ispita, prosek, datum upisa
--   indeks je primarni kljuc i strani na tabelu dosije

CREATE TABLE studentPodaci (
	indeks INTEGER NOT NULL PRIMARY KEY,
	br_polozenih SMALLINT,
	prosek FLOAT,
	datupisa DATE,
	FOREIGN KEY (indeks) REFERENCES dosije);
	
-- b) ubaciti u tabelu sledece informacije:
--   sve osim datupisa za studente sa prosekom preko 8
--   za studente koji imaju status diplomirao:
--      br polozenih = 40 prosek 10 datupisa nepoznat

INSERT INTO studentPodaci(indeks, br_polozenih, prosek)
WITH diplomirani AS(
SELECT d.indeks
FROM dosije d
WHERE d.idstatusa = (SELECT id 
					FROM studentskistatus 
					WHERE naziv = 'Diplomirao')
)
SELECT i.indeks, COUNT(i.idpredmeta), AVG(i.ocena +0.0)
FROM ispit i
WHERE i.ocena > 5 AND i.status = 'o'
	AND i.indeks NOT IN (SELECT * FROM diplomirani)
GROUP BY i.indeks
HAVING AVG(i.ocena +0.0) > 8
UNION
SELECT indeks, 40, 10.0
FROM diplomirani;

SELECT * FROM studentPodaci;

-- c) ubaciti u tabelu sledece informacije
--   1)studentima koji imaju status diplomiranog studenta
--     azurirati datum upisa
--   2) studentima koji su vec u tabeli i na budzetu su
--      azurirati br polozneih ispita i prosek
--   3) studente koji su vec u tabeli i imaju status ispisan
--      obrisati unos iz tabele
--   4) studentima koji se ne nalzaze u tabeli uneti indeks
--      validan br_pp i prosek
--   ostale ne dirati

MERGE INTO studentPodaci sp
USING (
	SELECT d.indeks, 
	        ss.naziv, 
	        d.datupisa, 
	        AVG(
		        CASE 
		        	WHEN ocena > 5 AND status = 'o' THEN ocena + 0.0
		        	ELSE NULL
	        	END
	        ) prosek,
     		SUM(
	     		CASE
	     			WHEN ocena > 5 AND status = 'o' THEN 1
	     			ELSE 0
	 			END
 			) br_polozenih
	FROM (dosije d JOIN studentskistatus ss
			ON d.idstatusa = ss.id)
		 LEFT JOIN ispit i
			ON d.indeks = i.indeks
	GROUP BY d.indeks, d.datupisa, ss.naziv 
) as pom
ON sp.indeks = pom.indeks
WHEN MATCHED AND pom.naziv = 'Diplomirao'
	THEN UPDATE SET sp.datupisa = pom.datupisa
WHEN MATCHED AND pom.naziv = 'Budzet'
	THEN UPDATE SET 
		(sp.br_polozenih, sp.prosek) = (pom.br_polozenih, pom.prosek)
WHEN MATCHED AND pom.naziv = 'Ispisan'
	THEN DELETE
WHEN NOT MATCHED AND pom.naziv <> 'Ispisan'
	THEN INSERT (indeks, br_polozenih, prosek)
		 VALUES (pom.indeks, pom.br_polozenih, pom.prosek)
ELSE IGNORE;

SELECT * FROM studentPodaci;

----------------------------------------------

-- Napisati upit na SQL koji za skolsku goindu i studijski program
-- osnovnih studija izracunava koliki je procenat studenata koji su godine
-- upisali fakultet upisan upravo na taj studijski program, kao i 
-- prosecnu ocenu studenata tog studijskog programa u toku te sk godine
-- oznaku i nzaiv studijskog programa
-- skolsku godinu ukupan broj upisanih na fakultet
-- procenat na smeru, prosek

WITH upisaniPoGoidni AS(
		SELECT indeks / 10000 godina, COUNT(*) brStud
		FROM dosije
		GROUP BY indeks / 10000),
	studijskiProgramStat AS(
		SELECT d.idprograma, indeks / 10000 godina, COUNT(*) brStud
		FROM dosije d
		GROUP BY d.idprograma, indeks / 10000),
	prosekPoProgramu AS (
		SELECT d.idprograma, i.skgodina, avg(i.ocena + 0.0) prosek
		FROM dosije d JOIN ispit i ON d.indeks = i.indeks
		WHERE ocena > 5 AND status= 'o'
		GROUP BY d.idprograma, i.skgodina)
SELECT sp.id, sp.naziv, upg.brStud, sps.brStud * 100.0 / upg.brStud,
		ppp.prosek
FROM studijskiprogram sp JOIN nivokvalifikacije nk
		ON sp.idnivoa = nk.id
	JOIN studijskiProgramStat sps ON sp.id = sps.idprograma
	JOIN prosekPoProgramu ppp ON sps.idprograma = ppp.idprograma
								AND sps.godina = ppp.skgodina
	JOIN upisaniPoGoidni upg ON upg.godina = sps.godina
WHERE nk.naziv = 'Osnovne akademske studije';