--1
--select d.indeks, ime, prezime,  naziv, ocena
--       case when i.id_predmeta in (select id_predmeta
--                                   from obavezan_predmet op
--                                   where op.id_smera=d.id_smera) then 'obavezan'
--            else 'izborni'
--       end predmet
--from dosije d join ispit i on d.indeks=i.indeks
--       join predmet p on p.id_predmeta=i.id_predmeta
--where ocena>5 and status_prijave='o' and ime like 'P%' and month(dat_rodjenja) between 2 and 7;


--2

--create table predmet_student (
--  id_predmeta integer,
--  studenti smallint
--);


--insert into predmet_student
--select id_predmeta, 5
--from obavezan_predmet
--where id_smera=201;

--merge into predmet_student ps
--using (select id_predmeta, count(*) n
--       from ispit
--       where ocena>5 and status_prijave='o'
--       group by id_predmeta) as p
--on ps.id_predmeta=p.id_predmeta
--when matched then
--  update set
--    ps.studenti=p.n
--when not matched then
--  insert 
--  values (p.id_predmeta, p.n);

--3

--create table student_podaci (
--  indeks integer,
--  broj_predmeta smallint,
--  prosek float,
--  dat_rodjenja date);

--insert into student_podaci
--select indeks, count(*), avg(ocena+0.0), null
--from ispit
--where ocena>5 and status_prijave='o'
--group by indeks
--having avg(ocena+0.0)>8
--union
--select indeks, 10, 10.00, null
--from dosije
--where status='diplomirao';


--merge into student_podaci sp
--using (select d.indeks, status, dat_rodjenja, count(i.id_predmeta) brp, avg(ocena+0.0) prosek
--       from dosije d left outer join ispit i on d.indeks=i.indeks and ocena>5 and status_prijave='o'
--       group by   d.indeks, status, dat_rodjenja) as p
--on sp.indeks=p.indeks
--when matched and p.status='diplomirao' then
--  update set
--    sp.dat_rodjenja=p.dat_rodjenja
--when matched and p.status='ispisan' then
-- delete
--when matched and p.status='budzet' then
--  update set
--    (sp.broj_predmeta, sp.prosek)=(p.brp, p.prosek)
--when not matched and p.status<>'ispisan' then
--  insert (indeks, broj_predmeta, prosek)
--  values (p.indeks, p.brp, p.prosek)
--else ignore;



--4
--with uslovni(id_predmeta, id_uslovnog) as (
--select id_predmeta, id_uslovnog
--from uslovni_predmet
--where id_predmeta=662
--union all
--select up.id_predmeta, up.id_uslovnog
--from uslovni, uslovni_predmet up
--where uslovni.id_uslovnog=up.id_predmeta)
--select distinct u.id_predmeta, p1.naziv, id_uslovnog, p2.naziv
--from uslovni u  join predmet p1
--      on p1.id_predmeta=u.id_predmeta
--     join predmet p2
--       on p2.id_predmeta=u.id_uslovnog;

--5
--with uslovni(nivo, id_predmeta, id_uslovnog) as (
--select 0, id_predmeta, id_uslovnog
--from uslovni_predmet
--where id_predmeta=662
--union all
--select uslovni.nivo+1, up.id_predmeta, up.id_uslovnog
--from uslovni, uslovni_predmet up
--where uslovni.id_uslovnog=up.id_predmeta)
--select distinct nivo, u.id_predmeta, p1.naziv, id_uslovnog, p2.naziv
--from uslovni u  join predmet p1
--      on p1.id_predmeta=u.id_predmeta
--     join predmet p2
--       on p2.id_predmeta=u.id_uslovnog
--where nivo>0;

--6
--with sp as (
--	select s.id_smera, s.oznaka, s.bodovi espbuk, sum(p.bodovi) espbob
--	from obavezan_predmet op
--		join predmet p
--			on p.id_predmeta = op.id_predmeta
--		right outer join smer s
--			on s.id_smera = op.id_smera
-- moze i ovako:
--	from smer s
--		left outer join obavezan_predmet op
--			on s.id = op.id_smera
--		left outer join predmet p
--			on p.id = op.id_predmeta		

--	group by s.id_smera, s.oznaka, s.bodovi
--),

--polozeno as (
--	select 
--		d.indeks, 
--		d.id_smera,
--		sum(coalesce(p.bodovi,0)) poluk, 
--		sum(case when op.id_predmeta is not null then p.bodovi else 0 end) polob
--	from ispit i 
--		join predmet p
--			on i.id_predmeta = p.id_predmeta
--			and i.ocena>5 
--			and i.status_prijave='o'
--		right outer join dosije d
--			on d.indeks = i.indeks
--		left outer join obavezan_predmet op
--			on op.id_smera = d.id_smera
--			and op.id_predmeta = i.id_predmeta
--	group by d.indeks, d.id_smera
--)

--select 
--	indeks, oznaka, 
--	espbuk, poluk, poluk*100.0/espbuk procuk,
--	espbob, polob, 
--	case when espbob>0 then polob*100.0/espbob else 0 end procob
--from polozeno p
--	join sp
--		on sp.id_smera = p.id_smera
--order by procuk;

--7
--with prolaznost as (
--select ir.naziv naziv_roka, p.id_predmeta,
--       p.naziv naziv_predmeta, count(i.indeks) brPrijavili,
--       sum(case when status_prijave='o' and ocena> 5 then 1 else 0 end) brPolozili,
--       sum(case when status_prijave='x' and ocena> 5 then 1 else 0 end) brPonistili
--from ispitni_rok ir left outer join ispit i
--       on ir.godina=i.godina_roka and ir.oznaka=i.oznaka_roka
--     left outer join predmet p
--       on p.id_predmeta=i.id_predmeta
--where ir.godina between 2005 and 2009
--group by ir.godina, ir.naziv, p.id_predmeta, p.naziv)
--select naziv_roka, naziv_predmeta, brPrijavili, brPolozili,
--       case when brPrijavili>0 then brPolozili*100.0/brPrijavili else 0 end "Procenat polozenih",
--       brPonistili, 
--       case when brPrijavili>0 then brPonistili*100.0/brPrijavili else 0 end "Procenat ponistenih",
--       case
--         when brPrijavili>0 and (brPolozili+brPonistili)*100.0/brPrijavili > 80 then 'odlicna'
--         when brPrijavili>0 and (brPolozili+brPonistili)*100.0/brPrijavili > 40 then 'srednja'
--         else 'losa'
--       end as rang
--from prolaznost
--order by rang;

