-- db2 -td@ -f putanja

--1
--CREATE TRIGGER brisanje_studenta
--              NO CASCADE BEFORE DELETE
--              ON db.dosije
--              REFERENCING OLD AS stari
--              FOR EACH ROW 
--              WHEN(stari.status <> 'ispisan')
--              BEGIN ATOMIC
--                SIGNAL SQLSTATE '75000' 
--      ('Student ciji status nije ispisan ne moze se obrisati.');
--              END@

--INSERT INTO DB.DOSIJE (INDEKS, ID_SMERA, STATUS, 
--IME, PREZIME, POL, JMBG, DAT_UPISA)
--VALUES(20090208, 201, 'budzet', 'Marko', 'Markovic',
--'m', '1205989035819', current_date)@

--DELETE FROM DB.DOSIJE
--WHERE INDEKS=20090208@

--UPDATE DB.DOSIJE
--SET STATUS='ispisan'
--WHERE INDEKS=20090208@

--select * from db.dosije where indeks=20090208@

--DELETE FROM DB.DOSIJE
--WHERE INDEKS=20090208@


--2
--drop table broj_predmeta@

--drop trigger novi_pred@
--drop trigger brisanje_pred@

--CREATE TABLE broj_predmeta(
--broj SMALLINT
--)@

--INSERT INTO broj_predmeta 
--SELECT COUNT(*) 
--FROM db.predmet@

--SELECT *
--FROM broj_predmeta@

--CREATE TRIGGER novi_pred 
--                   AFTER INSERT 
--                   ON db.predmet
--                   FOR EACH ROW
--                   BEGIN ATOMIC
--                         UPDATE broj_predmeta
--                         SET broj = broj + 1;
--                   END@

--CREATE TRIGGER brisanje_pred 
--                   AFTER DELETE 
--                   ON db.predmet
--                   FOR EACH ROW
--                   BEGIN ATOMIC
--                         UPDATE broj_predmeta
--                         SET broj = broj - 1;
--                         END@

--SELECT *
--FROM broj_predmeta@

--INSERT INTO db.predmet (id, sifra, naziv, semslus, krediti) 
--      VALUES(2006, 'PRED1', 'Predmet 1', 1, 15)@

--SELECT *
--FROM BROJ_PREDMETA@

--DELETE FROM db.predmet
--WHERE ID=2006@

--SELECT *
--FROM BROJ_PREDMETA@

--3
--drop trigger promena_kredita@

--create trigger promena_kredita 
--      no cascade before update of krediti on db.predmet
--referencing old as staro new as nova
--for each row 
--begin atomic
-- SET nova.krediti= CASE 
--                       WHEN staro.krediti > nova.krediti THEN staro.krediti-1
--                       WHEN staro.krediti < nova.krediti THEN staro.krediti+1
--                       ELSE staro.krediti
--                     END;  
--end@

--delete from db.predmet where id=1605@

--INSERT INTO db.predmet(id, sifra, naziv, semslus, krediti)
--              VALUES(1605, 'PRED 5', 'Predmet 1',  1, 4)@


--SELECT *
--FROM DB.PREDMET
--WHERE ID=1605@

--UPDATE DB.PREDMET
--SET KREDITI=20
--WHERE ID=1605@

--SELECT *
--FROM DB.PREDMET
--WHERE ID=1605@

--UPDATE DB.PREDMET
--SET KREDITI=2
--WHERE ID=1603@

--SELECT *
--FROM DB.PREDMET
--WHERE ID=1602@

--DROP TRIGGER promena_kredita@



--rekurzivni SQL

--4
--with uslovni(id_predmeta, id_uslovnog) as (
--
--select id_predmeta, id_uslovnog
--from db.uslovni_predmet
--where id_predmeta = 662

--union all

--select up.id_predmeta, up.id_uslovnog
--from uslovni, db.uslovni_predmet up
--where uslovni.id_uslovnog=up.id_predmeta)
--
--select id_predmeta, substr(p1.naziv,1,25), id_uslovnog, p2.naziv
--from uslovni u  join db.predmet p1
--      on p1.id=u.id_predmeta
--     join db.predmet p2
--       on p2.id=u.id_uslovnog@


--5
--with uslovni(nivo, id_predmeta, id_uslovnog) as (

--select 1, id_predmeta, id_uslovnog
--from db.uslovni_predmet
--where id_predmeta=662

--union all

--select uslovni.nivo+1, up.id_predmeta, up.id_uslovnog
--from uslovni, db.uslovni_predmet up
--where uslovni.id_uslovnog=up.id_predmeta)

--select nivo, id_predmeta, substr(p1.naziv,1,20), id_uslovnog, p2.naziv
--from uslovni u  join db.predmet p1
--      on p1.id=u.id_predmeta
--     join db.predmet p2
--       on p2.id=u.id_uslovnog
--where nivo>1 AND NIVO<10@

--vezbanje upita

--6
with na_smeru as (
select id_smera, count(distinct d.indeks) n, 
avg(ocena+0.0) ocena
from db.dosije d join db.smer s
       on id_smera = s.id
     join db.nivo_kvalifikacije nk
       on nk.id = s.id_nivoa and nk.stepen = 'VI'
     left outer join db.ispit i
       on d.indeks = i.indeks and status_prijave='o' 
              and ocena>5
       and godina = 2006
where year(dat_upisa) = 2006
group by id_smera
),
svi as (
select sum(n) n
from na_smeru
)
select
s.oznaka, SUBSTR(s.naziv,1,25) naziv_smera, svi.n ukupno, 
us.n  nasmer, DEC(us.n * 100.0 / svi.n,5,2) proc, 
DEC(ocena,4,2)
from db.smer s join na_smeru us
       on us.id_smera = s.id
     , svi
order by ocena@