SQL Murder Mystery Solution & Step-by-Step Explanation

SQL Murder Mystery is a fun way to practice SQL, but do you know what's NOT fun? Getting stuck & confused while solving it 😭. That's why we've provided full answers to SQL Murder Mystery, along with a step-by-step explanation of the SQL queries we used to solve the game!

SQL Murder Mystery: Clues in Crime Scene Report

Let's start with this excerpt provided by SQL Murder Mystery:

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.

We're given three clues 🔎 to find the suspect:

1. The crime is a murder.
2. The crime scene occured on Jan 15, 2018.
3. The crime scene took place in SQL City.

SQL Murder Mystery: What's Our Game Plan?

It can be overwhelming to just jump in, so let's start with a high-level game plan:

1. Locate Witnesses: Using the clues, let's locate those who might have seen something suspicious.
2. Review Interviews: Once we've identified our witnesses, let's carefully review their statements for further clues.
3. Investigate Further: Armed with the interview insights, we'll investigate further into the database to track down our suspect.

Before we start writing SQL queries, let's first understand the database and how it's structured by reviewing with the Entity Relationship Diagram (ERD)!

SQL Murder Mystery: Entity Relationship Diagram (ERD)

Imagine the Entity Relationship Diagram (ERD) as a detective's map 🗺️, laying out suspects, clues, and connections within our database, like a blueprint for solving crimes.

In SQL, Primary Keys are unique identifiers, ensuring no duplicates. They're represented by gold key icons 🔑. For instance, in our table, the field is a Primary Key.

Now, Foreign Keys are arrows 🏹 connecting related data across tables, establishing relationships. For example, the table links to the table through fields like and , forming connections.

The game starts with the Crime Scene Report ( table), so let's proceed!

Step #1 in SQL Murder Mystery: Retrieve Crime Scene Report

Our first task is to obtain the crime scene report from the database.

We'll utilize the statement to view the contents of the table:

Connecting Clues to Crime Scene Report

Recall the clues provided earlier? Now, we need to connect these clues to the relevant fields in the table.

Let's match the clues to the corresponding fields:

1. The crime is a murder ➡️ Find in the field
2. The crime scene occurred on Jan 15, 2018 ➡️ Find in the field
3. It took place in SQL City ➡️ Find in the field

By connecting these clues to the appropriate fields, we'll be able to focus our investigation more effectively.

Step #2 in SQL Murder Mystery: Filter Crime Scenes by Type

Based on the first clue provided, we'll narrow down the crime scenes by type, focusing specifically on murder.

We'll use the SQL WHERE clause to filter the table by type:

The table below shows the murder crime scenes.

Step #3 in SQL Murder Mystery: Narrow Down Crime Scenes by Date and City

Now, let's narrow down the crime scenes further by refining our query to to include only those entries that match the provided date and location. We'll use the SQL AND operator to search for multiple conditions:

The result shows the report's description:

Security footage shows that there were 2 witnesses. The first witness lives at the last house on "Northwestern Dr". The second witness, named Annabel, lives somewhere on "Franklin Ave".

Aha! 💡 Now, we know that there are two witnesses to the crime scene:

📌 Witness #1: The first witness lives at the last house on "Northwestern Dr".

📌 Witness #2: The second witness, named Annabel, lives somewhere on "Franklin Ave".

Step #4 in SQL Murder Mystery: Explore Persons Involved

With the specific crime scene identified, our next step is to explore the witnesses involved in the case.

We'll run the statement to view the table:

Step #5 in SQL Murder Mystery: Identify First Witness's Location

Now, let's focus on identifying the location of the first witness mentioned in the crime scene report.

We'll locate the first witness who lives at the last house on "Northwestern Dr" street in the table using and SQL ORDER BY clauses:

This query searches for the witness who lives on "Northwestern Dr" street and order the results by address number in descending order, helping us identify the exact location of the first witness.

Our first witness is Morty Schapiro with 14887, 118009, and 111564949.

Step #6 in SQL Murder Mystery: Locate Second Witness by Name and Address

Similarly, we'll locate the second witness mentioned in the crime scene report, who is named "Annabel" and resides on "Franklin Ave" street.

We'll use the statement with a SQL LIKE wildcard pattern to search for the second witness's details.

If you haven't seen the LIKE operator, you'll definitely want to review our SQL LIKE tutorial because it's used a ton in real-world SQL, and is also a popular command used in SQL interview questions.

Here's the query we ran, which searches for a witness who resides on "Franklin Ave" with the name containing "Annabel":

