TP Bonus : Enquête SQL

Sommaire

• Avant de commencer
• Les règles du jeu
• Le plan de la ville : les tables
• À vous de jouer : choisissez votre enquête
• Enquête n° 1 : on la fait ensemble
  • 1. Le rapport de police
  • 2. Retrouver les deux témoins
  • 3. Lire leurs dépositions
  • 4. Traduire chaque indice en condition SQL
  • 5. Valider
• Enquêtes suivantes : indices et solutions
  • Le meurtre de SQL Ville
  • Le braquage du SQL Express
  • La formule du professeur Noside
  • Panique à la septième séance
  • Menace sur Nova City
  • L'Infiltré de Little Italy
• Pour aller plus loin
• Conclusion

Vous savez maintenant écrire des requêtes SQL et les exécuter depuis PHP avec PDO. Il est temps de vérifier que vous savez aussi lire des données pour en tirer une conclusion. Un crime a été commis à SQL Ville, la police a une base de données… et rien d'autre. C'est à vous de jouer !

Ce TP est une adaptation en français du SQL Murder Mystery de NUKnightLab, avec plusieurs histoires différentes pour ne pas refaire toujours la même enquête.

Pour les étudiants en avance

Ce TP est un bonus : il n'est pas noté et ne demande aucun rendu. Vous pouvez le faire en autonomie, seul ou à deux, dès que vous avez terminé le TP en cours. Comptez environ une heure par histoire.

Avant de commencer

Il vous faut le TP 5 SQL (SELECT, WHERE, jointures) et l'aide-mémoire SQL sous la main. À la fin, vous saurez explorer une base que vous n'avez pas conçue, croiser plusieurs tables avec JOIN, et réduire 10 000 habitants à un seul nom avec LIKE, BETWEEN, ORDER BY et GROUP BY … HAVING.

Rattrapage express : les mots-clés dont vous aurez besoin

• SELECT … FROM … WHERE … : filtrer des lignes.
• LIKE 'abc%', LIKE '%abc', LIKE '%abc%' : commence par, se termine par, contient.
• BETWEEN a AND b : un intervalle (dates et nombres).
• ORDER BY … DESC LIMIT 1 : la plus grande valeur.
• JOIN table t ON t.colonne = autre.colonne : relier deux tables.
• GROUP BY … HAVING COUNT(*) = n : compter par personne.

Les règles du jeu

• Toutes les histoires se passent dans la même base : mêmes tables, mêmes colonnes. Seules les données changent.
• Chaque enquête commence par la table rapport_police. Le rapport vous dit comment trouver les témoins, les témoins vous décrivent le coupable, et parfois le coupable vous mène plus loin encore.
• Les dates sont stockées sous la forme d'un nombre entier AAAAMMJJ (le 15 janvier 2018 s'écrit 20180115), les heures sous la forme HHMM (1830 pour 18h30).
• Quand vous pensez avoir trouvé, vous accusez quelqu'un en insérant son nom dans la table solution. La base vous répond si c'est la bonne personne… et si l'enquête continue :

INSERT INTO solution VALUES (1, 'Prénom Nom');
SELECT valeur FROM solution;

• Le journal de bord au-dessus de l'éditeur coche les étapes au fur et à mesure de vos INSERT réussis (dans votre navigateur uniquement).

Interdit

Pas de SELECT * FROM personne en espérant repérer le coupable à l'œil : il y a 10 000 habitants. L'objectif est justement d'écrire des requêtes qui réduisent le nombre de résultats jusqu'à n'en avoir qu'un.

La méthode, en quatre étapes (à lire une fois, puis à garder sous le coude)

Quelle que soit l'histoire, la démarche est toujours la même.

Étape 1 : lire le rapport de police

Vous connaissez la date, le type de crime et la ville : c'est un simple filtre.

SELECT * FROM rapport_police
WHERE ville = 'SQL Ville' AND type = 'meurtre' AND date = 20180115;

Attention, il peut y avoir plusieurs rapports le même jour dans la même ville. Lisez la description : elle vous dit comment retrouver les témoins (une rue, un prénom, un numéro, un revenu…).

Étape 2 : identifier les témoins

Le rapport ne donne jamais un nom, seulement une manière de le retrouver. Quelques exemples de formulations et la requête qui va avec :

Le rapport dit…	Vous cherchez…
« la dernière maison de la rue X »	ORDER BY numero_rue DESC LIMIT 1
« le plus petit numéro de la rue X »	ORDER BY numero_rue ASC LIMIT 1
« prénommé Lucas, rue X »	nom LIKE 'Lucas %'
« la personne au revenu le plus élevé de la rue X »	une jointure avec revenu puis ORDER BY revenu_annuel DESC LIMIT 1

