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

Kysely:

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

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

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

2. Silminnäkijä

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

Kysely

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

1. Silminnäkijän kuulustelu

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

Vaihtoehdot kassin perusteella

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

kysely

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

Vaihtoehdot rekisterin ja kassin perusteella

Henkilöid on 67318 eli murhaaja on Jeremy Bowers

kysely

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);

2. Silminnäkijän kuulustelu

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.

Mahdolliset murhaajat:

membership_id	person_id
48Z55	67318
48Z7A	28819

Kysely

SELECT membership_id, person_id
FROM get_fit_now_check_in INNER JOIN get_fit_now_member ON membership_id = id
WHERE check_in_date = 20180109 AND membership_id IN ('48Z7A', '48Z55');

Kuulustelujen yhdistäminen

Kyselyt voidaan myös yhdistää, jolloin muodostuu kaksi sisäkkäistä alikyselyä (SELECT-lause listan IN sisällä)

SELECT name
FROM person
WHERE id IN 
	(SELECT person.id
	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
			(SELECT person_id
			FROM get_fit_now_member
			WHERE membership_status = 'gold' AND id LIKE '48Z%')
	INTERSECT
	SELECT person_id
	FROM get_fit_now_check_in INNER JOIN get_fit_now_member ON membership_id = id
	WHERE check_in_date = 20180109)

Sergein ratkaisu

Sergein ratkaisussa on käytetty pelkkiä liitosehtoja:

-- [13.30] Sergei Vasilyev
SELECT drivers_license.id AS license, person.id, person.name
FROM drivers_license
   INNER JOIN person ON license_id = drivers_license.id
   INNER JOIN get_fit_now_member ON person.id = person_id
   INNER JOIN get_fit_now_check_in ON get_fit_now_member.id = membership_id
WHERE gender = 'male'
   AND plate_number LIKE '%H42W%'
   AND membership_id LIKE '48Z%'
   AND check_in_date = 20180109

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

Sergein ratkaisu

Toinen lähestymistapa on määritellä enemmän taulujen välisiä liitoksia:

-- [13.16] Sergei Vasilyev
SELECT person.id, person.name, event_name, COUNT(person_id)
FROM drivers_license
    INNER JOIN person ON license_id = drivers_license.id
    INNER JOIN facebook_event_checkin ON person.id = facebook_event_checkin.person_id
WHERE gender = 'female'
   AND car_model = 'Model S'
   AND car_make = 'Tesla'
   AND hair_color = 'red'
   AND height BETWEEN 65 AND 67
   AND event_name = 'SQL Symphony Concert'
   AND date BETWEEN 20171201 AND 20171231

Murhan tilaajaksi selvisi Miranda Priestly