Exercices corrigés de NSI : bases de données relationnelles et SQL

La relation s'appelle eleve. Ses attributs sont id, nom et classe. Son degré, c'est-à-dire son nombre d'attributs, vaut 3. Sa cardinalité, c'est-à-dire son nombre de lignes, vaut 4. Une ligne affirme un fait : « il existe un élève, d'identifiant 1, nommé Dupont, inscrit en 1G3 ». Cette lecture en termes de fait n'est pas une image : c'est elle qui justifie que l'ordre des lignes soit sans importance et qu'un doublon soit dépourvu de sens.

Un domaine est l'ensemble des valeurs autorisées pour un attribut, autrement dit son type accompagné de ses contraintes. Ici : id est un entier strictement positif ; nom est une chaîne de caractères non vide, d'au plus une centaine de caractères ; classe est une chaîne appartenant à une liste fermée de codes de classes de l'établissement. Le système refusera alors d'écrire un identifiant négatif, un nom vide, ou une classe inexistante comme « 1G9 ». Sans domaine déclaré, ces trois erreurs entrent dans la base et n'en ressortent jamais.

La clé primaire est un attribut, ou un groupe d'attributs, dont la valeur identifie de façon unique et non nulle chaque ligne de la relation. L'attribut nom serait un mauvais choix pour trois raisons. Un, l'unicité n'est pas garantie dans le temps : deux homonymes finiront par arriver, et la base deviendrait alors incapable d'enregistrer le second. Deux, la valeur peut CHANGER, par mariage ou correction d'orthographe, et il faudrait alors mettre à jour toutes les tables qui la référencent. Trois, elle est longue, donc coûteuse à indexer et à recopier. Une clé primaire doit être unique, stable et courte, ce qu'un identifiant artificiel garantit par construction.

Non, pas selon la définition mathématique : une relation est un ENSEMBLE de n-uplets, et un ensemble ne contient pas deux fois le même élément. Deux lignes identiques n'apporteraient d'ailleurs aucune information, puisqu'elles affirmeraient exactement le même fait. Dans un système réel, l'absence de doublon n'est garantie que si une clé primaire ou une contrainte d'unicité est déclarée : sans elle, la plupart des moteurs acceptent d'insérer deux lignes identiques, ce qui fausse ensuite tout comptage. Déclarer la clé primaire n'est donc pas une formalité, c'est ce qui rend la table conforme au modèle.

Une clé candidate est un ensemble d'attributs qui pourrait servir de clé primaire, c'est-à-dire qui identifie de façon unique chaque ligne et dont aucune partie stricte n'a cette propriété. Une relation peut en avoir plusieurs, et l'on en choisit une comme clé primaire. Oui, une clé primaire peut porter sur plusieurs attributs : dans une table de notes, le couple formé de eleve_id et devoir_id est une clé primaire naturelle si l'on décide qu'un élève ne peut avoir qu'une seule note par devoir. On parle alors de clé COMPOSÉE, et elle exprime une règle de gestion, pas seulement une contrainte technique.

Une clé étrangère est un attribut, ou un groupe d'attributs, d'une relation, dont la valeur doit obligatoirement correspondre à une valeur de clé primaire existante dans une autre relation, ou bien être nulle si la conception l'autorise. C'est la table note qui porte la contrainte, par son attribut eleve_id ; elle s'appuie sur la table eleve, par sa clé primaire id. Le sens de la flèche compte : la contrainte pèse sur celui qui référence, jamais sur celui qui est référencé.

L'insertion est REFUSÉE. Aucun élève d'identifiant 9 n'existe dans la table eleve, donc la ligne violerait l'intégrité référentielle : elle affirmerait la note d'un élève inexistant. Le système lève une erreur explicite et n'écrit rien. C'est exactement le service qu'on attend d'une base de données : une donnée incohérente est rejetée AU MOMENT de l'écriture, et non découverte six mois plus tard par un calcul de moyenne qui tombe faux.

Trois comportements possibles, tous prévus par la norme. Le REFUS : la suppression est rejetée tant que des notes référencent l'élève, ce qui oblige à traiter les notes d'abord. La SUPPRESSION EN CASCADE : les notes de l'élève sont supprimées automatiquement en même temps que lui. La MISE À NUL : eleve_id passe à la valeur nulle dans les notes concernées, qui deviennent orphelines mais subsistent. Dans un carnet de notes, il faut choisir le REFUS : une suppression en cascade ferait disparaître silencieusement des évaluations, et une mise à nul produirait des notes qui n'appartiennent à personne, donc inexploitables. Le refus force à décider explicitement, ce qui est exactement ce qu'on veut sur des données scolaires.

Oui, c'est ce qu'on appelle une clé étrangère réflexive. Exemple : dans une table eleve, un attribut tuteur_id qui référence l'identifiant d'un autre élève de la même table, dans un système de tutorat entre pairs. On obtient alors un GRAPHE ORIENTÉ dont les sommets sont les lignes de la table, ou un arbre si l'on ajoute la contrainte qu'aucun cycle n'est permis et qu'un seul élément n'a pas de tuteur. C'est ainsi qu'on représente une hiérarchie, une arborescence de catégories ou un fil de discussion en réponses imbriquées.

Trois arguments. La REDONDANCE : le nom serait stocké autant de fois qu'il y a de notes, ce qui gaspille de l'espace et surtout multiplie les occasions d'incohérence. L'ANOMALIE DE MISE À JOUR : corriger une faute d'orthographe dans un nom obligerait à modifier toutes les lignes de notes, et en oublier une seule crée deux élèves là où il n'y en a qu'un. La PERTE DE LA CONTRAINTE : un nom recopié n'est plus vérifié par personne, alors qu'une clé étrangère est contrôlée à chaque écriture. On peut ajouter un quatrième argument, purement pratique : la jointure que l'on cherchait à éviter coûte, sur une clé indexée, à peine plus qu'une lecture directe, alors que la dénormalisation coûte pour toujours.

La table de gauche répète le couple Dupont et 1G3 sur trois lignes, une par note. L'information « Dupont est en 1G3 » y est donc écrite TROIS fois, alors qu'il s'agit d'un seul fait. Dans la conception de droite, elle n'est écrite qu'une fois, dans la table des élèves, et les trois notes ne portent que l'identifiant.

Dans la conception de droite, on modifie UNE ligne de la table des élèves, et c'est fini : les trois notes suivent automatiquement, puisqu'elles ne stockent que l'identifiant. Dans celle de gauche, il faut modifier les trois lignes concernées. Le risque propre à la première conception est l'ANOMALIE DE MISE À JOUR : si l'une des trois lignes est oubliée, la base affirme simultanément que Dupont est en 1G3 et en 1G4. Elle contient alors une contradiction, et aucune requête ne pourra plus trancher, puisque les deux réponses y sont écrites noir sur blanc.

Dans la conception de droite, on insère simplement une ligne dans la table des élèves, sans aucune note : c'est parfaitement légitime. Dans celle de gauche, une ligne exige une valeur de note ; il faudrait inventer une note fictive, ou laisser une valeur nulle qui faussera tous les calculs de moyenne. On appelle cela une ANOMALIE D'INSERTION : la conception interdit d'enregistrer un fait, l'inscription de l'élève, parce qu'un autre fait sans rapport, sa première note, n'existe pas encore.

