Databázové systémy
SK EN

Pokročilé dopyty

Ciele

  1. Precvičiť si písanie zložitých SELECTOV.

Úvod

Toto cvičenie je venované precvičovaniu písania dotazov. Nadviažeme na predchádzajúce cvičenie, pričom využijeme všetky doposiaľ nadobudnuté znalosti a praktickými úlohami nad databázou Movies vedomosti aplikujeme. Úlohy tohto cvičenia sú zložitejšie, avšak ich zvládnutie Vás dostatočne pripraví na zápočtovú písomku v 11. týždni.

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

Postup

Krok 1

Úloha 1.1

V ktorej krajine sa produkuje najviac filmov? Napíšte dopyt, ktorý vráti 2 stĺpce: názov krajiny a počet v nej vyprodukovaných filmov. Krajiny zotrieďte podľa počtu filmov zostupne.

Riešenie

SELECT cou_name,
       Count(*) AS pocet
FROM   c_country,
       t_produced_in
WHERE  cou_id = xprd_cou_id
GROUP  BY cou_name
ORDER  BY pocet DESC;

Úloha 1.2

Zistite, ktorý žáner nie je vôbec zastúpený vo filmoch. Realizujte dve verzie, prvú využitím NOT IN a verziu využitím NOT EXISTS. Pre overenie vedzte, že takýchto žánrov je 8.

Riešenie

SELECT gen_name 
FROM c_genre 
WHERE gen_id NOT IN(SELECT xiog_gen_id FROM t_is_of_genre tiog);

Druhá verzia:

SELECT gen_name 
FROM c_genre WHERE NOT EXISTS (SELECT xiog_gen_id 
FROM t_is_of_genre tiog WHERE gen_id = xiog_gen_id);

Úloha 1.3

Vypíšte, ktoré žánre pri filmoch prevládajú (2 stĺpce: žáner a počet). Vypíšte aj žánre, ktoré nie sú zastúpené. Výsledok zotrieďte zostupne.

Riešenie

SELECT gen_name,
       Count(*) AS pocet
FROM   c_genre,
       t_is_of_genre
WHERE  gen_id = xiog_gen_id
GROUP  BY gen_name
UNION
SELECT gen_name,
       0 AS pocet
FROM   c_genre
WHERE  gen_id NOT IN (SELECT xiog_gen_id
                      FROM   t_is_of_genre tiog)
ORDER  BY pocet DESC;

Alternatívny spôsob

SELECT gen_name,
       Count(*) AS pocet
FROM   c_genre,
       t_is_of_genre
WHERE  gen_id = xiog_gen_id
GROUP  BY gen_name
UNION
SELECT gen_name,
       0 AS pocet
FROM   c_genre
WHERE  NOT EXISTS (SELECT xiog_gen_id
                   FROM   t_is_of_genre tiog
                   WHERE  gen_id = xiog_gen_id)
ORDER  BY pocet DESC;

Úloha 1.4

Zoraďte mestá podľa kapacity kín. Výsledok nech obsahuje 2 stĺpce, prvý názov mesta a druhý súčet kapacít hál v danom meste.

Časť výsledných záznamov
Obr. 2: Časť výsledných záznamov

Riešenie

SELECT cit_name,
       Sum(hal_capacity) kapacita
FROM   c_city,
       t_cinema,
       t_hall
WHERE  cit_id = xcin_cit_id
       AND cin_id = xhal_cin_id
GROUP  BY cit_name
ORDER  BY kapacita DESC;

Úloha 1.5

Vypíšte hercov, ktorí boli obsadení vo viac ako jednom filme. Použite klauzulu HAVING. Na výpis mena využite funkciu CONCAT(). Vo výsledku záznamy zoraďte podľa počtu filmov zostupne. Pre overenie vedzte, že takých hercov je 9.

Riešenie

SELECT Concat(per_first_name, ' ', per_last_name) AS meno,
       Count(*)                                   AS pocet
FROM   t_person,
       t_person_role,
       c_role,
       t_cast
WHERE  xpro_per_id = per_id
       AND xpro_rol_id = rol_id
       AND xcas_pro_id = pro_id
       AND rol_name = 'Actor'
GROUP  BY Concat(per_first_name, ' ', per_last_name)
HAVING Count(*) > 1
ORDER  BY pocet DESC;

Úloha 1.6

Vypíšte program kín v Košiciach. Výstup nech obsahuje stĺpce ako kino, začiatok vysielania, názov filmu, trvanie filmu v minútach, pôvodný jazyk, dabingový jazyk (ak neexistuje, vypíšte "-"), názov haly a cenu lístka.

Časť výsledných záznamov
Obr. 3: Časť výsledných záznamov

Riešenie

SELECT cin_name,
       prg_starts,
       mov_name,
       Concat(mov_duration, ' min')       AS dlzka,
       xprg_lan_id,
       COALESCE(xprg_lan_id_dubbing, '-') AS dabing,
       hal_name,
       prg_price
FROM   t_program,
       t_hall,
       t_cinema,
       c_city,
       t_movie
WHERE  cit_id = xcin_cit_id
       AND cin_id = xhal_cin_id
       AND hal_id = xprg_hal_id
       AND mov_id = xprg_mov_id
       AND cit_name = 'Košice';

Úloha 1.7

Vypíšte všetkých režisérov. Na výpis mena využite funkciu CONCAT(). Režisérov zoraďte podľa mena. Pre overenie vedzte, že ich je 20.

Riešenie

