SQL Murder Mystery Game
Fun fact: I did the murder mystery game about two years ago but didn’t document it.
I found the SQL Murder Mystery Game challenge here and you can try it out too.
Overview:
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.
Here are the database and my code. Enjoy my detective skills.
Understanding The Database
- Run this query to find the names of the tables in this database.
- Run this query to find the structure of the `crime_scene_report` table
**Change the value of ‘name’ to see the structure of the other tables
Querying The Database
- Retrieving the crime scene report from the police department’s database.
Here are the full details of both witnesses so we can retrieve their transcripts from the database
Using person_id, we can retrieve their transcripts
Using information from both witnesses’ transcripts, I created 2 views which can later be joined to bring IDs present in both views
Joining both views together, Jeremy Bowers with get_fit_now membership id 48Z55 and person_id 67318 is our only suspect
Inserting the solution
Further Query
Oh wow, Mr. Jeremy Bowers was hired by a woman to do the dirty work. I wonder why she wanted that young person dead tho??
Ms Miranda Priestly is the person who hired Mr Jeremy Bowers to murder someone but has she been investigated by the Police yet?
No, she hasn’t. Oh well, I guess she does have a lot of money.
Over to you Detective.
