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.

Télécharger la base bibliotheque.db puis l’ouvrir avec DB Browser for SQLite.

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 précédés de # sont des clés étrangères : id_livre référence la table Livres et id_eleve référence la table Eleves. L’attribut date_retour vaut NULL lorsque le livre n’a pas encore été rendu.

Prise en main

Dans DB Browser for SQLite, aller dans l’onglet Parcourir les données et observer le contenu de chacune des trois tables.

1. Combien de livres la base contient-elle ?
2. Combien d’élèves sont inscrits ?
3. Repérer les emprunts dont la colonne date_retour est vide (NULL). Que cela signifie-t-il ?

Exercice 1 - Requêtes de sélection

Aller dans l’onglet Exécuter le SQL. Pour chaque question, écrire la requête SQL correspondante et vérifier le résultat obtenu.

1. Afficher les titres de tous les livres.
2. Afficher les titres et les auteurs de tous les livres, triés par ordre alphabétique du titre.
3. Afficher les genres distincts présents dans la base.
4. Afficher les titres des livres publiés après 1950.
5. Afficher les titres des livres du genre « Science-fiction », triés par année de publication croissante.
6. Afficher le nom et le prénom des élèves de la classe « Terminale A ».

Exercice 2 - Fonctions d’agrégation

1. Combien de livres y a-t-il dans la base ? (Utiliser COUNT.)
2. Quelle est l’année de publication du livre le plus ancien ? (Utiliser MIN.)
3. Combien de genres différents sont représentés dans la base ?
4. Combien d’emprunts ont été effectués au total ?
5. Combien d’emprunts n’ont pas encore été retournés (c’est-à-dire dont date_retour est NULL) ?

Exercice 3 - Jointures

Pour répondre aux questions suivantes, il faut croiser les informations de plusieurs tables à l’aide de jointures.

1. Afficher le prénom et le nom de chaque élève ayant effectué au moins un emprunt, ainsi que la date de l’emprunt. On triera les résultats par date d’emprunt croissante.

   Indication : joindre les tables Emprunts et Eleves sur l’attribut id_eleve.

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

   Indication : cette requête nécessite une jointure sur trois tables.

3. Afficher les titres des livres actuellement empruntés (non encore rendus), avec le prénom et le nom de l’élève concerné.

4. Quels livres Alice Martin a-t-elle empruntés ? Afficher les titres et les dates d’emprunt.

5. Afficher, pour chaque élève (prénom et nom), le nombre total d’emprunts effectués. Trier par nombre d’emprunts décroissant.

   Indication : utiliser COUNT avec GROUP BY.

Exercice 4 - Modification des données

1. Un nouveau livre vient d’arriver à la bibliothèque : Le Meilleur des mondes d’Aldous Huxley, genre « Dystopie », publié en 1932. L’identifiant sera 13. Écrire la requête INSERT correspondante, puis vérifier que le livre apparaît bien dans la table.

2. L’élève Raphaël Laurent (identifiant 8) emprunte ce nouveau livre aujourd’hui. Écrire la requête INSERT qui crée cet emprunt (identifiant 16, date du jour, pas de date de retour).

3. Emma Dubois rend le livre 1984 qu’elle avait emprunté le 15 décembre 2025. Écrire une requête UPDATE pour mettre à jour la date de retour avec la date du jour.

   Indication : identifier d’abord l’identifiant de l’emprunt concerné grâce à une requête SELECT.

4. Le livre Les Fleurs du mal (identifiant 12) est retiré de la bibliothèque. Que se passe-t-il si on tente de le supprimer avec DELETE FROM Livres WHERE id_livre = 12 ? Pourquoi cette requête pose-t-elle un problème si le livre a été emprunté ?

Exercice 5 - Contraintes d’intégrité en action

Tester les requêtes suivantes et expliquer le résultat obtenu dans chaque cas.

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

   Pourquoi cette insertion échoue-t-elle ?

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

   Quel problème cette requête pose-t-elle ?

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

   Que vérifie la contrainte NOT NULL sur l’attribut titre ?

4. Activer les clés étrangères en exécutant PRAGMA foreign_keys = ON; avant les requêtes précédentes. Refaire les tests des questions 1 et 2. Le comportement change-t-il ?

   Remarque. Par défaut, SQLite ne vérifie pas les contraintes de clé étrangère. Il faut les activer manuellement avec cette commande PRAGMA à chaque nouvelle connexion.

Pour aller plus loin

1. Afficher les livres qui n’ont jamais été empruntés.

   Indication : un livre qui n’a jamais été emprunté n’apparaît dans aucune ligne de la table Emprunts. On pourra utiliser une sous-requête avec NOT IN.

2. Quel est le livre le plus emprunté ? Afficher son titre et le nombre d’emprunts.

3. Afficher, pour chaque classe, le nombre d’emprunts réalisés par les élèves de cette classe.

4. Quels élèves ont emprunté au moins un livre de science-fiction ? Afficher leurs prénoms sans doublons.