-- za svaki predmet izdovijit koliko je studenata polozilo taj predmet

SELECT p.id, p.oznaka, (SELECT COUNT(*) FROM ispit i
						WHERE i.idpredmeta = p.id 
							AND i.ocena > 5 AND i.status = 'o')
FROM predmet p;

-- SLOZENI UPITI
--WITH ime_tabele AS (
--	UPIT
--)
--SELECT *
--FROM ime_tabele

-- 1. predmeti su rasporedjeni u sledece kategorije:
-- 			1. laki - espb < 6 
--			2. srednje teskie espbe izmedju 6 i 8
-- 			3. teski inace
-- 		izdvojiti broj predmeta za svaku kategoriju

SELECT CASE
		  WHEN espb < 6 THEN 'laki'
		  WHEN espb BETWEEN 6 AND 8 THEN 'srednje teski'
		  ELSE 'teski'
	  END as tezina, COUNT(*)
  FROM predmet
  GROUP BY CASE
		  WHEN espb < 6 THEN 'laki'
		  WHEN espb BETWEEN 6 AND 8 THEN 'srednje teski'
		  ELSE 'teski'
	  END;
-- napravimo tabelu u kojo imamo id_predmeta i njegovu tezinu
WITH klasifikacija AS(
	SELECT CASE
		  	WHEN espb < 6 THEN 'laki'
		  	WHEN espb BETWEEN 6 AND 8 THEN 'srednje teski'
		  	ELSE 'teski'
 	 	   END as tezina, id
  	FROM predmet
)
SELECT tezina, COUNT(*)
FROM klasifikacija
GROUP BY tezina;

-- 2. koliko je studenata polozilo vise od 20 espb

WITH polozeno AS (
	SELECT i.indeks, SUM(p.espb) polozeno_poena
	FROM ispit i JOIN predmet p
		ON i.idpredmeta = p.id
	WHERE i.ocena > 5 AND i.status = 'o'
	GROUP BY i.indeks 
)
SELECT COUNT(*)
FROM polozeno
WHERE polozeno_poena > 20;

-- 3. Naci broj ispitnih rokova u kojima su studenti polozili
-- bar 2 razlicita predmeta
WITH rokovi AS (
	SELECT i.skgodina, i.oznakaroka, COUNT(DISTINCT i.idpredmeta) broj_polozenih_ispita
	FROM ispit i
	WHERE i.ocena > 5 AND i.status = 'o'
	GROUP BY  i.skgodina, i.oznakaroka
)
SELECT COUNT(*)
FROM rokovi
WHERE broj_polozenih_ispita >= 2;

SELECT COUNT(*) FROM ispitnirok

-- 4. uz svaki predmet izdvojiti id i br studenata koji su ga polagali
-- a) sa agregatnim funkcijama
-- b) skupovnim operatorima
-- v) pomocnim tabelama

--b)

SELECT i.idpredmeta, COUNT(DISTINCT indeks) br_studenata
FROM ispit i
GROUP BY i.idpredmeta
UNION
SELECT p.id, 0
FROM predmet p
WHERE NOT EXISTS(SELECT * FROM ispit i WHERE i.idpredmeta = p.id)
ORDER BY 2 DESC;

-- a)
SELECT p.id, COUNT(DISTINCT indeks)
FROM predmet p LEFT JOIN ispit i
	ON p.id = i.idpredmeta
GROUP BY p.id;

-- c) pomocna tabela?
WITH statistika_ispit AS(
	SELECT i.idpredmeta, COUNT(DISTINCT indeks) br_studenata
	FROM ispit i
	GROUP BY i.idpredmeta
)
SELECT p.id, coalesce(br_studenata, 0) 
FROM predmet p LEFT JOIN statistika_ispit si
	ON p.id = si.idpredmeta;


-- 5. za svakog studenta izdvojiti broj ispita koje je pao
-- 	  ako nije pao ni jedan ispit staviti 0

-- c) pomocna tabela
WITH pali_ispiti AS
(SELECT i.indeks as indeks , SUM(CASE coalesce(i.ocena,5)
									 WHEN 5 THEN 1 
									 ELSE 0 
								 END ) AS broj_padanja 
 FROM ispit i 
 GROUP BY i.indeks)
SELECT d.ime, d.prezime, coalesce(pi.broj_padanja, 0) 
FROM dosije d LEFT JOIN pali_ispiti pi
	ON d.indeks = pi.indeks;
 
-- b) skupovne opetacije

SELECT d.ime, d.prezime, COUNT(*)
FROM dosije d JOIN ispit i
	ON d.indeks = i.indeks
WHERE i.ocena = 5 OR i.ocena is NULL
GROUP BY d.indeks, d.ime, d.prezime
UNION 
SELECT d.ime, d.prezime, 0
FROM dosije d 
WHERE NOT EXISTS(SELECT * FROM ispit i 
				WHERE i.indeks = d.indeks 
					AND( i.ocena = 5 OR i.ocena is null));
					
-- 6. izdvojiti broj studenata koji su polzili neke predmete
-- u bar 2 razlicita roka
WITH broj_rokova AS (
	SELECT indeks, COUNT(DISTINCT (char(skgodina) || oznakaroka)) br_rokova
	FROM ispit
	WHERE ocena > 5 AND status = 'o'
	GROUP BY indeks
)
SELECT COUNT(*)
FROM broj_rokova
WHERE br_rokova >= 2;



-- bez pomocnih tabela!

SELECT COUNT(DISTINCT i.indeks)
FROM ispit i
WHERE i.ocena > 5 AND i.status = 'o'
	AND EXISTS(SELECT *
				FROM ispit i1
				WHERE i1.indeks = i.indeks
					 AND i1.ocena > 5 AND i1.status = 'o'
					 AND (i1.skgodina <> i.skgodina OR i1.oznakaroka <> i.oznakaroka));

----------- VEZBA ------------------
-- 2 napisati upit u SQL kojim se izdvajaju nazivi ispitnih rokova
--  	u kojima nijedan student nije pao na ispitu iz predmeta koji nosi 8 espb
--		ili za taj rok postoji neki drugi rok odrzan u istoj godini
--		u kome je barem jedan student pao neki predmet od 8 bodova
-- 	    urediti prema nazivu roka;
--
--    padanje <=> ocena = 5
--
WITH osam_espb AS(
	SELECT id
	FROM predmet
	WHERE espb = 8
)
SELECT naziv
FROM ispitnirok ir
WHERE NOT EXISTS(SELECT * FROM ispit i
				WHERE i.skgodina = ir.skgodina AND i.oznakaroka = ir.oznakaroka
					AND ocena = 5 AND status = 'o'
					AND i.idpredmeta IN (SELECT * FROM osam_espb))
	OR EXISTS(SELECT * FROM ispit i
			WHERE ocena = 5 AND status = 'o'
				AND i.skgodina = ir.skgodina AND i.oznakaroka <> ir.oznakaroka
				AND i.idpredmeta IN (SELECT * FROM osam_espb));

-- izdvojiti indekse studentaa koji su rodjeni u istom gradu kao oni
-- studenti koji su upisani na matf pre vise od 4 godine
SELECT d.indeks
FROM dosije d
WHERE EXISTS(SELECT * FROM dosije d2
			 WHERE d2.mestorodjenja = d.mestorodjenja
			 	AND d2.datupisa + 5 YEARS + 5 MONTHS < CURRENT DATE );
YEARS(current_date - datupisa) > 4...