CREATE TABLE Grad
(
    PTT INTEGER PRIMARY KEY,
    Naziv varchar(45)
);

CREATE TABLE Status
(

    ID INTEGER PRIMARY KEY,
    status varchar(45)
    
);

CREATE TABLE Dobavljac
(
    ID_Dobavljaca INTEGER PRIMARY KEY,
    naziv varchar(45),
    status INTEGER,
    grad INTEGER,
    FOREIGN KEY (grad) REFERENCES Grad(PTT),
    FOREIGN KEY (status) REFERENCES Status(ID)
);

CREATE TABLE Dio
(
    ID_Dijela INTEGER PRIMARY KEY,
    naziv varchar(45),
    boja varchar(45),
    tezina INTEGER,
    grad INTEGER,
    FOREIGN KEY (grad) REFERENCES Grad(PTT)
);

CREATE TABLE Proizvod
(
    ID_Proizvoda INTEGER PRIMARY KEY,
    naziv varchar(45),
    grad INTEGER,
    FOREIGN KEY (grad) REFERENCES Grad(PTT)
);

CREATE TABLE SPJ
(
    ID_Dijela INTEGER,
    ID_Dobavljaca INTEGER,
    ID_Proizvoda INTEGER,
    kolicina INTEGER,
    PRIMARY KEY(ID_Dobavljaca,ID_Dijela,ID_Proizvoda),
    FOREIGN KEY(ID_Dobavljaca) REFERENCES Dobavljac(ID_Dobavljaca),
    FOREIGN KEY(ID_Dijela) REFERENCES Dio(ID_Dijela),
    FOREIGN KEY(ID_Proizvoda) REFERENCES Proizvod(ID_Proizvoda)
    
);

INSERT INTO Grad VALUES (77000,"BIHAĆ");
INSERT INTO Grad VALUES (71000,"SARAJEVO");
INSERT INTO Grad VALUES (41000,"ZAGREB");

INSERT INTO Status VALUES (10,"REDOVAN");
INSERT INTO Status VALUES (20,"POVREMEN");
INSERT INTO Status VALUES (30,"VANREDAN");


INSERT INTO Dobavljac VALUES (1,"EXTREME-X",10,77000);
INSERT INTO Dobavljac VALUES (2,"URBAN-K",20,71000);
INSERT INTO Dobavljac VALUES (3,"KEDIRA-S",30,41000);
INSERT INTO Dobavljac VALUES (4,"MOBIL",10,77000);


INSERT INTO Proizvod VALUES (1,"RAČUNAR",77000);
INSERT INTO Proizvod VALUES (2,"PDA UREĐAJ",71000);
INSERT INTO Proizvod VALUES (3,"MOBILNI TELEFON",77000);


INSERT INTO Dio VALUES (1,"RAM MEMORIJA","",10,77000);
INSERT INTO Dio VALUES (2,"TASTATURA","CRNA",200,77000);
INSERT INTO Dio VALUES (3,"MIŠ","BIJELA",10,71000);

INSERT INTO SPJ VALUES (1,1,1,50);
INSERT INTO SPJ VALUES(2,1,3,0);
INSERT INTO SPJ VALUES(3,2,2,10);

select "1.Prikazati nazive redovnih dobavljača: ";
select d.naziv FROM Dobavljac d join STATUS s on s.ID=d.status where s.status="REDOVAN";
select char(10);

select "3.Prikazati koliko je isporučeno računara: ";
select spj.kolicina from SPJ spj join PROIZVOD p on p.ID_Proizvoda=spj.ID_Proizvoda where p.naziv="RAČUNAR";
select char(10);

select "5.Prikazati koliko ima vanrednih dobavljača: ";
select count(d.naziv) FROM Dobavljac d join STATUS s on s.ID=d.status where s.status="VANREDAN";
select char(10);

select "7.Prikazati PTTNaziv iz tabele grad kao ' DOBAVLJAČ MOBIL  JE IZ GRADA'  iz kojeg je dobavljač Mobil: ";
select "DOBAVLJAČ MOBIL JE IZ GRADA: " || g.naziv FROM Grad g join Dobavljac d on d.grad=g.PTT where d.naziv = "MOBIL";
select char(10);

