CREATE TABLE dosije
(
    indeks integer PRIMARY KEY,
    ime varchar(45),
    prezime varchar(45),
    god_rodjenja date,
    mjesto_rodjenja varchar(45)
);

CREATE TABLE predmet
(
    id_predmeta INTEGER primary key,
    sifra integer,
    naziv varchar(45),
    krediti integer
);

CREATE TABLE ispitni_rok
(
    godina_roka DATE,
    oznaka_roka varchar(45),
    naziv varchar(45),
    PRIMARY KEY(godina_roka,oznaka_roka)
);

CREATE TABLE ispit
(
    indeks integer,
    id_predmeta integer,
    godina_roka DATE,
    oznaka_roka varchar(45),
    ocena integer,
    datum_ispita DATE,
    PRIMARY KEY(indeks,id_predmeta,godina_roka,oznaka_roka),
    FOREIGN KEY (indeks) REFERENCES dosije(indeks),
    FOREIGN KEY (id_predmeta) REFERENCES predmet(id_predmeta),
    FOREIGN KEY (godina_roka,oznaka_roka) REFERENCES ispitni_rok(godina_roka,oznaka_roka)  
);



INSERT INTO dosije VALUES (1,"Ajdin","Kanurić",'2024-01-03',"Bihać");
INSERT INTO dosije VALUES (2,"Adian","Kudić",'2024-05-07',"Bihać");
INSERT INTO dosije VALUES (3,"Amar","Pačić",'2024-03-23',"Cazin");
INSERT INTO dosije VALUES (4,"Ajna","Felić",'2024-01-13',"Sarajevo");
INSERT INTO dosije VALUES (5,"Emir","Arnautović",'2022-11-27',"Sarajevo");
INSERT INTO dosije VALUES (6,"Arman","Veladžić",'2021-02-28',"Sarajevo");
INSERT INTO dosije VALUES (7,"Sinedin","Semanić",'2025-01-19',"Zenica");
INSERT INTO dosije VALUES (8,"Timur","Toromanović",'2019-05-09',"Cazin");

insert into predmet values(1,1,"programiranje",6);
insert into predmet values(2,2,"matematika",8);
insert into predmet values(3,3,"likovno",4);
insert into predmet values(4,4,"muzičko",2);
insert into predmet values(5,5,"fizika",7);
insert into predmet values(6,6,"njemački",4);
insert into predmet values(7,7,"engleski",2);
insert into predmet values(8,8,"hemija",7);

INSERT INTO ispitni_rok VALUES ('2024-06-08',"Maturski","Matematika");
INSERT INTO ispitni_rok VALUES ('2024-06-09',"Maturski","Engleski");
INSERT INTO ispitni_rok VALUES ('2024-06-04',"Maturski","Bosanski");
INSERT INTO ispitni_rok VALUES ('2024-06-10',"Maturski","Računarske Mreže");
INSERT INTO ispitni_rok VALUES ('2024-04-08',"Test","Matematika");
INSERT INTO ispitni_rok VALUES ('2024-04-13',"Test","Engleski");
INSERT INTO ispitni_rok VALUES ('2024-02-08',"Test","RMRE");
INSERT INTO ispitni_rok VALUES ('2024-05-08',"Test","Bosanski Jezik");



INSERT INTO ispit VALUES (1,1,'2024-06-08',"Maturski",8,'2024-06-08');
INSERT INTO ispit VALUES (2,2,'2024-06-08',"Maturski",6,'2024-06-08');
INSERT INTO ispit VALUES (3,3,'2024-06-04',"Maturski",5,'2024-06-04');
INSERT INTO ispit VALUES (4,1,'2024-04-08',"Test",7,'2024-04-08');
INSERT INTO ispit VALUES (5,2,'2024-04-13',"Test",9,'2024-04-13');
INSERT INTO ispit VALUES (6,3,'2024-02-08',"Test",6,'2024-02-08');
INSERT INTO ispit VALUES (7,4,'2024-05-08',"Test",5,'2024-05-08');
INSERT INTO ispit VALUES (8,1,'2024-06-08',"Maturski",10,'2024-06-08');



-- Težina predmeta
select p.*, 
case 
    when krediti < 6 then 'lak' 
    when krediti between 6 and 7 then 'srednje težak' 
    else 'težak' 
end 
from predmet p;

select char(10);

-- Ocjena ispita za programiranje
select i.*, 
case 
    when ocena = 5 then 'pao' 
    when ocena = 6 then 'provukao se' 
    else 'polozio' 
end 
from ispit i 
join predmet p on i.id_predmeta=p.id_predmeta 
where p.naziv='programiranje';