Case 4: Murder in Mesopotamia

At an archaeological dig in Mesopotamia, tensions run high among the team members living in close quarters. One afternoon, Louise Leidner—the beautiful and enigmatic wife of the expedition leader—is found dead in her bedroom, struck fatally on the head.

The window was barred and the only entrance was under constant watch, leaving the circumstances of her murder baffling.

Your mission: Use SQL to reconstruct the sequence of events, verify alibi details and uncover who murdered Louise Leidner at the dig site.

About this case

Based on Murder in Mesopotamia (Agatha Christie, 1936). At an archaeological dig in the desert, tensions boil over when the expedition leader's wife is found murdered in a locked room. With limited suspects and nowhere to hide, the killer must be among the team.

What you'll practise

This intermediate case continues building advanced skills:

WHERE with multiple AND conditions — to eliminate suspects systematically
ORDER BY — to sequence events and spot inconsistencies in alibis
Complex filtering logic — combining time-based and relationship-based conditions
Pattern recognition — spotting contradictions in testimony across multiple queries

The database

You'll query the suspect_info table, tracking each team member's role at the dig, their relationship to the victim, their stated movements, and suspicious details from their statements. The schema is shown on screen throughout.

Difficulty

Intermediate. Requires strong grasp of AND logic and the ability to combine multiple conditions to filter a larger dataset. Alibis are more complex than previous cases.