Aller au contenu

Exercices#

Bases de données et SQL

Mode d’emploi

  • Niveau : ★ échauffement, ★★ classique (niveau bac), ★★★ défi ; les coups de pouce et les corrigés se déplient sous chaque exercice.

  • Écrire chaque requête SQL sur plusieurs lignes (SELECT… / FROM… / WHERE…).

  • Tester ses requêtes dans la console SQL de Basthon : console.basthon.fr/?kernel=sql. Trois bases sont fournies (musique.sql, pays.sql, bibliotheque.sql), à télécharger sur github.com/MathThibaud/nsi-fichiers-eleves, dossier terminale/06-exercices-bdd-sql : copier-coller le script de la base voulue, l’exécuter, puis interroger. Chaque exercice indique la base à charger.

  • Usage de l’IA, indiqué sur chaque exercice : aucune ; en appui (déboguer, reformuler, vérifier ; la réponse finale est écrite et comprise par vous) ; l’exercice s’appuie sur une réponse d’IA. Voir la charte d’usage de l’IA.

Modèle relationnel#

On travaillera ici sur la base bibliotheque, dont voici le schéma (clé primaire soulignée, clé étrangère notée #) :

auteur (id, nom, pays)
livre (id, titre, #id_auteur, annee, genre)
adherent (id, nom, prenom)
emprunt (id, #id_livre, #id_adherent, date_emprunt)

★ Exercice 1 — Lire un schéma #

• essentiel

  1. Combien de relations comporte la base ? Combien d’attributs pour la relation emprunt ?

  2. Donner la clé primaire de livre, puis les clés étrangères de emprunt.

  3. Quel est le rôle de l’attribut id_auteur dans la relation livre ?

  4. Proposer un domaine (type) raisonnable pour annee et pour date_emprunt.

Coup de pouce

Chaque ligne du schéma est une relation ; ses attributs sont les noms entre parenthèses. La clé primaire est soulignée, une clé étrangère est précédée de #. Pour un domaine, se demander quel genre de valeur l’attribut contient : un nombre entier, un texte, une date ?

Corrigé

1. 4 relations ; emprunt a 4 attributs (id, id_livre, id_adherent, date_emprunt).
2. Clé primaire de livre : id. Clés étrangères de emprunt : id_livre (référence livre.id) et id_adherent (référence adherent.id).
3. id_auteur relie chaque livre à son auteur (il pointe vers auteur.id) : cela évite de recopier le nom de l’auteur et garantit la cohérence.
4. annee : INTEGER ; date_emprunt : TEXT (format ’AAAA-MM-JJ’ — SQLite n’a pas de type date natif).

★ Exercice 2 — Clés et contraintes #

  1. Expliquer pourquoi on préfère un attribut id plutôt que titre comme clé primaire de livre.

  2. Le SGBD refuse d’insérer dans emprunt une ligne dont l’id_livre vaut 99. Quelle contrainte est en cause ? L’expliquer.

  3. Rappeler la différence entre une clé primaire et une clé étrangère.

Coup de pouce

Question 1 : deux livres différents peuvent-ils porter le même titre ? Question 2 : l’attribut id_livre d’un emprunt doit correspondre à une ligne existante de quelle relation ?

Corrigé

1. Deux livres peuvent porter le même titre (rééditions, œuvres homonymes) : titre ne garantirait pas l’unicité. Un id numérique la garantit simplement.
2. La contrainte de référence : id_livre = 99 ne correspond à aucun id de livre \(\Rightarrow\) refus.
3. La clé primaire identifie de façon unique les lignes de sa table ; la clé étrangère pointe vers la clé primaire d’une autre table pour les relier (et peut, elle, se répéter).

★★ Exercice 3 — Faire évoluer le modèle #

On souhaite désormais enregistrer, pour chaque livre, sa maison d’édition (nom, ville). Proposer une évolution du schéma qui évite de recopier ces informations dans chaque livre (on précisera la nouvelle relation, sa clé primaire, et la clé étrangère ajoutée).

Coup de pouce

Les informations d’un éditeur ne doivent figurer qu’une fois dans la base. Comment le modèle relationnel relie-t-il deux relations ?

Corrigé

On crée une relation editeur (``id``, nom, ville) et l’on ajoute dans livre une clé étrangère #id_editeur référençant editeur.id. Chaque éditeur n’est alors écrit qu’une seule fois.

Les trois bases fournies#

Base musique :

artiste
id nom pays
1 Daft Punk France
2 Stromae Belgique
3 Adele Royaume-Uni
4 Angèle Belgique
chanson
id titre id_artiste annee duree streams
1 One More Time 1 2000 320 450
2 Get Lucky 1 2013 369 900
3 Instant Crush 1 2013 337 600
4 Alors on danse 2 2009 216 700
5 Papaoutai 2 2013 232 850
6 Formidable 2 2013 197 500
7 Someone Like You 3 2011 285 950
8 Hello 3 2015 295 1000
9 Balance ton quoi 4 2018 191 400
10 Bruxelles je t’aime 4 2021 200 300

(duree en secondes, streams en millions.)

Base pays (population en millions d’habitants, superficie en milliers de km2) :

id nom continent capitale population superficie
1 France Europe Paris 68 552
2 Allemagne Europe Berlin 84 358
3 Italie Europe Rome 59 301
4 Espagne Europe Madrid 48 506
5 Japon Asie Tokyo 125 378
6 Chine Asie Pékin 1412 9597
7 Inde Asie New Delhi 1417 3287
8 Brésil Amérique Brasilia 215 8516
9 Canada Amérique Ottawa 39 9985
10 Mexique Amérique Mexico 128 1964
11 Égypte Afrique Le Caire 109 1002
12 Nigéria Afrique Abuja 219 924
13 Australie Océanie Canberra 26 7692
14 Maroc Afrique Rabat 37 447

Base bibliotheque : schéma donné au début (relations auteur, livre, adherent, emprunt). Charger bibliotheque.sql pour la découvrir.

Interroger : SELECT et WHERE#

★ Exercice 4 — Sélections simples #

• essentiel[base pays — Basthon]

Écrire une requête qui affiche :

  1. le nom de tous les pays d’Europe ;

  2. le nom et la capitale des pays de plus de 100 millions d’habitants ;

  3. le nom des pays d’Asie dont la superficie dépasse 1000.

Coup de pouce

Chaque requête suit le même schéma : SELECT (les colonnes voulues), FROM (la table), WHERE (la condition que doivent vérifier les lignes). Les chaînes de caractères s’écrivent entre apostrophes ; deux conditions exigées ensemble se relient par AND.

Corrigé

Démarche : pour chaque requête, on choisit les colonnes à afficher (SELECT), la table (FROM), puis la condition que doivent vérifier les lignes (WHERE).

SELECT nom FROM pays WHERE continent = 'Europe';                 -- 1.

SELECT nom, capitale FROM pays WHERE population > 100;           -- 2.

SELECT nom FROM pays WHERE continent = 'Asie' AND superficie > 1000; -- 3.

▷ Erreur fréquente. Écrire WHERE continent = Europe sans apostrophes : le SGBD cherche alors une colonne nommée Europe. Les chaînes de caractères s’écrivent entre apostrophes.

★★ Exercice 5 — Conditions composées #

• essentiel[base pays — Basthon]

  1. le nom des pays d’Afrique ou d’Océanie ;

  2. la capitale des pays dont la population est comprise entre 50 et 150 millions ;

  3. le nom des pays qui ne sont pas en Europe.

Coup de pouce

Question 2 : « compris entre » cache deux comparaisons ; quel opérateur logique les relie ? Question 3 : le cours donne un opérateur de différence, et aussi NOT.

Corrigé

Démarche : chaque question combine deux conditions : OR si l’une suffit, AND si les deux sont exigées (« compris entre ») ; « pas en Europe » se traduit par <> (ou par NOT).

SELECT nom FROM pays                                             -- 1.
WHERE continent = 'Afrique' OR continent = 'Océanie';

SELECT capitale FROM pays                                        -- 2.
WHERE population >= 50 AND population <= 150;

SELECT nom FROM pays WHERE continent <> 'Europe';                -- 3.

▷ Erreur fréquente. Écrire continent = ’Afrique’ AND continent = ’Océanie’ : aucun pays n’est sur les deux continents à la fois, et le résultat est vide. « Afrique ou Océanie » se traduit par OR.

Trier, dédoublonner, rechercher un motif#

★ Exercice 6 — Trier et dédoublonner #

• essentiel[base pays — Basthon]

  1. le nom de tous les pays, triés par population décroissante ;

  2. la liste des continents, sans doublon.

Coup de pouce

Question 1 : ORDER BY trie par ordre croissant si l’on ne précise rien ; quel mot-clé ajouter pour l’ordre décroissant ? Question 2 : quel mot-clé, placé juste après SELECT, supprime les doublons ?

Corrigé

Démarche : ORDER BY … DESC trie par ordre décroissant ; DISTINCT, placé juste après SELECT, supprime les doublons.

SELECT nom FROM pays ORDER BY population DESC;   -- 1.
SELECT DISTINCT continent FROM pays;             -- 2.

★★ Exercice 7 — LIMIT et LIKE #

[base pays — Basthon]

  1. le nom et la superficie des 3 pays les plus vastes ;

  2. le nom des pays commençant par la lettre A ;

  3. la capitale des pays dont le nom de capitale contient un espace.

Coup de pouce

Question 1 : trier d’abord, puis ne garder que les premières lignes. Questions 2 et 3 : dans un motif LIKE, le symbole % remplace n’importe quelle suite de caractères ; où le placer ?

Corrigé

Démarche : pour les trois plus vastes, on trie par superficie décroissante, puis LIMIT ne garde que les premières lignes. Dans un motif LIKE, % remplace n’importe quelle suite de caractères (même vide).

SELECT nom, superficie FROM pays                 -- 1.
ORDER BY superficie DESC LIMIT 3;

SELECT nom FROM pays WHERE nom LIKE 'A%';         -- 2.

SELECT capitale FROM pays WHERE capitale LIKE '% %';  -- 3.

▷ Erreur fréquente. Écrire LIKE ’A’ sans % : on ne trouve que les pays dont le nom est exactement « A ». Le % représente la suite du nom.

Agréger#

★ Exercice 8 — Fonctions d’agrégation #

• essentiel[base pays — Basthon]

  1. le nombre de pays de la base ;

  2. la population moyenne ;

  3. la plus grande superficie.

Coup de pouce

Une fonction d’agrégation (COUNT, SUM, AVG, MIN, MAX) se place dans le SELECT et renvoie une seule valeur pour toute la table. COUNT(*) compte les lignes.

Corrigé

Démarche : une fonction d’agrégation, placée dans le SELECT, résume toute une colonne en une seule valeur.

SELECT COUNT(*) FROM pays;         -- 1.
SELECT AVG(population) FROM pays;  -- 2.
SELECT MAX(superficie) FROM pays; -- 3.

★★ Exercice 9 — Agrégats et filtre #

[base pays — Basthon]

  1. la population totale des pays d’Asie ;

  2. la plus petite et la plus grande population, en une requête.

Coup de pouce

Le WHERE s’applique avant l’agrégat : on filtre les lignes, puis on calcule. Un même SELECT peut contenir plusieurs agrégats séparés par des virgules.

Corrigé

Démarche : le WHERE filtre d’abord les lignes, l’agrégat est calculé ensuite sur les lignes restantes ; plusieurs agrégats se séparent par des virgules.

SELECT SUM(population) FROM pays WHERE continent = 'Asie';  -- 1.
SELECT MIN(population), MAX(population) FROM pays;          -- 2.

▷ Erreur fréquente. Mettre l’agrégat dans le WHERE (WHERE population = MAX(population)) : le WHERE examine les lignes une par une, avant tout calcul ; les agrégats se placent dans le SELECT.

★ Exercice 10 — Agréger la base musique #

[base musique — Basthon]

  1. le nombre de chansons sorties en 2013 ;

  2. la durée moyenne des chansons ;

  3. le total des streams de la base.

Coup de pouce

Mêmes outils qu’à l’exercice 8, sur la table chanson. Question 1 : le WHERE garde d’abord les chansons de 2013, puis l’on compte les lignes restantes.

Corrigé

Démarche : mêmes agrégats sur la table chanson ; après un WHERE, COUNT(*) compte les lignes qui vérifient la condition.

SELECT COUNT(*) FROM chanson WHERE annee = 2013;  -- 1.
SELECT AVG(duree) FROM chanson;                   -- 2.
SELECT SUM(streams) FROM chanson;                 -- 3.

Regrouper : GROUP BY#

★★★ Exercice 11 — Regrouper par continent #

≈ 15 min[base pays — Basthon]

  1. le nombre de pays par continent ;

  2. la population totale par continent, triée de la plus grande à la plus petite ;

  3. les continents comptant au moins 4 pays.

Coup de pouce

GROUP BY continent forme un groupe par continent : les agrégats du SELECT se calculent alors groupe par groupe. Question 3 : on filtre des groupes, pas des lignes.

Coup de pouce 2 (début de solution)

Question 1 :
SELECT continent, COUNT(*)
FROM pays
GROUP BY continent;
Les questions 2 et 3 partent de la même structure.

Corrigé 
SELECT continent, COUNT(*) FROM pays GROUP BY continent;   -- 1.

SELECT continent, SUM(population) FROM pays                 -- 2.
GROUP BY continent
ORDER BY SUM(population) DESC;

SELECT continent FROM pays                                  -- 3.  (Europe)
GROUP BY continent
HAVING COUNT(*) >= 4;

Croiser des tables : les jointures#

★★ Exercice 12 — Jointure de deux tables #

• essentiel[base bibliotheque — Basthon]

  1. le titre de chaque livre avec le nom de son auteur ;

  2. les titres des livres écrits par Camus ;

  3. les titres des livres dont l’auteur est du Royaume-Uni.

Coup de pouce

Quelle clé étrangère de livre référence la relation auteur ? C’est elle qui donne la condition du ON. Préfixer les attributs par le nom de leur table en cas d’ambiguïté.

Corrigé

Démarche : la clé étrangère livre.id_auteur référence auteur.id : c’est la condition du ON. On filtre ensuite avec WHERE sur les attributs de auteur.

SELECT livre.titre, auteur.nom                    -- 1.
FROM livre
JOIN auteur ON livre.id_auteur = auteur.id;

SELECT livre.titre                                -- 2.
FROM livre
JOIN auteur ON livre.id_auteur = auteur.id
WHERE auteur.nom = 'Camus';

SELECT livre.titre                                -- 3.
FROM livre
JOIN auteur ON livre.id_auteur = auteur.id
WHERE auteur.pays = 'Royaume-Uni';

▷ Erreur fréquente. Joindre sur livre.id = auteur.id : on associe le livre numéro 1 à l’auteur numéro 1, sans rapport entre eux. La condition relie la clé étrangère (livre.id_auteur) à la clé primaire qu’elle référence.

★★★ Exercice 13 — Jointure de trois tables #

≈ 15 min[base bibliotheque — Basthon]

  1. pour chaque emprunt, le nom et le prenom de l’adhérent avec le titre du livre emprunté ;

  2. le nom de chaque adhérent avec son nombre d’emprunts.

Coup de pouce

emprunt est au centre : elle contient une clé étrangère vers chacune des deux autres relations, d’où deux JOIN … ON successifs. Question 2 : il faut regrouper par adhérent.

Coup de pouce 2 (début de solution)

SELECT adherent.nom, adherent.prenom, livre.titre
FROM emprunt
JOIN adherent ON emprunt.id_adherent = adherent.id …

Corrigé 
SELECT adherent.nom, adherent.prenom, livre.titre    -- 1.
FROM emprunt
JOIN adherent ON emprunt.id_adherent = adherent.id
JOIN livre    ON emprunt.id_livre    = livre.id;

SELECT adherent.nom, COUNT(*)                        -- 2.
FROM emprunt
JOIN adherent ON emprunt.id_adherent = adherent.id
GROUP BY adherent.id;      -- par l'identifiant : deux homonymes restent distincts

★★★ Exercice 14 — Jointure et agrégat (musique) #

≈ 10 min[base musique — Basthon]

Afficher le nom de chaque artiste belge avec son total de streams.

Coup de pouce

Trois étapes : joindre chanson et artiste, ne garder que les artistes belges, regrouper par artiste pour faire la somme.

Coup de pouce 2 (début de solution)

SELECT artiste.nom, SUM(chanson.streams)
FROM chanson
JOIN artiste ON chanson.id_artiste = artiste.id …

Corrigé 
SELECT artiste.nom, SUM(chanson.streams)
FROM chanson
JOIN artiste ON chanson.id_artiste = artiste.id
WHERE artiste.pays = 'Belgique'
GROUP BY artiste.id;

Modifier la base#

★★ Exercice 15 — Ajouter, modifier, supprimer #

• essentiel[base bibliotheque — Basthon]

  1. ajouter l’auteur Victor Hugo (id 6, pays ’France’) ;

  2. remplacer le genre du livre d’id 1 par ’Dystopie’ ;

  3. supprimer l’emprunt d’id 6. (Que se passerait-il sans le WHERE ?)

Coup de pouce

Une instruction par question : INSERT INTO … VALUES, UPDATE … SET … WHERE, DELETE FROM … WHERE. Les chaînes de caractères s’écrivent entre apostrophes.

Corrigé

Démarche : une instruction par opération : INSERT INTO … VALUES, UPDATE … SET … WHERE, DELETE FROM … WHERE ; le WHERE désigne la ou les lignes concernées.

INSERT INTO auteur (id, nom, pays) VALUES (6, 'Hugo', 'France'); -- 1.

UPDATE livre SET genre = 'Dystopie' WHERE id = 1;                -- 2.

DELETE FROM emprunt WHERE id = 6;                                -- 3.

3. Sans le WHERE, DELETE FROM emprunt; effacerait tous les emprunts.

▷ Erreur fréquente. Écrire VALUES (6, Hugo, France) sans apostrophes autour des chaînes : le SGBD prend Hugo et France pour des noms de colonnes et refuse la requête.

★★ Exercice 16 — Le virement bancaire : transactions et ACID #

• essentiel

Une banque stocke ses comptes dans la relation compte (``id``, titulaire, solde), avec la contrainte CHECK (solde >= 0) : un solde ne peut pas être négatif. Au départ, le compte 1 (Léa) contient 100 € et le compte 2 (Noé) 30 €.

Un SGBD garantit qu’une transaction (un groupe d’opérations, ouvert par BEGIN TRANSACTION; et validé par COMMIT;) respecte quatre propriétés, résumées par l’acronyme ACID :

  • Atomicité : toutes les opérations de la transaction sont faites, ou aucune (en cas d’échec, ROLLBACK annule tout) ;

  • Cohérence : la transaction fait passer la base d’un état qui respecte les contraintes à un autre état qui les respecte ;

  • Isolation : des transactions exécutées en même temps ne se perturbent pas, comme si elles avaient lieu l’une après l’autre ;

  • Durabilité : une fois validée, une transaction n’est plus perdue, même en cas de panne.

  • Écrire les deux requêtes UPDATE qui réalisent un virement de 50 € du compte 1 vers le compte 2.

  • Sans transaction, le serveur tombe en panne juste après la première requête. Quel est alors l’état de la base ? Quelle propriété aurait évité ce problème ?

  • On recommence en entourant les deux requêtes de BEGIN TRANSACTION; et COMMIT;. Que contient la base après le COMMIT ? Juste après, une coupure de courant survient : quelle propriété garantit que le virement n’est pas perdu ?

  • On tente maintenant de virer 80 € du compte 1 vers le compte 2, dans une transaction. Laquelle des deux requêtes échoue, et pourquoi ? Que devient la requête qui avait réussi ? Quelle propriété est en jeu ?

  • Le compte 1 contient maintenant 50 €. Léa et son père, chacun depuis son téléphone, retirent au même moment 20 € de ce compte. Chaque application lit le solde, calcule le nouveau solde, puis l’écrit. Quel solde final attend-on ? Montrer qu’avec l’ordre « lecture de Léa, lecture du père, écriture de Léa, écriture du père », le compte finit à 30 €. Qu’est-ce que la banque a perdu ? Quelle propriété le SGBD doit-il assurer ?

Coup de pouce

Question 1 : UPDATE compte SET solde = solde - … WHERE …. Question 5 : faire un tableau à deux colonnes (Léa, père) et noter, ligne après ligne, la valeur lue puis la valeur écrite par chacun.

Corrigé

1. Démarche : un virement, c’est un débit et un crédit : deux UPDATE, chacun ciblant un compte par son id.

UPDATE compte SET solde = solde - 50 WHERE id = 1;
UPDATE compte SET solde = solde + 50 WHERE id = 2;

2. Le compte 1 a été débité (50 €) mais le compte 2 n’a pas été crédité (toujours 30 €) : 50 € ont disparu. L’atomicité l’aurait évité : la transaction est faite entièrement ou pas du tout, donc la panne aurait tout annulé (Léa retrouve ses 100 €).

3. Après le COMMIT : compte 1 à 50 €, compte 2 à 80 €. La durabilité garantit qu’une transaction validée survit à la coupure (elle est écrite sur disque).

4. Le débit UPDATE compte SET solde = solde - 80 WHERE id = 1; échoue : le solde deviendrait \(50 - 80 = -30\), ce que la contrainte CHECK (solde >= 0) interdit. Le crédit de 80 € sur le compte 2, lui, réussit. Attention : le SGBD (SQLite, par exemple) n’annule que l’instruction fautive ; la transaction reste ouverte avec le crédit déjà fait. Si l’on exécutait alors COMMIT, le compte 2 passerait à 160 € : 80 € créés de nulle part ! C’est donc à l’application de détecter l’erreur et d’exécuter ROLLBACK;, qui défait le crédit : les soldes restent 50 € et 80 €. Propriétés : atomicité (tout ou rien), qui préserve la cohérence (la base ne quitte jamais un état qui respecte les contraintes). (Dans SQLite, le message d’erreur commence par « CHECK constraint failed ».)

5. On attend \(50 - 20 - 20 = \mathbf{10}\) €.

Étape Léa Père
1 lit 50
2 lit 50
3 écrit \(50-20 =\) 30
4 écrit \(50-20 =\) 30

Le compte finit à 30 € : 40 € ont été retirés mais seulement 20 € débités. L’écriture de Léa a été écrasée et la banque a perdu 20 €. Le SGBD doit assurer l’isolation : la seconde transaction doit attendre que la première soit terminée, comme si elles s’exécutaient l’une après l’autre.

Vers le bac#

★★ Exercice 17 — Location entre particuliers (d’après Polynésie 2023, jour 2) #

≈ 30 min

Un site de location d’objets entre particuliers utilise le schéma relationnel suivant. La table Possede relie un membre aux objets qu’il propose.

Membre (id_membre, nom, prenom, cp)
Objet (id_objet, type, tarif)
Reservation (id_reservation, #id_objet, #id_membre, date_location, date_retour)
Possede (#id_membre, #id_objet)

  1. Écrire une requête affichant le nom et le prenom des membres dont le code postal cp est ’69003’.

  2. Écrire une requête qui modifie le tarif de l’objet d’id_objet 1 pour le passer à 15.

  3. Écrire une requête qui ajoute le membre Wendie Renard (id_membre 6, cp ’69100’).

  4. On exécute DELETE FROM Membre WHERE nom = ’Ali’ AND prenom = ’Mohamed’;. Expliquer pourquoi cette requête produit une erreur.

    Coup de pouce

    Question 4 : ce membre apparaît-il dans d’autres relations ? Que garantit une contrainte de clé étrangère ?

  5. Mohamed Ali a pour id_membre 1. Proposer la suite de requêtes permettant de le supprimer correctement de la base.

    Coup de pouce

    Question 5 : dans quel ordre supprimer : les lignes qui référencent ce membre, ou le membre lui-même ?

  6. (jointure) Écrire une requête comptant le nombre de réservations réalisées par le membre Fernando Alonso.

  7. (jointure) Écrire une requête donnant les nom et prenom des membres qui possèdent un objet de type ’appareil à raclette’.

    Coup de pouce

    Question 7 : Possede fait le lien entre Membre et Objet : il faut donc deux jointures.

Corrigé

Barème indicatif (sur 4) : Q1 : 0,5 ; Q2 : 0,5 ; Q3 : 0,5 ; Q4 : 0,5 ; Q5 : 0,5 ; Q6 : 0,75 ; Q7 : 0,75

Démarche : une sélection avec WHERE, une mise à jour avec UPDATE … SET … WHERE, un ajout avec INSERT INTO … VALUES.

SELECT nom, prenom FROM Membre WHERE cp = '69003';   -- 1.

UPDATE Objet SET tarif = 15 WHERE id_objet = 1;      -- 2.

INSERT INTO Membre (id_membre, nom, prenom, cp)      -- 3.
VALUES (6, 'Renard', 'Wendie', '69100');

4. Mohamed Ali est référencé (clé étrangère id_membre) par des lignes de Reservation et/ou Possede : le supprimer violerait la contrainte de référence \(\Rightarrow\) erreur.
5. On supprime d’abord les lignes qui le référencent, puis le membre :

DELETE FROM Reservation WHERE id_membre = 1;
DELETE FROM Possede WHERE id_membre = 1;
DELETE FROM Membre WHERE id_membre = 1;

▷ Erreur fréquente. Supprimer le membre en premier : la contrainte de référence l’interdit tant que des lignes de Reservation ou de Possede le référencent. On supprime d’abord ce qui le référence.

Démarche : pour les questions 6 et 7, chaque JOIN … ON relie une clé étrangère à la clé primaire qu’elle référence ; Possede sert de lien entre Membre et Objet.

SELECT COUNT(*)                                      -- 6.
FROM Reservation
JOIN Membre ON Reservation.id_membre = Membre.id_membre
WHERE Membre.nom = 'Alonso' AND Membre.prenom = 'Fernando';

SELECT Membre.nom, Membre.prenom                     -- 7.
FROM Membre
JOIN Possede ON Membre.id_membre = Possede.id_membre
JOIN Objet   ON Possede.id_objet = Objet.id_objet
WHERE Objet.type = 'appareil à raclette';

★★ Exercice 18 — Location de véhicules (d’après La Réunion 2023, jour 2) #

≈ 30 min

Schéma de la base :

vehicule (id_vehicule, immatriculation, marque, modele, type, carburant)
utilisateur (id_utilisateur, nom, prenom, permis, adresse, ville)
location (id_location, #id_utilisateur, #id_vehicule, date_debut, date_fin)

Extrait de la relation vehicule :

id_vehicule immatriculation marque modele type carburant
1 AB-135-YZ Peugeot 208 citadine diesel
2 EC-246-TP Renault Zoe citadine electrique
3 LC-231-MG Tesla Model X SUV electrique
4 ML-128-VM Citroën C3 citadine essence
5 CL-142-CE Citroën C5 berline diesel
6 JL-526-LM Peugeot 508 berline diesel

  1. Donner le résultat de :

    SELECT id_vehicule
FROM vehicule
WHERE type = 'citadine';

  2. Écrire une requête affichant les immatriculation des véhicules diesel.

  3. Écrire une requête calculant le nombre de véhicules de la table.

  4. Citer, en justifiant, les clés étrangères de la relation location.

  5. Expliquer pourquoi la requête suivante produit une erreur (l’id_location 1 existe déjà) :

    Coup de pouce

    Question 5 : quel est le rôle de id_location dans la relation, et quelle propriété doit-il vérifier ?

    INSERT INTO location
VALUES (1, 132, 4, '2022-05-10', '2022-05-12');

  6. Louise DUBOIS (id_utilisateur 133) habite au numéro 50, et non 52, de la rue de la Liberté. Écrire la requête qui corrige son adresse.

  7. (jointure) Écrire une requête listant le modele et l’immatriculation des véhicules dont la location a débuté le ’2022-06-21’, ainsi que le nom et le prenom des utilisateurs correspondants.

    Coup de pouce

    Question 7 : les informations demandées sont réparties dans trois relations : laquelle contient les deux clés étrangères ?

Corrigé

Barème indicatif (sur 4) : Q1 : 0,5 ; Q2 : 0,5 ; Q3 : 0,5 ; Q4 : 0,5 ; Q5 : 0,5 ; Q6 : 0,5 ; Q7 : 1

1. Résultat : 1, 2, 4 (les trois citadines).

SELECT immatriculation FROM vehicule WHERE carburant = 'diesel'; -- 2.
SELECT COUNT(*) FROM vehicule;                                   -- 3.

4. Dans location, id_utilisateur et id_vehicule sont des clés étrangères : ce sont les clés primaires de utilisateur et vehicule, utilisées ici pour référencer ces relations.
5. L’id_location 1 existe déjà : l’insertion violerait la contrainte d’entité (unicité de la clé primaire).

UPDATE utilisateur                                   -- 6.
SET adresse = '50 rue de la Liberté'
WHERE id_utilisateur = 133;

SELECT vehicule.modele, vehicule.immatriculation,    -- 7.
       utilisateur.nom, utilisateur.prenom
FROM location
JOIN vehicule    ON location.id_vehicule    = vehicule.id_vehicule
JOIN utilisateur ON location.id_utilisateur = utilisateur.id_utilisateur
WHERE location.date_debut = '2022-06-21';

★★ Exercice 19 — Podcasts de radio (d’après Centres étrangers 2023, jour 1) #

≈ 30 min

Extraits de podcast (clé primaire id_podcast, #id_emission clé étrangère vers emission) :

id_podcast theme annee id_emission
1 L’enseignement supérieur est-il juste ? 2022 10081
3 Travailleurs de plateformes 2021 10175
4 Souveraineté numérique française 2019 10183
5 Dans le cloud en Islande 2019 10212

La relation emission (id_emission, nom, radio, animateur) décrit les émissions ; la relation description contient un id_description, un resume, une duree (en minutes) et l’id_podcast du podcast décrit.

  1. Écrire le schéma relationnel de la relation description (attributs et types probables, clé primaire soulignée, clé étrangère).

    Coup de pouce

    Question 1 : repérer l’attribut qui identifie chaque description, puis celui qui fait référence à une autre relation.

  2. Écrire ce qu’affiche :

    SELECT theme, annee
FROM podcast
WHERE id_emission = 10081;

  3. Écrire une requête affichant les theme des podcasts de l’année 2019.

  4. Décrire ce que renvoie SELECT DISTINCT theme FROM podcast;.

  5. Le nom de l’animateur de l’émission « Le Temps du débat » doit devenir « Emmanuel L. ». Écrire la requête de mise à jour.

  6. Écrire une requête ajoutant l’émission « Hashtag » (radio « France inter », animateur « Mathieu V. », id_emission 12850).

  7. (jointure) Écrire une requête listant le theme, le nom de l’émission et le resume des podcasts dont la duree est strictement inférieure à 5 minutes.

    Coup de pouce

    Question 7 : description est reliée à podcast par id_podcast, et podcast à emission par id_emission : ce sont ces attributs qui servent dans les conditions de jointure.

Corrigé

Barème indicatif (sur 4) : Q1 : 0,75 ; Q2 : 0,5 ; Q3 : 0,5 ; Q4 : 0,5 ; Q5 : 0,5 ; Q6 : 0,5 ; Q7 : 0,75

1. description (``id_description``:INT, resume:TEXT, duree:INT, #id_podcast:INT).
2. (’L’enseignement supérieur est-il juste ?’, 2022).

SELECT theme FROM podcast WHERE annee = 2019;        -- 3.

4. La liste des thèmes de podcast, sans doublon.

UPDATE emission                                      -- 5.
SET animateur = 'Emmanuel L.'
WHERE nom = 'Le Temps du débat';

INSERT INTO emission (id_emission, nom, radio, animateur)   -- 6.
VALUES (12850, 'Hashtag', 'France inter', 'Mathieu V.');

SELECT podcast.theme, emission.nom, description.resume      -- 7.
FROM description
JOIN podcast  ON description.id_podcast = podcast.id_podcast
JOIN emission ON podcast.id_emission    = emission.id_emission
WHERE description.duree < 5;

★★ Exercice 20 — Vols spatiaux (d’après Métropole 2023, jour 2) #

≈ 30 min

La base comporte les relations suivantes ; extrait de Fusee ci-dessous.

Astronaute (id_astronaute, nom, prenom, nationalite, nb_vols)
Fusee (id_fusee, modele, constructeur, nb_places)
Vol (id_vol, #id_fusee, date)
Equipe (#id_vol, #id_astronaute)

id_fusee modele constructeur nb_places
1 Falcon 9 SpaceX 6
2 Starship SpaceX 100
3 Soyouz TsSKB Progress 2
4 SLS Boeing 6

  1. Donner la définition d’une clé primaire.

  2. On tente d’insérer un astronaute avec id_astronaute = 1 (déjà attribué à PESQUET). Expliquer pourquoi le SGBD renvoie une erreur.

  3. Écrire le schéma relationnel de la table Fusee en précisant le domaine de chaque attribut.

  4. Écrire le résultat de SELECT COUNT(*) FROM Fusee WHERE constructeur = ’SpaceX’;.

  5. Écrire une requête affichant le modele et le constructeur des fusées ayant au moins 4 places.

  6. Écrire une requête affichant les nom et prenom des astronautes dans l’ordre alphabétique du nom.

  7. (jointure) Écrire une requête donnant le nom et le prenom des astronautes ayant décollé le ’25/10/2022’.

Coup de pouce

Question 7 : la date est dans Vol, les noms dans Astronaute ; quelle relation fait le lien entre les deux ?

Corrigé

Barème indicatif (sur 4) : Q1 : 0,5 ; Q2 : 0,5 ; Q3 : 0,75 ; Q4 : 0,25 ; Q5 : 0,5 ; Q6 : 0,5 ; Q7 : 1

1. Une clé primaire est un attribut (ou groupe d’attributs) qui identifie de façon unique chaque enregistrement.
2. id_astronaute = 1 est déjà attribué : l’insertion violerait la contrainte d’entité (unicité de la clé primaire).
3. Fusee (``id_fusee``:INT, modele:TEXT, constructeur:TEXT, nb_places:INT).
4. Résultat : 2 (Falcon 9 et Starship).

SELECT modele, constructeur                          -- 5.
FROM Fusee
WHERE nb_places >= 4;

SELECT nom, prenom                                   -- 6.
FROM Astronaute
ORDER BY nom;

SELECT Astronaute.nom, Astronaute.prenom             -- 7.
FROM Astronaute
JOIN Equipe ON Astronaute.id_astronaute = Equipe.id_astronaute
JOIN Vol    ON Equipe.id_vol            = Vol.id_vol
WHERE Vol.date = '25/10/2022';

▷ Erreur fréquente. Joindre directement Astronaute et Vol : aucune clé étrangère ne les relie. Il faut passer par la relation Equipe, qui contient une clé étrangère vers chacune.

★★ Exercice 21 — Ludothèque (d’après Métropole 2025, jour 2) #

≈ 30 min

Une ludothèque (un seul exemplaire par jeu) utilise le schéma (# = clé étrangère). Quand un jeu est emprunté et pas encore rendu, dateRendu vaut NULL.

jeu (nomJeu, editeur, anneeSortie, ageMinimum, categorie)
adherent (idAdherent, nom, prenom, dateNaissance, adresse)
emprunt (idEmprunt, #nomJeu, #idAdherent, dateEmprunt, dateRendu)

  1. Expliquer pourquoi on ne peut pas prendre l’attribut nom comme clé primaire de adherent.

  2. Décrire ce que renvoie SELECT nomJeu, editeur FROM jeu ORDER BY nomJeu;.

  3. Écrire une requête affichant le nomJeu et la categorie des jeux sortis à partir de 2010 et d’ageMinimum strictement inférieur à 10.

  4. Écrire une requête affichant le nomJeu des jeux en cours d’emprunt (non encore rendus).

    Coup de pouce

    Question 4 : une valeur NULL ne se teste pas avec = : quel opérateur SQL du cours teste l’absence de valeur ?

  5. (jointure) Écrire une requête affichant le nom et le prenom des adhérents ayant emprunté le jeu « Catan ».

  6. Le jeu « Catan » (emprunt d’idEmprunt 1538) a été rendu le ’2025-06-03’. Écrire la requête de mise à jour correspondante.

  7. (modélisation) La ludothèque ajoute une relation evenement (``nom``, dateEvenement, heure) et une relation participation reliant les adhérents aux événements auxquels ils participent. Proposer les clés étrangères de participation en précisant les attributs référencés.

    Coup de pouce

    Question 7 : une participation associe un adhérent et un événement : comment chacun est-il identifié dans sa relation ?

Corrigé

Barème indicatif (sur 4) : Q1 : 0,5 ; Q2 : 0,5 ; Q3 : 0,5 ; Q4 : 0,75 ; Q5 : 0,75 ; Q6 : 0,5 ; Q7 : 0,5

1. Deux adhérents peuvent avoir le même nom (homonymes) : nom ne serait pas unique.
2. Affiche le nomJeu et l’editeur de tous les jeux, triés par ordre alphabétique du nom.

SELECT nomJeu, categorie                             -- 3.
FROM jeu
WHERE anneeSortie >= 2010 AND ageMinimum < 10;

SELECT nomJeu                                        -- 4.  (en cours d'emprunt)
FROM emprunt
WHERE dateRendu IS NULL;

SELECT adherent.nom, adherent.prenom                 -- 5.
FROM adherent
JOIN emprunt ON adherent.idAdherent = emprunt.idAdherent
WHERE emprunt.nomJeu = 'Catan';

UPDATE emprunt                                       -- 6.
SET dateRendu = '2025-06-03'
WHERE idEmprunt = 1538;

7. participation a deux clés étrangères : #nom (référence evenement.nom) et #idAdherent (référence adherent.idAdherent).

Critiquer une réponse d’IA#

★★ Exercice 22 — Une requête proposée par un assistant d’IA #

[base bibliotheque — Basthon]

Sur la base bibliotheque (schéma de la section 1), un élève demande à un assistant d’IA : « Écris la requête SQL qui compte le nombre d’emprunts effectués par l’adhérente dont le nom est ’Durand’. » Voici la réponse obtenue :

SELECT COUNT(*)
FROM emprunt
JOIN adherent
WHERE adherent.nom = 'Durand';

« On joint la table des emprunts à celle des adhérents, on ne garde que les lignes de l’adhérente Durand, et COUNT(*) compte les lignes restantes. »

  1. La réponse est-elle correcte ? La vérifier sur une mini-base : adherent contient (1, ’Durand’, ’Léa’), (2, ’Martin’, ’Noé’), (3, ’Petit’, ’Zoé’) ; emprunt contient cinq lignes, dont deux d’id_adherent 1, deux d’id_adherent 2 et une d’id_adherent 3. Quel résultat attend-on ? Quel résultat renvoie la requête ? (On pourra créer ces deux tables dans Basthon et tester.)

  2. Localiser et corriger l’erreur.

    Coup de pouce

    Sans condition ON, avec quelles lignes de adherent chaque emprunt est-il associé ? Combien de lignes la jointure produit-elle avant le WHERE ?

  3. Quelle vérification simple, faite avant de faire confiance à la réponse, aurait suffi à détecter l’erreur ?

Corrigé

1. Non. On attend 2 (les deux emprunts d’id_adherent 1). Sur la mini-base, la requête renvoie 5 : sans condition ON, la jointure produit le produit cartésien (\(5 \times 3 = 15\) lignes, toutes les combinaisons emprunt/adhérent) ; le WHERE n’en conserve que celles associées à la ligne « Durand », soit les cinq emprunts, quel que soit leur emprunteur.

2. L’erreur est l’absence de condition de jointure (JOIN sans ON). Correction (résultat sur la mini-base : 2) :

SELECT COUNT(*)
FROM emprunt
JOIN adherent ON emprunt.id_adherent = adherent.id
WHERE adherent.nom = 'Durand';

3. La tester sur une toute petite base dont on connaît la réponse, ou afficher les lignes jointes (SELECT *) : les emprunts de Martin ou de Petit sur la ligne « Durand » sautent aux yeux. Réflexe : tout JOIN a son ON.

S’entraîner à l’oral#

★★ Exercice 23 — Expliquer en deux minutes #

Au Grand Oral comme devant l’examinateur de l’épreuve pratique, il faut savoir expliquer une notion clairement, sans lire ses notes. Choisir l’un des sujets suivants (ou le tirer au sort) :

  1. Expliquer à un camarade pourquoi une médiathèque gère ses emprunts avec une base de données relationnelle plutôt qu’avec un tableur.

  2. Expliquer le rôle d’une clé primaire et celui d’une clé étrangère.

  3. Expliquer ce que fait une jointure, sur l’exemple de deux tables.

Préparation (5 min, seul). Noter au brouillon trois idées et un exemple, rien de plus : comme au Grand Oral, ces notes servent d’aide-mémoire. Exposé (2 min, chronométré). Debout, face à un camarade, sans lire ses notes et sans rien écrire : tout passe par la parole. Retour (2 min). Le camarade pose une question de relance (on peut alors s’aider d’un schéma), remplit la grille ci-dessous et donne un conseil ; puis on échange les rôles. Cette grille reprend les critères d’évaluation du Grand Oral.

Coup de pouce

Structurer chaque explication en trois temps : une définition en une phrase, un exemple petit et concret déroulé à voix haute, puis le piège (l’erreur que l’auditeur risque de faire). Finir par une phrase qui répond à la question posée. Garder le même exemple pour tout l’exposé (des livres, des adhérents, des emprunts) et dire pour chaque notion quel problème concret elle évite.

Grille entre camarades Oui En partie Pas encore
Clair — audible, posé ; chaque mot technique est expliqué ▫ ▫ ▫
Juste — c’est exact, et l’exemple montre vraiment l’idée ▫ ▫ ▫
Construit — un fil conducteur, tenu en deux minutes, sans lire ▫ ▫ ▫
Un conseil pour la prochaine fois :
Corrigé

Pas de texte à apprendre par cœur : voici les éléments attendus pour chaque sujet. L’ordre, les mots et l’exemple peuvent être différents ; l’explication est réussie si ces idées y sont, justes et reliées entre elles.

Sujet 1.

  • Les données sont réparties en relations (tables) liées entre elles, sans redondance : l’adresse d’un adhérent n’est écrite qu’une fois.

  • Les contraintes d’intégrité sont vérifiées par le SGBD : il refuse, par exemple, l’emprunt d’un livre qui n’existe pas.

  • Le SGBD gère les accès simultanés de plusieurs utilisateurs, les droits d’accès, les sauvegardes, et répond aux requêtes SQL sur des millions de lignes.

  • Piège : un argument de confort (« c’est plus pratique ») ; les vrais arguments sont la cohérence, la sécurité et le partage.

Sujet 2.

  • Clé primaire : attribut (ou groupe d’attributs) qui identifie chaque enregistrement de façon unique ; deux lignes ne peuvent pas avoir la même.

  • Clé étrangère : attribut qui fait référence à la clé primaire d’une autre table ; c’est elle qui crée le lien entre les tables.

  • Exemple : Livre(id, titre) et Emprunt(id, id_livre, date), où id_livre fait référence à Livre.id.

  • Piège : une clé étrangère n’est pas unique (un livre est emprunté plusieurs fois) ; on ne peut pas supprimer un livre encore référencé par un emprunt.

Sujet 3.

  • Une jointure (JOIN … ON …) associe chaque ligne d’une table aux lignes de l’autre qui vérifient la condition, en général « clé étrangère \(=\) clé primaire ».

  • Exemple :

SELECT Livre.titre, Emprunt.date
FROM Emprunt
JOIN Livre ON Emprunt.id_livre = Livre.id;

  • Le résultat est une table qui réunit des colonnes des deux tables : on lit le titre de chaque livre emprunté.

  • Piège : oublier la condition ON (on obtient toutes les combinaisons) ; préfixer par le nom de la table les attributs de même nom.