Задача # 6. Разбор задачи по SQL: Определение авиакомпаний, эксплуатирующих Boeing

SQL — один из ключевых навыков для аналитиков, разработчиков и инженеров данных. На собеседованиях часто дают практические задачи для проверки понимания языка. Разберём типовые задачи и их решения.

Предыдущее задание:

Задача 6. Определение авиакомпаний, эксплуатирующих Boeing

Постановка задачи

Требуется вывести уникальные названия авиакомпаний, которые совершали перелёты на самолётах марки Boeing. Результирующая таблица должна содержать одно поле:

• name — название авиакомпании

Анализ структуры базы данных

Для решения задачи нам понадобятся две таблицы:

1. Trip — содержит информацию о рейсах, включая:
   id — идентификатор рейса
   company — идентификатор авиакомпании (внешний ключ)
   plane — модель самолёта (нас интересует значение 'Boeing')
2. company — содержит информацию об авиакомпаниях:
   id — идентификатор компании (первичный ключ)
   name — название компании (нужен для вывода)

Детальный разбор решения

SQL-запрос

SELECT DISTINCT
c.name
FROM Trip t
JOIN company c ON c.id = t.company
WHERE t.plane = 'Boeing';

Пошаговое объяснение:

1. FROM Trip t — начинаем с таблицы рейсов, назначаем ей алиас 't'
2. JOIN company c ON c.id = t.company — соединяем с таблицей компаний по ключу
3. WHERE t.plane = 'Boeing' — фильтруем только рейсы на Boeing
4. SELECT DISTINCT c.name — выбираем только уникальные названия компаний

Ключевые аспекты решения

1. Использование JOIN

Задача требует соединения двух таблиц:

• Таблица Trip содержит информацию о самолётах
• Таблица Company содержит названия авиакомпаний
• Соединение происходит по полю company в trip и id в company

2. Применение DISTINCT

Оператор DISTINCT необходим, чтобы:

• Исключить дублирование названий компаний
• Учесть, что одна компания может совершать множество рейсов на Boeing
• Получить только уникальные значения

3. Условие фильтрации

Важные моменты фильтрации:

• Сравнение plane = 'Boeing' должно быть точным
• В реальных базах могут быть варианты: 'Boeing 737', 'Boeing-747' и т.д.
• При необходимости можно использовать LIKE 'Boeing%'

Возможные модификации запроса

1. Регистронезависимый поиск

Если есть сомнения в регистре:

SELECT DISTINCT c.name
FROM trip t
JOIN company c ON c.id = t.company
WHERE LOWER(t.plane) LIKE 'boeing%';

2. Подсчёт количества рейсов

Если нужно узнать интенсивность использования:

SELECT c.name, COUNT(*) as flights_count
FROM trip t
JOIN company c ON c.id = t.company
WHERE t.plane LIKE 'Boeing%'
GROUP BY c.name
ORDER BY flights_count DESC;

3. Включение разных моделей Boeing

Для учёта всех модификаций:

SELECT DISTINCT c.name
FROM trip t
JOIN company c ON c.id = t.company
WHERE t.plane LIKE 'Boeing%';

Оптимизация запроса

Для улучшения производительности:

1. Создать индекс на поле plane в таблице Trip
2. Создать индекс на поле company в таблице Trip
3. Создать индекс на поле id в таблице company

Частые ошибки

1. Забывают указать DISTINCT, получая дубли
2. Неправильно указывают условие соединения таблиц
3. Путают порядок таблиц в JOIN

Заключение

Данная задача демонстрирует:

1. Базовый принцип соединения таблиц
2. Важность устранения дубликатов
3. Особенности фильтрации строковых значений

🔑 Итоговое решение:

SELECT DISTINCT
c.name
FROM trip t
JOIN company c ON c.id = t.company
WHERE t.plane = 'Boeing';