Geodatabáze – cvičení 5/6
(skupina 1)
Mgr.  Josef Chrást
(269703@mail.muni.cz)
znak_PrF_cerny_RGB.emf

Obsah cvičení
—Kombinování dat z více tabulek
◦Spojení
–WHERE
–JOIN
—Agregační funkce (COUNT, AVG, MAX, MIN, SUM)
—Seskupení řádků
◦GROUP BY
—Praktické cvičení
◦
◦
2
4.11.2013

Spojení
—Kombinace sloupců z více tabulek do jediného výsledku dotazu
—Spojení
◦Křížové
–kartézský součin vstupních množin
◦Vnitřní
– spojení na základě vazby primárního a cizího klíče
◦Vnější
–zahrnutí nespárovaných řádků alespoň jedné z tabulek
3
4.11.2013

Vnitřní spojení
—WHERE
—
—
—JOIN
◦
—
Př. : 1) SELECT nazev_filmu, zanr
            FROM filmy JOIN zanr_filmu ON filmy.id_film = zanr_filmu.id_film

        2) SELECT nazev_filmu, zanr
            FROM filmy JOIN zanr_filmu USING (id_film)
4
4.11.2013
Př. : 1) SELECT nazev_filmu, zanr
            FROM filmy, zanr_filmu
            WHERE filmy.id_film = zanr_filmu.id_film AND reziser_prij = ‘Frič’

Agregační funkce
—Získávání souhrnných údajů z celé tabulky či pouze z její části
—Funkce
◦COUNT – počet hodnot ve sloupci (počet řádků)
–Klíčové slovo DISTINCT – počet jedinečných hodnot ve sloupci
◦AVG – průměrná hodnota sloupce nebo výrazu
◦MAX – maximální hodnota ve sloupci
◦MIN – minimální hodnota ve sloupci
◦SUM – součet hodnot ve sloupci
—
4.11.2013
5

Agregační funkce
4.11.2013
6
Př. : 1) SELECT COUNT (*) AS pocet_filmu, AVG (stopaz) AS prum_delka,
            MIN (rok_produkce) AS nejstr_film, MAX (rok_produkce) AS nejnov_film
            FROM filmy


        2) SELECT COUNT(DISTINCT(zanr_filmu)) AS pocet_zanru
            FROM filmy
        3) SELECT ROUND(AVG(hodnoceni),2) AS prum_hodnoceni
            FROM filmy
        4) a) SELECT AVG(hodnoceni_csfd) AS prum_hodnoceni
                 FROM filmy
                SELECT nazev_filmu, hodnoceni_csfd
                 FROM filmy
                 WHERE hodnoceni csfd > „prum_hodnota“
            b) SELECT nazev_filmu, hodnoceni_csfd
                 FROM filmy
                 WHERE hodnoceni_csfd > (SELECT AVG(hodnoceni_csfd) FROM filmy)
pocet_filmu
prum_delka
nejstr_film
nejnov_film
10
124
1912
2012

Seskupení řádků
—GROUP BY
◦Sestavení řádků do skupin podle hodnot v jenom či více sloupcích
◦Aplikování agregační funkce na každou skupinu
◦Výsledek – jeden řádek pro každou skupinu
4.11.2013
7
Př. : 1) SELECT zanr_filmu AS zanr, COUNT (*) AS pocet_filmu
            FROM filmy
            GROUP BY zanr_filmu

zanr
pocet_filmu
Komedie
5
Drama
2
Sci-fi
3

Filmová databáze
—Nedostatky z minule
◦Jedna tabulka
◦Omezené množství uložených informací
◦Porušení podmínek základních normálních forem
–Atomičnost uložených informací
–Závislost na celém primárním klíči
–Bez závislostí mezi neklíčovými atributy
◦
–
8
4.11.2013
nazev
reziser_prij
rok_produkce
hodnoceni_csfd
delka
zeme_puvodu
zanr_filmu
Forrest Gump
Zemeckis
1994
95
142
USA
Drama/Komedie/Romantický
Tenkrát na Západě
Leone
1968
91
166
Itálie/USA
Western/Dobrodružný
…






id_film
nazev
kod_zanru
popis_zanru
1
Dědictví aneb Kurvahošigutntág
Ko
Komedie
2
Sedm statečných
W
Western
3
U pokladny stál
Ko
Komedie
…

Filmová databáze
ERD_cviceni5.jpg
4.11.2013
9

Cvičení
—Pracujte s tabulkami „filmy“, „herci“, „obsazeni_filmu“ a „zanr_filmu“. Provádějte pouze
dotazovací příkazy!
—Úkoly
—I. Spojení
—a) Vypište z databáze všechny komedie i s hereckým obsazením a seřaďte je sestupně podle hodnocení
ČSFD?
—b) V jakých filmech (dle žánru) hraje Bolek Polívka? Výsledné řádky seřaďte vzestupně podle roku
produkce.
—II. Agregační funkce
—a) Kolik herců obsahuje tabulka „herci“?
—b) Kolik různých filmových žánrů je zastoupeno v databázi?
—c) Jaká je průměrná délka (stopáž) filmů klasifikovaných jako trillery?
—d) Vypište název a rok produkce nejstaršího a nejnovějšího filmu v databázi.
—e) Zjistěte, kolik herců (z databáze) hrálo ve filmu Pelíšky a Návrat do budoucnosti. Výsledky
seřaďte sestupně podle počtu herců.
—f) Vypište jména všech herců, kteří hráli v nejdelším filmu, a seřaďte je od nejstaršího po
nejmladšího herce.
—
—
—
—
—
—
—
—
—
—
4.11.2013
10