Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*
select * from crime_scene_report where type='murder' and city='SQL City';
date type description city
20180215 murder REDACTED REDACTED REDACTED SQL City
20180215 murder Someone killed the guard! He took an arrow to the knee! SQL 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 interview where person_id in (select id from person where address_street_name like '%Northwestern Dr%' or address_street_name like '%Franklin Ave%');
Perhaps a way to narrow it down more, but the last two rows say:
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".
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.
select * from person where id in (14887, 16371);
id name license_id address_number address_street_name ssn
14887 Morty Schapiro 118009 4919 Northwestern Dr 111564949
16371 Annabel Miller 490173 103 Franklin Ave 318771143
select * from get_fit_now_check_in WHERE check_in_date=20180109 and membership_id like '48Z%';
membership_id check_in_date check_in_time check_out_time
48Z7A 20180109 1600 1730
48Z55 20180109 1530 1700
select * from drivers_license where plate_number like '%H42W%';
id age height eye_color hair_color gender plate_number car_make car_model
183779 21 65 blue blonde female H42W0X Toyota Prius
423327 30 70 brown brown male 0H42W2 Chevrolet Spark LS
664760 21 71 black black male 4H42WR Nissan Altima
select * from get_fit_now_check_in where membership_id in (select id from get_fit_now_member where person_id in (select id from person where license_id in (423327, 664760)));
membership_id check_in_date check_in_time check_out_time
48Z55 20180109 1530 1700
select * from person where id in (select person_id from get_fit_now_member where id='48Z55');
id name license_id address_number address_street_name ssn
67318 Jeremy Bowers 423327 530 Washington Pl, Apt 3A 871539279
Check your solution
Did you find the killer?
INSERT INTO solution VALUES (1, 'Jeremy Bowers');
SELECT value FROM solution;
value
Congrats, you found the murderer! But wait, there's more... If you think you're up for a challenge, try querying the interview transcript of the murderer to find the real villian behind this crime. If you feel especially confident in your SQL skills, try to complete this final step with no more than 2 queries. Use this same INSERT statement to check your answer.
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.
select * from drivers_license where hair_color='red' and car_make='Tesla';
id age height eye_color hair_color gender plate_number car_make car_model
202298 68 66 green red female 500123 Tesla Model S
291182 65 66 blue red female 08CM64 Tesla Model S
736081 79 69 brown red male GCAQ6Y Tesla Model S
918773 48 65 black red female 917UU3 Tesla Model S
select * from facebook_event_checkin where person_id in (select id from person where license_id in (202298, 291182, 736081, 918773));
person_id event_id event_name date
57410 3841 harder. 20170610
57410 6505 God did not create the world in 7 days; he screwed around for 6 days 20171119
57410 1649 knows what it is. 20170701
99716 1143 SQL Symphony Concert 20171206
99716 1143 SQL Symphony Concert 20171212
99716 1143 SQL Symphony Concert 20171229
select * from person where id=99716;
id name license_id address_number address_street_name ssn
99716 Miranda Priestly 202298 1883 Golden Ave 987756388
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!
*/
@bikemule

This comment has been minimized.

Copy link
Owner Author

@bikemule bikemule commented Dec 16, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment