Current Status Labels for Train PNRs

Easy
527.

Current Status Labels for Train PNRs

A rail reservation is in exactly one of three states: confirmed (it has a coach and a berth_no), RAC (it has an rac_no) or waitlisted (it has a wl_no). The other state columns are NULL.

Return pnr, passenger_name and current_status, where current_status reads like the enquiry screen: CNF/<coach>/<berth_no> for a confirmed berth (for example CNF/S4/32), RAC <rac_no> (for example RAC 7) or WL <wl_no> (for example WL 23). Return the rows in any order.

Tables

Table: Reservation

ColumnType
pnrbigint
passenger_namevarchar
train_noint
coachvarchar
berth_noint
rac_noint
wl_noint

Primary key: pnr.

One passenger per PNR. Exactly one of (coach and berth_no), rac_no or wl_no is filled in.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Reservation

pnrpassenger_nametrain_nocoachberth_norac_nowl_no
4521876390Kavya12627S432NULLNULL
4521876391Arjun12627NULLNULL7NULL
6203914875Neha12951NULLNULLNULL23
6203914880Harsh12951B25NULLNULL
2876104553Simran16345NULLNULLNULL1

Output

pnrpassenger_namecurrent_status
2876104553SimranWL 1
4521876390KavyaCNF/S4/32
4521876391ArjunRAC 7
6203914875NehaWL 23
6203914880HarshCNF/B2/5
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Current Status Labels for Train PNRs — SQL Strings Problem

A rail reservation is in exactly one of three states: confirmed (it has a coach and a berth_no), RAC (it has an rac_no) or waitlisted (it has a wl_no).

  • Difficulty: Easy
  • Topics: Strings, Conditional Logic
  • Dialect: MySQL
  • Problem: #527

Problem statement

A rail reservation is in exactly one of three states: confirmed (it has a coach and a berth_no), RAC (it has an rac_no) or waitlisted (it has a wl_no). The other state columns are NULL.

Return pnr, passenger_name and current_status, where current_status reads like the enquiry screen: CNF/<coach>/<berth_no> for a confirmed berth (for example CNF/S4/32), RAC <rac_no> (for example RAC 7) or WL <wl_no> (for example WL 23). Return the rows in any order.

Tables

Table: Reservation

ColumnType
pnrbigint
passenger_namevarchar
train_noint
coachvarchar
berth_noint
rac_noint
wl_noint

Primary key: pnr.

One passenger per PNR. Exactly one of (coach and berth_no), rac_no or wl_no is filled in.

Examples

Example 1

Reservation

pnrpassenger_nametrain_nocoachberth_norac_nowl_no
4521876390Kavya12627S432NULLNULL
4521876391Arjun12627NULLNULL7NULL
6203914875Neha12951NULLNULLNULL23
6203914880Harsh12951B25NULLNULL
2876104553Simran16345NULLNULLNULL1

Output

pnrpassenger_namecurrent_status
2876104553SimranWL 1
4521876390KavyaCNF/S4/32
4521876391ArjunRAC 7
6203914875NehaWL 23
6203914880HarshCNF/B2/5

How to solve Current Status Labels for Train PNRs

Each row needs a different label depending on which state columns are filled, so the natural shape is a CASE with one branch per state: a non-NULL coach means a confirmed berth, otherwise a non-NULL rac_no means RAC, and otherwise the passenger is on the waitlist. Inside each branch CONCAT builds the text, turning the numbers into strings on the way.

There is a neat trick hiding in MySQL's CONCAT rule: it returns NULL if any argument is NULL. So CONCAT('CNF/', coach, '/', berth_no) is NULL for every row that is not confirmed, CONCAT('RAC ', rac_no) is NULL unless the row is in RAC, and COALESCE over the three picks the one that exists — the first alternative.

CONCAT_WS (concat with separator) is a third spelling. Every form reads each row once; nothing is grouped or joined.

Reference solution (MySQL)

SELECT pnr, passenger_name,
       CASE
         WHEN coach IS NOT NULL THEN CONCAT('CNF/', coach, '/', berth_no)
         WHEN rac_no IS NOT NULL THEN CONCAT('RAC ', rac_no)
         ELSE CONCAT('WL ', wl_no)
       END AS current_status
FROM Reservation

Another way

SELECT pnr, passenger_name, COALESCE(CONCAT('CNF/', coach, '/', berth_no), CONCAT('RAC ', rac_no), CONCAT('WL ', wl_no)) AS current_status FROM Reservation

Another way

SELECT pnr, passenger_name, IF(coach IS NULL, IF(rac_no IS NULL, CONCAT_WS(' ', 'WL', wl_no), CONCAT_WS(' ', 'RAC', rac_no)), CONCAT_WS('/', 'CNF', coach, berth_no)) AS current_status FROM Reservation

← Flight Bookings by Sales Channel · Hotel Loyalty Members per Email Domain →