Inspired by @veltman's command-line mystery, use SQL to research clues and find out whodunit!

Go to file

Joe Germuska 52c8427da7 update for GA4		2023-06-26 16:53:32 -05:00
css	make result .datatable a bit bigger; vacuum the DB (little change)	2022-09-23 10:49:57 -05:00
scripts	WIP	2019-10-10 08:54:02 -05:00
174092-clue-illustration.png	WIP	2019-10-10 08:54:02 -05:00
CNAME	Create CNAME	2019-10-10 14:17:59 -05:00
LICENSE	Create LICENSE	2018-01-17 10:26:09 -06:00
README.md	👾 update links	2022-07-06 02:53:54 +06:00
index.html	update for GA4	2023-06-26 16:53:32 -05:00
prompt_beginner.pdf	Update README and add cheatsheet and prompts	2018-03-08 19:01:06 -06:00
prompt_experienced.pdf	Minor clarifications and additions to pdfs	2018-04-05 14:37:38 -05:00
reference.pdf	Minor clarifications and additions to pdfs	2018-04-05 14:37:38 -05:00
requirements.txt	Bump jupyterlab from 1.1.4 to 1.2.21	2021-08-23 20:09:19 +00:00
schema.png	fix DB so that ssn is a proper FK relationahip (and CHAR since that's more realistic) and create a new ER diagram	2022-09-23 15:40:04 -05:00
sql-murder-mystery.db	fix DB so that ssn is a proper FK relationahip (and CHAR since that's more realistic) and create a new ER diagram	2022-09-23 15:40:04 -05:00
sqlite_studio.pdf	Minor clarifications and additions to pdfs	2018-04-05 14:37:38 -05:00
walkthrough.html	update for GA4	2023-06-26 16:53:32 -05:00

README.md

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.

If you just want to solve the mystery, go to mystery.knightlab.com. If you're new to SQL, you may want to start at our walkthrough. It won't teach you everything about SQL, but it should teach you all that you need to solve the mystery.

What Else is Here?

Before we built the web-based version, we designed this for people to download and solve on their own computer. If you're interested in that, read on.

What you need to solve on your own computer

sql-murder-mystery.db: This SQLite database file contains all the data that you will be working with.
prompt: Depending on your experience level with SQL, find the prompt in either the prompt_experienced file or the prompt_beginner file.
reference: This is a crash course on SQL concepts and commands.
a SQLite environment of your choice: For beginners, we recommend using SQLiteStudio, which is a good graphical interface to use to inspect your data and write queries.

Getting Started

For SQL beginners: start with the reference, read the prompt_beginner file, then get started by installing SQLiteStudio and loading the db file. If you get stuck at any point, feel free to refer back to the reference, or file a GitHub issue so we can know where our instructions need to be improved.
For experienced SQL users: read the prompt_experienced file, then download the sql-murder-mystery.db file and use a SQL environment of your choice to solve the mystery. You can use the reference to refresh your memory of SQL. Try to complete the activity all within your SQL environment (without writing down notes)!

Checking the Solution

Write the following queries in your SQL environment to check whether you've found the right murderer:

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

SELECT value FROM solution;

Authors

Inspiration

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

Copyright and License

Original code for this project is released under the MIT License.

Original text and other content is released under Creative Commons CC BY-SA 4.0.

SQL query custom web components used here were adapted from code created and released to the public domain by Zi Chong Kao, creator of Select Star SQL.

Detective image by rambleron used under Vecteezy's free license.