detectiveSQL
EST. 1888 · LONDON
CONFIDENTIAL · FOR THE EYES OF THE DETECTIVE ONLY
The city is full of dirty data.
Only a true detective can crack it.
Welcome to the agency, detective. The cases are filed below. Each one hides the truth inside a database, and the only way in is a well-written query. Pour yourself a drink. Light a cigarette. Let's get to work.
/ THE DOSSIER
Active Case Files
Apprentice
Start here if you have never written a query. Four short cases, one new idea each, ending where the first Misdemeanor begins.
FILE No. 001 · 1888
OPENThe Locked Drawer
AGENCY OFFICE, LONDON
"Your first day. The petty cash is gone, and the only witness is the signing book."
WHERE. How to ask the database for only the rows that matter.
1 TABLEINVESTIGATE →FILE No. 002 · 1888
OPENThe Pawnbroker's Window
CLERKENWELL, LONDON
"A silver case walked out of a locked cabinet. Four days later it was in a pawnbroker's window."
ORDER BY and LIMIT. How to sort rows and take only the top one.
1 TABLEINVESTIGATE →FILE No. 003 · 1888
OPENThe Laundry Mark
BLOOMSBURY, LONDON
"They left a coat behind. The coat does not carry a name. It carries a number."
JOIN. How to follow a key from one table into another.
2 TABLESINVESTIGATE →FILE No. 004 · 1888
OPENThe Cab Ranks
MARYLEBONE, LONDON
"Somebody watched that house for a week. Nobody remembers a face. The cab book remembers every fare."
GROUP BY and COUNT. How to ask how many, per person, instead of reading rows.
2 TABLESINVESTIGATE →
Misdemeanor
FILE No. 005 · 1891
OPENThe Blue Lantern Affair
WHITECHAPEL, LONDON
"The window was smashed and the till was open. But the silver was left on the floor, and the thief took the cheapest thing in the shop."
6 TABLESINVESTIGATE →FILE No. 006 · 1879
OPENThe Stolen Encore
BRISTOL, ENGLAND
"At 9:50 the band was losing. Ten minutes later they had won, and the one-vote-per-phone rule had been switched off."
6 TABLESINVESTIGATE →FILE No. 007 · 1892
OPENThe Rhyme at Alderwick Hall
ALDERWICK HALL, KENSINGTON
"Sir Rowan collapsed in his locked study. No one said they had gone in. The only witness was a poem."
5 TABLESINVESTIGATE →
Felony
FILE No. 008 · 1932
OPENPlease Don't Check Me Out
BELLWEATHER HOTEL, BATH
"He checked out at ten past six. At half past six he telephoned the front desk from inside the hotel, and the line went dead."
6 TABLESINVESTIGATE →FILE No. 009 · 1991
OPENFit for Release
OXFORD, ENGLAND
"They found the doctor inside an abandoned ambulance. The cuts were made after he died, and every one of them had been stitched shut again."
5 TABLESINVESTIGATE →FILE No. 010 · 1953
OPENThe Quiet Ward
ST ALDEN'S HOSPITAL, LEEDS
"Admissions held steady. Deaths did not. Every one of them was reviewed on its own, and every one of them was approved."
5 TABLESINVESTIGATE →
Capital
FILE No. 011 · 1971
OPENThe Sunday Cipher
BRISTOL, ENGLAND
"Seven people wrote in to confess. Six of them only know what the newspaper printed."
5 TABLESINVESTIGATE →FILE No. 012 · 1884
OPENThe Price of Silence
SANTA MARINA, BRAZIL
"The guests arrived by helicopter and left before the photographs. One line in the organiser's phone was not about the wine."
9 TABLESINVESTIGATE →
/ THE METHOD
Three steps to a confession.
- I.
Read the file
Every case opens with a briefing — the crime, the players, the patterns to look for. Read it twice. Detail matters.
- II.
Study the evidence
You'll be shown the schema of the database in evidence. Tables, columns, foreign keys. The truth is in there somewhere.
- III.
Submit your query
Write the SQL that exposes the culprit. One query. One answer. Get it right and the case closes.
/ BEHIND THE CASE
A side project that got out of hand.
detectiveSQL is an open-source SQL mystery game. Each case gives you a database full of evidence and a blank query editor. The files run from 1879 to 1991, from a London antique shop to a hospital ward in Leeds and a bank archive in Brazil. Your job is to follow the clues, connect the dots, and uncover what happened.
The idea is simple: make SQL fun to explore. Every query can reveal a clue, challenge a suspicion, or lead you somewhere unexpected. You learn by chasing answers, one discovery at a time.