Opakování

Řazení výsledků - ORDER BY

SELECT rok_produkce, hodnoceni_csfd, nazev_filmu FROM filmy
    ORDER BY rok_produkce ASC, hodnoceni_csfd DESC;
            

WHERE - provnávací operátory

SELECT hodnoceni_csfd, nazev_filmu FROM filmy
    WHERE reziser_prij = 'Hrebejk';
            

SELECT hodnoceni_csfd, nazev_filmu FROM filmy
    WHERE hodnoceni_csfd >= 90;
            

AND, OR, NOT

SELECT rok_produkce, hodnoceni_csfd, nazev_filmu FROM filmy
    WHERE rok_produkce = '2012' AND hodnoceni_csfd > 90;
            

SELECT naklady, reziser_prij, nazev_filmu FROM filmy
    WHERE
      (reziser_prij = 'Sverak' OR reziser_prij = 'Menzel')
      AND naklady < 1 000 000
    ORDER BY naklady;
            

IS [NOT] NULL

SELECT nazev_filmu FROM filmy
    WHERE naklady IS NULL;
            

SELECT premiera, nazev_filmu FROM filmy
    WHERE rok_produkce = '2013' AND premiera IS NOT NULL;
            

expression = NULL × expression IS NULL

BETWEEN

SELECT naklady, nazev_filmu FROM filmy
    WHERE naklady BETWEEN 1000000 AND 2000000;
            

LIKE

SELECT nazev_filmu FROM filmy
    WHERE nazev_filmu LIKE 'Tři%';
            

IN

SELECT nazev_filmu, reziser_prij
FROM filmy
    WHERE reziser_prij IN ('Sverak', 'Menzel');
            

Trocha nové teorie

M:N tabulky - kontrola cizího klíče

Kontrola cizího klíče - omezení

CREATE TABLE book_author (
    book_id INT NOT NULL,
    author_id INT NOT NULL,

    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (author_id) REFERENCES authors(id)
);
            

Kombinování dat z více tabulek

• CROSS JOIN (Kartézský součin)
• INNER JOIN
• OUTER JOIN

CROSS JOIN

Spojení dvou tabulek "každý s každým" a.k.a. Beverly Hills

Př: V tabulce studenti je 25 záznamů, v tabulce předměty je 40 záznamů.
Kolik záznamů bude ve sloučené tabulce a proč?

1000

CROSS JOIN - příklad

CROSS JOIN - příklad

SELECT * FROM studenti, barvy;
            

SELECT * FROM studenti CROSS JOIN barvy;
            

CROSS JOIN - příklad

Následně je možné z tabulky klasicky vybírat pomocí WHERE.

SELECT * FROM studenti, barvy WHERE barvy.barva ='modrá';
            

CROSS JOIN - použití

NEPOUŽÍVAT ! bez dobrých důvodů !

• Velmi náročný pro výpočet při větším množství záznamů
• Velká výstupní sada informací (tabulka)
• Pomalý
• Omezit podmínkou!

[INNER] JOIN

Nejpoužívanější

• INNER JOIN ON (TRUE) je shodný s CROSS JOIN
• INNER JOIN navíc určuje podmínku výběru (které řádky zůstanou)
• Vybere a spojí pouze ty řádky, které je schopen spárovat (viz příklad dále).

[INNER] JOIN - příklad

[INNER] JOIN - příklad

SELECT * FROM autor
INNER JOIN kniha ON (autor.id = kniha.id_autora);
            

SELECT * FROM autor
CROSS JOIN kniha WHERE autor.id = kniha.id_autora;
            

[OUTER] JOIN

• LEFT [OUTER] JOIN
• RIGHT [OUTER] JOIN
• FULL [OUTER] JOIN

LEFT [OUTER] JOIN

Vybere všechny záznamy z "LEVÉ" tabulky a přiřadí záznamy, které může. Ke zbylým záznamům přiřadí NULL.

SELECT * FROM autor
LEFT OUTER JOIN kniha ON (autor.id = kniha.id_autora);
            

RIGHT [OUTER] JOIN

Vybere všechny záznamy z "PRAVÉ" tabulky a přiřadí záznamy, které může. Ke zbylým záznamům přiřadí NULL.

SELECT * FROM autor
RIGHT OUTER JOIN kniha ON (autor.id = kniha.id_autora);
            

FULL [OUTER] JOIN

Vybere všechny záznamy z "PRAVÉ" i "LEVÉ" tabulky a přiřadí záznamy, které může. Ke zbylým záznamům přiřadí NULL.

SELECT * FROM autor
FULL OUTER JOIN kniha ON (autor.id = kniha.id_autora);
            

Vkládání cizích klíčů

Vložit cizí klíč (neboli referenci na jinou tabulku) je možné dvěma způsoby

Vkládání cizích klíčů - postupně

SELECT id FROM autor WHERE prijmeni = 'Pratchett';

INSERT INTO kniha (id, nazev, id_autora) VALUES
  (7, 'Čaroprávnost', ###),
  (8, 'Soudné sestry', ###);
  -- místo ### je nalezené ID z předchozího dotazu
            

Tímto způsobem je možné zkontrolovat, že nám záznam skutečně vrací ID (resp. záznam), který opravdu chceme.

Vkládání cizích klíčů - najednou

INSERT INTO kniha (id, nazev, id_autora) VALUES
  (7, 'Čaroprávnost',  (SELECT id FROM autor WHERE prijmeni = 'Pratchett') ),
  (8, 'Soudné sestry',  (SELECT id FROM autor WHERE prijmeni = 'Pratchett') );
            

Pokud jsme si jisti, že nám záznam vybere správného autora, můžeme použít vnořený SELECT.

DIY - Do It Yourself

Konečně dodržujeme 3. NF protože umíme JOIN ☺ jupí!

ÚKOL 1

Navrhněte databázi oblíbených filmů obsahující popisné informace o daných filmech (český název, anglický název, rok vydání, žánr, hodnocení CSFD a další) + jejich herecké obsazení (jméno, příjmení, datum narození a další).

Kdo se nerad dívá na filmy, nebo má raději jiné téma (např. hry či knihy) můžete navrhnout databázi o těchto tématech.
Podmínkou je, že databáze obsahuje alespoň 4 rozdílné tabulky, jednu relaci 1:M a jednu relaci M:N

Dbejte na správnou volbu datových typů, omezení sloupců a definování odpovídající referenční integrity!

ÚKOL 1 - pokračování

Vypište z tabulek všechny komedie i s hereckým obsazením a proveďte jejich seřazení sestupně podle hodnocení ČSFD?

V jakých filmech (dle žánru) hraje Váš oblíbený herec (herečka)? Uveďte český i anglický název filmu a proveďte jejich seřazení vzestupně podle roku produkce.

Ti kteří si zvolili jiné téma, provedou výběry analogicky dle tématu.