Vnorené dopyty

Ciele
  1. Precvičiť vnorené selecty
  2. Vyskúšať si operátory IN a EXISTS
  3. Vyskúšať si vnorené selecty pri modifikačných príkazoch
Úvod
    Pracujeme na databáze Chinook s nasledovnou schémou.
    Obr.: : Relačný model databázy Chinook
Postup
  1. Úloha: Zisti názov najdlhšej skladby v obchode. Pre overenie vedz, že jej dĺžka v milisekundách je 5286953.
    Úloha: Nájdi najdlhšiu skladbu v albume War od U2.
    Poznámka: Opäť rozmýšľaj iteratívne, po krokoch. Skús najprv nájsť dĺžku najdlhšej skladby v albume War. Potom napíš select, ktorým nájdeš názov skladby v albume War, ak poznáš jej dĺžku. A nakoniec tieto dva dopyty spoj do jedného.
    Poznámka: Nezabudni na to, že rovnakú dĺžku ako má najdlhšia skladba z War, môže mať aj skladba z iného albumu.
  2. Úloha: 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: Vypíš 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íš jednu verziu s použitím operátora IN (resp. NOT IN), a jednu verziu s operátorom EXISTS (resp. NOT EXISTS).
  3. Úloha: Vypíš informácie o zákazníkovi, ktorý minul v obchode najviac peňazí (súčet jeho objednávok je najvyšší). Vypíš jeho ID, meno, priezvisko a celkovú sumu (pre overenie je to 49,62 dolárov).
    Poznámka: Ak si nevieš rady, zobraz si nasledujúcu nápovedu (avšak odporúčam ti najprv sa poriadne zamyslieť).
    Zobraziť riešenie
    Úloha: Vypíš 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š musieť použiť pseudostĺpec ROWNUM (pozri prednášku 06. Príkaz SELECT).
  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: Napíš UPDATE, ktorý zlacní o 20% všetky skladby, ktoré majú nadpriemernú cenu. Pre overenie vedz, že upraviť máš 213 záznamov.
    Poznámka: Prosím, riešenie tejto úlohy urob v transakcii, ktorú nepotvrdzuj, ale na konci zruš. 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ži príkaz ROLLBACK na návrat do pôvodného stavu DB.
Zdroje
  1. PL/SQL tutorial
  2. Prednáška 17: Triggery
  3. Používanie triggerov v Oracle
Doplňujúce úlohy
    Úloha:

    Chinook

    Vypíš zoznam skladieb, ktoré boli kúpené aspoň 100x. Pre overenie správnosti dopytu vedz, že počet týchto skladieb je 0 a počet skladieb predaných práve 2x je 256.
    Úloha:

    Chinook

    Zisti, ktorý z playlistov je najdlhší (môže ich byť viac s rovnakou dĺžkou). Pre overenie správnosti dopytu vedz, že sú dva, oba sa volajú 'Music', jeden s id 1 a druhý s id 8.
    Úloha:

    Chinook

    Pridaj 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:

    Chinook

    Napíš 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: Opäť, ak si nevieš rady, skús si zobraziť nápovedu (avšak odporúčam ti najprv sa poriadne zamyslieť).
    Zobraziť riešenie
comments powered by Disqus