SQL Murder Mystery

The SQL Murder Mystery is a fun little exercise to practice some basic (and not so basic) SQL statements. As a SQL master, your mission is to discover the murder that took place in SQL city, and discover the true identity of the killer by digging through the police database.

The first two statements are given to you as a glimpse of how the structure of the police database looks like:

SELECT name 
  FROM sqlite_master
 where type = 'table'


SELECT sql 
  FROM sqlite_master
 where name = 'crime_scene_report'

After that, you are given a couple of clues as to the date (15 Jan 2018) , place (SQL City) and nature of the crime (Murder) to find the culprit. According to the instructions, we can solve it in 2 huge ‘JOIN’ queries, but we are going to go for (very) basic SQL statements in this article.

Running the two given SQL statements, we get the following:

Notice that the ‘date’ is stored as an integer. We can run the statement to see what ‘date’ actually looks like.

SELECT date FROM crime_scene_report 

Now that we know how date actually looks like, we can run the following statement to find the crime scene report we are interested in. Note that it is standard convention for keywords in SQL statements like SELECT, FROM, WHERE etc to be capitalized, but for the sake of easy typing, we are just going to type in lowercase wherever possible.

select * from 'crime_scene_report' where date='20180115' and type='murder' and city='SQL City'

Apparently, we have 2 witnesses for this crime! One of them is an ‘Annabel’ from ‘Franklin Ave’, while the other is at the last house in ‘Northwestern Dr’. Referring back to the last database table format query, we have a table named ‘Person’. We use the following statement to find out what the ‘Person’ table looks like:

SELECT sql FROM sqlite_master where name = 'person'

Now that we know what the ‘Person’ table looks like, let’s get some information of our first witness Annabel.

select * from 'person' where address_street_name='Franklin Ave' and name LIKE '%Annabel%'

Now for our second witness. We need to find the last house on ‘Northwestern Dr’, and can run the following query to do so:

select MAX(address_number) from 'person' where address_street_name='Northwestern Dr'

Now that we have gotten our last house number, we can find that mysterious second witness.

select * from 'person' where address_street_name='Northwestern Dr' and address_number=4919

Not that we have retrieved our two witnesses’ identities, we can check out their interview with the following statements:

SELECT sql FROM sqlite_master where name = 'interview'


select * from 'interview' where person_id='16371'   

           
select * from 'interview' where person_id='14887'

Looks like it’s time to go to the gym. We also get new clues that our murder suspect is a gold member of the gym with a membership number starting with ’48Z’, and a car plate that includes ‘H32W’.

SELECT sql FROM sqlite_master where name = 'get_fit_now_member'


SELECT * FROM 'get_fit_now_member' where id LIKE '%48Z%' and membership_status='gold'

Attempting to get a hit using the car plate number, however, returns no results:

SELECT * FROM 'drivers_license' where plate_number LIKE '%H24W%'

We get two hits for the membership id with gold membership_status. Let’s find out more about these two gold members.

select * from 'person' where id='28819'


select * from 'person' where id='67318'

Now that we have their license_id, we can look up their cars to see the car plate number. We run the following queries:

SELECT * FROM 'drivers_license' where id='173289'


SELECT * FROM 'drivers_license' where id='423327'

Seems that we’ve got a hit! Jeremy Bowers seem to be our man. Let’s see what he has to say for what he has done:

select * from interview where person_id='67318'

Looks like Jeremy is just a hired assassin. It’s great that we got him, but let’s find the mastermind as well. From the make and model of her car, we can get her identity with the following queries:

SELECT * from 'drivers_license' where car_make='Tesla' and car_model='Model S' and gender='female' and hair_color='red' 

All three hits returned have the height we are seeking. We’ll have to check out each of them through the ‘Person’ database:

SELECT * FROM 'person' where license_id='202298'


SELECT * FROM 'person' where license_id='291182'


SELECT * FROM 'person' where license_id='918773'

Our hitman Bowers mentioned that the mastermind behind the murders attended the SQL Symphony Concerts thrice in December 2017. Let’s check out the structure of the facebook_event_checkin database and attempt to get the identity of the mastermind:

SELECT sql FROM sqlite_master where name = 'facebook_event_checkin'


select * from facebook_event_checkin where person_id='99716'


select * from facebook_event_checkin where person_id='57410'


select * from facebook_event_checkin where person_id='78881'

Luckily for us, our mastermind is a social media check-in addict. By now, it’s pretty clear who our mastermind is. Let’s see if Miranda Priestly is the woman behind the murder:

INSERT INTO solution VALUES (1, 'Miranda Priestly');
SELECT value FROM solution;

Bingo! We got her! Good job detective!

We hope you enjoy this little walkthrough as much as we did writing it!