By using the operator alongside the wildcard before and after "Annabel", it looks for any occurence of "Annabel" within the field.

Our second witness is Annabel Miller with 16371, 490173, and 318771143.

Step #7 in SQL Murder Mystery: Review Witness Interviews

With both witnesses identified, our next step is to review their interviews for any additional clues.

We'll use the statement with the SQL IN operator to view the interview table and retrieve the interviews of the witnesses:

Transcripts from witnesses:

I heard a gunshot and then saw a man run out. He had a "Get Fit Now Gym" bag. The membership number on the bag started with "48Z". Only gold members have those bags. The man got into a car with a plate that included "H42W".

I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.

So, here's our clues:

• Murderer is a man and a member of a gym called "Get Fit Now Gym" and has a gold membership no. starting from "48Z".
• He was working out at the gym on 9th Jan.
• He drives a car with car plate containing "H42W".

Step #8 in SQL Murder Mystery: Check Gym Check-Ins using Clues

Now, let's follow the clues provided in the witness interviews and check the gym check-ins for potential suspects.

We'll use the provided clues to query the gym check-ins:

We've gotten two members with s 48Z7A and 48Z55.

Step #9 in SQL Murder Mystery: Verify Gender and Car Plate Number

Continuing our investigation, we'll verify the gender and car plate information of potential suspects to gather more clues.

We're getting closer! The owner of the car could either have 423327 or 664760.

Step #10 in SQL Murder Mystery: Gather Additional Details on Persons

With potential suspects identified, let's gather additional details about them from the person table.

Now, let's check which of these two are a member of the gym.

Step #11 in SQL Murder Mystery: Finalize Suspect Identification

drumroll 🥁 The moment we've all been waiting for: Unveiling the murderer!

We've found the murderer - it's Jeremy Bowers!

Step #12 in SQL Murder Mystery: Confirm Solution in the Database

Before concluding our investigation, we need to confirm our solution by updating the database with the identified suspect:

Results shown:

Congrats, you found the murderer! But wait, there's more... If you think you're up for a challenge, try querying the interview transcript of the murderer to find the real villain behind this crime. If you feel especially confident in your SQL skills, try to complete this final step with no more than 2 queries. Use this same INSERT statement with your new suspect to check your answer.

Well, it's not over yet! 😮‍💨

Although the challenge instructs us to complete it with only two more queries, we'll be taking a few more steps to simplify the process.

Step #13 in SQL Murder Mystery: Review The Murderer's Interview

Let's analyze the murderer's interview and see what he has to say:

The murderer's interview:

I was hired by a woman with a lot of money. I don't know her name but I know she's around 5'5" (65") or 5'7" (67"). She has red hair and she drives a Tesla Model S. I know that she attended the SQL Symphony Concert 3 times in December 2017.

Here's the clues we've gathered:

• The real villain is a female, approximately 5'5" (65") or 5'7" (67") tall.
• She has red hair and drives a Tesla Model S.
• She attended the SQL Symphony Concert 3 times in December 2017.

This is getting interesting! 😉

Step #14 in SQL Murder Mystery: Identify Tesla Model S Owners

To narrow down our suspect list, we'll search for individuals matching the description provided by the murderer. This includes females with red hair, heights between 5'5" (65") and 5'7" (67"), who drive a Tesla Model S.

Three women fit the criteria.

Step #15 in SQL Murder Mystery: Retrieve Personal Details of Potential Suspects

Next, let's retrieve the personal details of the three women using their license IDs:

Step #16 in SQL Murder Mystery: Determine SQL Symphony Concert Attendees

To ascertain which women attended the SQL Symphony Concert, we'll utilize the SQL COUNT() function:

OH SHIT WE SOLVED IT:

The solution to SQL Murder Mystery is that Miranda Priestly was the murderer!

Step #17 in SQL Murder Mystery: Confirm The Culprit

Let's verify if we've correctly identified the mastermind of this mystery! 🕵🏻‍♂️

Results shown:

Congrats, you found the brains behind the murder! Everyone in SQL City hails you as the greatest SQL detective of all time. Time to break out the champagne!

The SQL learning doesn't have to stop here though! Check out some other free SQL resources we recommend, which take will turn you into a SQL wizard.

SQL Murder Mystery Alternatives And More SQL Games

If you're looking for more fun ways to practice SQL, check out this blog on 4 SQL games to play that make practicing SQL fun.

If you're looking for interactive SQL exercises that are more much more difficult than SQL Murder Mystery, check out these 200+ SQL Interview Questions:

And of course, to build those SQL foundations checkout the 100% free SQL tutorial: