--1
--SELECT S.SRBR, SIME
--FROM SPJ JOIN S ON SPJ.SRBR=S.SRBR 
--WHERE KOL>=200
--EXCEPT
--SELECT S.SRBR, SIME
--FROM SPJ JOIN S ON SPJ.SRBR=S.SRBR 
--WHERE KOL<200;


-- 2a

--WITH POLOZENO AS (SELECT I.ID_PREDMETA,  I.SEMESTAR, COUNT(*) BRUP
--FROM DB.ISPIT I JOIN DB.DOSIJE D 
--       ON D.INDEKS=I.INDEKS
--     JOIN DB.SMER S 
--       ON S.ID=D.ID_SMERA
--WHERE OCENA>5 AND STATUS_PRIJAVE='o' AND S.NAZIV='Informatika' AND GODINA=2006
--GROUP BY I.ID_PREDMETA, I.SEMESTAR),
--UPISANO AS (SELECT ID_PREDMETA, SEMESTAR, COUNT(*) N
--FROM DB.UPISAN_KURS UK JOIN DB.DOSIJE D 
--       ON D.INDEKS=UK.INDEKS
--     JOIN DB.SMER S 
--       ON S.ID=D.ID_SMERA
--WHERE S.NAZIV='Informatika' AND GODINA=2006
--GROUP BY ID_PREDMETA, SEMESTAR)
--SELECT P.SIFRA, P.NAZIV,  UPISANO.SEMESTAR,  N BROJUPISANIH, COALESCE(BRUP, 0) BRUP, COALESCE(BRUP, 0)*100.0/N PROCENATUSPESNOSTI
--FROM UPISANO JOIN DB.PREDMET P 
--       ON UPISANO.ID_PREDMETA=P.ID
--     LEFT OUTER JOIN POLOZENO 
--      ON UPISANO.ID_PREDMETA=POLOZENO.ID_PREDMETA  AND UPISANO.SEMESTAR=POLOZENO.SEMESTAR 
--ORDER BY 6 DESC;  


-- drugo resenje

--WITH A AS (SELECT UK.ID_PREDMETA,  UK.SEMESTAR, COUNT(DISTINCT UK.INDEKS) BRUP, COUNT(DISTINCT I.INDEKS) N
--FROM  DB.DOSIJE D JOIN DB.SMER S 
--        ON S.ID=D.ID_SMERA
--      JOIN DB.UPISAN_KURS UK 
--        ON D.INDEKS=UK.INDEKS
--      LEFT OUTER JOIN DB.ISPIT I  
--        ON UK.INDEKS=I.INDEKS  AND UK.ID_PREDMETA=I.ID_PREDMETA AND UK.SEMESTAR=I.SEMESTAR AND UK.GODINA=I.GODINA
--           AND OCENA>5 AND STATUS_PRIJAVE='o' 
--WHERE  S.NAZIV='Informatika' AND UK.GODINA=2006
--GROUP BY UK.ID_PREDMETA, UK.SEMESTAR)
--SELECT P.SIFRA, P.NAZIV,  A.SEMESTAR,   BRUP BROJUPISANIH, COALESCE(N, 0) BROJPOLOZENIH, COALESCE(N, 0)*100.0/BRUP PROCENATUSPESNOSTI
--FROM A JOIN DB.PREDMET P ON A.ID_PREDMETA=P.ID
--ORDER BY (COALESCE(N, 0)+0.0)/BRUP  DESC;  

--2b

--WITH UPISANO AS (SELECT D.ID_SMERA, S.OZNAKA OZNAKA_SMERA, S.NAZIV NAZIV_SMERA, UK.ID_PREDMETA, COUNT(*) BRS
--FROM DB.DOSIJE D 
--     JOIN DB.SMER S 
--       ON D.ID_SMERA=S.ID
--     JOIN DB.NIVO_KVALIFIKACIJE NK 
--       ON NK.ID=S.ID_NIVOA
--     JOIN DB.UPISAN_KURS UK 
--       ON D.INDEKS=UK.INDEKS
--     JOIN DB.OBAVEZAN_PREDMET OP 
--       ON S.ID=OP.ID_SMERA AND UK.ID_PREDMETA=OP.ID_PREDMETA
--WHERE UK.GODINA=2007 AND NK.STEPEN='VI'
--GROUP BY  D.ID_SMERA, S.OZNAKA, S.NAZIV, UK.ID_PREDMETA),
--PONOVLJENO AS (SELECT D.ID_SMERA, UK.ID_PREDMETA, COUNT(DISTINCT D.INDEKS) SPU
--FROM DB.DOSIJE D 
--     JOIN DB.SMER S 
--       ON D.ID_SMERA=S.ID
--     JOIN DB.NIVO_KVALIFIKACIJE NK 
--       ON NK.ID=S.ID_NIVOA
--     JOIN DB.UPISAN_KURS UK 
--       ON D.INDEKS=UK.INDEKS
--     JOIN DB.OBAVEZAN_PREDMET OP 
--       ON S.ID=OP.ID_SMERA AND UK.ID_PREDMETA=OP.ID_PREDMETA
--WHERE UK.GODINA=2007 AND NK.STEPEN='VI'
--      AND EXISTS ( SELECT *
--                   FROM DB.UPISAN_KURS UK2
--                   WHERE UK2.INDEKS=D.INDEKS AND UK2.ID_PREDMETA=UK.ID_PREDMETA
--                         AND UK2.GODINA < 2007)
--GROUP BY  D.ID_SMERA, UK.ID_PREDMETA)
--SELECT OZNAKA_SMERA, NAZIV_SMERA, P.NAZIV NAZIV_PREDMETA, BRS UKUPAN_BROJ_STUDENATA_UPISANIH, SPU, SPU*100.0/BRS PROCENAT
--FROM UPISANO 
--     JOIN PONOVLJENO ON UPISANO.ID_SMERA=PONOVLJENO.ID_SMERA AND UPISANO.ID_PREDMETA=PONOVLJENO.ID_PREDMETA
--     JOIN DB.PREDMET P ON UPISANO.ID_PREDMETA=P.ID
--ORDER BY SPU*100.0/BRS;

--2c
--WITH POLOZENO AS (SELECT D.INDEKS,  SUM(P.KREDITI) Polozeno
--FROM DB.DOSIJE D  
--     JOIN DB.UPISAN_KURS UK ON D.INDEKS=UK.INDEKS 
--     JOIN DB.ISPIT I ON (UK.INDEKS=I.INDEKS AND I.GODINA=UK.GODINA AND UK.ID_PREDMETA=I.ID_PREDMETA)
--     JOIN DB.PREDMET P ON I.ID_PREDMETA=P.ID
--WHERE OCENA>5 AND STATUS_PRIJAVE='o' AND UK.GODINA=2006
--GROUP BY  D.INDEKS),
--UPISANO AS (SELECT UK.INDEKS,  SUM(KREDITI) UPISANO
--FROM DB.UPISAN_KURS UK  JOIN DB.PREDMET P ON UK.ID_PREDMETA=P.ID
--WHERE GODINA=2006
--GROUP BY UK.INDEKS),
--KATEGORIJA AS (SELECT  COUNT(*) BRS,  SUM(CASE WHEN UPISANO.UPISANO-COALESCE(POLOZENO.Polozeno,0) BETWEEN 1 AND 6 THEN 1 ELSE 0 END) IIKATEGORIJA,
--SUM(CASE WHEN UPISANO.UPISANO-COALESCE(POLOZENO.Polozeno,0) BETWEEN 7 AND 12 THEN 1 ELSE 0 END) IIIKATEGORIJA,
--SUM(CASE WHEN UPISANO.UPISANO-COALESCE(POLOZENO.Polozeno,0) BETWEEN 13 AND 18 THEN 1 ELSE 0 END) IVKATEGORIJA,
--SUM(CASE WHEN UPISANO.UPISANO-COALESCE(POLOZENO.Polozeno,0)BETWEEN 19 AND 23 THEN 1 ELSE 0 END) VKATEGORIJA,
--SUM(CASE WHEN UPISANO.UPISANO-COALESCE(POLOZENO.Polozeno,0) BETWEEN 24 AND 30 THEN 1 ELSE 0 END) VIKATEGORIJA,
--SUM(CASE WHEN UPISANO.UPISANO-COALESCE(POLOZENO.Polozeno,0)>30 THEN 1 ELSE 0 END) VIIKATEGORIJA
--FROM UPISANO LEFT OUTER JOIN  POLOZENO 
--       ON POLOZENO.INDEKS=UPISANO.INDEKS )
--SELECT  BRS, (BRS- IIKATEGORIJA - IIIKATEGORIJA - IVKATEGORIJA - VKATEGORIJA- VIKATEGORIJA- VIIKATEGORIJA) POLOZILISVE,
--CAST((BRS- IIKATEGORIJA - IIIKATEGORIJA - IVKATEGORIJA - VKATEGORIJA- VIKATEGORIJA- VIIKATEGORIJA) AS FLOAT)*100/BRS PROCENATSVE, 
--IIKATEGORIJA "BRS 1-6",
--CAST(IIKATEGORIJA AS FLOAT)*100/BRS "1-6", 
--IIIKATEGORIJA "BRS 7-12",
--CAST(IIIKATEGORIJA*100 AS FLOAT)/BRS "7-12",
--IVKATEGORIJA "BRS 13-18",
--CAST(IVKATEGORIJA*100 AS FLOAT)/BRS "13-18",
--VKATEGORIJA "BRS 19-23",
--CAST(VKATEGORIJA*100 AS FLOAT)/BRS "19-23",
--VIKATEGORIJA "BRS 24-30",
--CAST(VIKATEGORIJA*100 AS FLOAT)/BRS "24-30",
--VIIKATEGORIJA "BRS PREKO 30",
--CAST(VIIKATEGORIJA*100.0/BRS AS DECIMAL(5,3)) "PREKO 30"
--FROM KATEGORIJA;

--3
--CREATE DISTINCT TYPE PROSEK AS FLOAT WITH COMPARISONS;

--create table db.prosek (
--indeks int not null,
--prosek prosek,
--primary key (indeks),
--foreign key fk_dosije(indeks)
--references db.dosije
--on delete cascade
--);

--CREATE FUNCTION PROSEK(indeks_studenta INT)
--RETURNS PROSEK
--RETURN SELECT AVG(CAST(OCENA AS FLOAT))
--       FROM DB.ISPIT I
--       WHERE I.indeks=indeks_studenta
--             AND ocena>5 AND STATUS_PRIJAVE='o';

--DELETE FROM DB.prosek
--WHERE INDEKS NOT IN ( SELECT INDEKS
--                      FROM DB.UPIS_GODINE);

CREATE TRIGGER azuriranje_proseka 
  AFTER UPDATE OF OCENA, STATUS_PRIJAVE ON DB.ISPIT
  REFERENCING NEW AS promenjeno
  FOR EACH ROW
  BEGIN ATOMIC
    UPDATE DB.PROSEK
    SET PROSEK=PROSEK(promenjeno.INDEKS)
    WHERE INDEKS=promenjeno.INDEKS;
  END@

