Ciele
- Precvičiť vnorené selecty
- Vyskúšať si operátory IN a EXISTS
- Vyskúšať si vnorené selecty pri modifikačných príkazoch
Úvod
Pracujeme na databáze Chinook s nasledovnou schémou.
Postup
Krok 1
Úloha 1.1
Zistite názov najdlhšej skladby v obchode. Pre overenie vedzte, že jej dĺžka v milisekundách je 5286953.
Úloha 1.2
Nájdite najdlhšiu skladbu v albume War od U2.
Poznámka
Úlohu riešte iteratívne, po krokoch. Najprv skúste nájsť dĺžku najdlhšej skladby v albume War. Potom napíšte select, ktorým nájdete názov skladby v albume War, ak poznáte jej dĺžku. A nakoniec tieto dva dopyty spojte do jedného.
Poznámka
Nezabudnite na to, že rovnakú dĺžku ako má najdlhšia skladba z War, môže mať aj skladba z iného albumu.
Krok 2
Úloha 2.1
Vypíš zoznam skladieb, ktoré neboli kúpené ani raz. Pre overenie správnosti dopytu vedz, že počet týchto skladieb je 1519.
Poznámka
Tie, ktoré neboli kúpené, sa nenachádzajú ako položka na žiadnej faktúre (ich id nie je uvedené v žiadnom zázname tabuľky INVOICELINE).
Úloha 2.2
Vypíšte názvy všetkých skladieb, ktoré nepatria do playlistu Music Videos. Pre overenie správnosti je celkový počet týchto skladieb 3502.
Poznámka
Napíšte jednu verziu s použitím operátora IN (resp. NOT IN), a jednu verziu s operátorom EXISTS (resp. NOT EXISTS).
Krok 3
Úloha 3.1
Vypíšte informácie o zákazníkovi, ktorý minul v obchode najviac peňazí (súčet jeho objednávok je najvyšší). Vypíšte jeho ID, meno, priezvisko a celkovú sumu (pre overenie je to 49,62 dolárov).
Poznámka
V prípade, že neviete vyriešiť úlohu, zobrazte si nasledujúcu nápovedu (avšak odporúčame najprv pokúsiť sa úlohu vyriešiť samostatne).
Riešenie
Najprv získajte zoznam zákazníkov s celkovou sumou, ktorú minuli, zoraďte ich podľa danej sumy a potom použite vonkajší select na to, aby si vybral iba prvý záznam (tentokrát vnorený dopyt bude v klauzule FROM).
Samozrejme, existuje viacero spôsobov, ako túto úlohu vyriešiť, je možné použiť aj agregačné funkcie.
Úloha 3.2
Vypíšte prvých 10 jedinečných (neopakujúcich sa) názvov skladieb z playlistu 'TV Shows' usporiadaných podľa abecedy vzostupne.
Poznámka
Tentokrát bude potrebné použiť klauzulu LIMIT.
Krok 4
Vnoriť dopyt môžeme aj pri modifikačných príkazoch (INSERT, UPDATE, DELETE). Pomocou takýchto vnorených dopytov môžeme modifikovať databázu dynamicky na základe jej aktuálneho stavu.
Úloha 4.1
Napíšte UPDATE, ktorý zlacní o 20% všetky skladby, ktoré majú nadpriemernú cenu. Pre overenie vedzte, že upraviť máš 213 záznamov.
Poznámka
Prosím, riešenie tejto úlohy vypracovať v transakcii, ktorú na konci nepotvrdzujte ale zrušte. Pomocné riešenia v ďalších úlohách budú uvádzať na overenie údaje na pôvodnom stave databázy. Tzn. že COMMIT by mohol spôsobiť, že údaje získané správnym riešením SELECT-u by sa mohli líšiť od tých, ktoré uvádzam pre overenie správnosti. Po overení správneho vykonania modifikačných príkazov na konci tejto úlohy jednoducho použite príkaz ROLLBACK na návrat do pôvodného stavu DB.
Zdroje
Doplňujúce úlohy
Úloha A.1
Chinook
Vypíšte zoznam skladieb, ktoré boli kúpené aspoň 100x. Pre overenie správnosti dopytu vedzte, že počet týchto skladieb je 0 a počet skladieb predaných práve 2x je 256.
Úloha A.2
Chinook
Zistite, ktorý z playlistov je najdlhší (môže ich byť viac s rovnakou dĺžkou). Pre overenie správnosti dopytu vedzte, že sú dva, oba sa volajú 'Music', jeden s id 1 a druhý s id 8.
Úloha A.3
Chinook
Pridajte do tabuľky 'customer' stĺpec 'premium', o ktorom bude platiť nasledovné:
- bude to práve jeden znak z množiny {'T', 'F'}, čiže zákazník buď je, alebo nie je prémiový,
- predvolene každý zákazník nie je prémiový (čiže má hodnotu 'F'), a
- hodnota v stĺpci je povinná.
Úloha A.4
Chinook
Napíšte UPDATE s poddopytom, ktorý nastaví príznak 'premium' na 'T' (urobí ich prémiovými, môže to byť užitočné napr. pri nejakých zľavách pre verných zákazníkov) tým zákazníkom, ktorí v obchode minuli viac ako 40 dolárov.
Poznámka
Ak si neviete rady, skúste si zobraziť nápovedu (avšak odporúčam najprv sa pokúsiť vyriešiť úlohu samostatne).
Riešenie
Opäť je potrebné riešiť úlohu iteratívne. Ako zistíte, ktorí zákazníci minuli viac ako 40 dolárov? Bude potrebné prepojiť zákazníkov až s položkami na ich faktúrach. Tam potrebujete nájsť sumu všetkých ich položiek zoskupením cez id zákazníka (cena jednej položky je quantity*unitprice). Z týchto skupín záznamov, kde jedna skupina predstavuje zaplatenú sumu pre jedného zákazníka odfiltrujete len tie skupiny so sumou nad 40 dolárov. Potom z toho získať id zákazníkov je triviálna úloha. Daným zákazníkom potrebujete nastaviť príznak premium na T.