--1
--select indeks, max(coalesce(datum_usmenog, datum_pismenog)) poslednji
--from ispit
--where ocena > 5 and status_prijave='o'
--group by indeks;

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

--3
--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');



--4

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

--5
create view ispitni_rok_2011 as 
select godina, oznaka, naziv, pocetak_prijavljivanja, kraj_prijavljivanja 
from ispitni_rok 
where godina >= 2011;

select * from ispitni_rok_2011 ;

insert into ispitni_rok values(2014, 'jan', 'Januar 2014', '10.1.2014', '20.1.2014', '1');

insert into ispitni_rok_2011 
values(2015, 'dec', 'Decembar 2015', current date, current_date + 20 days) ;

update ispitni_rok_2011 
set naziv = 'Jan 2014'
 where godina = 2014 and oznaka = 'jan' ;


delete from ispitni_rok_2011
where naziv= 'Decembar 2015';

drop view ispitni_rok_2011;

--6
create view mp_ispiti as 
select i.* from ispit i join dosije d 
    on i.indeks = d.indeks 
where d.ime = 'Marko' and d.prezime = 'Petrovic' ;

update mp_ispiti
set napomena='Ispiti Marka Petrovica';

create view mp_ispiti1 as 
select * from ispit i
 where exists (select * 
                     from dosije d 
                     where i.indeks = d.indeks and d.ime = 'Marko' and d.prezime = 'Petrovic');
       
update mp_ispiti1
set napomena='Ispiti Marka Petrovica';
              
select *
from mp_ispiti1;

--7

create view polaganja_stud_kv_bg as
select i1.*
from dosije d join ispit i1 on d.indeks = i1.indeks
join ispit i2 on i2.indeks = i1.indeks
where ime in ('Marko','Maja') and i2.oznaka_roka = 'apr'
and i2.godina_roka = 2008 and i2.ocena > 5 and i2.status_prijave ='o';

create view polaganja_stud_kv_bg_2 as
select * from ispit i
where exists (select * from dosije
where indeks = i.indeks
and ime in ('Marko','Maja'))
and exists (select * from ispit
where indeks = i.indeks and ocena > 5 and status_prijave = 'o'
and oznaka_roka = 'apr' and godina_roka = 2008 )
with check option;

select *
from dosije
where indeks= 20100026;

insert into polaganja_stud_kv_bg_2(indeks, id_predmeta, godina, semestar,
godina_roka, oznaka_roka, datum_prijave, nacin_prijave)
values (20100026,2001,2011,1,2011,'okt','10/01/2011','auto');


insert into polaganja_stud_kv_bg_2(indeks, id_predmeta, godina, semestar,
godina_roka, oznaka_roka, datum_prijave, nacin_prijave)
values (20070143,635,2007,1,2008,'okt','10/01/2008','auto');

update polaganja_stud_kv_bg_2
 set datum_prijave = '10/25/2008'
 where indeks = 20070143 and id_predmeta = 635 and godina_roka = 2008 and oznaka_roka = 'okt';

delete from polaganja_stud_kv_bg_2 
where indeks = 20070143 and id_predmeta = 635 and godina_roka = 2008 and oznaka_roka = 'okt';