Bookings database: airline schema, tables and SQL exercises

Bookings is the PostgreSQL demo database of an airline: flights between 104 airports, bookings, tickets and boarding passes. On SQLtest.online you can query it right in your browser: solve exercises with automatic checking and run your own queries in the playground, with nothing to install.

• 8 tables and 4 views
• 33,121 flights
• 1,045,726 tickets × flights
• 52 SQL exercises

What is Bookings

Bookings is the demo database that Postgres Professional publishes for learning PostgreSQL. It models the flights of a Russian airline: routes, aircraft and their seat maps, bookings, tickets and boarding passes.

This copy holds flights from July to September 2017. Airport and aircraft names are stored as JSONB in English and Russian, and airport coordinates use the PostgreSQL point type, so the database is good for practicing PostgreSQL-specific SQL as well as JOINs and analytics on large tables.

ER diagram

The diagram shows the Bookings tables and the foreign keys between them. Click it to open the full-size version.

What's inside

The tables fall into two groups.

The key thing to remember: one booking can include several passengers, and one ticket can cover several flights. The link between tickets and flights is ticket_flights, the largest table. The views aircrafts, airports, flights_v and routes show the same data in a friendlier form.

How much data the tables hold:

Table structure

Click a table to see its columns, a sample row and its keys.

Sample queries

These queries show how the data is connected. Copy any of them and run it in the playground.

A ticket and its booking: the passenger and the booking amount.

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;

A flight and its aircraft: reading an English name from a JSONB column with ->>.

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;

Fare and seat: from a ticket segment to its flight and boarding pass. The LEFT JOIN keeps segments without a boarding pass.

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 exercises by topic

There are 52 exercises on the Bookings database, from simple lookups to analytics over a million rows. Solutions are checked automatically on a real PostgreSQL server. The number on the right is how many exercises a topic has; the colored dots show its difficulty range.

• Calculations 1
• Analytical queries 5

Where to start

The first exercises on the Bookings database:

1. Get airports data
2. Airports List
3. Long-Range Aircrafts
4. Find Boeing aircraft
5. Flights Departed from Domodedovo
6. List Aircraft from Domodedovo
7. Get Bookings by Date
8. Aircraft usage analysis
9. Fare Conditions Types
10. Aircraft Lacking Business Class Seats

All Bookings exercises →