Ciele
- Agregačné funkcie pre agregovanie kolekcie hodnôt do jednej hodnoty.
- Zoskupovanie záznamov do skupín podľa rovnakej hodnoty v zadanom stĺpci.
- Množinové operácie.
Úvod
Pracujeme na databáze Chinook s nasledovnou schémou.
Postup
Krok 1
Agregačné funkcie umožňujú agregovať viaceré hodnoty do jednej - napr. count zráta počet záznamov:
SELECT count(*) FROM album;
Vďaka agregačným funkciám je možné spočítať počet špecifických záznamov (napr. koľko umelcov nemá žiaden album), vypočítať priemernú hodnotu, maximálnu hodnotu, atď.
Úloha 1.1
Koľko skladieb sa nachádza v albume War? Pre overenie správnosti ich je 10.
Úloha 1.2
Zistite, koľko bolo spolu predaných skladieb z albumu War od U2. Pre overenie vedzte, že ich bolo 11.
Úloha 1.3
Zistite čas v sekundách najdlhšej skladby, najkratšej skladby a priemerný čas skladieb albumu War od U2. Pre overenie vedzte, že priemerný čas skladieb je 252,5 sekúnd.
Krok 2
Použitie agregačných funkcií je zvlášť zaujímavé v kombinácii so zoskupením výsledkov. Zoskupovanie s GROUP BY klauzulou umožňuje získať sumárne hodnoty pre skupiny záznamov podľa hodnoty vybraného stĺpca. Napr. je možné získať počet skladieb podľa skladateľa:
SELECT composer, COUNT(*)
FROM track
GROUP BY composer
ORDER BY COUNT(*) desc;
Úloha 2.1
Vypíšte krajiny a počet zákazníkov pre každú z nich. Pre overenie správnosti dopytu vedzte, že najviac zákazníkov (13) je z USA.
Úloha 2.2
Vypíšte sumárne zisky z predaja po jednotlivých rokoch. Pre overenie správnosti dopytu vedzte, že celkový zisk z predaja v roku 2009 bol $449.46.
Poznámka
V GROUP BY klauzule nemusí byť len priamo stĺpec, môže tam byť aj nejaký výraz, ktorý z riadku v tabuľke vypočíta hodnotu.
Úloha 2.3
Vypíšte sumárne zisky z predaja po jednotlivých rokoch a mesiacoch. Pre overenie správnosti dopytu vedzte, že celkový zisk z predaja v apríli roku 2011 bol $51.62.
Úloha 2.4
Upravte SELECT z predchádzajúcej úlohy tak, aby sa vypísali iba tie mesiace, v ktorých bol zisk aspoň $50. Pre overenie vedzte, že také mesiace boli iba 3.
Úloha 2.5
Vypíšte sumárne zisky z predaja po jednotlivých rokoch a mesiacoch tak, aby vo výsledku boli zároveň sumárne zisky po jednotlivých rokoch, a zároveň sumárny zisk dokopy. Opäť ide o modifikáciu pred-predchádzajúcej úlohy.
Poznámka
Nepoužívajte pri tom množinové operácie.
Krok 3
Množinové operácie pracujú s tabuľkami a výsledkami SELECT-ov ako s množinami a umožňujú nám z nich vytvárať zjednotenie, prienik a množinový rozdiel. Prostredníctvom množinových operácií si napr. je možné ľahko zistiť či v systéme sa nachádza zákazník s rovnakým priezviskom ako má niektorí zo zamestnancov:
SELECT lastname
FROM employee
INTERSECT
SELECT lastname
FROM customer;
Úloha 3.1
Zistite, ktorí umelci nie sú zároveň skladateľmi. Pre overenie vedzte, že ich je 228.
Úloha 3.2
Získajte zoznam umelcov aj skladateľov, ktorých nájdete v tabuľkách Artist a Track (bez duplikátov). Do výsledku neuveďte null hodnotu, ktorá sa nachádza v skladateľovi, ak je pre skladbu neznámy. Výsledok usporiadajte podľa abecedy. Pre overenie vedzte, že dokopy je umelcov a skladateľov 1080.
Úloha 3.3
Vypíšte zoznam všetkých skladieb (aspoň názvy spolu s názvom skladateľa), ktoré nepatria do playlistu 'Heavy Metal Classic'. Pre overenie správnosti je počet týchto skladieb 3477.
Poznámka
Niektoré skladby môžu mať rovnaký názov i skladateľa, ale nie sú tie isté.
Zdroje
Doplňujúce úlohy
Úloha A.1
Chinook
Vypíšte sumárne počet skladieb v jednotlivých playlistoch. Pre overenie správnosti vedzte, že v playliste TV Shows sa nachádza 213 skladieb a v playliste Audiobooks sa nenachádza žiadna skladba (počet je 0). Výsledný zoznam usporiadajte vzostupne podľa názvu playlistu.
Poznámka
Niektoré playlisty majú rovnaké meno, aj keď nejde o ten istý playlist.
Poznámka
Niektoré playlisty nemajú žiadne skladby.
Úloha A.2
Chinook
Vypíšte zisky sumárne podľa žánru. Žáner vypíšte, aj keď nemá žiadny zisk. Pre overenie vedzte, že najlepšie zarábajúci žáner je Rock so ziskom $826,65.
Úloha A.3
Chinook
Posledná úloha z kroku č. 2 si vyžadovala použitie príkazu ROLLUP. Prepíšte riešenie úlohy bez použitia ROLLUP iba s použitím množinových operácií.
Poznámka
Znenie pôvodnej úlohy pre zopakovanie: Vypíšte sumárne zisky z predaja po jednotlivých rokoch a mesiacoch tak, aby vo výsledku boli zároveň sumárne zisky po jednotlivých rokoch, a zároveň sumárny zisk dokopy.
Úloha A.4
Chinook
Vypíšte zoznam všetkých skladieb (aspoň názvy spolu s názvom skladateľa), ktoré nepatria do playlistu 'Heavy Metal Classic'.
Poznámka
Úloha je rovnaká ako posledná v kroku 3, tentokrát ju však vyriešte iba s použitím spájania tabuliek.
Úloha A.5
Podnik
Zistite koľko rôznych stavov majú pracovníci z miest Prešov a Košice (názov mesta a počet stavov). Vo výsledku bude napr. záznam Košice 1, pretože zamestnanci z Košíc sú všetci ženatí (teda v Košiciach je len 1 stav - ženatý).
Úloha A.6
Podnik
Vypíšte počet pracovníkov, ich priemernú mzdu, maximálnu a minimálnu mzdu, z tých miest, v ktorých počet pracovníkov je vyšší ako 2.
Úloha A.7
Podnik
Vypíšte sumárne podľa miest zamestnancov sumu nevyčerpanej dovolenky v roku 2015. Pre overenie vedzte, že zamestnanci z Košíc v roku 2015 nevyčerpali 10 dní dovolenky.