Mysql запросы

Отличный выбор! База данных classicmodels — это золотой стандарт для обучения SQL. Она идеально подходит вашей аудитории (бухгалтеры/экономисты), потому что моделирует реальный бизнес (дистрибьютор моделей машин) и содержит все ключевые сущности: клиенты, товары, заказы, платежи, сотрудники.

Я адаптировал вашу программу и задания специально под эту базу, сохранив структуру (от простого к сложному) и добавив «бухгалтерский» контекст к каждой задаче.

Адаптация программы под БД `classicmodels`

Вот как теперь будут выглядеть блоки в терминах понятных вашим студентам:

Таблица customers -> Наши контрагенты (покупатели).
Таблица products -> Номенклатура (товары на складе).
Таблица orders -> Журнал заказов (реализация).
Таблица orderdetails -> Спецификация заказа (расшифровка, что именно купили).
Таблица payments -> Банковская выписка (поступление оплат).
Таблица employees -> Наши сотрудники (менеджеры по продажам).
Таблица offices -> Филиалы компании.

Комплект заданий для первого урока (и далее)

Я сгруппировал задания по темам, как у вас. Эти запросы можно и нужно выполнять на данных classicmodels.

Блок 1: Базовые SELECT запросы (Знакомство с данными)

Задание 1.1 (Знакомство с контрагентами): Вывести список всех клиентов (customers) из США (USA). Показать название компании (customerName), город (city) и штат (state).
- Контекст: «Давайте посмотрим, кто наши основные покупатели в Америке.»
Задание 1.2 (Знакомство с товарами): Вывести все товары (products) от производителя „Autoart Studio Design“ (productVendor).
- Контекст: «Посмотрим весь ассортимент одного поставщика.»
Задание 1.3 (Фильтрация по деньгам): Показать все заказы (orders) со статусом „Shipped“ (Отгружен).
- Контекст: «Отчет по отгруженным заказам. Это наша реализация.»

Блок 2: Агрегирующие функции и GROUP BY (Оборотно-сальдовые ведомости)

Задание 2.1 (Анализ остатков): Посчитать, сколько всего товаров (quantityInStock) каждого производителя (productVendor) есть на складе. Отсортировать по убыванию количества.
- Контекст: «Смотрим, у кого из поставщиков самые большие складские запасы.»
Задание 2.2 (Средний чек): Рассчитать среднюю стоимость заказа (total_amount). Подсказка: сумму заказа нужно посчитать в подзапросе или с помощью CTE, либо использовать таблицу orderdetails.
- Контекст: «Какой у нас средний чек по компании?»
Задание 2.3 (Анализ по странам): Посчитать количество клиентов в каждой стране (country). Вывести только те страны, где больше 5 клиентов (используйте HAVING).
- Контекст: «Определяем ключевые рынки сбыта.»

Блок 3: JOIN операции (Связанные документы)

Задание 3.1 (Заказы и клиенты): Вывести список всех заказов (orders), добавив к ним название компании-клиента (customerName).
- Контекст: «Формируем реестр заказов с расшифровкой контрагента.»
Задание 3.2 (Товары в заказах): Показать, какие товары (productName) входят в заказ номер 10101.
- Контекст: «Смотрим спецификацию конкретного заказа.»
Задание 3.3 (Топ-3 клиента по выручке): Найти 3 клиентов (customerName), которые принесли максимальную общую выручку (сумма amount из таблицы payments).
- Контекст: «Кто наши самые ценные клиенты?»

Блок 4: Подзапросы (Subqueries)

Задание 4.1 (Клиенты без заказов): Найти клиентов, которые зарегистрированы, но еще не сделали ни одного заказа.
- Контекст: «Ищем неактивных контрагентов для отдела продаж.»
Задание 4.2 (Лучший товар): Найти товар (или товары), цена которых выше средней цены по всем товарам.
- Контекст: «Товары премиум-сегмента.»

Блок 5: Оконные функции (Window Functions)

Задание 5.1 (Рейтинг клиентов): Проранжировать клиентов по сумме их оплат (из payments). Вывести имя, страну, сумму и ранг.
- Контекст: «Строим рейтинг клиентов для программы лояльности.»
Задание 5.2 (Накопительный итог): Показать помесячную сумму платежей и накопительный итог с начала года (используйте оконную функцию SUM() OVER()).
- Контекст: «Анализ динамики поступлений денежных средств.»

Блок 6: Работа с датами

