База данных Bookings: авиаперевозки, схема, таблицы и SQL-задачи

Bookings — демонстрационная база PostgreSQL об авиакомпании: рейсы между 104 аэропортами, бронирования, билеты и посадочные талоны. На SQLtest.online с ней можно работать прямо в браузере: решать задачи с автоматической проверкой и писать свои запросы в песочнице, ничего не устанавливая.

• 8 таблиц и 4 представления
• 33 121 рейс
• 1 045 726 перелётов по билетам
• 52 SQL-задачи

Что такое Bookings

Bookings — демобаза, которую компания Postgres Professional выпускает для изучения PostgreSQL. Она описывает рейсы российской авиакомпании: маршруты, самолёты и схемы их салонов, бронирования, билеты и посадочные талоны.

В этой копии рейсы с июля по сентябрь 2017 года. Названия аэропортов и самолётов хранятся в JSONB на английском и русском, а координаты аэропортов — в типе point. Поэтому на базе удобно тренировать и особенности PostgreSQL, и JOIN, и аналитику на больших таблицах.

ER-диаграмма

Диаграмма показывает таблицы Bookings и связи между ними по внешним ключам. Нажмите, чтобы открыть её в полном размере.

Из чего состоит база

Таблицы делятся на две группы.

Главное, что стоит запомнить: в одном бронировании может быть несколько пассажиров, а один билет может включать несколько перелётов. Билеты и рейсы связывает ticket_flights — самая большая таблица. Представления aircrafts, airports, flights_v и routes показывают те же данные в более удобном виде.

Сколько данных в таблицах:

Структура таблиц

Нажмите на таблицу, чтобы увидеть её столбцы, пример строки и ключи.

Примеры запросов

Эти запросы показывают, как связаны данные. Скопируйте любой и запустите в песочнице.

Билет и его бронирование — пассажир и сумма бронирования:

SELECT t.ticket_no, t.passenger_name, b.book_date, b.total_amount
FROM tickets t
JOIN bookings b ON b.book_ref = t.book_ref
ORDER BY t.ticket_no
LIMIT 3;

Рейс и самолёт — английское название из JSONB-столбца через ->>:

SELECT f.flight_no, f.departure_airport, f.arrival_airport,
       a.model ->> 'en' AS aircraft
FROM flights f
JOIN aircrafts_data a ON a.aircraft_code = f.aircraft_code
ORDER BY f.flight_id
LIMIT 3;

Тариф и место — от перелёта по билету к рейсу и посадочному талону. LEFT JOIN сохраняет перелёты без талона:

SELECT tf.ticket_no, f.flight_no, tf.fare_conditions, tf.amount, bp.seat_no
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
LEFT JOIN boarding_passes bp
       ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
ORDER BY tf.ticket_no, f.flight_id
LIMIT 3;

SQL-задачи по темам

Задач на базе Bookings: 52 — от простых выборок до аналитики по миллиону строк. Решение проверяется автоматически на настоящем PostgreSQL. Число справа — количество задач в теме, цветные метки — диапазон сложности.

• Вычисления 1
• Аналитические запросы 5

С чего начать

Первые задачи по базе Bookings:

1. Получить данные аэропортов
2. Список аэропортов
3. Дальнемагистральные самолеты
4. Список самолетов Boeing
5. Список рейсов из Домодедово
6. Список самолётов из Домодедово
7. Получить бронирования по дате
8. Анализ использования самолётов
9. Типы тарифов
10. Самолеты без Бизнес-класса

Все задачи по Bookings →