Databázové systémy
SK EN

Spájanie tabuliek

Ciele

  1. Príkaz SELECT.
  2. Vnútorné spájanie (INNER JOIN).
  3. Prirodzené spájanie (NATURAL JOIN).
  4. Vonkajšie spájanie (OUTER JOIN).
  5. Rekurzívne spájanie.

Úvod

Spájanie tabuliek nám umožňuje využívať cudzie a primárne kľúče na získanie informácií o vzťahoch medzi entitami uloženými v databáze. Napr. v databáze Chinook môžeme vidieť názvy album, do ktorých patria jednotlivé skladby spojením tabuľky Track s tabuľkou Album použitím cudzieho kľúča Track.albumId, ktorým sa každá skladba odkazuje na album, do ktorého patrí:

SELECT track.name, album.title
FROM album
         JOIN track ON album.albumId = track.albumId;

Pracujeme na databáze Chinook s nasledovnou schémou.

Relačný model databázy Chinook
Obr. 1: Relačný model databázy Chinook

Postup

Krok 1

Úloha 1.1

Vypíšte zoznam všetkých videosúborov (v názve typu média je 'video'). Pre overenie vedzte, že je ich 214.

Poznámka

Pre zistenie typu média skladby je potrebne spojiť tabuľky Track a MediaType.

Úloha 1.2

Vypíšte zoznam všetkých zákazníkov, o ktorých sa stará Margaret Park-ová. Vo výpise zobrazte zákazníkovo priezvisko, meno, a jeho telefónny kontakt, pričom zákazníkov zoraďte najprv podľa priezviska a potom podľa mena. Pre overenie vedzte, že je ich 20 a jeden z nich je Bjrn Hansen.

Poznámka

Pri zložitejších úlohách je potrebné pracovať iteratívne. Najprv sa pokúste vypísať iba kombinácie zamestnanec - jeho zákazník. Potom pridajte klauzulu na vyfiltrovanie tých záznamov, v ktorých vystupuje Margaret Park. Následne zobrazte iba meno, priezvisko a telefón zákazníka. Nakoniec môžete výsledok usporiadať.

Krok 2

V rámci cvičenia precvičíme aj prirodzené spájanie, aj keď sa v praxi veľmi nevyužíva. Vďaka tomuto precvičeniu je jednoduché uvedomiť si, prečo sa nezvykne používať.

Na minulom cvičení bola už jedna úloha aj na spájanie tabuliek. Cieľom bolo vypísať názvy všetkých albumov skupiny U2. Na to bolo potrebné spojiť tabuľky Album a Artist na podmienke 'Album.artistid=Artist.artistid'. Keďže vidíme, že stĺpce pre cudzí a primárny kľúč vyjadrujúci vzťah medzi albumom a umelcom sa volajú rovnako, vieme, že na ich spojenie môžeme použiť prirodzené spájanie (NATURAL JOIN).

Úloha 2.1

Opäť vypíšte zoznam všetkých albumov skupiny U2, ale tentokrát pri tom využitím NATURAL JOIN.

Úloha 2.2

Skúste prepísať aj riešenie dnešnej prvej úlohy použitím NATURAL JOIN-u. Je to vôbec možné? V prípade, že sa Vám nedarí vyriešiť úlohu, môžete si zobraziť riešenie:

Riešenie

Túto úlohu prirodzeným spájaním nevyriešime. V tomto prípade totiž rovnaké mená stĺpcov nie sú iba v prípade páru primárny a cudzí kľúč (genreId), ale aj v prípade názvu skladby (Track.name) a názvu typu média (MediaType.name).

V tomto prípade sa teda NATURAL JOIN pokúša spojiť iba tie páry skladieb a typov médií, ktoré majú nielen rovnaký genreId (v prípade skladby je to cudzí kľúč, v prípade typu média je to primárny kľúč), ale zároveň musia mať rovnaký aj názov - stĺpec name je v oboch tabuľkách. V databáze nemáme žiadnu skladbu, ktorá by sa volala rovnako ako jej typ média.

Úloha 2.3

Prepíšte select na získanie zoznamu všetkých video súborov použitím kombinácie JOIN USING. Táto kombinácia funguje tak ako prirodzené spájanie, avšak umožňuje explicitne uviesť názvy stĺpcov, na základe ktorých sa majú tabuľky spájať. Týmto sa vyrieši problém zhody názvov stĺpcov, ktoré nevyjadrujú vzťah medzi tabuľkami (v tomto prípade stĺpec name).

Poznámka

Práve to, že NATURAL JOIN spája tabuľky podľa všetkých stĺpcov s rovnakým menom, je dôvod, prečo sa nezvykne používať. Ak človek zmení jednu tabuľku pridaním stĺpca, ktorý bude mať náhodou rovnaký názov, ako existujúci stĺpec v druhej tabuľke, SELECT s NATURAL JOIN-om sa zrazu začne správať inak ako doposiaľ.

Krok 3

Úloha 3.1

Vypíšte zoznam všetkých umelcov, ktorí nemajú žiaden album.

Poznámka

Použite LEFT OUTER JOIN na to, aby sa pri spájaní do výsledku dostali aj tí umelci, ktorí žiaden album nespravili. Následne je už len potrebné odfiltrovať z výsledku všetkých ostatných. Pre overenie vedzte, že ich je 71.

Úloha 3.2

Vypíšte zoznam zamestnancov, spolu s menami ich priamych nadradených (meno zreťazené s priezviskom). Nezabudnite vypísať aj šéfa, aj keď priameho nadriadeného nemá.

Úloha 3.3

Zistite meno, priezvisko a dátum narodenia najstaršieho zamestnanca.

Poznámka

Pre vyriešenie použite rovnaký princíp, aký sme použili pre riešenie problému umelcov bez albumu - kombináciu LEFT JOIN-u s testom na null hodnotu.

Poznámka

V podmienke spájania nemusí byť len test na rovnosť cudzieho a primárneho kľúča, ale napr. môžete chcieť, aby dátum narodenia prvého človeka v dvojici bol starší ako dátum druhého človeka.

Zdroje

  1. Prednáška 10: Vnorené dopyty

Doplňujúce úlohy

Úloha A.1

Chinook

Vypíšte zoznam všetkých zákazníkov, ktorý v roku 2012 nič neobjednali. Výsledný zoznam usporiadajte podľa priezviska a mena vzostupne. Pre overenie vedzte, že ich je 12.

Úloha A.2

Podnik

Vypíšte zoznam pracovníkov, nimi čerpaný počet dní dovolenky v roku 2016 a text, či čerpali resp. nečerpali dovolenku. Napr.:

priezvisko meno dov_cerpane Cerpal?
Bajny Ivan 23 cerpal
Maly Jan 10 cerpal
Vodny Marian 0 necerpal

Poznámka

Pre vypísanie hlášky, či zamestnanec dovolenku čerpal alebo nečerpal, na riešenie úlohy môžete použiť konštrukciu CASE.

CASE expression
  WHEN value THEN result
  [WHEN ...]
  [ELSE result]
END [AS collum_name]

Úloha A.3

Podnik

Vypíšte meno, priezvisko a nečerpanú dovolenku podnikových zamestnancov, ak predpokladáme, že aktuálna nečerpaná dovolenka je ostatok z roku 2015 + ostatok z roku 2016. Pre overenie vedzte, že napr. Ivan Bajny má ostávajúcu dovolenku 22 dní.

Úloha A.4

Chinook

Ktoré playlisty nemajú žiadnu skladbu zo žánru Latin? Pre overenie vedzte, že je ich 14.

© Copyright 2024 KPI FEI TUKE