P°φklad 22
Zadßnφ: Vyberte ty firmy, kterΘ m∞ly faktury za vφce ne₧ 10000,-KΦ letos (tabulka FAKTURY) i vloni (tabulka FAKT_2).
Popis °eÜenφ: Vytvo°φme dva dotazy podle p°φkladu 18. Pak provedeme mno₧inov² pr∙nik odpov∞dφ - pomocφ klauzule INTERSECT.
SQL:
SELECT A.nazev
FROM Firmy A, Faktury B
WHERE A.cislo=B.firma
GROUP BY B.firma
HAVING 10000 < SUM(B.castka)
INTERSECT CORRESPONDING BY (nazev)
SELECT A.nazev
FROM Firmy A, Fakt_2 B
WHERE A.cislo=B.firma
GROUP BY B.firma
HAVING 10000 < SUM(B.castka)
Poznßmky:
a) Malou zm∞nou lze vytvo°it dotaz na firmy, kterΘ tohoto obratu dosßhly letos ale ne vloni. StaΦφ pou₧φt mno₧inov² rozdφl EXCEPT.
SQL:
SELECT A.nazev
FROM Firmy A, Faktury B
WHERE A.cislo=B.firma
GROUP BY B.firma
HAVING 10000 < SUM(B.castka)
EXCEPT CORRESPONDING BY (nazev)
SELECT A.nazev
FROM Firmy A, Fakt_2 B
WHERE A.cislo=B.firma
GROUP BY B.firma
HAVING 10000 < SUM(B.castka)
PokraΦovßnφ