Exercice SQL corrigé requêtes complexes

Dans cet exercice, le schéma relationnel est particulièrement simple, a n que l’expression des requêtes soit 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 Personne et Livre et précisant les clés étrangères dans les relations Emprunt et Retard faisant référence à une personne 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:

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)

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 :

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 :

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:

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
)
)