Gymnázium a Střední odborná škola, Rokycany, Mládežníků 1115 Číslo projektu:
CZ.1.07/1.5.00/34.0410
Číslo šablony:
V/2 - inovace směřující k rozvoji odborných kompetencí
Název materiálu:
Pracovní listy – Databáze
Ročník:
3. ročník (IT)
Identifikace materiálu:
BEL_52_DAT_PL11
Jméno autora:
Miloslav Bělský
Předmět:
Databáze
Tématický celek:
Výběrový dotaz
Anotace:
Obsahem tohoto pracovního listu je seskupování řádků ve výběrovém dotazu
Datum:
26. 11. 2013
11-1
Databáze Pracovní list č. 11
Seskupování řádků Cíl Naučit se seskupovat řádky při dotazech, které poskytují souhrnné výsledky. Např. průměrný plat zaměstnanců v jednotlivých odděleních firmy, nejstarší zaměstnanec v každém oddělení atd.
Výklad Spojování řádků Řádky spojujeme (seskupujeme) v případě, že chceme získat informace o souhrnných stavech (maximum, minimum, součet, ...). V těchto dotazech prakticky vždy využíváme některou skupinovou funkci. Výhodu seskupování řádků si ukážeme na následujícím příkladu: Počty zaměstnanců v jednotlivých odděleních můžeme zjistit dvěma způsoby: SELECT oddeleni, prijmeni FROM zamestnanci ORDER BY oddeleni; V tomto případě získáme report, ve kterém budou zaměstnanci seřazeni podle oddělení a my si je můžeme spočítat. Samozřejmě by bylo pohodlnější, kdyby je spočítal dotaz za nás: SELECT oddeleni, COUNT(prijmeni) FROM zamestnanci GROUP BY oddeleni ORDER BY oddeleni; V tomto případě získám seznam oddělení a u každého počet zaměstnanců v daném oddělení. Při seskupování platí základní pravidla:
Každý sloupec v klauzuli SELECT má skupinovou funkci, nebo je v klauzuli GROUP BY.
Z předchozího pravidla vyplývá, že lze seskupovat podle několika kritérií.
Pozor na hodnotu NULL např. při použití funkce COUNT 11-2
GROUP BY a HAVING Základní syntax při seskupování vypadá následovně: SELECT sl1, sl2,..., funkce(sl3), funkce(sl4) FROM tabulka GROUP BY sl1, sl2, ...; Seskupovat můžeme podle jednoho nebo více sloupců. Pokud seskupujeme podle dvou a více sloupců, provádí se postupně zleva doprava. Chceme-li seskupené množiny omezit podmínkou, např. chceme vidět jen ta oddělení, která mají více než jednoho zaměstnance, přidáme do syntaxe klauzuli HAVING podmínka: SELECT oddeleni, MAX(plat) FROM zamestnanci GROUP BY oddeleni HAVING COUNT(*)>1; Vypíše maximální plat v těch odděleních, kde pracuje více než jeden zaměstnanec. Nezáleží na pořadí GROUP BY a HAVING, ale obě klauzule se musejí nacházet mezi WHERE a ORDER BY. Rozšíření ROLLUP a CUBE Jedná se o rozšíření, která jsou takto platná pouze v systému ORACLE, jiné DB systémy je implementují odlišnými způsoby. Tato rozšíření slouží k vypsání mezisoučtů a pomocných součtů pro seskupované sloupce. Vysvětlíme si na konkrétních příkladech: SELECT oddeleni, pozice, COUNT(*) FROM zamestnanci GROUP BY ROLLUP(oddeleni, pozice); Tento dotaz vrací počty zaměstnanců na pozicích v jednotlivých odděleních a navíc poskytne mezisoučty v rámci oddělení a celkový součet zaměstnanců nazávěr. SELECT oddeleni, pozice, COUNT(*) FROM zamestnanci GROUP BY CUBE(oddeleni, pozice); Oproti ROLLUP přidává navíc celkové součty zaměstnanců na daných pozicích. Např. kolik celkem pracuje účetních v celé firmě, kolik manažerů atd. Při seskupování řádků samozřejmě můžeme i spojovat tabulky. Vzniká tím komplexní dotaz, u kterého je potřeba věnovat pozornost správnému zápisu jednotlivých klauzulí. 11-3
Příklady k procvičení Vytvářejte dotazy podle následujícího zadání. Pod každým úkolem je prostor na poznámky: 1. Vypište čísla oddělení a počty zaměstnanců v jednotlivých odděleních. (EMOLOYEES)
2. Předchozí výpis proveďte pouze pro oddělení 20, 60, 80.
3. Z tabulky JOB_HISTORY vypište pro každého zaměstnance jeho číslo, datum poslední změny pozice a zkratku této pozice.
4. Z tabulky D_PLAY_LIST_ITEMS vypište jméno události a počet písniček, které budou na události hrány.
5. Vypište název země a počet lokací v této zemi. (COUNTRIES, LOCATIONS)
6. Vypište názvy regionů a počty zemí v těchto regionech. Sloupce vhodně pojmenujte. (COUNTRIES, REGIONS)
7. Vypište názvy oddělení a průměrný plat v každém z nich. Vypište pouze oddělení, která mají více než dva zaměstnance. Název oddělení bude velkými písmeny a plat bude mít symbol měny a mezerou oddělné řády. Např. $10 000.
8. Vypište maximální a minimální platy v odděleních, která mají průměrný plat vyšší než 10000. Vypište název oddělení a seřaďte data podle průměrného platu. (EMPLOYEES, DEPARTMENTS) 11-4
9. *** Vypište příjmení zaměstnance, jeho plat a počet jemu podřízených zaměstnanců včetně maximálního, minimálního a průměrného platu všech jeho podřízených. Sloupce vhodně pojmenujte a seřaďte data podle počtu podřízených.
10. *** V rámci předchozího dotazu vypište pouze vedoucí, kteří berou méně, než některý jejich zaměstnanec.
11. Příklad 9 doplňte tak, že nás zajímají jen vedoucí, kteří mají více než jednoho podřízeného.
12. *** Vypište součty platů na jednotlivých pozicích v jednotlivých odděleních. Chceme vidět i celkový součet pro jednotlivá oddělení a celou firmu. V reportu vypište název oddělení, název pozice. Do reportu nechceme zahrnout zaměstnance, kteří nepracuji v žádném oddělení. Všechny sloupce vhodně pojmenujte. (JOBS, EMPLOYEES, DEPARTMENTS)
13. Report z předchozího příkladu obohaťte o součty platů na jednotlivých pozicích ve firmě.
11-5
