Bases de données relationnelles et SQL

Le modèle relationnel : relations, attributs et schéma#

~6 min de lectureBaseeduscol-programme-nsi-terminale

Anatomie d'une relation : attributs, n-uplets et domaines

Fig. 1La relation eleve : les attributs (id, nom, prenom, classe) forment les colonnes ; chaque n-uplet (enregistrement) est une ligne ; toutes les valeurs d'une colonne partagent le même domaine. L'attribut id, mis en évidence, joue le rôle de clé primaire.

Une relation (couramment appelée table) est un ensemble de n-uplets (lignes, ou enregistrements). Chaque relation est décrite par un nom et une liste d'attributs (colonnes), par exemple eleve(id, nom, prenom, classe).

Le domaine d'un attribut est l'ensemble des valeurs qu'il peut prendre (entiers, chaînes de caractères, dates, booléens…). Toutes les valeurs d'une même colonne appartiennent au même domaine : une colonne age contient des entiers, jamais du texte.

Le schéma relationnel d'une relation est la donnée de son nom et de ses attributs (avec leurs domaines) ; le schéma d'une base est l'ensemble des schémas de ses relations, complété par les clés. Il décrit la structure (l'intension), indépendamment des données réelles (l'extension) qui peuvent changer.

Une relation est un ensemble : il n'y a pas de doublon de n-uplet et l'ordre des lignes n'a aucune signification. De même, l'ordre des colonnes n'est pas porteur de sens — on désigne un attribut par son nom, jamais par sa position.

Rappel de première : on manipulait déjà des données tabulées (fichiers CSV, p-uplets nommés). Le modèle relationnel formalise cette idée en y ajoutant les clés et les contraintes, et en confiant la gestion à un logiciel dédié, le SGBD.

Le programme demande explicitement de REPÉRER LES ANOMALIES d'un schéma, et c'est la question la plus discriminante du thème. Trois familles se reconnaissent à l'œil. L'anomalie de REDONDANCE : la même information est écrite plusieurs fois — le nom de l'auteur répété sur chacun de ses livres —, si bien qu'une correction doit être faite partout à la fois. L'anomalie d'INSERTION : on ne peut pas enregistrer un fait sans en inventer un autre — impossible d'entrer un auteur qui n'a pas encore publié, si l'auteur n'existe que comme colonne de la table des livres. L'anomalie de SUPPRESSION : effacer une ligne détruit une information sans rapport — supprimer le dernier livre d'un auteur efface l'auteur. Le remède est toujours le même : sortir l'entité mêlée dans sa propre relation et la référencer par une clé étrangère.

Distinguer la STRUCTURE d'une base de son CONTENU est une capacité attendue en soi. La structure — l'ensemble des schémas relationnels, avec les noms d'attributs, leurs domaines et les contraintes — est stable, décidée à la conception, et c'est elle qu'on dessine. Le contenu — les n-uplets effectivement présents à un instant donné, aussi appelé l'extension — change à chaque insertion. Une question du type « ce schéma est-il correct ? » porte sur la structure ; une question du type « cette insertion est-elle acceptée ? » porte sur la rencontre entre le contenu proposé et les contraintes de la structure.

Vocabulaire

→ Cartes

relationEnsemble de n-uplets partageant le même schéma ; couramment appelée table.
attributColonne d'une relation, caractérisée par son nom et son domaine.
domaineEnsemble des valeurs qu'un attribut peut prendre.
schéma relationnelNom d'une relation et liste de ses attributs avec leurs domaines et leurs contraintes.
anomalie de schémaDéfaut de conception provoquant redondance, ou impossibilité d'insérer ou de supprimer une information isolément.

Notation d'un schéma de relation

\text{relation : } \mathtt{eleve}(\underline{\mathtt{id}},\ \mathtt{nom},\ \mathtt{prenom},\ \mathtt{classe})

On note le nom de la relation suivi de ses attributs entre parenthèses. L'attribut souligné (ici id) désigne la clé primaire. C'est l'intension : la structure, indépendante des lignes réellement stockées.

La lecture charge du contenu depuis YouTube (Google).Ouvrir sur YouTube ↗

Exemple corrigé

Décrire une relation à partir de son extension

On donne la relation livre(isbn, titre, annee, id_auteur). On affiche trois n-uplets : (978-2070, 'Candide', 1759, 7), (978-2253, 'Germinal', 1885, 12), (978-2070, 'Zadig', 1747, 7). Nommez la relation, ses attributs, proposez un domaine pour chacun, et indiquez combien de n-uplets sont présents. Y a-t-il un problème ?

Identifier relation et attributs
La relation s'appelle livre. Ses attributs (colonnes) sont isbn, titre, annee et id_auteur. C'est l'intension (le schéma) ; les trois lignes affichées sont l'extension (les données).
Proposer un domaine pour chaque attribut
isbn : chaîne de caractères (le tiret et les chiffres en font un identifiant texte, pas un nombre). titre : chaîne de caractères. annee : entier. id_auteur : entier (il référencera plus tard un auteur).
$\mathtt{livre}(\underline{\mathtt{isbn}},\ \mathtt{titre},\ \mathtt{annee},\ \mathtt{id\_auteur})$
Compter les n-uplets et repérer l'anomalie
Trois lignes sont affichées, mais deux portent le même isbn 978-2070 pour deux titres différents (« Candide » et « Zadig »). Si isbn est censé identifier de façon unique un livre, ces deux n-uplets violent l'unicité attendue de cet identifiant : isbn ne peut pas servir de clé primaire telle quelle.

Résultat : Relation livre, quatre attributs (isbn : texte ; titre : texte ; annee : entier ; id_auteur : entier), trois n-uplets affichés. Anomalie : l'isbn 978-2070 apparaît deux fois pour deux livres distincts — il ne peut donc pas faire office de clé primaire, qui doit être unique.

Objectif Bac

Objectif Bac : savoir lire un schéma relationnel donné et nommer correctement chaque concept — distinguer relation, attribut, domaine, n-uplet — sans confondre la structure (schéma) et le contenu (les lignes).
Objectif Bac : à partir d'une description en français d'un problème (« on gère des livres et leurs auteurs »), proposer un schéma relationnel cohérent, c'est-à-dire choisir les relations, leurs attributs et le domaine de chacun.
Objectif Bac : repérer et NOMMER une anomalie de schéma — redondance, anomalie d'insertion, anomalie de suppression — puis proposer la décomposition en deux relations qui la supprime.
Objectif Bac : distinguer explicitement structure et contenu d'une base avant de répondre : la question porte-t-elle sur le schéma ou sur les n-uplets ?

Erreurs fréquentes

Confondre le schéma (la structure : noms d'attributs et domaines) avec l'extension (les n-uplets effectivement présents) : ajouter ou supprimer des lignes ne change pas le schéma.
Croire que l'ordre des lignes ou des colonnes a un sens, ou tolérer deux lignes parfaitement identiques : une relation est un ensemble de n-uplets, sans ordre ni doublon.
Confondre le modèle relationnel avec la représentation tabulaire vue en première : le tableau de données de première n'a ni clé, ni contrainte, ni accès concurrent — c'est précisément pourquoi le programme de terminale introduit les bases relationnelles.
Proposer comme clé primaire un attribut qui n'identifie pas de façon unique (le nom d'un auteur, le titre d'un livre) : deux personnes peuvent être homonymes et deux livres porter le même titre.

Révision active

Un club sportif veut gérer ses adhérents et les activités auxquelles ils sont inscrits. Proposez un schéma relationnel : nommez les relations, listez leurs attributs et indiquez pour chacun un domaine plausible. Repérez ensuite, dans votre schéma, ce qui relèvera plus tard d'une clé primaire.

S’entraîner sur des exercices associés50 questions sur ce thème

Rappel actif

Rappelle-toi les points clés — puis révèle.

Sources : Programme de l'enseignement de spécialité de NSI — classe terminale, voie générale (annexe de l'arrêté du 19-7-2019, BO spécial n° 8 du 25 juillet 2019) (Ministère de l’Éducation nationale — Bulletin officiel)

Clés primaires, clés étrangères et contraintes d'intégrité#

~5 min de lectureStandardeduscol-programme-nsi-terminale

Lien clé primaire ↔ clé étrangère entre deux relations

Fig. 2La table livre porte une clé étrangère id_auteur qui référence la clé primaire id de la table auteur. Intégrité référentielle : chaque id_auteur de livre doit exister dans auteur.id (ici 1 = Voltaire, 2 = Zola).

La clé primaire est un attribut (ou un petit groupe d'attributs) qui identifie de façon unique chaque n-uplet d'une relation. Elle doit être unique (jamais deux lignes avec la même valeur) et non NULL (toujours renseignée). On la souligne dans le schéma.

La clé étrangère est un attribut d'une relation qui référence la clé primaire d'une autre relation (ou de la même). Elle matérialise un lien entre tables : id_auteur dans livre référence id dans auteur.

Contrainte d'intégrité de relation (ou d'entité) : la clé primaire est unique et non NULL. Conséquence : on ne peut pas insérer deux n-uplets de même clé, ni laisser la clé primaire vide.

Contrainte d'intégrité référentielle : toute valeur prise par une clé étrangère doit exister comme valeur de clé primaire dans la table référencée (ou valoir NULL si autorisé). On ne peut pas référencer un auteur inexistant.

Conséquence pratique des contraintes : on ne peut pas supprimer un n-uplet encore référencé (supprimer un auteur dont des livres dépendent romprait l'intégrité référentielle), ni insérer un livre dont l'id_auteur ne correspond à aucun auteur. C'est le SGBD qui fait respecter ces règles.

Le programme nomme TROIS contraintes d'intégrité et les distingue soigneusement. La contrainte de DOMAINE exige que chaque valeur appartienne au domaine déclaré de son attribut : une date dans un attribut déclaré DATE, un entier positif dans un attribut d'année. La contrainte de RELATION (ou d'entité) exige que la clé primaire soit unique et jamais NULL : sans elle, deux lignes deviendraient indiscernables. La contrainte de RÉFÉRENCE (intégrité référentielle) exige que toute valeur non nulle d'une clé étrangère existe effectivement comme clé primaire dans la relation référencée : sans elle, un livre pourrait pointer vers un auteur inexistant. Une question d'examen qui demande « quelle contrainte est violée ? » attend l'un de ces trois noms, pas une paraphrase.

Vocabulaire

→ Cartes

clé primaireAttribut ou groupe d'attributs identifiant de façon unique chaque n-uplet d'une relation ; unique et non NULL.
clé étrangèreAttribut d'une relation dont la valeur doit exister comme clé primaire dans une autre relation.
intégrité référentielleContrainte garantissant qu'aucune clé étrangère ne référence un n-uplet inexistant.
contrainte de domaineContrainte imposant que chaque valeur appartienne à l'ensemble déclaré pour son attribut.

Intégrité référentielle (formalisée)

\forall\ t \in \mathtt{livre},\quad t.\mathtt{id\_auteur} \in \{\,a.\mathtt{id}\ :\ a \in \mathtt{auteur}\,\}\ \cup\ \{\mathtt{NULL}\}

Toute valeur de la clé étrangère id_auteur d'un n-uplet de livre doit appartenir à l'ensemble des clés primaires existantes de auteur (ou valoir NULL si c'est autorisé). C'est la règle que vérifie le SGBD à chaque insertion ou modification.

Exemple corrigé

Diagnostiquer des violations de contraintes d'intégrité

On a auteur(id, nom) et livre(isbn, titre, id_auteur), id_auteur étant clé étrangère vers auteur.id. auteur contient les id 7 et 12. On tente : (1) insérer livre('978-1', 'Essai', 99) ; (2) supprimer l'auteur 7 alors que le livre '978-1' (s'il existait) ou tout autre livre le référence. Pour chaque cas, dites la contrainte concernée et le verdict du SGBD.

Cas (1) — insertion d'un livre
On insère un livre avec id_auteur = 99. Or aucun auteur n'a l'id 99 (seuls 7 et 12 existent). La clé étrangère pointerait vers une clé primaire inexistante : c'est une violation de l'intégrité référentielle.
$99 \notin \{\,a.\mathtt{id}\ :\ a \in \mathtt{auteur}\,\} = \{7,\,12\}$
Verdict du cas (1)
Le SGBD refuse l'insertion. Pour qu'elle réussisse, il faudrait d'abord insérer l'auteur 99 dans auteur, puis insérer le livre.
Cas (2) — suppression d'un auteur référencé
On veut supprimer l'auteur 7. Si au moins un livre porte id_auteur = 7, supprimer l'auteur laisserait ces livres avec une clé étrangère pointant vers un n-uplet disparu : ce serait à nouveau une violation de l'intégrité référentielle.
Verdict du cas (2)
Tant que des livres référencent l'auteur 7, le SGBD refuse la suppression (sauf politique ON DELETE particulière). Il faut d'abord traiter les livres concernés (les supprimer ou réaffecter leur id_auteur).

Résultat : (1) Insertion refusée : id_auteur = 99 n'existe pas dans auteur (intégrité référentielle). (2) Suppression de l'auteur 7 refusée tant qu'un livre le référence (intégrité référentielle). Dans les deux cas, c'est le SGBD qui fait respecter automatiquement la contrainte.

Objectif Bac

Objectif Bac : sur un schéma à plusieurs tables, désigner la clé primaire de chaque relation et chaque clé étrangère, en indiquant quelle table elle référence.
Objectif Bac : étant donné une opération (insertion, suppression, modification) ou un jeu de données, repérer si une contrainte d'intégrité (de relation ou référentielle) est violée et expliquer pourquoi.
Objectif Bac : nommer la contrainte violée parmi les trois du programme — domaine, relation, référence — plutôt que de décrire l'effet en français.
Objectif Bac : expliquer pourquoi une clé primaire ne peut pas être NULL, en partant de sa fonction : identifier de façon unique.
Objectif Bac : tracer les clés étrangères d'un schéma sous forme de flèches vers les clés primaires référencées, ce qui rend les dépendances vérifiables d'un coup d'œil.

Erreurs fréquentes

Confondre clé primaire et clé étrangère : la clé primaire identifie les lignes de sa propre table ; la clé étrangère pointe vers la clé primaire d'une autre table.
Oublier qu'une clé primaire doit être non NULL et unique, ou autoriser une clé étrangère à pointer vers une valeur inexistante : ce sont précisément les deux contraintes d'intégrité que le SGBD interdit de violer.
Croire qu'une clé étrangère doit porter le même nom que la clé primaire qu'elle référence : seuls le domaine et la valeur doivent correspondre.
Oublier qu'une clé primaire peut être COMPOSÉE de plusieurs attributs, notamment dans une relation d'association entre deux entités.

Révision active

On a auteur(id, nom) et livre(isbn, titre, id_auteur) où id_auteur est une clé étrangère vers auteur.id. La table auteur contient les id 7 et 12. On tente : (1) d'insérer livre('978-1', 'Essai', 99) ; (2) de supprimer l'auteur 7 alors qu'un livre le référence. Pour chaque opération, dites quelle contrainte est en jeu et si l'opération est acceptée.

S’entraîner sur des exercices associés50 questions sur ce thème

Rappel actif

Rappelle-toi les points clés — puis révèle.

Sources : Programme de l'enseignement de spécialité de NSI — classe terminale, voie générale (annexe de l'arrêté du 19-7-2019, BO spécial n° 8 du 25 juillet 2019) (Ministère de l’Éducation nationale — Bulletin officiel)

Le système de gestion de bases de données (SGBD)#

~5 min de lectureStandardeduscol-programme-nsi-terminale

Le SGBD, intermédiaire offrant quatre services autour de la base

Fig. 3Les applications n'accèdent jamais directement aux fichiers : elles passent par le SGBD (mis en évidence), qui garantit la persistance, la gestion des accès concurrents, l'efficacité et la sécurisation autour de la base de données centrale.

Un SGBD (Système de Gestion de Bases de Données) est le logiciel placé entre les applications et les données : il stocke la base, en garantit la cohérence et exécute les requêtes (SQLite, PostgreSQL, MySQL/MariaDB…). On parle de SGBD relationnel quand les données sont organisées en relations.

Persistance : les données survivent à l'arrêt du programme et de la machine — elles sont écrites durablement sur disque, contrairement aux structures en mémoire vive qui disparaissent à la fin de l'exécution.

Gestion des accès concurrents : plusieurs utilisateurs peuvent lire et écrire en même temps sans corrompre les données. Le SGBD organise ces accès au moyen de transactions (suites d'opérations exécutées de façon « tout ou rien ») pour préserver la cohérence.

Efficacité : le SGBD répond rapidement même sur de gros volumes, grâce à des structures internes d'indexation qui évitent de parcourir toute la table à chaque requête (sans que l'on ait à les programmer soi-même).

Sécurisation : le SGBD protège les données (gestion de droits d'accès par utilisateur, sauvegardes, journalisation permettant la reprise après panne). Ces quatre services — persistance, accès concurrents, efficacité, sécurisation — sont précisément ce qu'un SGBD apporte par rapport à de simples fichiers.

Le programme précise qu'il s'agit de comprendre le RÔLE et les ENJEUX de ces services « sans en détailler le fonctionnement » — la réponse attendue est donc un raisonnement, pas une description de mécanisme interne. Pour la gestion des accès concurrents, il suffit d'exposer le scénario du problème : deux clients lisent le même dernier siège disponible, tous deux le réservent, et la base se retrouve avec une double réservation. Le SGBD l'empêche par la notion de TRANSACTION — un groupe d'opérations qui se déroule entièrement ou pas du tout, et pendant lequel les autres utilisateurs ne voient pas d'état intermédiaire. Décrire ce scénario et nommer la transaction suffit ; détailler les verrous ne rapporte rien de plus.

Vocabulaire

→ Cartes

SGBDLogiciel intercalé entre les applications et les données, qui rend les services de persistance, de concurrence, d'efficacité et de sécurisation.
persistancePropriété des données de survivre à l'arrêt du programme et de la machine.
transactionGroupe d'opérations exécuté entièrement ou pas du tout, qui protège la base des états intermédiaires incohérents.
accès concurrentsLectures et écritures simultanées de plusieurs utilisateurs sur la même base, que le SGBD doit arbitrer.

Exemple corrigé

Associer une situation au service du SGBD concerné

Pour une billetterie en ligne, identifiez le service du SGBD mobilisé dans chacune de ces situations : (a) deux clients tentent au même instant d'acheter le dernier siège ; (b) après une coupure de courant, on doit retrouver toutes les ventes validées ; (c) une recherche par numéro de commande doit répondre instantanément sur des millions de lignes ; (d) seul un administrateur peut consulter les coordonnées bancaires.

(a) Dernier siège, deux clients simultanés
Deux écritures concurrentes sur la même donnée : c'est la gestion des accès concurrents. Via une transaction, le SGBD sérialise les opérations pour qu'un seul des deux clients obtienne le siège, sans double vente.
(b) Retrouver les ventes après coupure
Les données doivent survivre à l'arrêt brutal de la machine : c'est la persistance (écriture durable sur disque), renforcée par la sécurisation (journalisation/reprise après panne) qui garantit qu'une vente validée n'est pas perdue.
(c) Recherche instantanée sur des millions de lignes
Répondre vite sur un gros volume relève de l'efficacité : le SGBD utilise un index sur le numéro de commande pour ne pas parcourir toute la table.
(d) Accès réservé à l'administrateur
Restreindre l'accès à certaines données selon l'utilisateur relève de la sécurisation (gestion des droits d'accès).

Résultat : (a) accès concurrents ; (b) persistance (et sécurisation) ; (c) efficacité (indexation) ; (d) sécurisation (droits d'accès). Ces quatre situations illustrent les quatre services attendus d'un SGBD relationnel.

Objectif Bac

Objectif Bac : citer et expliquer les services rendus par un SGBD relationnel (persistance, gestion des accès concurrents, efficacité, sécurisation), idéalement en justifiant pourquoi de simples fichiers ne les offrent pas.
Objectif Bac : associer une situation concrète au service du SGBD qui la prend en charge (deux clients réservent le même siège → accès concurrents/transactions ; reprise après coupure de courant → persistance/sécurisation).
Objectif Bac : illustrer la gestion des accès concurrents par un scénario de double réservation, plutôt que par une définition abstraite.
Objectif Bac : rester au niveau du RÔLE et des ENJEUX — le programme exclut explicitement le détail du fonctionnement des services du SGBD.
Objectif Bac : associer chacun des quatre services (persistance, accès concurrents, efficacité, sécurisation) à une conséquence concrète pour l'utilisateur.

Erreurs fréquentes

Confondre le SGBD (le logiciel) avec la base de données (les données elles-mêmes) ou avec le langage SQL (le moyen d'interroger) : ce sont trois choses distinctes.
Réduire le SGBD au seul stockage : la valeur ajoutée tient surtout à la gestion des accès concurrents (transactions), à l'efficacité (index) et à la sécurisation, pas uniquement à la persistance.
Décrire les verrous, les journaux ou l'algorithme d'indexation en croyant approfondir : c'est hors des attendus, et le temps y est perdu.
Assimiler la sécurisation à la seule sauvegarde : elle recouvre aussi les droits d'accès par utilisateur, qui déterminent qui peut lire et qui peut écrire.

Révision active

Une billetterie en ligne vend des places de concert. Décrivez, pour chacun des quatre services d'un SGBD (persistance, accès concurrents, efficacité, sécurisation), une situation où ce service est indispensable au bon fonctionnement de la billetterie.

S’entraîner sur des exercices associés50 questions sur ce thème

Rappel actif

Rappelle-toi les points clés — puis révèle.

Sources : Programme de l'enseignement de spécialité de NSI — classe terminale, voie générale (annexe de l'arrêté du 19-7-2019, BO spécial n° 8 du 25 juillet 2019) (Ministère de l’Éducation nationale — Bulletin officiel)

Interroger une base en SQL : SELECT, jointure, agrégation#

~6 min de lectureApprofondissementeduscol-programme-nsi-terminale

Une jointure : combiner livre et auteur sur la clé

Fig. 4La jointure livre JOIN auteur ON livre.id_auteur = auteur.id rapproche chaque livre de la ligne d'auteur dont l'id égale son id_auteur. La table résultat réunit titre et nom : c'est cette table qui est affichée ici.

Le squelette d'une requête d'interrogation est : `SELECT <attributs> FROM <table> [JOIN <table2> ON <condition>] [WHERE <condition>] [GROUP BY <attribut>] [HAVING <condition>] [ORDER BY <attribut> [ASC|DESC]]`. SELECT * sélectionne toutes les colonnes.

Projection = choix des colonnes (la liste après SELECT). Sélection = choix des lignes vérifiant une condition (la clause WHERE). SELECT DISTINCT élimine les doublons du résultat. ORDER BY trie le résultat (ASC croissant par défaut, DESC décroissant).

Jointure (JOIN … ON …) : combine deux relations en rapprochant les lignes qui vérifient une condition d'égalité, typiquement clé étrangère = clé primaire (livre.id_auteur = auteur.id). On préfixe les attributs ambigus par le nom de la table.

Fonctions d'agrégation : COUNT (effectif), SUM (somme), AVG (moyenne), MIN, MAX. Elles condensent un ensemble de lignes en une seule valeur. GROUP BY calcule l'agrégat par groupe ; HAVING filtre ces groupes (alors que WHERE filtre les lignes avant regroupement).

Les conditions WHERE se construisent avec les comparateurs =, <>, <, >, <=, >=, les connecteurs AND, OR, NOT, ainsi que LIKE (motifs), IN (appartenance à une liste) et BETWEEN (intervalle). Attention : tester une valeur manquante s'écrit IS NULL, jamais = NULL.

Attention au périmètre exact du programme, qui protège des exercices trop ambitieux : les clauses attendues sont SELECT, FROM, WHERE et JOIN. Le programme ajoute qu'« on peut utiliser DISTINCT, ORDER BY ou les fonctions d'agrégation SANS utiliser les clauses GROUP BY et HAVING ». Autrement dit, `SELECT COUNT() FROM livre WHERE auteur_id = 3` est dans les attendus — une agrégation sur un sous-ensemble filtré —, tandis que `SELECT auteur_id, COUNT() FROM livre GROUP BY auteur_id` les dépasse. Le rapport du jury du Concours général 2025 le confirme en observant que la question d'agrégation par groupes fut « peu traitée et très rarement réussie », tout en reconnaissant que GROUP BY excède les attendus de la spécialité. Sachez lire une requête avec GROUP BY ; ne comptez pas dessus pour composer votre réponse.

Vocabulaire

→ Cartes

projectionChoix des colonnes conservées dans le résultat ; c'est la liste qui suit SELECT.
sélectionChoix des lignes conservées ; c'est la condition qui suit WHERE.
jointureRapprochement des lignes de deux relations vérifiant une condition, typiquement clé étrangère = clé primaire.
fonction d’agrégationFonction condensant plusieurs lignes en une valeur : COUNT, SUM, AVG, MIN, MAX.

Compter les livres par auteur (1/3)

\texttt{SELECT a.nom, COUNT(*) AS nb}

On projette le nom de l'auteur et le nombre de livres associés ; AS nb nomme la colonne calculée par l'agrégat COUNT(*).

Compter les livres par auteur (2/3)

\texttt{FROM auteur AS a JOIN livre AS l ON l.id\_auteur = a.id}

La jointure rapproche chaque livre de son auteur via l'égalité clé étrangère = clé primaire (l.id_auteur = a.id).

Compter les livres par auteur (3/3)

\texttt{GROUP BY a.id, a.nom ORDER BY nb DESC;}

On regroupe par auteur pour que COUNT(*) compte les livres de chacun, puis on trie du plus prolifique au moins prolifique.

COUNT(*) de livres par auteur (jeu d'exemple)

Fig. 5Sur le jeu d'exemple, la requête GROUP BY id_auteur avec COUNT(*) renvoie deux groupes : Voltaire avec 2 livres (mis en évidence), Zola avec 1 livre.

Exemple corrigé

Trois requêtes d'interrogation : sélection-tri, jointure, agrégation

Avec auteur(id, nom) et livre(isbn, titre, annee, id_auteur), écrivez : (1) les titres des livres parus après 1850, triés par année croissante ; (2) le titre de chaque livre avec le nom de son auteur ; (3) le nombre de livres par auteur, du plus prolifique au moins prolifique.

(1) Sélection + projection + tri (une table)
Projection sur titre (et annee pour le tri), sélection des lignes telles que annee > 1850, tri croissant par annee.
$\texttt{SELECT titre, annee FROM livre WHERE annee > 1850 ORDER BY annee ASC;}$
(2) Jointure de deux tables
On relie chaque livre à son auteur par l'égalité clé étrangère = clé primaire, puis on projette le titre et le nom.
$\texttt{SELECT l.titre, a.nom FROM livre AS l JOIN auteur AS a ON l.id\_auteur = a.id;}$
(3) Jointure + agrégation + regroupement + tri
On joint livre et auteur, on regroupe par auteur, on compte les livres de chaque groupe avec COUNT(*), puis on trie ce comptage par ordre décroissant.
$\texttt{SELECT a.nom, COUNT(*) AS nb FROM auteur AS a JOIN livre AS l ON l.id\_auteur = a.id GROUP BY a.id, a.nom ORDER BY nb DESC;}$
Vérifier le résultat de (3) sur le jeu d'exemple
Avec Candide (id_auteur 7) et Zadig (id_auteur 7) pour Voltaire, et Germinal (id_auteur 12) pour Zola : Voltaire totalise 2 livres, Zola 1. La requête renvoie donc (Voltaire, 2) puis (Zola, 1) grâce au tri décroissant.

Résultat : (1) renvoie les titres d'après-1850 triés par année ; (2) apparie chaque titre à son auteur par la jointure ; (3) renvoie (Voltaire, 2) puis (Zola, 1). La clé du sujet est de joindre sur l.id_auteur = a.id, puis de regrouper par auteur avant d'agréger.

Explication pas à pas4 étapes

1
Une requête d'interrogation suit toujours le même squelette : SELECT pour les colonnes, FROM pour les tables, WHERE pour filtrer les lignes, ORDER BY pour trier. Lisons-la dans cet ordre.
2
Deux mots clés à ne jamais confondre : la projection choisit les colonnes, juste après SELECT ; la sélection choisit les lignes, dans la clause WHERE. L'une agit verticalement, l'autre horizontalement.
3
Pour relier deux tables, on joint sur l'égalité clé étrangère égale clé primaire. Ici l'identifiant d'auteur du livre rejoint l'identifiant de la table auteur : chaque livre retrouve son auteur.
Jointure sur l'égalité clé étrangère = clé primaire
Fig.Joindre, c'est apparier les lignes sur l'égalité clé étrangère = clé primaire : chaque livre retrouve son auteur via livre.id_auteur = auteur.id.
4
Enfin, pour compter ou totaliser, on regroupe avec GROUP BY puis on agrège avec COUNT, SUM ou AVG. Compter les livres par auteur donne, sur notre exemple, Voltaire deux, Zola un.
COUNT(*) de livres par auteur

Erreurs fréquentes

Écrire une jointure sans condition (`FROM livre, auteur` sans clause de rapprochement) : le résultat est le produit de toutes les lignes de l'une par toutes celles de l'autre, et le nombre de lignes explose.
Confondre WHERE et HAVING (WHERE filtre les lignes avant regroupement, HAVING filtre les groupes après agrégation), ou écrire `= NULL` au lieu de `IS NULL`. Oublier la condition de jointure produit par ailleurs un produit cartésien (toutes les combinaisons de lignes).
Placer une condition d'agrégat dans le WHERE (`WHERE COUNT(*) > 3`) : le WHERE filtre des lignes, pas des résultats d'agrégation.
Croire que SQL distingue les majuscules dans les mots-clés : `select` et `SELECT` sont équivalents, mais les valeurs des chaînes, elles, sont sensibles à la casse.

Révision active

Avec auteur(id, nom) et livre(isbn, titre, annee, id_auteur), écrivez les requêtes SQL pour : (1) les titres des livres parus après 1850, triés par année croissante ; (2) le titre de chaque livre accompagné du nom de son auteur ; (3) le nombre de livres écrits par chaque auteur, du plus prolifique au moins prolifique.

S’entraîner sur des exercices associés50 questions sur ce thème

Rappel actif

Rappelle-toi les points clés — puis révèle.

Sources : Programme de l'enseignement de spécialité de NSI — classe terminale, voie générale (annexe de l'arrêté du 19-7-2019, BO spécial n° 8 du 25 juillet 2019) (Ministère de l’Éducation nationale — Bulletin officiel)

Mettre à jour une base en SQL : INSERT, UPDATE, DELETE#

~6 min de lectureStandardeduscol-programme-nsi-terminale

Les trois opérations de mise à jour sur une table

Fig. 6INSERT ajoute une ligne ; UPDATE modifie les valeurs des lignes ciblées par le WHERE ; DELETE retire les lignes ciblées par le WHERE. Attention : sans clause WHERE, UPDATE et DELETE agissent sur TOUTE la table.

INSERT ajoute un ou plusieurs n-uplets : `INSERT INTO table (col1, col2, …) VALUES (v1, v2, …);`. Préciser la liste des colonnes est plus sûr ; les attributs omis prennent leur valeur par défaut (ou NULL). Les valeurs doivent respecter les domaines et les contraintes.

UPDATE modifie des n-uplets existants : `UPDATE table SET col = nouvelleValeur [, col2 = …] WHERE condition;`. On peut modifier plusieurs colonnes d'un coup. La clause WHERE désigne les lignes concernées.

DELETE supprime des n-uplets : `DELETE FROM table WHERE condition;`. Seules les lignes vérifiant la condition sont supprimées.

Avertissement capital : un UPDATE ou un DELETE sans clause WHERE s'applique à toutes les lignes de la table (modification ou effacement total). Le WHERE n'est pas optionnel en pratique.

Les mises à jour restent soumises aux contraintes d'intégrité : un INSERT ou un UPDATE qui rendrait une clé primaire non unique, ou une clé étrangère orpheline, est refusé ; un DELETE qui laisserait des références pendantes l'est aussi. Le SGBD vérifie après chaque opération.

Une méthode de sécurité s'apprend en même temps que la syntaxe et vaut pour l'épreuve comme pour la vie professionnelle : écrire d'abord la requête sous forme de SELECT portant la même clause WHERE, vérifier que le nombre de lignes renvoyées est bien celui qu'on veut modifier ou supprimer, puis seulement remplacer `SELECT *` par `DELETE` ou par `UPDATE … SET …`. Cette précaution est la parade au sinistre classique du DELETE sans WHERE, qui vide la table entière sans le moindre avertissement.

L'ordre d'exécution imposé par les contraintes se déduit du sens des références et tombe régulièrement à l'examen. Pour INSÉRER, on commence par la table RÉFÉRENCÉE : l'auteur avant le livre, faute de quoi la clé étrangère du livre pointerait dans le vide. Pour SUPPRIMER, l'ordre s'inverse : le livre avant l'auteur, sinon la suppression de l'auteur laisserait des livres orphelins et la contrainte d'intégrité référentielle la refuserait.

Vocabulaire

→ Cartes

INSERTRequête d'ajout d'un ou plusieurs n-uplets dans une relation.
UPDATERequête de modification des n-uplets vérifiant une condition ; sans WHERE, elle modifie toute la table.
DELETERequête de suppression des n-uplets vérifiant une condition ; sans WHERE, elle vide la table.
n-uplet orphelinLigne dont la clé étrangère référencerait un n-uplet supprimé ; l'intégrité référentielle l'interdit.

Insertion d'un n-uplet

\texttt{INSERT INTO auteur (id, nom) VALUES (20, 'Hugo');}

On ajoute un auteur en précisant explicitement les colonnes ; la valeur de la clé primaire id doit être unique et non NULL.

Modification ciblée par la clé primaire

\texttt{UPDATE livre SET annee = 1869 WHERE isbn = '978-9';}

La clause WHERE isbn = '978-9' garantit qu'un seul livre est modifié ; sans elle, l'année de tous les livres serait écrasée.

Suppression conditionnelle

\texttt{DELETE FROM livre WHERE annee < 1800;}

Seuls les livres dont l'année est strictement antérieure à 1800 sont supprimés ; toutes les autres lignes sont conservées.

Exemple corrigé

Une séquence complète de mise à jour, contraintes comprises

Avec auteur(id, nom) et livre(isbn, titre, annee, id_auteur) : (1) ajoutez l'auteur (20, 'Hugo') ; (2) ajoutez ('978-9', 'Les Misérables', 1862, 20) ; (3) corrigez son année en 1869 ; (4) supprimez tous les livres parus avant 1800. Justifiez l'ordre (1) puis (2).

(1) Insérer l'auteur
On crée d'abord l'auteur, car le livre y fera référence par sa clé étrangère.
$\texttt{INSERT INTO auteur (id, nom) VALUES (20, 'Hugo');}$
(2) Insérer le livre référençant cet auteur
Maintenant que l'auteur 20 existe, la clé étrangère id_auteur = 20 satisfait l'intégrité référentielle.
$\texttt{INSERT INTO livre (isbn, titre, annee, id\_auteur) VALUES ('978-9', 'Les Misérables', 1862, 20);}$
(3) Corriger l'année du livre
On cible le seul livre concerné par sa clé primaire isbn, et on remplace l'année.
$\texttt{UPDATE livre SET annee = 1869 WHERE isbn = '978-9';}$
(4) Supprimer les livres antérieurs à 1800
La condition WHERE annee < 1800 limite la suppression aux seules lignes voulues ; le livre de 1869 n'est pas touché.
$\texttt{DELETE FROM livre WHERE annee < 1800;}$
Justifier l'ordre (1) puis (2)
Si l'on insérait le livre avant l'auteur, sa clé étrangère id_auteur = 20 pointerait vers un auteur inexistant : l'intégrité référentielle serait violée et le SGBD refuserait l'insertion. On crée donc toujours la ligne référencée avant la ligne qui la référence.

Résultat : Les quatre requêtes s'écrivent INSERT / INSERT / UPDATE / DELETE avec, à chaque mise à jour ciblée, une clause WHERE sur la clé primaire ou sur la condition voulue. L'ordre (1) avant (2) est imposé par l'intégrité référentielle : l'auteur doit exister avant le livre qui le référence.

Erreurs fréquentes

Omettre la clause WHERE dans UPDATE ou DELETE : l'opération s'applique alors à toute la table — erreur classique aux conséquences irréversibles.
Oublier que les contraintes d'intégrité s'appliquent aussi aux mises à jour : insérer une clé étrangère inexistante, dupliquer une clé primaire ou supprimer une ligne encore référencée sera refusé par le SGBD.
Écrire `UPDATE livre SET titre = 'X' WHERE titre = titre` : la condition est toujours vraie et la mise à jour touche toutes les lignes.
Croire qu'un DELETE refusé par une contrainte a partiellement supprimé des lignes : la contrainte fait échouer l'opération dans son ensemble, la base reste dans son état antérieur.

Révision active

Avec auteur(id, nom) et livre(isbn, titre, annee, id_auteur) : (1) ajoutez l'auteur (id 20, nom 'Hugo') ; (2) ajoutez le livre ('978-9', 'Les Misérables', 1862, 20) ; (3) corrigez l'année du livre d'isbn '978-9' en 1869 ; (4) supprimez tous les livres parus avant 1800. Indiquez aussi pourquoi l'ordre des étapes (1) puis (2) est important.

S’entraîner sur des exercices associés50 questions sur ce thème

Rappel actif

Rappelle-toi les points clés — puis révèle.

Sources : Programme de l'enseignement de spécialité de NSI — classe terminale, voie générale (annexe de l'arrêté du 19-7-2019, BO spécial n° 8 du 25 juillet 2019) (Ministère de l’Éducation nationale — Bulletin officiel)

Points clés

Décrire une relation à partir de son extension

Points clés

Diagnostiquer des violations de contraintes d'intégrité

Points clés

Associer une situation au service du SGBD concerné

Points clés

Trois requêtes d'interrogation : sélection-tri, jointure, agrégation

Points clés

Une séquence complète de mise à jour, contraintes comprises

Sources

Bases de données relationnelles et SQL

Points clés

Décrire une relation à partir de son extension

Points clés

Diagnostiquer des violations de contraintes d'intégrité

Points clés

Associer une situation au service du SGBD concerné

Points clés

Trois requêtes d'interrogation : sélection-tri, jointure, agrégation

Points clés

Une séquence complète de mise à jour, contraintes comprises

Sources