CREATE TABLE ODJEL
(
    ID_Odjel INTEGER PRIMARY KEY,
    naziv_odjel VARCHAR(45),
    grad_odjel VARCHAR(45)
);

CREATE TABLE PLATA
(
    ID_Plata INTEGER PRIMARY KEY,
    iznos_plata DECIMAL(10,2)
);

CREATE TABLE POSAO
(
    ID_Posao INTEGER PRIMARY KEY,
    naziv_posao VARCHAR(45),
    odjel INTEGER,
    FOREIGN KEY (odjel) REFERENCES Odjel(ID_Odjel)
);

CREATE TABLE MENADŽER 
(
    ID_Menadžer INTEGER PRIMARY KEY,
    naziv_menadžer VARCHAR(45),
    odjel_menadžer INTEGER,
    FOREIGN KEY (odjel_menadžer) REFERENCES Odjel(ID_Odjel)
    
);


CREATE TABLE ZAPOSLENIK
(
    ID_Zaposlenik INTEGER PRIMARY KEY,
    ime_zaposlenik VARCHAR(45),
    prezime_zaposlenik VARCHAR(45),
    plata_zaposlenik INTEGER,
    odjel_zaposlenik INTEGER,
    menadžer_zaposlenik INTEGER,
    posao_zaposlenik INTEGER,
    datum_zaposlenik DATE,
    povecanje_zaposlenik DECIMAL(10,2),
    FOREIGN KEY (menadžer_zaposlenik) REFERENCES MENADŽER (ID_Menadžer),
    FOREIGN KEY (plata_zaposlenik) REFERENCES PLATA (ID_Plata),
    FOREIGN KEY (odjel_zaposlenik) REFERENCES ODJEL (ID_Odjel),
    FOREIGN KEY (posao_zaposlenik) REFERENCES POSAO(ID_Posao)
);


INSERT INTO ODJEL VALUES (1,'Odjel 1','Bihać');
INSERT INTO ODJEL VALUES (2,'Odjel 2','Cazin');
INSERT INTO ODJEL VALUES (3,'Odjel 3','Bosanska Krupa');

INSERT INTO PLATA VALUES (1,420);
INSERT INTO PLATA VALUES (2,7000);
INSERT INTO PLATA VALUES (3,1320.25);

INSERT INTO POSAO VALUES (1,'Moler',1);
INSERT INTO POSAO VALUES (2,'Zidar',3);
INSERT INTO POSAO VALUES (3,'Stolar',2);

INSERT INTO MENADŽER VALUES (1,'Ismar Kasic',2);
INSERT INTO MENADŽER VALUES (2,'Amir Cavkic',3);
INSERT INTO MENADŽER VALUES (3,'Amina Zulic',1);

INSERT INTO ZAPOSLENIK VALUES (1,'Dino','Muslic',1,2,3,1,'04-03-2022',0);
INSERT INTO ZAPOSLENIK VALUES (2,'Adis','Dupanovic',3,2,1,1,'25-02-2022',50);
INSERT INTO ZAPOSLENIK VALUES (3,'Faruk','Behrem',3,3,2,2,'02-02-2020',150);
INSERT INTO ZAPOSLENIK VALUES (4,'Hana','Kurtovic',1,1,1,1,'25-04-2022',200);

select "1.Prikazati ime i prezime zaposlenika zajedno sa nazivom odjela, gradom i iznosom njihove plate: ";
select z.ime_zaposlenik,z.prezime_zaposlenik,o.naziv_odjel,o.grad_odjel,p.iznos_plata from ZAPOSLENIK z join ODJEL o on odjel_zaposlenik=ID_Odjel join PLATA p on plata_zaposlenik=ID_Plata;
select char(10);
select "2.Prikazati ime i prezime zaposlenika, naziv posla koji obavljaju i naziv njihovog menadžera: ";
select z.ime_zaposlenik,z.prezime_zaposlenik,p.naziv_posao,m.naziv_menadžer from ZAPOSLENIK z join POSAO p on ID_Posao=posao_zaposlenik join MENADŽER m on ID_Menadžer=menadžer_zaposlenik;
select char(10);
select "3.Izračunati ukupan iznos plata za svaki odjel: ";
select o.naziv_odjel,SUM(p.iznos_plata) FROM ZAPOSLENIK z join ODJEL o on odjel_zaposlenik=ID_Odjel join PLATA p on plata_zaposlenik=ID_Plata GROUP BY naziv_odjel;
select char(10);
select "4.Izračunati prosječnu platu za svaki posao: ";
select po.naziv_posao,AVG(p.iznos_plata) FROM ZAPOSLENIK z join POSAO po on ID_Posao=posao_zaposlenik join PLATA p on ID_Plata=plata_zaposlenik GROUP BY naziv_posao;
select char(10);
select "5.Prikazati zaposlenike čija je plata veća od prosječne plate svih zaposlenika u firmi: ";
select z.ime_zaposlenik,z.prezime_zaposlenik,p.iznos_plata FROM ZAPOSLENIK z join ODJEL o on ID_Odjel=odjel_zaposlenik join PLATA p on ID_Plata=plata_zaposlenik where p.iznos_plata>(select AVG(iznos_plata) from PLATA) GROUP BY naziv_odjel;
select char(10);
select o.naziv_odjel,MAX(p.iznos_plata) FROM ZAPOSLENIK z JOIN ODJEL o ON z.odjel_zaposlenik=ID_Odjel JOIN PLATA p on z.plata_zaposlenik=ID_Plata GROUP BY o.naziv_odjel;
select char(10);
select o.grad_odjel, SUM(p.iznos_plata) FROM ZAPOSLENIK z JOIN ODJEL o on o.ID_Odjel=z.odjel_zaposlenik JOIN PLATA p on p.ID_Plata=z.plata_zaposlenik GROUP BY o.grad_odjel;
select char(10);
select o.naziv_odjel, COUNT(z.ID_Zaposlenik) FROM ODJEL o join ZAPOSLENIK z on o.ID_Odjel=z.odjel_zaposlenik GROUP BY o.naziv_odjel HAVING COUNT(z.ID_Zaposlenik)>1;
select char(10);
select z.ime_zaposlenik,z.prezime_zaposlenik, (p.iznos_plata + z.povecanje_zaposlenik), p.iznos_plata FROM ZAPOSLENIK z JOIN Plata p ON z.plata_zaposlenik=p.ID_Plata;
select char(10);
select pos.naziv_posao FROM POSAO pos JOIN ZAPOSLENIK z on z.posao_zaposlenik=pos.ID_Posao WHERE z.ID_Zaposlenik IS NULL;
select char(10);
select z.ime_zaposlenik,z.prezime_zaposlenik,o.naziv_odjel FROM ZAPOSLENIK z JOIN ODJEL o ON o.ID_Odjel=z.odjel_zaposlenik JOIN PLATA p ON p.ID_Plata=z.plata_zaposlenik where p.iznos_plata<2000;  
select char(10);
select o.naziv_odjel,AVG(z.povecanje_zaposlenik) FROM ZAPOSLENIK z JOIN ODJEL o ON z.odjel_zaposlenik=o.ID_Odjel GROUP BY o.naziv_odjel;
select char(10);
select z.ime_zaposlenik,z.prezime_zaposlenik FROM ZAPOSLENIK z JOIN PLATA p on p.ID_Plata=z.plata_zaposlenik JOIN ODJEL o on o.ID_Odjel=z.odjel_zaposlenik where  p.iznos_plata>(select AVG(p1.iznos_plata) FROM PLATA p1 JOIN ZAPOSLENIK z1 on p1.ID_Plata=z1.plata_zaposlenik  where z1.odjel_zaposlenik=z.odjel_zaposlenik );