Ciele
- Modifikácia štruktúry databázy (ALTER).
- SQL príkazy na modifikáciu obsahu databázy (INSERT, UPDATE, DELETE).
- Príkaz SELECT.
Úvod
Na tomto cvičení sa budeme venovať manipulácii s údajmi. Hlavnou úlohou cvičenia bude precvičenie príkazu SELECT. Príkaz SELECT je jednou z najbežnejších úloh pri práci s databázou slúžiaci na dopytovanie údajov z tabuliek. Je jedným z najkomplexnejších príkazov v PostgreSQL. Má veľa klauzúl, ktoré môžete použiť na vytvorenie flexibilného dotazu.
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 Movies, môžte si všimnúť vygenerovaný stĺpec per_age predstavujúci vek osoby v tabuľke t_person. Vygenerovaný stĺpec je špeciálny stĺpec, ktorý sa vždy počíta z iných stĺpcov (v našom prípade zo stĺpca per_birthdate). Existujú dva druhy vygenerovaných stĺpcov: STORED a VIRTUAL. PostgreSQL momentálne implementuje iba uložené vygenerované stĺpce. Na výpočet veku bola použitá funkcia AGE().
Úloha 1.1
Takto vygenerovaný stĺpec môže spôsobovať anomálie. Zmeňte tabuľku t_person tak, že stĺpec per_age odstránite. Splnenie úlohy si overte vypísaním štruktúry tabuľky.
Riešenie
ALTER TABLE t_person
DROP COLUMN per_age;
Poznámka
Vygenerovaný stĺpec per_age si zachová svoju hodnotu (vek osoby vzhľadom na to, kedy sme skript movies.sql spustili). Postupom času by bola nutná neustála aktualizácia hodnôt tohto stĺpca (po narodeninách osoby znovu vek prepočítať a uložiť).
Úloha 1.2
Ak na stĺpci, ktorý chceme vymazať závisia aj iné objekty, napríklad triggre, pohľady, storované procedúry atď, nedokážeme ho vymazať. Na odstránenie takých stĺpcov spolu so všetkými na nich závislými objektami použite možnosť CASCADE.
Riešenie
ALTER TABLE t_person
DROP COLUMN per_age CASCADE;
Znova spustite príkaz na zmazanie stĺpca per_age z predchádzajúcej úlohy.
Úloha 1.3
Ak chceme odstrániť neexistujúci stĺpec, PostgreSQL vypíše chybu. Aby sme tomu predišli, pridajte podmienku IF EXISTS do príkazu drop column.
Riešenie
ALTER TABLE t_person
DROP COLUMN IF EXISTS per_age CASCADE;
Krok 2
Úloha 2.1
Do tabuľky c_country, ktorá predstavuje číselník krajín, pridajte záznam 'Slovak Republic' s id "SVK". Môžete však vytvoriť aj viacero záznamov.
Riešenie
INSERT INTO c_country
(cou_name, cou_inserted, cou_inserted_by, cou_id)
VALUES('Slovak Republic', now(), SESSION_USER, 'SVK');
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
Kino "Cinemax Prešov" sa presťahovalo z Fábryho 54 na Vihorlatskú 2. Napíšte update nad tabuľkou t_cinema, ktorý túto skutočnosť odzrkadlí, a splnenie úlohy si overte zobrazením obsahu tabuľky.
Riešenie
UPDATE t_cinema
SET cin_street = 'Vihorlatská 2'
WHERE cin_name = 'Cinemax Prešov';
Náš dátový model obsahuje okrem iného aj tabuľky c_city a c_country. Tieto tabuľky je potrebné prepojiť, aby bolo jasné, v ktorom štáte je dané mesto.
Úloha 2.3
Do tabuľky c_city doplňte stĺpec s názvom xcit_cou_id. Stĺpec nastavte ako povinný, s predvolenou hodnotu rovnou id Slovenskej republiky. Dátový typ je bpchar(3).
Riešenie
ALTER TABLE c_city
ADD xcit_cou_id bpchar(3) NOT NULL
DEFAULT 'SVK';
Úloha 2.4
Vytvorte cudzí kľúč z tabuľky c_city (zo stĺpca xcit_cou_id) odkazujúci na tabuľku c_country na stĺpcec cou_id. Nezabudnite určiť, čo sa má stať, ak primárny kľúč v rodičovskej tabuľke bude zmazaný.
Riešenie
ALTER TABLE c_city ADD CONSTRAINT c_city_fk FOREIGN KEY (xcit_cou_id) REFERENCES c_country(cou_id) ON DELETE CASCADE;
Poznámka
ON DELETE CASCADE je v praxi najčastejšie využívanou možnosťou. Znamená to, že v prípade, ak bude z rodičovskej tabuľky vymazaný riadok, na ktorý sa odkazujú iné riadky "detskej" tabuľky, automaticky budú odstránené aj všetky tieto riadky v tabuľke dieťa.
Úloha 2.5
Prezrite si záznamy v tabuľke c_city. Mestá, ktoré sa nenachádzajú v defaultne nastavenej Slovenskej republike, zaraďte do správnej krajiny.
Riešenie
UPDATE c_city SET xcit_cou_id = 'CZE'
WHERE cit_name = 'Praha';
UPDATE c_city SET xcit_cou_id = 'GBR'
WHERE cit_name = 'London';
UPDATE c_city SET xcit_cou_id = 'GER'
WHERE cit_name = 'Berlín';
UPDATE c_city SET xcit_cou_id = 'ITA'
WHERE cit_name = 'Rím';
Krok 3
Úloha 3.1
Zistite krstné meno a priezvisko všetkých žien z tabuľky t_person. Stĺpce vo výsledku pomenujte ako "Krstne meno" a "Priezvisko". Výsledok obsahuje 25 záznamov.
Riešenie
SELECT per_first_name "Krstne meno",
per_last_name "Priezvisko"
FROM t_person tp
WHERE tp.xper_sex_id = 'F';
Úloha 3.2
Riešenie
SELECT Round(Avg(mov_duration), 2) "Priemerna dlzka"
FROM t_movie tm;
Poznámka
PostgreSQL poskytuje množstvo vstavaných funkcií (funkcie na prácu s reťazcami, matematické funkcie, funkcie na prácu s XML a ďalšie) a operátorov, ktoré môžete využívať pri písaní selectov. Nájdete ich na tomto odkaze. Je dobré si ich preštudovať, aby ste ťažkopádne nevytvárali niečo, čo môžte realizovať zavolaním hotovej vstavanej funkcie.
Úloha 3.3
Táto úloha nadväzuje na predchádzajúcu. Zistite názvy filmov z tabuľky t_movie, ktorých dĺžka trvania je menšia ako priemerná dĺžka trvania všetkých filmov v tabuľke. Stĺpce vo výsledku pomenujte ako "Film" a "Dlzka v minutach". Takýchto filmov je 11.
Riešenie
SELECT mov_name "Film",
mov_duration "Dlzka v minutach"
FROM t_movie tm
WHERE tm.mov_duration < (SELECT Round(Avg(mov_duration), 2) "Priemerna dlzka"
FROM t_movie tm2);
Úloha 3.4
Vypíšte názvy filmov a ich dátum vydania z tabuľky t_movie, ktoré boli vydané v marci alebo v septembri. Stĺpce vo výsledku pomenujte ako "Film" a "Datum vydania". Takýchto filmov je 5.
Riešenie
SELECT mov_name "Film",
mov_release_date "Datum vydania"
FROM t_movie tm
WHERE ( Extract (month FROM mov_release_date) = 3
OR Extract (month FROM mov_release_date) = 9 );
Úloha 3.5
Zistite krstné meno, priezvisko, dátum narodenia a vek mužov z tabuľky t_person, ktorí sú narodení medzi dátumom 1976-06-06 a 1978-05-31. Vypočítajte ich vek k terajšiemu dátumu (použiť môžte funkciu AGE()). Stĺpce vo výsledku pomenujte ako "Krstne meno", "Priezvisko", "Datum narodenia", "Vek". Takýchto záznamov je 5.
Riešenie
SELECT per_first_name "Krstne meno",
per_last_name "Priezvisko",
per_birthdate "Datum narodenia",
Age(per_birthdate) "Vek"
FROM t_person tp
WHERE tp.xper_sex_id = 'M'
AND tp.per_birthdate BETWEEN ( To_date('1976-06-06', 'YYYY-MM-DD') ) AND
(
To_date('1978-05-31', 'YYYY-MM-DD') );
Poznámka
Je dôležité porozumieť práci s dátumovými hodnotami v databáze, najmä pri vkladaní údajov a generovaní insertovacích skriptov pre rôzne nastavenia databázy Postgres. V prvom kroku je nutné sa naučiť, ako dáta vkladať do stĺpca typu DATE. Na to sa využíva funkcia TO_DATE(). V druhom kroku je potrebné vedieť dáta vypísať v požadovanom formáte. Na to s apoužíva funkcia TO_CHAR()
Upozornenie
Po vypracovaní úloh z cvičenia prosím o vyplnenie nasledujúceho dotazníka.