--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(datum_rodjenja) between 2 and 7;


--2
--create index dosije_ime_prezime on dosije (ime desc, prezime desc); 

--drop index dosije_ime_prezime;


--3

--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);
--  
-- select *
-- from predmet_student;

--4

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

--delete from student_podaci;
--
--insert into student_podaci
--select indeks, count(*), avg(ocena+0.0), null
--from ispit 
--where ocena>5 and status_prijave='o'
--and indeks not in (select indeks
--                  from status
--                  where status='diplomirao')
--group by indeks
--having avg(ocena+0.0)>8
--union
--select d.indeks, 10, 10.00, null
--from dosije d join status s on d.indeks=s.indeks
--where status='diplomirao';
--
--
--merge into student_podaci sp
--using (select d.indeks, status, datum_rodjenja, 
--       count(i.id_predmeta) brp, avg(ocena+0.0) prosek
--       from dosije d join status s on d.indeks=s.indeks
--       left outer join ispit i on d.indeks=i.indeks 
--               and ocena>5 and status_prijave='o'
--       where datum = (select max(datum)
--                      from status 
--                      where indeks=d.indeks)
--       group by   d.indeks, status, datum_rodjenja) as p
--on sp.indeks=p.indeks
--when matched and p.status='diplomirao' then
--  update set
--    sp.dat_rodjenja=p.datum_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;


--5
--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;

--6

--update upis_godine ug
--set upisano_bodova=(select coalesce(sum(p.bodovi),0)
--                    from upisan_kurs uk join predmet p on p.id_predmeta=uk.id_predmeta
--                    where  uk.indeks=ug.indeks 
--                           and uk.godina=ug.godina),
--   overeno_bodova = (select coalesce(sum(p.bodovi),0)
--                    from ispit i join predmet p on p.id_predmeta=i.id_predmeta
--                    where  i.indeks=ug.indeks 
--                           and i.godina=ug.godina
--                           and ocena>5 and status_prijave='o'),
--    datum_upisa='20.9.2010',
--    datum_overe='25.9.2011'
--where ug.godina=2010 and indeks in ( select indeks
--                                     from dosije d join smer s
--                                         on d.id_smera=s.id_smera
--                                       join nivo_kvalifikacije nk
--                                         on s.id_nivoa=nk.id_nivoa
--                                     where nk.stepen='I');


--7

--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;