Prezentace / Lekce 7 – SQL do hloubky
1 / 10
Lekce 07 · 45 min · T/P 30 / 70

SQL umí mnohem víc než SELECT * FROM

Dnes rozšíříme SQL o šest mocných nástrojů: ORDER BY, LIKE, INNER JOIN, LEFT JOIN, GROUP BY + agregace, a rozdíl mezi WHERE a HAVING.

Dataset · eshop.db

20 zákazníků ze 4 měst, 30 produktů v 6 kategoriích, 30 objednávek, 80 položek.

Dataset · knihovna.db

15 autorů z 5 zemí, 40 knih v 10 žánrech, 20 čtenářů, 50 výpůjček (15 stále nevrácených).

Jak to funguje. Výklad (S1–S7) používá převážně eshop. V hlavním cvičení (S8) si zvolíš podle libosti – obě sady 10 úloh jsou srovnatelně náročné, ale liší se doménou.
01 · Řazení, omezení, unikátní

ORDER BYGGlosářWWikipedie CS, LIMITGGlosářWWikipedie CS, DISTINCT

Tři klauzule, které budete denně používat. ORDER BY řadí, LIMIT omezuje počet, DISTINCT odstraňuje duplicity.

ORDER BY
ORDER BY cena DESC
ORDER BY prijmeni ASC
ORDER BY cena DESC, id ASC

ASC = vzestupně (default), DESC = sestupně. Lze řadit podle víc sloupců.

LIMIT
LIMIT 5
LIMIT 10 OFFSET 20

Omezuje počet řádků. OFFSET přeskočí N řádků (stránkování).

DISTINCT
SELECT DISTINCT mesto
FROM zakaznici

Vrátí jen unikátní hodnoty. Užitečné pro „kolik různých…".

Úloha 01 / 10. Najdi 5 nejdražších produktů. Zobraz jen nazev a cena.
02 · Hledání v textu

LIKEGGlosářWWikipedie CS – částečná shoda

= hledá přesnou shodu, LIKE umí wildcards: % (libovolně znaků) a _ (právě jeden znak).

LIKE 'M%'začíná na M
LIKE '%ová'končí na „ová"
LIKE '%kabel%'obsahuje „kabel"
LIKE '___ova'3 znaky + „ova"

Pozor na diakritiku. SQLite LIKE je defaultně case-insensitive jen pro ASCII. LIKE 'nová%' chytí „Novák", ale LIKE 'něm%' nemusí chytit „NĚmec", protože znak ě není považován za stejný jako Ě. Pro češtinu používej UPPER(). Například … WHERE UPPER(prijmeni) LIKE UPPER('Němec%').

Úloha 02 / 10. Vypiš všechny produkty, jejichž název obsahuje slovo Kabel. Seřaď vzestupně podle ceny.
03 · Spojení tabulek

INNER JOINGGlosářWWikipedie CS – jen shodné řádky

Cizí klíče by byly k ničemu, kdybychom neuměli tabulky spojit. INNER JOIN vezme jen ty řádky, kde oba klíče odpovídají.

objednavky 🔑 id ↗ zakaznik_id datum status zakaznici 🔑 id jmeno prijmeni mesto ON objednavky.zakaznik_id = zakaznici.id
INNER JOIN vrátí jen ty řádky, které mají shodu v obou tabulkách. Bez JOINu by tabulky zůstaly oddělené.

Syntax s aliasy

SELECT o.id, o.datum, z.jmeno, z.prijmeni, z.mesto
FROM objednavky o
INNER JOIN zakaznici z ON o.zakaznik_id = z.id
ORDER BY o.datum DESC
LIMIT 5;

o a z jsou aliasy – zkracují zápis. Místo objednavky.datum stačí o.datum. V SELECT lze použít jak aliasy, tak plné názvy.

Úloha 03 / 10. Vypiš 5 nejnovějších objednávek spolu se jménem a příjmením zákazníka. Pořadí: od nejnovějšího data, při shodě data podle id DESC.
INNER JOIN A B Vrací jen průnik LEFT JOIN A B Vrací celé A + průnik; chybějící B = NULL
Kulaté obrazce (tabulky) a jejich průniky – vizuální rozdíl mezi INNER a LEFT JOIN.

Praktická ukázka

Chci vypsat všechny autory a počet jejich knih v naší knihovně. Někteří autoři mohou mít 0 knih – chci je přesto vidět.

-- INNER JOIN by autory bez knih vynechal
SELECT a.prijmeni, COUNT(k.id) AS pocet_knih
FROM autori a
LEFT JOIN knihy k ON k.autor_id = a.id
GROUP BY a.id
ORDER BY pocet_knih DESC;
Úloha 04 / 10. Vypiš zákazníky, kteří nikdy neobjednali. Použij LEFT JOIN a WHERE ... IS NULL. (V našem datasetu mají všichni aspoň jednu objednávku, takže výsledek je prázdný – to je correct.)
LEFT JOIN pattern „kdo nemá". LEFT JOIN ... WHERE ... IS NULL je standardní vzor pro „všechny z levé tabulky, které nemají odpovídající řádek v pravé". V knihovně: kniha bez výpůjčky. V e-shopu: produkt bez nákupu.
05 · Seskupení

GROUP BYGGlosářWWikipedie CS + COUNT

GROUP BY seskupí řádky podle hodnoty sloupce. COUNT(*) spočítá, kolik jich v každé skupině je.

SELECT kategorie, COUNT(*) AS pocet
FROM produkty
GROUP BY kategorie
ORDER BY pocet DESC;

Vrátí 1 řádek pro každou kategorii s počtem produktů. AS pocet přejmenuje sloupec ve výstupu.

Úloha 05 / 10. Spočítej, kolik produktů je v každé kategorii. Při stejném počtu seřaď abecedně.
Pravidlo GROUP BY. V SELECT smí být jen (a) sloupce, podle kterých seskupuješ, nebo (b) agregační funkce (COUNT, SUM, AVG…). Jinde to je chyba (v PostgreSQL i nedefinované v SQLite – pamatuj na to).
06 · Další agregace

Pět agregačních funkcíGGlosářWWikipedie CS

Všechny pracují nad skupinou (nebo celou tabulkou, pokud neuvedeš GROUP BY).

COUNT(*)počet řádků (včetně NULL)
COUNT(sloupec)počet ne-NULL hodnot
SUM(sloupec)součet
AVG(sloupec)průměr (ignoruje NULL)
MIN(sloupec)minimum
MAX(sloupec)maximum

Scalar funkce

Agregační funkce pracují na sloupci/skupině. Scalar funkce pracují na jednom řádku:

ROUND(x, N)zaokrouhlení na N míst
UPPER(text)velká písmena
LENGTH(text)délka řetězce
SUBSTR(text, od, N)výřez
Úloha 06 / 10. Pro každou kategorii: počet produktů, nejnižší cena, nejvyšší cena, průměrná cena (zaokrouhlená na celé Kč). Seřaď od nejdražšího průměru.
07 · Filtr před × po agregaci

WHERE vs HAVING

Obě klauzule filtrují výsledky dotazu. Ale každá v jinou chvíli. Nesmí být proto zaměněny.

WHERE – před agregací

Filtruje jednotlivé řádky před tím, než se seskupí. Nemůže používat agregační funkce.

-- OK: filtr jednotlivých objednávek
SELECT zakaznik_id, COUNT(*)
FROM objednavky
WHERE status = 'dokončena'
GROUP BY zakaznik_id;
HAVING – po agregaci

Filtruje skupiny po agregaci. MŮŽE používat agregační funkce (COUNT, SUM atd.).

-- Chci jen zákazníky s > 2 objednávkami
SELECT zakaznik_id, COUNT(*)
FROM objednavky
GROUP BY zakaznik_id
HAVING COUNT(*) > 2;

Pořadí provádění v SQL

  1. FROM – vezmi řádky z tabulek (JOIN)
  2. WHERE – vyfiltruj řádky před agregací
  3. GROUP BY – seskup je
  4. HAVING – vyfiltruj skupiny po agregaci
  5. SELECT – vyber sloupce (a agregace)
  6. ORDER BY – seřaď
  7. LIMIT – omez počet

