Найти пилотов, летавших вторыми пилотами в New York в августе 2023

71. Найти вторых пилотов, летавших в New York в августе 2023

Условие задачи:
Даны таблицы pilots, planes и flights.

Необходимо вывести имена пилотов, которые в качестве второго пилота летали в New York в августе 2023 года.

Результат должен содержать только поле name, без дубликатов.

В описании таблицы flights отсутствует поле second_pilot_id, хотя оно используется в условии задачи. Будем считать, что это поле есть и ссылается на pilots.pilot_id.

Код:

pilots
- pilot_id (PK)
- name
- age

planes
- plane_id (PK)
- plane_name
- capacity
- cargo_flag

flights
- flight_id (PK)
- flight_date (DATE)
- plane_id -> planes.plane_id
- first_pilot_id -> pilots.pilot_id
- second_pilot_id -> pilots.pilot_id
- destination

Спойлеры к решению

Подсказки

💡 Соедини flights с pilots по second_pilot_id.
💡 Отфильтруй рейсы с destination = 'New York'.
💡 Для августа 2023 удобно использовать полуинтервал дат: от 2023-08-01 включительно до 2023-09-01 не включительно.
💡 Чтобы один пилот не выводился несколько раз, используй DISTINCT.

Решение

SELECT DISTINCT
    p.name
FROM flights f
JOIN pilots p
    ON p.pilot_id = f.second_pilot_id
WHERE f.destination = 'New York'
  AND f.flight_date >= DATE '2023-08-01'
  AND f.flight_date < DATE '2023-09-01';

Соединение:

JOIN pilots p
    ON p.pilot_id = f.second_pilot_id

позволяет получить данные именно о втором пилоте каждого рейса.

Диапазон:

f.flight_date >= DATE '2023-08-01'
AND f.flight_date < DATE '2023-09-01'

выбирает все рейсы за август 2023 года.

DISTINCT нужен, чтобы пилот, совершивший несколько подходящих рейсов, появился в результате только один раз.

Таблица planes для решения этой задачи не требуется, поскольку никаких условий по самолёту в запросе нет.