ESCEP
******
Niveau : LPTI 3 Enseignant : ALMOU Bassirou
TD 2 : Gestion de ventes
Colonne Explication Type(taille) Contrainte
idc Identifiant du client Serial Primary key
nom Nom du client Varchar(50) Not null
tel Numéro de téléphone du client Varchar(20) Not null unique
ncom Identifiant de la commande Varchar(50) Not null
jour Jour de la commande Timestamp Default current_timestamp
montant Montant total de la commande Numeric(7) Not null , montant>=200
echeance Date d’échéance du versement du montant restant de date
la commande
idlc Identifiant de la ligne de commande Serial Primary key
qte Quantité commandée Numeric(7) Not null , qte>0
idp Prix unitaire du produit commande Serial Primary key
prd Nom du produit Varchar(50) Not null unique
prix Prix du produit Numeric(6) Not null. prix>=200
idcat Identifiant de la catégorie Serial Primary key
categorie Le nom de la catégorie du produit Varchar(50) Not null unique
jourl Jour de livraison Timestamp Defaut current_timestamp
qtel Quantité du produit livrée Numeric(7) Not null, qtel>0
prixl Prix du produit livré par le fournisseur Numeric(7) Not null, prixl >=200
idl Identifiant de la livraison Serial Primary key
jourl Jour de la livraison Date Not null
idf Identifiant du fournisseur Serial Primary key
nomf Nom du fournisseur Varchar(50) Not null
telf Numéro de téléphone du fournisseur Varchar(20) Not null unique
nif Nif du fournisseur Varchar(12) Not null unique
jourv Jour du versement du montant restant de la Date Not null
commande
montantv Montant versé Numeric(7) Not null
ids Identifiant de la succursale Serial Primary key
succursale Nom de la succursale Varchar(50) Not null
sitgeo Situation géographique de la succursale Varchar(150) Not null
region Région ou se trouve la succursale Varchar(50) Not null
idb Identifiant du bon de commande Serial Primary key
jourb Jour de la soumission du bon de commande Date Default current_date
idlignebon Identifiant du bon de commande Serial Primary key
qteb Quanite du produit du bon de commande Numeric(5) Not null
1
Travail demande
I. Langage de Définition de Données
1) Créer l’utilisateur administrateur avec les paramètres suivants :
- Mot de passe de votre choix
- Peut créer la base de données
- Compte valide jusqu’au 31 décembre 2030
2) Créer une base de données business dont le propriétaire est l’utilisateur administrateur
3) Créer un schéma vente dont le propriétaire est l’utilisateur administrateur
4) Créer les tables issues du modelé ci-dessous dans le schéma vente tout en respectant les contraintes :
clefs primaires (PRIMARY KEY), clefs étrangères (FOREIGN KEY), l’unicité de valeur (UNIQUE),
vérification (CHECK)
II. Langage de Manipulation de données
5) Donner l’instruction qui permet de charger les données du fichier [Link] dans la table
succursale
6) Donner l’instruction qui permet de copier le contenue de la table commande dans le fichier
[Link] avec séparateur ‘ ;’
7) Créer une fonction qui permet d’insérer les données dans la table client
8) Créer une fonction qui retourne le nombre de commande par jour pour un mois donné
9) Créer une fonction qui modifie l’enregistrement d’un fournisseur
10) Créer une fonction qui supprime une ligne de commande
11) Créer une procédure qui ajoute une livraison
12) Créer une procédure qui modifie l’enregistrement du produit
13) Créer une procédure qui afficher les 15 premières commandes
III. Langage d’Interrogation de Données
14) Donner les produits dont le prix dépasse 150
15) Le nombre de produits dont le prix dépasse 150
16) Donner les produits dont le prix est compris entre 50 et 150
17) Chercher le nombre de produit par tranche de prix :
2
[35-45]
[46-60]
[61-85]
[86-100]
[101-200]
[201-300]
[autre]
18) Donner l’instruction qui permet de copier le résultat de la requête précédente dans le fichier
[Link] avec séparateur ‘ ;’
19) Donner les produits de la catégorie dont idcat est égale à 1
20) Donner le nombre de commandes par mois en 2020
21) Donner le nombre de commandes par semaine au mois de janvier 2020
22) Donner le nombre de commandes par an
23) Donner le fournisseur dont le produit est le moins vendu en 2020
24) Donner les produits
25) Donner les trois produits les plus livrés en 2021
26) Donner les cinq produits les moins livrés en 2021
27) Donner le pourcentage de nombre de produits vendus au deuxième trimestre de l’année 2020
28) Donner la liste des dix premiers clients ayant des numéros Niger Télécoms
29) Donner le nombre de produits par catégorie et trier le résultat par ordre croissant du nom de la catégorie
30) Donner les commandes dont le montant total dépasse 1500 au mois de janvier 2017
31) Donner le chiffre d’administrateurs réalisé au dernier trimestre de l’année 2016
32) Donner les trois (3) produits les moins vendus au dernier trimestre de l’année 2016
33) Donner les dix (10) produits les plus vendus au premier trimestre de l’année 2017
34) Donner la quantité restant en stock de chaque produit
35) Donner le client qui a généré le plus de chiffre d’administrateurs au premier trimestre de l’année 2017
36) Donner les détails de la commande qui a généré le plus grand chiffre d’administrateurs
37) Lister les produits qui vont périmer dans 30 jours
38) Déterminer le montant total des produits déjà périmés
39) Déterminer le chiffre d’administrateurs réalisé par chaque client au premier trimestre de l’année 2017
40) Calculer la remise pour chaque client : si le montant est >= 1000 alors faire une remise de 7% ; si le
montant >= 7000 et <10000 alors faire une remise de 4%, si le montant <7000 et >=5000 alors faire une
remise de 2%.
41) Calculer le montant TTC du chiffre d’administrateurs réalisé par chaque client sachant que TVA=19%
42) Afficher le montant TTC minimal et le montant maximal générés par chaque produit
43) Si le montant TTC est >= 10000 le client donne 70% du montant TTC et le reste est payé dans 30 jours
et si le montant est compris entre 7000 et <10000 alors le client donne 85% du montant TTC et le reste
est payé dans 30 jours. Donner le reste à payer dans 30 jours
44) Déterminer le montant total restant à payer dans les 30 jours pour tous les clients
45) Déterminer le montant total restant à payer dans les 30 jours pour chaque client
46) A partir du résultat de la requête de la question numéro 6, déduire une vue nommée BUSINESS, que
vous importez les données dans EXCEL. Cela permettra aux utilisateurs d’avoir accès sur le réseau, aux
données dans un classeur EXCEL
47) Déterminer le montant TTC généré par chaque produit
48) L’entreprise souhaite faire une réduction de 5% au prochain achat, aux clients qui ont généré un chiffre
d’administrateurs de plus de 2000. Pour cela, déterminer la liste de ces clients à l’aide d’une vue
nommée VUEClient. Puis, écrire une requête qui faire une réduction de 5% aux clients qui sont dans la
vue VUEClient au prochain achat.
49) Donner le chiffre d’administrateurs généré par chaque succursale au premier trimestre de l’année 2017
3
50) Donner le pourcentage du Chiffre d’administrateurs généré par chaque succursale par rapport au
montant total HT
51) Déterminer la moyenne du montant TTC généré pour chaque produit
52) Déterminer le nombre d’achat réalisé par chaque client durant le mois de janvier 2017
Déterminer la quantité restant en stock par produit (somme de quantité livrée – somme de quantité commandée)
53) Donner la liste des clients qui ont réglé plus de 70% de leurs dettes
54) Donner la liste des clients qui n’ont pas réglé leurs dettes trente (30) jours après la date de la commande
55) Donner un bonus de 1000 à tout client ayant réglé sa dette avant la date prévue, c’est-à-dire 30 jours
après la date la commande
56) Donner la liste des produits qui n’ont pas été achetés depuis la date de leur livraison
57) Donner la liste des fournisseurs dont les produits n’ont pas été achetés depuis la date de leur livraison
58) Créer une vue nommée AFFICHE70 qui afficher la liste des clients qui ont réglé plus de 70% de leurs
dettes
59) Une semaine après, le Directeur des Etudes vous demande de lui envoyer le script qui a permis à la
création de la vue AFFICHE70
60) Exporter la liste des produits qui vont périmer dans 30 jours dans une table nommée ‘peremption30jrs’
III. Langage de contrôle de Données
51) créer le groupe agentsuccusale avec les options suivantes :
- mot de passe de votre choix
- valide au 30 mars 2026
- crypter le mot de passe
52) créer les utilisateurs moussa, rabiou, salissou, hamissou, laouali, tassiou, halima, amina avec les mots de passe
de vos choix
53) créer les rôles suivants :
role Table Privilèges
controleur Commnde, lignecommande,profuit Select
superviseur Livraison. Lignelivraison, boncommande, ligneboncommande Insert, select, update
caissier Coommande, versement Insert, select
vendeur Commande, lignecommande, succursale, produit, versement, categorie Select, insert
comptable versement Select, insert
financier Livraison, lignelivraisom, fournisseur Select, insert
client Commnde, lignecommande,profuit Select, insert
54) créer les groupes gcontroleur,gsuperviseur,gcaissier,gvendeur,gcomptable,gfinancier,gclient et leur attribuer les
rôles respectivement
56) ajouter les utilisateurs moussa, rabiou, salissou dans le groupe gvendeur. Ajouter les utilisateurs salissou,
hamissou, laouali dans le gfinancier. Ajouter les utilisateurs tassiou, halima, amina dans le groupe gcaissier
IV. Sauvegarde et restauration de données
V. Audit de base de données
VI. Automatisation
VII. Conteneurisation
VIII. Sécurité