Klíč k porozumění. WHERE nemůže filtrovat „zákazníky s více než 2 objednávkami", protože v okamžiku WHERE ještě neexistují skupiny – je to jednotlivý řádek objednávky. HAVING to umí, protože běží až po GROUP BY.

Úloha 07 / 10. Vypiš kategorie, kde průměrná cena produktu přesahuje 5000 Kč. Seřaď od nejdražší průměrné.
08 · Hlavní cvičení · zbývající úlohy

Ještě 3 úlohy – nad datasetem knihovna

Přepínáme na knihovna.db. Úlohy používají LEFT JOIN, IS NULL a HAVING – vše, co jsme dnes probrali.

Úloha 08 / 10. Vypiš seřazené názvy knih, které jsou momentálně půjčené (nevrácené). Použij IS NULL a INNER JOIN s tabulkou knih.
Úloha 09 / 10. Kolik knih má každý autor? Vypiš jméno, příjmení a počet. Seřaď od nejplodnějšího, při shodě abecedně podle příjmení.
Úloha 10 / 10. Žánry, ve kterých je více než 3 knihy. Použij HAVING.
Stihli jste všech 10? Pokud ano, pokračujte k závěrečnému kvízu. Pokud ne, vraťte se zpět a doplňte. Všechna řešení najdete v data/*.sql nebo si v playgroundu experimentujte – SQLite nic neuloží.
09 · Ověření

Souhrnný kvíz – 8 otázek

Otázka 1 · ABCD
1 správná
Která kombinace správně vypíše 3 nejdražší produkty?
  • A
    SELECT * FROM produkty LIMIT 3 ORDER BY cena ASC
  • B
    SELECT * FROM produkty WHERE cena MAX LIMIT 3
  • C
    SELECT * FROM produkty ORDER BY cena DESC LIMIT 3
  • D
    SELECT TOP 3 * FROM produkty
Otázka 2 · ABCD
1 správná
Co znamená wildcard % v LIKE?
  • A
    právě jeden znak
  • B
    libovolný počet znaků (včetně nuly)
  • C
    číslice
  • D
    konec textu
Otázka 3 · ABCD
1 správná
Jaký je rozdíl mezi INNER JOIN a LEFT JOIN?
  • A
    INNER vrátí jen řádky se shodou; LEFT vrátí všechny z levé tabulky a NULL místo chybějících
  • B
    INNER je rychlejší, LEFT pomalejší
  • C
    INNER funguje v SQLite, LEFT jen v MySQL
  • D
    Jsou identické
Otázka 4 · ABCD
1 správná
Co platí o rozdílu WHERE × HAVING?
  • A
    WHERE je novější než HAVING
  • B
    WHERE filtruje před GROUP BY, HAVING po GROUP BY (smí používat agregační funkce)
  • C
    WHERE je pro čísla, HAVING pro text
  • D
    Jsou totožné
Otázka 5 · ABCD
1 správná
K čemu slouží GROUP BY?
  • A
    Řadí řádky
  • B
    Filtruje řádky
  • C
    Seskupí řádky podle hodnoty sloupce pro následnou agregaci
  • D
    Přejmenuje sloupec
Otázka 6 · ABCD
1 správná
Co vrátí COUNT(sloupec) (s explicitním sloupcem)?
  • A
    Počet řádků, kde sloupec NENÍ NULL
  • B
    Průměr hodnot
  • C
    Součet hodnot
  • D
    Maximum
Otázka 7 · Multi-select
vyber všechny správné
Které z těchto jsou agregační funkce?
  • ✓
    COUNT
  • ✓
    SUM
  • ✓
    AVG
  • ✓
    MAX
Otázka 8 · ABCD
1 správná
Jak najdeš autory, kteří nemají v knihovně žádnou knihu?
  • A
    SELECT * FROM autori WHERE knihy = NULL
  • B
    SELECT a.* FROM autori a LEFT JOIN knihy k ON k.autor_id = a.id WHERE k.id IS NULL
  • C
    SELECT * FROM autori INNER JOIN knihy ON 0 = 0
  • D
    SELECT * FROM autori EXCEPT knihy
Předchozí← L6 Databáze HotovoZpět na rozcestník →