Photo by Jonathan Kemper on Unsplash
Learning SQL and solving Murder Mystery
7 min read
Table of contents
- Step 1 : Finding crime scene report
- Step 2 : Finding First Witness
- Step 3 : Finding Second Witness
- Step 4: Finding the witnesses interview transcripts
- Step 5: Finding Gym Members
- Step 6: Querying the drivers_license table
- Step 7: We found the murderer
- Step 8: Verification
- Mystery Solved
- Lessons Learnt
My name is Namrata Joshi, coming from a medical background who practiced physical therapy for almost 12 years. I recently started learning SQL as I am advancing towards a career change and have decided to blog my journey. I found this amazing online source that helped me to not only practice SQL but also learn in an interesting and fun way. I hope this blog comes in handy to anyone who is trying to solve the SQL City Murder Mystery.
Step 1 : Finding crime scene report
I retrieved the crime scene report from the police department’s database with the information which is provided( date= 15th Jan 2018 and city= SQL City, type = Murder). I was able to get the date format by running a query(
select * from crime_scene_report ) which helped me to use that in the query below to get a description in the
crime_scene_report with the matching criteria.
SELECT * from crime_scene_report where date="20180115" and city = "SQL City";
Step 2 : Finding First Witness
From the above description, there is a clue that there are 2 witnesses. I used the first witness information- “Lives at the last house on Northwestern Dr” to run a query in
Person table. This helped me find the name and
address_number columns with matching criteria of
address_street_name= “Northwestern Dr”. I used
ORDER BY to arrange the
address_number column data in a descending order( sorting the address_number from last to first) and limited my output result to 3 to get the name of the first result output being the first witness.
Select name, address_number from person where address_street_name = "Northwestern Dr" Order by address_number desc Limit 3;
1st witness- Morty Schapiro
Step 3 : Finding Second Witness
There is another clue in the retrieved
crime_scene_report description about the second witness whose name is “Annabel” and lives somewhere on “Franklin Ave”. I used the
Person table to find the
address_number of a person. I used a
WHERE Clause inputting the provided information of
address_street_name = “Franklin Ave” and used a wildcard
%’ later to get a more precise search. For example, using
% after Annabel to retrieve all the data after the word Annabel. This helped me get the full name of the 2nd witness.
Select name, address_number from person where address_street_name = "Franklin Ave" and name like 'Annabel%';
2nd Witness- Annabel Miller
Step 4: Finding the witnesses interview transcripts
After getting the full names of the first and 2nd witnesses, it was important to find their interview transcripts individually from the
interview table. Hence, I queried both the
Person tables as the transcript was needed from the interview table and the name had to be used in the person table. I used a
WHERE clause to join two tables using the primary key
id from the person table and foreign key
person_id.) and used an
AND clause to input the name of each witness in individual queries to get their interview transcripts.
1) Morty Schapiro
select person.name, interview.transcript from person, interview where person.id = interview.person_id and person.name = 'Morty Schapiro';
2) Annabel Miller
select person.name, interview.transcript from person, interview where person.id = interview.person_id and person.name = 'Annabel Miller';
Step 5: Finding Gym Members
I combined the interview transcript information of both the witnesses as they were related. Both said that the murderer was recognised as the "Get Fit Now Gym member". Hence I had to join two tables and use a
get_fit_now_member table to get the
membership_status information. I used the provided information of
get_fit_now_check_in table and
membership_id starting with “48Z” from
get_fit_now_member table. I added the
WHERE clause to join the two tables using the primary key
get_fit_now_member table and the foreign key of
get_fit_now_check_in table. I further applied the
AND’clause and a wildcard
% to input the exact information provided by the witnesses. This helped me get a precise output data on names of gold membership status members with their membership ids starting with “48Z”.
select get_fit_now_member.name, get_fit_now_member.id, get_fit_now_member.membership_status from get_fit_now_member, get_fit_now_check_in where get_fit_now_member.id = get_fit_now_check_in.membership_id and (get_fit_now_check_in.check_in_date = '20180109' and get_fit_now_member.id like '48Z%');
Step 6: Querying the drivers_license table
Using the license plate information provided by "Morty Schapiro", I wanted to find the name of the person having a car license plate number containing “H42W”. For this, I used the two tables of
drivers_license. I joined two tables using a
WHERE clause matching the primary key of
drivers_license table and foreign key of
person table to retrieve the common data present in both tables. I used an
AND clause and a wildcard
% after “H42W” to get the list of names and their respective drivers license plate numbers containing the input data.
Select person.name, drivers_license.plate_number from person, drivers_license Where person.license_id= drivers_license.id and drivers_license.plate_number like '%H42W%';
Step 7: We found the murderer
The murderer is “Jeremy Bowers” after merging the outputs of step 5 and step 6.
Step 8: Verification
a) Checking "Jeremy Bowers" interview transcript.
I was able to find the interview transcript of Jeremy Bowers using both
interview tables. I used those two tables as
name columns were present in
person tables and
transcript column was present in the
interview table. I joined those tables using the primary key of
id in the
person table and foreign key of
person_id in the
interview table with a
WHERE clause. I used an
AND clause to specify
name specifically being ''Jeremy Bowers''.
Select person.id, person.name, interview.transcript from person,interview where person.id = interview.person_id and person.name= 'Jeremy Bowers';
b) Finding the brain behind the murder
I had to find the
name of the person and the person
id matching the
transcript description provided by Jeremy Bowers. I used these 4 tables of
facebook_event_checkin in the query below. I joined the tables using their keys with a
WHERE and multiple
AND clauses. I input the information of hair color being red, car make being Tesla and gender being female to retrieve specific data matching those information.
select person.id, person.name from person,drivers_license, income, facebook_event_checkin where person.license_id = drivers_license.id and (person.id = facebook_event_checkin.person_id and person.ssn = income.ssn and drivers_license.hair_color= 'red' and drivers_license.car_make= 'Tesla' and drivers_license.gender = 'female');
I used a subquery first to find the person's id, person’s name, height, car model, income from 3 tables of
income. I also included a
WHERE and multiple
AND clauses to join the three tables matching their keys and input the hair color, car make and gender information as derived from Jeremy’s interview transcript. I later used an
ORDER BY to sort the
annual_income column data in the descending order. I later ran a query to find
id of the person from the above subquery as well as from the
facebook_event_checkin table. It was important to join the
facebook_event_checkin tables using keys of
person_id respectively to retrieve the matching data.
select id, name from (select person.id, person.name, drivers_license.height, drivers_license.car_model, income.annual_income from person,drivers_license, income where person.license_id = drivers_license.id and (person.ssn = income.ssn and drivers_license.hair_color= 'red' and drivers_license.car_make= 'Tesla' and drivers_license.gender = 'female') Order by income.annual_income desc) ,facebook_event_checkin where id = facebook_event_checkin.person_id;
Brain Behind the murder
- It was helpful to first query all the different tables to get a better read of the inside data.
- I learnt to think creatively to join different tables depending upon the information I was getting from each query.
- It was fun to play around with a few wildcards and filters to retrieve the exact data although it took some trials and errors to get rid of the few syntax errors.
- I could also have used a combination of different JOINS in my queries although it was easier to filter the data using WHERE and AND clauses.
- Readability was an issue in some of my subqueries until I started to add some indentations.
- It is always helpful to think about multiple ways to retrieve the required data.