Exercice Langage SQL : Requêtes complexes
Rédigé par , Publié le 12 Décembre 2011, Mise à jour le Lundi, 12 Décembre 2011 03:51Objectif :
S’entraîner à exprimer les requêtes complexes.
Enoncé de l'Exercice
On suppose qu'une bibliothèque gère une base de données dont le schéma est le suivant (les clés primaires des relations sont soulignées) :
Emprunt (Personne, Livre, DateEmprunt, DateRetourPrevue, DateRetourEffective)
Retard (Personne, Livre, DateEmprunt, PenalitéRetard)
Exprimer, lorsque cela est possible, les requêtes suivantes en algèbre relationnelle, en calcul à variable nuplet et en SQL.
- Quelles sont les personnes ayant emprunté le livre "Recueil Examens BD" ?
- Quelles sont les personnes n'ayant jamais rendu de livre en retard ?
- Quelles sont les personnes ayant emprunté tous les livres (empruntés au moins une fois) ?
- Quels sont les livres ayant été empruntés par tout le monde (i.e. tous les emprunteurs) ?
- Quelles sont les personnes ayant toujours rendu en retard les livres qu'elles ont empruntés ?
Dans cet exercice, le schéma relationnel est particulièrement simple, a n que l'expression des requêtessoit facile à exprimer. Il s'agit néanmoins de requêtes complexes. Vous pouvez vous entraînerà exprimer ces requêtes en améliorant le schéma, c'est-à-dire en ajoutant deux relations Personneet Livre et précisant les clés étrangères dans les relations Emprunt et Retard faisant référence à unepersonne et à un livre.
1- Quelles sont les personnes ayant emprunté le livre "Recueil Examens BD" ?
En algèbre relationnelle :
IIPersonne(?Livre='Recueil...'(Emprunt))
En calcul relationnel :
{t.Personne | Emprunt(t) ?(u.Livre='Recueil...') }
En SQL:
1 2 |
SELECT Personne FROM Emprunt WHERE Livre = 'Recueil...' |
2- Quelles sont les personnes n'ayant jamais rendu de livre en retard ?
En algèbre relationnelle :
?Personne(Emprunt)??Personne(Retard)
En calcul relationnel :
{t.Personne | Emprunt(t) ? ¬[? u Retard(u) ? (u.Personne =t.Personne) )]}
En SQL:
Deux manières possibles:
1 2 3 4 5 6 7 8 9 10 11 |
1) SELECT t.Personne FROM Emprunt t WHERE NOT EXISTS (SELECT * FROM Retard u WHERE u.Personne=t.Personne) 2) SELECT Personne FROM Emprunt WHERE Personne NOT IN (SELECT Personne FROM Retard) |
3- Quelles sont les personnes ayant emprunté tous les livres (empruntés au moinsune fois) ?
En SQL: simple traduction de la requête en calcul relationnel :
1 2 3 4 5 6 7 8 9 10 11 |
SELECT t.Personne FROM Emprunt t WHERE NOT EXISTS ( SELECT * FROM Emprunt u WHERE NOT EXISTS ( SELECT * FROM Emprunt v WHERE v.Personne=t.Personne AND v.Livre=u.Livre ) ) |
4- Quels sont les livres ayant été empruntés par tout le monde (i.e. tous les emprunteurs)?
En SQL, simple traduction de la requête en calcul relationnel :
1 2 3 4 5 6 7 8 9 |
SELECT t.Livre FROM Emprunt t WHERE NOT EXISTS ( SELECT * FROM Emprunt u WHERE NOT EXISTS ( SELECT * FROM Emprunt v WHERE u.Livre=t.Livre AND v.Personne=u.Personne ) ) |
5- Quelles sont les personnes ayant toujours rendu en retard les livres qu'elles ontempruntés ?
En calcul relationnel :
{t.Personne | Emprunt(t) ? [? u [Emprunt(u) ? (u.Personne = t.Personne)] =? (? vRetard(v)?(v.Personne =u.Personne)?(u.Livre =v.Livre) )]}
En SQL:
1 2 3 4 5 6 7 8 9 10 |
SELECT t.Personne FROM Emprunt t WHERE NOT EXISTS (SELECT * FROM Emprunt u WHERE u.Personne=t.Personne AND NOT EXISTS (SELECT * FROM Retard v WHERE v.Personne=u.Personne AND v.Livre=u.Livre ) ) |