Lab · SQL and Databases
Lab: a train search like IRCTC
The brief
Write the queries behind a train search screen: trains to New Delhi, cheap trains from two cities, Express trains with no First AC, the three cheapest options and the list of starting cities for a drop-down.
Why it matters
Every search box on IRCTC, a bus booking app or a shopping site turns into a SELECT with WHERE, ORDER BY and LIMIT behind the scenes. Getting the filter exactly right (brackets, NULL, LIKE) is the difference between a correct screen and an angry customer.
Skills you practise
- SELECT and aliases
- WHERE with AND, OR and brackets
- LIKE and IS NULL
- ORDER BY and LIMIT
- DISTINCT
What you will have at the end
Five saved queries (01-to-delhi.sql to 05-cities.sql) that power a train search screen, each one checked against the data.
How it is marked (rubric, 100 points)
| Correct resultsAll five checks pass, including the brackets in step 2 and IS NULL in step 3. | 50 |
|---|---|
| Clean queriesOnly the columns asked for, one clause per line, and clear aliases. | 20 |
| Understands the trapsYou can explain why AND needs brackets with OR, why = NULL finds nothing, and where LIMIT must go. | 30 |
0/5 steps done
Step 1 of 5
Trains to New Delhi
01-to-delhi.sql lists the train number, name and departure time of every train whose to_city is New Delhi.
The trains table has one row per train: train_no, name, from_city, to_city, departs, duration_min (journey time in minutes), fare_3a (AC 3 tier fare) and fare_1a (First AC fare, empty when the train has no First AC in this data). Run SELECT * FROM trains; first to see it.
Now pick only three columns, train_no, name and departs, and keep only the rows where to_city = 'New Delhi'. Text values go in single quotes and must match exactly, including the capital letters.
Run shows what your code prints. Check runs the step's tests. Ctrl+Enter checks too.