Пекарната, етап 3: въпросите

Накратко: Третият етап отговаря на въпросите на пекарната с JOIN, GROUP BY, LEFT JOIN, WITH и COALESCE: менюто, сметките, най-продаваното и приходите по град.

Проблем

Какво искат да знаят

Пекарната има данни. Сега иска отговори: какво да напише в менюто, колко е платил всеки клиент, кое да пече повече, на кого да прати отстъпка, кои поръчки са големи и откъде идват парите.

Всеки въпрос е една заявка към базата от етап 2. Нищо не се записва: базата е същата след всяка заявка.

Идеята

Евтините неща

Най-простият въпрос е към една таблица: продуктите под 2 €, от най-евтиния нагоре.

SQLпразна база

SELECT name, price
FROM products
WHERE price < 2
ORDER BY price;

Пробвай

Менюто

Направи менюто: категорията (колона category), името и цената на всеки продукт. Подреди го по категории в реда на номерата им (хляб, сладкиши, напитки), а в категорията от най-евтиния продукт.

SQLпразна база

SELECT name, price
FROM products;

Решение

SELECT c.name AS category, p.name, p.price
FROM products p
JOIN categories c ON c.id = p.category_id
ORDER BY c.id, p.price;

Идеята

Сметката

Всеки ред от поръчката струва quantity * unit_price. Поръчка 5 на Десислава:

SQLпразна база

SELECT order_id, product_id, quantity, unit_price,
       quantity * unit_price AS line
FROM order_items
WHERE order_id = 5;

Сумата на поръчката е сборът на тези редове: SUM(quantity * unit_price) с GROUP BY по поръчка.

Пробвай

Сметките на всички

За всяка поръчка, която не е отказана: номер, името на клиента, датата и сумата (колона total, закръглена до 2 знака). Подреди по номер.

SQLпразна база

SELECT o.id, o.ordered_at
FROM orders o
ORDER BY o.id;

Решение

SELECT o.id, c.name, o.ordered_at, ROUND(SUM(i.quantity * i.unit_price), 2) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.id
ORDER BY o.id;

По-точно, за любопитните

GROUP BY o.id, а в SELECT са c.name и o.ordered_at, които не са в групата. SQLite го позволява. PostgreSQL приема o.ordered_at, защото първичният ключ на orders е в групата, но не и c.name, която е от друга таблица. Там се пише GROUP BY o.id, c.name, и така заявката работи навсякъде.

Пробвай

Кое да пече повече

Трите най-продавани продукта по бройки, без отказаните поръчки: името и броят (колона pieces), от най-продавания. При равни бройки по име.

SQLпразна база

SELECT product_id, quantity
FROM order_items;

Решение

SELECT p.name, SUM(i.quantity) AS pieces
FROM order_items i
JOIN orders o ON o.id = i.order_id
JOIN products p ON p.id = i.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.id
ORDER BY pieces DESC, p.name
LIMIT 3;

Идеята

Кой не е поръчвал

LEFT JOIN пази всеки клиент, дори без поръчка. Там колоните на поръчката са NULL, и WHERE o.id IS NULL оставя точно тях:

SQLпразна база

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Пробвай

Отстъпка за новите

Пекарната праща отстъпка на всички, които нямат нито една истинска поръчка: без поръчки или само с отказани. Покажи имената им по азбучен ред.

SQLпразна база

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Решение

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
WHERE o.id IS NULL
ORDER BY c.name;

Пробвай

Големите поръчки

Кои поръчки (без отказаните) са над средната сума на поръчка? Номер и сума (total, закръглена до 2 знака), по номер. Направи сумите с WITH totals AS (…).

SQLпразна база

WITH totals AS (
  SELECT o.id, SUM(i.quantity * i.unit_price) AS total
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
  GROUP BY o.id
)
SELECT id, ROUND(total, 2) AS total
FROM totals
ORDER BY id;

Решение

WITH totals AS (
  SELECT o.id, SUM(i.quantity * i.unit_price) AS total
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id
)
SELECT id, ROUND(total, 2) AS total
FROM totals
WHERE total > (SELECT AVG(total) FROM totals)
ORDER BY id;

Упражнение

Откъде идват парите

Приходите по град от поръчките, които не са отказани, за всички градове на клиентите. Град без приходи е с 0, а клиентите без град са под 'без град'. Колони city и revenue (закръглена до 2 знака), от най-големия приход.

SQLпразна база

SELECT c.city, o.id
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Решение

SELECT COALESCE(c.city, 'без град') AS city,
       ROUND(COALESCE(SUM(i.quantity * i.unit_price), 0), 2) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY c.city
ORDER BY revenue DESC;

По-точно, за любопитните

SUM върху група само с NULL дава NULL, не 0. Затова е COALESCE(SUM(…), 0). Сумата на поръчка е десетична, затова е ROUND: 0.1 + 0.2 в REAL не е точно 0.3.

Идеята

Какво следва

Всички тези заявки само четат. Следващият етап сменя данните: цени, статуси, наличности, изтрити поръчки.

Тест

Провери се

1. Защо в сметката на поръчката се събира quantity * unit_price, а не price от products?unit_price е цената, която клиентът е платил. price в products е днешната и може да е другаprice в products е NULLТака е по-кратко

2. LEFT JOIN orders o ON o.customer_id = c.id и после WHERE o.status <> 'cancelled'. Какво става с клиентите без поръчки?Изчезват: при тях o.status е NULL, а NULL <> 'cancelled' не е истинаБроят се два пътиОстават с NULL

3. Средното на поръчка е 9.40 €. Защо не се пише WHERE total > 9.4?SQLite не сравнява дробни числа9.4 не е цяло числоУтре има нови поръчки и средното е друго. Подзаявката го смята всеки път

Създаден от Иво Гергов като част от кампанията на Evo Tech за достъпно обучение по програмиране ·

Пекарната, етап 3: въпросите

Бисквитки