Jeu : Enquête SQL

Sommaire

• À vous de jouer : choisissez votre enquête
• Les règles du jeu
• Le plan de la ville
• 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 écrire des requêtes SQL ? 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 jeu 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 jeu est un bonus : il n'est pas noté et ne demande aucun rendu. Vous pouvez y jouer en autonomie, seul ou à deux, dès que vous avez terminé le TP en cours. Comptez environ une heure par histoire. Il vous faut le TP 5 SQL (SELECT, WHERE, jointures) et l'aide-mémoire SQL sous la main.

À 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), le bouton « Réinitialiser » remet la base dans son état d'origine et le schéma des tables est dépliable sous l'éditeur.

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.

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 décrivent le coupable, et parfois le coupable vous mène plus loin encore.
• Les dates sont des entiers AAAAMMJJ (20180115 pour le 15 janvier 2018), les heures 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. Le journal de bord au-dessus de l'éditeur coche les étapes réussies.

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

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.

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.

Vous préférez un vrai client SQL ? Le lien « Télécharger la base » vous donne le fichier .sqlite, à ouvrir avec DB Browser for SQLite, PhpStorm ou sqlite3.

Le plan de la ville

Chaque association du MCD devient une clé étrangère : personne.permis_id, personne.nir, salle_sport_membre.personne_id, salle_sport_passage.membre_id, interrogatoire.personne_id, et la table evenement_participation (personne_id, evenement_id, nom_evenement, date). Ce sont exactement les ON de vos jointures.

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, copiez chaque requête et comparez votre résultat au mien. Les autres histoires seront à faire seul, avec la même méthode.

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 ». Le rapport ne donne jamais un nom, seulement une manière de le retrouver.

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.

Les autres formulations que vous rencontrerez

Le rapport dit…	Vous cherchez…
« le plus petit numéro de la rue X »	ORDER BY numero_rue ASC LIMIT 1
« la personne au revenu le plus élevé de la rue X »	une jointure avec revenu puis ORDER BY revenu_annuel DESC LIMIT 1

3. Lire leurs dépositions

Les dépositions sont dans interrogatoire, reliée à personne par personne_id. 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');

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. Tous les indices sont nécessaires : la base contient volontairement des personnes qui correspondent à presque tout.

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. 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, 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).
• Listez 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

En jouant, 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.