Case 5: Evil Under the Sun

At a luxurious seaside resort on Smugglers’ Island, the sun-soaked holiday is shattered when Arlena Marshall—a glamorous and flirtatious former actress—is found dead around noon in a secluded cove. She is discovered around 11:45 a.m. by fellow guests Patrick and Emily, lying lifeless in a cove where she had gone alone earlier that morning.

Her presence had stirred tension among the other guests, many of whom had motive to wish her harm.

Your mission: Use SQL to reconstruct the sequence of events, verify alibi details and uncover who murdered Arlena Marshall at the resort.

About this case

Based on Evil Under the Sun (Agatha Christie, 1941). At an exclusive seaside resort, a glamorous woman is found murdered in a secluded cove during the sunny afternoon. Every guest had the opportunity—and several had motive to wish her dead.

What you'll practise

This case challenges your growing SQL skills:

WHERE with strict AND conditions — to verify tight alibi windows
ORDER BY — to sequence movements minute by minute
Time-based filtering — pinpointing exactly when the murder occurred
Multi-condition logic — eliminating suspects based on their location at specific times

The database

You'll query the suspect_info table, tracking each guest's stated movements throughout the day, their relationships to the victim, and any suspicious gaps in their alibis. Every detail matters at the resort.

Difficulty

Intermediate. Requires careful attention to time-based filtering and the ability to spot inconsistencies in guest statements. Alibis are deliberately tight and tricky to verify.