Introduction
Hello there,
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.
Query
SELECT *
from crime_scene_report
where date="20180115"
and city = "SQL City";
Output
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.
Query
Select name,
address_number
from person
where address_street_name = "Northwestern Dr"
Order by address_number desc
Limit 3;
Output
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 name and 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 LIKE and % after Annabel to retrieve all the data after the word Annabel. This helped me get the full name of the 2nd witness.
Query
Select name,
address_number
from person
where address_street_name = "Franklin Ave"
and name like 'Annabel%';
Output
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 Interview and 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 keyid 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
Query
select person.name,
interview.transcript
from person, interview
where person.id = interview.person_id
and person.name = 'Morty Schapiro';
Output
2) Annabel Miller
Query
select person.name,
interview.transcript
from person, interview
where person.id = interview.person_id
and person.name = 'Annabel Miller';
Output
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 name, id and membership_status information. I used the provided information of check_in_date from 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 id from get_fit_now_member table and the foreign key of membership_id from 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”.
Query
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%');
Output
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
person and drivers_license. I joined two tables using a WHERE clause matching the primary key of id from drivers_license table and foreign key of license_id from 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.
Query
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%';
Output
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 person and interview tables. I used those two tables asid and 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''.
Query
Select person.id,
person.name,
interview.transcript
from person,interview
where person.id = interview.person_id
and person.name= 'Jeremy Bowers';
Output
b) Finding the brain behind the murder
Option 1
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 person, drivers_license, income and 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.
Query
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');
Output
Option 2
I used a subquery first to find the person's id, person’s name, height, car model, income from 3 tables of person, drivers_license and 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 nameand id of the person from the above subquery as well as from the facebook_event_checkin table. It was important to join the personand facebook_event_checkin tables using keys of id and person_id respectively to retrieve the matching data.
Query
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;
Output
Mystery Solved
Brain Behind the murder
Miranda Priestly
Lessons Learnt
- 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.
Resources
SQL City Murder Mystery
