Занятие 27. Практикум 7

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

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

Способ 1. LEFT JOIN

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 будут NULL. Фильтр по ep.employee_id IS NULL оставляет только таких сотрудников.

Способ 2. Подзапрос с NOT IN

USE northwind;

SELECT first_name, last_name
FROM employees
WHERE id NOT IN (
    SELECT employee_id FROM employee_privileges
);

Объяснение: подзапрос возвращает список идентификаторов сотрудников с привилегиями. Внешний запрос выбирает тех, чьих идентификаторов в этом списке нет.

Способ 3. CTE

USE northwind;

WITH privileged AS (
    SELECT employee_id FROM employee_privileges
)
SELECT first_name, last_name
FROM employees
WHERE id NOT IN (SELECT employee_id FROM privileged);

Объяснение: CTE отделяет подготовку списка привилегированных сотрудников от основной логики фильтрации.

Задание 2. Заказы в Las Vegas от выбранных сотрудников

Способ 1. Подзапрос

USE northwind;

SELECT *
FROM orders
WHERE ship_city = 'Las Vegas'
  AND employee_id IN (
      SELECT id
      FROM employees
      WHERE first_name LIKE '%e%'
         OR job_title = 'Sales Representative'
  );

Способ 2. CTE

USE northwind;

WITH selected_employees AS (
    SELECT id
    FROM employees
    WHERE first_name LIKE '%e%'
       OR job_title = 'Sales Representative'
)
SELECT *
FROM orders
WHERE ship_city = 'Las Vegas'
  AND employee_id IN (SELECT id FROM selected_employees);

Объяснение: условие на сотрудников вынесено в CTE, что делает основной запрос короче и понятнее.

Задание 3. Клиенты из компаний A–F и перевозчик №3

Способ 1. JOIN

USE northwind;

SELECT DISTINCT c.first_name, s.company AS shipper_company
FROM customers AS c
JOIN orders AS o ON c.id = o.customer_id
JOIN shippers AS s ON o.shipper_id = s.id
WHERE c.company IN ('A', 'B', 'C', 'D', 'F')
  AND o.shipper_id = 3;

Объяснение: цепочка JOIN связывает клиентов, заказы и перевозчиков. DISTINCT убирает дубли, если клиент делал несколько таких заказов.

Способ 2. Подзапрос

USE northwind;

SELECT first_name,
       (SELECT company FROM shippers WHERE id = 3) AS shipper_company
FROM customers
WHERE company IN ('A', 'B', 'C', 'D', 'F')
  AND id IN (
      SELECT customer_id
      FROM orders
      WHERE shipper_id = 3
  );

Объяснение: скалярный подзапрос возвращает название перевозчика, а многострочный — список клиентов, которые использовали его.

Способ 3. Временная таблица

USE northwind;

CREATE TEMPORARY TABLE target_customers AS
SELECT id, first_name, company
FROM customers
WHERE company IN ('A', 'B', 'C', 'D', 'F');

CREATE TEMPORARY TABLE shipper3_orders AS
SELECT DISTINCT customer_id
FROM orders
WHERE shipper_id = 3;

SELECT tc.first_name, s.company AS shipper_company
FROM target_customers AS tc
JOIN shipper3_orders AS so ON tc.id = so.customer_id
CROSS JOIN shippers AS s
WHERE s.id = 3;

DROP TEMPORARY TABLE IF EXISTS target_customers;
DROP TEMPORARY TABLE IF EXISTS shipper3_orders;

Объяснение: временные таблицы сохраняют промежуточные результаты между запросами. Не забудьте удалить их после использования.