--1
--SELECT INDEKS, MAX(COALESCE(DATUM_USMENOG, DATUM_PISMENOG)) POSLEDNJI
--FROM DB.ISPIT
--WHERE OCENA > 5 AND STATUS_PRIJAVE='o'
--GROUP BY INDEKS;

--2
--WITH INDEKSBODOVA AS (SELECT INDEKS, SUM(KREDITI) BODOVI
--FROM DB.ISPIT I JOIN DB.PREDMET P ON I.ID_PREDMETA=P.ID
--WHERE OCENA > 5 AND STATUS_PRIJAVE='o'
--GROUP BY INDEKS)
--SELECT D.INDEKS, IME, PREZIME, BODOVI
--FROM DB.DOSIJE D JOIN INDEKSBODOVA ON D.INDEKS=INDEKSBODOVA.INDEKS
--WHERE BODOVI = (SELECT MAX(BODOVI)
--                FROM INDEKSBODOVA);

--4
--SELECT D.IME, D.PREZIME, P.NAZIV
--FROM DB.DOSIJE D JOIN DB.OBAVEZAN_PREDMET OP 
--       ON D.ID_SMERA=OP.ID_SMERA
--     JOIN DB.PREDMET P 
--       ON P.ID=OP.ID_PREDMETA
--WHERE D.ID_SMERA=201 AND YEAR(DAT_UPISA)=2008
--AND NOT EXISTS (SELECT *
--                FROM DB.ISPIT I
--                WHERE I.INDEKS= D.INDEKS AND
--                      I.ID_PREDMETA=OP.ID_PREDMETA AND
--                      OCENA>5 AND STATUS_PRIJAVE='o');

--5
--WITH NEPOLOZENI AS (
--SELECT D.INDEKS, D.IME, D.PREZIME,P.ID, P.NAZIV 
--FROM DB.DOSIJE D JOIN DB.OBAVEZAN_PREDMET OP 
--       ON D.ID_SMERA=OP.ID_SMERA
--     JOIN DB.PREDMET P 
--       ON P.ID=OP.ID_PREDMETA
--WHERE D.ID_SMERA=201 AND D.INDEKS/10000=2008 AND  
--NOT EXISTS ( SELECT *
--             FROM DB.ISPIT I
--             WHERE I.INDEKS= D.INDEKS AND
--                   I.ID_PREDMETA=OP.ID_PREDMETA AND
--                   OCENA>5 AND STATUS_PRIJAVE='o'))

--SELECT NEPOLOZENI.IME, NEPOLOZENI.PREZIME, 
--NEPOLOZENI.NAZIV PREDMET, P.NAZIV
--FROM NEPOLOZENI JOIN DB.USLOVNI_PREDMET UP 
--       ON NEPOLOZENI.ID=UP.ID_PREDMETA 
--     JOIN DB.PREDMET P 
--       ON P.ID=UP.ID_USLOVNOG  
--WHERE UP.ID_USLOVNOG NOT IN ( SELECT ID_PREDMETA
--                              FROM DB.ISPIT I
--                              WHERE INDEKS=NEPOLOZENI.INDEKS AND
--                                    OCENA>5 AND STATUS_PRIJAVE='o')
--ORDER BY NEPOLOZENI.PREZIME, NEPOLOZENI.IME;

--6

--WITH A AS (
--SELECT ID_NIVOA, ID_PREDMETA, COUNT(*) N
--FROM DB.SMER S JOIN DB.OBAVEZAN_PREDMET OP 
--       ON S.ID=OP.ID_SMERA
--GROUP BY ID_NIVOA, ID_PREDMETA),
--B AS (
--SELECT ID_NIVOA, COUNT(*) N 
--FROM DB.SMER
--GROUP BY ID_NIVOA)
--SELECT NK.NAZIV,  P.NAZIV
--FROM A JOIN B 
--       ON A.ID_NIVOA=B.ID_NIVOA
--     JOIN DB.NIVO_KVALIFIKACIJE NK 
--       ON NK.ID=A.ID_NIVOA
--     JOIN DB.PREDMET P 
--       ON A.ID_PREDMETA=P.ID
--WHERE A.N=B.N
--ORDER BY 1;


-- II nacin

--SELECT DISTINCT KV.NAZIV, P.NAZIV
--FROM DB.OBAVEZAN_PREDMET OP JOIN DB.SMER S1
--       ON OP.ID_SMERA=S1.ID
--     JOIN DB.NIVO_KVALIFIKACIJE KV
--	ON KV.ID=S1.ID_NIVOA
--     JOIN DB.PREDMET P
--	ON OP.ID_PREDMETA=P.ID
--WHERE NOT EXISTS( SELECT * 
--                  FROM DB.SMER S
--	          WHERE S.ID_NIVOA=S1.ID_NIVOA
--	          AND NOT EXISTS( SELECT * 
--                                  FROM DB.OBAVEZAN_PREDMET P1
--		                  WHERE OP.ID_PREDMETA=P1.ID_PREDMETA
--		                        AND P1.ID_SMERA=S.ID));

--6a

--update db.dosije
--set status='diplomirao'
--where id_smera = ( select id
--          		       from db.smer
--		       where naziv='Informatika')
--and 180 <= ( select sum(p.krediti)
--	          from db.ispit i join db.predmet p
--			on i.id_predmeta=p.id
--	          where indeks = db.dosije.indeks and ocena > 5 and status_prijave='o');

--6b 
--delete from db.ispit
--where godina=2008 and indeks in ( select indeks
--				         from db.dosije
--				         where status='mirovanje');

--7
with predmetstatistika as (
select id_predmeta id, 
sum(case when ocena>5 then 1 else 0 end) brP, 
count(*) uk
from db.ispit
where status_prijave='o'
group by id_predmeta)
select p.naziv,  (a.brP+0.0)/a.uk prolaznost
from db.predmet p join predmetstatistika a on p.id=a.id
order by prolaznost desc;

