Ciele
- Modifikácia štruktúry databázy (ALTER).
- SQL príkazy na modifikáciu obsahu databázy (INSERT, UPDATE, DELETE).
- Príkaz SELECT na definíciu otázok nad databázou.
- Usporiadanie výsledku prostredníctvom ORDER BY.
Úvod
Predchádzajúce cvičenia boli venované dátovému modelovaniu. Dnes prejdeme k manipulácii s údajmi, ktoré v databáze sú resp. chceme mať v databáze uložené. Hlavnou úlohou cvičenia bude precvičenie príkazu 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.
Postup
Krok 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 s pozorne pozriete 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 1.1
Takto dopočítavaný stĺpec môže spôsobovať anomálie pri modifikovaní údajov (porušuje 3NF). Zmeňte tabuľku invoice tak, že stĺpec total odstránite. Splnenie úlohy si overte vypísaním štruktúry tabuľky.
Krok 2
V ďalšom kroku vložíme do databázy nejaké údaje.
Úloha 2.1
V tabuľke so zákazníkmi vytvorte záznam o sebe a jeden záznam o osobe opačného pohlavia. Môžete však vytvoriť záznamov niekoľko (otec, mama, sestra, brat, prezident, "John Rambo", …). Vyplňte však iba povinné údaje. Ostatné hodnoty vypĺňať netreba. Hodnotu CUSTOMERID nastavte na 100 a v prípade pridávania ďalších záznamov pre každý ďalší záznam túto hodnotu zvýšte 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 venovať na jednej z neskorších prednášok, avšak už teraz je potrebné vedieť, že aby sa vo väčšine databázových systémov príkazy na modifikáciu údajov v databáze naozaj vykonali, je potrebné potvrdiť transakciu, v rámci ktorej boli tieto príkazy vykonané príkazom COMMIT. PostgreSQL implicitne využíva prístup automatického "COMMIT"-u po každom príkaze ktorý explicitne nezačína príkazom pre začatie transakcie. Demonštráciu jednoduchého využitia transakcií je uvedená nižšie:
-- start a transaction
BEGIN;
-- insert a new row into the table_name table
INSERT INTO table_name(collum1, collum2)
VALUES('value1', 'value2');
-- commit the change
COMMIT;
Poznámka
Pozor, to že Select vráti údaje po začatí transakcie, ktoré ste zadali aj bez COMMIT-u neznamená, že tie boli zapísané do DB. V takomto prípade Select iba ukáže, ako by to vyzeralo po ich zapísaní. V prípade, že pri manipulácií s databázou ste urobili po začatí transakcie nechcené operácie, môžete databázu jednoduchú vrátiť do povôdného stavu použitím príkazu ROLLBACK:
-- start a transaction
BEGIN;
-- insert a new row into the table_name table
INSERT INTO table_name(collum1, collum2)
VALUES('value1', 'value2');
-- roll back the change
ROLLBACK;
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 precvičovať nebudeme, ale môžete skúsiť zmazať jeden z novovytvorených záznamov.
Úloha 2.2
V rámci povyšovania zamestnancov sa generálny manažér rozhodol povýšiť Margaret Park-ovú na post 'Sales Manager'. Napíšte update nad tabuľkou employee, ktorý túto skutočnosť odzrkadlí, a splnenie úlohy si overte zobrazením obsahu tabuľky.
Krok 3
V tejto časti sa budeme venovať komunikácii s databázou - budeme klásť databáze otázky použitím príkazu SELECT.
Úloha 3.1
Zistite názvy všetkých skladieb, ktoré zložila skupina U2.
Úloha 3.2
Zistite, koľko sekúnd trvá skladba "One" od skupiny U2.
Úloha 3.3
Ktorých zákazníkov máme zo strednej Európy?
Poznámka
Táto otázka je trochu ťažšia. Je v tabuľke customer stĺpec, ktorý priamo zaznamenáva oblasť, z ktorej zákazník pochádza? Alebo je potrené zistiť, ktoré krajiny patria do strednej Európy?
Krok 4
V tejto časti využijeme vstavané funkcie.
Úloha 4.1
Zistite, ktorí zamestnanci majú narodeniny vo februári.
Poznámka
Ktorá funkcia umožní zistiť z dátumu číslo mesiaca? (Pamätajte, že pracujete s databázou PostgreSQL.)
Úloha 4.2
Vypíšte zoznam všetkých skladieb, pričom vypíšte 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 nezabudnite 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 je vhodné rozdeliť na podúlohy, ktoré sú ľahšie. Napr. túto úlohu ja možné rozložiť na niekoľko jednoduchších podproblémov:
- Ako zaokrúhliť hodnotu čísla na 2 desatinné miesta?
- Ako vypočítať hodnotu skladby v eurách, resp. v CZK?
- Ako pripísať k výsledku aj znak meny?
- A ako správne pomenovať stĺpec?
Krok 5
V tomto kroku prejdime k práci s reťazcami:
Úloha 5.1
Vypíšte zoznam všetkých zákazníkov, ktorí majú svoj e-mailový účet na gmail.com alebo na yahoo.com.
Poznámka
Ošetrite aj to, aby sa do výsledku nedostali mailové adresy ako napr. login@mygmail.com
Krok 6
V tomto poslednom kroku sa zameriame zoradenie výsledkov. Ak sa Vám úloha bude zdať ťažká, opäť sa pokúste rozložiť ju na podúlohy.
Úloha 6.1
Vypíšte zoznam všetkých zamestnancov, ktorí sa narodili v 70tych rokoch minulého tisícročia. Vypíšte ich v poradí od najstaršieho po najmladšieho.
Obsah tohto cvičenia je naplnený. Ak ste stihli vypracovať všetky úlohy, pokračujte doplňujúcimi úlohami.
Zdroje
Doplňujúce úlohy
Úloha A.1
Chinook
Rozšírte 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 A.2
Chinook
Zmeňte 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 A.3
Podnik
Vložte 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-may-1977', 'Biela 56', 'Moldava', 0, 'slobodny', 13000
99, 'Rybansky', 'Marian', '22-jul-1969', 'Ruzova 7', 'Bratislava', 3, 'zenaty', 14000
100, 'Dodatocny', 'Jozef', '01-jan-1967', 'Modra 5', 'Bratislava', 0, 'slobodny', 9000
Úloha A.4
Podnik
Vypíšte zoznam všetkých miest, ktoré sa vyskytujú v tabuľke podnik.
Úloha A.5
Podnik
Z tabuľky Podnik vyhľadajte 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ďte ich od najstaršieho po najmladšieho.
Úloha A.6
Podnik
Vyhľadajte všetkých pracovníkov mimo Košíc, ktorých priezvisko obsahuje písmeno "a" alebo ich krstné meno sa začína na "M". Usporiadajte ich podľa abecedy (najprv podľa priezviska, potom podľa mena).
Úloha A.7
Podnik
Vypíšte zoznam všetkých pracovníkov z tabuľky Podnik usporiadaných podľa jednotlivých miest.
Úloha A.8
Podnik
Vyhľadajte z tabuľky podnik všetkých Prešovčanov, ktorým zostáva do dôchodku viac ako 5 rokov (predpokladajme dôchodkový vek 60 rokov).