The SQL Murder Mystery Walk Through

SQL-Murder-Mystery-Solution.md

Start

Key to The SQL Murder Mystery

See the Clue As Given in Question

The crime was a murder that occurred sometime on Jan.15, 2018 and that it took place in SQL City

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

Output

Description- 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".

ClueNo.1 Now we have a Named And Unknown Witness

Now Let Check For Murder Keyword in Interview Table

select * from interview
where lower(transcript) like '%murder%';

Output

| person_id | transcript |

| 14849 | murder to leave it behind?’ She said the last words out loud, and the |

| 15121 | Queen jumped up and bawled out, “He’s murdering the time! Off with his |

| 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.|

ClueNo.2 Here We got a Clue that 16371 is a witness and in the same gym as murderer with details about it

Check for 16371 in Person

select * from person 
where id = 16371;

Output

| id | name | license_id |address_number | address_street_name | ssn |

| 16371 | Annabel Miller | 490173 | 103 | Franklin Ave | 318771143 |

ClueNo.3 Here We found that Annabel Miller Is Our Secret Witness

ClueNo.4 We know that Murderer is in Same Gym As Witness and Was working out on January 9th and was in Same Place as Witness On Day Of Murder.

Let check For these Clue

select * from facebook_event_checkin f
 join (select *
 from person n
 join (select * from get_fit_now_member m
 join get_fit_now_check_in c on m.id = c.membership_id
 where check_in_date =20180109) p
 on n.id = p.person_id) q
 on f.person_id = q.id
 where date = 20180115;

You can check the name by inserting it in the next query box

Hooray We Found That Jeremy Bowers is the murderer but He is not mastermind behind it.Lets find the mastermind

ClueNo.5 We got to know that Jeremy Bowers Has Confession in Interview.

So lets check that with his Person_id = 67318

select * from interview
where person_id = 67318;

Output

Transcript-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.

ClueNo.6 We Got Some Clue about her Car and her Hair color and Height And Also that she is rich.

Lets Input These Clue

select name,max(annual_income) from person p
join drivers_license d
on p.license_id = d.id
join income i
on p.ssn = i.ssn
where car_make = 'Tesla' 
and hair_color = 'red';

Output

name max(annual_income) Miranda Priestly 310000

You can check the name by inserting it in the next query box

Hooray and Congratulations

We have Found the MasterMind Behind The Murder - Miranda Priestly

This Murder Is Now Solved ✨ 😆