Bases de données et requêtes simples

Résumé en cinq points

1Vocabulaire des bases de données

1.1Tables ou relations, attributs ou colonnes

Une table est un ensemble de lignes. Deux conséquences :

• l'ordre des lignes n'a aucun sens : une table n'a pas de « première ligne » tant qu'on ne l'a pas triée ;
• l'ordre des colonnes n'a pas d'importance non plus : on désigne toujours un attribut par son nom, jamais par sa position.

Le vocabulaire double du programme vient de là : relation et attribut du côté mathématique, table et colonne du côté logiciel.

1.2Domaine, types de données et schéma de table

Sont explicitement hors programme :

• les types propres à chaque moteur SQL ;
• la représentation des dates : on utilise à la place des nombres ou des chaînes dont l'ordre suit le temps, par exemple '2026-09-24' ;
• les collations : on suppose l'ordre lexicographique usuel sur les chaînes ;
• la valeur NULL.

1.3Clé primaire

Dans l'exemple, id est la clé primaire de chaque table.

Une clé primaire n'est pas forcément un unique attribut, même si c'est le cas le plus fréquent. Dans ce cas, c'est le n-uplet de valeurs qui est unique, pas chaque attribut séparément.

La notion d'index est hors programme.

2Entités, associations et clés étrangères

2.1Modèle entité-association

Pour concevoir une base, on repère :

• des entités, objets du monde réel décrits par des attributs : une ville, une gare, un trajet ;
• des associations entre ces entités : une gare est située dans une ville, un trajet part d'une gare.

Aucun formalisme graphique précis n'est au programme, mais on peut représenter entités et associations par des diagrammes sagittaux informels, des flèches entre des ensembles.

2.2Cardinalités 1-1, 1-*, *-*

On classe les associations selon le nombre d'éléments liés de chaque côté.

2.3Clé étrangère

Dans l'exemple, gare.id_ville est une clé étrangère qui référence ville.id, et trajet.id_depart et trajet.id_arrivee référencent gare.id.

Les associations 1-1 et 1-* se traduisent directement par une clé étrangère :

• pour une association 1-*, elle est placée du côté « plusieurs » ;
• pour une association 1-1, elle peut être placée dans l'une ou l'autre des deux tables.

C'est la gare qui porte l'identifiant de sa ville, pas l'inverse, car une ville aurait besoin d'un nombre variable de colonnes pour lister ses gares.

2.4Séparation d'une association *-* en deux associations 1-*

Une association *-* ne peut pas se traduire par une seule clé étrangère. On crée une table intermédiaire, dite table d'association, dont chaque ligne représente un lien.

Pour élèves et cours, la table inscription(id_eleve, id_cours) contient deux clés étrangères, vers eleve.id et vers cours.id. L'association *-* devient ainsi deux associations 1-* : un élève a plusieurs inscriptions, un cours a plusieurs inscriptions, chaque inscription concerne un seul élève et un seul cours.

3Requêtes SELECT

3.1Structure d'une requête et projection

Une requête d'interrogation s'écrit :

SELECT nom, population FROM ville

Elle renvoie une nouvelle table, formée des colonnes nom et population de toutes les lignes de ville. Choisir des colonnes s'appelle la projection. SELECT * renvoie toutes les colonnes. Le résultat d'une requête est toujours une table, ce qui permet d'imbriquer des requêtes, point vu au chapitre suivant.

3.2Sélection avec une clause WHERE

La clause WHERE ne garde que les lignes qui vérifient une condition. C'est la sélection.

SELECT nom FROM ville
WHERE pays = 'France' AND population > 100000

Les opérateurs au programme sont :

• les opérations arithmétiques +, -, *, /, en passant outre les subtilités de la division entière ou flottante ;
• les comparaisons =, <>, <, <=, >, >= ;
• les connecteurs AND, OR, NOT.

En SQL, l'égalité s'écrit avec un seul signe égal et la différence avec <>. Les chaînes sont entre apostrophes. Les comparaisons de chaînes suivent l'ordre lexicographique : 'Lille' < 'Lyon' est vrai.

Une condition peut contenir un calcul : WHERE prix / duree < 0.5 garde les trajets à moins de cinquante centimes la minute.

3.3Renommage avec AS

AS donne un nom à une colonne du résultat, en particulier à une colonne calculée.

SELECT id, prix / duree AS prix_par_minute
FROM trajet

Il sert aussi à renommer une table dans une requête, ce qui devient indispensable pour les autojointures.

3.4DISTINCT, ORDER BY, LIMIT et OFFSET

• SELECT DISTINCT supprime les lignes en double du résultat. SELECT DISTINCT pays FROM ville donne la liste des pays sans répétition. Sans DISTINCT, un pays apparaîtrait autant de fois qu'il a de villes.
• ORDER BY trie le résultat selon une ou plusieurs colonnes, par ordre croissant par défaut, décroissant avec DESC. ORDER BY pays, population DESC trie par pays, puis à pays égal par population décroissante.
• LIMIT n ne garde que les n premières lignes du résultat, OFFSET k saute les k premières. Combinés avec ORDER BY, ils répondent aux questions de classement.

SELECT nom FROM ville
ORDER BY population DESC
LIMIT 1 OFFSET 2

Cette requête renvoie la troisième ville la plus peuplée.

Ordre d'écriture des clauses à respecter : SELECT, FROM, WHERE, puis ORDER BY, puis LIMIT et OFFSET.

Ordre logique d'évaluation, qui explique le sens de la requête. Le moteur :

1. part des tables du FROM ;
2. filtre les lignes avec WHERE ;
3. calcule les colonnes du SELECT ;
4. supprime les doublons si DISTINCT est présent ;
5. trie avec ORDER BY ;
6. découpe avec LIMIT et OFFSET.

La norme ne permet pas d'utiliser dans le WHERE un alias défini dans le SELECT, même si certains logiciels le tolèrent, alors qu'on peut trier sur cet alias dans ORDER BY.

4Opérateurs ensemblistes et produit cartésien

4.1UNION, INTERSECT, EXCEPT

Deux requêtes dont les résultats ont le même nombre de colonnes, de types compatibles, se combinent comme des ensembles.

• R1 UNION R2 renvoie les lignes présentes dans l'un ou l'autre résultat.
• R1 INTERSECT R2 renvoie les lignes présentes dans les deux.
• R1 EXCEPT R2 renvoie les lignes de R1 absentes de R2.

Ces opérateurs travaillent sur des ensembles et suppriment les doublons.

SELECT id_depart FROM trajet
INTERSECT
SELECT id_arrivee FROM trajet

L'opérateur ensembliste devient indispensable quand les deux ensembles viennent de colonnes ou de tables différentes, comme ici.

SELECT id_depart FROM trajet
EXCEPT
SELECT id_arrivee FROM trajet

4.2Produit cartésien

Écrire deux tables dans la clause FROM, séparées par une virgule, forme leur produit cartésien : toutes les combinaisons d'une ligne de la première et d'une ligne de la seconde. Si ville a 100 lignes et gare 300, ville, gare en a 30 000. Seules quelques-unes ont un sens, celles où la gare est dans la ville, et on les garde avec une condition.

SELECT gare.nom, ville.nom FROM gare, ville
WHERE gare.id_ville = ville.id

Le nombre de lignes d'un produit cartésien est le produit des nombres de lignes, et son nombre de colonnes la somme des nombres de colonnes. Ville, gare a donc 4 + 4 = 8 colonnes.

Quand deux tables ont des attributs de même nom, on les préfixe par le nom de la table. Ce produit suivi d'une sélection est exactement ce que réalise une jointure, écrite plus proprement avec JOIN … ON au chapitre suivant.

5Mise en œuvre et limites du programme

La mise en œuvre se fait avec un logiciel qui permet d'interroger une base existante par des requêtes SQL.

Sont hors programme :

• la création et la suppression de tables en SQL ;
• la récupération du résultat d'une requête depuis un programme Python ;
• la distinction entre modèle logique et physique ;
• les bases non relationnelles ;
• les méthodes de modélisation ;
• les fragments du langage autres que l'interrogation, et les transactions ;
• l'optimisation des requêtes.

Méthodes

Ce qu'il faut savoir refaire en khôlle

1. Définir table, attribut, domaine, schéma, ligne, clé primaire et clé étrangère sur une base fournie.
2. À partir d'une description en français, identifier entités et associations, leurs cardinalités, et proposer un schéma de tables avec clés primaires et étrangères, en traitant une association *-* par une table intermédiaire.
3. Écrire des requêtes de sélection et de projection avec conditions composées, colonnes calculées et renommage.
4. Utiliser DISTINCT, ORDER BY, LIMIT et OFFSET pour répondre à une question de classement.
5. Utiliser UNION, INTERSECT ou EXCEPT, et expliquer pourquoi les deux requêtes doivent avoir des colonnes compatibles.
6. Expliquer ce que produit un produit cartésien, son nombre de lignes, et comment une condition le transforme en jointure.

Les résultats à connaître

En SQL, les « résultats » sont les propriétés exactes des clés et des requêtes. On doit pouvoir les énoncer puis les justifier sur un exemple.

