Ciele
- Vnútorné spájanie (INNER JOIN).
- Vonkajšie spájanie (OUTER JOIN).
- Naučiť sa písať zložité dotazy.
Ú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 Movies môžeme vidieť názvy miest, v ktorých sa nachádzajú kiná spojením tabuliek t_cinema a c_city cez cudzí kľúč xcin_cit_id previazaný medzi stĺpcami xcin_cit_id a cit_id (stĺpec xcin_cit_id je cudzím kľúčom v tabuľke t_cinema do tabuľky c_city, cit_id je primárny kľúčom tabuľky c_city).
SELECT
c_city.cit_name,
t_cinema.cin_name
FROM
c_city
INNER JOIN t_cinema
ON c_city.cit_id = t_cinema.xcin_cit_id;
Poznámka
Nasledujúci obrázok môže byť užitočnou pomôckou pri osvojení si spájania tabuliek.
Poznámka
NATURAL JOIN na cvičení precvičovať nebudeme, pretože dátový model je navrhnutý v súlade s doporučenou konvenciou z praxe. Tým pádom sa vylučuje použitie natural joinu, keďže každý stĺpec v databáze má jednoznačný názov. Natural join sa tiež v praxi veľmi nepoužíva, pretože cieľ dotazu nie je očividný, použité stĺpce nie sú jasne pomenované a môžu sa "nečakane" zmeniť. Preto ho treba skôr brať ako teoretickú možnosť.
Postup
Krok 1
Úloha 1.1
Vypíšte filmy spolu so žánrami, do ktorých sú zaradené. Pre kontrolu vedzte, že vo výsledku je 49 záznamov.
Riešenie
SELECT
t_movie.mov_name,
c_genre.gen_name
FROM
t_movie
INNER JOIN t_is_of_genre
ON t_movie.mov_id = t_is_of_genre.xiog_mov_id
INNER JOIN c_genre
ON t_is_of_genre.xiog_gen_id = c_genre.gen_id;
Poznámka
Pre zistenie žánru filmu je potrebné spojiť tabuľky t_movie a c_genre.
Úloha 1.2
Vypíšte mestá spolu s názvom kín, ktoré sa v nich nachádzajú. Kinám vypíšte aj haly s ich kapacitou. Pre kontrolu vedzte, že vo výsledku je 57 záznamov.
Riešenie
SELECT
c_city.cit_name,
t_cinema.cin_name,
t_hall.hal_name,
t_hall.hal_capacity
FROM
t_cinema
INNER JOIN t_hall
ON t_cinema.cin_id = t_hall.xhal_cin_id
INNER JOIN c_city
ON t_cinema.xcin_cit_id = c_city.cit_id;
Úloha 1.3
Vypíšte filmy spolu s krajinou, v ktorej boli vyprodukované. Pre overenie vedzte, že záznamov vo výsledku je 26.
Riešenie
SELECT
t_movie.mov_name,
c_country.cou_name
FROM
c_country
INNER JOIN t_produced_in
ON c_country.cou_id = t_produced_in.xprd_cou_id
INNER JOIN t_movie
ON t_produced_in.xprd_mov_id = t_movie.mov_id;
Úloha 1.4
Vypíšte filmy spolu s osobami a ich rolou, akou sa na filme podieľali. Pre overenie vedzte, že záznamov vo výsledku je 184.
Riešenie
SELECT
t_movie.mov_name,
c_role.rol_name,
t_person.per_last_name,
t_person.per_first_name
FROM
t_movie
INNER JOIN t_cast
ON t_movie.mov_id = t_cast.xcas_mov_id
INNER JOIN t_person_role
ON t_cast.xcas_pro_id = t_person_role.pro_id
INNER JOIN t_person
ON t_person_role.xpro_per_id = t_person.per_id
INNER JOIN c_role
ON t_person_role.xpro_rol_id = c_role.rol_id;
Krok 2
Poznámka
Pri left a right outer joinoch dávajte pozor na poradie tabuliek.
Úloha 2.1
Vypíšte krajiny, v ktorých bol vyprodukovaný nejaký film. Vypíšte aj tie krajiny, ktoré neboli zahrnuté pri produkcii žiadneho filmu. Pre overenie vedzte, že sa Vám má vypísať 27 záznamov.
Riešenie
SELECT
c_country.cou_name,
t_movie.mov_name
FROM
c_country
LEFT OUTER JOIN t_produced_in
ON c_country.cou_id = t_produced_in.xprd_cou_id
LEFT OUTER JOIN t_movie
ON t_produced_in.xprd_mov_id = t_movie.mov_id;
Úloha 2.2
Vypíšte mestá, spolu s názvom kín, ktoré sa v nich nachádzajú. Kinám vypíšte aj haly s ich kapacitou. Vypíšte aj tie mestá, v ktorých nie sú žiadne kiná. Pre overenie vedzte, že sa Vám má vypísať 61 záznamov.
Riešenie
SELECT
c_city.cit_name,
t_cinema.cin_name,
t_hall.hal_name,
t_hall.hal_capacity
FROM
t_cinema
INNER JOIN t_hall
ON t_cinema.cin_id = t_hall.xhal_cin_id
RIGHT OUTER JOIN c_city
ON t_cinema.xcin_cit_id = c_city.cit_id;
Upozornenie
Ak ste si zabudli vymazať záznam "USA" z minulého cvičenia, vypíše sa Vám 62 záznamov.
Úloha 2.3
Vypíšte žánre, spolu s filmami tohto žánru. Vypíšte aj tie žánre, ktoré žiaden film nespĺňa. Pre overenie vedzte, že ich je 57.
Riešenie
SELECT
c_genre.gen_name,
t_movie.mov_name
FROM
c_genre
LEFT OUTER JOIN t_is_of_genre
ON c_genre.gen_id = t_is_of_genre.xiog_gen_id
LEFT OUTER JOIN t_movie
ON t_is_of_genre.xiog_mov_id = t_movie.mov_id;
Úloha 2.4
Ktoré filmy vysielajú v akých jazykoch? Bez ohľadu na to, či je to jazyk pôvodný, alebo dabing. Vypíšte názov jazyka, nie skratku. Stĺpce výsledku nech sú: film, originálny jazyk, dabing. Každú kombináciu vypíšte najviac raz (SELECT DISTINCT). Vypíšte aj tie filmy, ktoré nemajú dabing. Zázamy vo výsledku zoraďte podľa názvu filmu. Pre overenie vedzte, že ich je 50.
Riešenie
SELECT DISTINCT t_movie.mov_name,
c1.lan_name AS original,
c2.lan_name AS dabing
FROM c_language c1
INNER JOIN t_program
ON c1.lan_id = t_program.xprg_lan_id
INNER JOIN t_movie
ON t_program.xprg_mov_id = t_movie.mov_id
LEFT OUTER JOIN c_language c2
ON t_program.xprg_lan_id_dubbing = c2.lan_id
ORDER BY mov_name;
Upozornenie
Po vypracovaní úloh z cvičenia prosím o vyplnenie nasledujúceho dotazníka.