vinaydawani · August 5, 2021 00:49
diff --git a/knightlabs_mystery.sql b/knightlabs_mystery.sql
 SELECT *
 FROM crime_scene_report
 WHERE
    type = 'murder'
    AND city = "SQL City"
    AND date = "20180115"
 --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".	SQL City


 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


 SELECT *
 FROM person
 WHERE
    address_street_name = "Northwestern Dr"
    AND address_number = (
        SELECT MAX(address_number)
        FROM person
        WHERE address_street_name = "Northwestern Dr"
    )
 --id	name	license_id	address_number	address_street_name	ssn
 --14887	Morty Schapiro	118009	4919	Northwestern Dr	111564949


 SELECT *
 FROM interview
 Where person_id IN (14887, 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".
 -- 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.


 --Following up 1st lead from MORTY
 SELECT *
 FROM get_fit_now_member
 WHERE id LIKE "48Z%" AND membership_status = "gold"
 -- 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
 -- 423327	30	70	brown	brown	male	0H42W2	Chevrolet	Spark LS
 -- 664760	21	71	black	black	male	4H42WR	Nissan	Altima


 -- Following up 2nd lead from Annabel
 SELECT *
 FROM get_fit_now_check_in
 WHERE check_in_date = 20180109
 -- membership_id	check_in_date	check_in_time	check_out_time
 -- X0643	20180109	957	1164
 -- UK1F2	20180109	344	518
 -- XTE42	20180109	486	1124
 -- 1AE2H	20180109	461	944
 -- 6LSTG	20180109	399	515
 -- 7MWHJ	20180109	273	885
 -- GE5Q8	20180109	367	959
 -- 48Z7A	20180109	1600	1730
 -- 48Z55	20180109	1530	1700
 -- 90081	20180109	1600	1700


 SELECT *
 FROM person
 WHERE license_id IN (183779, 423327, 664760)
 -- id	name	license_id	address_number	address_street_name	ssn
 -- 51739	Tushar Chandra	664760	312	Phi St	137882671
 -- 67318	Jeremy Bowers	423327	530	Washington Pl, Apt 3A	871539279
 -- 78193	Maxine Whitely	183779	110	Fisk Rd	137882671

 -- SOLUTION: Jeremy Bowers
	SELECT *
	FROM crime_scene_report
	WHERE
	type = 'murder'
	AND city = "SQL City"
	AND date = "20180115"
	--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". SQL City


	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


	SELECT *
	FROM person
	WHERE
	address_street_name = "Northwestern Dr"
	AND address_number = (
	SELECT MAX(address_number)
	FROM person
	WHERE address_street_name = "Northwestern Dr"
	)
	--id name license_id address_number address_street_name ssn
	--14887 Morty Schapiro 118009 4919 Northwestern Dr 111564949


	SELECT *
	FROM interview
	Where person_id IN (14887, 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".
	-- 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.


	--Following up 1st lead from MORTY
	SELECT *
	FROM get_fit_now_member
	WHERE id LIKE "48Z%" AND membership_status = "gold"
	-- 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
	-- 423327 30 70 brown brown male 0H42W2 Chevrolet Spark LS
	-- 664760 21 71 black black male 4H42WR Nissan Altima


	-- Following up 2nd lead from Annabel
	SELECT *
	FROM get_fit_now_check_in
	WHERE check_in_date = 20180109
	-- membership_id check_in_date check_in_time check_out_time
	-- X0643 20180109 957 1164
	-- UK1F2 20180109 344 518
	-- XTE42 20180109 486 1124
	-- 1AE2H 20180109 461 944
	-- 6LSTG 20180109 399 515
	-- 7MWHJ 20180109 273 885
	-- GE5Q8 20180109 367 959
	-- 48Z7A 20180109 1600 1730
	-- 48Z55 20180109 1530 1700
	-- 90081 20180109 1600 1700


	SELECT *
	FROM person
	WHERE license_id IN (183779, 423327, 664760)
	-- id name license_id address_number address_street_name ssn
	-- 51739 Tushar Chandra 664760 312 Phi St 137882671
	-- 67318 Jeremy Bowers 423327 530 Washington Pl, Apt 3A 871539279
	-- 78193 Maxine Whitely 183779 110 Fisk Rd 137882671

	-- SOLUTION: Jeremy Bowers
No results found