Dans la conception de droite, on perd la note et rien d'autre : Nadeau reste inscrit en 1G1. Dans celle de gauche, la suppression de sa seule ligne fait disparaître du même coup l'existence de Nadeau et son appartenance à la 1G1 : on perd un fait qu'on ne voulait pas supprimer. C'est l'ANOMALIE DE SUPPRESSION, la troisième du trio classique.

La règle : une table doit décrire UNE SEULE sorte de chose, et chacun de ses attributs doit dépendre de la clé primaire entière et de rien d'autre. Ici, la classe dépend de l'élève et non de la note : elle n'a donc rien à faire dans une table de notes. Formulée autrement, chaque fait doit être enregistré à un seul endroit ; dès qu'une information est recopiée, elle peut se contredire, et une base qui se contredit n'est plus une source de vérité. C'est cette exigence qu'on appelle la normalisation, et les trois anomalies des questions b, c et d en sont la justification complète.

La requête : SELECT nom FROM eleve WHERE classe = '1G3'. Le résultat contient deux lignes, Dupont et Ferrand. Noter que le résultat d'une requête est lui-même une relation, ici de degré 1 et de cardinalité 2 : c'est ce qui permet de composer les requêtes entre elles.

La requête : SELECT valeur FROM note WHERE valeur > 12 ORDER BY valeur DESC. Les notes strictement supérieures à 12 sont 15, 14 et 17. Triées par ordre décroissant : 17, 15, 14. Sans la clause ORDER BY, l'ordre des lignes n'est pas garanti : il dépend du plan d'exécution choisi par le moteur et peut changer d'une exécution à l'autre, ce qui est une source classique de tests instables.

La requête : SELECT DISTINCT eleve_id FROM note. Le résultat est 1, 2, 3, 4, soit quatre lignes, alors que la table en compte sept. Le mot-clé DISTINCT élimine les doublons du résultat : sans lui, l'identifiant 1 apparaîtrait deux fois et les identifiants 3 et 4 également. Il agit APRÈS la projection, ce qui est important : projeter d'abord peut créer des doublons qui n'existaient pas dans la table de départ, exactement comme au chapitre des données en tables.

Sur ces données, WHERE valeur > 12 ramène 15, 14 et 17, soit trois lignes ; WHERE valeur >= 12 ramène en plus la note 12, soit quatre lignes. La différence porte donc exactement sur les lignes dont la valeur vaut la borne. Pour la classe : WHERE classe = '1G3' ramène les seuls élèves de 1G3, soit Dupont et Ferrand ; WHERE classe LIKE '1G%' ramène tous ceux dont le code commence par 1G, donc aussi Nadeau en 1G1, soit trois élèves. Le signe pour cent remplace n'importe quelle suite de caractères, y compris vide.

Parce qu'en SQL, la valeur nulle ne signifie pas « une valeur particulière » mais « valeur inconnue ». Toute comparaison avec une valeur inconnue donne un résultat inconnu, ni vrai ni faux, et la clause WHERE ne retient que ce qui est VRAI : les lignes à valeur nulle sont donc écartées, y compris par une condition de différence. C'est la logique à trois valeurs de SQL, et elle surprend toujours. La bonne écriture est WHERE valeur <> 12 OR valeur IS NULL, ou plus simplement WHERE valeur IS NULL quand on cherche précisément ces lignes. On retient qu'on ne compare jamais une valeur nulle avec le signe égale : on utilise IS NULL et IS NOT NULL.

La requête : SELECT eleve.nom, note.valeur FROM note JOIN eleve ON note.eleve_id = eleve.id. Le résultat, sept lignes : Dupont 12, Dupont 15, Nadeau 9, Ferrand 14, Ferrand 17, Ouellet 8, Ouellet 11. Chaque ligne de note trouve exactement un élève, puisque eleve_id est une clé étrangère non nulle vers une clé primaire : le nombre de lignes du résultat est donc égal au nombre de lignes de note.

La requête : SELECT DISTINCT eleve.nom FROM note JOIN eleve ON note.eleve_id = eleve.id WHERE note.valeur > 13. Les notes strictement supérieures à 13 sont 15, appartenant à Dupont, 14 et 17, appartenant toutes deux à Ferrand. Sans DISTINCT, Ferrand apparaîtrait DEUX fois, une par note retenue. Le résultat correct est donc Dupont et Ferrand, deux lignes. Le réflexe à prendre : dès que la question porte sur les élèves et que le filtre porte sur les notes, il faut se demander si un élève peut satisfaire le filtre plusieurs fois.

Sans condition de jointure, le moteur associe CHAQUE ligne de note à CHAQUE ligne de eleve, sans aucun rapport entre elles. On obtient $7 \times 4 = 28$ lignes, dont 21 sont fausses : elles attribuent la note d'un élève à un autre. Ce résultat s'appelle le PRODUIT CARTÉSIEN. Il est particulièrement dangereux parce qu'il ne provoque aucune erreur : la requête s'exécute, renvoie des lignes plausibles, et les totaux calculés ensuite sont multipliés par quatre. Le symptôme à connaître est un nombre de lignes anormalement grand, souvent un multiple exact du nombre attendu.

La requête : SELECT eleve.nom, note.valeur FROM eleve LEFT JOIN note ON note.eleve_id = eleve.id. Le type de jointure est la jointure EXTERNE gauche : elle conserve toutes les lignes de la table de gauche, ici eleve, même celles qui n'ont aucune correspondance à droite. Pour un élève sans note, la colonne valeur contient la valeur NULLE, et il faut alors se souvenir de la question e de l'exercice 4 : un calcul de moyenne ignorera cette ligne, mais un comptage de lignes, lui, la comptera. C'est exactement la différence entre COUNT(*) et COUNT(valeur).

La requête : SELECT eleve.nom, devoir.matiere, note.valeur FROM note JOIN eleve ON note.eleve_id = eleve.id JOIN devoir ON note.devoir_id = devoir.id WHERE devoir.matiere = 'maths'. Le résultat contient trois lignes, les notes du devoir 10 : Dupont maths 12, Nadeau maths 9, Ferrand maths 14. Le principe se généralise : on part de la table centrale, celle qui porte les clés étrangères, et on ajoute une jointure par table à rejoindre, chacune avec sa propre condition. La table note est ici la table d'ASSOCIATION entre élèves et devoirs.

La classe est une colonne de eleve, la valeur une colonne de note : il faut une jointure. La requête : SELECT COUNT(*), AVG(note.valeur), MAX(note.valeur) FROM note JOIN eleve ON note.eleve_id = eleve.id WHERE eleve.classe = '1G3'.

La jointure associe à chaque note son élève ; le WHERE garde les élèves de 1G3, Dupont et Ferrand, et leurs notes 12, 15, 14 et 17.

Les agrégats portent sur ces quatre lignes : COUNT(*) vaut $4$, AVG vaut $\frac{12+15+14+17}{4}=\frac{58}{4}=14{,}5$ et MAX vaut $17$.

