SQL Murder Mystery : apprendre le SQL avec un meurtre

Un meurtre a eu lieu à SQL City le 15 janvier 2018. Le détective vous a confié le rapport de police, et vous l'avez perdu. Tout ce qui reste, c'est la base de données du commissariat. À vous de retrouver l'assassin, une requête après l'autre.

C'est le principe de SQL Murder Mystery, un jeu gratuit créé au Knight Lab de Northwestern University, un laboratoire qui mêle développeurs et journalistes. On y apprend le SQL, le langage qui sert à interroger les bases de données relationnelles, sans cours magistral : chaque notion arrive au moment où on en a besoin pour avancer. Le jeu tourne dans le navigateur, sans compte ni installation.

Comment se présente le jeu

Le site mystery.knightlab.com propose deux entrées. Les débutants passent par un walkthrough qui explique ce qu'est une table, une clé primaire, une requête, puis les guide jusqu'aux premiers indices. Les habitués du SQL reçoivent juste l'énoncé et se débrouillent.

Les requêtes s'écrivent dans des zones de code sur la page et s'exécutent en local grâce à SQL.js, une version de SQLite compilée pour le navigateur. Rien ne part sur un serveur, et on peut tout casser sans conséquence : un rechargement remet la base à zéro.

Le projet a été créé en 2018 par Joon Park et Cathy He, deux anciens fellows du Knight Lab, et adapté pour le web par Joe Germuska. Il s'inspire d'un jeu plus ancien, le Command Line Murder Mystery, qui faisait la même chose avec grep et les commandes Unix. Le code est sous licence MIT, les textes sous Creative Commons BY-SA 4.0, et le dépôt GitHub dépasse les 2 200 étoiles. L'illustration du détective, reprise en tête de cet article, est signée rambleron et diffusée par Vecteezy sous licence gratuite.

Un détail à savoir : tout le jeu est en anglais. Les témoignages, les noms de rue et les consignes. Le vocabulaire reste simple, mais il faut lire attentivement, car les indices sont dans les détails des phrases.

La base de données

La base contient neuf tables. Certaines sont petites, d'autres comptent plus de 20 000 lignes, assez pour qu'on ne puisse pas tout parcourir à l'œil.

Les tables sont reliées par des clés étrangères : person.license_id pointe vers drivers_license.id, interview.person_id vers person.id, et ainsi de suite. Le site fournit un diagramme du schéma, mais retrouver soi-même la structure fait partie de l'exercice :

SELECT name FROM sqlite_master WHERE type = 'table';
 
SELECT sql FROM sqlite_master WHERE name = 'person';

Ces deux requêtes sont propres à SQLite. Sur PostgreSQL, on interrogerait information_schema.tables, et dans psql la commande \d person fait le même travail.

Les premières requêtes

Le point de départ est donné par l'énoncé : un meurtre, le 15 janvier 2018, à SQL City. Premier piège, la date est stockée comme un entier au format AAAAMMJJ, pas comme une vraie date :

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

Le rapport mentionne deux témoins. Le premier vit « dans la dernière maison » d'une rue donnée. Il n'y a pas de colonne « dernière maison », il faut traduire la phrase en SQL : trier par numéro décroissant et garder la première ligne.

SELECT id, name, address_number
FROM person
WHERE address_street_name = 'Northwestern Dr'
ORDER BY address_number DESC
LIMIT 1;

Le second témoin s'appelle Annabel et vit dans une autre rue. On ne connaît que son prénom, c'est le moment d'utiliser LIKE :

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

Il reste à lire ce qu'ils ont déclaré. Les témoignages sont dans interview, les noms dans person, et une jointure relie les deux. Je m'arrête là pour ne rien gâcher : la suite se joue sur une salle de sport, une plaque d'immatriculation partielle et un concert.

Ce que le jeu enseigne vraiment

J'ai bouclé l'enquête en six requêtes. Elles couvrent à peu près tout ce qu'un développeur utilise chaque semaine :

