Mini project: railway reservation queries

60 min · Intermediate

A railway reservation system is a set of linked tables: a booking points to a passenger, a train and two stations, and a train's route links it to every station it stops at. Every useful screen, from seat availability to the journey on your ticket, is a join across them.

What you'll build

The sample database is loaded for you from data/shop.sql: five tables (rail_stations, rail_trains, rail_routes, rail_passengers, rail_bookings) with keys between them, and sample bookings for 20 and 21 December 2025, including confirmed seats, a waiting list and cancellations. The data is simplified sample data, not a real timetable: each train has one class and only a few seats.

Your queries.sql answers six questions with joins: the route of train 12007 stop by stop, seat availability for every train on 20 December 2025, the waiting list of train 12007 on that date, a journey report that joins seven tables (stations and routes twice each), the trains with no live booking on 20 December 2025, and the stations where no train stops.

Sample run

+---------+-----------------+----------+-----------+
| stop_no | station         | arrival  | departure |
+---------+-----------------+----------+-----------+
|       1 | Chennai Central | NULL     | 06:00:00  |
|       2 | KSR Bengaluru   | 10:55:00 | 11:00:00  |
|       3 | Mysuru Junction | 13:00:00 | NULL      |
+---------+-----------------+----------+-----------+
+----------+-------------------+-------+-----------+---------+-----------+
| train_no | name              | seats | confirmed | waiting | available |
+----------+-------------------+-------+-----------+---------+-----------+
|    12007 | Shatabdi Express  |     3 |         3 |       2 |         0 |
|    12123 | Deccan Queen      |     3 |         0 |       0 |         3 |
|    12627 | Karnataka Express |     4 |         2 |       0 |         2 |
|    12759 | Charminar Express |     2 |         1 |       0 |         1 |
|    22691 | Rajdhani Express  |     3 |         0 |       0 |         3 |
+----------+-------------------+-------+-----------+---------+-----------+
+-------+------+----------+-----+
| wl_no | pnr  | name     | age |
+-------+------+----------+-----+
|     1 | 8104 | Deepak S |  45 |
|     2 | 8105 | Esha P   |  19 |
+-------+------+----------+-----+
+------+------------+-------------------+--------------+-----------+----------+-----------+----------+-------+
| pnr  | passenger  | train             | journey_date | from_city | departs  | to_city   | arrives  | seat  |
+------+------------+-------------------+--------------+-----------+----------+-----------+----------+-------+
| 8101 | Ananya Rao | Shatabdi Express  | 2025-12-20   | Chennai   | 06:00:00 | Bengaluru | 10:55:00 | C1-01 |
| 8102 | Bharath K  | Shatabdi Express  | 2025-12-20   | Chennai   | 06:00:00 | Mysuru    | 13:00:00 | C1-02 |
| 8103 | Chitra M   | Shatabdi Express  | 2025-12-20   | Bengaluru | 11:00:00 | Mysuru    | 13:00:00 | C1-03 |
| 8104 | Deepak S   | Shatabdi Express  | 2025-12-20   | Chennai   | 06:00:00 | Mysuru    | 13:00:00 | WL 1  |
| 8105 | Esha P     | Shatabdi Express  | 2025-12-20   | Chennai   | 06:00:00 | Bengaluru | 10:55:00 | WL 2  |
| 8107 | Gowri N    | Karnataka Express | 2025-12-20   | Bengaluru | 19:20:00 | Delhi     | 09:00:00 | S1-11 |
| 8108 | Harsh V    | Karnataka Express | 2025-12-20   | Bengaluru | 19:20:00 | Delhi     | 09:00:00 | S1-12 |
| 8109 | Ananya Rao | Charminar Express | 2025-12-20   | Chennai   | 18:10:00 | Hyderabad | 06:30:00 | S2-04 |
| 8111 | Farhan A   | Deccan Queen      | 2025-12-21   | Mumbai    | 17:10:00 | Pune      | 20:25:00 | D1-07 |
+------+------------+-------------------+--------------+-----------+----------+-----------+----------+-------+
+----------+------------------+
| train_no | name             |
+----------+------------------+
|    12123 | Deccan Queen     |
|    22691 | Rajdhani Express |
+----------+------------------+
+--------------+--------------------+
| station_code | name               |
+--------------+--------------------+
| ERS          | Ernakulam Junction |
+--------------+--------------------+

Requirements

Your program should do all of these.

queries.sql contains only the six queries below, in this order, each ending with a semicolon. The rail_ tables and rows already exist (they come from data/shop.sql), so do not create or insert anything.
Query 1, the route of train 12007: columns stop_no, station (the station name), arrival, departure, sorted by stop_no.
Query 2, availability on 2025-12-20 for every train, including trains with no bookings: columns train_no, name, seats, confirmed, waiting, available (seats minus confirmed), sorted by train_no. Cancelled bookings are counted in neither column.
Query 3, the waiting list of train 12007 on 2025-12-20: columns wl_no, pnr, name, age, sorted by wl_no.
Query 4, the journey report of every live (not cancelled) booking: columns pnr, passenger, train, journey_date, from_city, departs (departure time at the boarding station), to_city, arrives (arrival time at the destination), seat (the seat, or WL followed by the waiting-list number), sorted by pnr.
Query 5, trains with no live booking on 2025-12-20: columns train_no, name, sorted by train_no.
Query 6, stations where no train stops: columns station_code, name, sorted by station_code.
Every query that returns several rows has an ORDER BY, so the output is the same on every run.