Contrôle : la moyenne $14{,}5$ est comprise entre la plus basse, $12$, et la plus haute, $17$.

Piège : écrire SELECT AVG(valeur) FROM note WHERE classe = '1G3'. La table note n'a pas de colonne classe : la requête est refusée, et seule la jointure donne accès à la classe.

La matière est dans devoir, le nom dans eleve : on part de note, la table qui porte les deux clés étrangères, et l'on fait deux jointures. La requête : SELECT eleve.nom, devoir.matiere, note.valeur FROM note JOIN eleve ON note.eleve_id = eleve.id JOIN devoir ON note.devoir_id = devoir.id WHERE devoir.matiere = 'physique' OR devoir.matiere = 'NSI' ORDER BY note.valeur DESC.

Le résultat, quatre lignes : Ferrand, NSI, 17 ; Dupont, physique, 15 ; Ouellet, NSI, 11 ; Ouellet, physique, 8.

Contrôle : la table compte $7$ notes, dont $3$ de mathématiques (question e) ; il en reste bien $7-3=4$.

Avec AND, la condition demanderait qu'une même note soit à la fois de physique et de NSI : aucune ligne ne la vérifie, et le résultat est vide, sans message d'erreur.

Piège : traduire « physique ou NSI » par AND parce que la phrase dit « les notes de physique et de NSI ». Le WHERE teste UNE ligne à la fois : chaque note n'a qu'une matière.

Deux conditions qui doivent être vraies ensemble, cette fois : AND. La requête : SELECT COUNT(*), AVG(note.valeur) FROM note JOIN eleve ON note.eleve_id = eleve.id JOIN devoir ON note.devoir_id = devoir.id WHERE eleve.classe = '1G3' AND devoir.matiere = 'maths'.

Les notes de mathématiques sont celles du devoir 10 : Dupont 12, Nadeau 9 et Ferrand 14. Nadeau est en 1G1 : il reste Dupont et Ferrand.

COUNT(*) vaut $2$ et AVG vaut $\frac{12+14}{2}=13$.

Contrôle : la question f) donnait $4$ notes pour la classe 1G3 ; les deux autres, 15 et 17, sont de physique et de NSI.

L'ordre est imposé par la clé étrangère : note.devoir_id doit désigner un devoir qui EXISTE déjà dans la table devoir.

On insère donc d'abord le devoir : INSERT INTO devoir (id, matiere, coefficient) VALUES (13, 'physique', 3).

Puis la note : INSERT INTO note (id, eleve_id, devoir_id, valeur) VALUES (107, 2, 13, 16). Nadeau a l'identifiant 2, qui existe dans eleve : la seconde clé étrangère est satisfaite aussi.

Dans l'ordre inverse, la première commande serait refusée au nom de l'intégrité référentielle, comme à l'exercice 2 : aucun devoir 13 n'existerait encore.

La requête de g) renvoie ensuite cinq lignes : Ferrand, NSI, 17 ; Nadeau, physique, 16 ; Dupont, physique, 15 ; Ouellet, NSI, 11 ; Ouellet, physique, 8.

Piège : chercher Nadeau par son nom dans la commande INSERT. La table note ne connaît que eleve_id : c'est l'identifiant 2 qu'on écrit.

La requête : SELECT COUNT(*), MIN(valeur), MAX(valeur), AVG(valeur) FROM note. Les sept notes sont 12, 15, 9, 14, 17, 8 et 11. Le compte vaut 7, le minimum 8, le maximum 17, et la moyenne $86 / 7 \approx 12{,}29$. Attention à COUNT : COUNT(*) compte les LIGNES, COUNT(valeur) compte les valeurs non nulles, et les deux diffèrent dès qu'une valeur nulle existe.

La requête : SELECT eleve.nom, AVG(note.valeur) FROM note JOIN eleve ON note.eleve_id = eleve.id GROUP BY eleve.id, eleve.nom. Les moyennes : Dupont, notes 12 et 15, moyenne $27 / 2 = 13{,}50$. Nadeau, note 9 seule, moyenne $9{,}00$. Ferrand, notes 14 et 17, moyenne $31 / 2 = 15{,}50$. Ouellet, notes 8 et 11, moyenne $19 / 2 = 9{,}50$. Il faut regrouper sur l'identifiant et non sur le seul nom : deux homonymes seraient sinon fondus dans un même groupe, ce qui est précisément le défaut que la clé primaire servait à éviter.

La requête : la même, complétée par HAVING AVG(note.valeur) >= 12. Le résultat contient Dupont, moyenne 13,50, et Ferrand, moyenne 15,50. La clause WHERE ne convient pas parce qu'elle s'applique AVANT le regroupement, donc à des lignes individuelles, alors que la moyenne n'existe qu'après regroupement. Écrire WHERE AVG(...) provoque une erreur de syntaxe dans tout moteur correct. L'ordre logique d'exécution est immuable : FROM, puis JOIN, puis WHERE, puis GROUP BY, puis HAVING, puis SELECT, puis ORDER BY. WHERE filtre des lignes, HAVING filtre des groupes.

La requête : SELECT devoir.matiere, COUNT(*), AVG(note.valeur) FROM note JOIN devoir ON note.devoir_id = devoir.id GROUP BY devoir.matiere. Mathématiques, devoir 10 : notes 12, 9 et 14, soit 3 notes et une moyenne de $35 / 3 \approx 11{,}67$. Physique, devoir 11 : notes 15 et 8, soit 2 notes et une moyenne de $23 / 2 = 11{,}50$. NSI, devoir 12 : notes 17 et 11, soit 2 notes et une moyenne de $28 / 2 = 14{,}00$. On vérifie le total des effectifs : $3 + 2 + 2 = 7$, ce qui correspond bien au nombre de notes.

La requête : SELECT SUM(note.valeur * devoir.coefficient) / SUM(devoir.coefficient) FROM note JOIN devoir ON note.devoir_id = devoir.id. Le numérateur vaut $12 \times 4 + 15 \times 3 + 9 \times 4 + 14 \times 4 + 17 \times 4 + 8 \times 3 + 11 \times 4 = 48 + 45 + 36 + 56 + 68 + 24 + 44 = 321$. Le dénominateur vaut $4 + 3 + 4 + 4 + 4 + 3 + 4 = 26$. La moyenne pondérée vaut donc $321 / 26 \approx 12{,}35$, contre $12{,}29$ pour la moyenne simple. L'écart est faible mais réel, et il vient de ce que les matières à coefficient 4 portent ici des notes légèrement meilleures. Le point de méthode est essentiel : on ne calcule JAMAIS une moyenne pondérée en moyennant des moyennes, on somme les produits et on divise une seule fois, exactement comme au chapitre des données en tables.

La commande : INSERT INTO eleve (id, nom, classe) VALUES (5, 'Tremblay', 'TG2'). Si l'identifiant 5 existe déjà, l'insertion est REFUSÉE et une erreur de violation de contrainte d'unicité est levée : la clé primaire garantit qu'aucune ligne existante ne sera écrasée. C'est un comportement voulu, et c'est ce qui distingue une insertion d'une mise à jour. Certains moteurs proposent une variante qui met à jour la ligne si elle existe, mais elle doit être demandée explicitement, jamais subie.