La progression est bien pensée. Les premières requêtes ne touchent qu'une table. Puis les indices s'éparpillent : le témoignage donne un numéro de membre partiel, le passage à la salle donne une date, le permis donne une plaque. Aucune table ne contient la réponse seule. Il faut les croiser, et c'est précisément ce qui rend les jointures évidentes. Ce que PostgreSQL : les fondamentaux explique sur le papier devient ici un réflexe, parce qu'on en a besoin pour avancer.

La dernière étape va plus loin. Il faut compter combien de fois une personne apparaît à un événement sur une période, puis ne garder que celles qui atteignent un seuil. GROUP BY avec HAVING déroute souvent les débutants. Après une enquête, on n'oublie plus la différence entre WHERE, qui filtre les lignes, et HAVING, qui filtre les groupes.

Pour les plus à l'aise, le jeu lance un défi : résoudre la dernière partie en deux requêtes maximum. Ça pousse vers les sous-requêtes et les jointures multiples bien construites.

Vérifier sa réponse

On propose un suspect en insérant son nom dans la table solution :

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

La réponse s'affiche dans la foulée. Le mécanisme est malin : un trigger SQLite s'exécute après chaque insertion, compare le nom proposé à la bonne réponse, vide la table et y écrit un message de félicitations ou d'échec. C'est un bon exemple de logique placée dans la base elle-même.

Les plus curieux remarqueront que ce trigger est lisible dans sqlite_master, et que les réponses y sont seulement encodées en hexadécimal. On peut donc tricher en une requête. Ça n'a aucun intérêt, mais ça rappelle une vraie leçon : tout ce qui est stocké côté client est lisible par le client.

Jouer en local

Le dépôt GitHub contient le fichier sql-murder-mystery.db, une base SQLite de 3,7 Mo. On peut la télécharger et l'interroger avec l'outil de son choix :

curl -LO https://github.com/NUKnightLab/sql-mysteries/raw/master/sql-murder-mystery.db
sqlite3 sql-murder-mystery.db

Dans le shell sqlite3, la commande .tables liste les neuf tables et .schema person affiche la structure d'une table.

Jouer en local a deux avantages : on garde l'historique de ses requêtes, et on s'entraîne avec un vrai client SQL. Les auteurs recommandent SQLiteStudio aux débutants. DBeaver ou DB Browser for SQLite font aussi l'affaire. Le dépôt fournit en plus deux énoncés en PDF, un pour débutants et un pour confirmés, et un aide-mémoire SQL.

Pour qui, et quoi faire ensuite

Le jeu n'est pas un cours complet, et ses auteurs le disent eux-mêmes. Un vrai débutant gagne à commencer par le walkthrough, puis à compléter avec Select Star SQL, un tutoriel interactif gratuit dont le jeu réutilise d'ailleurs des composants. SQLBolt est une autre bonne option, en exercices courts.

Je le recommande dans trois cas :

• Vous débutez en SQL. Une heure ou deux de jeu ancre mieux JOIN et GROUP BY que n'importe quel chapitre de livre.
• Vous utilisez un ORM et n'écrivez jamais de SQL. Le jeu vous rappelle ce qui se passe sous la couche d'abstraction.
• Vous formez des gens. C'est un excellent exercice d'équipe ou de premier jour de formation, à faire en binôme.

Il ne couvre pas ce qui touche à l'écriture et à la conception : UPDATE, DELETE, transactions, index, modélisation d'un schéma. Pour savoir quand une base relationnelle est le bon choix, et quand elle ne l'est pas, SQL vs NoSQL prend le relais.

La plupart des gens apprennent le SQL en recopiant des requêtes sur des tables users et orders dont ils se fichent. SQL Murder Mystery donne une raison de vouloir la réponse, et c'est tout ce qui manquait. Si le SQL est le trou dans votre parcours, une soirée sur SQL City vaut mieux qu'une semaine de lecture. Et si vous cherchez où le placer dans votre apprentissage, la roadmap développeur web 2026 le classe parmi les fondamentaux, pas parmi les options.