Module : Gestion Web des Bases de Données pour l'IoT
Travaux Pratiques Langage SQL
Objectifs :
• Se familiariser avec le langage SQL ;
• Créer une base de données avec ses différentes tables;
• Exécuter des requêtes simple et imbriquées de SQL.
Instructions :
– Les TPs peuvent se faire maximum par deux personnes (un binôme) ;
– À la fin de TP il faut fournir un rapport contenant les requêtes ainsi que des images
des résultats obtenus, et une interprétation de ces résultats.
Soit une base de données contenant les tables suivantes :
PRODUIT ( NP, LIBP, COUL, POIDS, PU, QTES)
– NP : numéro de produit de type entier,
– LIBP : nom de produite de type texte,
– COUL : Couleur du produit,
– POIDS : poids du produit,
– PU : prix unitaire,
– QTES : quantité disponible du produit.
CLIENT (NCL, NOMCL, ADRCL)
– NCL: Numéro de client,
– NOMCL: Nom du client,
– ADRCL : Adresse du client.
COMMANDE( NCMD, DATECMD,NCL)
– NCMD : Numéro de commande,
– DATECMD : date de la commande,
– NCL : numéro de client
LIGNE_CMD(#NCMD, #NP,QTE)
– NCMD : numéro de la commande,
– NP : numéro de produit,
– QTE : quantité
La base de données contient les éléments suivant :
1 Pr [Link]
Module : Gestion Web des Bases de Données pour l'IoT
PRODUIT COMMANDE
NP LIBP COUL POIDS PU QTES NCMD DATECMD NCL
1 Robinet Gris 5 18 1200 C001 10/01/2011 CL02
2 Prise Blanc 1,2 15 1000 C002 13/02/2017 CL05
3 Cable Blanc 2 25 1500 C003 15/08/2019 CL03
4 Peinture Blanc 25 330 900 C004 21/03/2020 CL05
5 Poignée Gris 3 12 1300 C005 19/04/2020 CL03
6 Serrure Jaune 2 47 1250
7 Verrou Gris 1,7 5,5 2000
8 Fer Noir 50 90 800
CLIENT LIGNE _CMD
NCL NOMCL ADRCL NCMD NP QTE
CL01 Erradi Tanger C001 1 250
CL02 Sbai Kenitra C001 4 300
CL03 Raouf Casablanca C001 6 100
CL04 Kamil Rabat C002 2 200
CL05 Raouf Marrakech C002 7 550
CL06 Elkohen Safi C003 1 50
CL07 Mamoun Marrakech C004 2 100
CL08 Samir Casablanca C004 4 150
CL09 Diouri Agadir C004 5 70
CL10 Essaid Oujda C004 8 90
CL11 Berrada C005 1 650
C005 2 100
2 Pr [Link]
Module : Gestion Web des Bases de Données pour l'IoT
À l’aide des requêtes SQL :
I. Définitions et insertion :
1. Créer toute les tables de la base de données.
a. NB : Lors de la création il faut prendre en considération les contraintes.
2. Alimenter ces tables par les éléments sur la page précédente.
II. Interrogation d'une seule table
1. Afficher les dates des commandes du client Numéro 3.
2. Afficher le numéro et le nom des clients de la ville de Oujda.
3. Afficher le numéro et le nom des clients de la ville de Zagora.
4. Afficher la liste des commandes commandées à partir de l’année 2019.
5. Afficher la liste des produits dont leur quantité commandée est supérieure à 250.
6. Afficher la liste des produits dont le prix unitaire est compris entre 20 et 50 avec
deux opérateurs différents.
7. Afficher les noms des clients qui commencent avec la lettre ‘E’.
8. Afficher les noms des clients ayant le caractère "M" en 3e position.
9. Afficher les noms des clients qui n’ont pas une adresse fixe (non renseignée).
10. Afficher les noms des clients triés par ordre alphabétique décroissant.
11. Afficher les noms et les prix de tous les produits en commençant par le produit
le plus cher.
12. Afficher la quantité en stock des produits.
13. Afficher le nom et poids total pour chaque produit.
14. Afficher le chiffre d’affaires.
15. Afficher la moyenne des prix unitaires des produits.
16. Afficher le nombre de commandes par client.
III. Jointures Internes
1. Afficher les noms et les adresses des clients ayant passé une commande entre le
01/01/2020 et le 31/12/2020.
2. Afficher les noms et les prix unitaires des produits commandés par le client
numéro 2.
3 Pr S Belkhala
Module : Gestion Web des Bases de Données pour l'IoT
3. Afficher les noms des clients qui sont de la même ville que Samir.
4. Afficher les noms et les adresses des clients ayant commandé le produit le plus
cher.
5. Afficher les noms des produits dont le prix unitaire dépasse la moyenne des prix
de tous les produits.
6. Afficher les numéros et les noms des clients de la ville de Rabat ou de Safi.
7. Afficher les numéros, les noms et les prix unitaires des produits de la commande
C004.
8. Afficher l’ensemble des produits communs aux commandes C001 et C005.
IV. Jointures Externes
1. Afficher les noms des clients qui n’ont passé aucune commande.
2. Afficher la liste de tous les produits qui n’ont pas été commandés.
3. Afficher la liste de tous les clients et, s’ils en ont, les dates de leurs commandes.
4. Afficher la liste des produits et, s’ils ont été commandés, la quantité totale
commandée pour chacun.
5. Afficher les noms et adresses des clients ayant passé au moins une commande.
6. Afficher la liste des commandes et, pour chaque commande, le nom du client.
7. Afficher la liste de tous les produits et, s’ils en ont, le nom des clients qui les ont
commandés.
V. Requêtes Imbriquées
1. Afficher la liste des clients qui ont au moins deux commandes.
2. Afficher la liste des clients qui ont au moins deux commandes en affichant les
informations des clients.
3. Afficher la liste des produits commandés plus de deux fois.
4. Afficher la liste des produits commandés plus de deux fois en affichant les
informations des clients.
5. Afficher le nombre de produits commandés ainsi que le total des quantités
commandées de chaque commande, trié par ordre décroissant du nombre de
produits et croissant de la quantité totale.
4 Pr S Belkhala
Module : Gestion Web des Bases de Données pour l'IoT
6. Afficher les produits commandés par le client CL05 avec un prix
supérieur à celui du produit "Poignée".
7. Afficher les noms des produits dont la somme des quantités commandées est
inférieure à leur quantité en stock.
8. Afficher le nom du produit le plus commandé (le plus demandé).
9. Afficher les numéros des commandes ayant une date inférieure à toutes les
commandes du client Raouf.
10. Liste des commandes ayant une date supérieure à au moins une des commandes
du client CL05.
5 Pr S Belkhala