Première commande : UPDATE note SET valeur = 13 WHERE id = 102. Seconde commande : UPDATE note SET valeur = valeur + 1 WHERE devoir_id = 11. Les notes du devoir 11 sont 15, pour Dupont, et 8, pour Ouellet : elles deviennent 16 et 9. Noter l'écriture valeur = valeur + 1, qui lit et écrit le même attribut dans la même commande : c'est parfaitement légal, le moteur évaluant le membre droit sur la valeur d'avant la mise à jour.

Sans clause WHERE, la mise à jour porte sur TOUTES les lignes de la table. Les sept notes 12, 15, 9, 14, 17, 8 et 11 deviennent 13, 16, 10, 15, 18, 9 et 12. Aucun message d'erreur n'apparaît parce que la commande est parfaitement valide : l'absence de WHERE signifie « toutes les lignes », ce qui est un usage légitime dans d'autres contextes. Le moteur exécute donc exactement ce qui lui a été demandé, et il annonce même le succès en indiquant sept lignes modifiées. Ce nombre est d'ailleurs le seul signal disponible : voir sept là où l'on attendait deux est ce qui doit alerter.

La commande : DELETE FROM note WHERE valeur < 10. Les notes strictement inférieures à 10 sont 9 et 8, donc DEUX lignes sont supprimées. La vérification à faire avant est de remplacer DELETE par SELECT, en gardant exactement la même clause WHERE : SELECT * FROM note WHERE valeur < 10 affiche les lignes qui seront détruites, sans rien détruire. Cette habitude, qui coûte cinq secondes, est la plus rentable de tout le chapitre : elle transforme une opération irréversible en une opération vérifiée.

Quatre précautions. Un, SAUVEGARDER la base avant toute opération d'écriture en masse. Deux, ÉCRIRE LA REQUÊTE EN SELECT D'ABORD, avec le même filtre, pour voir ce qui sera touché. Trois, travailler dans une TRANSACTION, afin de pouvoir tout annuler si le nombre de lignes affectées ne correspond pas à l'attendu. Quatre, limiter les DROITS du compte utilisé : un compte de lecture seule pour les requêtes d'analyse rend impossible toute écriture accidentelle. C'est la deuxième qui protège directement de l'erreur de la question c, puisqu'elle aurait affiché sept lignes au lieu de deux avant toute modification ; la troisième aurait permis de réparer après coup.

FAUX. Une clé primaire doit être unique, non nulle et STABLE. L'absence actuelle de doublon ne garantit rien pour l'avenir : deux homonymes finiront par arriver et la base refusera alors d'enregistrer le second. De plus un nom change, et toutes les clés étrangères qui le référenceraient devraient être mises à jour. Énoncé correct : une clé primaire doit être un identifiant artificiel, stable et sans signification métier, sauf clé naturelle réellement immuable.

FAUX, elles ne s'appliquent pas au même objet. WHERE filtre des LIGNES, avant tout regroupement, et ne peut donc pas porter sur un agrégat. HAVING filtre des GROUPES, après regroupement, et porte précisément sur des agrégats. Énoncé correct : WHERE s'exécute avant GROUP BY et HAVING après ; écrire WHERE AVG(...) est une erreur de syntaxe, et écrire HAVING sur une colonne non agrégée est au mieux inutile.

FAUX, et c'est bien le problème. Le moteur produit le PRODUIT CARTÉSIEN, c'est-à-dire toutes les associations possibles : $7 \times 4 = 28$ lignes ici, dont 21 sont fausses. Aucune erreur n'est signalée. Énoncé correct : une jointure sans condition est légale et renvoie le produit cartésien ; le symptôme est un nombre de lignes anormalement grand, souvent un multiple exact du nombre attendu.

FAUX pour les lignes dont la valeur est NULLE. En SQL, la valeur nulle signifie « inconnue », et toute comparaison avec elle donne un résultat inconnu, que la clause WHERE écarte. Énoncé correct : WHERE valeur <> 12 ramène les lignes dont la valeur est connue et différente de 12 ; pour inclure les valeurs nulles il faut écrire WHERE valeur <> 12 OR valeur IS NULL.

FAUX sur les deux points. Sur le fond, cette dénormalisation crée les trois anomalies de l'exercice 3 : mise à jour, insertion et suppression, et elle supprime la contrainte d'intégrité qui vérifiait le lien. Sur la performance, une jointure sur une clé primaire indexée coûte à peine plus qu'une lecture directe, alors que le nom recopié alourdit chaque ligne et donc chaque lecture. Énoncé correct : on ne dénormalise que sur mesure démontrée, jamais par principe, et jamais sur des données susceptibles de changer.

T1 lit 100, T2 lit 100, T1 écrit $100 - 30 = 70$, T2 écrit $100 - 50 = 50$, puis les deux valident. Le solde final vaut 50, puisque l'écriture de T2 recouvre celle de T1. Il devrait valoir $100 - 30 - 50 = 20$. La mise à jour perdue est celle de T1 : T2 avait lu le solde AVANT que T1 ne l'écrive, donc son calcul reposait sur une valeur devenue périmée, et son écriture a effacé le travail de T1 sans même s'en apercevoir. Ce défaut porte un nom, la mise à jour perdue, et il ne se produit que si les deux transactions se chevauchent.

Une transaction est un groupe d'opérations traité comme une SEULE opération indivisible du point de vue de la base. Les quatre propriétés garanties sont l'atomicité, la cohérence, l'isolation et la durabilité. ATOMICITÉ : la transaction est exécutée entièrement ou pas du tout, jamais à moitié. COHÉRENCE : elle fait passer la base d'un état vérifiant toutes les contraintes à un autre état les vérifiant également. ISOLATION : son déroulement ne doit pas être perturbé par les transactions concurrentes, et le résultat doit être le même que si elles s'étaient exécutées l'une après l'autre. DURABILITÉ : une fois validée, elle survit à toute panne, y compris une coupure de courant immédiate.

C'est l'ISOLATION qui est violée : le résultat obtenu, 50, n'est celui d'aucune exécution où T1 et T2 se suivraient. En les exécutant l'une puis l'autre, dans un ordre ou dans l'autre, on obtiendrait toujours 20. Le mécanisme qui l'empêche est le VERROUILLAGE : lorsqu'une transaction lit une donnée qu'elle compte modifier, elle pose un verrou dessus, et toute autre transaction qui tente d'y accéder est mise en attente jusqu'à la validation de la première. T2 aurait alors attendu, puis lu 70 au lieu de 100, et écrit 20. Une variante répandue, plus rapide, laisse les deux avancer mais vérifie à la validation que la donnée n'a pas changé depuis la lecture, et rejoue la transaction perdante.

C'est l'ATOMICITÉ qui protège la base, secondée par la durabilité. La transaction n'ayant pas été validée au moment de la panne, elle est ANNULÉE dans son intégralité : au redémarrage, le système relit son journal, constate qu'une transaction est restée incomplète, et défait ses écritures partielles. On retrouve donc les deux comptes exactement dans leur état d'avant le virement : les 200 n'ont ni disparu ni été dupliqués. Sans cette garantie, une panne au mauvais moment ferait s'évaporer de l'argent, ce qui est la raison historique pour laquelle les bases de données existent.

