--1
create table student_ispiti (
  indeks integer not null primary key,
  broj_polozenih_ispita smallint,
  prosek double,
  constraint si_d foreign key (indeks) references da.dosije
);



--2
alter table student_ispiti
  add broj_prijavljenih_ispita smallint
  add constraint ispiti check(broj_prijavljenih_ispita>=broj_polozenih_ispita);

--3
insert into student_ispiti
select indeks, sum(case when ocena>5 and status='o' then 1 else 0 end) broj_polozenih, 
               avg(case when ocena>5 and status='o' then ocena*1.0 else null end),
               count(*) broj_prijavljenih 
from da.ispit
group by indeks;

--4		
drop table student_ispiti;

--5
--select d.indeks, d.ime, d.prezime, p.naziv
--from da.dosije d join da.STUDIJSKIPROGRAM sp
--		on d.idprograma=sp.id and sp.naziv='Informatika'
--	 join da.NIVOKVALIFIKACIJE nk 
--	 	on sp.idnivoa=nk.id and lower(nk.naziv) like 'osnovne%'
--	 join da.PREDMETPROGRAMA pp
--	 	on pp.idprograma=d.idprograma and vrsta='obavezan'
--	 join 	da.predmet p
--	 	on pp.idpredmeta=p.id
--
--where year(datupisa)=2015 and not exists (select *
--                                         from da.ispit i
--                                         where i.indeks= d.indeks and
--                                              i.idpredmeta=p.id and
--                                         ocena>5 and status='o');

--6
--create view ispitni_rok_2018 as 
--select skgodina, oznakaroka, naziv, 
--        datpocetka, datkraja 
--from da.ispitnirok 
--where skgodina >= 2018;
--


--select * from ispitni_rok_2018 ;
--

--select * from da.ISPITNIROK ;


--insert into ispitni_rok_2018 
--values(2020, 'dec', 'Decembar 2020', 
--   current date - 1 year, current_date - 1 year + 20 days) ;

--insert into ispitni_rok_2018 
--values(2015, 'dec', 'Decembar 2015', 
--   current date - 5 years, current_date - 5 years + 20 days) ;

--update ispitni_rok_2018 
--set naziv = 'Jan 2015'
--where skgodina = 2015 oznakaroka = 'jan1' ;
--napomena: nece biti promene jer se rok ne nalazi u redovima dobijenim izvrsavanjem pogleda 


--update ispitni_rok_2018 
--set naziv = 'Dec 2020'
--where skgodina = 2020 and trim(oznakaroka) = 'dec' ;

--select * from da.ISPITNIROK ;

--delete from ispitni_rok_2018
--where naziv= 'Decembar 2015';
--napomena: nece biti promene jer se rok ne nalazi u redovima dobijenim izvrsavanjem pogleda 


--delete from ispitni_rok_2018
--where naziv= 'Dec 2020';

--
--drop view ispitni_rok_2018;

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

--update mp_ispiti
--set datpolaganja=null;
--napomena: javlja se greska jer je u from klauzuli u definiciji pogleda izvrseno spajanje tabela

-- druga verzija pogleda

--create view mp_ispiti1 as 
--select * from da.ispit i
-- where exists (select * 
--               from da.dosije d 
--               where i.indeks = d.indeks and d.ime = 'Marko' 
--                     and d.prezime = 'Petrovic');

--update mp_ispiti1
--set datpolaganja=null;
--prolazi       

--select *
--from mp_ispiti1;

--8

--create view polaganja_m2 as
--select * from da.ispit i
--where exists (select * from da.dosije
--where indeks = i.indeks
--and ime in ('Marko','Marija'))
--and exists (select * from da.ispit
--where indeks = i.indeks and ocena > 5 and status = 'o'
--and oznakaroka = 'jan1' and skgodina = 2018 )
--with check option;

--unos ispita za studenta Marka koji zadovoljava neophodne uslove

--insert into polaganja_m2(indeks, idpredmeta, 
--skgodina, oznakaroka, status, poeni, ocena)
--values (20150013,2027,2018, 'sep2', 'o', 40, 5);


--student Jovana (ne zadovoljava uslove pogleda)
--select *
--from da.dosije
--where indeks = 20150001;

--unos njenog ispita koriscenjem pogleda ne prolazi 
--insert into polaganja_m2(indeks, idpredmeta, 
--skgodina, oznakaroka, status)
--values (20150001,1877,2016, 'jun1', 'n');

--ali prolazi sa tabelom ispit
--insert into da.ispit(indeks, idpredmeta, 
--skgodina, oznakaroka, status)
--values (20150001,1877,2016, 'jun1', 'n');


-- azuriranje unetog ispita koriscenjem pogleda prolazi za Marka sa indeksom 13/20015
--update polaganja_m2
--set datpolaganja = '10/25/2018'
--where indeks = 20150013 and 
--      idpredmeta = 2027 and 
--      skgodina= 2018 and oznakaroka = 'sep2';

-- azuriranje unetog ispita koriscenjem pogleda 
-- ne prolazi za Jovanu jer ne zadovoljava uslove iz pogleda 

--update polaganja_m2
--set datpolaganja = '6/25/2016'
--where indeks = 20150001 and 
--      idpredmeta = 1877 and 
--      skgodina= 2016 and oznakaroka = 'jun1';

-- ali prolazi koriscenjem tabele 

--update da.ispit
--set datpolaganja = '6/25/2016'
--where indeks = 20150001 and 
--      idpredmeta = 1877 and 
--      skgodina= 2016 and oznakaroka = 'jun1';


--brisanje ispita koriscenjem pogleda prolazi za Marka
--delete from polaganja_m2 
--where indeks = 20150013 and 
--      idpredmeta = 2027 and 
--      skgodina= 2018 and oznakaroka = 'sep2';


--brisanje ispita koriscenjem pogleda ne prolazi za Jovanu
-- jer ne zadovoljava uslove iz pogleda 

--delete from polaganja_m2 
--where indeks = 20150001 and 
--      idpredmeta = 1877 and 
--      skgodina= 2016 and oznakaroka = 'jun1';


--9
--create index dosije_ime_prezime on da.dosije 
--(ime desc, prezime desc); 

--drop index dosije_ime_prezime;


--10

select ir.naziv
from da.ispitnirok ir
where not exists(select *
			from da.ispit i1 join da.dosije d1 on i1.indeks=d1.indeks
			 where i1.skgodina=ir.skgodina 
			      and i1.oznakaroka=ir.oznakaroka 
			      and month(d1.datupisa) in (11,12)
				 and not exists(select *
				 		  from da.ispit i2 join da.dosije d2 on i2.indeks=d2.indeks
				 		  where i2.skgodina=ir.skgodina 
				 		        and i2.oznakaroka=ir.oznakaroka 
				 		        and i2.idpredmeta=i1.idpredmeta 
				 		        and i2.ocena>5  and status='o'
				 		        and month(d2.datupisa) in (9,10)))
                                
                                
    -- ako zelimo da osiguramo da postoji bar jedan predmet koji zadovoljava trazene uslove u tekstu
    -- dodajemo i naredni uslov     
	and  exists(select *
			from da.ispit i1 join da.dosije d1 on i1.indeks=d1.indeks
			 where i1.skgodina=ir.skgodina 
			      and i1.oznakaroka=ir.oznakaroka 
			      and month(d1.datupisa) in (11,12))
		;