Bases de données relationnelles et SQL

Cours complet · informatique (MP2I/MPI), chapitre 21 · MP2I et MPI

Travailler ce chapitre sur Adloun Exercices corrigés de ce chapitre

21.1 Dire ce qu'on veut, pas comment l'obtenir

Ce chapitre est le seul du livre où l'on ne programme pas. On interroge. Et c'est la troisième famille de langages du chapitre chap:algo-prog : le déclaratif, où l'on décrit le résultat voulu sans dire par quels parcours l'atteindre.

Le programme borne l'ambition avec netteté : « on se limite volontairement à une description applicative des bases de données en langage SQL. Il s'agit de permettre d'interroger une base présentant des données à travers plusieurs relations. On ne présente ni l'algèbre relationnelle ni le calcul relationnel. »

AttentionCe qui est explicitement hors programme

La liste est longue et le B.O. la donne : « la création, la suppression et la modification de tables au travers du langage SQL sont hors programme » ; et aussi « la notion de modèle logique vs physique, les bases de données non relationnelles, les méthodes de modélisation de base, les fragments DDL, TCL et ACL du langage SQL, l'optimisation de requêtes par l'algèbre relationnelle ». La notion d'index est hors programme, ainsi que tout ce qui touche aux dates et aux collations.

On écrit donc uniquement des SELECT. C'est peu, et c'est déjà un langage complet pour interroger.

21.2 Le vocabulaire

Définition 21.1Table, attribut, enregistrement, schéma

Une table — ou relation — est un ensemble de lignes de même forme.

ses colonnes sont les attributs, chacun ayant un domaine : entier, flottant, chaîne ;
ses lignes sont les enregistrements ;
le schéma est la liste des attributs avec leurs domaines.

Le programme s'en tient à « une notion sommaire de domaine » et écarte toute considération sur les types des moteurs.

Exemple 21.2La base qui servira à tout le chapitre

Eleve <div class="overflow-x-auto my-6"><table style="border-collapse:collapse;width:100%" class="text-xs"><tr style="border-top:1.5px solid #2E2C26;border-bottom:1.5px solid #2E2C26"><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">id</th><th style="text-align:left;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">nom</th><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">promo</th></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">1</td><td style="text-align:left;padding:8px 12px;color:#34332D;vertical-align:top">Alaoui</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2026</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2</td><td style="text-align:left;padding:8px 12px;color:#34332D;vertical-align:top">Benali</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2026</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">3</td><td style="text-align:left;padding:8px 12px;color:#34332D;vertical-align:top">Cohen</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2027</td></tr><tr style="border-bottom:1.5px solid #2E2C26"><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">4</td><td style="text-align:left;padding:8px 12px;color:#34332D;vertical-align:top">Dupont</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2027</td></tr></table></div> Matiere <div class="overflow-x-auto my-6"><table style="border-collapse:collapse;width:100%" class="text-xs"><tr style="border-top:1.5px solid #2E2C26;border-bottom:1.5px solid #2E2C26"><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">code</th><th style="text-align:left;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">intitule</th><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">coef</th></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">10</td><td style="text-align:left;padding:8px 12px;color:#34332D;vertical-align:top">Informatique</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">4</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">11</td><td style="text-align:left;padding:8px 12px;color:#34332D;vertical-align:top">Mathématiques</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">9</td></tr><tr style="border-bottom:1.5px solid #2E2C26"><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">12</td><td style="text-align:left;padding:8px 12px;color:#34332D;vertical-align:top">Physique</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">6</td></tr></table></div>

Note <div class="overflow-x-auto my-6"><table style="border-collapse:collapse;width:100%" class="text-xs"><tr style="border-top:1.5px solid #2E2C26;border-bottom:1.5px solid #2E2C26"><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">eleve</th><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">matiere</th><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">valeur</th></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">1</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">10</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">17</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">1</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">11</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">12</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">10</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">8</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">12</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">14</td></tr><tr style="border-bottom:1.5px solid #2E2C26"><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">3</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">11</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">15</td></tr></table></div>

Les attributs en gras forment la clé primaire. L'élève 4 n'a aucune note — ce détail servira.

Définition 21.3Clé primaire

La clé primaire est un ensemble d'attributs identifiant chaque ligne de façon unique. Le programme précise : « une clé primaire n'est pas forcément associée à un unique attribut même si c'est le cas le plus fréquent ». Dans Note, elle est le couple (eleve, matiere) : un élève n'a qu'une note par matière, mais plusieurs notes en tout.

21.3 Entités, associations, clés étrangères

Définition 21.4Clé étrangère

Une clé étrangère est un attribut dont les valeurs sont des clés primaires d'une autre table. Elle matérialise un lien.

C'est très exactement ce que le chapitre chap:hachage appelait sérialiser un graphe : en mémoire un lien est une adresse, dans une base c'est une valeur. Elle a un sens hors du processus, elle survit à l'extinction de la machine, et elle se vérifie.

Définition 21.5Les trois cardinalités

Le programme demande le modèle entité-association « au travers de cas concrets d'associations $1 - 1$ , $1 - *$ , $* - *$ ».

Type	Exemple	Comment on le traduit
$1 - 1$	un élève, un dossier	clé étrangère d'un côté
$1 - *$	une promo, plusieurs élèves	clé étrangère du côté « plusieurs »
$* - *$	élèves et matières	une table intermédiaire

ImportantUne association

* - *

se scinde en deux associations

1 - *

C'est la phrase du programme, et c'est la règle de modélisation la plus utile du chapitre. Un élève suit plusieurs matières, une matière est suivie par plusieurs élèves : on ne peut mettre la clé étrangère ni d'un côté ni de l'autre. On introduit donc une table intermédiaire — ici Note — dont chaque ligne représente un couple.

La table intermédiaire n'est pas un artifice technique : elle porte souvent une information propre — ici la valeur de la note, qui n'appartient ni à l'élève ni à la matière.

21.4 Interroger

21.4.1 Sélection, projection, renommage

Définition 21.6La requête de base


SELECT nom, promo          -- PROJECTION : quelles colonnes
FROM Eleve                 -- la table
WHERE promo = 2027;        -- SÉLECTION : quelles lignes

Les opérateurs autorisés dans le WHERE sont limitativement ceux-ci : + - * /, = <> < > <= >=, AND OR NOT, IS NULL, IS NOT NULL.

Définition 21.7Renommage, tri, dédoublonnage, découpage


SELECT DISTINCT promo AS annee_de_sortie
FROM Eleve
ORDER BY promo DESC
LIMIT 10 OFFSET 0;

DISTINCT supprime les doublons, ORDER BY trie, LIMIT et OFFSET découpent le résultat.

Attention`NULL` n'est pas une valeur, et ne se compare pas

valeur = NULL n'est jamais vrai — pas même quand la valeur est effectivement absente. NULL signifie « on ne sait pas », et toute comparaison avec l'inconnu rend l'inconnu. C'est pourquoi IS NULL et IS NOT NULL existent, et pourquoi ils sont au programme.

Conséquence pratique : une somme ignore les NULL, et une moyenne aussi — mais un COUNT(colonne) les ignore là où COUNT(*) les compte. Deux comptages sur la même table peuvent donc légitimement différer.

21.4.2 Opérateurs ensemblistes

Définition 21.8`UNION`, `INTERSECT`, `EXCEPT`

Ils combinent les résultats de deux requêtes de même schéma :


SELECT eleve FROM Note WHERE matiere = 10
EXCEPT
SELECT eleve FROM Note WHERE matiere = 11;
-- les élèves notés en informatique et PAS en mathématiques : {2}

Le produit cartésien s'obtient en nommant deux tables dans le FROM sans condition — il produit toutes les combinaisons, et c'est presque toujours une faute lorsqu'il n'est pas voulu.

21.4.3 Jointures

ImportantLa jointure est ce qui donne son sens au modèle relationnel

Une jointure recolle deux tables suivant une condition — en pratique, l'égalité d'une clé étrangère et d'une clé primaire. Le programme demande de les présenter « en lien avec la notion d'associations entre entités » : la jointure parcourt l'association que la clé étrangère a déclarée.


SELECT e.nom, m.intitule, n.valeur
FROM Note AS n
JOIN Eleve AS e ON n.eleve = e.id
JOIN Matiere AS m ON n.matiere = m.code;

nom	intitule	valeur
Alaoui	Informatique	17
Alaoui	Mathématiques	12
Benali	Informatique	8
Benali	Physique	14
Cohen	Mathématiques	15

Cinq lignes : autant que la table Note. Dupont n'apparaît pas.

Définition 21.9Jointure externe à gauche

LEFT JOIN conserve toutes les lignes de la table de gauche, en complétant par NULL celles qui n'ont pas de correspondant.


SELECT e.nom, n.valeur
FROM Eleve AS e
LEFT JOIN Note AS n ON n.eleve = e.id;

