The Blue Lantern Affair

WHITECHAPEL, LONDON · DIFFICULTY: MISDEMEANOR

THE BRIEFING

Elias Rowe owns an antique shop in Whitechapel. He reported a break-in just after midnight. The front window was smashed, the till was open, and silver was spread across the floor. But nothing valuable was taken. The only thing missing was a small blue lantern worth £75, and it came from a locked cabinet that was never forced open. Start with what the police picked up at the scene. Most of it belongs to the shop. One thing does not, and it has a number on it. Follow that number. It leads you to another record, and that one leads to the next.

CLUES ON THE TABLE

01.The break-in was reported just after midnight, on the night of 14 June 1891.
02.Nothing valuable was taken. The only thing missing is the blue lantern, worth £75.
03.Everything the police picked up is listed in scene_evidence. Start there.
04.One thing found at the scene does not belong to the shop, and it has a number on it.
05.Rowe writes down the time each visitor arrives and the time they leave.

/ THE EVIDENCE

Database Schema

These are the tables you can query. The lines are the keys that join them. Hover a table to see what it connects to.

scene_evidence4 COL

evidence_idINT
item_foundVARCHAR(150)
found_locationVARCHAR(150)
descriptionVARCHAR(300)

repair_tickets5 COL

ticket_noINT
item_leftVARCHAR(150)
date_leftDATE
date_collectedDATE
NULL if not picked up
account_codeVARCHAR(20)
→ shop_accounts.account_code

shop_accounts5 COL

account_idINT
account_codeVARCHAR(20)
account_nameVARCHAR(150)
suspect_idINT
→ suspects.suspect_id
opened_onDATE

suspects4 COL

suspect_idINT
nameVARCHAR(100)
occupationVARCHAR(100)
relationship_to_ownerVARCHAR(200)

visitor_log5 COL

visit_idINT
suspect_idINT
→ suspects.suspect_id
visit_reasonVARCHAR(200)
arrival_timeTIMESTAMP
departure_timeTIMESTAMP
NULL if never written down

private_offers6 COL

offer_idINT
suspect_idINT
→ suspects.suspect_id
item_nameVARCHAR(150)
offer_amountDECIMAL(10,2)
offer_dateDATE
offer_statusVARCHAR(50)

JOINS TO ANOTHER TABLEJOINED FROM ANOTHER TABLE

/ THE QUERY TERMINAL

Run your queries

Use SQL to explore the data. Each table holds part of the answer.

query.sql · 0 queries

or Ctrl+Enter

/ NOTES

Your notepad

Write down anything useful as you go.

NOTES

Your notes and your last query are kept in this browser.

/ THE ACCUSATION

Name your suspect

Type the suspect's full name and submit.

NEXT CASE

The Stolen Encore →

"At 9:50 the band was losing. Ten minutes later they had won, and the one-vote-per-phone rule had been switched off."