Rikostapahtuma

Murhamysteeri löytyy osoitteesta: https://mystery.knightlab.com/

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"

1.Silminnäkijä

viimeinen talo Northwestern Dr:lla on 4919

Kysely:

SELECT MAX(address_number)
FROM person
WHERE address_street_name = 'Northwestern Dr';

Siellä asuu:

id	name	license_id	address_number	address_street_name	ssn
14887	Morty Schapiro	118009	4919	Northwestern Dr	111564949

Kysely:

SELECT *
FROM person
WHERE address_street_name = 'Northwestern DR' AND address_number = 4919;

Mortyn kuulustelupöytäkirja:

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

Kysely:

SELECT *
FROM interview
WHERE person_id = 14887;

2. Silminnäkijä

id	name	license_id	address_number	address_street_name	ssn
16371	Annabel Miller	490173	103	Franklin Ave	318771143

Kysely:

SELECT *
FROM person
WHERE address_street_name = 'Franklin Ave' AND name LIKE 'Annabel%';

Annabelin kuulustelupöytäkirja:

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.

Kysely:

SELECT *
FROM interview
WHERE person_id = 16371;

Gold membership selvitys:

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

Tulos:

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

Kumman auto oli kyseessä:

SELECT drivers_license.id AS license, person.id AS person
FROM drivers_license INNER JOIN person ON drivers_license.id = person.license_id
WHERE plate_number LIKE '%H42W%' AND Gender = 'male' AND person.id IN (28819, 67318);

Tulos:

67318 Jeremy Bowers

Murhan tilaajan selvittäminen

Haetaan Jeremy Bowersin kuulustelupöytäkirja:

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 interview
WHERE person_id = 67318;

Selvitetään ajokorttitiedoista kuvaukseen sopivat henkilöt.

SELECT *
FROM drivers_license
WHERE gender = 'female' and hair_color = 'red' AND car_make = 'Tesla'
	AND car_model = 'Model S' AND height BETWEEN 65 AND 67;

Selvitetään konserttitiedot facebook-taulun avulla.

SELECT person_id 
FROM facebook_event_checkin
WHERE event_name = 'SQL Symphony Concert' AND date BETWEEN 20171201 AND 20171231
GROUP BY person_id
HAVING COUNT(person_id) = 3;

Kun molemmista kyselyistä tehdään joukko-opillinen leikkaus (intersection) edellisten tulosjoukkojen välillä

SELECT name
FROM person
WHERE id IN (SELECT person_id 
FROM facebook_event_checkin
WHERE event_name = 'SQL Symphony Concert' AND date BETWEEN 20171201 AND 20171231
GROUP BY person_id
HAVING COUNT(person_id) = 3
INTERSECT              
SELECT person.id
FROM drivers_license INNER JOIN person ON drivers_license.id = person.license_id
WHERE gender = 'female' and hair_color = 'red' AND car_make = 'Tesla'
	AND car_model = 'Model S' AND height BETWEEN 65 AND 67);

Tulos on: Miranda Priestly