SELECT P.PONUDAID, P.DATUM, P.NAZIV_PREDUZECA, SP.PROIZVODJAC, O.NAZIV_OPREME, SP.GARANTNI_ROK)
FROM PONUDA P LEFT OUTER JOIN STAVKA_PONUDE SP
ON P.PONUDAID=SP.PONUDAID
LEFT OUTER JOIN VRSTA_OPREME O
ON SP.OPREMAID=O.OPREMAID
WHERE EXTRACT(YEAR FROM P.DATUM) = EXTRACT(YEAR FROM CURRENT_DATE) AND
(P.PERIOD_OTPLATE > 12 OR P.GOTOVINSKI_POPUST>=0.05);

SELECT P.PONUDAID, P.NAZIV_PREDUZECA, P.PERIOD_OTPLATE, P.GOTOVINSKI_POSPUST 
FROM PONUDA P INNER JOIN STAVKA PONUDE SP
ON P.PONUDAID=SP.PONUDAID
INNER JOIN VRSTA_OPREME O
ON SP.OPREMAID=O.OPREMAID
WHERE O.NAZIV_OPREME='KOMPJUTERSKA OPREMA'
GROUP BY P.NAZIV_PREDUZECA
HAVING COUNT(DISTINCT SP.PROIZVODJAC)>1;

CREATE VIEW PREGLED_KONKURSA(SIFRA_OPREME, NAZIV_OPREME, UK_KOL, GODINA) AS
SELECT O.OPREMAID, O.NAZIV_OPREME, SUM(SK.KOLICINA), EXTRACT(YEAR FROM K.DATUM_DO)
FROM VRSTA_OPREME O LEFT OUTER JOIN STAVKA_KONKURSA
ON O.OPREMAID=SK.OPREMAID
LEFT OUTER JOIN KONKURS K
ON K.KONKURSID=SK.KONKURSID
WHERE EXTRAXT(YEAR FROM K.DATUM_DO) IN (2004,2005)
GROUP BY O.OPREMAID, EXTRAXT(YEAR FROM K.DATUM_DO);
