Занятие 28. Summary session 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;

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

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);

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

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

Способ 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;