Case 14: The Murder at the Vicarage

Colonel Lucius Protheroe, the unpopular churchwarden, is found shot dead in the vicar's study in St. Mary Mead. The vicar had joked at dinner that anyone who murdered Protheroe would be doing the world a favor—and someone did.

Both Lawrence Redding (a young artist) and Anne Protheroe (the Colonel's wife) confess to the crime. Miss Marple watches the village and the evidence to find who really killed the Colonel.

Your mission: Use SQL to meet the suspects, see who was at the vicarage, identify the artist, trace who confessed, and reveal who killed Colonel Protheroe.

About this case

Based on The Murder at the Vicarage (Agatha Christie, 1930). In the quiet village of St. Mary Mead, a hated churchwarden is shot dead in the vicar's study. Two people confess to the murder—but Miss Marple knows that village secrets run deeper than anyone is admitting.

What you'll practise

This classic case builds intermediate SQL skills:

SELECT + WHERE — to identify who was at the vicarage
ORDER BY — to build a timeline of events in the village
Multiple AND conditions — to distinguish alibis from the truth
Confession analysis — understanding why people lie and what the truth really is

The database

You'll query the suspect_info table, tracking each suspect's relationship to Colonel Protheroe, their stated location at the time of death, and the web of village gossip and secrets.

Difficulty

Intermediate. Miss Marple teaches us that small-town secrets matter. Requires careful analysis of who benefits from Protheroe's death and who would sacrifice themselves for a secret.