Réviser SQL avec 'SQL Murder Mystery'

SQL Murder Mystery est un site proposant de résoudre une enquête policière en mettant en pratique SQL. Une seule indication est donnée au départ : “the crime was a ​murder​ that occurred sometime on ​Jan.15, 2018​ and that it took place in ​SQL City”. Tout l’exercice consiste à requêter une base de données sous-jacente que les services de police auraient constitués. Celle-ci est schématisée par un Entity-Relationships-Diagram. Il faut donc trouver la logique en requêtant les tables à la suite les unes des autres pour déduire qui est le criminel puis le commanditaire.

1 : Retrouver la description du crime_scene_report

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

On apprend dans le champ description de crime_scene_report : “The first witness lives at the last house on “Northwestern Dr”. The second witness, named Annabel, lives somewhere on “Franklin Ave”.”

2.1 : Trouver qui est le premier témoin et son id

SELECT address_number 
WHERE address_street_name = 'Northwestern Dr`
ORDER BY address_number DESC #pour trouver directement le numéro de la dernière maison de la rue

La dernière maison de la rue est logiquement le numéro le plus élévé de la rue : 4919 address_number

On cherche ensuite à connaître tous les détails de ce témoin vivant au 4919 rue Northwestern Dr.

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

On obtient :

2.2 Trouver l’id du second témoin

On sait que le second témoin se nomme Annabel et vie dans la rue Franklin. La colonne name de la table person est composée du prenom ET du nom. Or nous n’avons que le prénom, il faut donc utiliser une méthode SQL de matching partiel.

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

On obtient :

3. Trouver la retranscription des interrogatoires de chaque témoin

On a l’id de chaque témoin. Retrouvons l’intérrogatoire de temoin n°1 :

SELECT * FROM interview
WHERE person_id = '14887'

On obtient :

Retrouvons l’interrogatoire de témoin n°2 :

SELECT * FROM interview
WHERE person_id = '16371'

On obtient :

4. Tracer le membre de la salle de sport aperçu

On sait que le suspect à un sac qui commence par “48Z”. Seuls les membres “gold” ont ces sacs. Le suspect est rentré dans une voiture avec une plaque incluant le n° “H42W”.

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

On obtient :

Il y a 2 résultats : on se souvient que le 2ème témoin se souvient que les évènements se sont déroulés le 9 Janvier. Or le premier résultat a un membership_start_date de 20160305. Il ne peut donc s’agir que de Jeremy Bowers.

L’interface nous le confirme : “Congrats, you found the murderer! But wait, there’s more… If you think you’re up for a challenge, try querying the interview transcript of the murderer to find the real villain behind this crime”.

5. Continuons avec le challenge proposé : retrouvez l’interrogatoire du meutrier

SELECT * FROM interview
WHERE person_id = '67318'

On obtient :

6. Trouver qui est allé 3 fois au concert "SQL Symphony Concert " en Décembre 2017

SELECT * FROM facebook_event_checkin
WHERE event_name = 'SQL Symphony Concert'
AND date LIKE '201712__'
GROUP BY person_id
HAVING COUNT(person_id) = 3

On obtient 2 entrées :

Il nous faut donc affiner pour savoir laquelle des deux personnes est la commanditaire

7. Trouver laquelle des deux personnes conduit une Tesla Model S

On procède à une jointure des tables person et drivers_license basé sur l’id des 2 personnes étant allées à l’évènement.

SELECT *
FROM drivers_license
JOIN person
ON person.license_id = drivers_license.id
WHERE person.id = '24556' OR person.id = '99716';

On obtient une seule entrée :

L’interface nous le confirme : “Congrats, you found the brains behind the murder! Everyone in SQL City hails you as the greatest SQL detective of all time. Time to break out the champagne!”.

Ce que j’ai appris

1. Le matching partiel SQL avec % et _
2. GROUP BY et HAVING
3. La jointure de base en SQL