Une fois les témoins identifiés, lisez leur interrogatoire : c'est là que se trouvent les indices sur le coupable.

Étape 3 : croiser les indices

Chaque témoin donne un ou plusieurs indices (une plaque, une salle de sport, un événement, une description physique…). Tous les indices sont nécessaires : la base contient volontairement des personnes qui correspondent à presque tout.

Construisez une requête qui part de personne et ajoute une jointure par indice. Testez au fur et à mesure : à chaque condition ajoutée, le nombre de lignes doit diminuer.

Que se passe-t-il derrière ? Quand un indice parle de « trois fois à un événement », un simple WHERE ne suffit pas : il faut compter les participations par personne avec GROUP BY personne_id HAVING COUNT(*) = 3. C'est exactement ce que vous ferez plus tard pour compter les commandes d'un client ou les articles d'un panier.

Étape 4 : valider, puis continuer

Validez avec INSERT INTO solution. Si le message vous dit que l'histoire continue, lisez l'interrogatoire du coupable : il a peut-être été payé par quelqu'un.

Le plan de la ville : les tables

Tout ce que la police sait tient dans ce MCD. Chaque association devient une clé étrangère dans les tables : Posséder donne personne.permis_id, Percevoir donne personne.nir, Être inscrit donne salle_sport_membre.personne_id, Effectuer donne salle_sport_passage.membre_id, Déposer donne interrogatoire.personne_id, et Participer devient la table evenement_participation (avec personne_id, evenement_id, nom_evenement et date). Ce sont exactement les ON de vos jointures ; le schéma des tables est aussi dépliable dans l'éditeur.

À vous de jouer : choisissez votre enquête

