SQL Murder Mystery, Notebook Style

In passing, I noticed that Simon Willison had posted a datasette mediated version of the Knight Lab SQL Murder Mystery.

The mystery ships as a SQLite database and a clue…

To my mind, a Jupyter notebook provides an ideal medium for playing this sort of game. In between writing queries onto the database, and displaying the responses inline within the notebook, detectives can also use markdown cells to write notes, pull out salient points, formulate hypotheses and write natural language questions that can then be cast into SQLese.

So as an aside for TM351 students that they really don’t need, I put together a notebook that sets the scene for the murder mystery, along the way showing how to create PostgreSQL databases and users, set database permissions on a per user basis, and import the original SQLite database into Postgres.

You can find the notebook here along with a link for how to download and install the TM351 VM yourself if you fancy giving it a spin…

Author: Tony Hirst

I'm a Senior Lecturer at The Open University, with an interest in #opendata policy and practice, as well as general web tinkering...

4 thoughts on “SQL Murder Mystery, Notebook Style”

  1. Might be a nudge for me, but thanks for the link to the original Murder Mystery, a brilliant concept. That led me to the Command Line Murder Mystery and into a whole set of rabbit holes by Noah Veltman https://noahveltman.com/ – thanks for the journey, Tony!

    1. @cogdog On my to do list is to cast the command line mystery into a similar notebook format; didn’t get a chance today… maybe tomorrow…

    2. @cogdog Also on my to do list are simpler, binderised versions that run the notebook in MyBinder rather than TM351 VM using either postgres or just the sqlite db.

Comments are closed.

%d bloggers like this: