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.
SELECT * FROM crime_scene_report
WHERE type='murder' AND city='SQL City' AND date=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 |
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 DESC
LIMIT 1;
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 |
SELECT * FROM drivers_license WHERE id=490173 OR id=118009;
id |
age |
height |
eye_color |
hair_color |
gender |
plate_number |
car_make |
car_model |
118009 |
64 |
84 |
blue |
white |
male |
00NU00 |
Mercedes-Benz |
E-Class |
490173 |
35 |
65 |
green |
brown |
female |
23AM98 |
Toyota Yaris |
|
SELECT get_fit_now_member.id, person_id, get_fit_now_member.name, membership_start_date, membership_status, person.name
FROM get_fit_now_member
JOIN person ON person.id = person_id
WHERE person.id=16371 OR person.id=14887;
id |
person_id |
name |
membership_start_date |
membership_status |
name |
90081 |
16371 |
Annabel Miller |
20160208 |
gold |
Annabel Miller |
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 |
SELECT * FROM interview WHERE person_id=16371 OR 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. |
SELECT * FROM get_fit_now_member
JOIN person ON person.id = person_id
JOIN drivers_license ON drivers_license.id = person.license_id
WHERE
get_fit_now_member.id LIKE '48Z%' AND membership_status='gold' AND
drivers_license.gender = 'male' AND drivers_license.plate_number LIKE '%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;
| person_id | event_id | event_name | date |
| 67318 | 4719 | The Funky Grooves Tour| 20180115 |
| 67318 | 1143 | SQL Symphony Concert | 20171206 |
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=20180109
AND check_in_time < 1700
AND 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.id
WHERE get_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
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
Tesla
Model S
- She attended
SQL Symphony Concert 3
, 3 times in December 2017
SELECT * FROM person
JOIN drivers_license ON person.license_id = drivers_license.id
WHERE
drivers_license.gender = 'female' AND
drivers_license.hair_color = 'red' AND
(height >= 65 AND 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 |
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;
SELECT * FROM interview WHERE person_id = 99716;
- Did not give an interview
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! |