
--1
--select s.naziv smer, p1.naziv obavezan_predmet, p2.naziv uslovni_predmet
--from nivo_kvalifikacije nk join smer s on s.id_nivoa=nk.id_nivoa
--       join  obavezan_predmet op on op.id_smera=s.id_smera
--       join predmet p1 on p1.id_predmeta=op.id_predmeta
--       left outer join uslovni_predmet up on up.id_predmeta=op.id_predmeta
--       left outer join predmet p2 on p2.id_predmeta=up.id_uslovnog
--where nk.stepen='I' 
--order by smer, obavezan_predmet;


--2
--select d.indeks, d.ime, d.prezime, p.naziv
--from dosije d join obavezan_predmet op on d.id_smera=op.id_smera
--     join predmet p on p.id_predmeta=op.id_predmeta
--     join smer s on s.id_smera=d.id_smera
--     join nivo_kvalifikacije nk on nk.id_nivoa=s.id_nivoa
--where nk.stepen='I' and s.naziv='Informatika' and year(datum_upisa)=2009 and not exists (select *
--                                                                                from ispit i
--                                                                                where i.indeks= d.indeks and
--                                                                                      i.id_predmeta=op.id_predmeta and
--                                                                                      ocena>5 and status_prijave='o');

--3

--I nacin

--with indeksbodova as (select indeks, sum(p.bodovi) polbodovi
--from ispit i join predmet p on i.id_predmeta=p.id_predmeta
--where ocena > 5 and status_prijave='o'
--group by indeks),
--maxsmer as (select id_smera, max(polbodovi) maxb
--from dosije d join indeksbodova on d.indeks=indeksbodova.indeks
--group by id_smera)
--
--select  s.naziv, d.indeks, ime, prezime, polbodovi
--from dosije d join indeksbodova on d.indeks=indeksbodova.indeks
--     join maxsmer on maxsmer.id_smera=d.id_smera join smer s on maxsmer.id_smera=s.id_smera
--where polbodovi = maxb;

--II nacin

--with indeksbodova as (select id_smera, d.indeks, ime, prezime, sum(p.bodovi) polbodovi
--from dosije d join ispit i on d.indeks=i.indeks
--     join predmet p on i.id_predmeta=p.id_predmeta
--where ocena > 5 and status_prijave='o'
--group by id_smera, d.indeks, ime, prezime)
--
--select  s.naziv, indeks, ime, prezime, polbodovi
--from indeksbodova ib join smer s on ib.id_smera=s.id_smera
--where polbodovi = (select max(polbodovi)
--                   from indeksbodova ib2
--                   where ib2.id_smera=ib.id_smera);



--4
--create type indeks as integer;

--5
--create table dosije1 (
--		  indeks indeks not null , 
--		  id_smera integer not null , 
--		  ime varchar(50) not null , 
--		  prezime varchar(50) not null ,  
--		  dat_upisa date not null,
--                  primary key (indeks),
--		  foreign key (id_smera) references smer);  


--6

--insert into dosije1
--select indeks, id_smera, ime, prezime, datum_upisa
--from dosije;


--7
--select d1.indeks + d2.indeks
--from dosije1 d1, dosije1 d2
--where d1.indeks=20100022 and d2.indeks=20100025;

--select integer(d1.indeks) + integer(d2.indeks)
--from dosije1 d1, dosije1 d2
--where d1.indeks=indeks(20100022) and d2.indeks=indeks(20100025);


--8
--create function godinaupisa(indeks indeks)
--returns integer
--return integer(indeks)/10000;

--9
--create function brojindeksa(indeks indeks)
--returns integer
--return mod(integer(indeks),10000);

--select godinaupisa(indeks), brojindeksa(indeks)
--from dosije1;

--10
--create function max (indeks)
--returns indeks
--source sysibm.max(integer);

--select max(indeks)
--from dosije1;


--11

--create type bodovi as smallint;

--create function obaveznibodovi(smer integer)
--returns bodovi
--return select sum(bodovi)
--       from obavezan_predmet op join predmet p 
--         on p.id_predmeta=op.id_predmeta
--       where op.id_smera=smer;

--create table polozenobodova 
--(
--  indeks integer not null,
--  polozenobodova bodovi,
--  primary key (indeks)
--);

--insert into polozenobodova
--select d.indeks, sum(p.bodovi)
--from dosije d join ispit i2 on d.indeks=i2.indeks
--       join predmet p on p.id_predmeta=i2.id_predmeta
--where not exists ( select *
--                   from obavezan_predmet op
--                   where op.id_smera=d.id_smera
--                         and not exists (select *
--                                         from ispit i
--                                         where i.id_predmeta=op.id_predmeta 
--                                               and d.indeks=i.indeks
--                                               and ocena>5 and status_prijave='o'))
--and ocena>5 and status_prijave='o'
--group by d.indeks; 

--create view studpolobav as (
--select d.indeks, ime, prezime, s.naziv, polozenobodova, obaveznibodovi(s.id_smera) obavezbodovi
--from dosije d join smer s on d.id_smera=s.id_smera
--     join polozenobodova pk on pk.indeks=d.indeks;
--);

--12
--with na_smeru as (
--select s.id_smera, count(distinct d.indeks) n, avg(ocena+0.0) ocena
--from  smer s
--                    join nivo_kvalifikacije nk
--           on nk.id_nivoa = s.id_nivoa and nk.stepen = 'I'
--       
--       left outer join dosije d
--        on d.id_smera = s.id_smera and year(datum_upisa) = 2006
--       left outer join ispit i
--         on d.indeks = i.indeks and status_prijave='o' and ocena>5 and godina = 2006
--group by s.id_smera ),
--svi as (
--select sum(n) n
--from na_smeru
--)
--select s.oznaka, s.naziv, svi.n ukupno, us.n nasmer, us.n * 100.0 / svi.n proc, ocena
--from smer s join na_smeru us
--           on us.id_smera = s.id_smera, svi
--order by ocena;