-- broj studenata, min ime, max indeks

SELECT COUNT(*) as br_studenata, MIN(ime) as najmanje_ime, MAX(indeks) as max_indeks
FROM dosije;

-- ne brojimo NULL - za neki atribut ili ako zelimo da brojimo raz vrednosti:

SELECT COUNT(*) as br_studenata, COUNT(datdiplomiranja), COUNT(DISTINCT mestorodjenja)
FROM dosije;

-- koliko ima razlicitih pozitivnih ocena u bazi
SELECT COUNT(DISTINCT NULLIF(ocena, 5))
FROM ispit; 


-- izdvojimo br indeks i bro espb poena koji su polozili za studenata koji su izasli na ispit
SELECT i.indeks, SUM(p.espb)
FROM ispit i JOIN predmet p
	ON i.idpredmeta = p.id
WHERE i.ocena > 5 AND i.status = 'o'
GROUP BY i.indeks;

-- za studente koji su sakupili bar 20 espb
-- izdvojimo ukupan broj espb koji su osvojili
SELECT i.indeks, SUM(p.espb) br_polozenih_espb
FROM ispit i JOIN predmet p
	ON i.idpredmeta = p.id
WHERE i.ocena > 5 AND i.status = 'o'
GROUP BY i.indeks
HAVING SUM(p.espb) > 20
ORDER BY 2;

-- izdvojiti ime, prezime, indeks i broj espb koji su studenti polozili
SELECT i.indeks, d.ime, d.prezime, SUM(p.espb)
FROM ispit i JOIN predmet p
			ON i.idpredmeta = p.id
	JOIN
	dosije d
	on i.indeks = d.indeks 
WHERE i.ocena > 5 AND i.status = 'o'
GROUP BY i.indeks, d.ime, d.prezime;

-- izdvojiti proseke studenata
SELECT d.indeks, d.ime, d.prezime, AVG(ocena + 0.0)
FROM ispit i RIGHT JOIN dosije d
	ON i.indeks = d.indeks AND	i.ocena > 5 AND i.status = 'o'
GROUP BY  d.indeks, d.ime, d.prezime;

-- za svaki od ispitnih rokova i za svaki polagani predmet u tom roku
-- odrediti broj uspesnih polaganja (uzimati samo rokove i predmete koji postoje u tabeli ispit)

SELECT i.skgodina, i.oznakaroka, i.idpredmeta, COUNT(*)
FROM ispit i
WHERE ocena > 5 AND status = 'o'
GROUP BY i.skgodina, i.oznakaroka, i.idpredmeta
ORDER BY 1, 2, 3;

-- za svaki predmet treba izracunati koliko je studenata polozilo taj predmet

SELECT p.id, p.oznaka, 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, p.oznaka
ORDER BY 1, 2;

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



-- izdvojiti indeks, ime, prezime studenata koji su polozili bar 3 predmeta


SELECT d.indeks, d.ime, d.prezime, COUNT(*)
FROM dosije d JOIN ispit i
	ON d.indeks = i.indeks
WHERE i.ocena > 5 AND i.status = 'o'
GROUP BY d.indeks, d.ime, d.prezime
HAVING COUNT(*) >= 3;



-- izdvoijit indeks tudenata koji su polozili bar 3 predmeta
-- i id predmeta koji su polozili 3 studenta..

SELECT i.indeks as id
FROM ispit i
WHERE i.ocena > 5 AND i.status = 'o'
GROUP BY i.indeks
HAVING COUNT(*) >= 3
UNION
SELECT i.idpredmeta as id
FROM ispit i
WHERE i.ocena > 5 AND i.status = 'o'
GROUP BY i.idpredmeta
HAVING COUNT(*) >= 3
ORDER BY 1 DESC;

-- za sve studente koji su polagali neki ispit
-- izdvojiti koliko su dobili osmica i koliko su dobili devetki

SELECT d.indeks, (SELECT COUNT (*) 
				  FROM ispit i2 
				  WHERE i2.indeks = d.indeks 
				    AND i2.ocena = 8 AND i2.status = 'o') "BROJ OSMICA", (SELECT COUNT (*) 
																	  FROM ispit i2 
																	  WHERE i2.indeks = d.indeks 
																	    AND i2.ocena = 9 AND i2.status = 'o') "BROJ DEVETKI" 
FROM dosije d
WHERE EXISTS(SELECT * FROM ispit i WHERE i.indeks = d.indeks);

SELECT i.indeks, SUM(CASE ocena
						WHEN 8  THEN 1
						ELSE 0
					 END) AS osmice  , SUM(CASE ocena
											WHEN 9 THEN 1
											ELSE 0
										 END) AS devetke, SUM(
										 					CASE COALESCE(ocena, 5)
										 						WHEN 5 THEN 1
										 						ELSE 0
										 				    END
										 					) as padanja
FROM ispit i
GROUP BY i.indeks;


-- za predmet koji je prvi polagan na fakultetu 
-- izdvojiti oznaku predmeta, ime i prezime studenata koji su ga ikada upisali
SELECT p.oznaka, d.ime, d.prezime
FROM predmet p, dosije d
WHERE
	EXISTS(SELECT * FROM uspisankurs k WHERE k.idpredmeta = p.id AND k.indeks = d.indeks)
	AND
		EXISTS(SELECT * FROM ispit i
			   WHERE i.idpredmeta = p.id
			   		AND NOT EXISTS(SELECT *
			   						FROM ispit i2
			   						WHERE i.datpolaganja > i2.datpolaganja))