TP : requêtes SQL sur la base bibliothèque

Présentation

On travaille avec la base de données d’une bibliothèque de lycée, qui contient trois tables.

Le TP se fait dans Capytale (bouton « Version Capytale ») : la base y est chargée automatiquement et le résultat attendu figure sous la plupart des questions. On peut aussi télécharger la base bibliotheque.db et l’ouvrir avec DB Browser for SQLite, dans l’onglet Exécuter le SQL.

• Dans Capytale, écrire une seule requête par cellule : seul le résultat de la dernière requête d’une cellule s’affiche.
• Les parties 1 à 4 interrogent la base sans la modifier. Les parties 5 et 6 la modifient : exécuter leurs requêtes une seule fois, dans l’ordre.
• Dans Capytale, les modifications disparaissent quand on recharge la page. Si la base n’est plus dans l’état attendu, cliquer sur Sauvegarder, recharger la page, puis exécuter de nouveau, dans l’ordre, les requêtes de la partie en cours.

Schéma relationnel

• Livres(id_livre, titre, auteur, genre, annee_publication)
• Eleves(id_eleve, nom, prenom, classe)
• Emprunts(id_emprunt, #id_livre, #id_eleve, date_emprunt, date_retour)

Les attributs soulignés forment la clé primaire ; ceux précédés de # sont des clés étrangères : Emprunts.id_livre référence Livres.id_livre et Emprunts.id_eleve référence Eleves.id_eleve.

Les dates sont écrites sous la forme 'AAAA-MM-JJ'. Tous les attributs sont déclarés NOT NULL, sauf date_retour, qui n’a pas de valeur (NULL) tant que le livre n’est pas rendu.

Partie 1 - Découverte de la base

1. Exécuter la requête SELECT * FROM Livres;, puis afficher de même toute la table Eleves. Combien de livres la base contient-elle ? Combien d’élèves ?

2. Afficher toute la table Emprunts. Pour certains emprunts, la date de retour est absente : c’est la valeur NULL, que Capytale affiche null. Quels emprunts sont concernés ? Que cela signifie-t-il ?

-- 1.
SELECT * FROM Livres;
SELECT * FROM Eleves;
-- 2.
SELECT * FROM Emprunts;

Partie 2 - Requêtes de sélection

3. Afficher le titre de tous les livres.

   Indice

   Une seule colonne, sans condition.

4. Afficher le titre et l’auteur de chaque livre, par ordre alphabétique des titres.

   Indice

   La clause ORDER BY se place en fin de requête ; l’ordre croissant est l’ordre par défaut.

5. Afficher les genres présents dans la base, chacun une seule fois.

   Indice

   Le mot-clé DISTINCT, placé juste après SELECT, supprime les doublons.

6. Afficher le titre des livres publiés après 1950.

   Indice

   La condition porte sur l’attribut annee_publication.

7. Afficher le titre des livres de science-fiction, du plus ancien au plus récent.

   Indice

   Le genre est écrit 'Science-fiction' dans la base. La clause ORDER BY se place après WHERE.

8. Afficher le nom et le prénom des élèves de la classe Terminale A.

   Indice

   La classe s’écrit 'Terminale A', entre apostrophes.

-- 3.
SELECT titre FROM Livres;
-- 4.
SELECT titre, auteur FROM Livres
ORDER BY titre;
-- 5.
SELECT DISTINCT genre FROM Livres;
-- 6.
SELECT titre FROM Livres
WHERE annee_publication > 1950;
-- 7.
SELECT titre FROM Livres
WHERE genre = 'Science-fiction'
ORDER BY annee_publication;
-- 8.
SELECT nom, prenom FROM Eleves
WHERE classe = 'Terminale A';

Partie 3 - Fonctions d’agrégation

9. Retrouver par une requête le nombre de livres obtenu à la question 1.

   Indice

   COUNT(*) compte les lignes d’une table.

10. Afficher l’année de publication du livre le plus ancien.

    Indice

    Le livre le plus ancien est celui dont l’année de publication est la plus petite : une fonction d’agrégation la donne directement.

11. Compter les genres différents représentés dans la base. L’expression COUNT(DISTINCT genre) compte chaque valeur différente de genre une seule fois.

    Indice

    Comparer le résultat avec celui de la question 5.

12. Compter les emprunts enregistrés dans la base.

    Indice

    Chaque ligne de la table Emprunts correspond à un emprunt.

13. Compter les emprunts dont le livre n’a pas encore été rendu. Une valeur absente se teste avec IS NULL, jamais avec = NULL.

    Indice

    La condition porte sur l’attribut date_retour.

-- 9.
SELECT COUNT(*) FROM Livres;
-- 10.
SELECT MIN(annee_publication) FROM Livres;
-- 11.
SELECT COUNT(DISTINCT genre) FROM Livres;
-- 12.
SELECT COUNT(*) FROM Emprunts;
-- 13.
SELECT COUNT(*) FROM Emprunts
WHERE date_retour IS NULL;

Partie 4 - Jointures

14. Afficher, pour chaque emprunt, le prénom et le nom de l’élève ainsi que la date de l’emprunt, du plus ancien au plus récent.

    Indice

    Les dates sont dans Emprunts, les noms dans Eleves : joindre ces deux tables sur l’attribut commun id_eleve.

15. Afficher le titre de chaque livre emprunté, avec le prénom de l’emprunteur et la date de l’emprunt.

    Indice

    Trois tables : relier Emprunts à Livres par id_livre, puis à Eleves par id_eleve, avec deux JOIN.

16. Afficher le titre des livres qui n’ont pas encore été rendus, avec le prénom et le nom de l’élève concerné.

    Indice

    Reprendre la jointure de la question 15 et ajouter la condition de la question 13.

17. Afficher le titre et la date d’emprunt des livres empruntés par Alice Martin.

    Indice

    Jointure sur les trois tables, avec une condition sur le prénom et une condition sur le nom, reliées par AND.

18. Afficher, pour chaque élève, son prénom, son nom et son nombre d’emprunts, du plus grand nombre au plus petit, puis par ordre alphabétique des noms en cas d’égalité.

    Indice

    GROUP BY Eleves.id_eleve regroupe les lignes d’un même élève : COUNT(*) compte alors les emprunts de chaque groupe. Nommer ce nombre avec AS permet de trier sur lui. Voir le complément sur GROUP BY du cours sur les requêtes de sélection.

19. Afficher, pour chaque classe, le nombre d’emprunts réalisés par ses élèves.

    Indice

    Même jointure qu’à la question 18, avec un regroupement par classe.

20. Afficher, sans doublon, le prénom des élèves qui ont emprunté au moins un livre de science-fiction.

    Indice

    Jointure sur les trois tables, condition sur le genre, et DISTINCT après SELECT.

21. Afficher le titre de chaque livre emprunté et son nombre d’emprunts, du plus emprunté au moins emprunté. Quel est le livre le plus emprunté ?

    Indice

    Regrouper les emprunts par livre avec GROUP BY Livres.id_livre, compter, puis trier sur ce nombre.

22. Question facultative. Afficher le titre des livres qui n’ont jamais été empruntés. Un tel livre n’apparaît dans aucune ligne de la table Emprunts. La condition id_livre NOT IN (SELECT id_livre FROM Emprunts) est vraie quand l’identifiant du livre ne figure pas dans le résultat de la requête entre parenthèses, appelée sous-requête.

    Indice

    La requête principale porte sur la table Livres ; la condition ci-dessus se place après WHERE.

-- 14.
SELECT Eleves.prenom, Eleves.nom, Emprunts.date_emprunt
FROM Emprunts
JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve
ORDER BY Emprunts.date_emprunt;
-- 15.
SELECT Livres.titre, Eleves.prenom, Emprunts.date_emprunt
FROM Emprunts
JOIN Livres ON Emprunts.id_livre = Livres.id_livre
JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve;
-- 16.
SELECT Livres.titre, Eleves.prenom, Eleves.nom
FROM Emprunts
JOIN Livres ON Emprunts.id_livre = Livres.id_livre
JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve
WHERE Emprunts.date_retour IS NULL;
-- 17.
SELECT Livres.titre, Emprunts.date_emprunt
FROM Emprunts
JOIN Livres ON Emprunts.id_livre = Livres.id_livre
JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve
WHERE Eleves.prenom = 'Alice' AND Eleves.nom = 'Martin';
-- 18.
SELECT Eleves.prenom, Eleves.nom, COUNT(*) AS nb
FROM Emprunts
JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve
GROUP BY Eleves.id_eleve
ORDER BY nb DESC, Eleves.nom;
-- 19.
SELECT Eleves.classe, COUNT(*) AS nb
FROM Emprunts
JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve
GROUP BY Eleves.classe;
-- 20.
SELECT DISTINCT Eleves.prenom
FROM Emprunts
JOIN Livres ON Emprunts.id_livre = Livres.id_livre
JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve
WHERE Livres.genre = 'Science-fiction';
-- 21.
SELECT Livres.titre, COUNT(*) AS nb
FROM Emprunts
JOIN Livres ON Emprunts.id_livre = Livres.id_livre
GROUP BY Livres.id_livre
ORDER BY nb DESC;
-- 22.
SELECT titre FROM Livres
WHERE id_livre NOT IN (SELECT id_livre FROM Emprunts);

Partie 5 - Modification des données

23. Un nouveau livre arrive : Le Meilleur des mondes, d’Aldous Huxley, genre Dystopie, publié en 1932. L’ajouter avec l’identifiant 13, puis afficher la ligne de ce livre.

    Indice

    Les valeurs s’écrivent dans l’ordre des colonnes du schéma, les chaînes entre apostrophes.

24. Le 5 janvier 2026, Raphaël Laurent (identifiant 8) emprunte ce livre. Enregistrer cet emprunt avec l’identifiant 16, puis afficher, grâce à une jointure, le titre du livre, le prénom de l’emprunteur et la date de l’emprunt 16.

    Indice

    La date s’écrit '2026-01-05' et l’absence de date de retour NULL. Pour l’affichage, reprendre la jointure de la question 15.

25. Le 12 janvier 2026, Emma Dubois rend le livre 1984 qu’elle avait emprunté le 15 décembre 2025. Trouver l’identifiant de cet emprunt, enregistrer la date de retour, puis afficher la ligne de l’emprunt.

    Indice

    Le SELECT qui donne l’identifiant reprend la jointure de la question 15, avec trois conditions. L’UPDATE désigne ensuite l’emprunt par sa clé primaire.

26. a. Le livre Les Fleurs du mal (identifiant 12) est retiré de la bibliothèque. Le supprimer de la table Livres, puis compter les livres de la base.

    Indice

    DELETE FROM suivi d’une condition sur la clé primaire.

    b. Le livre 1984 (identifiant 9), trop abîmé, doit aussi être retiré. Essayer de le supprimer. Quel message obtient-on ? Pourquoi ?

    Indice

    Des lignes de la table Emprunts désignent-elles le livre 9 ?

-- 23.
INSERT INTO Livres (id_livre, titre, auteur, genre, annee_publication)
VALUES (13, 'Le Meilleur des mondes', 'Aldous Huxley', 'Dystopie', 1932);
SELECT * FROM Livres WHERE id_livre = 13;
-- 24.
INSERT INTO Emprunts (id_emprunt, id_livre, id_eleve, date_emprunt, date_retour)
VALUES (16, 13, 8, '2026-01-05', NULL);
SELECT Livres.titre, Eleves.prenom, Emprunts.date_emprunt
FROM Emprunts
JOIN Livres ON Emprunts.id_livre = Livres.id_livre
JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve
WHERE Emprunts.id_emprunt = 16;
-- 25.
SELECT Emprunts.id_emprunt
FROM Emprunts
JOIN Livres ON Emprunts.id_livre = Livres.id_livre
JOIN Eleves ON Emprunts.id_eleve = Eleves.id_eleve
WHERE Livres.titre = '1984' AND Eleves.nom = 'Dubois'
  AND Emprunts.date_emprunt = '2025-12-15';
UPDATE Emprunts
SET date_retour = '2026-01-12'
WHERE id_emprunt = 15;
SELECT * FROM Emprunts WHERE id_emprunt = 15;
-- 26. a.
DELETE FROM Livres
WHERE id_livre = 12;
SELECT COUNT(*) FROM Livres;
-- 26. b.
DELETE FROM Livres
WHERE id_livre = 9;

Partie 6 - Contraintes d’intégrité

Les requêtes des questions 27 à 29 sont données : les exécuter, puis lire le message du SGBD.

27. Pourquoi la requête suivante est-elle refusée ?

    INSERT INTO Eleves VALUES (1, 'Dupont', 'Marie', 'Terminale C');
    

    Indice

    Un élève d’identifiant 1 existe-t-il déjà ?

28. Pourquoi la requête suivante est-elle refusée ?

    INSERT INTO Emprunts VALUES (17, 99, 1, '2026-01-10', NULL);
    

    Indice

    Le livre 99 existe-t-il dans la table Livres ?

29. Pourquoi la requête suivante est-elle refusée ? Que garantit la contrainte NOT NULL posée sur l’attribut titre ?

    INSERT INTO Livres VALUES (14, NULL, 'Voltaire', 'Conte', 1759);
    

    Indice

    Quelle valeur la requête donne-t-elle au titre ?

30. Le SGBD SQLite ne vérifie les clés étrangères que si on le lui demande, à chaque connexion, avec la commande PRAGMA foreign_keys = ON;. Dans Capytale, la base est chargée avec cette commande, et DB Browser l’exécute lui-même à l’ouverture de chaque base ; dans le TP sur Python et les bases de données, il faudra l’exécuter soi-même.

    a. Désactiver la vérification avec PRAGMA foreign_keys = OFF;, puis exécuter de nouveau l’insertion de la question 28. Que se passe-t-il ?

    b. Compter les emprunts d’Alice Martin (identifiant 1) dans la table Emprunts, puis afficher, grâce à une jointure avec la table Livres, le titre des livres qu’elle a empruntés. Pourquoi les deux résultats ne concordent-ils pas ?

    Indice

    L’emprunt 17 désigne le livre 99 : quelle ligne de Livres la jointure peut-elle lui associer ?

    c. Supprimer l’emprunt 17, réactiver la vérification avec PRAGMA foreign_keys = ON;, puis la contrôler avec PRAGMA foreign_keys;, qui affiche 1 quand elle est active.

-- 27.
INSERT INTO Eleves VALUES (1, 'Dupont', 'Marie', 'Terminale C');
-- 28.
INSERT INTO Emprunts VALUES (17, 99, 1, '2026-01-10', NULL);
-- 29.
INSERT INTO Livres VALUES (14, NULL, 'Voltaire', 'Conte', 1759);
-- 30. a.
PRAGMA foreign_keys = OFF;
INSERT INTO Emprunts VALUES (17, 99, 1, '2026-01-10', NULL);
-- 30. b.
SELECT COUNT(*) FROM Emprunts WHERE id_eleve = 1;
SELECT Livres.titre
FROM Emprunts
JOIN Livres ON Emprunts.id_livre = Livres.id_livre
WHERE Emprunts.id_eleve = 1;
-- 30. c.
DELETE FROM Emprunts
WHERE id_emprunt = 17;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;