--1
select trim(ime) || ' ' || (case
	when ime_oca is not NULL and length(rtrim(ime_oca)) <> 0 then substr(ime_oca,1,1) || '. '
		when ime_majke is not nuLL and length(rtrim(ime_majke)) <> 0 then substr(ime_majke,1,1) || '. '
		else '' end) || prezime ime_i_prezime
from dosije d
where not exists (select *
	from upisan_kurs uk join obavezan_predmet op
		on op.id_predmeta = uk.id_predmeta
	where uk.indeks = d.indeks
		and op.id_smera = d.id_smera
		and not exists( select * from ispit
			where indeks =d.indeks and ocena >=8 and status_prijave = 'o' and id_predmeta = uk.id_predmeta )
		)
and not exists (select *
	from ispit
where indeks = d.indeks and ocena = 5 and status_prijave = 'o' and
coalesce(datum_usmenog, datum_pismenog) > current_date - 6 year);


--2

with godine as(
select ime, count(*) god
from upis_godine ug join dosije d
on ug.indeks=d.indeks
group by d.indeks, ime),

prosek_godina as
(select ime, decimal(avg(god*1.0),5,3) pr
from godine
group by ime),

datumi as(
select ime, min(coalesce(datum_usmenog, datum_pismenog)) min_dat
from ispit i join dosije d
on i.indeks=d.indeks
where ocena>5 and status_prijave='o'
group by ime)

select pp.ime,pp.pr,days(current date)-days(d.min_dat) br_dana
from prosek_godina pp left join datumi d on pp.ime=d.ime
order by ime;


--3

create table upisani_kursevi_2016 like upisan_kurs;

ALTER TABLE upisani_kursevi_2016
ADD PRIMARY KEY (indeks, id_predmeta, godina, semestar )
ADD FOREIGN KEY fk_pred (ID_PREDMETA) REFERENCES predmet
ADD FOREIGN KEY fk_dos (indeks) REFERENCES dosije;

insert into upisani_kursevi_2016
select distinct d.indeks, p.id_predmeta, 2016, 2
from dosije d join upisan_kurs uk
  on d.indeks = uk.indeks join predmet p on p.id_predmeta = uk.id_predmeta
where naziv like 'Uvod%'
and not exists (select * from ispit
    where ocena >5 and status_prijave = 'o' and indeks = d.indeks
 and id_predmeta = p.id_predmeta)
    and year(d.datum_upisa) >= 2011;


update upisani_kursevi_2016
set semestar =1
where semestar = 2 and indeks in (select indeks
    from dosije d join smer s on d.id_smera = s.id_smera join nivo_kvalifikacije nk
		    on nk.id_nivoa = s.id_nivoa
		    where nk.stepen = 'I');
    
-- a moze i sa merge

merge into upisani_kursevi_2016 uk
using (select indeks
    from dosije d join smer s on d.id_smera = s.id_smera join nivo_kvalifikacije nk
		    on nk.id_nivoa = s.id_nivoa
		    where nk.stepen = 'I') as p
on p.indeks = uk.indeks
when  matched and uk.semestar = 2 then
  update
   set semestar =1
else
 ignore  ;
 

drop table upisani_kursevi_2016;