SQL Detective is a mystery you solve by writing database queries. It's for people who have never written SQL. Each chapter teaches one idea in two lines, shows you an example, then asks a question about the case that you answer with a query.
It's still in our workshop, so it's listed as beta. The first case is complete.
The case
Chandpur Junction, 14 October, just before midnight. The station's 1906 brass clock has vanished from its little museum. The station keeps its records in four tables:
- report: the incident report
- people: everyone in the case
- gate_log: every swipe at the station gates that night
- vehicles: registered vehicles and their owners
You read them with SQL. We won't tell you who did it.
Eight chapters, one idea each
| Chapter | You learn |
|---|---|
| 1. Read the report | SELECT * FROM |
| 2. Who used the gate? | WHERE |
| 3. The missing forty minutes | AND, >=, <= |
| 4. Put it in order | ORDER BY |
| 5. Part of a plate | LIKE |
| 6. Whose vehicles? | JOIN |
| 7. Back and forth | COUNT, GROUP BY |
| 8. Name the thief | HAVING, IN (…) |
Each solved chapter gives you a clue, and the clues add up to the answer.
Chapter 3: the question, your query, the clue you earned, and the rows it found.
Any correct query counts
The game doesn't compare your query's text with ours. It runs your query on a real database in your browser (SQLite) and compares the rows you get back. So:
SELECT *andSELECT name, timecan both be right, as long as the columns the question needs are there.BETWEEN '23:10' AND '23:50'is just as good as>= … AND <= ….- Column names and aliases don't matter, and row order only matters in the chapter about
ORDER BY.
When the rows are wrong, it tells you how, in plain words: too many rows, too few, a missing column, or the right rows in the wrong order.
A wrong answer still shows its result table, with a nudge about what's off.
Error messages you can act on
Database error messages are written for people who already know SQL. SQL Detective rewrites them:
SELECT * FORM gate_log→ "FORM isn't a SQL word. Did you mean FROM?"WHERE gate = Main→ "There's no column called Main. If you meant a piece of text, put it in single quotes: 'Main'."time >= 23:10→ "Times are text here, so put them in single quotes: '23:10'."HAVINGbeforeGROUP BY→ "GROUP BY has to come before HAVING", with the full order of the parts.
A typo in a keyword gets a plain-English fix instead of 'syntax error'.
Phones like to turn ' into curly quotes. The game quietly fixes those before running your query.
When you're stuck
There's a Stuck? Show hint button with three steps: a reminder of the idea, then the part of the query you need, then the whole query with blanks. Hints are counted and shown at the end, but nothing is taken away for using them.
You can also tap any table to peek at all its rows, and tap table and column names to type them into your query. That makes it workable on a phone.
Your query only reads
Only SELECT queries run. Detectives read the records; they don't change them. The whole database lives in your browser tab and starts fresh on each visit, while your progress through the chapters is saved on your device.