CASE FILE · No. 002 · 1888
The Pawnbroker's Window
CLERKENWELL, LONDON · DIFFICULTY: APPRENTICE
THE BRIEFING
A silver cigarette case was taken from a locked cabinet in a house on Amwell Street on the night of 3 May. It is an unusual piece and worth more than anything else on the street. The pawnbroker two roads over keeps a pledge book: what was brought in, by whom, when, and what he paid for it. He has handed it over without much complaint. Whoever pawned that case was paid more than the broker paid for anything else after the theft. Sort his book by the money and look at the top. One warning. There is a larger sum in the book than the one you want, and it has nothing to do with this.
THIS CASE TEACHES
ORDER BY and LIMIT. How to sort rows and take only the top one.
CLUES ON THE TABLE
- 01.The theft was on the night of 3 May 1888. Anything pledged before that cannot be the stolen case.
- 02.ORDER BY amount_paid puts the smallest first. ORDER BY amount_paid DESC puts the largest first.
- 03.LIMIT 1 keeps only the first row of whatever the sorting produced.
- 04.Sorting the whole book without a WHERE gives you the largest pledge of the month, not of the days that matter.
/ 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.
- ticket_noINT
- itemVARCHAR(80)
- pledged_byVARCHAR(60)
- pledged_atDATETIME
- amount_paidDECIMAL(6,2)what the broker handed over, in pounds
/ 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 Laundry Mark →"They left a coat behind. The coat does not carry a name. It carries a number."