Portugues
SQL Murder Mystery
Saved games stay in this browser only.
About the game
SQL Murder Mystery is a detective story that you solve by writing database queries. A murder took place in SQL City on 15 January 2018. The police have a database with crime scene reports, witness interviews, driving licences, gym check-ins, event attendance and income records, and nothing else to go on. You start by finding the crime scene report, and every answer you read points to the next table you have to search, until you can name the killer.
The game works as a lesson and as a puzzle. Beginners can open the walkthrough, which introduces SELECT, WHERE, LIKE,
JOIN and a few other pieces of SQL, just enough to finish the case, with short exercises along the way. Experienced users can
skip it and start with only the first hint. When you think you know who did it, an exercise at the end checks your answer.
Playing it here
Click Play and the first page opens. Each box with a coloured background is a small SQL editor connected to the case database, which runs entirely in your browser (the database is a SQLite file and the queries are executed by sql.js, so nothing is sent anywhere). Edit the query, press Run and the rows appear underneath; the database is shared by every box on the page, so what you learn in one exercise is available in the next. A link on the first page shows the diagram of all the tables, which is worth keeping open.
The solution box at the end lets you insert your answer into a table and read back whether it was right. Reload the page to start with fresh example queries.
Controls
| Input | Action |
|---|---|
| Keyboard | write SQL in the editor boxes |
| Run button | execute the query in that box |
| Reset button | restore the example query of that box |
| Links in the text | go between the main page and the walkthrough, show the schema diagram |
A few things worth knowing
Start with the report. The text gives you the date, the kind of crime and the city; the crime_scene_report table is where
you begin, and it tells you where to look next.
Read the transcripts. The witness interviews are long, and the details that matter are buried in the sentences. Searching text
with LIKE helps.
Use the diagram. Many tables are connected by ids; joining them is how you get from a name to a licence plate or a gym card.
Check, then look deeper. The first name you find may not be the end of the story. The check box at the end says if you are right, and the walkthrough goes through the whole case step by step if you are stuck.
Credits and licence
SQL Murder Mystery was created by Joon Park and Cathy He while they were fellows at the Northwestern University Knight Lab, and adapted for the web by Joe Germuska; it was inspired by the command-line murder mystery of Terminal City. The code is released under the MIT licence, and the text, the database and the diagram under the Creative Commons Attribution-ShareAlike 4.0 licence, as stated in the README of the repository on GitHub. The page also contains CodeMirror, lodash and sql.js (MIT licence, with SQLite in the public domain) and the SQL exercise components of Zi Chong Kao, released to the public domain. We changed the pages: the Knight Lab header and footer, the detective illustration (from a stock site, not under an open licence), the analytics, the social media tags and the web fonts loaded from other websites were removed, and the style is ours, so that nothing is requested from other websites. The licence texts are linked from this page.