Занятие 26. Табличные выражения

🏠 Дополнительная практика по материалам занятия

Все задания выполняются в базе northwind. Решите задачи с помощью CTE; где указано — сравните с подзапросом.

Задание 1. Продукты и количество заказанных единиц

Выведите названия продуктов из таблицы products, включая количество заказанных единиц quantity для каждого продукта из таблицы order_details.

Решите задачу двумя способами:

• с помощью CTE;
• с помощью подзапроса.

Решение через CTE

USE northwind;

WITH product_qty AS (
    SELECT product_id, SUM(quantity) AS total_quantity
    FROM order_details
    GROUP BY product_id
)
SELECT p.product_name, COALESCE(pq.total_quantity, 0) AS total_quantity
FROM products AS p
LEFT JOIN product_qty AS pq ON p.id = pq.product_id;

Решение через подзапрос

USE northwind;

SELECT p.product_name,
       COALESCE((SELECT SUM(od.quantity)
                 FROM order_details AS od
                 WHERE od.product_id = p.id), 0) AS total_quantity
FROM products AS p;

Задание 2. Заказы после первого заказа клиента Lee

Найдите все заказы из таблицы orders, сделанные после даты самого первого заказа клиента Lee из таблицы customers.

USE northwind;

WITH lee_first_order AS (
    SELECT MIN(o.order_date) AS first_order_date
    FROM orders AS o
    JOIN customers AS c ON o.customer_id = c.id
    WHERE c.last_name = 'Lee'
)
SELECT *
FROM orders
WHERE order_date > (SELECT first_order_date FROM lee_first_order);

Задание 3. Продукты с максимальным target_level

Найдите все продукты из таблицы products, у которых target_level равен максимальному значению target_level в таблице.

USE northwind;

WITH max_target AS (
    SELECT MAX(target_level) AS max_level
    FROM products
)
SELECT *
FROM products
WHERE target_level = (SELECT max_level FROM max_target);