SELECT Concat(per_first_name, ' ', per_last_name) AS meno
FROM   t_person,
       t_person_role,
       c_role
WHERE  xpro_per_id = per_id
       AND xpro_rol_id = rol_id
       AND rol_name = 'Director'
ORDER  BY meno;

Úloha 1.8

Vypíšte najkratšie trvajúci film aj najdlhšie trvajúci film. K týmto filmom vypíšte ich názov a priezvisko režiséra.

Správny výsledok
Obr. 4: Správny výsledok

Riešenie

SELECT mov_name,
       mov_duration,
       per_last_name
FROM   t_movie,
       t_cast,
       t_person_role,
       c_role,
       t_person
WHERE  mov_duration = (SELECT Min(mov_duration)
                       FROM   t_movie)
       AND xcas_pro_id = pro_id
       AND xcas_mov_id = mov_id
       AND xpro_rol_id = rol_id
       AND xpro_per_id = per_id
       AND rol_name = 'Director'
UNION
SELECT mov_name,
       mov_duration,
       per_last_name
FROM   t_movie,
       t_cast,
       t_person_role,
       c_role,
       t_person
WHERE  mov_duration = (SELECT Max(mov_duration)
                       FROM   t_movie)
       AND xcas_pro_id = pro_id
       AND xcas_mov_id = mov_id
       AND xpro_rol_id = rol_id
       AND xpro_per_id = per_id
       AND rol_name = 'Director';

Úloha 1.9

Vypíšte kedy a kde (názov kina) hrajú film "Forrest Gump". Vypíšte aj cenu lístka. Pre overenie, výsledok obsahuje 7 záznamov.

Riešenie

SELECT cin_name,
       prg_starts,
       mov_name,
       prg_price
FROM   t_program,
       t_hall,
       t_cinema,
       c_city,
       t_movie
WHERE  cit_id = xcin_cit_id
       AND cin_id = xhal_cin_id
       AND hal_id = xprg_hal_id
       AND mov_id = xprg_mov_id
       AND mov_name = 'Forrest Gump';

Úloha 1.10

Vypíšte filmy produkované v Českej republike spolu s ich žánrom a priezviskom režiséra. Pre overenie vedztze, že výsledok obsahuje 2 záznamy.

Riešenie

SELECT mov_name,
       per_last_name,
       gen_name
FROM   t_movie,
       t_produced_in,
       t_cast,
       t_person_role,
       c_role,
       t_person,
       t_is_of_genre tiog,
       c_genre
WHERE  xprd_mov_id = mov_id
       AND xcas_mov_id = mov_id
       AND xcas_pro_id = pro_id
       AND xpro_per_id = per_id
       AND rol_id = xpro_rol_id
       AND xiog_mov_id = mov_id
       AND xiog_gen_id = gen_id
       AND xprd_cou_id = 'CZE'
       AND rol_name = 'Director';

Úloha 1.11

Vypíšte programy kín. Pre overenie vedztze, že výsledok obsahuje 98 záznamov.

Formát výsledných záznamov
Obr. 5: Formát výsledných záznamov

Riešenie

SELECT cit_name,
       cin_name,
       prg_starts,
       mov_name,
       Concat(mov_duration, ' min') AS dlzka,
       xprg_lan_id,
       COALESCE(xprg_lan_id_dubbing, '-') as dabbing,
       hal_name,
       prg_price
FROM   t_program,
       t_hall,
       t_cinema,
       c_city,
       t_movie
WHERE  cit_id = xcin_cit_id
       AND cin_id = xhal_cin_id
       AND hal_id = xprg_hal_id
       AND mov_id = xprg_mov_id;

Úloha 1.12

Z predchádzajúceho dopytu vytvorte pohľad. View nazvyte v_program_kin.

Riešenie

CREATE OR replace VIEW v_program_kin
AS
  SELECT cit_name                           AS mesto,
         cin_name                           AS kino,
         prg_starts                         AS datum,
         mov_name                           AS film,
         Concat(mov_duration, ' min')       AS dlzka,
         xprg_lan_id                        AS jazyk,
         Coalesce(xprg_lan_id_dubbing, '-') AS dabbing,
         hal_name                           AS hala,
         prg_price                          AS cena
  FROM   t_program,
         t_hall,
         t_cinema,
         c_city,
         t_movie
  WHERE  cit_id = xcin_cit_id
         AND cin_id = xhal_cin_id
         AND hal_id = xprg_hal_id
         AND mov_id = xprg_mov_id;

Úloha 1.13

Aké filmy sa hrajú v meste Košice dňa 6.4.2021? Vypíšte kino, názov filmu a čas (napr. 20:30). Použite view v_program_kin. Pre overenie vedztze, že výsledok obsahuje 3 záznamy.

Riešenie

SELECT kino,
       film,
       To_char(datum, 'HH24:MI') AS cas
FROM   v_program_kin
WHERE  mesto = 'Košice'
       AND Date(datum) = To_date('6.4.2021', 'DD.MM.YYYY')
ORDER  BY cas;

Úloha 1.14

Vypíšte krajiny, v ktorých nebol produkovaný žiaden film. Pre overenie vedztze, že výsledok obsahuje 1 záznam.

Riešenie

SELECT cou_id,
       cou_name
FROM   c_country
WHERE  cou_id NOT IN(SELECT xprd_cou_id
                     FROM   t_produced_in);

Upozornenie

Po vypracovaní úloh z cvičenia prosím o vyplnenie nasledujúceho dotazníka.

© Copyright 2024 KPI FEI TUKE