select "9.Prikazati naziv dobavljača i naziv grada (iz tabele GRAD) za sve dobavljače: ";
select d.naziv,g.naziv from Dobavljac d join Grad g on g.PTT=d.grad;
select char(10);

select "11.Prikazati naziv dobavljača i naziv dijela koje isporučuje: ";
select d.naziv,dio.naziv from Dobavljac d join SPJ spj on d.ID_Dobavljaca=spj.ID_Dobavljaca join DIO dio on spj.ID_Dijela=dio.ID_Dijela ;
select char(10);

select "13.Prikazati naziv dijela i naziv proizvoda za koje postoji isporuka: ";
select dio.naziv,p.naziv from SPJ spj join Dio dio on dio.ID_Dijela=spj.ID_Dijela join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda;
select char(10);

select "15.Prikazati naziv dobavljača, naziv grada i naziv statusa za sve dobavljače: ";
select d.naziv,g.naziv,s.status from Dobavljac d join Grad g on g.PTT=d.grad join Status s on d.status=s.ID;
select char(10);

select "17.Prikazati naziv dobavljača i ukupan broj različitih proizvoda koje snabdijeva: ";
select d.naziv, count(spj.ID_Proizvoda) from SPJ spj join Dobavljac d on spj.ID_Dobavljaca=d.ID_Dobavljaca GROUP BY d.naziv;
select char(10);

select "19.Prikazati naziv grada i ukupan broj dobavljača u tom gradu: ";
select g.naziv, count(d.ID_Dobavljaca) from Grad g join Dobavljac d on d.grad=g.PTT GROUP BY g.PTT;
select char(10);

select "21.Prikazati naziv dobavljača koji isporučuju dijelove proizvedene u istom gradu kao i oni: ";
select d.naziv,dio.naziv,g.naziv from Dobavljac d join SPJ spj on spj.ID_Dobavljaca=d.ID_Dobavljaca join Dio dio on dio.ID_Dijela=spj.ID_Dijela join Grad g on d.grad=g.PTT where d.grad=dio.grad;
select char(10);

select "23.Prikazati naziv proizvoda i naziv grada gdje se proizvod pravi, te naziv dobavljača koji ga snabdijeva: ";
select p.naziv,g.naziv,d.naziv from SPJ spj join Dobavljac d on d.ID_Dobavljaca=spj.ID_Dobavljaca join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda join Grad g on g.PTT=p.grad  where p.grad=g.PTT ;
select char(10);

select "25.Prikazati naziv dijela i naziv grada dobavljača koji ga isporučuje: ";
select dio.naziv, d.naziv, g.naziv from SPJ spj join Dobavljac d on d.ID_Dobavljaca=spj.ID_Dobavljaca join Dio dio on spj.ID_Dijela=dio.ID_Dijela join Grad g on g.PTT=d.grad;
select char(10);

select "27.Prikazati naziv proizvoda i ukupan broj dobavljača koji ga snabdijevaju: ";
select p.naziv, count(spj.ID_Dobavljaca) from SPJ spj join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda group by spj.ID_Proizvoda;
select char(10);

select "29.Prikazati naziv grada i naziv dobavljača čiji je status VANREDAN: ";
select g.naziv,d.naziv,s.status from Dobavljac d join Grad g on g.PTT=d.grad join Status s on s.ID=d.status where s.status="VANREDAN";
select char(10);

select "31.Prikazati naziv proizvoda i ukupnu količinu dijelova isporučenih iz Sarajeva: ";
select p.naziv, spj.kolicina, g.naziv from SPJ spj join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda join Grad g on g.PTT=p.grad where g.naziv="SARAJEVO";
select char(10);

select "33.Prikazati naziv dijela, njegovu težinu i naziv proizvoda za koji je isporučen: ";
select dio.naziv,dio.tezina,p.naziv from SPJ spj join Dio dio on dio.ID_Dijela=spj.ID_Dijela join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda;
select char(10);

select "35.Prikazati naziv grada i ukupan broj isporuka realizovanih iz tog grada: ";
select g.naziv,count(d.ID_Dobavljaca) from SPJ spj join Dobavljac d on d.ID_Dobavljaca=spj.ID_Dobavljaca join Grad g on g.PTT=d.grad group by g.PTT;
select char(10);

