--1.izdvojiti informacije o svim predmetima koji nose najvise bodova
SELECT *
FROM predmet p
WHERE p.espb >= ALL(SELECT espb FROM predmet);

-- SELECT espb FROM predmet je nekorelisani podupit -> moze sam za sebe da se izvrsi

--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 p.espb < SOME(SELECT espb FROM predmet);

-- ANY i SOME rade iste stvari

--(NOT)EXISTS(SELECT....) <- proverava postoji li neki rezultat ovog podupita
SELECT *
FROM predmet p
WHERE EXISTS(SELECT *
			 FROM predmet p1
			 WHERE p1.espb > p.espb);

-- sada imamo KORELISANI podupit <- uglavnom manje efikasno

--3. 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

--4. izdvojiti nazive predmeta koje je polagao student sa indeksom 20170022

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

-- moramo distinct jer je mozda neke ispite polagao vise puta

--4. - sa podupitima

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

--4. sa operatorom ANY
 
SELECT p.naziv
FROM predmet p JOIN ispit i
	ON p.id = i.idpredmeta
WHERE 2017022 = ANY(SELECT i.indeks
					FROM ispit i1
					WHERE i1.idpredmeta = p.id);

-- sa nekorelisanim podupitom:

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

-- IN

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

--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);

--drugi nacin

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));

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

SELECT d.indeks
FROM dosije d
WHERE NOT EXISTS(SELECT *
                 FROM ispit i1 
                 WHERE i1.indeks = 20140026 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))


--8. izdvojiti naziv predmeta ciji je kurs organizovan 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
					));

--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;



--CASE IZARAZ


--11. za svaki ispit izdvojiti indeks, idpredmeta , dobijenu ocenu. 
-- vrednost ocene ispisati slovima. ako je predmet nepolozen, umesto ocene ispisi nepolozen

SELECT i.indeks, i.idpredmeta, CASE i.ocena
				WHEN 6 THEN 'sest'
				WHEN 7 THEN 'sedam'
				WHEN 8 THEN 'osam'
				WHEN 9 THEN 'devet'
				WHEN 10 THEN 'deset'
				ELSE 'nepolozen'
				END AS ocena
FROM ispit i;

--12. ispit je lak ako nosi < 6 espb
--		srednje tezak  ako nosi izmedju 6 i 8 espb
-- 		tezak ako nosi > 8
--   izdvojiti nazive predmeta, broj espb koji nosi kao i njihovu tezinu

SELECT naziv, espb, CASE
					    WHEN espb < 6 THEN 'lak'
						WHEN espb BETWEEN 6 AND 8 THEN 'srednje tezak'
						ELSE 'tezak'
					END AS 'TEZINA ISPITA'
FROM predmet;


--12. izdvojiti ime, prezime, indeks, status za svakog studenta.
-- status studenta moze biti:
--	brucos (nije polagao nijedan ispit)
--	nijedan polozen (nema polozenih predmeta)
--	student (inace)
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 status
FROM dosije d;