LIF4 - TD9
Requˆetes SQL
Correction
Exercice 1:
On considere une entreprise de ventes de voitures. Un modele de voiture est d´ecrit par une
marque, une d´enomination. Une voiture est identifi´ee par un num´ero de s´erie, et a un mod`ele, une
couleur et un prix affich´e et un coˆut (prix auquel la voiture est revenue). Des clients, on connaˆıt
le nom, le pr´enom et l’adresse. Parmi les clients, on trouve les anciens propri´etaires des voitures
d’occasion, ainsi que les personnes ayant achet´e une voiture au magasin. Lorsqu’une vente est
r´ealis´ee, on en connaˆıt le vendeur (dont on connaˆıt le nom, le pr´enom, l’adresse et le salaire fixe)
et le prix d’achat r´eel (en tenant compte d’un rabais ´eventuel). Chaque vendeur touche une prime
de 5% de la diff´erence entre le prix d’achat affich´e et le coˆut de la voiture. L’entreprise est r´epartie
sur un certain nombre de magasins et chaque vendeur op`ere dans un magasin unique. Chaque
voiture est, ou a ´et´e, stock´ee dans certains magasins et est vendue dans le dernier magasin ou elle
a ´et´e stock´ee. On garde trace des dates d’arriv´ee dans et de d´epart des magasins. Un transfert de
voiture entre deux magasins se fait dans la journ´ee.
1. Donner un diagramme Entit´e/Association pour repr´esenter ces donn´ees
Correction:
2. Donner un sch´ema de base de donn´ees correspondant `a ce diagramme
Correction:
Voiture(numserie, couleur, prix, cout, marque, modele,nomp?,prenomp?)
Arrivee(numserie,date,magasin)
Client(nom,prenom,adresse)
Vendeur(nom,prenom,adresse,salairefixe,magasin)
Vente(numero,date,prixachat,numserie,noma,prenoma,nomv,prenomv)
3. ´Ecrire les requˆetes suivantes en SQL:
1
ModelemarquedénominationVoiturenumseriecouleurprixcoutArrivéedateMagasinnomVentedateprixachatnumeroVendeurnomprénomadressesalairefixenomprenomadresseClientmodele_devendsvendueeffectueacheteestdansdans1,n1,10,10,n0,11,10,n1,11,10,n1,11,n0,n1,11,n(a) Donner la liste des voitures (num´ero) vendues apr`es le 15 avril 2007.
Correction:
SELECT Voiture.numserie
FROM Voiture, Vente
WHERE Voiture.numserie = Vente.numserie
AND date > ’2007-04-15’
(b) Donner la voiture qui rapport´e le plus d’argent.
Correction:
SELECT Voiture.numserie
Publicité
FROM Voiture,Vente
WHERE Voiture.numserie = Vente.numserie
AND prixachat-cout >= ALL (SELECT prixachat-cout
FROM Voiture,Vente
WHERE Voiture.numserie = Vente.numserie)
(c) Donner le vendeur ayant accord´e le plus gros rabais.
Correction:
SELECT nomv, prenomv
FROM Vente,Voiture
WHERE Voiture.numserie = Vente.numserie
AND prix-prixachat >= ALL (SELECT prix-prixachat
FROM Voiture,Vente
WHERE Voiture.numserie = Vente.numserie)
(d) Les b´en´efices de chaque magasin pour le mois de janvier 2007.
Correction:
SELECT magasin, SUM(benef) as benefice
FROM (SELECT nom,prenom,magasin,
SUM(prixachat-cout-0.5*(prix-cout))-salaire as benef
FROM Voiture,Vente,Vendeur
WHERE Voiture.numserie = Vente.numserie
AND nomv = nom
AND prenomv = prenom
AND date BETWEEN ’2007-01-01’ AND ’2007-01-31’
GROUP BY nom,prenom,magasin) BeneficesVendeurs
GROUP BY magasin
(e) Le meilleur client (celui ayant rapport´e le plus d’argent `a l’entreprise).
Correction:
SELECT nomc,prenomc
FROM Vente,Voiture
WHERE Voiture.numserie = Vente.numserie
GROUP BY nomc,prenomc
HAVING SUM(prixachat-cout-0.5*(prix-cout)) >= ALL
(SELECT SUM(prixachat-cout-0.5*(prix-cout))
FROM Vente,Voiture
WHERE Voiture.numserie = Vente.numserie
GROUP BY nomc,prenomc)
Publicité
(f) La marque pour laquelle on a accord´e le plus de rabais.
Correction: A faire
2
4. ´Ecrire en alg`ebre relationnelle une requˆete pour obtenir la voiture ayant le coˆut le plus
´elev´e. Donner, parmi les requˆetes pr´ec´edentes, celles qui peuvent ˆetre traduites en alg`ebre
relationnelle.
Correction: Requˆete en alg`ebre (les voitures moins celles ayant au moins une voiture plus
ch`ere):
πnumserie(V oiture) \ πnumserie(σcout<cout2V oiture × ρcout/cout2(πcout(V oiture)))
Les requˆetes a,b et c peuvent ˆetre traduites en alg`ebre relationnelle.
Exercice 2:
On consid`ere le Sch´ema de la base de donn´ees CINEMA:
• FILM (NUMF,TITRE, GENRE, ANNEE, DUREE, BUDGET, REALISATEUR, SALAIRE REAL)
• DISTRIBUTION (NUMF, NUMA, ROLE, SALAIRE)
• PERSONNE (NUMP, PRENOM, NOM, DATENAIS)
• ACTEUR (NUMA, AGENT, SPECIALITE, TAILLE, POIDS)
L’attribut REALISATEUR de la relation FILM est l’identifiant d’une PERSONNE. Il en est
de mˆeme pour les attributs NUMA et AGENT de la relation ACTEUR.
Donner les requˆetes SQL permettant de r´epondre aux questions suivantes. Lorsque cela est pos-
sible, on donnera ´egalement les requˆetes ´equivalentes en calcul relationnel de n-uplets (tuples) et
en algebre relationnelle, puis on donnera un plan d’ex´ecution en utilisant l’optimisation a base de
r`egles.
1. Retrouver la liste de tous les films.
Correction:
SELECT *
FROM FILM
2. Retrouver la liste des films dont la longueur d´epasse 180 min.
Correction:
SELECT *
FROM FILM
WHERE DUREE >180
3. Donner la liste de tous les genres de film.
Correction:
SELECT DISTINCT GENRE
FROM FILM
4. Donner le nombre de films par genre.
Publicité
Correction:
3
SELECT GENRE, COUNT(*)
FROM FILM
GROUP BY GENRE
5. Trouver le/les titre(s) et l’/les ann´ee(s) du/des film(s) le(s) plus long(s).
Correction:
SELECT TITRE, ANNEE
FROM FILM
WHERE DUREE =
(SELECT MAX(DUREE)
FROM FILM)
6. Trouver tous les ”couples d’acteurs”, i.e., les acteurs ayant jou´es le ”Premier” rˆole dans un
mˆeme film (sans doublons).
7. Trouver le nom des personnes qui ne sont ni agents, ni acteurs et ni r´ealisateurs.
Correction: Pour les questions suivantes, proposer deux requˆetes diff´erentes : une avec
des jointures et une autre avec le mot cl´e ”IN”.
8. Donner le nom et le pr´enom des r´ealisateurs qui ont jou´e dans au moins un de leurs propres
films
Correction:
• forme plate:
SELECT DISTINCT P.PRENOM, P.NOM
FROM PERSONNE P, FILM F, DISTRIBUTION D
WHERE P.NUMP = F.REALISATEUR
AND WHERE F.NUMF = D.NUMF
AND D.NUMA = F.REALISATEUR
• forme imbriqu´ee:
SELECT DISTINCT PRENOM, NOM
FROM PERSONNE
WHERE P.NUMP IN (
SELECT REALISATEUR
FROM FILM
WHERE (REALISATEUR, NUMF) IN (
SELECT NUMA, NUMF
FROM DISTRIBUTION))
9. Quel est le total des salaires des acteurs du film “Nuits blanches `a Seattle”.
Publicité
Correction:
• forme plate:
SELECT SUM(D.SALAIRE)
FROM FILM F, DISTRIBUTION D
WHERE F.NUMF = D.NUMF
AND F.TITRE = ’Nuits blanches `a SEATLE’
4
• forme imbriqu´ee:
SELECT SUM(SALAIRE)
FROM DISTRIBUTION
WHERE NUMF IN (
SELECT NUMF
FROM FILM WHERE TITLE="NUITS BLANCHES `a SEATLE")
10. Pour chaque film de Spielberg (titre, ann´ee), donner le total des salaires des acteurs.
Correction:
• forme plate
SELECT F.TITRE, F.ANNEE, SUM(D.SALAIRE)
FROM FILM F, DISTRIBUTION D, PERSONNE P
WHERE F.NUMF = D.NUMF
AND F.REALISATEUR = P.NUMP
AND P.NOM = ’Spielberg’
GROUP BY F.TITRE, F.ANNEE
• forme imbriqu´ee
SELECT F.TITRE, F.ANNEE, X.SUMSAL
FROM FILM F, (
SELECT NUMF, SUM(SALAIRE) AS SUMSAL
FROM DISTRIBUTION
GROUP BY NUMF ) AS X
WHERE F.NUMPF = X.NUMF
AND F.REALISATEUR IN(
SELECT NUMP
FROM PERSONNE
WHERE NOM=’SPIELBERG’)
5