La situation : T1 détient un verrou sur la ressource A et demande B, tandis que T2 détient un verrou sur B et demande A. Aucune des deux ne peut avancer, et aucune ne relâchera son verrou : elles attendront indéfiniment. Cela s'appelle un INTERBLOCAGE. Deux façons de le traiter. La DÉTECTION : le système construit le graphe des attentes, y cherche un cycle, et lorsqu'il en trouve un, choisit une victime dont il annule la transaction pour libérer ses verrous ; la transaction annulée est ensuite rejouée. La PRÉVENTION : on impose à toutes les transactions de demander leurs verrous dans un ordre total fixé à l'avance, par exemple par ordre croissant d'identifiant de ressource ; aucun cycle ne peut alors se former, mais la contrainte est lourde à respecter dans un programme réel.

La relation livre : isbn, clé primaire ; titre ; auteur ; annee. La relation exemplaire : id, clé primaire ; isbn, clé étrangère vers livre ; etat ; date_achat. La relation eleve : id, clé primaire ; nom ; prenom ; classe. La relation emprunt : id, clé primaire ; exemplaire_id, clé étrangère vers exemplaire ; eleve_id, clé étrangère vers eleve ; date_sortie ; date_retour, qui peut être nulle. La table emprunt porte donc deux clés étrangères : c'est une table d'ASSOCIATION entre deux entités, comme la table note l'était entre élèves et devoirs.

La table exemplaire existe parce qu'un livre et un exemplaire ne sont pas la même chose : le livre est une oeuvre, décrite par son titre et son auteur ; l'exemplaire est un objet physique, qui a un état, une date d'achat, et qui peut être perdu ou abîmé indépendamment des autres. Si emprunt pointait directement sur livre, la base ne pourrait plus dire QUEL exemplaire est sorti : elle serait incapable de gérer deux emprunts simultanés du même titre, ni de savoir lequel des trois exemplaires n'est jamais revenu, ni de retirer un exemplaire du circuit sans toucher aux autres. On perdrait aussi la possibilité de compter les exemplaires disponibles, qui est pourtant la question la plus fréquente d'un usager.

La requête : SELECT livre.titre, eleve.nom FROM emprunt JOIN exemplaire ON emprunt.exemplaire_id = exemplaire.id JOIN livre ON exemplaire.isbn = livre.isbn JOIN eleve ON emprunt.eleve_id = eleve.id WHERE emprunt.date_retour IS NULL. Il faut trois jointures parce que le titre est à deux tables de distance de l'emprunt, en passant par l'exemplaire. Et il faut écrire IS NULL, jamais un signe égale suivi de NULL, pour la raison vue à l'exercice 4.

La requête : SELECT livre.titre, COUNT(*) AS n FROM emprunt JOIN exemplaire ON emprunt.exemplaire_id = exemplaire.id JOIN livre ON exemplaire.isbn = livre.isbn GROUP BY livre.isbn, livre.titre ORDER BY n DESC LIMIT 5. Les clauses s'exécutent dans cet ordre : FROM et les jointures rassemblent les lignes ; GROUP BY les regroupe par livre ; COUNT compte chaque groupe ; ORDER BY trie les groupes par effectif décroissant ; LIMIT ne garde que les cinq premiers. Le regroupement porte sur l'ISBN et non sur le seul titre, car deux livres différents peuvent porter le même titre.

Sans index sur exemplaire.id, chaque ligne d'emprunt oblige à parcourir toute la table exemplaire pour trouver sa correspondance : cela fait $52\ 000 \times 6\ 800 = 353\ 600\ 000$ lignes examinées, plus $52\ 000 \times 4\ 200 = 218\ 400\ 000$ pour la seconde jointure. Avec un index, chaque recherche coûte de l'ordre de $\log_{2}(6\ 800) \approx 12{,}7$ comparaisons, donc environ $52\ 000 \times 13 = 676\ 000$ opérations, soit environ $523$ fois moins. En pratique, la clé primaire étant automatiquement indexée par tout moteur, la version efficace est celle que l'on obtient par défaut ; le piège survient quand la jointure porte sur une colonne NON indexée, ce qui est précisément le cas des clés étrangères, que la plupart des moteurs n'indexent PAS automatiquement. La conclusion pratique est donc simple : indexer toutes les clés étrangères, systématiquement.

Un retour ne crée pas de ligne : il complète l'emprunt existant. La commande : UPDATE emprunt SET date_retour = '2026-10-20' WHERE id = 812.

Avant, trois emprunts avaient une date de retour nulle : 812, 813 et 814. Après, il en reste deux, 813 et 814 : la requête de c) renvoie $2$ lignes, Candide avec Bouchard et Germinal avec Roy.

Piège : écrire WHERE exemplaire_id = 501. L'exemplaire 501 a déjà servi à l'emprunt 810 : la commande modifierait DEUX lignes et écraserait la date de retour du 22 septembre. Seule la clé primaire id désigne à coup sûr une seule ligne.

La base ACCEPTE la commande : l'identifiant 815 est neuf, ce qui respecte la clé primaire ; l'exemplaire 503 et l'élève 33 existent, ce qui respecte les deux clés étrangères.

Pourtant l'exemplaire 503 n'est pas rentré : l'emprunt 813 de Bouchard a une date de retour nulle. Le schéma n'exprime pas la règle « un exemplaire n'est prêté qu'à un élève à la fois » ; c'est au programme qui écrit dans la base de la vérifier.

La requête à exécuter avant : SELECT COUNT(*) FROM emprunt WHERE exemplaire_id = 503 AND date_retour IS NULL. Elle renvoie $1$ : l'exemplaire est sorti, on ne l'enregistre pas.

Avec 507, la commande est REFUSÉE : aucun exemplaire d'identifiant 507 n'existe, et la clé étrangère exemplaire_id viole l'intégrité référentielle. Rien n'est écrit.

Piège : croire que les contraintes d'intégrité garantissent toutes les règles de la bibliothèque. Elles garantissent que la ligne désigne des objets qui existent, pas que la situation décrite soit possible.

Le titre est dans livre, l'emprunt pointe vers exemplaire : deux jointures. La requête : SELECT COUNT(*) FROM emprunt JOIN exemplaire ON emprunt.exemplaire_id = exemplaire.id JOIN livre ON exemplaire.isbn = livre.isbn WHERE livre.titre = 'Germinal'.

Les exemplaires de Germinal sont 501 et 502 ; ils apparaissent dans les emprunts 810, 811, 812 et 814. Le résultat est $4$. Le retour enregistré en f) n'y change rien : on compte tous les emprunts, rendus ou non.

Si l'on connaît l'ISBN, la table livre devient inutile, car exemplaire porte déjà l'ISBN : une seule jointure, emprunt JOIN exemplaire ON emprunt.exemplaire_id = exemplaire.id, et le WHERE porte directement sur exemplaire.isbn.

Remarque : le filtre sur le titre suppose qu'un seul livre s'appelle Germinal. Deux éditions différentes ont deux ISBN, comme le notait le corrigé de d) ; le filtre sur l'ISBN est le seul sûr.

