Bases de données et SQL en prépa

En prépa, la première fois qu'on te parle de bases de données, tu as souvent l'impression de changer de matière : plus de démonstration, plus de récursivité, juste des tableaux et un langage qui ressemble à de l'anglais. C'est pourtant l'un des blocs les plus rentables de l'option informatique : le cours tient en quelques notions, la logique est limpide, et les questions d'écrit ou de TP se ressemblent d'une année sur l'autre.

Cet article te donne le cours complet de SQL en prépa, au programme de l'option info en MPSI/MP2I puis en MPI : le modèle relationnel (table, attribut, clé primaire, clé étrangère), la requête de base SELECT ... FROM ... WHERE, le tri et les doublons, les fonctions d'agrégation avec GROUP BY, et les jointures entre deux tables. Tout est illustré sur un mini-schéma élèves-notes, avec des requêtes commentées et 3 exercices corrigés. Nos profs Hadamard, anciens de MP2I et de MPSI option info passés par l'X, l'ENS et CentraleSupélec, y ont tous planché : ils te montrent les réflexes qui font gagner du temps.

Le modèle relationnel : tables, attributs et clés

Une base de données relationnelle range l'information dans des relations, un mot savant pour désigner des tables à deux dimensions. Chaque table décrit un type d'objet : les élèves, les notes, les livres d'une bibliothèque, les réservations d'un cinéma.

• Une colonne est un attribut : elle porte un nom et un type (entier, texte, réel, date). Dans une table élèves, on aura par exemple les attributs nom, prenom, classe.
• Une ligne est un enregistrement (ou n-uplet) : elle décrit un objet précis, un élève donné avec toutes ses valeurs d'attributs.
• La clé primaire est l'attribut (ou le petit groupe d'attributs) qui identifie chaque ligne de façon unique. Deux lignes ne peuvent jamais avoir la même clé primaire : c'est ce qui garantit qu'un élève ne soit pas confondu avec un autre.
• La clé étrangère est un attribut d'une table qui référence la clé primaire d'une autre table. C'est le fil qui relie les tables entre elles.

Voici le mini-schéma qui servira de terrain d'entraînement dans tout l'article : une table eleves et une table notes, reliées par une clé étrangère.

-- Table des élèves : chaque élève a un identifiant unique (clé primaire)
CREATE TABLE eleves (
    id      INTEGER PRIMARY KEY,   -- clé primaire
    nom     TEXT,
    prenom  TEXT,
    classe  TEXT                   -- 'MP2I', 'MPI', ...
);

-- Table des notes : id_eleve pointe vers eleves.id (clé étrangère)
CREATE TABLE notes (
    id        INTEGER PRIMARY KEY, -- clé primaire de la table notes
    id_eleve  INTEGER,             -- clé étrangère vers eleves.id
    matiere   TEXT,                -- 'Maths', 'Physique', 'Info', ...
    note      REAL,
    FOREIGN KEY (id_eleve) REFERENCES eleves(id)
);

On peut imaginer le contenu suivant : trois élèves dans eleves (Alice en MP2I, Bilal en MPI, Chloé en MP2I) et une poignée de notes dans notes, chacune reliée à un élève par son id_eleve.

Le langage SQL, en une phrase

SQL (Structured Query Language) est le langage standard pour interroger une base relationnelle. En prépa, on l'utilise surtout pour lire des données : poser une question à la base et récupérer un tableau de résultats. Une requête de lecture répond toujours à trois questions, dans cet ordre.

1. Quelles colonnes ? C'est la clause SELECT.
2. Dans quelle table ? C'est la clause FROM.
3. Quelles lignes garder ? C'est la clause WHERE (facultative).

Le squelette de toute requête tient donc en trois mots-clés, qu'on complétera ensuite avec le tri, les doublons, l'agrégation et les jointures.

SELECT   colonnes
FROM     table
WHERE    condition;

SELECT, FROM, WHERE : la requête de base

Commençons par la plus simple des requêtes : tout afficher. L'étoile * signifie « toutes les colonnes ».

-- Afficher toutes les colonnes de tous les élèves
SELECT *
FROM eleves;

On choisit ensuite les colonnes qui nous intéressent en les listant après SELECT, et on filtre les lignes avec WHERE. Ici, on ne veut que le nom et le prénom des élèves de la classe MP2I.

