Case 2: The Murder of Roger Ackroyd

In the quiet village of King’s Abbot, the wealthy widower Roger Ackroyd was found dead in his study late last night. He had received a mysterious phone call just before bedtime and when his niece discovered him, he lay slumped over his desk—bludgeoned from behind.

A shattered telephone receiver lay on the floor. The study’s door and windows were locked from the inside and no footprints could be traced in the gardens outside.

Your mission: Use SQL to cross-check alibis, analyze the timeline of events and uncover the truth behind Roger Ackroyd’s murder.

About this case

Based on The Murder of Roger Ackroyd (Agatha Christie, 1926). A wealthy widower is found dead in a locked study, bludgeoned from behind. The phone receiver is shattered, the windows are sealed—yet someone got inside.

What you'll practise

This intermediate case builds on the basics. You'll use:

SELECT + WHERE — to filter suspects by motive and opportunity
ORDER BY — to reconstruct the timeline of events
Multiple AND conditions — to narrow down who had both motive and access
BETWEEN — to check alibis against time windows

The database

You'll query the suspect_info table, which contains detailed alibis, relationships to the victim, and suspicious details about each person in the household. The schema is shown on screen throughout—you never need to memorise it.

Difficulty

Beginner to Intermediate. Requires understanding of AND conditions and time-based filtering. A step up from Case 1, with more complex data to sort through.