• Deux lignes distinctes d'une table n'ont jamais la même valeur de clé primaire : c'est la définition, exigible.
• Toute valeur d'une clé étrangère est une valeur de la clé primaire qu'elle référence : c'est la définition, exigible.
• Une association 1-∗ se traduit par une clé étrangère placée du côté « plusieurs » : exigible avec justification.
• Une association ∗-∗ se traduit par une table d'association à deux clés étrangères. Celle-ci remplace l'association par deux associations 1-∗ : exigible avec justification.
• Le résultat d'un SELECT sans DISTINCT peut contenir des lignes en double, même si les tables interrogées n'en ont pas : exigible, à savoir illustrer.
• Une projection qui contient la clé primaire de la table interrogée ne produit aucun doublon, et DISTINCT y est inutile : exigible avec justification.
• DISTINCT porte sur la ligne entière du résultat, pas sur la première colonne : exigible.
• NOT est prioritaire sur AND, et AND est prioritaire sur OR : exigible.
• Ordre logique d'évaluation : FROM, WHERE, SELECT, DISTINCT, ORDER BY, LIMIT et OFFSET. Il fixe le sens de la requête : exigible.
• UNION, INTERSECT et EXCEPT exigent le même nombre de colonnes, de types compatibles, et suppriment les doublons : exigible.
• UNION et INTERSECT sont commutatifs, EXCEPT ne l'est pas : exigible.
• Le produit cartésien de tables à n et p lignes, et à a et b colonnes, a n × p lignes et a + b colonnes : exigible avec justification.
• Si A.cle_etr référence B.cle, le produit de A et B filtré par A.cle_etr = B.cle a exactement autant de lignes que A : exigible avec justification.
• Hors programme signalé : NULL, les types de dates, les index, les collations, la création de tables, l'algèbre relationnelle, les opérateurs absents de la liste officielle (LIKE, IN, BETWEEN). Les jointures JOIN … ON, l'agrégation et les requêtes imbriquées relèvent du chapitre suivant.

Clés de démonstration

Clé étrangère du côté « plusieurs ». Une gare est dans une seule ville, elle porte donc une seule valeur id_ville. Une ville a un nombre variable de gares, elle devrait porter un nombre variable de colonnes, ce qu'un schéma fixe interdit. L'astuce est de se demander « combien de valeurs faudrait-il stocker dans chaque ligne ? ». La réponse doit être exactement une.

Table d'association. Si l'on met id_cours dans la table eleve, un élève qui suit trois cours occupe trois lignes. Son nom est alors répété, et la clé id n'est plus unique. La table inscription stocke un lien par ligne, et chaque ligne pointe vers un seul élève et un seul cours. L'astuce est de repérer le mot « plusieurs » des deux côtés de la phrase qui décrit l'association.

Projection contenant la clé. Si deux lignes du résultat étaient égales, elles auraient la même valeur de clé. Elles proviendraient donc de la même ligne de la table, puisque la clé est unique. Or chaque ligne de la table donne une seule ligne du résultat. L'astuce est de remonter de la ligne du résultat vers sa ligne d'origine.

Nombre de lignes du produit. Chaque ligne du produit est un couple formé d'une ligne de A et d'une ligne de B. On compte donc n choix puis p choix, soit n × p couples. Les colonnes se juxtaposent, d'où a + b colonnes. L'astuce est de voir une ligne du produit comme un couple.

Produit filtré par une clé étrangère. On fixe une ligne de A. Sa valeur de clé étrangère existe dans B, c'est la définition. Elle y apparaît une seule fois, puisque c'est une clé primaire. Chaque ligne de A est donc associée à exactement une ligne de B. L'astuce est d'utiliser les deux propriétés : l'existence vient de la clé étrangère, l'unicité de la clé primaire.

Alias et WHERE. Le WHERE est évalué avant le SELECT. Un alias défini dans le SELECT n'existe donc pas encore au moment du filtrage. L'astuce est de réciter l'ordre logique d'évaluation.

Exemples et contre-exemples à connaître

1. WHERE NOT pays = 'France' AND population > 1000000 garde les grandes villes étrangères. Le NOT ne porte que sur la première comparaison.
2. SELECT DISTINCT nom, pays FROM ville peut afficher deux fois le même nom, avec deux pays différents. DISTINCT élimine des lignes entières.
3. Deux villes homonymes dans deux pays : une requête avec OR sans DISTINCT affiche ce nom deux fois, une UNION ne l'affiche qu'une fois. Le résultat n'est pas le même.
4. SELECT nom FROM ville WHERE pays = 'France' INTERSECT SELECT nom FROM ville WHERE population > 1000000 renvoie à tort une petite ville française homonyme d'une grande ville étrangère. On combine les identifiants, pas les noms.
5. ORDER BY population DESC LIMIT 1 ne renvoie qu'une ville même si deux villes sont ex æquo. La ville écartée est arbitraire.
6. LIMIT 5 sans ORDER BY renvoie cinq lignes quelconques. Le résultat peut changer d'un logiciel à l'autre.
7. FROM ville, gare sans condition compte 30 000 lignes au lieu de 300. La requête ne signale aucune erreur.
8. R1 EXCEPT R2 et R2 EXCEPT R1 sont en général différents. Les gares de départ jamais atteintes ne sont pas les gares d'arrivée d'où l'on ne repart jamais.
9. Si un élève peut repasser un cours, le couple (id_eleve, id_cours) n'est plus une clé primaire. Une clé se choisit d'après le monde réel, pas d'après les données présentes.
10. Pour comparer deux lignes d'une même table, on écrit FROM trajet AS t1, trajet AS t2. Sans renommage, on ne peut pas désigner les deux exemplaires de la table.

