“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.”
This is a neat SQL Murder Mystery available on knightlab.com
“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.”
There is a more involved walkthrough on the site itself which goes deep into definitions and the different keywords and functions of SQL, here
I wanted to put together my own tutorial to show my thought process throughout the exercise.
SPOILERS
Enjoy! 🤓
SQLite
The site starts off by giving you a query to run to find the names of the tables in the database. Note: The SQL Murder Mystery is buit using SQLite. This just means that there is no SQL server database engine like you would see at an enterprise. SQLite is file based and strives to provide local data storage for individual applications and devices.
If you are interested in the details around Appropriate Uses for SQLite, check it out here: https://sqlite.org/whentouse.html
Back to the story 📖
When you run the first query, you are given the names of 9 different tables (shown below)
Investigation 🕵🏻♀️
Since you lost the crime report, slick, let’s see what we can find in the crime_scene_report table
You can first start with looking at the table structure if you are curious about what fields are available
Searching the table structure is a good practice, especially in the real world because you will come across tables with TONS of unnecessary fields that you can ignore when querying the data.
Something else that is helpful if you are new to relational databases, the site provides the schema diagram. Here you can see the 9 tables that are available and how / if they relate to each other.
These are EXTREMELY helpful in the real world. Not only will you most likely need to use more than one table in your analysis, but the field names are more than likely to not match up as nicely as they do here.
Ok, let’s finally write a query
We know that the crime happened in SQL City, but we are unsure of the date. So let’s just check out all the murder type crimes in SQL City from the case scene report table. I ordered by date for ease of searching for the date I vaguely remembered, Jan. 15, 2018.
SELECT *
FROM crime_scene_report
WHERE city = 'SQLCity'
AND type = ‘murder’
ORDERBYdate
This first report is on the date I remembered, ok so my memory isn’t bad, only my ability to not lose crime reports.
The report states that there were 2 witnesses. We know where both of them live and also one of their names. Great! We have an interview table, so let’s go check out to see if they were interviewed and see what they had to say.
Per the table structure for the interview table, it looks like we will not be able to get very far without person id, so let’s actually look at the person table first.
Looks like we have address street name and name to leverage here. There are a lot of houses on Northwestern Dr, but ordering by address number DESC will get us the last house, as stated in the crime report.
SELECT *
FROM person
WHERE address_street_name LIKE '%Northwestern Dr%'
OR (address_street_name LIKE '%Franklin Ave%'
AND name LIKE '%Annabel%')
ORDER BY address_street_name, address_number DESC
The top two records in this table are the ones we need. So let’s go see what Annabel Miller (16371) and Morty Schapiro (14887) had to say in their interviews.
SELECT *
FROM interview
WHERE person_id IN (16371,14887)
Looks like the killer goes to the gym, he is likely to be a gold member (‘48Z’ on the gym bag), and the murder happened on January 9th.
We can also indirectly infer that it takes about a week to write a crime report, but that is not of value to solving this mystery, just fun facts 🤓
Let’s go see who was at the gym on Jan 9th, that was a gold member ‘48Z’. There are two members that fit this description, but the information here isn’t very helpful.
SELECT *
FROM get_fit_now_check_in
WHERE check_in_date = '20180109'
AND membership_id LIKE '%48Z%'
Let’s look at the other gym table to see what else we can find.
SELECT *
FROM get_fit_now_member
WHERE id IN ('48Z7A','48Z55')
They are both indeed gold members. What else do we have here… oh yea, Morty mentioned that he saw the guy get into a car with license plate ‘H42W’. Let’s go check out the drivers license table with these two suspects, but first need to get their license id from the person table.
SELECT *
FROM person
WHERE id IN (67318,28819)
SELECT *
FROM drivers_license
WHERE id IN (173289,423327)
Only one guy has a car, Jeremy Bowers (67318), hmm. Ok. I wonder if he was interviewed…
SELECT *
FROM interview
WHERE person_id = 67318
Welp, he definitely is the one that shot the gun. However, he was actually hired to do the job!! Let’s go find the woman behind the scenes!
First to check how many people in the drivers license table fit this description
SELECT *
FROM drivers_license
WHERE gender = 'female'
AND hair_color = 'red'
AND height BETWEEN 65 and 67
ANDcar_make = 'Tesla'
AND car_model = 'Model S'
We are down to three suspects. The other information we have is regarding the amount of money she has and also that she attended the SQL Symphony Concert 3 times in December 2017.
Let’s check out income first because I am curious what ‘a lot of money’ means. But first we need the ssn, so back to the person table.
SELECT *
FROM person
WHERE license_id IN (202298,291182,918773)
SELECT *
FROM income
WHERE ssn IN (961388910,337169072,987756388)
This isn’t very helpful, only two have recorded income and both appear to be very well compensated. Let’s go see who checked into the SQL Symphony Concert.
SELECT *
FROM facebook_event_checkin
WHERE date LIKE '201712%'
AND person_id in (78881,90700,99716)
Looks like we found the killer, Miranda Priestly!!
Solution
Another cool feature of this site is the Check your Solution feature. Let’s see if we are right
Yay!! 🍾
What did you think? Did you learn anything new? Did you have fun?
If you know of any other fun activities like this, please share in the comments 👇🏻
Happy Learning!!
Comments