Vkladanie údajov do databázy a prvé dopyty

Ciele
  1. Modifikácia štruktúry databázy (ALTER).
  2. SQL príkazy na modifikáciu obsahu databázy (INSERT, UPDATE, DELETE).
  3. Príkaz SELECT na definíciu otázok nad databázou.
  4. Usporiadanie výsledku prostredníctvom ORDER BY.
Úvod
    Doteraz sme pracovali s opisom databázy. Dnes sa pustíme do manipulácie s údajmi, ktoré v databáze chceme mať uložené. V rámci cvičenia budeme precvičovať príkaz SELECT:
    
    SELECT   čo
    FROM     odkiaľ
    WHERE    čo_nás_zaujíma
    ORDER BY podľa_čoho_triediť;
    Pracujeme na databáze Chinook s nasledovnou schémou.
    Obr.: : Relačný model databázy Chinook
Postup
  1. V rámci opakovania z minulého týždňa začneme modifikovaním opisu databázy, teda toho, ako databáza vyzerá. Ak sa opäť pozrieš na model databázy Chinook, môžeš si všimnúť dopočítavaný stĺpec total v tabuľke invoice, kde je celková cena faktúry, ktorá sa dá vypočítať ako suma cien za jednotlivé položky na faktúre.
    Úloha: Keďže vieš, že takto dopočítavaný stĺpec môže spôsobovať anomálie pri modifikovaní údajov (porušuje 3NF), zmeň tabuľku invoice tak, že stĺpec total odstrániš. Splnenie úlohy si over vypísaním štruktúry tabuľky.
  2. No dobre, povedzme že problém s modifikáciou štruktúry databázy nemáš. Poďme teda do databázy vkladať nejaké údaje. Je pravda, že Chinook v sebe má už pomerne slušný objem údajov, ale treba aby si si to skúsil aj ty.
    Úloha: V tabuľke so zákazníkmi vytvor záznam o sebe a jeden záznam o osobe opačného pohlavia. Môžeš však vytvoriť záznamov niekoľko (otec, mama, sestra, brat, prezident, "John Rambo", …). Vyplň však iba povinné údaje! Ostatné hodnoty vypĺňať netreba. Hodnotu CUSTOMERID nastav na 100 a v prípade pridávania ďalších záznamov pre každý ďalší záznam túto hodnotu zvýš o 1.
    Poznámka:

    Databázové systémy podporujú transakcie - sekvencie príkazov, ktoré sa majú vykonať ako jeden celok. Podrobnejšie sa budeme o transakciách baviť na jednej z neskorších prednášok, avšak už teraz potrebuješ vedieť, že ak chceš aby sa príkazy na modifikáciu údajov v databáze naozaj vykonali, musíš potvrdiť transakciu, v rámci ktorej boli tieto príkazy vykonané (transakcia sa začne automaticky prvým príkazom, a končí príkazom potvrdenia alebo zrušenia transakcie).

    Preto aby sa zmeny naozaj zapísali do databázy, po dopísaní modifikačných príkazov pošli databáze príkaz:

    COMMIT;
    Pozor, to že ti select vráti údaje, ktoré si tam zadal aj bez COMMIT-u neznamená, že tie boli zapísané do DB. Select ti iba ukáže, ako by to vyzeralo po ich zapísaní.

    Príkazom INSERT vieme vkladať do databázy nové riadky (záznamy). Ak chceme zmeniť tie existujúce, používame príkaz UPDATE. Príkaz DELETE na mazanie si teraz precvičovať nebudeme, ale môžeš skúsiť zmazať jeden z novovytvorených záznamov a nepotvrdiť ho COMMIT-om, aby si o zadané údaje neprišiel (transakciu, ktorá ti začne týmto príkazom DELETE vieš zrušiť príkazom ROLLBACK, ktorý je náprotivkom COMMIT-u).
    Úloha: V rámci povyšovania zamestnancov sa generálny manažer rozhodol povýšiť Margaret Park-ovú na post 'Sales Manager'. Napíš update nad tabuľkou employee, ktorý túto skutočnosť odzrkadlí, a splnenie úlohy si over zobrazením obsahu tabuľky (použi select).
  3. Tak a teraz poďme na tú najzaujímavejšiu časť komunikácie s databázou - budeme klásť databáze otázky použitím príkazu SELECT

    Začnime však zľahka...

    Úloha: Zisti názvy všetkých skladieb, ktoré zložila skupina U2.
    Úloha: Zisti, koľko sekúnd trvá skladba "One" od skupiny U2.
    Úloha: Ktorých zákazníkov máme zo strednej Európy?
    Poznámka: Táto otázka je kúsok ťažšia. Je v tabuľke customer nejaký stĺpec, ktorý priamo zaznamenáva oblasť, z ktorej zákazník pochádza? Alebo budeš musieť zistiť, ktoré krajiny patria do strednej Európy?
  4. Ľahké, že? Skúsme do toho teraz zapojiť aj nejaké vstavané funkcie.
    Úloha: Zisti, ktorí zamestnanci majú narodeniny vo februári.
    Poznámka: Ktorá funkcia ti umožní zistiť z dátumu číslo mesiaca? (Pamätaj, že pracuješ s databázou Oracle.)
    Úloha: Vypíš zoznam všetkých skladieb, pričom vypíš jej názov, skladateľa, jednotkovú cenu v dolároch (hodnota v stĺpci UnitPrice), cenu v eurách (1$=0.76€) a v českých korunách (1$=19.20CK). Pri vypisovaní jednotlivých prepočítaných cien nezabudni správne nazvať aj stĺpec (napr. USD, EUR, CZK) a za hodnotu pripíš aj samotnú jednotku ($, €, CK). Každú cenu zaokrúhli na 2 desatinné miesta.
    Poznámka: Ťažšie, resp. zložitejšie úlohy sa snaž rozdeliť si na podúlohy, ktoré sú ľahšie a rieš tú úlohu postupne. Napr. túto úlohu vieš rozložiť na niekoľko jednoduchších podproblémov:
    • Ako zaokrúliš hodnotu čísla na 2 desatinné miesta?
    • Ako vypočítaš hodnotu skladby v eurách, resp. v CZK?
    • Ako pripíšeš k výsledku aj znak meny?
    • A ako správne pomenuješ stĺpec?
  5. Dobre, a teraz sa skúsme trošku pohrať s reťazcami:
    Úloha: Vypíš zoznam všetkých zákazníkov, ktorí majú svoj e-mailový účet na gmail.com alebo na yahoo.com.
    Poznámka: Ošetri aj to, aby sa do výsledku nedostali mailové adresy ako napr. milan.nosal@mygmail.com
  6. V tomto poslednom kroku si ešte vyskúšame zoradzovanie výsledkov. Ak sa ti úloha bude zdať ťažká, opäť sa pokús rozložiť si ju na podúlohy.
    Úloha: Vypíš zoznam všetkých zamestnancov, ktorí sa narodili v 70tych rokoch minulého tisícročia. Vypíš ich v poradí od najstaršieho po najmladšieho.
    Týmto sa uzatvára dnešné cvičenie. Ak si stihol viac, alebo sa chceš len precvičiť, skús sa pozrieť na doplňujúce úlohy.
Zdroje
  1. Prednáška 07: Spájanie tabuliek
  2. Prednáška 13: Sekvencie
  3. Príklad na neasociatívnosť vonkajšieho spájania
Doplňujúce úlohy
    Úloha:

    Chinook

    Rozšír tabuľku CUSTOMER o nový stĺpec s názvom SEX, ktorý bude hovoriť o pohlaví používateľa. O tomto stĺpci platí:
    • bude mať veľkosť 1 znak,
    • bude povinný,
    • bude môcť obsahovať len hodnoty 'M' (Male) alebo 'F' (Female),
    • bude mať predvolenú hodnotu nastavenú na 'M'.
    Úspešnosť operácie si over zobrazením schémy tabuľky.
    Úloha:

    Chinook

    Zmeň pohlavie na ženské tým osobám v tabuľke CUSTOMER, ktorých meno môže byť nasledovné: 'Helena', 'Kara', 'Fernanda', 'Jenifer', 'Kathy', 'Heather', 'Julia', 'Martha', 'Ellie', 'Madalena', 'Hannah', 'Camille', 'Isabelle', 'Emma' a 'Puja'
    Úloha:

    Podnik

    Vlož do tabuľky Podnik nasledujúce údaje:
    
    Ev_cislo,   Priezvisko,     Meno,       Narodeny,       Ulica,              Mesto,          Poc_deti,   Stav,            Mzda
    11,         'Bajny',        'Ivan',     '05-dec-1967',  'Ruzova 12',        'Kosice',       2,          'zenaty',       15000
    22,         'Maly',         'Jan',      '22-feb-1973',  'Letna 15',         'Presov',       1,          'zenaty',       12000
    33,         'Velky',        'Igor',     '19-dec-1969',  'Bulharska 34',     'Zilina',       0,          'slobodny',     10500
    44,         'Kral',         'Peter',    '18-jan-1945',  'Vihorlatska 50',   'Kosice',       0,          'zenaty',       21000
    55,         'Malorolnik',   'Valer',    '24-mar-1972',  'Hlavna 5',         'Presov',       1,          'zenaty',       14500
    66,         'Vodny',        'Marian',   '24-nov-1957',  'Gerlachovska 1',   'Zilina',       0,          'slobodny',     16000
    77,         'Kristof',      'Jan',      '25-dec-1978',  'Internatna 5',     'Kosice',       1,          'zenaty',        8000
    88,         'Kocis',        'Martin',   '04-máj-1977',  'Biela 56',         'Moldava',      0,          'slobodny',     13000
    99,         'Rybansky',     'Marian',   '22-júl-1969',  'Ruzova 7',         'Bratislava',   3,          'zenaty',       14000
    100,        'Dodatocny',    'Jozef',    '01-jan-1967',  'Modra 5',          'Bratislava',   0,          'slobodny',      9000
    Úloha:

    Podnik

    Vypíš portfolio všetkých miest, ktoré sa vyskytujú v tabuľke podnik.
    Úloha:

    Podnik

    Z tabuľky Podnik vyhľadaj priezvisko, meno a dátum narodenia všetkých pracovníkov, ktorí sa narodili v čase od 1.1.1985 do 31.12.2005. Zoraď ich od najstaršieho po najmladšieho.
    Úloha:

    Podnik

    Vyhľadaj všetkých pracovníkov mimo Košíc, ktorých priezvisko obsahuje písmeno "a" alebo ich krstné meno sa začína na "M". Usporiadaj ich podľa abecedy (najprv podľa priezviska, potom podľa mena).
    Úloha:

    Podnik

    Vypíš zoznam všetkých pracovníkov z tabuľky Podnik usporiadaných podľa jednotlivých miest.
    Úloha:

    Podnik

    Vyhľadaj z tabuľky podnik všetkých Prešovčanov, ktorým zostáva do dôchodku viac ako 15 rokov (predpokladajme dôchodkový vek 60 rokov).
comments powered by Disqus