Úvod do jazyka SQL Hodina č. 6 Osnova • Info k testu • GROUP BY • Další SQL slovesa • JOIN! TEST • Středa 6. 4., začátek 15.50 (čili v rámci hodiny) v B2.33 • Tři snadné příklady • Každý za 10 bodů • Dálkaři (a prezenční studenti s dobrou výmluvou) si domluví termín bokem - napište nám. • Můžete mít vlastní materiály + dokumentaci SQLite GROUP BY + agregace agragační funkce WHERE + GROUP BY + agregace where BLUE or RED WHERE + GROUP BY + agregace GROUP BY color WHERE + GROUP BY + agregace Agregační funkce číslo číslo Vypište fakulty, kde pracuje alespoň 200 lidí SELECT fakulta, SUM(pocet_lidi) as pocet FROM fakulty WHERE pocet >= 200 GROUP BY fakulta ^ TOHLE NEFUNGUJE! agragační funkce WHERE WHERE se totiž aplikuje ještě před agregací GROUP BY + agregace + HAVING agragační funkce GROUP BY + agregace + HAVING GROUP BY + agregace + HAVING HAVING Vypište fakulty, kde pracuje alespoň 200 lidí SELECT fakulta, SUM(pocet_lidi) as pocet FROM fakulty GROUP BY fakulta HAVING pocet >= 200 ^ TOHLE FUNGUJE! Opakování SELECT fakulta, SUM(pocet_lidi) as lidi_na_pracovisti FROM fakulty WHERE fakulta LIKE "f%" GROUP BY fakulta HAVING lidi_na_pracovisti > 200 ORDER BY fakulta DESC LIMIT 3 DISTINCT SELECT fakulta FROM fakulty; SELECT DISTINCT fakulta FROM fakulty; SELECT DISTINCT fakulta, budova FROM fakulty; U každé fakulty vypište počet budov a kateder SELECT fakulta, COUNT(budova), COUNT(katedra) FROM fakulty GROUP BY fakulta; ^ TOHLE NEFUNGUJE! oba COUNTy prostě spočítají “počet řádků”. Budov je však méně, jen se opakují U každé fakulty vypište počet budov a kateder SELECT fakulta, COUNT(DISTINCT budova), COUNT(katedra) FROM fakulty GROUP BY fakulta; ^ TOHLE FUNGUJE! DISTINCT způsobí, že každá z budov je započítána jen jednou Slovesa SQL • SELECT … • INSERT • UPDATE • DELETE INSERT INSERT INTO table (column1, column2,…) VALUES (value1, value2,…); UPDATE UPDATE table SET column1 = value1, column2 = value2 WHERE … AND … UPDATE UPDATE table SET column1 = value1, column2 = value2 WHERE … AND … TIP: Vždy si nejdřív zkuste SELECT se stejnou podmínkou DELETE DELETE FROM table WHERE … AND … DELETE DELETE FROM table WHERE … AND … TIP: Vždy si nejdřív zkuste SELECT se stejnou podmínkou JOIN Příspěvky Jméno E-mail Příspěvek Kampaň Honza jsem@posp.cz 300 hithit Honza jsem@posp.cz 1000 kontaktní Tomáš fil.hrabal@gmail. com 500 hithit Eliška eliska@eps.cz 300 kontaktní Problémy? Jméno E-mail Příspěvek Kampaň Honza jsem@posp.cz 300 hithit Honza jsem@posp.cz 1000 kontaktní Tomáš fil.hrabal@gmail. com 500 hithit Eliška eliska@eps.cz 300 kontaktní Problémy? • Změna kontaktů u dárce - musíte změnit všude • Pokud byste chtěli více kontaktů (telefon, adresa, …), tak se tabulka nafukuje, ač vám původně šlo jen o evidenci příspěvků • Problematické evidování poznámek (např. “nevolat”) • Evidence potenciálních dárců je problematická PříspěvkyDonoři Řešení? Dvě tabulky 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 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í http://fund2.jdem.cz/ PříspěvkyDonoři Řešení? PříspěvkyDonoři ID Jméno Kontakt Poznámka 1 Honza jsem@posp.cz Nevolat před 10 dopo 2 Tomáš fil.hrabal@gmai l.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í JOIN ID Jméno Kontakt Poznám ka 1 Honza jsem@pos p.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í JOIN ID Jméno Kontakt Poznám ka 1 Honza jsem@pos p.cz Nevolat před 10 dopo 2 Tomáš fil.hrabal@ gmail.com 3 Eliška eliska@eps .cz Pracuje v EPS SELECT * FROM prispevky JOIN donori ON donori.ID = prispevky.donor 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í 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@gma il.com 500 hithit Eliška eliska@eps.cz Pracuje v EPS 300 kontaktní JOIN vs. LEFT JOIN Donoři Příspěvky JOIN vs. LEFT JOIN Donoři Příspěvky NIC (INNER) JOIN Donoři Příspěvky Řádek z levé tabulky, který nemá odpovídající záznam v pravé je “ztracen” Neví nic o sémantice vašich dat SELECT * FROM donori JOIN prispevky ON donori.rocnik = prispevky.castka POZOR: Databáze je hloupá! Jde o syntakticky správný dotaz, který vrací výsledky, ale ty nedávají moc smysl JOIN vs. LEFT JOIN Donoři Příspěvky NIC LEFT JOIN Donoři Příspěvky NULL Vypište lidi, kteří zatím nikdy nedarovali SELECT donori.* FROM donori LEFT JOIN prispevky ON donori.ID = prispevky.donor WHERE castka IS NULL Otázka Kolik kdo z dárců v průběhu roku přispěl? SELECT donori.jmeno, SUM(castka) FROM donori INNER JOIN prispevky ON donori.ID = prispevky.donor GROUP BY donori.ID