TD3 – SQL
Soit le schéma relationnel de la base de données commerciale suivante :
Client (Codc, Nomc, Ville)
Commande (Numc, Datec, Heurec, Mntc, #Codc, EtatC )
Produit (RefP, Libp, PU, Qtes, Seuil)
Fournisseur (Codf, Nomf, Villef)
Lig_cde (#Numc, #RefP, Qtec)
Frs_Prod (#Codf, #RefP, PUf, Qtef)
Partie 1 :
1. Créer en SQL la base de données commerciale en définissant les types des attributs selon le tableau ci-dessous et
en introduisant les contraintes nécessaires pour les clés primaires et secondaires
On nommera respectivement les contraintes de clé primaires: pk_Codc ; pk_Numc ; pk_RefP ; pk_Codf ;
pk_Lig; pk_Frs
On nommera respectivement les contraintes de clé étrangères: Fk_Codc ; Fk_Numc ; Fk_RefP_Lig; Fk_Codf ;
Fk_RefP_Frs
Attribut Type Dimension Description
Codc Numérique 4 Code du Client
Nomc Chaîne de caractères 20 Nom Client
Ville Chaîne de caractères 20 Ville du Client
Numc Numérique 4 Numéro de Commande
Datec Date - Date de Commande
Heurec Date Heure de la Commande
Mntc Numérique 10,3 Montant de la Commande
Etatc Chaîne de caractères 40 Type de la commande
RefP Chaîne de caractères 6 Référence du Produit
Libp Chaîne de caractères 20 Libellé du Produit
PU Numérique 10,3 Prix de vente unitaire du Produit
Qtes Numérique 4 Quantité en stock du Produit
Seuil Numérique 4 Seuil de réapprovisionnement du Produit
Codf Numérique 4 Code du fournisseur
Nomf Chaîne de caractères 20 Nom du fournisseur
Villef Chaîne de caractères 10 Ville du Fournisseur
Qtec Numérique 4 Quantité commandée d’un produit
PUf Numérique 10,3 Prix unitaire du Produit fourni
Qtef Numérique 4 Quantité fournie d’un produit
2. Modifier la structure de la table Commande afin de s’assurer que la clé étrangère possède la propriété "NOT
NULL".
3. Pour des besoins de suivi des clients d’autres informations s’avèrent utiles. Modifier la structure de la table Client
en rajoutant les attributs suivants :
Attribut Type Longueur Description
CA Numérique 10,3 Chiffre d’affaire Client
Cred Numérique 10,3 Crédit Client
CredMax Numérique 10,3 Crédit maximum autorisé Client
4. Modifier l’attribut Ville de la table Client afin d’avoir une longueur égale à 30.
5. Ajouter une contrainte (ck_Cred) à la table Client afin d’assurer un contrôle lors de l’introduction des données de
l’attribut Cred tel que (Cred <= CredMax).
6. Ajouter les contraintes ck_Qtes, ck_Seuil, ck_Qtec et ck_Qtef aux attributs Qtes, Seuil, Qtec et Qtef pour que les
valeurs introduites soient positives (>0).
7. Ajouter une contrainte (ck_Type) à la table Commande afin d’assurer que le champ EtatC possède l’une des
valeurs suivantes : {en attente, en cours, validée, livrée}
8. Insérer dans la table Client et Commande les données suivantes.
Client
Codc Nomc Ville CA Cred CredMax
100 STS Sousse 1250 120 350
200 STIP Sousse
300 AMS Monastir
400 TOTAL Sousse
500 METS Bizerte
600 TOUTA Bizerte
700 STB Sousse
800 COMAR Tunis
900 INFOPLUS Tunis
1000 BIAT Tunis
Commande
Numc Datec HeureC Mntc Codc EtatC
10 02/01/2010 15:30 0 100 livrée
20 02/01/2010 16:40 200
30 04/01/2010 100
40 15/01/2010 300
50 15/01/2010 100
60 23/01/2010 500
70 01/02/2010 600
80 05/02/2010 300
90 10/02/2010 500
9. Mettre à jour la table Client en initialisant les attributs CA et Cred à 0 ainsi que CredMax à 1000.
10. Mettre à jour la table Commande en initialisant l’attribut Mntc à 0.
11. Mettre à jour la table Produit en affectant à l’attribut Seuil, 10% de la quantité en stock pour chaque produit.
Partie 2 :
12. Donner la liste des produits figurant dans la base de données.
13. Donner la liste des produits dont le prix ne dépasse pas 100DT.
14. Donner les commandes (Numc, Codc) qui ont été passées après le 31/01/2010.
15. Donner la liste des produits (RefP, Libp, Qtes) à approvisionner.
16. Donner les clients qui ne se trouvent pas dans la ville de Tunis et qui ont dépassé leur crédit maximum.
17. Donner le détail des produits dont le prix est supérieur à 250DT classés par ordre croissant des prix.
18. Donner le nombre de clients qui ont passé des commandes durant le mois de Janvier 2010.
19. Donner le total des quantités commandées du produit BU44 en renommant le résultat par TotP.
20. Donner les clients qui ont un chiffre d’affaire plus grand que la moyenne des chiffres d’affaires de tous les
clients.
21. Donner le détail du produit le plus cher.
22. Donner les libellés et les prix des produits commandés de la commande 70 classés par ordre alphabétique.
23. Donner les clients (Codc, Nomc, Ville, CA) qui ont passé une commande, classés par ordre décroissant du
CA.
24. Quels sont les produits qui ont un prix plus grand que le prix du produit de référence MI33.
25. Donner le total des quantités fournies par fournisseur.
26. Donner le détail des commandes des clients de la ville de Sousse (Ref, Libp, PU, Qtec, Numc, Datec, Codc,
Nomc).
27. Donner le nombre de commandes par client.
28. Donner le nombre de produits par commande.
29. Donner les produits qui n’ont jamais été commandés
30. Donner les fournisseurs (Codf, Nomf) qui ont fourni des produits commandés par les clients de Tunis
31. Donner le nombre de fournisseurs par produit.
32. Donner les clients (Codc, Nomc) qui ont commandé au moins un produit commandé par le client de code 100.
33. Quelles sont les commandes (Numc, Datec, Nbrp) contenant le plus grand nombre de produits (Nbrp étant le
nombre de produits).
34. Quelles sont les commandes les plus chères (Numc, Datec, Totalc) avec Totalc le montant de la commande.
35. Pour chaque client, donner le nombre de ses commandes et leurs sommes totales (Codc, Nomc, Nbc, Totc)
avec Nbc et Totc respectivement le nombre de commandes par client et le montant total des commandes par
client. On ne veut afficher que les clients qui ont au moins 3 commandes
36. Donner les clients (Codc, Nomc, Ville) qui ont commandé tous les produits.
37. Donner les produits (Ref, Libp) commandés par tous les clients.
38. Donner les fournisseurs (Codf, Nomf) qui fournissent des produits qui ne sont pas commandés par des clients
de Sousse.
39. Donner le détail des produits (Ref, Libp, PU) qui sont fournis par plus d’un fournisseur.
40. Donner la moyenne des prix fournisseur pour chaque produit.
