SQL Murder Mystery -- Corrigé

Lien vers l'enquête.

Correction en utilisant JOIN pour obtenir chaque information en une seule requête. On peut faire sans mais il faut alors faire plusieurs requêtes en réutilisant des résultats intermédiaires.

Schéma des tables disponibles :

Lire le rapport sur le crime (un meurtre qui a lieu le 15 jan. 2018 à SQL City)

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

Lire l'interview du 1er témoin

SELECT transcript FROM interview
JOIN person ON interview.person_id = person.id
WHERE address_street_name = 'Northwestern Dr'
ORDER BY address_number DESC
LIMIT 1

Lire l'interview du 2e témoin

SELECT transcript FROM interview
JOIN person ON interview.person_id = person.id
WHERE address_street_name = 'Franklin Ave'
AND name LIKE 'Annabel%'

Enquête sur les infos du 1er témoin

SELECT person.name FROM person
JOIN get_fit_now_member AS gym ON gym.person_id = person.id
JOIN drivers_license AS permis ON permis.id = person.license_id
WHERE gym.id LIKE '48Z%' AND permis.plate_number LIKE '%H42W%'

Enquête sur les infos du 2e témoin

À quelle heure Annabel était à la salle de sport le 9 janvier ?

SELECT check_in_time,check_out_time FROM get_fit_now_check_in AS checkin
JOIN get_fit_now_member AS gym ON gym.id = checkin.membership_id
WHERE name LIKE 'Annabel%' AND check_in_date=20180109

Qui d'autre y était à ce moment là ?

SELECT name FROM get_fit_now_member AS gym
JOIN get_fit_now_check_in AS checkin ON checkin.membership_id = gym.id
WHERE check_in_date=20180109 AND check_in_time <= 1700 AND check_out_time >= 1600

Qui a commandité le crime ?

Lire l'interview du meurtrier :

SELECT transcript FROM interview
JOIN person ON person.id = interview.person_id
WHERE name='Jeremy Bowers'

Trouver la personne avec toutes ces infos, en une requête (et on vérifie qu'elle est bien riche):

SELECT person.name,car_make,car_model,height,event_name,date,annual_income 
FROM person

JOIN drivers_license AS permis ON person.license_id = permis.id
JOIN facebook_event_checkin AS fb ON fb.person_id = person.id
JOIN income ON person.ssn = income.ssn

WHERE hair_color='red' AND car_make = 'Tesla' AND car_model = 'Model S'
AND height >= 65 AND height <= 67 
AND event_name LIKE 'SQL%'