La requête : SELECT DISTINCT eleve.nom FROM emprunt JOIN exemplaire ON emprunt.exemplaire_id = exemplaire.id JOIN livre ON exemplaire.isbn = livre.isbn JOIN eleve ON emprunt.eleve_id = eleve.id WHERE livre.titre = 'Germinal' ORDER BY eleve.nom.

Les quatre emprunts de h) sont ceux des élèves 31, 33, 32 et 31, soit Roy, Lambert, Gagnon et Roy. DISTINCT supprime le second Roy, et ORDER BY range les noms : Gagnon, Lambert, Roy, trois lignes.

Il y a moins de lignes qu'en h) parce que h) compte des EMPRUNTS et i) des ÉLÈVES : Roy a emprunté Germinal deux fois, en septembre puis en octobre.

Piège : oublier DISTINCT. La requête renvoie alors quatre lignes, dont Roy deux fois, et le nombre d'élèves annoncé est faux.

La sous-requête calcule la moyenne, $12{,}29$, puis la requête principale garde les notes qui la dépassent : 15, 14 et 17, soit 3 lignes. On ne peut pas écrire WHERE valeur > AVG(valeur), parce que WHERE examine les lignes une par une, avant tout calcul d'agrégat : la moyenne n'existe pas encore à ce moment. La sous-requête la calcule à part, et son résultat devient une simple constante pour la requête principale.

La sous-requête renvoie les identifiants des élèves notés au devoir 12, soit 3 et 4. La requête renvoie donc Ferrand et Ouellet. Avec une jointure : SELECT DISTINCT eleve.nom FROM eleve JOIN note ON note.eleve_id = eleve.id WHERE note.devoir_id = 12. Le DISTINCT est indispensable dans la version avec jointure si un élève peut avoir plusieurs notes au même devoir ; la version avec IN, elle, ne produit jamais de doublon, puisqu'elle ne fait que tester l'appartenance de chaque élève à un ensemble.

SELECT nom FROM eleve WHERE id NOT IN (SELECT eleve_id FROM note WHERE devoir_id = 10). Les élèves notés au devoir 10 sont 1, 2 et 3 : le résultat est Ouellet, seul. Une jointure ne permet pas d'écrire aussi simplement cette requête, parce qu'elle associe des lignes qui EXISTENT, alors qu'on cherche ici une absence.

SELECT eleve.nom FROM eleve JOIN note ON note.eleve_id = eleve.id WHERE note.devoir_id = 10 AND note.valeur = (SELECT MAX(valeur) FROM note WHERE devoir_id = 10). Les notes du devoir 10 sont 12, 9 et 14 : le maximum vaut 14 et le résultat est Ferrand. En cas d'égalité au maximum, la requête renverrait tous les élèves concernés, ce qui est le comportement correct, alors qu'un tri suivi de LIMIT 1 en choisirait un arbitrairement.

Si le devoir 10 porte une ligne à eleve_id nul, la sous-requête renvoie 1, 2, 3 et NULL. La condition id NOT IN (1, 2, 3, NULL) signifie id <> 1 AND id <> 2 AND id <> 3 AND id <> NULL ; or la dernière comparaison donne toujours un résultat INCONNU, donc la conjonction n'est jamais vraie, et aucune ligne n'est retenue. C'est la logique à trois valeurs de l'exercice 4, dans sa forme la plus traîtresse. Correction : exclure les nulls dans la sous-requête, en ajoutant AND eleve_id IS NOT NULL, ou employer NOT EXISTS, qui n'a pas ce défaut.

PRIMARY KEY impose que l'attribut soit unique et non nul : il identifie la ligne. NOT NULL interdit la valeur nulle. REFERENCES déclare une clé étrangère : la valeur doit exister comme clé primaire dans la table désignée. CHECK impose une condition sur la valeur, ici une note entre 0 et 20 et un coefficient strictement positif. Le script contient 2 contraintes REFERENCES, toutes deux dans la table note.

La table note est créée en PREMIER alors qu'elle référence eleve et devoir, qui n'existent pas encore : le moteur ne peut pas vérifier des références vers des tables inconnues. Il faut créer d'abord les tables référencées, eleve et devoir, dans un ordre quelconque entre elles, puis la table note qui s'appuie sur elles. La règle générale : on crée une table après toutes celles qu'elle référence.

(1) REFUSÉE par CHECK, puisque 21 dépasse 20. (2) ACCEPTÉE, et c'est le piège : la condition CHECK appliquée à une valeur nulle donne un résultat inconnu, et une contrainte CHECK ne rejette que ce qui est FAUX ; comme valeur n'est pas déclarée NOT NULL, la ligne entre. (3) REFUSÉE par CHECK, puisque le coefficient 0 n'est pas strictement positif. (4) REFUSÉE par NOT NULL sur l'attribut classe. Trois commandes sur quatre sont donc refusées ; pour refuser aussi la deuxième, il faudrait ajouter NOT NULL à valeur.

Il faut supprimer d'abord note, puis eleve et devoir, dans l'ordre inverse de la création. Supprimer eleve en premier laisserait des lignes de note référencer une table disparue, ce que l'intégrité référentielle interdit : le moteur refuse la suppression tant qu'une clé étrangère pointe vers la table.

Une contrainte d'unicité sur le couple : UNIQUE (eleve_id, devoir_id). Elle n'empêche pas un élève d'avoir plusieurs notes ni un devoir d'en avoir plusieurs, mais elle interdit que le même COUPLE apparaisse deux fois. On aurait aussi pu faire de ce couple la clé primaire de note, à la place de id, comme dans la clé composée de l'exercice 1.

Ferrand a les notes 14 et 17 : la fonction renvoie [(14,), (17,)], une liste de deux tuples à un élément. La virgule dans (14,) est ce qui distingue un tuple à un élément d'un simple nombre entre parenthèses.

La concaténation produit : SELECT valeur FROM note JOIN eleve ON note.eleve_id = eleve.id WHERE eleve.nom = 'x' OR '1'='1'. La condition se lit eleve.nom = 'x' OU '1' = '1' ; la seconde partie est toujours vraie, donc la condition est vraie pour toutes les lignes, et la requête renvoie les 7 notes de la base, celles de tous les élèves.

C'est une INJECTION SQL. Le principe de la faille : le texte saisi par l'utilisateur est collé dans la requête avant son analyse, si bien qu'une apostrophe bien placée ferme la chaîne et transforme la suite de la saisie en code SQL exécuté par le moteur. Selon les droits du compte, la même technique permet de lire d'autres tables, de modifier des notes ou de supprimer des données.

La requête renvoie [], la liste vide : aucun élève ne s'appelle littéralement x' OR '1'='1. Avec le point d'interrogation, la requête est analysée par le moteur AVANT de recevoir la valeur, et la valeur est ensuite transmise comme une donnée, jamais relue comme du code : les apostrophes qu'elle contient ne sont plus que des caractères du nom cherché. C'est la seule protection fiable ; filtrer les apostrophes à la main laisse toujours passer une variante.

Il manque connexion.commit(). Le module ouvre implicitement une transaction avant une écriture, et tant qu'elle n'est pas validée, ses modifications ne sont visibles que de la connexion qui les a faites ; si le programme se termine sans valider, elles sont annulées. C'est la DURABILITÉ, liée à l'atomicité, de l'exercice 9 : seule une transaction validée est garantie et partagée.

Le couple (adherent_id, activite_id) : un adhérent peut suivre plusieurs activités et une activité accueillir plusieurs adhérents, mais le même couple ne doit apparaître qu'une fois. C'est une clé composée, formée de deux clés étrangères, typique d'une table d'association. La table contient 8 lignes.

SELECT adherent.nom FROM adherent JOIN inscription ON inscription.adherent_id = adherent.id JOIN activite ON inscription.activite_id = activite.id WHERE activite.nom = 'escalade'. Les adhérents 1, 2 et 4 sont inscrits à l'activité 1 : le résultat est Lemieux, Garnier et Diallo.

SELECT activite.nom, COUNT(inscription.adherent_id) FROM activite LEFT JOIN inscription ON inscription.activite_id = activite.id GROUP BY activite.id, activite.nom. Résultat : escalade 3, natation 3, judo 2, échecs 0. La jointure externe conserve les échecs, avec une colonne adherent_id nulle ; COUNT(inscription.adherent_id) ne compte que les valeurs non nulles, d'où 0. COUNT(*) compterait la LIGNE, et donnerait 1 aux échecs, un inscrit fantôme. Une jointure interne, elle, ferait disparaître les échecs du résultat.

SELECT adherent.nom, activite.jour FROM inscription JOIN adherent ON inscription.adherent_id = adherent.id JOIN activite ON inscription.activite_id = activite.id GROUP BY adherent.id, adherent.nom, activite.jour HAVING COUNT(*) > 1. Le mardi porte l'escalade et le judo ; seul l'adhérent 4 est inscrit aux deux. Le résultat est Diallo, mardi. On regroupe par adhérent ET par jour : un regroupement par adhérent seul signalerait aussi Lemieux et Garnier, inscrits à deux activités mais pas le même jour.

Les inscrits à l'escalade sont 1, 2 et 4, ceux de la natation 1, 2 et 5 : l'ensemble des adhérents concernés est {1, 2, 4, 5}, d'âges 16, 17, 16 et 18, d'où une moyenne de $\frac{67}{4} = 16{,}75$ ans. Requête correcte : SELECT AVG(age) FROM adherent WHERE id IN (SELECT adherent_id FROM inscription WHERE activite_id IN (1, 2)). Une moyenne calculée sur les lignes de la jointure compterait Lemieux et Garnier DEUX fois, une par activité : $\frac{16 + 17 + 16 + 16 + 17 + 18}{6} = \frac{100}{6} \approx 16{,}67$ ans. Le résultat paraît plausible, ce qui rend l'erreur invisible : c'est la même duplication qu'à l'exercice 5, cette fois cachée dans un agrégat.

La valeur nulle signifie que Bernard n'a PAS de responsable : c'est la racine de l'organisation. L'arbre : Bernard a pour subordonnés Caron et Fortin ; Caron a pour subordonnés Dubois et Éthier ; Fortin a pour subordonné Gagnon. Sa hauteur vaut 2 : le chemin le plus long, de Bernard à Dubois par exemple, compte deux arêtes. La clé étrangère réflexive évoquée à l'exercice 2 sert précisément à ranger une telle hiérarchie dans une seule table.

SELECT p.nom, r.nom FROM personnel AS p JOIN personnel AS r ON p.responsable_id = r.id. La table apparaît deux fois, une fois dans le rôle du membre et une fois dans le rôle du responsable : sans les alias p et r, l'expression personnel.nom serait ambiguë, et le moteur ne saurait pas de quelle copie on parle. On obtient 5 lignes, Bernard étant absent puisque sa clé étrangère nulle ne correspond à aucune ligne. Avec LEFT JOIN, on obtient 6 lignes, Bernard apparaissant avec un responsable nul.

SELECT r.nom, COUNT(*) FROM personnel AS p JOIN personnel AS r ON p.responsable_id = r.id GROUP BY r.id, r.nom. Résultat : Bernard 2, Caron 2, Fortin 1. Les membres sans subordonné n'apparaissent pas ; pour les faire figurer avec 0, il faudrait partir de r en jointure externe et compter p.id, comme au club de sport.

SELECT p.nom FROM personnel AS p JOIN personnel AS r ON p.responsable_id = r.id WHERE r.responsable_id IS NOT NULL. Les responsables qui ont eux-mêmes un responsable sont Caron et Fortin : le résultat est Dubois, Éthier et Gagnon, soit 3 membres, ceux du niveau 2 de l'arbre.

Bernard a 5 subordonnés, tous les autres membres. Une jointure remonte d'un niveau, deux jointures de deux niveaux : une requête écrite avec un nombre fixé de jointures ne voit qu'une profondeur fixée, alors que la hauteur de l'arbre peut changer à chaque réorganisation. Il faut un parcours RÉPÉTÉ jusqu'à ce qu'aucun nouveau membre n'apparaisse : soit une boucle dans le programme Python, qui interroge la base niveau par niveau comme un parcours en largeur, soit une requête récursive, que la plupart des moteurs proposent sous la forme WITH RECURSIVE.

La requête : SELECT COUNT(*), SUM(duree), AVG(duree), MIN(duree), MAX(duree) FROM trajet WHERE depart_id = 1. Le moteur exécute d'abord FROM, puis WHERE, qui ne garde que les trajets partis de la Gare : les lignes 1, 4, 5 et 8, de durées $18$, $5$, $25$ et $12$ minutes. Les cinq fonctions d'agrégation s'appliquent ENSUITE à ces quatre lignes seulement, et le résultat est UNE ligne de cinq colonnes : COUNT(*) vaut $4$, SUM(duree) vaut $18+5+25+12=60$, AVG(duree) vaut $\frac{60}{4}=15$, MIN(duree) vaut $5$ et MAX(duree) vaut $25$. Contrôle en une seconde : la moyenne tombe entre le minimum et le maximum, et elle vaut bien la somme divisée par le compte. L'erreur fréquente est de croire que les agrégats portent sur la table entière : ils portent sur ce que le WHERE a laissé passer.

Le quartier est une colonne de station, la durée une colonne de trajet : il faut une jointure, dont la condition relie la clé étrangère depart_id à la clé primaire de station. La requête : SELECT COUNT(*), AVG(trajet.duree) FROM trajet JOIN station ON trajet.depart_id = station.id WHERE station.quartier = 'Centre'. La jointure associe à chaque trajet SA station de départ, huit lignes ; le WHERE garde celles dont la station est la Gare ou la Mairie, les trajets 1, 2, 4, 5, 6 et 8. On obtient $6$ trajets, une somme de durées de $18+6+5+25+16+12=82$ et une moyenne de $\frac{82}{6}\approx 13{,}67$ minutes. Sans la condition de jointure, le produit cartésien donnerait $8\times 3=24$ lignes, dont $16$ avec une station du Centre, et leur moyenne serait $\frac{2\times 137}{16}=17{,}125$ minutes, la moyenne de TOUS les trajets : un nombre plausible, sans rapport avec la question. C'est le piège de l'exercice 5, caché cette fois dans un agrégat.

