Case 1: Murder on the Orient Express

The luxury train Orient Express is stranded in snow. In the morning, passenger Samuel Ratchett is found murdered in his cabin, stabbed 12 times during the night.

The doors and windows were locked from the inside. A conductor's button was found in the victim's compartment and footprints were seen outside.

Your mission: Use SQL to investigate the evidence, uncover contradictions in the alibis and identify the truth behind Ratchett’s death.

About this case

Based on Murder on the Orient Express (Agatha Christie, 1934). A wealthy American passenger is found stabbed twelve times aboard a snowbound luxury train. Every suspect has an alibi. Every alibi is suspicious.

What you'll practise

This is a beginner-friendly case. To solve it you'll use:

SELECT + WHERE — to filter suspects by case ID and retrieve specific columns
ORDER BY — to sort suspects and events chronologically
BETWEEN — to filter events within a specific time range
Multiple conditions with AND — to narrow down suspects by location and time

The database

You'll query 2 tables: suspect_info and events. The suspect_info table lists all passengers aboard the Orient Express—their names, roles, alibi locations, times, and relationships to the victim. The events table records actions taken during the night of the murder, tracking who did what and when.

The schema is shown on screen throughout—you never need to memorise it.

Difficulty

Beginner. Suitable if you know what SELECT * FROM table does. Questions progress from simple filtering to combining multiple conditions with AND and time-based filtering with BETWEEN.