SQL Murder Mystery
SQL Murder Mystery
You vaguely remember that the crime was a murder that occurred sometime on Jan.15, 2018 and that it took place in SQL City
SELECT city FROM crime_scene_report
SELECT city FROM crime_scene_report WHERE city = "SQL City"
SELECT date FROM crime_scene_report WHERE city = "SQL City"
SELECT date FROM crime_scene_report WHERE city = "SQL City" AND date = "20180115"
SELECT * FROM crime_scene_report WHERE city = "SQL City" AND date = "20180115"
SELECT * FROM crime_scene_report WHERE city = "SQL City" AND date = "20180115" AND type="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”.
First witness :
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 |
|---|---|---|---|---|---|
| 14887 | Morty Schapiro | 118009 | 4919 | Northwestern Dr | 111564949 |
Second witness :
SELECT * FROM person WHERE address_street_name = "Franklin Ave" AND name LIKE "%Annabel%"
| id | name | license_id | address_number | address_street_name | ssn |
|---|---|---|---|---|---|
| 16371 | Annabel Miller | 490173 | 103 Franklin Ave | 318771143 |
First witness interview
SELECT * FROM interview WHERE person_id = 14887
| person_id | transcript |
|---|---|
| 14887 | 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”. |
Second witness interview
SELECT * FROM interview WHERE person_id = 16371
| person_id | transcript |
|---|---|
| 16371 | I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th. |
let’s check if second witness was working out on January the 9th
SELECT * FROM get_fit_now_member WHERE person_id = 16371
| id | person_id | name | membership_start_date | membership_status | | —– | —– | —– | —– | —– | | 90081 | 16371 | Annabel Miller | 20160208 | gold |
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 |
We can confirm that this member was at the gym on the same day
Check the first witness interview Search for a “48Z” membership number, gold member, with a car plate including “H42W”
SELECT * FROM get_fit_now_check_in
JOIN get_fit_now_member ON get_fit_now_check_in.membership_id = get_fit_now_member.id
JOIN person ON get_fit_now_member.person_id = person.id
JOIN drivers_license ON person.license_id = drivers_license.id
WHERE membership_id LIKE "%48Z%"
AND membership_status = "gold"
AND plate_number LIKE "%H42W%"
| membership_id | check_in_date | check_in_time | check_out_time | 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 | 20180109 | 1530 | 1700 | 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 |
Check this person’s interview | 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. |
