Databázové systémy
SK EN

Komplexné dopyty

Ciele

  1. Agregačné funkcie pre agregovanie kolekcie hodnôt do jednej hodnoty.
  2. Použitie jednoduchých a zložených WHERE podmienok v kombinácii so vstavanými funkciami.
  3. Zoskupovanie záznamov do skupín podľa rovnakej hodnoty v zadanom stĺpci.
  4. Množinové operácie.

Ú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.

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

Postup

Krok 1

Úloha 1.1

Agregačné funkcie umožňujú agregovať viaceré hodnoty do jednej - napr. count zráta počet záznamov:

SELECT count(*) "pocet filmov" FROM t_movie;

Vďaka agregačným funkciám je možné spočítať počet špecifických záznamov, vypočítať priemernú hodnotu, maximálnu hodnotu, atď.

Úloha 1.2

Koľko krajín sa nachádza v tabuľke c_country? Pre overenie vedzte, že ich je 8.

Riešenie

SELECT Count(*) "Pocet krajin"
FROM   c_country;

Úloha 1.3

Zistite počet kín, ktoré sa nachádzajú v Košiciach. Pre overenie vedzte, že je sú 3.

Riešenie

SELECT Count(*) "Pocet kin v KE"
FROM   t_cinema,
       c_city
WHERE  xcin_cit_id = cit_id
       AND cit_name = 'Košice';

Úloha 1.4

Koľko hodín trvajú všetky filmy v databáze Movies? Pre overenie vedzte, že trvajú 53 hodín.

Riešenie

SELECT (sum(mov_duration)/60) "Sucet v hodinach" 
FROM t_movie tm;

Poznámka

Ak chcete mať vo výsledku aj desatinné čísla pri delení dvoch operandov typu int, musíte aspoň jeden z nich skonvertovať na dátový typ decimal.

SELECT ROUND(SUM(mov_duration)/CAST(60 as decimal),2) " Sucet v hodinach" 
from t_movie tm ;

Úloha 1.5

Aká je minimálna, maximálna a priemerná dĺžka filmov v tabuľke t_movie? Pre overenie vedzte, že najkratší film = 96, najldhší = 210 a priemer je 152 minút.

Riešenie

SELECT Min(mov_duration)           AS najkratsi,
       Max(mov_duration)           AS najdlhsi,
       Round(Avg(mov_duration), 1) AS priemer
FROM   t_movie tm;

Krok 2

Úloha 2.1

Vypíšte všetky osoby mužského pohlavia. Pre overenie vedzte, že ich je 115.

Riešenie

SELECT * FROM t_person WHERE xper_sex_id = 'M';

Úloha 2.2

Vypíšte všetky ženy, ktorých krstné meno začína písmenom "L". Pre overenie vedzte, že ich je 7.

Riešenie

SELECT * FROM t_person WHERE xper_sex_id = 'F' AND per_first_name  LIKE 'L%';

Úloha 2.3

Vypíšte všetky filmy, ktorých trvanie je od 100 do 150 minút. Pre overenie vedzte, že ich je 10.

Riešenie

SELECT * FROM t_movie WHERE mov_duration BETWEEN 100 AND 150;

Úloha 2.4

Vypíšte všetky filmy, ktorých trvanie je menšie alebo rovné ako 100 alebo väčšie alebo rovné ako 150, a zotrieďte ich podľa dĺžky názvu filmu od najdlhšieho. Pre overenie vedzte, že ich je 11.

Riešenie

Výsledné záznamy
Obr. 2: Výsledné záznamy

SELECT *
FROM   t_movie
WHERE  mov_duration <= 100
        OR mov_duration >= 150
ORDER  BY Length(mov_name) DESC;

Krok 3

Použitie agregačných funkcií je zvlášť zaujímavé v kombinácii so zoskupením výsledkov. Zoskupovanie s GROUP BY klauzulou umožňuje získať sumárne hodnoty pre skupiny záznamov podľa hodnoty vybraného stĺpca. Napr. je možné získať počet osôb podľa pohlavia:

SELECT xper_sex_id AS pohlavie,
       Count(*)    AS pocet
FROM   t_person
GROUP  BY xper_sex_id;

Úloha 3.1

Vypíšte počet vydaných filmov k danému roku. Záznamy zoraďte zostupne podľa počtu filmov.

Výsledné záznamy
Obr. 3: Výsledné záznamy

Riešenie

SELECT Extract(year FROM mov_release_date) AS rok,
       Count(*)                            AS pocet
FROM   t_movie tm
GROUP  BY Extract(year FROM mov_release_date)
ORDER  BY 2 DESC;

Úloha 3.2

