Public Úvod do jazyka SQL druhý blok Public Osnova • NULL • DISTINCT • JOIN Public Cvičení Vypište průměrnou délku filmu v jednotlivých letech. Výsledek seřaďte sestupně dle roku a u každého roku zároveň vypište celkový počet filmů. Do výsledku nezahrnujte filmy, u kterých není zadaný rok a filmy, u kterých je rok premiéry v budoucnosti. Public NULL Neexistující/nedefinovaná hodnota Nefungují operátory =, <>, ale pouze: IS NULL případně IS NOT NULL Public https://posp.cz/w/muni Public Počet lidí, které mají jednotlivé fakulty v jednotlivých budovách SELECT fakulta, budova, COUNT(*) FROM fakulty GROUP BY fakulta, budova Public COUNT(*) vs COUNT(sloupec) COUNT(sloupec) “započítává” pouze ty řádky, kde je hodnota NOT NULL Public Public Databáze “minidb” Vypište počet výtisků jednotlivých knih spolu s počtem těch, které jsou zrovna vypůjčené SELECT kniha, count(*) as vse, COUNT(pujceno) as vypujceno FROM minidb GROUP BY kniha Public DISTINCT Public Databáze “fakulty” Vypište jednotlivé fakulty. SELECT fakulta FROM fakulty GROUP BY fakulta ale i SELECT DISTINCT fakulta FROM fakulty DISTINCT zaručuje unikátnost zobrazené kombinace vypsaných sloupců, proto SELECT DISTINCT fakulta, budova FROM fakulty a SELECT fakulta, budova FROM fakulty GROUP BY fakulta už stejné výsledky nevrací Public Databáze “fakulty” Vypište počet budov jednotlivých fakult SELECT fakulta, COUNT(DISTINCT budova) FROM fakulty GROUP BY fakulta …bez DISTINCT by byla každá budova započítána tolikrát, kolik je v ní kateder Public COUNT(*) vs COUNT(sloupec) vs COUNT(DISTINCT sloupec) Public JOIN Public Příspěvky Public Jméno E-mail Příspěvek Kampaň Honza jsem@posp.cz 300 hithit Honza jsem@posp.cz 1000 kontaktní Tomáš fil.hrabal@gmail.c om 500 hithit Eliška eliska@eps.cz 300 kontaktní Public Problémy? - Pokud jeden dárce přispívá vícekrát, jeho Jméno a E-mail se budou v tabulce opakovat u každého příspěvku - Redundance zvyšuje riziko nekonzistence – například pokud dárce změní e-mail, budete muset aktualizovat každou odpovídající řádku. - Pokud byste chtěli aktualizovat informace o dárci (např. jeho jméno nebo e-mail), musíte tyto změny provést na více řádcích. To může být náročné na údržbu a vést k chybám. - Nemáte přehled o unikátních donorech bez provedení deduplikace (například pomocí GROUP BY nebo deduplikace podle e-mailu). - Chcete-li přidat další informace o donorech (např. telefon, adresa), budete muset upravit strukturu tabulky, což není optimální. - Nemůžete evidovat potenciální donory, kteří ještě nic nedarovali - … Koho by téma zajímalo víc, tak viz https://cs.wikipedia.org/wiki/Normalizace_datab%C3%A1ze Public PříspěvkyDonoři Řešení? Dvě separátní tabulky navzájem propojitelné hodnotou v nějakém sloupci/sloupcích Public ID Jméno Kontakt Telefon Poznámka 1 Honza jsem@posp.cz 111 Nevolat před 10 dopo 2 Tomáš fil.hrabal@gmail.com 222 3 Eliška eliska@frankbold.org 333 Pracuje ve FrankBold Public Donor Příspěvek Kdy Kampaň 1 300 30. 10. 2014 hithit 1 1000 1. 11. 2014 konktatní 2 500 1. 11. 2014 hithit 3 300 30. 10. 2014 kontaktní Sloupec Donor obsahuje hodnoty ze sloupce ID tabulky donorů Public posp.cz/w/fund2 Public PříspěvkyDonoři 1:M PříspěvkyDonoři Jeden donor může dát více příspěvků, každý příspěvek má jednoho donora Vazba 1:M (někdy psáno ”1:N”, nebo 1:Many) Public PříspěvkyDonoři 1:M KatedraFakulta Jeden fakulta může mít více kateder, každá katedra spadá pod jednu fakultu Public PříspěvkyDonoři 1:M KnihyNakladatel Jeden nakladatel může vydat více knih, každá kniha má jednoho nakladatele Public PříspěvkyDonoři 1:M PSČObec V rámci obce může být více PSČ, každé PSČ patří do jedné obce Public 1:M Příspě vky Donoři PSČObec V každém okrese může být více obcí, každá obec je v jednom okrese DonořiOkres Public 1:M Příspě vky Donoři PSČObec V každém kraji může být více okresů, každý okres patří do jednoho kraje DonořiOkres DonořiKraj Public ID Jméno Kontakt Poznámka 1 Honza jsem@posp.cz Nevolat před 10 dopo 2 Tomáš fil.hrabal@gmail .com 3 Eliška eliska@eps.cz Pracuje v EPS Donor Příspěvek Kdy Kampaň 1 300 30. 10. 2014 hithit 1 1000 1. 11. 2014 konktatní 2 500 1. 11. 2014 hithit 3 300 30. 10. 2014 kontaktní Public JOIN ID Jméno Kontakt Poznámka 1 Honza jsem@posp .cz Nevolat před 10 dopo 2 Tomáš fil.hrabal@g mail.com 3 Eliška eliska@eps. cz Pracuje v EPS Donor Příspěvek Kdy Kampaň 1 300 30. 10. 2014 hithit 1 1000 1. 11. 2014 konktatní 2 500 1. 11. 2014 hithit 3 300 30. 10. 2014 kontaktní SELECT * FROM prispevky JOIN donori ON donori.ID = prispevky.donor Public Výsledek Jméno Kontakt Poznámka Příspěvek Kampaň Honza jsem@posp.cz Nevolat před 10 dopo 300 hithit Honza jsem@posp.cz Nevolat před 10 dopo 1000 kontaktní Tomáš fil.hrabal@gmail .com 500 hithit Eliška eliska@eps.cz Pracuje v EPS 300 kontaktní Public JOIN vs. LEFT JOIN Donoři Příspěvky Public JOIN vs. LEFT JOIN Donoři Příspěvky NIC Public (INNER) JOIN Donoři Příspěvky Public POZOR: Databáze je hloupá! Neví nic o sémantice vašich dat Public Neví nic o sémantice vašich dat SELECT * FROM donori JOIN prispevky ON donori.rocnik = prispevky.castka POZOR: Databáze je hloupá! Public ID Jméno Kontakt Telefon Ročník Poznámka 1 Honza jsem@posp.cz 111 1984 Nevolat před 10 dopo 2 Tomáš fil.hrabal@gmail.com 222 1985 3 Eliška eliska@frankbold.org 333 1983 Pracuje ve FrankBold Public JOIN vs. LEFT JOIN Donoři Příspěvky NIC Public LEFT Donoři Příspěvky NULL Public Otázka Kolik kdo z dárců v průběhu roku přispěl? Pouze ti, kteří něco přispěli: SELECT donori.jmeno, SUM(castka) FROM donori INNER JOIN prispevky ON donori.ID = prispevky.donor GROUP BY donori.ID Všichni SELECT donori.jmeno, SUM(castka) FROM donori LEFT JOIN prispevky ON donori.ID = prispevky.donor GROUP BY donori.ID U těch co nepřispěli, je hodnota sloupce SUM(castka) NULL Public posp.cz/w/sport Vypište tabulku zápasu včetně jmen týmů (z tabulky “tym”) Pokud vícekrát připouji stejnou tabulku, musím zavést “aliasy”, jinak bude dotaz končit chybou “ambiguous column name”, databáze “neví” jaký konkrétní sloupeček myslíte. Příklad dotazu, který skončí chybou: SELECT * FROM zapas INNER JOIN tym ON tym.id_tym = domaci_tym INNER JOIN tym ON tym.id_tym = vitezny_tym Upravená funkční verze: SELECT tabulka_tym_domaci.nazev as nazev_domaci, tabulka_tym_hoste.nazev as nazev_hoste, golu_domaci, golu_hoste FROM zapas INNER JOIN tym AS tabulka_tym_domaci ON tabulka_tym_domaci.id_tym = domaci_tym INNER JOIN tym AS tabulka_tym_hoste ON tabulka_tym_hoste.id_tym = hostujici_tym (pozn. Název aliasu je čistě na vás) Public PříspěvkyDonoři M:N AutořiKnihy Vazba M:M (někdy psáno ”M:N”, nebo Many:Many) Public PříspěvkyDonoři M:N AutořiKnihy 1:M nestačí, neumí zachutit, že jeden autor napsal více knih Public PříspěvkyDonoři M:N AutořiKnihy M:1 nestačí, neumí zachutit, že jednu knihu napsalo vice lidí Public PříspěvkyDonoři M:N AutořiKnihy Řešením je tzv. vazební tabulka (doporučuji si pustit záznam 2. boku od času 2h 7min Public PříspěvkyDonoři M:N StudentPředmět Public PříspěvkyDonoři M:N Klíčové slovo Článek Public posp.cz/w/eshop Public Public Otázka Vypište knihy, které napsal Constance Carroll (jedním dotazem) SELECT autori.id, autori.jmeno, autori.prijmeni, knihy.nazev FROM autori INNER JOIN autori_knihy ON autori.id = autori_knihy.autori_id INNER JOIN knihy ON autori_knihy.knihy_id = knihy.id WHERE jmeno LIKE 'Constance' AND prijmeni LIKE 'Carroll’ autori_knihy je ”vazební tabulka” Public x Public Public