Train Name and Travel Year on Every Booking — SQL Easy Problem

Problem statement

A railway booking site stores each ticket with the number of the train it is for; the train's name lives in the Train table. Two different trains can share a name.

For every row of Booking, return the columns pnr, train_name, travel_year (the year of journey_date, as a number) and fare. Every booking's train is in Train; trains nobody booked do not appear. Return the rows in any order.

Tables

Table: Train

Primary key: train_no.

One row per train service.

Table: Booking

Primary key: pnr.

One row per ticket. fare is in rupees.

Examples

Example 1

Train

Booking

Output

How to solve Train Name and Travel Year on Every Booking

Each booking knows its train only by number, so the name has to be fetched from Train. An inner join on train_no puts every booking next to its train's row; because every booking's train exists, no booking is lost, and trains with no bookings simply never match anything.

Join on the key, never on a descriptive column. Here two different services are both called "Rajdhani Express" — joining or grouping by name would mix them up — while train_no identifies exactly one row of Train, so each booking produces exactly one output row.

The year comes from YEAR(journey_date), which returns a number. Taking the first four characters with LEFT and casting works too because dates are stored in a fixed YYYY-MM-DD form, but YEAR states the intent. Selecting the columns under the requested names finishes the query.

The comma join with the condition in WHERE is the older spelling of the same inner join, and a scalar subquery in the select list looks the name up per booking; with train_no as the primary key, all three are one index lookup per booking.

Reference solution (MySQL)

SELECT b.pnr, t.train_name, YEAR(b.journey_date) AS travel_year, b.fare
FROM Booking b
JOIN Train t ON t.train_no = b.train_no

Another way

SELECT b.pnr, t.train_name, CAST(LEFT(b.journey_date, 4) AS SIGNED) AS travel_year, b.fare FROM Booking b, Train t WHERE t.train_no = b.train_no

Another way

SELECT pnr, (SELECT train_name FROM Train t WHERE t.train_no = b.train_no) AS train_name, YEAR(journey_date) AS travel_year, fare FROM Booking b