Занятие 23. Практикум 6

✅ Решения заданий

Задание 1. UNION ALL трёх таблиц

USE northwind;

SELECT company FROM employees
UNION ALL
SELECT company FROM customers
UNION ALL
SELECT company FROM suppliers;

Объяснение: три однотипных запроса возвращают по одному столбцу company. UNION ALL сохраняет все строки, включая повторы.

Задание 2. Источник строки

Почему не стоит использовать UNION: одинаковые названия компаний встречаются в разных таблицах. Если применить UNION, такие строки будут удалены, и мы потеряем информацию о том, из каких таблиц они пришли.

USE northwind;

SELECT company, 'employees' AS source FROM employees
UNION ALL
SELECT company, 'customers' AS source FROM customers
UNION ALL
SELECT company, 'suppliers' AS source FROM suppliers;

Объяснение: литерал во втором столбце позволяет отличить источник каждой строки. Теперь даже совпадающие названия не сольются, потому что строки целиком различаются.

Задание 3. Сотрудники без привилегий

USE northwind;

SELECT e.first_name, e.last_name
FROM employees AS e
LEFT JOIN employee_privileges AS ep
  ON e.id = ep.employee_id
WHERE ep.employee_id IS NULL;

Объяснение: LEFT JOIN возвращает всех сотрудников. Для тех, у кого нет записи в employee_privileges, столбец ep.employee_id будет NULL. Фильтр оставляет только таких сотрудников.

Задание 4. Расшифровка inventory_transactions

USE northwind;

SELECT it.transaction_created_date, itt.type_name, p.product_name
FROM inventory_transactions AS it
JOIN inventory_transaction_types AS itt
  ON it.transaction_type = itt.id
JOIN products AS p
  ON it.product_id = p.id;

Объяснение: таблица inventory_transactions хранит идентификаторы типа транзакции и продукта. Через два JOIN получаем человекочитаемые названия.

Задание 5. Подсчёт транзакций по типу

USE northwind;

SELECT itt.type_name, COUNT(*) AS transaction_count
FROM inventory_transactions AS it
JOIN inventory_transaction_types AS itt
  ON it.transaction_type = itt.id
JOIN products AS p
  ON it.product_id = p.id
WHERE itt.type_name NOT LIKE '%Sold%'
GROUP BY itt.type_name;

Объяснение: WHERE отсеивает типы со словом Sold до группировки. COUNT(*) считает строки в каждой группе. Вместо GROUP BY itt.type_name можно использовать GROUP BY 1 (по номеру столбца), но именованный вариант читабельнее.

Задание 6. Расшифровка заказов Seattle

USE northwind;

SELECT o.*, e.first_name AS employee_name,
       c.company_name AS customer_name, s.company AS shipper_name
FROM orders AS o
LEFT JOIN employees AS e
  ON o.employee_id = e.id
LEFT JOIN customers AS c
  ON o.customer_id = c.id
LEFT JOIN shippers AS s
  ON o.shipper_id = s.id
WHERE o.ship_city = 'Seattle';

Почему LEFT JOIN: в таблице orders некоторые внешние ключи могут быть не заполнены или ссылаться на отсутствующие записи. Если использовать INNER JOIN, заказы с пустыми или «битыми» связями исчезнут из результата. LEFT JOIN сохраняет все заказы с ship_city = 'Seattle', а отсутствующие данные показывает как NULL.