Emprunt de livres dans un CDI

La base de données correspond à la modélisation d’emprunt de livres dans un CDI :

• Chaque élève d’un lycée peut emprunter des livres au CDI, les données concernants ces livres et les emprunts en cours sont stockés dans une base de données.

• Les auteurs ainsi que les éditeurs figurent également dans cette base.

Travail préalable

0. Déterminer la structure de la base de données et indiquer son schéma relationnel.

Requêtes simples

Donner le code SQL de chacune des requêtes suivantes.

1. Afficher tous les noms des auteurs.

1
2

SELECT nom 
FROM auteurs;

2. Afficher le titre de tous les livres.

1

SELECT titre FROM livres;

3. Afficher les noms des classes du lycée sans doublon.

1

SELECT classe FROM eleves;

4. Afficher les titres des livres et les annees d’édition classés selon les années.

1
2
3

SELECT titre, annee
FROM livres
ORDER BY annee ASC;

5. Quels sont les livres dont le titre contient le mot Astérix ?

1
2
3

SELECT titre
FROM livres
WHERE titre LIKE "%Astérix%";

Expressions et fonctions

Donner le code SQL de chacune des requêtes suivantes.

6. Afficher les noms et prénoms des élèves de la classe 1-G1.

1
2
3

SELECT nom, prenom
FROM eleves
WHERE classe = '1-G1';

7. Afficher les titres des livres publiés après 2000.

1
2
3

SELECT titre
FROM livres
WHERE annee > 2000;

8. Afficher les isbn des livres dont la date retour est déjà passée (au 25 octobre 2020).

1
2
3

SELECT isbn
FROM emprunt
WHERE date_ret > 2020-10-25;

9. Combien d’auteurs sont présents dans la base de données ?

1
2

SELECT COUNT(*)
FROM auteurs;

10. Quelle est l’annee d’édition du ou des livre(s) le(s) plus ancien(s) ?

1
2

SELECT MIN(annee)
FROM livres;

Requêtes imbriquées

Donner le code SQL de chacune des requêtes suivantes.

11. Afficher les titres des livres empruntés.

1
2
3
4

SELECT titre
FROM livres
WHERE isbn IN (SELECT isbn
    FROM emprunt);

12. Afficher, sans doublon, le nom et prénom des élèves qui ont emprunté au moins un livre.

1
2
3
4

SELECT DISTINCT nom, prenom
FROM eleves
WHERE num_etu IN (SELECT num_etu
    FROM emprunt);

13. Afficher, sans doublon, le nom et prénom des élèves qui ont emprunté au moins un livre avec une date retour dépassée (au 25 octobre 2020).

1
2
3
4
5

SELECT DISTINCT nom, prenom
FROM eleves
WHERE num_etu IN (SELECT num_etu
    FROM emprunt
    WHERE date_ret > '2020-10-25');

14. Quels sont les editeurs à avoir édité un livre contenant Astérix dans le titre ?

1
2
3
4
5

SELECT nom 
FROM editeurs
WHERE siret IN (SELECT siret
    FROM livres
    WHERE titre LIKE "%Astérix%");

15. Quel est le titre du ou des livre(s) le(s) plus ancien(s) ?

1
2
3
4

SELECT titre 
FROM livres
WHERE annee IN (SELECT min(annee)
    FROM livres);

Jointures

Donner le code SQL de chacune des requêtes suivantes en utilisant la clause JOIN.

16. Afficher les titres des livres empruntés.

1
2
3
4

SELECT titre
FROM livres 
JOIN emprunt
ON livres.isbn = emprunt.isbn;

17. Afficher, sans doublon, le nom et prénom des élèves qui ont emprunté au moins un livre.

1
2
3
4

SELECT DISTINCT nom, prenom
FROM eleves 
JOIN emprunt 
ON eleves.num_etu = emprunt.num_etu;

18. Afficher, sans doublon, le nom et prénom des élèves qui ont emprunté au moins un livre avec une date retour dépassée (au 25 octobre 2020).

1
2
3
4

SELECT nom, prenom
FROM eleves
JOIN emprunt ON eleves.num_etu = emprunt.num_etu
WHERE date_ret > '2020-10-25';

19. Qui est l’auteur du livre 1984 ?

1
2
3
4
5

SELECT nom 
FROM auteurs
JOIN ecrire ON auteurs.a_id = ecrire.a_id
JOIN livres ON ecrire.isbn = livres.isbn
WHERE livres.titre = "1984";

20. Afficher les titres des livres écrit par Pierre Boulle.

1
2
3
4
5

SELECT titre 
FROM livres
JOIN ecrire ON livres.isbn = ecrire.isbn
JOIN auteurs ON ecrire.a_id = auteurs.a_id
WHERE auteurs.nom = 'Pierre Boulle';

21. Combien de livres ont été écrit par Isaac Asimov ?

1
2
3
4
5

SELECT COUNT(livres.isbn)
FROM livres 
JOIN ecrire ON livres.isbn = ecrire.isbn
JOIN auteurs ON ecrire.a_id = auteurs.a_id
WHERE auteurs.nom = "Isaac Asimov";

1
2
3
4

SELECT COUNT(isbn)
FROM ecrire
JOIN auteurs on ecrire.a_id = auteurs.a_id
WHERE auteurs.nom = "Isaac Asimov";

22. Afficher les noms des editeurs ayant édité un écrit par Barjavel.

1
2
3
4
5
6

SELECT DISTINCT editeurs.nom 
FROM editeurs
JOIN livres ON editeurs.siret = livres.siret
JOIN ecrire ON livres.isbn = ecrire.isbn
JOIN auteurs ON ecrire.a_id = auteurs.a_id
WHERE auteurs.nom like '%Barjavel%';