Learn And Practice SQL With This Game

Ever tried to learn SQL, the query language that lets you poke at the innards of databases? Most tutorials start by having you create your own database, fill it with nonsense, and then run queries that will make your eyes glaze over (hooray, we can simulate the accounting department for a widget factory). The Knight Lab’s SQL Murder Mystery, however, makes things a bit more fun.

In the game, you know that a murder was committed, but you lost the police report describing it. Knowing only the date and location, can you sift through the information in the police database to figure out who the murderer was?

The scenario is a bit contrived, but it works as a puzzle because you have a question compelling you to dive through the data. My SQL is a bit rusty, so I immediately googled a bit of the syntax I’d forgotten, and started poking away. This is how we solve coding problems in real life, after all: Figure out what tools you need to answer the question you actually care about.

In the game, you start by getting your bearings—what tables are in this database?—and as you do, you find clues to follow up on. For example, it’s easy to find an entry for the police report, but it doesn’t mention the suspect; instead it just references two witnesses. In another table you can find witness interviews, and then follow up on clues (one witness remembers part of the culprit’s licence plate, for example.)

I’ve worked with SQL databases before, and at first I was annoyed at how the database was set up. (I would have done it differently.) Then again, in real life you often have to extract information from a database that is oddly organised. There’s also no in-story explanation of why the police database also contains useful but disconnected information, like the local gym’s member check-ins. But while it may not make sense to encounter all of these things in the same database in the real world, it does give you an idea of how a piece of information (a licence plate, a sighting at the gym) can get you access to a whole trove of further information if you can just find the right data source.

This game will take you maybe 10-15 minutes if you’re already good with SQL, or you can use it to guide a longer session if you’re still learning. (There is an accompanying walkthrough that teaches you as you go.) A bonus level at the end challenges you to find out who hired the hitman, and to do so with a three-table join (or at least that’s how I did it). Because if you really want to learn how to use a tool, you need to figure out how to ask and answer your own questions, not just run through a textbook.

Comments


Leave a Reply