stream-sql.sql

url - https://mystery.knightlab.com/

You vaguely remember that the crime was a ​murder​ that occurred sometime on ​Jan.15, 2018​ and that it took place in ​SQL City​. 

The SQL Murder Mystery is built using SQLite

name

crime_scene_report 
drivers_license
person
facebook_event_checkin
interview
get_fit_now_member
get_fit_now_check_in
income
solution

CREATE TABLE crime_scene_report ( date integer, type text, description text, city text )

SELECT * FROM crime_scene_report WHERE date = 20180115 AND 
city = 'SQL City' AND type = 'murder'

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

SELECT sql 
  FROM sqlite_master
 where name = 'person'

CREATE TABLE person ( id integer PRIMARY KEY, name text, license_id integer, address_number integer, address_street_name text, ssn integer, FOREIGN KEY (license_id) REFERENCES drivers_license(id) )

SELECT * FROM person WHERE address_street_name = 'Northwestern Dr' ORDER BY address_number ASC
id	name	license_id	address_number	address_street_name	ssn
14887	Morty Schapiro	118009	4919	Northwestern Dr	111564949

SELECT * FROM person WHERE name = 'Annabel' AND address_street_name = 'Franklin Ave'
id	name	license_id	address_number	address_street_name	ssn
16371	Annabel Miller	490173	103	Franklin Ave	318771143

SELECT * FROM drivers_license WHERE id = 490173

Morty
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

Annabel 
id	age	height	eye_color	hair_color	gender	plate_number	car_make	car_model
490173	35	65	green	brown	female	23AM98	Toyota	Yaris

SELECT * FROM get_fit_now_member WHERE person_id = 14887

id	person_id	name	membership_start_date	membership_status
90081	16371	Annabel Miller	20160208	gold

SELECT * FROM facebook_event_checkin WHERE person_id = 16371

Morty
person_id	event_id	event_name	date
14887	4719	The Funky Grooves Tour	20180115

Annabel
person_id	event_id	event_name	date
16371	4719	The Funky Grooves Tour	20180115

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

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

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.

SELECT * FROM get_fit_now_member WHERE membership_status = 'gold' AND id LIKE '48Z%'

id	person_id	name	membership_start_date	membership_status
48Z7A	28819	Joe Germuska	20160305	gold
48Z55	67318	Jeremy Bowers	20160101	gold

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

---target
423327	30	70	brown	brown	male	0H42W2	Chevrolet	Spark LS
664760	21	71	black	black	male	4H42WR	Nissan	Altima
-----

SELECT * FROM person WHERE license_id = 664760

id	name	license_id	address_number	address_street_name	ssn
67318	Jeremy Bowers	423327	530	Washington Pl, Apt 3A	871539279

id	name	license_id	address_number	address_street_name	ssn
51739	Tushar Chandra	664760	312	Phi St	137882671

SELECT * FROM get_fit_now_member WHERE person_id = 51739

id	person_id	name	membership_start_date	membership_status
48Z55	67318	Jeremy Bowers	20160101	gold

select p.name,gf.id, gf.membership_status from drivers_license dl inner join person p on p.license_id = dl.id inner join get_fit_now_member gf on gf.person_id = p.id WHERE plate_number LIKE '%H42W%'

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.

SELECT * from drivers_license WHERE car_model = 'Model S' AND gender = 'female'

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
918773	48	65	black	red	female	917UU3	Tesla	Model S


SELECT * from facebook_event_checkin WHERE event_name = 'SQL Symphony Concert' AND date = 20180109

SELECT * from person where license_id = 291182

id	name	license_id	address_number	address_street_name	ssn
78881	Red Korb	918773	107	Camerata Dr	961388910

id	name	license_id	address_number	address_street_name	ssn
90700	Regina George	291182	332	Maple Ave	337169072

d	name	license_id	address_number	address_street_name	ssn
99716	Miranda Priestly	202298	1883	Golden Ave	987756388

SELECT * from income where ssn = 337169072

ssn	annual_income
961388910	278000

ssn	annual_income
987756388	310000

SELECT * from Interview WHERE person_id = 99716