--4

--napomena: pre izvrsavanja naredbi namestiti da je podrazumevana shema DA

with  bez_desetke as (
 select skgodina, oznakaroka, count(distinct idpredmeta) broj_bez_10
 from ispit i
where not exists(select *
    	from ispit
    	where skgodina = i.skgodina and oznakaroka = i.oznakaroka
    	and ocena =10 and status='o' and idpredmeta = i.idpredmeta
    	)
group by skgodina, oznakaroka )
select naziv, count(distinct indeks) studenata, count (distinct idpredmeta) predmeta,
case when   count (distinct idpredmeta)  >0
	then coalesce(broj_bez_10,0)*100.0 / count (distinct idpredmeta)
	else 0
	end
procenat_onih_gde_nema_10
from ispitnirok ir
left join ispit i on ir.skgodina = i.skgodina and ir.oznakaroka = i.oznakaroka
left join bez_desetke bd on bd.skgodina = i.skgodina and bd.oznakaroka = i.oznakaroka
group by ir.skgodina, ir.oznakaroka, naziv, broj_bez_10
having count (distinct idpredmeta) <25;


--5

create table diplomirani (
indeks int not null primary key,
datum date,
prosek float,
duzina_studiranja smallint
);

insert into diplomirani 
select d.indeks, d.datdiplomiranja, avg(ocena*1.0), 
        days(datdiplomiranja)-days(datupisa)
from dosije d join STUDIJSKIPROGRAM s
		on d.idprograma=s.id
		   and s.obimespb/60=4
	join ispit i on d.indeks=i.indeks
	    and ocena>5 and status='o'
where datdiplomiranja is not null and d.indeks not in (select indeks
                     from ispit
                     where status='x')
group by d.indeks, datdiplomiranja, datupisa ;

merge into diplomirani dip
using (select d.indeks, s.naziv, datdiplomiranja datum, avg(ocena*1.0) prosek
from dosije d join STUDIJSKIPROGRAM s
		on d.idprograma=s.id
	join STUDENTSKISTATUS st 
		on d.idstatusa=st.id and st.naziv='Diplomirao'
	join ispit i on d.indeks=i.indeks
	    and ocena>5 and status='o'
group by d.indeks, s.naziv, datdiplomiranja ) as t
on dip.indeks=t.indeks
when matched and t.naziv='Matematika' then
  update
  set prosek=t.prosek
when not matched then
  insert (indeks, datum, prosek)
  values(t.indeks, t.datum, t.prosek);

delete from diplomirani
where indeks in (select indeks
                     from dosije
                     where year(datupisa)<2018);

drop table diplomirani;

--6

select d.indeks, trim(d.ime)||' '||trim(d.prezime), s.naziv
from dosije d join studijskiprogram s on d.idprograma=s.id
where s.naziv in ( 'Numericka matematika i optimizacija', 'Astronomija i astrofizika', 'Analiza') 
	  and exists(select *
		         from ispit i join predmet p 
		             on i.idpredmeta=p.id and indeks=d.indeks and ocena>5 and status='o'
		             join predmetprograma pp on pp.idprograma=d.idprograma and i.idpredmeta=pp.idpredmeta
		  		                                                        and vrsta='obavezan'
		         
		         where   p.espb = ( select max(p1.espb)
			       		           from ispit i1 join predmet p1 on i1.idpredmeta=p1.id
			       		          where indeks=d.indeks and ocena>5 and status='o'  
			       			  	        )
	              )
order by 1 desc;