--1. izdvojiti indek, ime, prezime kao i status
-- status: -brucos ako nije polagao ni jedan ispit
-- 		 - nijedan polozen ako nije polozio ni jedan ispit(a nije bruzos)
--        - student
SELECT d.indeks, d.ime, d.prezime, CASE
									 WHEN NOT EXISTS(SELECT * FROM ispit i
									 				 WHERE i.indeks = d.indeks)
						 				 THEN 'brucos'
					 				 WHEN NOT EXISTS(SELECT * FROM ispit i
									 				 WHERE i.indeks = d.indeks
									 				 	AND i.ocena > 5 AND i.status = 'o')
					 				 	 THEN 'nijedan polozen'
				 				 	 ELSE 'student'
			 				 	    END
FROM dosije d;

-- 2. Koliko ima studenata koji su polozili vise od 10 espb

SELECT COUNT(*) br_studenata
FROM (SELECT i.indeks, sum(p.espb) br_bodova
	 FROM ispit i JOIN predmet p ON i.idpredmeta = p.id
	 WHERE i.ocena > 5 AND i.status = 'o'
	 GROUP BY i.indeks)
WHERE br_bodova > 10;

WITH statistike AS (
	SELECT i.indeks, sum(p.espb) br_bodova
 	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 statistike
WHERE br_bodova > 10;

-- 3. laki < 6 espb, srednje teski izmedju 6 i 8 inace teski
--    nadjemo koliko ima kojih predmeta?
WITH tezina_predmeta AS (
	SELECT p.id, CASE
					WHEN p.espb < 6 THEN 'laki'
					WHEN p.espb BETWEEN 6 AND 8 THEN 'srednje teski'
					ELSE 'teski'
				END tezina
	FROM predmet p
)
SELECT tezina, COUNT(*) br_predmeta
FROM tezina_predmeta
GROUP BY tezina;

-- 4. za svaki predmet izdvojiti njegov id, br razlicitih studena koji su ga polagali
-- uz idenifikatore predmeta koje niko nije polaga izdvojiti 0

-- iz tabele ispit za predmete koji su nekad polagani izvucemo br studenata
-- za one koji nisu nikada polagani stavimo 0

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

-- BEZ UNIJE!
-- za svaki ispit broj studentaat koji ga je polozio <-
SELECT p.id, SUM(CASE 
					WHEN i.idpredmeta IS NOT NULL THEN 1
					ELSE 0
				 END)
FROM predmet p LEFT JOIN ispit i 
		ON p.id = i.idpredmeta
		AND i.ocena > 5 AND i.status > 'o'
GROUP BY p.id
ORDER BY 2;

--
WITH br_polaganja AS(
	SELECT i.idpredmeta, COUNT(DISTINCT i.indeks) br_studenata
	FROM ispit i
	GROUP BY i.idpredmeta
)
SELECT p.id, COALESCE(br_studenata, 0)
FROM predmet p LEFT JOIN br_polaganja bp
	ON p.id = bp.idpredmeta;

-- 5. za svakog studenta izdvojiti ime, prezime i br ispita koje je pao

SELECT d.ime, d.prezime, COUNT(*) as br_padova
FROM ispit i JOIN dosije d
	ON i.indeks = d.indeks
WHERE i.ocena = 5 AND i.status = 'o'
GROUP BY d.indeks, d.ime, d.prezime
UNION
SELECT d.ime, d.prezime, 0
FROM dosije d
WHERE d.indeks NOT IN (SELECT indeks FROM ispit
						WHERE ocena= 5 AND status = 'o');


WITH padovi AS (
	SELECT i.indeks, COUNT(idPredmeta) as br_padova
	FROM ispit i
	WHERE i.ocena = 5 AND i.status = 'o'
	GROUP BY i.indeks
)
SELECT d.ime, d.prezime, COALESCE(br_padova, 0)
FROM dosije d LEFT JOIN padovi p ON d.indeks = p.indeks;

-- 6. izdvojiti broj studenata koji su polozili neke predmete
-- u bar 2 razlicita roka

WITH pom AS(
	SELECT DISTINCT indeks, skgodina, oznakaroka
	FROM ispit
	WHERE ocena > 5 AND status = 'o'
),
pom2 AS( SELECT indeks
		 FROM pom
     	 GROUP BY indeks
		 HAVING COUNT(*) >= 2)
 SELECT COUNT(*)
 FROM pom2;

WITH pom AS(
	SELECT indeks, COUNT(DISTINCT (skgodina || oznakaroka)) br_rokova
	FROM ispit
	WHERE ocena > 5 AND status = 'o'
	GROUP BY indeks
)
SELECT COUNT(*)
FROM pom
WHERE br_rokova >=2;

-- izdvojiti indekse studenata koji su rodjeni u istom gradu kao
-- oni studenti koji su upisani na matf pre vise od  4 godine

WITH validni_gradovi AS(
	SELECT mestorodjenja
	FROM dosije
	WHERE YEAR(current date -  datupisa) = 4
)
SELECT d.indeks
FROM dosije d
WHERE d.mestorodjenja IN (SELECT * FROM validni_gradovi);

			
-- IZDVOJITI NAZIVE ISPITNIH ROKOVA U KOJIMA:
-- 	nijedan student nije pao ispit koji nosi 8 espb ili
--  postoji neki drugi ispitni rok iz iste godine u kome 
--  je neki student pao ispit koji nosi 8 espb

WITH osam_espb AS(
	SELECT id FROM predmet
	WHERE espb = 8
)
SELECT ir.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 i.skgodina = ir.skgodina AND i.oznakaroka <> ir.oznakaroka
				  	AND ocena = 5 AND status = 'o' AND i.idpredmeta IN (SELECT * FROM osam_espb));