-- 1. izdvojiti ukupan broj studenata, min i max
SELECT COUNT(*)  broj, MIN(indeks) min_indeks, MAX(indeks) max_indeks
FROM dosije;

-- 2. izdvojiti broj diplomiranih studenata
SELECT COUNT(datdiplomiranja)
FROM dosije;

-- 3. koliko razlicitih mesta rodjenja ima na fakulte
SELECT COUNT(DISTINCT mestorodjenja)
FROM dosije;

-- SUM AVG

-- 4. za studente koji su nesto polozili izdvojimo
-- br indeksa kao i broj sakupljenih espb
-- kao i ime i prezime
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;

-- 5. isto kao 4. samo hocemo da izdvojimo informacije
-- samo za one studente koji imaju bar 20 espb
SELECT i.indeks, d.ime, d.prezime, SUM(p.espb) osvojeni_poeni
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
HAVING SUM(p.espb) >= 20
ORDER BY osvojeni_poeni;

-- 6. izdvojiti ime, prezime, indeks kao i broj espb za svekog studenta 
-- broj polozenih ispit:
SELECT d.indeks, d.ime, d.prezime, SUM(CASE
											WHEN p.espb IS NULL THEN 0
											ELSE p.espb
										END) osvojeni_poeni
FROM ispit i JOIN predmet p
	ON i.idpredmeta = p.id
	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
HAVING SUM(CASE
			WHEN p.espb IS NULL THEN 0
			ELSE p.espb
  		   END) > 20
ORDER BY osvojeni_poeni;

-- 7. za svaki predmet izdvojiti koliko ga je studenata polozilo
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;

-- 8. za svakog studenta koji je nesto polozio iracunati prosek
SELECT d.indeks, d.ime, d.prezime, AVG(i.ocena + 0.0) prosek
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
ORDER BY 4;

-- 9, za svaki od isptinih rokova i za svaki polagani predmet u rom roku odredimo
--    broj uspesnih polaganja
SELECT i.idpredmeta, i.skgodina, i.oznakaroka, SUM(
												 CASE
												 	WHEN i.ocena > 5 AND i.status = 'o'
												 			THEN 1
												    ELSE 0
												 END
												) 
FROM ispit i
GROUP BY i.idpredmeta, i.skgodina, i.oznakaroka;
-- OVIME BI ELIMINSALI ONE KOJI NEMAJU USPESNIH POLAGANJA;
SELECT i.idpredmeta, i.skgodina, i.oznakaroka, COUNT(*)
FROM ispit i
WHERE i.ocena > 5 AND i.stauts = 'o'
GROUP BY i.idpredmeta, i.skgodina, i.oznakaroka;


-- 7'. za svaki predmet izdvojiti koliko ga je studenata polozilo
SELECT p.id, p.oznaka, COUNT(*)
FROM predmet p JOIN ispit i
	ON p.id = i.idpredmeta 
WHERE i.ocena > 5 AND i.status = 'o'
GROUP BY p.id, p.oznaka
UNION
SELECT p.id, p.oznaka, 0
FROM predmet p
WHERE p.id NOT IN (SELECT idpredmeta
				   FROM ispit
				   WHERE ocena > 5 AND status = 'o');

-- 10. za svakog studenta koji je polagao neki ispit
--     izdvojiti koliko je dobio osmica

SELECT d.indeks, d.ime, d.prezime, COUNT(*) br_osmica
FROM dosije d JOIN ispit i
	ON d.indeks = i.indeks
WHERE i.ocena = 8
GROUP BY d.indeks, d.ime, d.prezime;

SELECT d.indeks, d.ime, d.prezime, (SELECT COUNT(*)
									FROM ispit i
									WHERE i.indeks = d.indeks
										AND i.ocena = 8)
FROM dosije d;

-- dodamo broj devetki:
SELECT d.indeks, d.ime, d.prezime, SUM(
									CASE ocena
										WHEN 8 THEN 1
										ELSE 0
									END									
									) br_osmica, SUM(
													CASE ocena
														WHEN 9 THEN 1
														ELSE 0
													END									
													) br_devetki
FROM dosije d JOIN ispit i
	ON d.indeks = i.indeks
GROUP BY d.indeks, d.ime, d.prezime;


-- dodamo broj padanja padanje - dobio je 5 ili je ocena NULL:
SELECT d.indeks, d.ime, d.prezime, SUM(
									CASE ocena
										WHEN 8 THEN 1
										ELSE 0
									END									
									) br_osmica, 
									SUM(
										CASE ocena
											WHEN 9 THEN 1
											ELSE 0
										END									
									) br_devetki,  
									SUM( 
										CASE COALESCE(ocena, 5)
											WHEN 5 THEN 1
											ELSE 0
										END) br_padanja
FROM dosije d JOIN ispit i
	ON d.indeks = i.indeks
GROUP BY d.indeks, d.ime, d.prezime;

SELECT d.indeks, d.ime, d.prezime, SUM(
									CASE ocena
										WHEN 8 THEN 1
										ELSE 0
									END									
									) br_osmica, 
									SUM(
										CASE ocena
											WHEN 9 THEN 1
											ELSE 0
										END									
									) br_devetki,  
									SUM( 
										CASE 
											WHEN NULLIF(ocena, 5) IS NULL THEN 1
											ELSE 0
										END) br_padanja
FROM dosije d JOIN ispit i
	ON d.indeks = i.indeks
GROUP BY d.indeks, d.ime, d.prezime;


-- 11. ZA PREDMET KOJI JE PRVI POLAGAN
-- IZDVOJITI OZNAK PREDMETA, IME I PREZIME STUDENATA KOJI SU GA IKADA UPISALI

SELECT p.id, p.oznaka, d.ime, d.prezime
FROM predmet p, dosije d
WHERE EXISTS(SELECT * FROM upisankurs k 
			WHERE k.indeks = d.indeks
				AND k.idpredmeta = p.id)
	AND EXISTS(SELECT * FROM ispit i1
				WHERE i1.idpredmeta = p.id
					AND NOT EXISTS(SELECT *
									FROM ispit i2
									WHERE i2.datpolaganja < i1.datpolaganja));
SELECT p.id, p.oznaka, d.ime, d.prezime
FROM predmet p, dosije d
WHERE EXISTS(SELECT * FROM upisankurs k 
			WHERE k.indeks = d.indeks
				AND k.idpredmeta = p.id)
	AND EXISTS(SELECT * FROM ispit i1
				WHERE i1.idpredmeta = p.id
					AND i1.datpolaganja = (SELECT MIN(datpolaganja)
												FROM ispit));
				
NULIFF(x, y)		
-- 12. izdvojiti indeks, ime, prezime studenata koji su polozili bar 3 predmeta

SELECT d.indeks, d.ime, d.prezime
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;