The SQL Murder Mystery

stellabonaparte
May 20, 2022
3 min read

What a fun way to practice SQL!

Playing this SQL Mystery game was way more enjoyable than regular SQL practice.

Try it here https://mystery.knightlab.com/ before reading my results because--

Here Be Spoilers!

A crime has taken place, use the below ERD and your SQL knowledge to find the culprit. Here are the facts:

· It was a murder

· It occurred on Jan 15, 2018

· Location: SQL City

1) First , I queried for the given details:

SELECT *

FROM crime_scene_report

WHERE date = 20180115

AND type = 'murder'

AND city = 'SQL City'

Query Results:

2) The description from the last query gives two witnesses (one named) and the streets on which they live. From the ERD I saw the [person] table has addresses.

First I ordered just by address_number but that put one witness at the top and one at the bottom of a long list. To correct this I used ORDER BY street name so both were on top:

SELECT *

FROM person

WHERE address_street_name = 'Northwestern Dr'

OR address_street_name = 'Franklin Ave'

AND name LIKE 'Annabel%'

ORDER BY address_street_name, address_number DESC

LIMIT 2;

Query Results:

Perfect! I have my witnesses.

3) Now I can use their id numbers to locate their witness statements from [interview]:

SELECT *

FROM interview

WHERE person_id = 16371

OR person_id = 14887

Query Result:

4) The witnesses give me a lot of information!

Annabel Miller (id 16371) says she was an eyewitness to the murder and that she saw the culprit when she was working out on January 9th at her gym.
But first I will look at the data provided by witness Morty Schapiro (id 14887) starting with that partial membership id:

SELECT *

FROM get_fit_now_member

WHERE id LIKE '48Z%'

Query Results:

5) Next I see if any of these 3 suspects checked into the gym on January 9th:

SELECT *

FROM get_fit_now_check_in

WHERE membership_id LIKE '48Z%'

AND check_in_date = 20180109

This narrows it down to two of those suspects, Joe Germuska and Jeremy Bowers:

6) I want to see if Annabel was at the gym at the same time as either suspect. First, I used her person_id to get her gym membership_id:

SELECT *

FROM get_fit_now_member

WHERE person_id = 16371

That query returned her membership_id as 90081.

7) Next, I used her membership id to get her check-in times to compare to those of the suspects.

BOO, HISS!

This was a dead end, since she was there at the same time as both suspects that day!

What would Jessica Fletcher do?

Keep going!

8) Next, I looked at the license plate. Mr. Schapiro said the plate included “H42W” so I didn't assume it started with that sequence. I wrapped it in wildcards to allow for all possibilities.

I also only included males since the suspects are both male:

SELECT *

FROM drivers_license

WHERE plate_number LIKE '%H42W%'

AND gender = 'male'

Query Results: