Exam

Disponible uniquement sur Etudier
  • Pages : 9 (2072 mots )
  • Téléchargement(s) : 0
  • Publié le : 20 mars 2011
Lire le document complet
Aperçu du document
Nom :
Université Pierre et Marie Curie – Paris 6

Prénom : BDWA
EXAMEN - 27 MARS 2006 Documents autorisés

Page 1
Master d'informatique

Exercice 1. Requêtes décisionnelles
On considère une base de données de schéma S suivant :
CLIENTS (id_client, nom_client, id_pays) PAYS (id_pays, nom_pays, region) PRODUITS (id_prod, nom_prod, description, categorie) TEMPS(id_temps, jour, annee,mois) VENTES (id_prod, id_client, id_temps, quantité_vendue, montant_ventes)

7 pts

Question 1. Définissez une vue V1 (Pays, Produit, MeilleureAnnee) sur le schéma S, donnant, par pays et par produit, l’année où les ventes ont été les meilleures pour ce produit dans ce pays.
CREATE VIEW V1 AS SELECT DISTINCT nom_pays Pays, nom_prod Produit, annee MeilleureAnnee FROM VENTES V, TEMPS T, PRODUITSP, CLIENTS C, PAYS Pa WHERE V.id_prod= P.id_prod AND V.id_temps = T.id_temps AND V.id_client = C.id_client AND C.id_pays = Pa.id_pays AND Montant_ventes = ( SELECT MAX(montant_ventes) FROM VENTES V1, TEMPS T1, PRODUITS P1, CLIENTS C1, PAYS Pa1 WHERE V1.id_prod= P1.id_prod AND V1.id_temps = T1.id_temps AND V1.id_client = C1.id_client AND C1.id_pays = Pa1.id_pays AND Pa.id_pays = Pa1.id_pays ANDP.id_prod = P1.id_prod ) ;

Question 2. Définissez une vue V2 (Pays, Produit, Annee, Ventes) sur le schéma S, donnant la somme des ventes par pays, par produit, et par année.
CREATE VIEW V2 AS SELECT DISTINCT nom_pays Pays, nom_prod Produit, annee Annee, SUM(montant_ventes) Ventes FROM VENTES V, TEMPS T, PRODUITS P, CLIENTS C, PAYS Pa WHERE V.id_prod= P.id_prod AND V.id_temps = T.id_temps ANDV.id_client = C.id_client AND C.id_pays = Pa.id_pays GROUP BY nom_pays, nom_prod, annee ;

Question 3. Ecrivez une requête R1 sur la vue V2 en utilisant une fonction analytique permettant de calculer la somme des ventes des 5 dernières années (année en cours incluse) par pays, produit et année.
prompt R1 somme des ventes par pays, produit et année sur les 5 dernières années

prompt solution avecfenetre glissante select pays, produit, annee, sum(ventes) over (partition by pays, produit order by annee range between 4 preceding and current row) as somme5ans from V2 order by pays, produit, annee ;

Nom :

Prénom :

Page 2

prompt solution avec requete imbriquée select pays, produit, annee, ( select sum(ventes) from v2 b where a.pays = b.pays and a.produit = b.produit and b.anneebetween (a.annee - 4) and a.annee ) somme5ans from V2 a group by pays, produit, annee order by pays, produit, annee ;

prompt solution avec jointure select a.pays, a.produit, a.annee, sum(b.ventes) as somme5ans from V2 a, V2 b where a.pays = b.pays and a.produit = b.produit and b.annee between (a.annee - 4) and a.annee group by a.pays, a.produit, a.annee order by a.pays, a.produit, a.annee ;prompt solution erronée : la somme ne se limite pas à un produit et une année : select pays, produit, annee, sum(ventes) over (order by annee range between 4 preceding and current row) as somme5ans from V2 order by pays, produit, annee ;

Question 4. Définissez une vue V3, donnant la somme des ventes par pays, produit, année et l’année où les ventes ont été les meilleures pour ce produit dans cepays.
CREATE VIEW V3 AS SELECT V1.Pays, v1.Produit, Annee, Ventes, MeilleureAnnee FROM V1, V2 WHERE V1.Pays = V2.Pays AND V1.Produit=V2.Produit ;

Question 5. Ecrivez la requête SQL sur la vue V2, qui permet de construire un tableau contenant, pour l’Italie et pour la France, les ventes de chaque produit par année, en respectant les règles suivantes : Les ventes de livres en 2004 sont de 1000. En2005, les ventes de livres sont la somme des ventes de livres en 2003 et 2004. Les ventes de disques en 2005 sont les mêmes qu’en 2004. Ce tableau contient des données extraites de la vue et des données calculées. Un exemple de tableau montrant un résultat de cette requête est donné ci-dessous : PAYS Italie Italie Italie France France PRODUIT Livre Livre Disque Livre Livre ANNEE 2001 2002 2002...
tracking img