The SQL Murder Mystery

There's been a Murder in SQL City! The SQL Murder Mystery is designed to be both a self-directed lesson to learn SQL concepts and commands and a fun game for experienced SQL users to solve an intriguing crime.

New to SQL?

This exercise is meant more as a way to practice SQL skills than a full tutorial. If you've never used SQL at all, try the walkthrough. If you really want to learn a lot about SQL, you may prefer a complete tutorial like Select Star SQL.

If you're comfortable with SQL, you can dive in below!

Experienced SQL sleuths start here

A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a ​murder​ that occurred sometime on ​Jan.15, 2018​ and that it took place in ​SQL City​. Start by retrieving the corresponding crime scene report from the police department’s database.

Exploring the Database Structure

Experienced SQL users can often use database queries to infer the structure of a database. But each database system has different ways of managing this information. The SQL Murder Mystery is built using SQLite. Use this SQL command to find the tables in the Murder Mystery database.

SELECT name 

  FROM sqlite_master

 where type = 'table'

Besides knowing the table names, you need to know how each table is structured. The way this works is also dependent upon which database technology you use. Here's how you do it with SQLite.

SELECT sql 

  FROM sqlite_master

 where name = 'crime_scene_report'

The rest is up to you!

If you're really comfortable with SQL, you can probably get it from here.

But click here to show the schema diagram.

And you can always go to the walkthrough.

​

​

​

​

Check your solution

INSERT INTO solution VALUES (1, 'Insert the name of the person you found here');

        SELECT value FROM solution;

Credits

The SQL Murder Mystery was created by Joon Park and Cathy He while they were Knight Lab fellows. See the GitHub repository for more information.

Adapted and produced for the web by Joe Germuska.

This mystery was inspired by a crime in the neighboring Terminal City.

Web-based SQL is made possible by SQL.js

SQL query custom web components created and released to the public domain by Zi Chong Kao, creator of Select Star SQL.

Detective illustration courtesy of Vectors by Vecteezy

Original code for this project is released under the MIT License

Original text and other content for this project is released under Creative Commons CC BY-SA 4.0