-- UPIT 1
CREATE TABLE ekst87upit1 AS SELECT pl.naziv
FROM pravno_lice pl
WHERE pl.lokacija_id IN (select fl.lokacija_id
FROM fizicko_lice fl
WHERE pl.lokacija_id=fl.lokacija_id);
--UPIT 2

CREATE TABLE ekst87upit2 AS SELECT to_char(ug.datum_potpisivanja,'dd.mm.yyyy') AS "DATUM POTPISIVANJA" ,naziv
FROM pravno_lice pl,ugovor_za_pravno_lice ug
WHERE pl.pravno_lice_id=ug.pravno_lice_id AND  ug.datum_potpisivanja >(SELECT Min(f.datum_kupoprodaje)
                                                                      FROM faktura f, narudzba_proizvoda sf, garancija g
                                                                      WHERE f.faktura_id=sf.faktura_id AND g.narudzba_id=sf.narudzba_id AND g.garancija_id IS NOT NULL);
--UPIT 3
CREATE TABLE ekst87upit3 AS SELECT p.naziv
FROM proizvod p, kategorija k
WHERE p.kategorija_id=k.kategorija_id
AND k.kategorija_id = (SELECT k2.kategorija_id
                       FROM proizvod p2, kategorija k2,kolicina kol
                       WHERE  p2.kategorija_id=k2.kategorija_id AND p2.proizvod_id=kol.proizvod_id  AND kol.kolicina_proizvoda =(SELECT max(kol2.kolicina_proizvoda)
                       FROM kolicina kol2));
--UPIT 4
CREATE TABLE ekst87upit4 AS SELECT p.naziv AS "Proizvod",pl.naziv AS "Proizvodac"
FROM proizvod p,proizvodjac pr,pravno_lice pl
WHERE p.proizvodjac_id=pr.proizvodjac_id AND pr.proizvodjac_id=pl.pravno_lice_id AND pr.proizvodjac_id in (SELECT pr2.proizvodjac_id
                                                                                                       FROM proizvodjac pr2, proizvod p2
                                                                                                       WHERE pr2.proizvodjac_id=p2.proizvodjac_id
                                                                                                       AND p2.cijena > ALL (SELECT Avg(p3.cijena)
                                                                                                                            FROM proizvod p3, proizvodjac pr3
                                                                                                                            WHERE p3.proizvodjac_id=pr3.proizvodjac_id));
--UPIT 5
CREATE TABLE ekst87upit5 AS SELECT fl2.ime ||' '|| fl2.prezime as "Ime i prezime",sum(f2.iznos) as "Potroseni iznos"
FROM faktura f2, kupac k2, fizicko_lice fl2, uposlenik u
WHERE f2.kupac_id = k2.kupac_id AND k2.kupac_id=fl2.fizicko_lice_id AND u.uposlenik_id = fl2.fizicko_lice_id
GROUP BY fl2.ime, fl2.prezime
HAVING sum(f2.iznos)> (SELECT Round(avg(sum(f.iznos)),2)
                       FROM faktura f, kupac k, fizicko_lice fl
                       WHERE f.kupac_id = k.kupac_id AND k.kupac_id=fl.fizicko_lice_id
                       GROUP BY fl.ime, fl.prezime);


--UPIT 6
CREATE TABLE ekst87upit6 AS SELECT pl.naziv
FROM faktura ff, narudzba_proizvoda np2, isporuka i2, kurirska_sluzba ks2 , pravno_lice pl
WHERE ff.faktura_id=np2.faktura_id
      AND ff.isporuka_id=i2.isporuka_id
      AND i2.kurirska_sluzba_id=ks2.kurirska_sluzba_id
      AND ks2.kurirska_sluzba_id=pl.pravno_lice_id
      AND np2.popust_id IS NOT NULL
GROUP BY pl.naziv
HAVING  sum(np2.kolicina_jednog_proizvoda) = (SELECT Max(sum(np.kolicina_jednog_proizvoda))
                                              FROM faktura f, narudzba_proizvoda np, isporuka i, kurirska_sluzba ks
                                              WHERE f.faktura_id=np.faktura_id
                                                    AND f.isporuka_id=i.isporuka_id
                                                    AND i.kurirska_sluzba_id=ks.kurirska_sluzba_id
                                                    AND np.popust_id IS NOT NULL
                                              GROUP BY ks.kurirska_sluzba_id );

--UPIT 7
CREATE TABLE ekst87upit7 AS SELECT f.ime || ' ' || f.prezime as Kupac, Sum(np.kolicina_jednog_proizvoda*pr.cijena*pp.postotak/100)  AS Usteda
FROM fizicko_lice f, kupac k, faktura fk, narudzba_proizvoda np, proizvod pr, popust pp
WHERE f.fizicko_lice_id = k.kupac_id AND k.kupac_id = fk.kupac_id AND fk.faktura_id = np.faktura_id
AND np.proizvod_id = pr.proizvod_id AND np.popust_id = pp.popust_id AND pp.postotak IS NOT NULL
GROUP BY f.ime,f.prezime;

--UPIT 8
CREATE TABLE ekst87upit8 AS SELECT DISTINCT isp.isporuka_id, isp.kurirska_sluzba_id
FROM isporuka isp, faktura fk, narudzba_proizvoda np,  popust pp,proizvod p
WHERE p.proizvod_id=np.proizvod_id AND isp.isporuka_id = fk.isporuka_id AND fk.faktura_id = np.faktura_id
AND np.popust_id = pp.popust_id AND p.broj_mjeseci_garancije IS NOT NULL AND pp.postotak IS NOT NULL;

--UPIT 9
CREATE TABLE ekst87upit9 AS SELECT p2.naziv,p2.cijena
FROM proizvod p2
WHERE cijena > (SELECT Avg(Max(p.cijena))
                  FROM proizvod p, kategorija k
                  WHERE p.kategorija_id = k.kategorija_id
                  GROUP BY p.kategorija_id);

--UPIT 10
CREATE TABLE ekst87upit10 AS SELECT p2.naziv,p2.cijena
FROM proizvod p2,kategorija k2
WHERE k2.kategorija_id=p2.kategorija_id AND  p2.cijena < ALL (SELECT Avg(p.cijena)
                  FROM proizvod p, kategorija k
                  WHERE p.kategorija_id = k.kategorija_id  AND k.nadkategorija_id<>k2.kategorija_id

                  GROUP BY p.kategorija_id);