Techniques du chapitre

Technique 1, découper la question. Mini-exercice : les noms des gares de plus de dix quais. On affiche des noms (SELECT nom), on les lit dans une seule table (FROM gare), et on garde certaines lignes (WHERE nb_quais > 10). Aucun doublon n'est à craindre si l'on ajoute l'id, sinon on se demande si deux gares peuvent porter le même nom.

Technique 2, une condition de liaison par flèche. Mini-exercice : combien de conditions pour relier trajet, gare et ville ? Il y a deux flèches, de trajet vers gare et de gare vers ville, donc deux égalités au minimum. La règle générale : k tables reliées en chaîne demandent k − 1 égalités de clés.

Technique 3, classement et pagination. Mini-exercice : afficher la page k d'une liste de villes triée par nom, à 20 villes par page. On écrit ORDER BY nom LIMIT 20 OFFSET 20 * (k - 1) avec k remplacé par sa valeur. La page 1 correspond à OFFSET 0.

Technique 4, la négation par EXCEPT. Mini-exercice : les villes sans gare. On écrit SELECT id FROM ville EXCEPT SELECT id_ville FROM gare. Écrire WHERE ville.id <> gare.id_ville sur le produit serait faux, car une ville avec une gare est différente de toutes les autres gares.

Technique 5, renommer pour comparer une table à elle-même. Mini-exercice : les couples de trajets aller et retour. On écrit FROM trajet AS t1, trajet AS t2 WHERE t1.id_depart = t2.id_arrivee AND t1.id_arrivee = t2.id_depart. Chaque couple apparaît deux fois, dans les deux sens. On ajoute t1.id < t2.id pour ne le garder qu'une fois.

Technique 6, lire les cardinalités dans une phrase. Mini-exercice : « chaque patient a un médecin traitant, un médecin suit plusieurs patients ». L'association est 1-∗, et la clé étrangère id_medecin va dans la table patient. Aucune table intermédiaire n'est nécessaire.

Technique 7, majorer le nombre de lignes. Mini-exercice : combien de lignes au plus pour SELECT id_depart FROM trajet UNION SELECT id_arrivee FROM trajet ? Le résultat ne contient que des identifiants de gares distincts, donc au plus le nombre de gares, même si trajet est bien plus grande.

Exercices classiques corrigés

Tous les exercices utilisent la base de films suivante. film(id, titre, annee, duree, id_realisateur), où duree est en minutes. personne(id, nom, nationalite). role(id_film, id_acteur, personnage). Les attributs id_realisateur et id_acteur référencent personne.id, et id_film référence film.id.

Choisis ton niveau. Difficulté indicative ; les exercices qui se suivent restent dans l'ordre.

SELECT titre FROM film
WHERE duree > 120 AND (annee < 1980 OR annee > 2010)

SELECT titre, duree / 60 AS heures FROM film
ORDER BY duree DESC, titre

SELECT DISTINCT personne.nationalite
FROM film, personne
WHERE film.id_realisateur = personne.id AND film.annee > 2000

SELECT personne.id, nom FROM personne, role
WHERE personne.id = role.id_acteur
EXCEPT
SELECT personne.id, nom FROM personne, film
WHERE personne.id = film.id_realisateur

SELECT id_acteur FROM role INTERSECT SELECT id_realisateur FROM film

SELECT DISTINCT personne.id, personne.nom
FROM personne, film, role
WHERE role.id_film = film.id AND film.id_realisateur = personne.id
  AND role.id_acteur = personne.id

SELECT DISTINCT r1.id_acteur AS a1, r2.id_acteur AS a2
FROM role AS r1, role AS r2
WHERE r1.id_film = r2.id_film AND r1.id_acteur < r2.id_acteur

SELECT DISTINCT membre.id, membre.nom
FROM membre, reservation, trajet
WHERE membre.id = reservation.id_passager
  AND reservation.id_trajet = trajet.id AND trajet.depart = 'Lyon'

SELECT id, titre FROM film
EXCEPT
SELECT f1.id, f1.titre FROM film AS f1, film AS f2
WHERE f1.id_realisateur = f2.id_realisateur AND f2.duree > f1.duree

Erreurs fréquentes