Build it step by step in the Build tab (5 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

What's next

You finished the preview of Joins

Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.

Create account I already have an account

Next step

Start building

5 milestones

What you'll build

Sample run

+---------+-----------------+----------+-----------+
| stop_no | station         | arrival  | departure |
+---------+-----------------+----------+-----------+
|       1 | Chennai Central | NULL     | 06:00:00  |
|       2 | KSR Bengaluru   | 10:55:00 | 11:00:00  |
|       3 | Mysuru Junction | 13:00:00 | NULL      |
+---------+-----------------+----------+-----------+
+----------+-------------------+-------+-----------+---------+-----------+
| train_no | name              | seats | confirmed | waiting | available |
+----------+-------------------+-------+-----------+---------+-----------+
|    12007 | Shatabdi Express  |     3 |         3 |       2 |         0 |
|    12123 | Deccan Queen      |     3 |         0 |       0 |         3 |
|    12627 | Karnataka Express |     4 |         2 |       0 |         2 |
|    12759 | Charminar Express |     2 |         1 |       0 |         1 |
|    22691 | Rajdhani Express  |     3 |         0 |       0 |         3 |
+----------+-------------------+-------+-----------+---------+-----------+
+-------+------+----------+-----+
| wl_no | pnr  | name     | age |
+-------+------+----------+-----+
|     1 | 8104 | Deepak S |  45 |
|     2 | 8105 | Esha P   |  19 |
+-------+------+----------+-----+
+------+------------+-------------------+--------------+-----------+----------+-----------+----------+-------+
| pnr  | passenger  | train             | journey_date | from_city | departs  | to_city   | arrives  | seat  |
+------+------------+-------------------+--------------+-----------+----------+-----------+----------+-------+
| 8101 | Ananya Rao | Shatabdi Express  | 2025-12-20   | Chennai   | 06:00:00 | Bengaluru | 10:55:00 | C1-01 |
| 8102 | Bharath K  | Shatabdi Express  | 2025-12-20   | Chennai   | 06:00:00 | Mysuru    | 13:00:00 | C1-02 |
| 8103 | Chitra M   | Shatabdi Express  | 2025-12-20   | Bengaluru | 11:00:00 | Mysuru    | 13:00:00 | C1-03 |
| 8104 | Deepak S   | Shatabdi Express  | 2025-12-20   | Chennai   | 06:00:00 | Mysuru    | 13:00:00 | WL 1  |
| 8105 | Esha P     | Shatabdi Express  | 2025-12-20   | Chennai   | 06:00:00 | Bengaluru | 10:55:00 | WL 2  |
| 8107 | Gowri N    | Karnataka Express | 2025-12-20   | Bengaluru | 19:20:00 | Delhi     | 09:00:00 | S1-11 |
| 8108 | Harsh V    | Karnataka Express | 2025-12-20   | Bengaluru | 19:20:00 | Delhi     | 09:00:00 | S1-12 |
| 8109 | Ananya Rao | Charminar Express | 2025-12-20   | Chennai   | 18:10:00 | Hyderabad | 06:30:00 | S2-04 |
| 8111 | Farhan A   | Deccan Queen      | 2025-12-21   | Mumbai    | 17:10:00 | Pune      | 20:25:00 | D1-07 |
+------+------------+-------------------+--------------+-----------+----------+-----------+----------+-------+
+----------+------------------+
| train_no | name             |
+----------+------------------+
|    12123 | Deccan Queen     |
|    22691 | Rajdhani Express |
+----------+------------------+
+--------------+--------------------+
| station_code | name               |
+--------------+--------------------+
| ERS          | Ernakulam Junction |
+--------------+--------------------+

Requirements

Your program should do all of these.

queries.sql contains only the six queries below, in this order, each ending with a semicolon. The rail_ tables and rows already exist (they come from data/shop.sql), so do not create or insert anything.

Query 1, the route of train 12007: columns stop_no, station (the station name), arrival, departure, sorted by stop_no.

Query 2, availability on 2025-12-20 for every train, including trains with no bookings: columns train_no, name, seats, confirmed, waiting, available (seats minus confirmed), sorted by train_no. Cancelled bookings are counted in neither column.

Query 3, the waiting list of train 12007 on 2025-12-20: columns wl_no, pnr, name, age, sorted by wl_no.

Query 4, the journey report of every live (not cancelled) booking: columns pnr, passenger, train, journey_date, from_city, departs (departure time at the boarding station), to_city, arrives (arrival time at the destination), seat (the seat, or WL followed by the waiting-list number), sorted by pnr.

Query 5, trains with no live booking on 2025-12-20: columns train_no, name, sorted by train_no.

Query 6, stations where no train stops: columns station_code, name, sorted by station_code.

Every query that returns several rows has an ORDER BY, so the output is the same on every run.