--1
select ir1.naziv, ir2.naziv, p.naziv, d1.indeks, d2.indeks
from da.ISPITNIROK ir1 join da.ispit i1
		 on ir1.oznakaroka=i1.oznakaroka and ir1.skgodina=i1.skgodina
		 join da.dosije d1 on i1.indeks=d1.indeks
		   join da.predmet p
		   	on i1.idpredmeta=p.id and espb<3,
	 da.ISPITNIROK ir2 join da.ispit i2
		 on ir2.oznakaroka=i2.oznakaroka and ir2.skgodina=i2.skgodina
		 join da.dosije d2 on i2.indeks=d2.indeks
		 
where days(ir1.DATKRAJA) - days(ir1.DATPOCETKA) = days(ir2.DATKRAJA) - days(ir2.DATPOCETKA)
      and days(ir1.DATKRAJA) - days(ir1.DATPOCETKA) between 5 and 25
      and ir1.naziv<>ir2.naziv
      and i1.idpredmeta=i2.idpredmeta 
      and i1.indeks<i2.indeks
      and i1.ocena in (6, 8, 9)
      and i1.ocena=i2.ocena
      and d1.mestorodjenja=d2.mestorodjenja
      and d1.mestorodjenja like '_e_g%';
	  
--2 
	with student_smer as (
select idprograma, d.indeks
from da.dosije d join da.STUDIJSKIPROGRAM sp
      on d.idprograma=sp.id
       join da.ispit i 
		on d.indeks=i.indeks and status='o' and ocena>5
		join da.predmet p
		on p.id=i.idpredmeta
group by idprograma, d.indeks, sp.OBIMESPB
having sum(p.espb) >=  sp.OBIMESPB/2 ),
smer_brstudenata as (
select idprograma, count brstudenata
from student_smer
group by idprograma)

 select nk.naziv nivo, ss.naziv smer,  ss.OBIMESPB,
        count upisalo,
        avg(case when s.naziv='Diplomirao' then 100.0 else 0 end) diplomiralo,
        avg(case when s.naziv='Ispisan' then 100.0 else 0 end) ispisano,
        brstudenata*100.0/count pola_espb_polozilo
 from da.dosije d join da.STUDENTSKISTATUS s 
 	on d.idstatusa=s.id
 	join da.STUDIJSKIPROGRAM ss 
 	on d.idprograma=ss.id
 	join da.NIVOKVALIFIKACIJE nk
 	on ss.idnivoa=nk.id
 	left join smer_brstudenata sb
 		on ss.id=sb.idprograma
 group by nk.naziv , ss.naziv ,  ss.OBIMESPB, brstudenata;
 
--3

drop table student_generecije;

create table student_generecije (
id_smera int not null,  
godina int not null,
indeks int not null,
prosek float,
duzina_studiranja int,
 primary key (indeks)	
);


alter table student_generecije
	add foreign key fk_dosije (indeks) references da.dosije 
			on delete cascade
	add constraint prosek_vrednost check (prosek>=8.5);

insert into student_generecije (id_smera, godina, indeks)
with student_pod as (
select d.idprograma, 
case when month(datdiplomiranja)>9 then year(datdiplomiranja)
      else year(datdiplomiranja)-1 end  godina, 
d.indeks,
avg(ocena*1.0) * sm.obimespb/60 * 365 / (days(datdiplomiranja) - days(datupisa)) rez_formule

from da.dosije d join da.STUDENTSKISTATUS s on 
            d.idstatusa=s.id and s.naziv='Diplomirao'
		join da.ispit i on i.indeks=d.indeks and ocena>5 and status='o'
		join da.STUDIJSKIPROGRAM sm on sm.id=d.idprograma
group by d.idprograma, datdiplomiranja, obimespb, datupisa, d.indeks
having avg(ocena*1.0)>8.5
)
select idprograma, godina, indeks
from student_pod sp1
where rez_formule = (select max(rez_formule)
                                 from student_pod sp2
                                 where sp2.idprograma=sp1.idprograma
                                           and sp2.godina = sp1.godina) ;

update student_generecije sg
set prosek = (select avg(ocena*1.0)
                   from da.ispit i 
                   where i.indeks=sg.indeks
                        and ocena>5 and status='o'),
      duzina_studiranja = (select days(datdiplomiranja) - days(datupisa)
                                    from da.dosije d
                                    where datdiplomiranja is not null
                                           and d.indeks=sg.indeks);

delete from student_generecije sg
where exists (select *
              from da.ispit i 
              where i.indeks=sg.indeks and status='d')
              
       or (select count
           from da.ispit i join da.dosije d 
           		on d.indeks=i.indeks 
           		join da.PREDMETPROGRAMA pp
           		on pp.idprograma=d.idprograma and pp.idpredmeta=i.idpredmeta
           		and vrsta='obavezan'
           where d.indeks=sg.indeks and ocena=5) > 5 ;

--4

--resenje za smanjenu bazu podataka

--ra

--define alias i1 for ispit
--define alias i2 for ispit


--predmet[naziv, idpredmeta] 						
--join 
--(((ispit where ocena =5) [idpredmeta]                   
--minus
-- ((i1 where ocena =5) [indeks, idpredmeta]				
--times 
--(i2 where ocena =5) [indeks,idpredmeta]               
--  where i1.indeks < i2.indeks and i1.idpredmeta = i2.idpredmeta) 
--  ) [i1.idpredmeta]
--intersect 
--(ispit[indeks, idpredmeta]                               
--divideby
--dosije[indeks])) [naziv]

-- rr

--range of px is predmet    
--range of ix is ispit
--range of iy is ispit      
--range  of dx is dosije         

--px.naziv								
--where exists ix( ix.ocena = 5 and ix.idpredmeta = px.idpredmeta 
--  and not exists iy ( iy.ocena = 5 and iy.idpredmeta = px.idpredmeta 
--  and ix.indeks <> iy.indeks ))                   
--and not exists dx(not exists ix (                  
--ix.indeks = dx.indeks and px.idpredmeta = ix.idpredmeta))           


