Lekce 2.2

SQL v praxi

Výběr, filtrování a řazení dat, vkládání, úpravy a mazání záznamů, potom JOINy včetně rozdílu LEFT a RIGHT a agregace – vše nad připravenou databází e-shopu.

Cvičení 1

Sneaker shop – pokračování

Co si vyzkoušíte

Pokračujete nad databází Sneaker shop, tentokrát s dotazy přes víc tabulek a se součty. Jestli máte data ještě změněná z minulého cvičení, obnovte nejdřív databázi tlačítkem Obnovit databázi, ať počítáte s výchozími daty.

Zadání

  1. Vypište všechny objednávky zákazníka s e-mailem honza.novak@example.cz: číslo objednávky, datum a stav. Proč ho hledáme podle e-mailu, a ne podle jména?

  2. Vypište obsah objednávky 103: název produktu, počet kusů, cenu za kus v objednávce a dnešní cenu produktu. U kterého produktu se ceny liší a proč?

  3. Spočítejte, kolik objednávek má každý zákazník. Zákazníci bez objednávek ať ve výsledku zůstanou s nulou.

  4. Spočítejte celkovou hodnotu objednávek každého zákazníka a seřaďte zákazníky od toho, kdo utratil nejvíc. Hodnota objednávky je součet quantity * price přes její položky.

  5. Najděte zákazníky, kteří zatím nic neobjednali. Napište dotaz jednou přes LEFT JOIN a jednou přes RIGHT JOIN a ověřte, že oba vrátí stejné zákazníky.

  6. Najděte produkty, které se zatím neprodaly ani jednou.

  7. Pro každou kategorii vypište počet produktů a průměrnou cenu zaokrouhlenou na celé koruny. Nechte jen kategorie s průměrnou cenou nad 1 000 Kč.

  8. Tenhle dotaz měl vypsat zákazníky, kteří v obchodě utratili víc než 10 000 Kč. Spusťte ho, najděte chybu, opravte ji a vysvětlete, v čem byla:

    sql
    SELECT c.name, SUM(oi.quantity * oi.price) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE total > 10000
GROUP BY c.name
ORDER BY total DESC;

  9. Opravený dotaz už běží. Porovnejte jeho výsledek s krokem 4: sedí jména i částky? Pokud ne, najděte v dotazu druhou chybu a opravte i ji.

Bonus

  1. Vrácené objednávky (stav vrácená) se do útraty nepočítají. Upravte dotaz z kroku 4 tak, aby je vynechal. Změní se pořadí zákazníků?
  2. Spočítejte tržby obchodu po měsících. Měsíc z data vytáhne funkce strftime('%Y-%m', created_at).
  3. Najděte pět nejprodávanějších produktů podle počtu prodaných kusů.