This repository showcases the sql prompts I used to solve the infamous "SQL Murder Mystery" exercise.
The SQL Murder Mystery exercise is intended primarily as a way to practice SQL skills and was created by Joon Park and Cathy He while they were Knight Lab fellows. Find the original repository here: It was adapted and produced for the web by Joe Germuska. Link to the exercise: https://mystery.knightlab.com
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.
- Queries with constraints (WHERE, AND/OR, IN, BETWEEN, LIKE, etc.)
- Filtering and Sorting Query (e.g. ORDER BY, and LIMIT)
- Table Joins
To get started, we know that the "murder" took place on "Jan 15 2018" in "SQL City". Use these conditions to filter the crime report.
SELECT *
FROM Crime_scene_report
WHERE date = '20180115'
AND city = 'SQL City'
;
This gives us clues of 2 witnesses.
First witness lives on the last house of Northwestern Dr. Using these conditions and joining person table with interview table, we can find the name and trasncript of first witness.
SELECT name, transcript
FROM person
INNER JOIN interview
ON id = person_id
WHERE address_street_name = "Northwestern Dr"
ORDER BY address_number desc
LIMIT 1
;
We have first name and address of second witness. Using these conditions and joining person table with interview table, we can find the name and trasncript of Second witness.
SELECT name, transcript
FROM person
INNER JOIN interview
ON id = person_id
WHERE address_street_name = "Franklin Ave"
AND name LIKE "Annabel%"
;
From the two transcripts we know that the killer had "Gold" membership with id strating from 48Z. Using these conditions and joining get_fit_now_member table with get_fit_now_check_in table we can narrow our search to few people.
SELECT membership_id, check_in_date, person_id, name, membership_status
FROM get_fit_now_member
INNER JOIN get_fit_now_check_in
ON id = membership_id
WHERE membership_status = "gold"
AND membership_id LIKE "48Z%"
;
We now have 2 suspects and part of the Killer's plate number. Using these conditions and joining person table with drivers_license table we get our "KILLER".
SELECT person.id, name, plate_number
FROM person
JOIN Drivers_license
ON person.license_id = Drivers_license.id
WHERE name IN ("Joe Germuska", "Jeremy Bowers")
AND plate_number LIKE "%H42W%"
;
Spolier Alert !!
Looks like there is someone else behind the crime.
To find the mastermind, first let's look at the transcript of the Killer.
SELECT *
FROM interview
WHERE person_id = 67318
;
Clues in his statement: Gender = Female; Hair colour =Red, Height = Between 65 and 67, Car make = Tesla' and Car model = Model S, Event attended = SQL Symphoy Concert Use these clues and Joining drivers_license, person and facebook_event_checkin table, we find our "MASTERMIND"
SELECT
DISTINCT person.name, person.id, person.license_id
FROM drivers_license
INNER JOIN person
ON Drivers_license.id = person.license_id
INNER JOIN facebook_event_checkin
ON person.id = facebook_event_checkin.person_id
WHERE gender = 'female'
AND hair_color = 'red'
AND car_make = 'Tesla'
AND car_model ='Model S'
AND height Between 65 AND 67
;
Spolier Alert !!
Our SQL Skill has solved another crime!!!!