Задание 6.1 (Дни просрочки): Для каждого заказа (orders) посчитать разницу в днях между датой отгрузки (shippedDate) и требуемой датой (requiredDate). Вывести для тех заказов, которые были отгружены с опозданием.
- Контекст: «Анализ качества логистики и своевременности доставки.»
Задание 6.2 (Поквартальный анализ): Посчитать общую сумму платежей по кварталам за 2004 год.
- Контекст: «Квартальный отчет по поступлениям.»

Блок 7: CASE выражения (Аналитические группировки)

Задание 7.1 (Сегментация товаров): Создать новое поле price_category, которое будет классифицировать товары (products) по цене (buyPrice): „Low“ (< 50), „Medium“ (50-100), „High“ (> 100).
- Контекст: «Разбиваем товары на ценовые сегменты для маркетинга.»
Задание 7.2 (Надежность клиента): В зависимости от кредитного лимита (creditLimit) клиента (customers) присвоить ему категорию: „VIP“ ( > 100000), „Standard“ (50000-100000), „Low“ (< 50000).
- Контекст: «Оценка надежности контрагента.»

Блок 8: CTE (Common Table Expressions) и витрины данных

Задание 8.1 (Витрина по продажам): Создать «витрину» для отчета по продажам. Используя CTE, собрать данные: номер заказа, дата заказа, имя клиента, страна клиента, название товара, категория товара (productLine), количество, цена.
- Контекст: «Собираем единую таблицу для загрузки в Power BI.»

Блок 9: Аналитические паттерны

Задание 9.1 (Повторные покупки): Найти клиентов, которые делали заказы в разные месяцы. (Классика RFM-анализа).
- Контекст: «Выявляем лояльных клиентов с историей.»
Задание 9.2 (Самые популярные категории): Для каждой линейки продуктов (productLine) посчитать общую выручку и процент от общей выручки компании.
- Контекст: «Анализ структуры продаж по направлениям.»

Методические рекомендации по проведению первого урока с этой БД

Начало (15 мин): «Коллеги, перед вами база данных небольшой фирмы, которая продает коллекционные модели машинок по всему миру. Здесь есть клиенты, заказы, платежи — все, как в 1С или ERP-системе, с которой вы работали. Только теперь мы будем «выдергивать» данные не через стандартные отчеты, а с помощью универсального языка — SQL.»
Практика «Первый запрос» (15 мин):
- Покажите таблицу customers.
- Задача: «Давайте посмотрим, кто есть в нашей базе». Запрос: SELECT * FROM customers; — просто смотрим.
- Задача: «А теперь давайте найдем всех наших клиентов в Москве (NYC)». Запрос: SELECT customerName, city FROM customers WHERE city = 'NYC'; — видите, как просто заменить фильтр в Excel?
Практика «Сводная таблица» (20 мин):
- Задача: «Руководитель просит отчет: сколько клиентов в каждой стране. В Excel вы бы сделали сводную таблицу. А мы сделаем так:»
- Запрос: SELECT country, COUNT(*) as client_count FROM customers GROUP BY country ORDER BY client_count DESC;
- Комментарий: «Обратите внимание, мы не просто посчитали, но и отсортировали — страны с максимальным числом клиентов сверху.»
Практика «ВПР мечты» (INNER JOIN) (25 мин):
- Задача: «Самое интересное — связать заказы и клиентов. Нам нужно распечатать накладную, чтобы было понятно, кому везти товар.»
- Запрос: Объясните, что данные лежат в двух таблицах: в orders (номер заказа, дата, customerNumber) и в customers (название, адрес).
- Напишите JOIN:
```
SELECT orders.orderNumber, orders.orderDate, customers.customerName, customers.country
FROM orders
INNER JOIN customers ON orders.customerNumber = customers.customerNumber
WHERE orders.status = 'Shipped';
```
- Комментарий: «Это как ВПР в Excel, только мы сразу из двух таблиц данные тянем, и Excel не виснет на 10 тысяч строк.»
Заключение (5 мин): «Вот за 1,5 часа мы научились делать выгрузки, которые в 1С приходится заказывать у программистов или долго вымучивать в Excel. Разницу почувствовали? На следующем занятии научимся считать выручку и строить аналитические отчеты.»

Важное дополнение по настройке БД

В вашем скрипте для classicmodels есть одна особенность для первого урока. Таблицы создаются с помощью CREATE TABLE, но если вы будете показывать SELECT * FROM productlines, то увидите, что поля htmlDescription и image содержат NULL. Это нормально.

Для первого урока, чтобы сразу получить красивый результат, начните с таблиц, которые максимально понятны и хорошо заполнены:

customers (клиенты)
products (товары) — там много текста, это наглядно.
orders (заказы)

Это даст быстрый визуальный отклик и понимание, что «здесь есть данные».