TD
Requêtes SQL
Exercice 1:
On considère une entreprise de ventes de voitures. Un modèle de voiture est d´écrit par
une marque, une d´dénomination. Une voiture est identifiée par un numéro de série, et a un
modèle, une couleur et un prix affiché et un coût (prix auquel la voiture est revenue). Des
clients, on connaît 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 réalisée, on en connaît le vendeur (dont on connaît le nom, le
prénom, l’adresse et le salaire fixe) et le prix d’achat réel (en tenant compte d’un rabais
´éventuel). Chaque vendeur touche une prime de 5% de la différence entre le prix d’achat
affiché et le coût de la voiture. L’entreprise est répartie sur un certain nombre de magasins et
chaque vendeur opère dans un magasin unique. Chaque voiture est, ou à été, stockée dans
certains magasins et est vendue dans le dernier magasin où 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:
1,1
2. Donner un schéma de base de données correspondant `à ce diagramme
3. Ecrire les requêtes suivantes en SQL:
1
(a) Donner la liste des voitures (numéro) vendues après le 15 avril 2007.
(b) Donner la voiture qui rapp ortéle plus d’argent.
(c) Donner le vendeur ayant accordé le plus gros rabais.
(d) Les bénéfices de chaque magasin pour le mois de janvier 2007.
(e) Le meilleur client (celui ayant rapporté le plus d’argent à l’entreprise).
(f) La marque pour laquelle on a accordé le plus de rabais.
4. Ecrire en algèbre relationnelle une requête pour obtenir la voiture ayant le coût le plus
Élevé. Donner, parmi les requêtes précédentes, celles qui peuvent être traduites en algèbre relationnelle.
Exercice 2:
On considère le Schéma de la base de données 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ême pour les attributs NUMA et AGENT de la relation ACTEUR.
Donner les requêtes SQL permettant de répondre aux questions suivantes. Lorsque cela est
possible, on donnera ´également les requêtes ´équivalentes en calcul relationnel de n-uplets
(tuples) et en algèbre relationnelle, puis on donnera un plan d’exécution en utilisant l’optimisation
à base de règles.
1. Retrouver la liste de tous les films.
2. Retrouver la liste des films dont la longueur dépasse 180 min.
3. Donner la liste de tous les genres de film.
4. Donner le nombre de films par genre.
5. Trouver le/les titre(s) et l’/les année(s) du/des film(s) le(s) plus long(s).
6. Trouver tous les ”couples d’acteurs”, i.e., les acteurs ayant joués le ”Premier” rôle dans un même film
(sans doublons).
7. Trouver le nom des personnes qui ne sont ni agents, ni acteurs et ni réalisateurs.
8. Donner le nom et le prénom des réalisateurs qui ont joué dans au moins un de leurs propres films
9. Quel est le total des salaires des acteurs du film “Nuits blanches à Seattle”.
10. Pour chaque film de Spielberg (titre, année), donner le total des salaires des acteurs.
