Crack a murder case by writing SQL queries that dig through police reports, interviews and records until one name remains.
SQL Detective Mystery is a murder case where the clues live in a database and the only way to read them is with SQL. A killing took place in SQL City on January 15, 2018. You remember that a crime scene report exists, but nothing else, so you start from there and follow the trail one query at a time.
There is nothing to install. A real SQLite database runs inside the page, and your results appear as tables right under each query box.
Getting started
The page offers several code boxes. Each one already holds a query, which you are free to change, and you run it with the Run button or Shift + Enter. Reset puts a box back to its original text. The opening boxes reveal which tables exist and what columns a given table holds, which is the best place to begin.
A link above the last box opens a diagram of the database. It shows how tables such as person, interview, drivers_license and the gym records connect through ID numbers.
How the case unfolds
Think like a detective. Find the right crime scene report first, then read what it says about witnesses. Look those witnesses up and read their interviews. Their statements mention a gym, a vehicle and more, and each one points you to another table to search.
When you have a name, enter it in the box under Check your solution. The database replies with a message telling you whether you got it right. A correct answer opens a second challenge that asks who planned the crime.
Winning moves
- Filter hard. With more than a thousand reports, a WHERE clause on date, city and type saves a lot of scrolling.
- Use LIKE with wildcards when you only know part of a name.
- ORDER BY with DESC and LIMIT 1 finds extremes, such as the last house on a street.
- JOIN tables so one query can match several clues at once.
- Write each new name and ID on paper as soon as you find it.
Who it suits
This one is for curious players who like puzzles with real-world skills attached. Complete beginners can follow the walkthrough page, which explains the key SQL commands with examples you can run. People who already know SQL can skip straight to the case and try to finish the bonus step in very few queries.
The database is big, with about 10,000 people and 1,228 crime scene reports, plus thousands of interviews, licenses and check-ins. Once the page has loaded, it all runs offline in your browser.
Controls
| Action | Keyboard | Mouse | Touch |
|---|---|---|---|
| Type or edit a query | Type in the code box | Click inside a code box | Tap a code box to bring up the keyboard |
| Run the query in a box | Shift + Enter | Click Run | Tap Run |
| Put the box back to its starting query | — | Click Reset | Tap Reset |
| Show the database diagram | — | Click the "click here" link above the last code box | Tap the "click here" link |
| Reveal a suggested query (walkthrough page) | — | Click Show Solution | Tap Show Solution |
SQL Detective Mystery FAQ
Is this a good way to learn SQL?
It can be. A walkthrough page covers SELECT, WHERE and LIKE, then JOIN and aggregate functions, with sample boxes to try, and you can solve the entire case from there. Each box also has a Show Solution button if you get stuck.
Does it save my queries or progress?
No. The database only lives in the open page, so reloading resets every code box. Keep notes of the names, IDs and dates you find, because they are easy to lose once results scroll away.
Can I play on a Chromebook or a phone?
A Chromebook is a good fit since you type a lot. Phones and tablets work in part: tapping a code box brings up the on-screen keyboard and the Run button responds to taps, but writing long queries on a small screen is slow.
How do I know the case is closed?
Type your suspect's full name into the check box and run it. A wrong guess gets a try-again message. The right one leads to a harder bonus step: finding who was behind the crime, ideally in two queries or fewer.
