SQL тренажер: Найдите самый быстрый перелёт

SQL код скопирован в буфер обмена

Отточите свои навыки SQL с помощью наших интерактивных упражнений!
Практикуйте широкий спектр SQL-задач, от базовых запросов до сложных техник. Получайте мгновенную обратную связь на свои решения, чтобы учиться на ошибках и совершенствовать свои навыки. Увеличьте свою уверенность для собеседований и реальных приложений SQL.

Задание 21:

Найдите самый быстрый вариант перелёта между аэропортами Pulkovo Airport (LED) и Bryansk Airport (BZK) (с одной пересадкой) на дату 16 августа 2017 года. Для расчётов времени используйте поля scheduled_departure и scheduled_arrival
Выведите таблицу с полями:
    flight1_no - код первого полёта, 
    departure_airport - код аэропорта вылета, 
    connection_airport - код аэропорта пересадки,
    flight1_time - время первого полёта,
    connection_time - время ожидания,
    flight2_no - код второго полёта,
    arrival_airport - код аэропорта,
    flight1_time - время первого полёта,
    total_trip_time - полное время путешествия.

Напишите ваш запрос в поле ниже и нажмите кнопку "Проверить!"

Для написания ответа используйте синтаксис PostgreSQL. Описания таблиц приведены в правой панели.

Откройте для себя более 290 уникальных заданий.

Войдите, чтобы сохранить свой прогресс.

База данных Bookings (PostgreSQL)

Предметной областью этой базы данных являются рейсы авиакомпаний через различные аэропорты.

ER диаграмма базы данных Bookings

База данных Bookings содержит 8 таблиц:

aircrafts_data - таблица самолетов.
airports_data - таблица аэропортов.
boarding_passes - таблица посадочных талонов.
bookings - таблица бронирований.
flights - таблица рейсов.
seats - таблица мест в самолетах.
ticket_flights - таблица связей билетов с рейсами.
tickets - таблица билетов.

Таблица aircrafts_data

Колонки:

aircraft_code – Уникальный код для каждого самолета.
model — Название модели самолета на английском и русском языках в формате JSON.
range – Дальность полета самолета в километрах.

	aircraft_code	model	range
1	773	{"en": "Boeing 777-300", "ru": "Боинг 777-300"}	11100

Индексы:

PRIMARY KEY, btree (aircraft_code)

Таблица airports_data

Колонки:

airport_code – Уникальный код для каждого аэропорта.
airport_name — Название аэропорта на английском и русском языках в формате JSON.
city — Город аэропорта на английском и русском языках в формате JSON.
coordinates – Координаты аэропорта в виде POINT(долгота, широта).
timezone – Название часового пояса аэропорта.

	airport_code	airport_name	city	coordinates	timezone
1	YKS	{"en": "Yakutsk Airport", "ru": "Якутск"}	{"en": "Yakutsk", "ru": "Якутск"}	(129.77099609375,62.0932998657227)	Asia/Yakutsk

Индексы:

PRIMARY KEY, btree (airport_code)

Таблица boarding_passes

Колонки:

ticket_no - Номер билета.
flight_id - Идентификатор рейса.
boarding_no - Номер посадочного талона.
seat_no - Номер места.

	ticket_no	flight_id	boarding_no	seat_no
1	0005435212351	30625	1	2D

Индексы:

PRIMARY KEY, btree (ticket_no, flight_id)
UNIQUE CONSTRAINT, btree (flight_id, boarding_no)
UNIQUE CONSTRAINT, btree (flight_id, seat_no)

Ограничения внешнего ключа:

FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)

Таблица bookings

Колонки:

book_ref – Номер бронирования.
book_date – Дата бронирования.
total_amount – Общая стоимость бронирования.

	book_ref	book_date	total_amount
1	00000F	2017-07-05 00:12:00+00	265700.00

Индексы:

PRIMARY KEY, btree (book_ref)

Таблица flights

Колонки:

flight_id – Идентификатор рейса.
flight_no – Номер рейса.
scheduled_departure – Запланированное время отправления.
scheduled_arrival – Запланированное время прибытия.
departure_airport – Аэропорт вылета.
arrival_airport – Аэропорт прибытия.
status – Статус рейса.
aircraft_code – Код самолета, IATA.
actual_departure – Фактическое время отправления.
actual_arrival – Фактическое время прибытия.

	flight_id	flight_no	scheduled_departure	scheduled_arrival	departure_airport	arrival_airport	status	aircraft_code	actual_departure	actual_arrival
1	1185	PG0134	2017-09-10 06:50:00+00	2017-09-10 11:55:00+00	DME	BTK	Scheduled	319

Индексы:

PRIMARY KEY, btree (flight_id)
UNIQUE CONSTRAINT, btree (flight_no, scheduled_departure)

Таблица seats

Колонки:

aircraft_code – Код самолета, IATA.
seat_no – Номер места.
fare_conditions – Класс путешествия.

	aircraft_code	seat_no	fare_conditions
1	319	2A	Business

Индексы:

PRIMARY KEY, btree (aircraft_code, seat_no)

Ограничения внешнего ключа:

FOREIGN KEY (aircraft_code) REFERENCES aircrafts(aircraft_code) ON DELETE CASCADE

Таблица ticket_flights

Колонки:

ticket_no – Номер билета.
flight_id – Идентификатор рейса.
fare_conditions – Класс путешествия.
сумма – Стоимость поездки.

	ticket_no	flight_id	fare_conditions	amount
1	0005432159776	30625	Business	42100.00

Индексы:

PRIMARY KEY, btree (ticket_no, flight_id)

Ограничения внешнего ключа:

FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

Таблица tickets

Колонки:

ticket_no – Номер билета.
book_ref – Номер бронирования.
passenger_id – Идентификатор пассажира.
passenger_name – Имя пассажира.
contact_data – Контактная информация пассажира.

	ticket_no	book_ref	passenger_id	passenger_name	contact_data
1	0005432000987	06B046	8149 604011	VALERIY TIKHONOV	{"phone": "+70127117011"}

Индексы:

PRIMARY KEY, btree (ticket_no)

Ограничения внешнего ключа:

FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

SQLtest: задачи по SQL

База данных Bookings (PostgreSQL)

Таблица aircrafts_data

Таблица airports_data

Таблица boarding_passes

Таблица bookings

Таблица flights

Таблица seats

Таблица ticket_flights

Таблица tickets