nom	valeur
Alaoui	17
Alaoui	12
Benali	8
Benali	14
Cohen	15
Dupont	NULL

ImportantChoisir entre `JOIN` et `LEFT JOIN` n'est pas un détail de style

La question qui tranche : veut-on voir les entités qui n'ont aucun correspondant ? « Quelle est la moyenne de chaque élève ? » n'a pas la même réponse selon qu'on veut ou non voir Dupont, sans note. Avec JOIN, il disparaît du résultat — silencieusement. Avec LEFT JOIN, il apparaît avec une moyenne NULL.

C'est l'erreur la plus fréquente et la plus difficile à repérer, parce que le résultat est plausible : il manque simplement des lignes, et rien ne le signale.

21.4.4 Agrégation

Définition 21.10Les cinq fonctions, et `GROUP BY`

Le programme retient MIN, MAX, SUM, AVG, COUNT, « y compris avec GROUP BY », et s'en tient à la norme SQL99.


SELECT e.nom, COUNT(*) AS nb, AVG(n.valeur) AS moyenne
FROM Eleve AS e
JOIN Note AS n ON n.eleve = e.id
GROUP BY e.id, e.nom;

nom	nb	moyenne
Alaoui	2	14.5
Benali	2	11.0
Cohen	1	15.0

GROUP BY partage les lignes en paquets ; chaque fonction d'agrégation rend une valeur par paquet.

Définition 21.11`HAVING` : filtrer les agrégats


SELECT e.nom, AVG(n.valeur) AS moyenne
FROM Eleve AS e
JOIN Note AS n ON n.eleve = e.id
GROUP BY e.id, e.nom
HAVING AVG(n.valeur) >= 14;

Résultat : Alaoui ( $14, 5$ ) et Cohen ( $15$ ).

Important`WHERE` et `HAVING` : la différence, sur un exemple

Le programme demande de « marquer la différence entre WHERE et HAVING sur des exemples ». Elle tient au moment où le filtre agit.

`WHERE`	filtre les lignes, avant le regroupement
`HAVING`	filtre les paquets, après le regroupement


-- (a) moyenne des notes >= 12, par élève : le filtre agit sur les NOTES
SELECT e.nom, AVG(n.valeur) FROM Eleve e JOIN Note n ON n.eleve = e.id
WHERE n.valeur >= 12 GROUP BY e.id, e.nom;

-- (b) élèves dont la moyenne GÉNÉRALE est >= 12 : le filtre agit sur le PAQUET
SELECT e.nom, AVG(n.valeur) FROM Eleve e JOIN Note n ON n.eleve = e.id
GROUP BY e.id, e.nom HAVING AVG(n.valeur) >= 12;

Pour Benali (notes $8$ et $14$ ) : la requête (a) le fait apparaître avec une moyenne de $14$ — elle a jeté le $8$ — et la requête (b) l'écarte, sa vraie moyenne valant $11$ . Deux requêtes qui se ressemblent, deux questions différentes, et une seule répond à « qui a la moyenne ».

Corollaire : une fonction d'agrégation ne peut jamais figurer dans un WHERE, puisque les paquets n'existent pas encore.

iRemarqueRequêtes imbriquées

Le programme en demande « quelques exemples ». Une requête peut figurer dans le WHERE d'une autre :


SELECT nom FROM Eleve
WHERE id IN (SELECT eleve FROM Note WHERE valeur >= 15);
-- les élèves ayant au moins une note >= 15 : Alaoui, Cohen

21.5 L'ordre d'évaluation

ImportantUne requête ne s'exécute pas dans l'ordre où elle s'écrit

C'est ce qui explique la plupart des surprises. L'ordre logique est :

SELECT est évalué avant-dernier : c'est pourquoi un alias défini dans le SELECT ne peut pas être utilisé dans le WHERE — il n'existe pas encore —, mais peut l'être dans le ORDER BY, qui vient après.

21.6 Ce qu'il faut retenir

ImportantSQL : six points

On décrit le résultat, pas le parcours. C'est le paradigme déclaratif.
Une clé étrangère est un lien écrit comme une valeur — un pointeur qui survit à l'extinction de la machine.
Une association $* - *$ se traduit par une table intermédiaire, qui porte souvent une information propre.
JOIN perd les entités sans correspondant, LEFT JOIN les garde. Le choix change la réponse, et rien ne le signale.
WHERE filtre les lignes avant le regroupement, HAVING les paquets après.
NULL n'est pas une valeur : il ne se compare qu'avec IS NULL.

Continuer sur Adloun : animation, QCM, fiches, exercices