Un meurtre à SQL City

Enquête adaptée de SQL Murder Mystery, Joon Park & Cathy He, CC-BY-SA 4.0. Le schéma et le déroulé de l'enquête sont ceux de l'original ; les données ont été régénérées et traduites pour ce cours.

Un meurtre à SQL City#

Un crime a eu lieu à SQL City et vous devez jouer les détectives pour trouver le meurtrier. Pour résoudre ce mystère, vous avez accès à toute la base de données que détient la police criminelle. Le schéma relationnel de cette base de données se trouve plus bas sur cette page.

Pour débuter votre enquête, la police ne vous donne aucun indice, mais savez simplement que le crime que vous devez investiguer est un meurtre qui a eu lieu le 15 janvier 2018 à SQL City. Commencez par investiguer la table crime_scene_report pour trouver le rapport établi sur la scène du crime.

Quand vous aurez trouvé le coupable, vérifiez votre solution en bas de la page. Bonne chance !

Schéma relationnel de la base de données de la police 

1

Le meurtrier est :

Le commanditaire#

Si vous avez trouvé le meurtrier, félicitations ! Toutefois, bien que vous ayez trouvé la personne ayant commis le meurtre, ce n'est pas elle qui l'a commandité. Pour trouver le cerveau derrière cette opération, allez lire le ce que le meurtrier raconte dans son interrogatoire de la table interview.

Le commanditaire est :

Solution

Nous ne connaissons pas encore les jointures (JOIN) : nous allons donc croiser les tables à la main. À chaque étape, on note les identifiants (id) trouvés, et on les réutilise dans la requête suivante.

Partie 1 : Trouver le meurtrier

1. Lire le rapport du crime. C'est un meurtre commis le 15 janvier 2018 à SQL City.

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

Le rapport parle de deux témoins : le premier habite la dernière maison de Northwestern Dr, le second, prénommé Annabel, habite sur Franklin Ave.

2. Retrouver le premier témoin. La rue compte une centaine de maisons : on ne peut pas deviner « la dernière » à l'œil. « La dernière maison » = le plus grand numéro, donc on trie par address_number décroissant et on ne regarde que la première ligne.

SELECT * FROM person
WHERE address_street_name = 'Northwestern Dr'
ORDER BY address_number DESC;

La première ligne est Morty Schapiro (numéro 4919). On note son id.

3. Retrouver le second témoin. La rue Franklin Ave compte elle aussi une centaine d'habitants, et il y a une vingtaine de personnes prénommées Annabel : il faut donc combiner le prénom et la rue.

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

Une seule personne : Annabel Miller. On note son id.

4. Lire les deux dépositions. On réutilise les deux id notés (remplacez <id_morty> et <id_annabel> par les valeurs trouvées aux étapes 2 et 3).

SELECT * FROM interview WHERE person_id = <id_morty>;

SELECT * FROM interview WHERE person_id = <id_annabel>;

Morty décrit le tueur : un homme, membre gold de la salle « Get Fit Now », dont le numéro de sac commence par 48Z, et qui est monté dans une voiture dont la plaque contient H42W. Annabel précise qu'elle l'a croisé à la salle le 9 janvier (20180109).

5. Les membres « gold » dont le numéro commence par 48Z.

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

On obtient six membres (chercher juste 48Z, ou juste gold, en renverrait beaucoup plus). On note leur id de membre et leur person_id.

6. Qui est venu à la salle le 9 janvier ?

SELECT * FROM get_fit_now_check_in WHERE check_in_date = 20180109;

Cette liste est longue (une centaine de passages) : impossible d'y repérer le tueur directement. C'est le croisement avec l'étape 5 qui va la réduire.

7. Croiser à la main. Parmi les six membres de l'étape 5, on ne garde que ceux dont l'id de membre apparaît aussi dans la liste du 9 janvier. Il n'en reste que cinq suspects.

8. Départager grâce à la plaque. On liste d'abord les plaques contenant H42W :

SELECT * FROM driver_license WHERE plate_number LIKE '%H42W%';

Puis, pour chacun des cinq suspects, on remonte à son permis : on lit d'abord son license_id dans person, puis sa plaque dans driver_license.

SELECT license_id FROM person WHERE id = <person_id_du_suspect>;

SELECT plate_number FROM driver_license WHERE id = <license_id_trouvé>;

Un seul des cinq suspects a une plaque contenant H42W : Jeremy Bowers. C'est le meurtrier.

Partie 2 : Trouver le commanditaire

9. Lire la déposition du meurtrier (avec son person_id, trouvé à l'étape 8).

SELECT * FROM interview WHERE person_id = <id_jeremy_bowers>;

Il décrit la personne qui l'a engagé : une femme, aux cheveux roux, mesurant entre 65 et 67, qui conduit une Tesla Model S et qui est allée trois fois au SQL Symphony Concert en décembre 2017.

10. Filtrer les permis avec tout le signalement physique. Pris séparément, chaque critère renvoie une centaine de lignes (une centaine de Tesla Model S, une centaine de femmes rousses...). Il faut donc les cumuler tous avec AND.

SELECT * FROM driver_license
WHERE gender = 'female' AND hair_color = 'red'
  AND height >= 65 AND height <= 67
  AND car_make = 'Tesla' AND car_model = 'Model S';

Il ne reste que six femmes. On note leur id de permis.

11. Retrouver chaque personne, puis vérifier le concert. Pour chaque permis, on retrouve la personne grâce à son license_id, on note son id...

SELECT * FROM person WHERE license_id = <id_du_permis>;

... puis on regarde combien de fois cette personne est allée au concert en décembre 2017 :

SELECT * FROM facebook_event_checkin
WHERE person_id = <id_de_la_personne>
  AND event_name = 'SQL Symphony Concert'
  AND date >= 20171201 AND date <= 20171231;

Une seule des cinq femmes y est allée trois fois : Miranda Priestly. C'est elle, le cerveau de l'affaire.