Занятие 24. Summary session 6

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

Задание 1

USE northwind;

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

Задание 2

Почему 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;

Задание 4

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;

Задание 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;

Задание 6

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: некоторые заказы могут не иметь связанного сотрудника, клиента или перевозчика. INNER JOIN скрыл бы такие заказы из результата.