-- 1. izdvojiti nazive predmeta koje je polagao student
-- sa indeksom 22/2017

SELECT p.naziv
FROM predmet p JOIN ispit i
    ON p.id = i.idpredmeta
WHERE i.indeks = 20170022;

SELECT p.naziv
FROM  predmet p
WHERE EXISTS(SELECT * 
            FROM ispit i
            WHERE i.indeks = 20170022
                AND i.idpredmeta = p.id);

-- ovde je koriscen KORELISANI podupit


SELECT p.naziv
FROM  predmet p
WHERE p.id IN (SELECT i.idpredmeta 
               FROM ispit i
               WHERE i.indeks = 20170022);

-- ovde je koriscen NEKORELISANI podupit

SELECT p.naziv
FROM  predmet p
WHERE 20170022 IN (SELECT i.idpredmeta 
               FROM ispit i
               WHERE i.idpredmeta = p.id);

-- ANY i SOME
SELECT p.naziv
FROM predmet p
WHERE p.id = ANY(SELECT i.idpredmeta
                 FROM ispit i
                 WHERE i.indeks = 20170022);

SELECT p.naziv
FROM predmet p
WHERE p.id = SOME(SELECT i.idpredmeta
                 FROM ispit i
                 WHERE i.indeks = 20170022);

-- 2. izdvojiti informacije o svim predmetima osim o onima
--    koji nose najvise bodova

SELECT *
FROM predmet p
WHERE p.espb < ANY(SELECT espb FROM predmet);

SELECT *
FROM predmet p
WHERE EXISTS(SELECT *
             FROM predmet p1
             WHERE p1.espb > p.espb);

-- 3. izdvojiti informacije o predmetima sa maksimalnim brojem poena
SELECT *
FROM predmet p
WHERE p.espb >= ALL(SELECT espb
                    FROM predmet);

--4. izdvojiti informacije o  najskorije diplomiranim studentima
--zelimo da njegov datum diplomiranja bude >= od svih ostalih datuma diplomiranja
SELECT *
FROM dosije d
WHERE d.datdiplomiranja >= ALL(SELECT datdiplomiranja 
                               FROM dosije 
							   WHERE datdiplomiranja IS NOT NULL);

-- BITNO: bez IS NOT NULL nece raditi zato sto x >= NULL je netacno

--5.izdvojiti indekse, imena i prezimena studenata koji nisu polagali predmet ciji je identifikator 2166

SELECT d.indeks, d.ime, d.prezime
FROM dosije d
WHERE d.indeks NOT IN (SELECT i.indeks
						FROM ispit i
						WHERE i.idpredmeta = 2166);

SELECT d.indeks, d.ime, d.prezime
FROM dosije d
WHERE NOT EXISTS (SELECT *
                  FROM ispit i
                  WHERE i.idpredmeta = 2166
                    AND i.indeks = d.indeks);

-- 6. izdvojiti indekse, imena i prezimena studenata koji su 
--    polagali neki predmet koji nosi 5 espb
SELECT DISTINCT d.indeks, d.ime, d.prezime
FROM dosije d JOIN ispit i
	ON d.indeks = i.indeks
	JOIN predmet p
		ON i.idpredmeta = p.id
WHERE p.espb = 5;

SELECT d.indeks, d.ime, d.prezime
FROM dosije d
WHERE d.indeks IN (SELECT i.indeks
                   FROM ispit i
				   WHERE i.idpredmeta IN (SELECT p.id
				                          FROM predmet p
										  WHERE p.espb = 5));

SELECT d.indeks, d.ime, d.prezime
FROM dosije d
WHERE EXISTS(SELECT *
             FROM ispit i JOIN predmet p
                ON i.idpredmeta = p.id
             WHERE i.indeks = d.indeks AND p.espb = 5);

--7. izdvojiti naziv predmeta koji su organizovani u svim godinama koje postoje u bazi podataka

SELECT p.naziv
FROM predmet p
WHERE NOT EXISTS(
	SELECT * FROM skolskagodina sg
	WHERE NOT EXISTS(SELECT * 
	                FROM kurs k
		            WHERE k.idpredmeta = p.id
		            AND sg.skgodina = k.skgodina
					));

--8. prikazi brojeve indeksa studenata koji su polozili bar one predmete 
--   koje i student sa brojem indeksa 20150168

SELECT d.indeks
FROM dosije d
WHERE NOT EXISTS(SELECT *
                 FROM ispit i1 
                 WHERE i1.indeks = 20150168 AND
		            i1.status = 'o' AND  i1.ocena > 5
		            AND NOT EXISTS (SELECT *
		                            FROM ispit i2 
		                            WHERE i2.indeks = d.indeks AND
		                                AND i2.idpredmeta = i1.idpredmeta
		                                  i2.status = 'o' AND  i2.ocena > 5));

SELECT d.indeks
FROM dosije d
WHERE NOT EXISTS(SELECT * FROM predmet p
                 WHERE EXISTS (SELECT * FROM ispit i
				               WHERE i.indeks = 20150168
							        AND i.ocena > 5 AND i.status = 'o'
									AND i.idpredmeta = p.id)
					AND NOT EXISTS (SELECT * FROM ispit i
					                WHERE i.indeks = d.indeks
									AND i.ocena > 5 AND i.status = 'o'
									AND i.idpredmeta = p.id))

--9. izdvojiti podatke o predmetima koje su polagali svi studenti iz Berana koji studiraju smer
--   sa oznakom I4

SELECT *
FROM predmet p
WHERE NOT EXISTS(SELECT *
                FROM dosije d JOIN studijskiprogram sp
				    ON d.idprograma = sp.id
			    WHERE d.mestorodjenja = 'Berane' AND sp.oznaka = 'I4'
				    AND NOT EXISTS(SELECT *
					               FROM ispit i
								   WHERE i.indeks = d.indeks
								        AND i.idpredmeta = p.id));

--10. za studente koji su polagali ispit u roku odrzanom 2018/2019 
--    izdvoji podatke o polozenim ispitima
--    izdvojiti indeks, ime, prezime, naziv ispita, oznaka rok, i skgodina

SELECT d.indeks, d.ime, d.prezime, p.naziv, i.oznakaroka, i.skgodina
FROM dosije d JOIN ispit i
	ON d.indeks = i.indeks
	JOIN predmet p
	ON i.idpredmeta = p.id
WHERE i.ocena > 5 AND i.status = 'o'
	AND EXISTS(SELECT * FROM ispit i1
	           WHERE i1.indeks = d.indeks
			        AND i1.skgodina = 2018)
ORDER BY d.indeks;