Enseignement > Examen de bases de données  

Examen de Bases de données

Licence PRO TAIS Chalon-sur-Saône

4 octobre 2021

durée : 3h

On s'intéresse aux données de la gestion commerciale d'une petite boutique de jouets en bois.

I. Examinez attentivement les fichiers de données suivants pour comprendre les relations qui les lient.
produits.csv, clients.csv, Commande.csv, ligneCommande.csv

Créez un modèle conceptuel de données avec Looping ou tout autre outil à votre convenance. Enregistrez votre modèle au format .loo ou réalisez une copie d'écran.

II. Connectez-vous au serveur de bases de données avec cette interface WEB :
https://dixit.aricia.fr/phpMyAdmin
Votre nom d'utilisateur est votre NOM de famille (en majuscules) et votre mot de passe est votre PRENOM (en majuscule sans accents).
Vous avez alors accès à une base de données qui porte votre nom.
Tout d'abord, modifiez votre mot de passe pour éviter que vos condisciples puissent accéder à votre base de données

 

III. Créez les tables issues de votre modèle conceptuel de données, puis importez les données à partir des fichiers .csv
Attention, la première ligne de ces fichiers décrit les colonnes ; pensez à paramétrer l'importation pour que cette première ligne ne soit pas traitée

Documentation SQL recommandée

IV. Ecrivez des requêtes permettant de

  1. Trier les produits selon leur prix de vente dans l'ordre décroissant
  2. Afficher les clients qui ne résident pas en France
  3. Afficher la référence et le nom des produits commandés par Mr Jérome Fostinelli
  4. Afficher les noms des clients ayant commandé le produit "corde à sauter"
  5. Calculer le chiffre d'affaires réalisé au cours de l'année 2019
  6. Afficher le chiffre d'affaires réalisé par famille de produits
  7. Afficher le hit-parade des ventes (Liste des noms et des quantités des produits les plus vendus dans l'ordre décroissant des ventes)
  8. Trouver les produits qui ne se sont pas vendus
  9. Calculer le panier moyen
  10. Faire une promotion de 20% sur tous les produits de la famille "poupées"

Le code SQL de chacune de ces requêtes devra être copié dans un fichier texte, en précisant avant chaque requête, le numéro de la question.
Ce fichier texte sera à rendre.

On trouve dans la table "produits" et dans la table "ligneCommande" des champs relatifs au prix de vente. Il y a là une certaine redondance. Expliquez, dans votre fichier texte, en quoi cette redondance peut être utile.

Créez une archive zip contenant le modèle conceptuel de votre base de données et le fichier texte de vos requêtes et vos commentaires, et envoyez l'archive zip à l'adresse

francois@bonneville.nom.fr

Corrigé

Modèle conceptuel de données

Code SQL de création des tables :

CREATE TABLE Client(numClient INT, civilite VARCHAR(50), nom VARCHAR(50), prenom VARCHAR(50),
adresse VARCHAR(150), codepostal CHAR(7), ville VARCHAR(50), pays VARCHAR(50),
PRIMARY KEY(numClient));

CREATE TABLE Produit(numProduit INT, famille VARCHAR(50), nom VARCHAR(50), prixHT DECIMAL(15,2),
prixPromoHT DECIMAL(15,2), TVA DECIMAL(15,2), designation VARCHAR(250), PRIMARY KEY(numProduit));

CREATE TABLE Commande(numCommande INT, dateCommande DATE, numClient INT NOT NULL,
PRIMARY KEY(numCommande), FOREIGN KEY(numClient) REFERENCES Client(numClient));

CREATE TABLE ligneCommande(numProduit INT, numCommande INT, prixTTC DECIMAL(15,2), quantite INT,
PRIMARY KEY(numProduit, numCommande), FOREIGN KEY(numProduit) REFERENCES Produit(numProduit),
FOREIGN KEY(numCommande) REFERENCES Commande(numCommande));

 

Question 1
SELECT * FROM produit ORDER BY prixHT DESC

Question 2
SELECT * FROM clientele WHERE pays<>"France"

Question 3
SELECT sum(prixTTC*Quantite) FROM ligneCommande,Commande WHERE ligneCommande.Commande=Commande.NumeroCommande and year(Date_commande)=2019

Question 4
SELECT NumProduit,titre FROM clientele, Commande, ligneCommande, produit WHERE clientele.Nom="Fostinelli" AND clientele.Prenom="Jérome" AND clientele.NumClient=Commande.NumeroClient and Commande.NumeroCommande=ligneCommande.Commande and ligneCommande.Produit=produit.NumProduit

Question 5
SELECT DISTINCT nom,prenom FROM clientele, Commande, ligneCommande, produit WHERE produit.titre="Corde à sauter" AND clientele.NumClient=Commande.NumeroClient and Commande.NumeroCommande=ligneCommande.Commande and ligneCommande.Produit=produit.NumProduit

Question 6
SELECT sum(ligneCommande.prixTTC*ligneCommande.Quantite),produit.famille FROM ligneCommande,produit WHERE ligneCommande.Produit=produit.NumProduit GROUP BY produit.famille

Question 7
SELECT SUM(Quantite) AS SOMME,titre FROM ligneCommande,produit WHERE produit.NumProduit=ligneCommande.Produit GROUP BY ligneCommande.Produit ORDER BY SOMME DESC

Question 8
SELECT produit.titre FROM produit WHERE produit.NumProduit NOT IN (SELECT ligneCommande.Produit FROM ligneCommande)

Question 9
SELECT AVG(somme) FROM (SELECT sum(ligneCommande.prixTTC*ligneCommande.Quantite) as somme FROM ligneCommande GROUP by ligneCommande.Commande) AS PANIER

Question 10
UPDATE produit SET prixPromoHT=prixHT/1.2 WHERE famille="poupées"

La table ligneCommande contient le prix de vent du produit au moment de l'achat, alors que la table produit contient le prix de vente actuel. Entre temps, ce prix a pu changé ; il est donc indispensable d'enregistrer le prix de vente au moment de l'achat.