-- Odrediti redosled studenta u zavisnosti od trenutnog proseka njihovih ocena

with proseci(indeks, ime, prezime, prosek)
as
(
 select a.indeks, ime, prezime, dec(avg(ocena*1.0),4,2)
 from   ispit a, dosije b 
 where  ocena >5
 and    a.indeks=b.indeks
 and    a.datum_ispita=(select max(datum_ispita)
                        from   ispit c
                        where  a.indeks=c.indeks
                        and    a.id_predmeta=c.id_predmeta 
                       )
 group by a.indeks,ime,prezime
)
select indeks,ime, prezime, prosek, rank() over(order by prosek desc) as redosled
from   proseci 
order  by indeks; 


insert into ispit(indeks, id_predmeta, godina_roka, oznaka_roka, ocena, datum_ispita, bodovi)  values 
(20140022, 3001, 2015, 'jan', 7, '28.01.2015', 72);
       
with proseci(indeks, ime, prezime, prosek)
as
(
 select a.indeks, ime, prezime, dec(avg(ocena*1.0),4,2)
 from   ispit a, dosije b 
 where  ocena >5
 and    a.indeks=b.indeks
 and    a.datum_ispita=(select max(datum_ispita)
                        from   ispit c
                        where  a.indeks=c.indeks
                        and    a.id_predmeta=c.id_predmeta 
                       )
 group by a.indeks,ime,prezime
)
select indeks,ime, prezime, prosek, rank() over(order by prosek desc) as "Redosled"
from   proseci 
order  by indeks; 


