SQL lab 3 vezba

--1.
/*SELECT P.Tezina
FROM Posiljka P, Firma F
WHERE F.IdFir = P.IdFir AND F.Naziv = 'Tropik'
*/

--2
/*
Select DISTINCT F.*, P.Vrednost
FROM Firma F, Posiljka P
WHERE F.IdFir = P.IdFir AND P.Vrednost = (Select MAX(Vrednost)
                                          FROM Posiljka)
*/

--3
/*
SELECT F.Naziv, AVG(P.Tezina)
FROM Firma F, Posiljka P
WHERE F.IdFir = P.IdFir
GROUP BY F.IdFir,F.Naziv
*/

--4
/*
SELECT Z.ImePrezime
FROM Mehanicar M, Zaposlen Z
WHERE M.IDZap = Z.IDZap AND NOT EXISTS(Select *
                 FROM Popravlja Po
                 WHERE Po.IdZap = M.IdZap)
*/

--5
/*
SELECT Z.IdZap, Z.ImePrezime, K.Marka, K.Nosivost, P.Dana
FROM Zaposlen Z, Mehanicar M, Popravlja P, Kamion K
WHERE M.IdZap = P.IdZap AND P.IdKam = K.IdKam AND Z.IdZap = M.IdZap*/

--6 pretpostavljam da ovde treba nesto preko statusa ali ne kontam sta u statusu znaci sta

--7
/*
SELECT K.IdKam, K.Marka, K.Nosivost
FROM Kamion K, Posiljka P, Putovanje Put, sePrevozi SP
WHERE K.IdKam = Put.IdKam AND Put.IdPut = SP.IdPut AND P.IdPos = SP.IdPOS
GROUP BY K.IdKam, k.Marka, K.Nosivost
HAVING SUM(P.Tezina) < 7000
*/

--8
/*
SELECT Z.IdZap, Z.ImePrezime, K.Marka, COUNT(*)
FROM Zaposlen Z, Vozac V, Vozi vozi, Posiljka P, sePrevozi SP, Putovanje Put, Kamion K
WHERE V.IdZap = Z.IdZap AND vozi.IdZap = V.IdZap AND vozi.IdPut = Put.IdPut AND P.IdFir = 0
AND SP.IdPos = P.IdPos AND SP.IdPut = Put.IdPut AND Put.IdKam = K.IdKam
GROUP BY Z.IdZap, K.Marka
*/

--9
/*
SELECT Z.IdZap, Z.Staz, SUM(Pos.Tezina) AS Teret
FROM Zaposlen Z, Vozac V, Putovanje Put, Posiljka Pos, sePrevozi SP, Vozi vozi
WHERE V.IdZap= Z.IdZap AND vozi.IDZap = V.IdZap AND vozi.IdPut = Put.IdPut AND SP.IdPut = Put.IdPut
AND SP.IdPos = Pos.IdPos
GROUP BY Z.IdZap, Z.Staz
HAVING Z.Staz = (SELECT MAX(Staz)
                 FROM Zaposlen)
*/

--10 wooot
/*
SELECT K.IdKam, SUM(Pos.Tezina) AS Tezina, SUM(Pos.Vrednost) AS Vrednost
FROM Kamion K, Popravlja pop, Posiljka Pos, sePrevozi SP, Putovanje Put
WHERE K.IdKam = pop.IdKam AND Put.IdKam = K.IdKam AND SP.IdPut = Put.IdPut AND SP.IdPos = Pos.IdPos
GROUP BY K.IdKam
HAVING K.BrPopravljanja < (SELECT AVG(BrPopravljanja)
                           FROM Kamion) AND "Dragan Milutinovic" IN (Select Z.ImePrezime
                                                                    FROM Zaposlen Z, Vozac V, Vozi vozi, Putovanje Put
                                                                    WHERE Z.IdZap = V.IdZap AND vozi.IdZap = V.IdZap AND vozi.IdPut = Put.IdPut AND Put.IdKam = K.IdKam)

*/