Vypíšte len tie roky, v ktorých bolo vydaných viac filmov ako 1.

Riešenie

SELECT Extract(year FROM mov_release_date) AS rok,
       Count(*)                            AS pocet
FROM   t_movie tm
GROUP  BY Extract(year FROM mov_release_date)
HAVING Count(*) > 1
ORDER  BY 2 DESC;

Úloha 3.3

Vypočítajte priemerný vek mužov aj žien.

Riešenie

SELECT xper_sex_id AS pohlavie,
       Round(Avg(Extract(year FROM Now()) - Extract(year FROM per_birthdate)))
                   "priemerny vek"
FROM   t_person
GROUP  BY xper_sex_id;

Upozornenie

Na výpočet veku nepoužívajte funkciu AGE(). Tú sme si už predstavili v minulých úlohách. Porozmýšľajte a výpočet priemerného veku vyriešte bez použitia tejto funkcie.

Úloha 3.4

Z tabuľky t_person vypíšte vek najmladšieho a vek najstrašieho muža. To isté spravte so ženami. Výsledok nech obsahuje 3 stĺpce: pohlavie, najmladsi, najstarsi.

Výsledné záznamy
Obr. 4: Výsledné záznamy

Riešenie

SELECT xper_sex_id
       AS
       pohlavie,
       Round(Min(Extract(year FROM Now()) - Extract(year FROM per_birthdate)))
       AS
       najmladsi,
       Round(Max(Extract(year FROM Now()) - Extract(year FROM per_birthdate)))
       AS
       najstarsi
FROM   t_person
GROUP  BY xper_sex_id;

Úloha 3.5

Koľko mužov v tabuľke t_person má menej ako 60 rokov? Koľko žien v tabuľke t_person má menej ako 60 rokov? Výsledok nech obsahuje 2 stĺpce: pohlavie a počet osôb. Pre overenie vedzte, že aktuálne je takýchto žien 7 a mužov 22.

Riešenie

SELECT xper_sex_id AS pohlavie,
       Count(*)    "mladsi ako 60"
FROM   t_person
WHERE  Extract(year FROM Now()) - Extract(year FROM per_birthdate) < 60
GROUP  BY xper_sex_id;

Krok 4

Množinové operácie pracujú s tabuľkami a výsledkami SELECT-ov ako s množinami a umožňujú nám z nich vytvárať zjednotenie, prienik a množinový rozdiel.

Úloha 4.1

Pomocou UNION vypíšte všetky záznamy z t_program, ktoré su originálne v češtine alebo dabované po česky. Pre overenie vedzte, že ich je 25.

Riešenie

select * from t_program where xprg_lan_id = 'CS'
union 
select * from t_program where xprg_lan_id_dubbing = 'CS';

Poznámka

Porozmýšľajte, či sa takýto select dá napísať aj bez použitia UNION.

Úloha 4.2

Predstavte si, že potrebujete od používateľa, aby zadal miesto, kde sa nachádza. Miesto môže byť buď štát, alebo už konkrétne mesto. Vypíšte preňho takýto zoznam, z ktorého si môže vybrať. Použite na to tabuľky c_country a c_city. Výsledok nech obsahuje 2 stĺpce: typ (či ide o mesto alebo štát) a nazov. Výsledok zoraďte podľa typu a mesta.

Výsledné záznamy
Obr. 5: Výsledné záznamy

Riešenie

select 'stat' as typ, cou_name as nazov from c_country
union 
select 'mesto' as typ, cit_name as nazov from c_city
order by typ, nazov;

Úloha 4.3

Zistite, či sa v tabuľke c_city nenachádza mesto, s rovnakým názvom, ako je názov niektorej z krajín (tabuľka c_country).

Keďže sa v našej databáze taká kombinácia nenachádza, tak si kvôli demonštrácii operácií INTERSECT A EXCEPT vložíme do tabuľky c_city mesto s názvom "USA". Znova zhodu skontrolujte.

Riešenie

INSERT INTO c_city
(cit_id , cit_name, cit_inserted, cit_inserted_by,  xcit_cou_id)
VALUES(13, 'USA', now(), SESSION_USER, 'USA');
    
select cou_name as nazov from c_country
intersect
select cit_name as nazov from c_city;

Úloha 4.4

Vyberte všetky mestá, okrem tých, ktoré majú zhodný názov s názvom štátu. Čiže vypísať by sa mali všetky mestá, až na USA, ktoré sme v predošlej úlohe pridali.

Riešenie

select cit_name as nazov from c_city
except
select cou_name as nazov from c_country;

Poznámka

Nezabudnite USA z tabuľky c_city vymazať.

Upozornenie

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

© Copyright 2024 KPI FEI TUKE