You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
There's been a Murder in SQL City! The SQL Murder Mystery is designed to be both a self-directed lesson to learn SQL concepts and commands and a fun game for experienced SQL users to solve an intriguing crime.
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.
Crime report
SELECT*FROM crime_scene_report
WHERE type='murder'AND city='SQL City'ANDdate=20180115;
date
type
description
city
20180115
murder
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".
SQL City
Suspects
SELECT*FROM person
WHERE name LIKE'%Annabel%'AND address_street_name ='Franklin Ave';
SELECT*FROM person
WHERE address_street_name ='Northwestern Dr'ORDER BY address_number DESCLIMIT1;
id
name
license_id
address_number
address_street_name
ssn
16371
Annabel Miller
490173
103
Franklin Ave
318771143
14887
Morty Schapiro
118009
4919
Northwestern Dr
111564949
Suspects licenses
SELECT*FROM drivers_license WHERE id=490173OR id=118009;
SELECT*FROM get_fit_now_check_in WHERE membership_id ='90081';
membership_id
check_in_date
check_in_time
check_out_time
90081
20180109
1600
1700
Suspects Interviews
SELECT*FROM interview WHERE person_id=16371OR person_id=14887;
name
transcript
Morty Schapiro
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".
Annabel Miller
I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.
Investigating transcripts
Morty Schapiro's transcript
SELECT*FROM get_fit_now_member
JOIN person ONperson.id= person_id
JOIN drivers_license ONdrivers_license.id=person.license_idWHEREget_fit_now_member.idLIKE'48Z%'AND membership_status='gold'ANDdrivers_license.gender='male'ANDdrivers_license.plate_numberLIKE'%H42W%';
id
person_id
name
membership_start_date
membership_status
id
name
license_id
address_number
address_street_name
ssn
id
age
height
eye_color
hair_color
gender
plate_number
car_make
car_model
48Z55
67318
Jeremy Bowers
20160101
gold
67318
Jeremy Bowers
423327
530
Washington Pl, Apt 3A
871539279
423327
30
70
brown
brown
male
0H42W2
Chevrolet
Spark LS
SELECT*FROM get_fit_now_check_in
WHERE membership_id ='48Z55';
membership_id
check_in_date
check_in_time
check_out_time
48Z55
20180109
1530
1700
SELECT*FROM interview
WHERE person_id =67318;
person_id
transcript
67318
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.
The plot thickens
We'll track this mysterious woman later on
SELECT*FROM facebook_event_checkin
WHERE person_id =67318;
In her transcript she mentiones seeing the killer at the Gym. From her entry we know she was indeed there on 9th Jan, between 16:00 and 17:00. This means the killer must have checked in before she left and the killer must have checked out after she left.
SELECT*FROM get_fit_now_check_in
WHERE check_in_date=20180109AND check_in_time <1700AND check_out_time >1600;
membership_id
check_in_date
check_in_time
check_out_time
48Z7A
20180109
1600
1730
48Z55
20180109
1530
1700
90081
20180109
1600
1700
We have to remember that 90081 is Annabel
Notice 48Z55 is Jeremy Bowers, the guy from Morty's transcript
For 48Z7A there is just the one check-in
SELECT*FROM get_fit_now_member
JOIN person ON person_id =person.idWHEREget_fit_now_member.id='48Z7A';
id
person_id
name
membership_start_date
membership_status
id
name
license_id
address_number
address_street_name
ssn
48Z7A
28819
Joe Germuska
20160305
gold
28819
Joe Germuska
173289
111
Fisk Rd
138909730
SELECT*FROM facebook_event_checkin
WHERE person_id =28819;
This person attended no events
Jeremy Bowers's
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.
From this statement we can extract a few things
It's a female
5'5" (65") or 5'7" (67")
Hair color is red
Car is TeslaModel S
She attended SQL Symphony Concert 3, 3 times in December 2017
SELECT*FROM person
JOIN drivers_license ONperson.license_id=drivers_license.idWHEREdrivers_license.gender='female'ANDdrivers_license.hair_color='red'AND
(height >=65AND height <=67) AND
car_make ='Tesla'AND car_model='Model S';
id
name
license_id
address_number
address_street_name
ssn
id
age
height
eye_color
hair_color
gender
plate_number
car_make
car_model
78881
Red Korb
918773
107
Camerata Dr
961388910
918773
48
65
black
red
female
917UU3
Tesla
Model S
90700
Regina George
291182
332
Maple Ave
337169072
291182
65
66
blue
red
female
08CM64
Tesla
Model S
99716
Miranda Priestly
202298
1883
Golden Ave
987756388
202298
68
66
green
red
female
500123
Tesla
Model S
SELECT*FROM facebook_event_checkin
WHERE person_id IN (78881, 90700, 99716);
person_id
event_id
event_name
date
99716
1143
SQL Symphony Concert
20171206
99716
1143
SQL Symphony Concert
20171212
99716
1143
SQL Symphony Concert
20171229
Investigating Miranda Priestly
99716, Miranda Priestly is the only person matching the criteria
Will investigate the other points just to get an idea of this person
SELECT*FROM income WHERE ssn=987756388;
ssn
annual_income
987756388
310000
SELECT*FROM get_fit_now_member WHERE person_id =99716;
Not a GetFitNow member
SELECT*FROM interview WHERE person_id =99716;
Did not give an interview
Ending
Honestly not the ending I expected. I really hoped for this to go on for longer.
I've put in Miranda Priestly and won.
INSERT INTO solution VALUES (1, 'Miranda Priestly');
SELECT value FROM solution;
value
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!