Sélectionnez une histoire, lisez le brief, puis écrivez vos requêtes dans l'éditeur. Tout s'exécute dans votre navigateur (rien n'est envoyé sur un serveur) et le bouton « Réinitialiser » remet la base dans son état d'origine.

Histoire : Le meurtre de SQL Ville (15 janvier 2018)Le braquage du SQL Express (14 juin 2019)La formule du professeur Noside (7 février 2020)Panique à la septième séance (30 octobre 2021)Menace sur Nova City (12 avril 2022)L'Infiltré de Little Italy (18 septembre 2023) Télécharger la base (.sqlite)

Le meurtre de SQL Ville

Un meurtre a été commis. Vous ne vous souvenez plus du nom du coupable, seulement que le crime a eu lieu le 15 janvier 2018 à SQL Ville.

Point de départ : la table rapport_police, type meurtre, ville SQL Ville, date 20180115.

Journal de bordÉtape 1 : ?Étape 2 : ?

Schéma de la base (9 tables)

• evenement_participation : personne_id, evenement_id, nom_evenement, date
• interrogatoire : personne_id, transcription
• permis_conduire : id, age, taille, couleur_yeux, couleur_cheveux, genre, immatriculation, marque_voiture, modele_voiture
• personne : id, nom, permis_id, numero_rue, nom_rue, nir
• rapport_police : date, type, description, ville
• revenu : nir, revenu_annuel
• salle_sport_membre : id, personne_id, nom, date_debut_abonnement, statut_abonnement
• salle_sport_passage : membre_id, date_passage, heure_entree, heure_sortie
• solution : utilisateur, valeur

Aide-mémoire : traduire un indice en SQL

L'indice dit…	Table	Condition
la dernière maison / le plus petit numéro de la rue X	personne	WHERE nom_rue = 'X' ORDER BY numero_rue DESC LIMIT 1 (ou ASC)
prénommé Lucas, rue X	personne	nom LIKE 'Lucas %' AND nom_rue = 'X'
le revenu le plus élevé de la rue X	personne + revenu	JOIN revenu r ON r.nir = p.nir … ORDER BY r.revenu_annuel DESC LIMIT 1
ce que dit un témoin	interrogatoire	JOIN interrogatoire i ON i.personne_id = p.id
cheveux roux, entre 165 et 168 cm, 40 à 45 ans	permis_conduire	pc.couleur_cheveux = 'roux' AND pc.taille BETWEEN 165 AND 168
plaque qui commence par / finit par / contient ABC	permis_conduire	pc.immatriculation LIKE 'ABC%' / '%ABC' / '%ABC%'
membre « or », numéro qui commence par 48Z	salle_sport_membre	m.statut_abonnement = 'or' AND m.id LIKE '48Z%'
passé à la salle le 9 janvier 2018 entre 18h et 19h	salle_sport_passage	s.date_passage = 20180109 AND s.heure_entree BETWEEN 1800 AND 1900
allé 3 fois au concert X en décembre 2017	evenement_participation	e.nom_evenement = 'X' AND e.date BETWEEN 20171201 AND 20171231 GROUP BY p.id HAVING COUNT(*) = 3
gagne plus de 200 000 € par an	revenu	r.revenu_annuel > 200000

Base chargée, à vous de jouer.

Vous préférez un vrai client SQL ?

Le lien « Télécharger la base » vous donne le fichier .sqlite. Ouvrez-le avec DB Browser for SQLite, PhpStorm ou la ligne de commande sqlite3. Les requêtes sont exactement les mêmes.

Enquête n° 1 : on la fait ensemble

Pour prendre en main l'outil, nous allons résoudre Le meurtre de SQL Ville ensemble, requête par requête. Sélectionnez cette histoire dans l'éditeur ci-dessus, puis copiez chaque requête et comparez votre résultat au mien. Les autres histoires seront à faire seul.

1. Le rapport de police

Le brief vous donne trois informations : un meurtre, le 15 janvier 2018, à SQL Ville. Le lien « Insérer la requête de départ » écrit cette requête pour vous :

SELECT * FROM rapport_police
WHERE ville = 'SQL Ville' AND type = 'meurtre' AND date = 20180115;

Vous devez obtenir une ligne. Lisez sa description : deux témoins, le premier habite la dernière maison de « Rue du Nord-Ouest », le second se prénomme Annabel et habite « Avenue Franklin ».

Que se passe-t-il si j'enlève le type ?

Essayez ! Vous obtenez trois rapports ce jour-là. C'est le principe de toute l'enquête : chaque condition retire des lignes.

2. Retrouver les deux témoins

« La dernière maison » = le plus grand numéro de la rue. On trie les habitants de cette rue par numéro décroissant et on garde le premier :

SELECT * FROM personne
WHERE nom_rue = 'Rue du Nord-Ouest'
ORDER BY numero_rue DESC
LIMIT 1;

Résultat attendu : Martin Chapuis.

Pour Annabel, on filtre sur le début du nom (le prénom est suivi d'une espace) :

SELECT * FROM personne
WHERE nom_rue = 'Avenue Franklin' AND nom LIKE 'Annabel %';

Résultat attendu : Annabel Meunier.

3. Lire leurs dépositions

Les dépositions sont dans interrogatoire, reliée à personne par personne_id (suivez la flèche sur le schéma). Plutôt que de recopier les id, faisons une jointure et filtrons sur les noms :

SELECT p.nom, i.transcription
FROM interrogatoire i
JOIN personne p ON p.id = i.personne_id
WHERE p.nom IN ('Martin Chapuis', 'Annabel Meunier');

Lisez bien les deux textes. Martin donne : un sac de la salle « Forme Express » dont le numéro de membre commence par « 48Z », un abonnement « or », un passage à la salle le 9 janvier 2018 et une plaque contenant « H42W ». Annabel confirme le passage à la salle le 9 janvier.

4. Traduire chaque indice en condition SQL

Indice	Table	Condition
numéro de membre commence par 48Z	salle_sport_membre	m.id LIKE '48Z%'
abonnement or	salle_sport_membre	m.statut_abonnement = 'or'
passage le 9 janvier 2018	salle_sport_passage	s.date_passage = 20180109
plaque contient H42W	permis_conduire	pc.immatriculation LIKE '%H42W%'

On part de personne et on ajoute les jointures une par une, en exécutant à chaque fois pour voir le nombre de lignes diminuer :

-- Étape a : seulement la salle de sport (plusieurs résultats)
SELECT p.nom, m.id, m.statut_abonnement
FROM personne p
JOIN salle_sport_membre m ON m.personne_id = p.id
WHERE m.id LIKE '48Z%' AND m.statut_abonnement = 'or';

-- Étape b : on ajoute le passage du 9 janvier (moins de résultats)
SELECT p.nom, m.id, s.date_passage
FROM personne p
JOIN salle_sport_membre m ON m.personne_id = p.id
JOIN salle_sport_passage s ON s.membre_id = m.id
WHERE m.id LIKE '48Z%' AND m.statut_abonnement = 'or'
  AND s.date_passage = 20180109;

-- Étape c : on ajoute la plaque (un seul résultat)
SELECT p.nom, pc.immatriculation
FROM personne p
JOIN salle_sport_membre m ON m.personne_id = p.id
JOIN salle_sport_passage s ON s.membre_id = m.id
JOIN permis_conduire pc ON pc.id = p.permis_id
WHERE m.id LIKE '48Z%' AND m.statut_abonnement = 'or'
  AND s.date_passage = 20180109
  AND pc.immatriculation LIKE '%H42W%';

Résultat attendu à l'étape c : Jeremy Boivin.

5. Valider

INSERT INTO solution VALUES (1, 'Jeremy Boivin');
SELECT valeur FROM solution;

La base vous félicite… et vous dit que l'enquête continue : lisez l'interrogatoire de Jeremy Boivin (même requête qu'à l'étape 3 avec son nom). Il décrit la femme qui l'a payé. À vous de traduire ces nouveaux indices en conditions, comme à l'étape 4. Le seul piège : « trois fois au concert » demande de compter les participations par personne :

SELECT p.nom, COUNT(*) AS participations
FROM personne p
JOIN evenement_participation e ON e.personne_id = p.id
WHERE e.nom_evenement = 'Concert Symphonique SQL'
  AND e.date BETWEEN 20171201 AND 20171231
GROUP BY p.id
HAVING COUNT(*) = 3;

Il reste à combiner avec la description physique et la voiture (dans permis_conduire). Si vous bloquez, la solution complète est dans la section suivante.

Point de contrôle

Vous avez validé les deux noms de la première histoire et vous savez expliquer chaque JOIN. Vous êtes prêt pour les autres enquêtes, sans pas-à-pas cette fois.

Enquêtes suivantes : indices et solutions

Les autres enquêtes sont à faire seul. Essayez d'abord sans rien ouvrir. Si vous bloquez, dépliez les indices dans l'ordre : ils sont de plus en plus précis, la solution complète vient en dernier.

Le meurtre de SQL Ville

Structure : 2 témoins, puis un personnage à identifier (réponse 1), puis un personnage à identifier (réponse 2).

Indice 1 : les témoins

• le numéro le plus grand de « Rue du Nord-Ouest » : ORDER BY numero_rue DESC LIMIT 1.
• un prénom sur « Avenue Franklin » : nom LIKE 'Annabel %'.

Une fois trouvés, lisez leur interrogatoire (jointure sur personne_id).

Indice 2 : le coupable

Les indices viennent de : Martin Chapuis (salle, vehicule).

• salle : la salle de sport, c'est salle_sport_membre (statut, numéro de membre avec LIKE) et salle_sport_passage (date, créneau avec heure_entree BETWEEN …).
• vehicule : la voiture et la plaque sont dans permis_conduire ; un fragment de plaque se cherche avec LIKE ('abc%' commence par, '%abc' se termine par, '%abc%' contient).

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition. Une fois validé, lisez son interrogatoire : l'enquête continue.

Indice 3 : la personne derrière tout ça

Les indices viennent de : Jeremy Boivin (evenement, physique, vehicule).

• physique : la description physique (genre, cheveux, yeux, taille, âge) est dans permis_conduire, les intervalles se filtrent avec BETWEEN.
• vehicule : la voiture et la plaque sont dans permis_conduire ; un fragment de plaque se cherche avec LIKE ('abc%' commence par, '%abc' se termine par, '%abc%' contient).
• evenement : les participations sont dans evenement_participation ; pour « n fois », comptez avec GROUP BY personne_id HAVING COUNT(*) = n.

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition.

Voir l'une des solutions possibles

-- Témoin : Martin Chapuis
SELECT nom FROM personne
WHERE nom_rue='Rue du Nord-Ouest'
ORDER BY numero_rue DESC LIMIT 1;

-- Témoin : Annabel Meunier
SELECT nom FROM personne
WHERE nom_rue='Avenue Franklin'
  AND nom LIKE 'Annabel %';

-- Leurs interrogatoires
SELECT p.nom, i.transcription FROM interrogatoire i
JOIN personne p ON p.id = i.personne_id
WHERE p.nom IN ('Martin Chapuis', 'Annabel Meunier');

-- Réponse 1 : Jeremy Boivin
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
JOIN salle_sport_membre m ON m.personne_id=p.id
JOIN salle_sport_passage s ON s.membre_id=m.id
WHERE pc.immatriculation LIKE '%H42W%'
  AND m.statut_abonnement='or'
  AND m.id LIKE '48Z%'
  AND s.date_passage=20180109
GROUP BY p.id;
INSERT INTO solution VALUES (1, 'Jeremy Boivin');
SELECT valeur FROM solution;

-- Réponse 2 : Miranda Prieur
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
JOIN evenement_participation e ON e.personne_id=p.id
WHERE pc.genre='femme'
  AND pc.couleur_cheveux='roux'
  AND pc.taille BETWEEN 165 AND 168
  AND e.nom_evenement='Concert Symphonique SQL'
  AND e.date BETWEEN 20171201 AND 20171231
  AND pc.marque_voiture='Tesla'
  AND pc.modele_voiture='Model S'
GROUP BY p.id
HAVING COUNT(DISTINCT e.date)=3;
INSERT INTO solution VALUES (1, 'Miranda Prieur');
SELECT valeur FROM solution;

Le braquage du SQL Express

Structure : 1 témoin, puis un personnage à identifier (réponse 1), puis un personnage à identifier (réponse 2).

Indice 1 : les témoins

• un prénom sur « Chemin du Port » : nom LIKE 'Gaston %'.

Une fois trouvés, lisez leur interrogatoire (jointure sur personne_id).

Indice 2 : le coupable

Les indices viennent de : Gaston Perrier (evenement, physique).

• physique : la description physique (genre, cheveux, yeux, taille, âge) est dans permis_conduire, les intervalles se filtrent avec BETWEEN.
• evenement : les participations sont dans evenement_participation ; pour « n fois », comptez avec GROUP BY personne_id HAVING COUNT(*) = n.

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition. Une fois validé, lisez son interrogatoire : l'enquête continue.

Indice 3 : la personne derrière tout ça

Les indices viennent de : Bastien Mallet (adresse, revenu, salle).

• salle : la salle de sport, c'est salle_sport_membre (statut, numéro de membre avec LIKE) et salle_sport_passage (date, créneau avec heure_entree BETWEEN …).
• revenu : le revenu est dans revenu, reliée par nir.
• adresse : la rue (et le numéro) sont directement dans personne.

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition.

Voir l'une des solutions possibles

-- Témoin : Gaston Perrier
SELECT p.nom FROM personne p
JOIN revenu r ON r.nir=p.nir
WHERE p.nom_rue='Chemin du Port'
ORDER BY r.revenu_annuel ASC LIMIT 1;

-- Leurs interrogatoires
SELECT p.nom, i.transcription FROM interrogatoire i
JOIN personne p ON p.id = i.personne_id
WHERE p.nom IN ('Gaston Perrier');

-- Réponse 1 : Bastien Mallet
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
JOIN evenement_participation e ON e.personne_id=p.id
WHERE pc.genre='homme'
  AND pc.couleur_cheveux='roux'
  AND pc.couleur_yeux='bleu'
  AND pc.age BETWEEN 20 AND 29
  AND e.nom_evenement='Soirée western'
  AND e.date BETWEEN 20190501 AND 20190531
GROUP BY p.id
HAVING COUNT(DISTINCT e.date)=2;
INSERT INTO solution VALUES (1, 'Bastien Mallet');
SELECT valeur FROM solution;

-- Réponse 2 : Louis Vasseur
SELECT p.nom FROM personne p
JOIN revenu r ON r.nir=p.nir
JOIN salle_sport_membre m ON m.personne_id=p.id
JOIN salle_sport_passage s ON s.membre_id=m.id
WHERE r.revenu_annuel>200000
  AND p.nom_rue='Avenue Anatole-France'
  AND m.statut_abonnement='standard'
  AND m.id LIKE '%6J%'
  AND s.date_passage=20190613
  AND s.heure_entree BETWEEN 1900 AND 2000
GROUP BY p.id;
INSERT INTO solution VALUES (1, 'Louis Vasseur');
SELECT valeur FROM solution;

La formule du professeur Noside

Structure : 3 témoins, puis un personnage à identifier (réponse 1).

Indice 1 : les témoins

• le numéro le plus petit de « Impasse Blaise-Pascal » : ORDER BY numero_rue ASC LIMIT 1.
• un prénom sur « Rue Pierre-Curie » : nom LIKE 'Colette %' et numero_rue BETWEEN 200 AND 300.
• un prénom sur « Avenue de l'Industrie » : nom LIKE 'Karim %'.

Une fois trouvés, lisez leur interrogatoire (jointure sur personne_id).

Indice 2 : le coupable

Les indices viennent de : Roger Delmas (physique), Colette Lemaire (vehicule), Karim Boyer (salle).

• physique : la description physique (genre, cheveux, yeux, taille, âge) est dans permis_conduire, les intervalles se filtrent avec BETWEEN.
• vehicule : la voiture et la plaque sont dans permis_conduire ; un fragment de plaque se cherche avec LIKE ('abc%' commence par, '%abc' se termine par, '%abc%' contient).
• salle : la salle de sport, c'est salle_sport_membre (statut, numéro de membre avec LIKE) et salle_sport_passage (date, créneau avec heure_entree BETWEEN …).

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition.

Voir l'une des solutions possibles

-- Témoin : Roger Delmas
SELECT nom FROM personne
WHERE nom_rue='Impasse Blaise-Pascal'
ORDER BY numero_rue ASC LIMIT 1;

-- Témoin : Colette Lemaire
SELECT nom FROM personne
WHERE nom_rue='Rue Pierre-Curie'
  AND nom LIKE 'Colette %'
  AND numero_rue BETWEEN 200 AND 300;

-- Témoin : Karim Boyer
SELECT p.nom FROM personne p
JOIN revenu r ON r.nir=p.nir
WHERE p.nom_rue='Avenue de l''Industrie'
ORDER BY r.revenu_annuel DESC LIMIT 1;

-- Leurs interrogatoires
SELECT p.nom, i.transcription FROM interrogatoire i
JOIN personne p ON p.id = i.personne_id
WHERE p.nom IN ('Roger Delmas', 'Colette Lemaire', 'Karim Boyer');

-- Réponse 1 : Margaux Tessier
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
JOIN salle_sport_membre m ON m.personne_id=p.id
JOIN salle_sport_passage s ON s.membre_id=m.id
WHERE m.statut_abonnement='standard'
  AND s.date_passage=20200206
  AND s.heure_entree BETWEEN 1300 AND 1400
  AND pc.marque_voiture='Dacia'
  AND pc.immatriculation LIKE '%7X1S'
  AND pc.genre='femme'
  AND pc.couleur_cheveux='blond'
  AND pc.taille BETWEEN 162 AND 165
GROUP BY p.id;
INSERT INTO solution VALUES (1, 'Margaux Tessier');
SELECT valeur FROM solution;

Panique à la septième séance

Structure : 1 témoin, puis une fausse piste, puis un personnage à identifier (réponse 1), puis un personnage à identifier (réponse 2).

Indice 1 : les témoins

• un prénom sur « Boulevard Georges-Brassens » : nom LIKE 'Solène %'.

Une fois trouvés, lisez leur interrogatoire (jointure sur personne_id).

Indice 2 : la fausse piste

Les indices viennent de : Solène Barbier (evenement, physique).

• physique : la description physique (genre, cheveux, yeux, taille, âge) est dans permis_conduire, les intervalles se filtrent avec BETWEEN.
• evenement : les participations sont dans evenement_participation ; pour « n fois », comptez avec GROUP BY personne_id HAVING COUNT(*) = n.

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition. Une fois validé, lisez son interrogatoire : l'enquête continue.

Indice 3 : le coupable

Les indices viennent de : Theo Rocher (physique, salle, vehicule).

• physique : la description physique (genre, cheveux, yeux, taille, âge) est dans permis_conduire, les intervalles se filtrent avec BETWEEN.
• vehicule : la voiture et la plaque sont dans permis_conduire ; un fragment de plaque se cherche avec LIKE ('abc%' commence par, '%abc' se termine par, '%abc%' contient).
• salle : la salle de sport, c'est salle_sport_membre (statut, numéro de membre avec LIKE) et salle_sport_passage (date, créneau avec heure_entree BETWEEN …).

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition. Une fois validé, lisez son interrogatoire : l'enquête continue.

Indice 4 : la personne derrière tout ça

Les indices viennent de : Nathan Guichard (evenement, physique, revenu).

• physique : la description physique (genre, cheveux, yeux, taille, âge) est dans permis_conduire, les intervalles se filtrent avec BETWEEN.
• revenu : le revenu est dans revenu, reliée par nir.
• evenement : les participations sont dans evenement_participation ; pour « n fois », comptez avec GROUP BY personne_id HAVING COUNT(*) = n.

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition.

Voir l'une des solutions possibles

-- Témoin : Solène Barbier
SELECT nom FROM personne
WHERE nom_rue='Boulevard Georges-Brassens'
  AND nom LIKE 'Solène %';

-- Leurs interrogatoires
SELECT p.nom, i.transcription FROM interrogatoire i
JOIN personne p ON p.id = i.personne_id
WHERE p.nom IN ('Solène Barbier');

-- Fausse piste : Theo Rocher
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
JOIN evenement_participation e ON e.personne_id=p.id
WHERE pc.genre='homme'
  AND pc.couleur_cheveux='noir'
  AND pc.age BETWEEN 70 AND 75
  AND e.nom_evenement='Avant-première au Grand Rex'
  AND e.date=20211030
GROUP BY p.id
HAVING COUNT(DISTINCT e.date)=1;
INSERT INTO solution VALUES (1, 'Theo Rocher');
SELECT valeur FROM solution;

-- Réponse 1 : Nathan Guichard
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
JOIN salle_sport_membre m ON m.personne_id=p.id
WHERE m.id LIKE '8U%'
  AND pc.modele_voiture='i20'
  AND pc.immatriculation LIKE '8V9B%'
  AND pc.genre='homme'
  AND pc.couleur_yeux='vert';
INSERT INTO solution VALUES (1, 'Nathan Guichard');
SELECT valeur FROM solution;

-- Réponse 2 : Valerie Humbert
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
JOIN revenu r ON r.nir=p.nir
JOIN evenement_participation e ON e.personne_id=p.id
WHERE r.revenu_annuel>200000
  AND pc.genre='femme'
  AND pc.taille BETWEEN 163 AND 167
  AND pc.age BETWEEN 39 AND 43
  AND e.nom_evenement='Festival du Film Court'
  AND e.date BETWEEN 20210901 AND 20210931
GROUP BY p.id
HAVING COUNT(DISTINCT e.date)=3;
INSERT INTO solution VALUES (1, 'Valerie Humbert');
SELECT valeur FROM solution;

Menace sur Nova City

Structure : 2 témoins, puis un personnage à identifier (réponse 1), puis un personnage à identifier (réponse 2).

Indice 1 : les témoins

• le numéro le plus grand de « Chemin de l'Industrie » : ORDER BY numero_rue DESC LIMIT 1.
• un prénom sur « Avenue Kennedy » : nom LIKE 'Justine %' et numero_rue BETWEEN 300 AND 400.

Une fois trouvés, lisez leur interrogatoire (jointure sur personne_id).

Indice 2 : le coupable

Les indices viennent de : Marcel Bourgeois (physique, salle).

• physique : la description physique (genre, cheveux, yeux, taille, âge) est dans permis_conduire, les intervalles se filtrent avec BETWEEN.
• salle : la salle de sport, c'est salle_sport_membre (statut, numéro de membre avec LIKE) et salle_sport_passage (date, créneau avec heure_entree BETWEEN …).

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition. Une fois validé, lisez son interrogatoire : l'enquête continue.

Indice 3 : la personne derrière tout ça

Les indices viennent de : Justine Payet (vehicule), Kevin Lacroix (evenement, revenu).

• vehicule : la voiture et la plaque sont dans permis_conduire ; un fragment de plaque se cherche avec LIKE ('abc%' commence par, '%abc' se termine par, '%abc%' contient).
• revenu : le revenu est dans revenu, reliée par nir.
• evenement : les participations sont dans evenement_participation ; pour « n fois », comptez avec GROUP BY personne_id HAVING COUNT(*) = n.

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition.

Voir l'une des solutions possibles

-- Témoin : Marcel Bourgeois
SELECT nom FROM personne
WHERE nom_rue='Chemin de l''Industrie'
ORDER BY numero_rue DESC LIMIT 1;

-- Témoin : Justine Payet
SELECT nom FROM personne
WHERE nom_rue='Avenue Kennedy'
  AND nom LIKE 'Justine %'
  AND numero_rue BETWEEN 300 AND 400;

-- Leurs interrogatoires
SELECT p.nom, i.transcription FROM interrogatoire i
JOIN personne p ON p.id = i.personne_id
WHERE p.nom IN ('Marcel Bourgeois', 'Justine Payet');

-- Réponse 1 : Kevin Lacroix
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
JOIN salle_sport_membre m ON m.personne_id=p.id
WHERE m.statut_abonnement='standard'
  AND m.id LIKE '1H%'
  AND pc.genre='homme'
  AND pc.couleur_cheveux='roux'
  AND pc.couleur_yeux='bleu'
  AND pc.taille BETWEEN 173 AND 176;
INSERT INTO solution VALUES (1, 'Kevin Lacroix');
SELECT valeur FROM solution;

-- Réponse 2 : Helene Marchal
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
JOIN revenu r ON r.nir=p.nir
JOIN evenement_participation e ON e.personne_id=p.id
WHERE pc.marque_voiture='Mercedes'
  AND pc.modele_voiture='Classe C'
  AND pc.immatriculation LIKE '%3R5G%'
  AND e.nom_evenement='Conférence Cybersécurité'
  AND e.date BETWEEN 20220301 AND 20220331
  AND r.revenu_annuel>100000
GROUP BY p.id
HAVING COUNT(DISTINCT e.date)=2;
INSERT INTO solution VALUES (1, 'Helene Marchal');
SELECT valeur FROM solution;

L'Infiltré de Little Italy

Structure : 2 témoins, puis un personnage à identifier (réponse 1), puis un personnage à identifier (réponse 2).

Indice 1 : les témoins

• un prénom sur « Rue de Nantes » : nom LIKE 'Giuseppe %'.
• un prénom sur « Impasse des Vignes » : nom LIKE 'Antoine %'.

Une fois trouvés, lisez leur interrogatoire (jointure sur personne_id).

Indice 2 : le coupable

Les indices viennent de : Antoine Rey (adresse, physique).

• physique : la description physique (genre, cheveux, yeux, taille, âge) est dans permis_conduire, les intervalles se filtrent avec BETWEEN.
• adresse : la rue (et le numéro) sont directement dans personne.

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition. Une fois validé, lisez son interrogatoire : l'enquête continue.

Indice 3 : la personne derrière tout ça

Les indices viennent de : Enzo Chauvin (revenu, salle, vehicule).

• vehicule : la voiture et la plaque sont dans permis_conduire ; un fragment de plaque se cherche avec LIKE ('abc%' commence par, '%abc' se termine par, '%abc%' contient).
• salle : la salle de sport, c'est salle_sport_membre (statut, numéro de membre avec LIKE) et salle_sport_passage (date, créneau avec heure_entree BETWEEN …).
• revenu : le revenu est dans revenu, reliée par nir.

Partez de personne, ajoutez une jointure par indice, et vérifiez que le nombre de lignes diminue à chaque condition.

Voir l'une des solutions possibles

-- Témoin : Giuseppe Ferreira
SELECT p.nom FROM personne p
JOIN revenu r ON r.nir=p.nir
WHERE p.nom_rue='Rue de Nantes'
ORDER BY r.revenu_annuel DESC LIMIT 1;

-- Témoin : Antoine Rey
SELECT nom FROM personne
WHERE nom_rue='Impasse des Vignes'
  AND nom LIKE 'Antoine %';

-- Leurs interrogatoires
SELECT p.nom, i.transcription FROM interrogatoire i
JOIN personne p ON p.id = i.personne_id
WHERE p.nom IN ('Giuseppe Ferreira', 'Antoine Rey');

-- Réponse 1 : Enzo Chauvin
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
WHERE p.nom_rue='Rue des Vignes'
  AND p.numero_rue BETWEEN 260 AND 360
  AND pc.genre='homme'
  AND pc.couleur_cheveux='brun'
  AND pc.age BETWEEN 66 AND 71;
INSERT INTO solution VALUES (1, 'Enzo Chauvin');
SELECT valeur FROM solution;

-- Réponse 2 : Salvatore Guerin
SELECT p.nom FROM personne p
JOIN permis_conduire pc ON pc.id=p.permis_id
JOIN revenu r ON r.nir=p.nir
JOIN salle_sport_membre m ON m.personne_id=p.id
JOIN salle_sport_passage s ON s.membre_id=m.id
WHERE pc.marque_voiture='Honda'
  AND pc.immatriculation LIKE '5Y9F%'
  AND m.statut_abonnement='or'
  AND s.date_passage=20230918
  AND s.heure_entree BETWEEN 1000 AND 1100
  AND r.revenu_annuel>200000
GROUP BY p.id;
INSERT INTO solution VALUES (1, 'Salvatore Guerin');
SELECT valeur FROM solution;

Pour aller plus loin

• Résolvez la dernière étape d'une histoire en une seule requête (toutes les jointures d'un coup).
• Pour chaque histoire, écrivez la requête qui liste tous les habitants qui correspondent à un indice mais pas aux autres : ce sont les fausses pistes que la base a glissées exprès.
• Réécrivez l'une de vos requêtes finales en PHP avec PDO et une requête préparée, en passant la date et le type de crime en paramètres.

Conclusion

Dans ce TP vous avez :

• Exploré une base inconnue en partant de son schéma.
• Enchaîné filtres, jointures et agrégats pour passer de 10 000 habitants à un seul nom.
• Vu que chaque indice correspond à une condition SQL, et qu'une condition manquante laisse toujours plusieurs suspects.

👋 Si vous avez des questions, n'hésitez pas. Et si vous avez résolu toutes les histoires, venez me voir : j'en ai peut-être une autre pour vous.