Nová podstránka (kategorie: SELECT a Vnořené dotazy)
||
||
Poddotazy vracející jednu hodnotu
zobrazitvše kdo má plat vyšší jak průměr firmy
SELECT jméno, příjmení
FROM platy
WHERE plat > (SELECT AVG(plat) FROM platy)
Zjistěte název a cenu nejlevnější knihy.
SELECT název, cena
FROM kniha, výtisk
WHERE kniha.id = výtisk.publikace_id
AND cena = (SELECT MIN(cena) FROM výtisk)
Vypište jména všech autorů, kteří se podíleli při psaní nejdelší knihy (=s největším počtem stran)
SELECT jméno, příjmení
FROM autoři, napsané_knihy, výtisk
WHERE autoři.id = napsané_knihy.autor_id
AND napsané_knihy.publikace_id = výtisk.publikace_id
AND počet_stran = (SELECT MAX(počet_stran) FROM výtisk)
Poddotaz vyhodnocující
vypišvšechny kategorie, které obsahují v sobě další podkategorie
SELECT c.id AS id, c.category AS category, c.subcategory AS subcategory, c.title AS title
FROM category c
WHERE (SELECT count(id) AS pocet FROM category c2 WHERE category=c.id) > 0
ORDER BY subcategory,title,category
Poddotazy vracející více hodnot
Vnořovánídotazů nám rozšiřuje klasickou množinu relačních operátorů o operátory další, které se aplikují z levé strany na sloupec a z pravé strany na vnořený dotaz vracející více hodnot. Mezi ně patří operátory IN, ANY (SOME), ALL.
Operátor IN slouží pro jednoduché porovnání, zdali se hodnota sloupce vlevo vyskytuje mezi hodnotami vrácených vnořeným dotazem. Kdybychom např. chtěli názvy knih vydané v letech 1997 až 2000, mohli bychom takový dotaz pomocí operátoru IN zapsat takto:
SELECT název
FROM kniha, výtisk
WHERE kniha.id = výtisk.publikace_id
AND rok IN (1997, 1998, 1999, 2000)
Výraz v závorce je množina všech hodnot, ve kterých se má daná hodnota hledat. Většinou neuvádíme výčet explicitně, ale vrací nám jej nějaký vnořený dotaz. V této chvíli si tedy lze uvést náš dotaz na názvy knih vydaných v týchž letech, jako díla Aloise Jiráska:
SELECT název
FROM kniha, autoři, napsané_knihy, výtisk
WHERE kniha.id = napsané_knihy.publikace_id
AND napsané_knihy.autor_id = autoři.id
AND kniha.id = výtisk.publikace_id
AND rok IN ( SELECT rok
FROM autoři a, výtisk v, napsané_knihy n
WHERE a.id = n.autor_id
AND n.publikace_id = v.publikace_id
AND a.jméno LIKE 'Alois'
AND a.příjmení LIKE 'Jirásek'
)
Všimněte si, že jsem ve vnořeném dotazu v části FROM použil aliasy pro názvy tabulek. To proto, že tytéž názvy se objevují i v nadřazeném SELECTu. Kdybyste v tomto příkladu aliasů nepoužili, chyba by to nebyla, dotaz by vrátil očekávaná data. Ale někdy (a uvidíme to v příštím díle) je použití aliasů nutné, neboť v některých případech bude SQL server potřebovat vědět, na kterou tabulku v restrikci vnořeného dotazu se odkazujeme - zdali na tu "lokální" nebo na tu "globální".
V případě dlouhých názvů některých tabulek je výhodou je v rámci dotazu nahradit kratšími názvy. Podotýkám, že aliasy tabulek se narozdíl od aliasů sloupců, neuvozují modifikátorem AS.
Použití operátorů ANY(SOME) a ALL ukazuje následující syntaxe:
Operátor ANY nebo SOME určují, že relace se vztahuje na alespoň jednu z hodnot, kterou vrátí vnořený dotaz, operátor ALL aplikuje relaci na všechny hodnoty vrácené poddotazem.
Příklad
Chcemeseznam takových knih od Vítězslava Nezvala, jejichž výtisky nebyly nikdy dražší, než kterýkoliv výtisk díla Aloise Jiráska.
SELECT název
FROM kniha, výtisk, napsané_knihy, autoři
WHERE autoři.id = napsané_knihy.autor_id
AND napsané_knihy.publikace_id = kniha.id
AND kniha.id = výtisk.publikace_id
AND jméno LIKE 'Vítězslav'
AND příjmení LIKE 'Nezval'
AND cena < ALL (SELECT cena
FROM napsané_knihy n, výtisk v, autoři a
WHERE v.publikace_id = n.publikace_id
AND n.autor_id = a.id
AND a.jméno LIKE 'Alois'
AND a.příjmení LIKE 'Jirásek'
)
Výše uvedený dotaz můžeme přepsat použitím operátoru ANY v kombinaci s operátorem NOT (rozdíl bude jen v řádku, kde uvádíme sloupec CENA):