La distance du trajet 5 est NULL, c'est-à-dire INCONNUE. Comme toutes les fonctions d'agrégation sauf COUNT(*), AVG ignore les valeurs nulles : elle fait la moyenne des $7$ distances connues, $\frac{4{,}2+1{,}1+4{,}5+0{,}9+3{,}6+5{,}8+2{,}0}{7}=\frac{22{,}1}{7}\approx 3{,}16$ km. SUM(distance) ignore aussi la valeur nulle et vaut $22{,}1$, mais COUNT(*) compte les LIGNES, $8$ : le calcul de l'élève donne $\frac{22{,}1}{8}\approx 2{,}76$ km. Ce second calcul revient à dire que le trajet 5 a fait $0$ km alors qu'il a duré $25$ minutes : il est faux. La valeur juste est celle d'AVG, en précisant qu'elle porte sur les trajets dont la distance est connue. Pour écrire le même calcul à la main, on divise par COUNT(distance), qui ne compte que les valeurs non nulles et vaut $7$.

Sans GROUP BY, on écrit une requête par station, en changeant la valeur filtrée dans le WHERE. SELECT COUNT(*) FROM trajet WHERE depart_id = 1 donne $4$ pour la Gare ; avec depart_id = 2, on obtient $2$ pour la Mairie, les trajets 2 et 6 ; avec depart_id = 3, $2$ encore pour le Parc, les trajets 3 et 7. Le contrôle : $4+2+2=8$, le nombre total de trajets, que donne SELECT COUNT(*) FROM trajet. Si la somme ne tombait pas juste, une station serait oubliée ou un filtre mal écrit. Une copie qui écrit SELECT depart_id, COUNT(*) FROM trajet, sans rien d'autre, mêle une colonne ordinaire et un agrégat : le COUNT(*) résume les huit lignes en une seule, et la requête ne répond pas à la question. La clause GROUP BY depart_id ferait les trois comptes d'un coup, mais elle est hors programme, voir l'exercice 6 ; avec les clauses exigibles, une requête par valeur est la bonne réponse, et la somme la rend vérifiable.

SELECT MAX(duree) FROM trajet donne $34$ minutes. Mais SELECT velo, MAX(duree) FROM trajet mêle une colonne ordinaire, velo, qui a une valeur par ligne, et un agrégat, qui résume toutes les lignes en une seule : la norme SQL la refuse. SQLite l'accepte et renvoie, dans ce cas précis d'un MAX seul, la ligne du maximum ; c'est une particularité de ce moteur, et avec AVG à la place de MAX il prendrait velo sur une ligne quelconque. Deux écritures correctes avec les clauses du programme. Trier : SELECT velo, duree FROM trajet ORDER BY duree DESC, et lire la première ligne, V5 avec $34$ minutes. Ou procéder en deux temps : SELECT MAX(duree) FROM trajet, qui donne $34$, puis SELECT velo FROM trajet WHERE duree = 34, qui donne V5. La seconde a un avantage : en cas d'égalité, elle renvoie TOUS les vélos à $34$ minutes, là où la lecture de la première ligne en oublierait.

Dans station, la clé primaire est id. Dans trajet, la clé primaire est id ; depart_id et arrivee_id sont deux clés étrangères, qui pointent toutes deux vers station.id.

La commande est REFUSÉE : arrivee_id vaut 4, et aucune station d'identifiant 4 n'existe. Le trajet décrirait une arrivée dans une station inconnue, ce que l'intégrité référentielle interdit.

Le système lève une erreur et n'écrit rien : la table trajet compte toujours $8$ lignes. La clé primaire, elle, était respectée, puisque l'identifiant 9 est neuf, et depart_id vaut 3, le Parc, qui existe.

Piège : ne vérifier qu'une des deux clés étrangères. Une table peut en avoir plusieurs vers la même table, et la ligne doit les satisfaire TOUTES.

La commande : UPDATE trajet SET distance = 5.1 WHERE id = 5. Une seule ligne est modifiée, désignée par sa clé primaire.

Il n'y a plus de valeur nulle : COUNT(distance) vaut $8$, la somme des distances vaut $22{,}1+5{,}1=27{,}2$ et AVG(distance) vaut $\frac{27{,}2}{8}=3{,}40$ km.

Le calcul de l'élève, SUM(distance) / COUNT(*), donne maintenant aussi $\frac{27{,}2}{8}=3{,}40$ km : les deux écritures coïncident dès qu'aucune distance n'est nulle. Celle de l'élève reste fragile, elle redeviendrait fausse au premier boîtier en panne.

Contrôle : la nouvelle moyenne $3{,}40$ dépasse l'ancienne, $3{,}16$, ce qui est cohérent puisque la distance ajoutée, $5{,}1$ km, dépasse $3{,}16$.

Piège : écrire WHERE distance = NULL pour viser le trajet sans distance. Cette comparaison n'est jamais vraie, et la commande ne modifie AUCUNE ligne, sans message d'erreur. La bonne écriture est WHERE id = 5, ou à défaut WHERE distance IS NULL.

Le nom de la station est dans station : jointure sur la station de DÉPART. La requête : SELECT DISTINCT station.nom FROM trajet JOIN station ON trajet.depart_id = station.id WHERE trajet.velo = 'V7' ORDER BY station.nom.

Le vélo V7 a fait les trajets 1, 3 et 8, partis des stations 1, 3 et 1 : la Gare, le Parc et la Gare. Avec DISTINCT et le tri : Gare, Parc, deux lignes ; sans DISTINCT, la Gare apparaîtrait deux fois.

La distance totale : SELECT SUM(distance) FROM trajet WHERE velo = 'V7'. Elle vaut $4{,}2+4{,}5+2{,}0=10{,}7$ km ; aucune table n'est à joindre, la distance et le vélo sont dans trajet.

Piège : joindre sur arrivee_id. On obtiendrait les stations d'ARRIVÉE, le Parc, la Mairie et la Gare : trois lignes, qui ne répondent pas à la question.

Quatre trajets référencent la Mairie, d'identifiant 2 : les trajets 2 et 6 en sont partis, les trajets 3 et 4 y sont arrivés. Supprimer la station laisserait ces lignes pointer vers une station inexistante : la clé étrangère l'interdit.

La requête de comptage : SELECT COUNT(*) FROM trajet WHERE depart_id = 2 OR arrivee_id = 2. Elle renvoie $4$.

Les commandes, dans cet ordre : DELETE FROM trajet WHERE depart_id = 2 OR arrivee_id = 2, qui supprime ces $4$ trajets, puis DELETE FROM station WHERE id = 2, qui est alors acceptée.

Il reste $8-4=4$ trajets, les trajets 1, 5, 7 et 8, qui ne touchent que la Gare et le Parc.

Piège : ne supprimer que les trajets partis de la Mairie, WHERE depart_id = 2. Les trajets 3 et 4 y arrivent encore, et la suppression de la station reste refusée.

Effacer l'historique est un choix : on peut aussi garder la station et la marquer fermée, ce que discutent les comportements de l'exercice 2.