connect to stud2020;

drop table polozeni_ispiti;

create table polozeni_ispiti (
	Indeks			integer      not null,
	Ime				varchar(32)  not null,
	Prezime			varchar(44)  not null,
	Pol				char(1)      not null,
	Naziv_programa	varchar(48)  not null,	
	IdNivoa	        smallint     not null,	
    Id_predmeta	    smallint     not null,
	SkGodina	    smallint     not null,
	OznakaRoka	    varchar(20)  not null,    
	Naziv_predmeta	varchar(145) not null,	
	Status		    char(2)      not null,
	DatPolaganja    date  	     not null, 
	ocena           smallint     not null,
	poeni           smallint     not null
   )	
   organize by row not logged initially compress yes;
   
insert into polozeni_ispiti   		
select a.indeks, b.ime,b.prezime, b.pol,c.naziv as Naziv_programa, c.idnivoa as Nivo_Studija, d.id, a.skgodina, a.oznakaroka, d.naziv as Naziv_predmeta, a.status, a.datpolaganja, a.ocena, a.poeni
from   db.ispit a, db.dosije b, db.studijskiprogram c, db.predmet d
where  a.indeks=b.indeks
and    b.idprograma=c.id 
and    a.idpredmeta=d.id
and    a.ocena is not null;

comment on table polozeni_ispiti is 'Položeni ispiti po datumima';

commit;