-- Nom et prénom des élèves de MP2I
SELECT nom, prenom
FROM eleves
WHERE classe = 'MP2I';

La condition du WHERE se combine avec les opérateurs logiques AND, OR, NOT et les comparateurs usuels (=, <, >, <=, >=, <> pour « différent de »). Attention : le texte se met entre apostrophes simples, et l'égalité s'écrit avec un seul =, contrairement à Python.

-- Les notes strictement supérieures à 15 en Maths
SELECT id_eleve, note
FROM notes
WHERE matiere = 'Maths' AND note > 15;

Trier avec ORDER BY, dédoublonner avec DISTINCT

Une base ne renvoie pas ses lignes dans un ordre garanti. Pour les trier, on ajoute ORDER BY suivi de la colonne de tri, avec ASC pour l'ordre croissant (par défaut) ou DESC pour décroissant.

-- Les notes, de la meilleure à la plus faible
SELECT id_eleve, matiere, note
FROM notes
ORDER BY note DESC;

On peut trier sur plusieurs colonnes : SQL départage d'abord sur la première, puis sur la suivante en cas d'égalité.

-- Élèves triés par classe, puis par nom à l'intérieur de chaque classe
SELECT nom, prenom, classe
FROM eleves
ORDER BY classe ASC, nom ASC;

Enfin, DISTINCT retire les doublons du résultat : très utile pour obtenir la liste des valeurs distinctes d'un attribut.

-- La liste des classes présentes, sans répétition
SELECT DISTINCT classe
FROM eleves;

-- Les matières pour lesquelles il existe au moins une note
SELECT DISTINCT matiere
FROM notes;

Fonctions d'agrégation et GROUP BY

Jusqu'ici, une requête renvoyait des lignes brutes. Les fonctions d'agrégation font autre chose : elles résument un ensemble de lignes en une seule valeur. Il y en a cinq à connaître.

Utilisées seules, elles renvoient une valeur unique sur toute la table. On peut renommer la colonne de résultat avec AS pour un affichage propre.

-- Combien d'élèves dans la base ?
SELECT COUNT(*) AS nb_eleves
FROM eleves;

-- Moyenne, meilleure et plus faible note, toutes matières confondues
SELECT AVG(note) AS moyenne,
       MAX(note) AS meilleure,
       MIN(note) AS plus_faible
FROM notes;

Tout l'intérêt vient de GROUP BY : au lieu d'un seul résultat global, il regroupe les lignes par valeur d'un attribut et applique la fonction d'agrégation à chaque groupe. Pour obtenir la moyenne par matière, on regroupe par matiere.

-- Moyenne des notes, matière par matière
SELECT matiere, AVG(note) AS moyenne
FROM notes
GROUP BY matiere;

La règle d'or : toute colonne qui figure dans le SELECT à côté d'une fonction d'agrégation doit apparaître dans le GROUP BY. Ici matiere est dans les deux, c'est cohérent. Pour filtrer après regroupement (par exemple ne garder que les matières dont la moyenne dépasse 12), on utilise HAVING et non WHERE, car WHERE agit avant le regroupement.

-- Matières dont la moyenne de classe dépasse 12
SELECT matiere, AVG(note) AS moyenne
FROM notes
GROUP BY matiere
HAVING AVG(note) > 12;

Les jointures : recoller deux tables

On arrive au cœur du modèle relationnel. La table notes ne connaît que des id_eleve, pas des noms. Pour afficher le nom de l'élève à côté de chacune de ses notes, il faut recoller les deux tables : c'est une jointure. On écrit FROM eleves JOIN notes ON ..., la condition ON indiquant quelle clé relie les deux tables.

-- Le nom et le prénom de l'élève à côté de chacune de ses notes
SELECT eleves.nom, eleves.prenom, notes.matiere, notes.note
FROM eleves
JOIN notes ON eleves.id = notes.id_eleve;

SQL parcourt chaque ligne de eleves, cherche dans notes les lignes dont id_eleve vaut l'id de l'élève, et associe les deux. Comme un même nom de colonne peut exister dans les deux tables, on préfixe chaque colonne par le nom de sa table (eleves.nom, notes.note) pour lever toute ambiguïté.

Une jointure se combine avec tout le reste : WHERE pour filtrer, GROUP BY pour agréger, ORDER BY pour trier. C'est là que SQL devient réellement expressif. Voici la moyenne de chaque élève, nom compris, obtenue en joignant puis en regroupant par élève.

