LIF4 - TD9 Requêtes SQL Correction - CNRS

LIF4 - TD9
Requêtes SQL
Correction
Exercice 1:
On considéré une entreprise de ventes de voitures. Un modèle de voiture est décrit par une
marque, une dénomination. Une voiture est identifie par un numéro de série, et a un modèle, une
couleur et un prix ache et un cout (prix auquel la voiture est revenue). Des clients, on connat
le nom, le prénom et l'adresse. Parmi les clients, on trouve les anciens propriétaires des voitures
d'occasion, ainsi que les personnes ayant acheté une voiture au magasin. Lorsqu'une vente est
realisee, on en connat le vendeur (dont on connaet le nom, le prénom, l'adresse et le salaire xe)
et le prix d'achat réel (en tenant compte d'un rabais éventuel). Chaque vendeur touche une prime
de 5% de la dierence entre le prix d'achat ache et le cout de la voiture. L'entreprise est repartie
sur un certain nombre de magasins et chaque vendeur opéré dans un magasin unique. Chaque
voiture est, ou a ete, stockée dans certains magasins et est vendue dans le dernier magasin ou elle
a été stockée. On garde trace des dates d’arrivée dans et de départ des magasins. Un transfert de
voiture entre deux magasins se fait dans la journée.
1. Donner un diagramme Entité/Association pour représenter ces données
Correction:
2. Donner un schema de base de donnees 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êtes suivantes en SQL:
1
(a) Donner la liste des voitures (numero) vendues apres 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 rapporte le plus d'argent.
Correction:
SELECT Voiture.numserie
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 accorde 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 beneces 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 rapporte 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)