← К оглавлению занятия

Занятие 31. Практикум 8

📁 Блок: SQL / MySQL / Практикум ⏱️ Время изучения: ~90 мин 🎯 Сложность: Средняя

⚡ Кратко: суть темы

Повторяем фильтрацию, JOIN, агрегацию и оконные функции на трёх учебных базах.

  • hr.employees: WHERE, LIKE, CASE, AVG.
  • world: JOIN стран с городами и языками.
  • Airlines: GROUP BY, COUNT, RANK() OVER.

Что запомнить: сначала фильтруем строки, потом соединяем, группируем и сортируем.

Частая ошибка: пытаться отфильтровать агрегат через WHERE вместо HAVING.

📖 Что повторяем

На этом занятии не появляется новой теории — мы отрабатываем уже пройденные операторы на практических задачах. Главное — чётко понимать порядок работы SQL-запроса и правильно выбирать базу данных перед выполнением запроса.

1. База данных hr, таблица employees

Таблица employees содержит информацию о сотрудниках: имя, фамилию, зарплату, отдел и другие поля. В практикуме используются:

  • first_name, last_name — имя и фамилия.
  • salary — зарплата.
  • department_id — идентификатор отдела.

Повторяемые темы:

  • Фильтрация числовых значений: WHERE salary > 5000.
  • Фильтрация по точному совпадению: WHERE department_id = 90.
  • Поиск по шаблону: WHERE last_name LIKE 'L%'.
  • Условное поле через CASE: CASE WHEN salary > 10000 THEN 1 ELSE 0 END AS salary_group.
  • Агрегатная функция AVG(salary) с фильтром.

2. База данных world

Классическая учебная база world состоит из таблиц стран, городов и языков. В практикуме используются:

  • country — страны; ключевые поля Code, Name, Capital.
  • city — города; ключевые поля ID, Name, CountryCode.
  • countrylanguage — языки стран; ключевые поля CountryCode, Language, IsOfficial.

Повторяемые темы:

  • JOIN для получения названия столицы: country.Capital = city.ID.
  • JOIN для списка языков по странам.
  • Фильтр официальных языков: WHERE IsOfficial = 'T'.
  • Сравнение количества записей через COUNT(*).

3. База данных Airlines

Учебная база авиаперелётов. Основные таблицы:

  • aircraft — модели самолётов; поле model.
  • trips — рейсы; поля id, plane (модель самолёта).
  • tickets — билеты; поля id, trip_id, service_class, price.

Повторяемые темы:

  • Подсчёт количества через COUNT(*) с группировкой GROUP BY.
  • Среднее значение через AVG(price).
  • Соединение trips и tickets для подсчёта билетов на рейс.
  • Сортировка по агрегату: ORDER BY COUNT(*) DESC.
  • Оконная функция RANK() OVER (ORDER BY ...) для ранжирования рейсов.