-- Moyenne générale de chaque élève, avec son nom
SELECT eleves.nom, eleves.prenom, AVG(notes.note) AS moyenne
FROM eleves
JOIN notes ON eleves.id = notes.id_eleve
GROUP BY eleves.id, eleves.nom, eleves.prenom
ORDER BY moyenne DESC;

3 exercices corrigés : écrire des requêtes

On reprend le schéma eleves(id, nom, prenom, classe) et notes(id, id_eleve, matiere, note). À chaque énoncé, essaie d'écrire la requête avant de lire le corrigé.

Exercice 1, sélection et tri

Énoncé. Afficher le nom et le prénom des élèves de la classe MPI, classés par ordre alphabétique de nom.

Solution. Une requête SELECT ... FROM ... WHERE filtrée sur la classe, complétée par un ORDER BY sur le nom.

SELECT nom, prenom
FROM eleves
WHERE classe = 'MPI'
ORDER BY nom ASC;

Le ASC est facultatif (c'est l'ordre par défaut), mais l'écrire rend l'intention explicite pour le correcteur.

Exercice 2, agrégation par groupe

Énoncé. Pour chaque matière, afficher le nombre de notes enregistrées et la moyenne, classées de la meilleure moyenne à la plus faible.

Solution. On regroupe par matiere, on compte avec COUNT et on moyenne avec AVG, puis on trie sur la moyenne.

SELECT matiere,
       COUNT(*)  AS nb_notes,
       AVG(note) AS moyenne
FROM notes
GROUP BY matiere
ORDER BY moyenne DESC;

Point de vigilance : matiere apparaît dans le SELECT et dans le GROUP BY, comme l'exige la règle des agrégations. Les deux colonnes calculées sont renommées avec AS pour un résultat lisible.

Exercice 3, jointure et agrégation

Énoncé. Afficher, pour chaque élève, son nom, son prénom et sa meilleure note toutes matières confondues, en ne gardant que les élèves dont la meilleure note atteint au moins 16, classés du plus fort au moins fort.

Solution. Il faut relier eleves et notes par une jointure, agréger avec MAX, filtrer le résultat agrégé avec HAVING, puis trier.

SELECT eleves.nom,
       eleves.prenom,
       MAX(notes.note) AS meilleure_note
FROM eleves
JOIN notes ON eleves.id = notes.id_eleve
GROUP BY eleves.id, eleves.nom, eleves.prenom
HAVING MAX(notes.note) >= 16
ORDER BY meilleure_note DESC;

C'est la requête la plus complète du cours : elle enchaîne jointure, regroupement, fonction d'agrégation, filtre post-agrégation et tri. Le filtre porte sur MAX(notes.note), donc il vient dans HAVING et non dans WHERE, puisqu'il s'applique après le regroupement.

Ce qu'il faut retenir

• Le modèle relationnel : des tables (relations) faites de colonnes (attributs) et de lignes ; une clé primaire identifie chaque ligne, une clé étrangère relie deux tables.
• La requête de base : SELECT les colonnes, FROM la table, WHERE la condition sur les lignes.
• Le confort : ORDER BY pour trier (ASC/DESC), DISTINCT pour retirer les doublons.
• L'agrégation : COUNT, SUM, AVG, MIN, MAX résument des lignes ; GROUP BY le fait par groupe, HAVING filtre après regroupement.
• Les jointures : FROM A JOIN B ON A.cle = B.cle_etrangere recolle deux tables reliées par une clé.
• Les pièges Python-vers-SQL : un seul = pour l'égalité, apostrophes simples pour le texte.

Le bloc bases de données n'est qu'une facette de l'informatique de prépa. Il s'articule avec la programmation en Python, qui reste le langage de référence de l'option, et avec les structures de données — listes, piles, files, arbres — qui gouvernent la façon dont l'information est organisée en mémoire. Une table SQL et un tableau de dictionnaires Python répondent d'ailleurs à la même question : comment ranger des données pour les retrouver vite.

Si tu hésites encore sur l'intérêt de l'option informatique ou sur le choix d'une filière qui l'approfondit, nos articles sur l'option info en MPSI et sur la filière MP2I détaillent ce à quoi ressemblent vraiment ces cours, TP de SQL compris. Et si un point de ce chapitre résiste, un ancien taupin passé par l'informatique le déverrouille souvent en une séance.