CASE FILE · No. 001 · 1888
The Locked Drawer
AGENCY OFFICE, LONDON · DIFFICULTY: APPRENTICE
THE BRIEFING
Welcome to the agency. Before anyone hands you a murder, you get this. The petty cash tin lives in a drawer in the back office. It was full on Thursday morning and empty on Thursday afternoon. The caretaker locks the front door over the lunch hour, so at twenty past one the office should have been empty. Every person who works here signs the book on the way in and on the way out. That is the whole of your evidence. One table, twelve names, two times each. Find the person who was still inside at twenty past one.
THIS CASE TEACHES
WHERE. How to ask the database for only the rows that matter.
CLUES ON THE TABLE
- 01.The tin was emptied at twenty past one on 12 April 1888. That is 13:20.
- 02.Someone was inside at 13:20 if they signed in before it and signed out after it.
- 03.SELECT * FROM office_log; shows you every row. Add WHERE to keep only some of them.
- 04.Two conditions at once are joined with AND. Times compare like text: '08:25' is less than '13:20'.
- 05.Read the times closely. Two people left in the last five minutes before 13:20, and neither of them is your answer.
/ 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.
- entry_idINTone row per person, for this one day
- nameVARCHAR(60)
- roleVARCHAR(40)
- signed_in_atDATETIMEwhen they arrived
- signed_out_atDATETIMEwhen they left
/ THE QUERY TERMINAL
Run your queries
Use SQL to explore the data. Each table holds part of the answer.
/ NOTES
Your notepad
Write down anything useful as you go.
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 Pawnbroker's Window →"A silver case walked out of a locked cabinet. Four days later it was in a pawnbroker's window."