Занятие 29. Отработка основных операторов. Часть 1

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

Задание 1. Сортировка и ограничение

USE northwind;

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 15;

Задание 2. Постраничный вывод

USE northwind;

SELECT first_name, last_name
FROM customers
ORDER BY last_name
LIMIT 10 OFFSET 10;

Задание 3. Уникальные значения

USE northwind;

SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;

Задание 4. Агрегация и группировка

USE northwind;

SELECT c.category_name,
       COUNT(p.id) AS product_count,
       AVG(p.unit_price) AS avg_price
FROM categories AS c
JOIN products AS p ON c.id = p.category_id
GROUP BY c.category_name
ORDER BY avg_price DESC;

Задание 5. Фильтрация групп

USE northwind;

SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 2;

Задание 6. JOIN

USE northwind;

SELECT o.id AS order_id,
       c.first_name AS customer_name,
       s.company AS shipper_company
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
JOIN shippers AS s ON o.shipper_id = s.id;

Задание 7. UNION

USE northwind;

SELECT company FROM customers
UNION
SELECT company FROM suppliers
UNION
SELECT company FROM shippers;