select "37.Prikazati naziv proizvoda i naziv dijela koji se isporučuju iz istog grada kao proizvod: ";
select p.naziv,dio.naziv,g.naziv from SPJ spj join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda join Dio dio on dio.ID_Dijela=spj.ID_Dijela join Grad g on g.PTT=p.grad where p.grad=dio.grad;
select char(10);

-- 2
select naziv from Dio where grad=(select PTT from Grad where naziv="BIHAĆ") and tezina<(select avg(tezina) from Dio);

-- 4
select tezina from Dio where naziv="TASTATURA" and grad in (select PTT from Grad where naziv like "%Ć");

-- 6
select d.naziv from Dobavljac d join Status s on d.status=s.ID join Grad g on d.grad=g.PTT where s.status="REDOVAN" and g.naziv="BIHAĆ" order by d.naziv;

-- 8
select sum(spj.kolicina) from SPJ spj join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda join Grad g on p.grad=g.PTT where p.naziv="PDA UREĐAJ" and g.naziv="SARAJEVO";

-- 10
select d.naziv, s.status from Dobavljac d join Status s on d.status=s.ID;

-- 12
select d.naziv, p.naziv, spj.kolicina from SPJ spj join Dobavljac d on d.ID_Dobavljaca=spj.ID_Dobavljaca join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda;

-- 14
select d.naziv, p.naziv from Dobavljac d join Proizvod p on d.grad=p.grad;

-- 16
select p.naziv, sum(spj.kolicina) from SPJ spj join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda group by p.naziv;

-- 18
select dio.naziv, dio.boja, p.naziv from SPJ spj join Dio dio on dio.ID_Dijela=spj.ID_Dijela join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda;

-- 20
select g.naziv, count(p.ID_Proizvoda) from Grad g join Proizvod p on p.grad=g.PTT group by g.PTT;

-- 22
select d.naziv, dio.naziv from SPJ spj join Dobavljac d on d.ID_Dobavljaca=spj.ID_Dobavljaca join Dio dio on dio.ID_Dijela=spj.ID_Dijela where spj.kolicina>(select avg(kolicina) from SPJ);

-- 24
select d.naziv, s.status from SPJ spj join Dobavljac d on d.ID_Dobavljaca=spj.ID_Dobavljaca join Status s on s.ID=d.status join Dio dio on dio.ID_Dijela=spj.ID_Dijela where dio.naziv="RAM MEMORIJA";

-- 26
select d.naziv, p.naziv from Dobavljac d cross join Proizvod p where not exists (select * from SPJ spj where spj.ID_Dobavljaca=d.ID_Dobavljaca and spj.ID_Proizvoda=p.ID_Proizvoda);

-- 28
select dio.naziv, count(distinct spj.ID_Dobavljaca) from SPJ spj join Dio dio on dio.ID_Dijela=spj.ID_Dijela group by dio.naziv;

-- 30
select d.naziv, p.naziv, dio.naziv from SPJ spj join Dobavljac d on d.ID_Dobavljaca=spj.ID_Dobavljaca join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda join Dio dio on dio.ID_Dijela=spj.ID_Dijela order by d.naziv;

-- 32
select distinct d.naziv, g.naziv from SPJ spj join Dobavljac d on d.ID_Dobavljaca=spj.ID_Dobavljaca join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda join Grad g on g.PTT=d.grad where p.naziv="RAČUNAR";

-- 34
select d.naziv, p.naziv, count(*) from SPJ spj join Dobavljac d on d.ID_Dobavljaca=spj.ID_Dobavljaca join Proizvod p on p.ID_Proizvoda=spj.ID_Proizvoda group by d.naziv,p.naziv;

-- 36
select s.status, count(d.ID_Dobavljaca) from Status s join Dobavljac d on d.status=s.ID group by s.status;

-- 38
select d.naziv, dio.naziv, g.naziv from SPJ spj join Dobavljac d on d.ID_Dobavljaca=spj.ID_Dobavljaca join Dio dio on dio.ID_Dijela=spj.ID_Dijela join Grad g on g.PTT=dio.grad where d.grad<>dio.grad;