• Document: BDWA EXAMEN - 27 MARS 2006 Documents autorisés. Exercice 1. Requêtes décisionnelles
  • Size: 91.66 KB
  • Uploaded: 2018-12-08 09:39:52
  • Status: Successfully converted


Some snippets from your converted document:

Nom : Prénom : Page 1 Université Pierre et Marie Curie – Paris 6 Master d'informatique BDWA EXAMEN - 27 MARS 2006 Documents autorisés Exercice 1. Requêtes décisionnelles 7 pts 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) 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, PRODUITS P, 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 AND P.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 AND V.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 avec fenetre 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.annee between (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 ce pays. 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. En 2005, 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 PRODUIT ANNEE VENTES Italie Livre 2001 1000 Italie Livre 2002 5333 Italie Disque 2002 81500 France Livre 2001 1000 France Livre 2002 6130 Nom : Prénom : Page 3 France Disque 2002 89700 Requete R2 SELECT SUBSTR(pays,1

Recently converted files (publicly available):