TP1 : FAMILIARISATION AVEC LE LANGAGE SQL
Soit une base de données relationnelle suivante
Nom de la base : gestion_commande
Les différentes entités et attributs
Produit (NP, LibP, Coul, Poids, PU, Qtes) - Désigne l’ensemble des produits.
Client (NCl, NomCl, AdrCl) - Désigne l’ensemble des clients.
Commande (NCmd, DateCmd, #NCl) - Désigne l’ensemble des commandes.
Ligne_Cmd (#NCmd, #NP, Qte) - Désigne l’ensemble des lignes de commandes
1. Créer la structure de cette base de données
CREATE DATABASE gestion_commande ;
2. Pour la table Client
CREATE TABLE Client(
NCl VARCHAR(4) NOT NULL UNIQUE,
Nom Cl VARCHAR(15),
AdrCl VARCHAR(50);
3. Pour la table Produit définir NP en tant que clé primaire. Ne pas définir l’attribut Qtes ceci sera défini
ultérieurement par l’instruction ALTER TABLE.
CREATE TABLE Produit(
NP VARCHAR(4) PRIMARY KEY,
LibP VARCHAR(15),
Coul VARCHAR(10),
Poids DECIMAL(6,3),
PU DECIMAL(6,3));
4. Pour la table Commande ne définit pas la contrainte ni de propriétés sur la clé, ceci seradéfini
ultérieurement par l’instruction ALTER TABLE.
CREATE TABLE Commande(
NCmd VARCHAR(4),
DateCmd DATE,
NCl VARCHAR(4));
5. Pour la table Ligne_Cmd spécifier la contrainte de clé primaire et l’une des contraintes de clé étrangère.
CREATE TABLE Ligne_Cmd(
NCmd VARCHAR(4),
NP VARCHAR(4),
Qte NUMBER(5),
PRIMARY KEY (NCmd, NP),
FOREIGN KEY (NP) REFERENCES Produit(NP));
6. Ajouter l’attribut Qtes à la table Produit.
ALTER TABLE Produit
ADD Qtes DECIMAL(5);
7. Modifier la taille de l’attribut LibP à VARCHAR(20).
ALTER TABLE Produit
MODIFY LibP VARCHAR(20);
8. Ajouter la contrainte de PRIMARY KEY sur l’attribut NCmd de la table Commande.
ALTER TABLE Commande
ADD PRIMARY KEY (NCmd);
9. Ajout er les contraintes de clés étrangères sur la table Commande.
ALTER TABLE Commande
ADD FOREIGN KEY (NCl) REFERENCES Client(NCl);
10. Ajouter les contraintes de clés étrangères sur la table Ligne_Cmd.
ALTER TABLE Ligne_Cmd
ADD FOREIGN KEY (NCmd) REFERENCES Commande(NCmd);
11. Ajouter la contrainte CHECK sur l’attribut Qte (Qte> 0) de la table Ligne_Cmd.
ALTER TABLE Ligne_Cmd
ADD CHECK (Qte> 0);
12. Remplir les tables client, produit, commande et Ligne_cmd
« Remplissage de la table Client. »
INSERT INTO Client VALUES(‘CL01’,’BATAM’,’Tunis’);
INSERT INTO Client VALUES(‘CL02’,’BATIMENT’,’Tunis’);
INSERT INTO Client VALUES(‘CL03’,’AMS’,’Sousse’);
INSERT INTO Client VALUES(‘CL04’,’GLOULOU’,’Sousse’);
INSERT INTO Client VALUES(‘CL05’,’PRODELEC’,’Tunis’);
INSERT INTO Client VALUES(‘CL06’,’ELECTRON’,’Sousse’);
INSERT INTO Client VALUES(‘CL07’,’SBATIM’,’Sousse’);
INSERT INTO Client VALUES(‘CL08’,’SANITAIRE’,’Tunis’);
INSERT INTO Client VALUES(‘CL09’,’SOUDURE’,’Tunis’);
INSERT INTO Client VALUES(‘CL10’,’MELEC’,’Monastir’);
INSERT INTO Client VALUES(‘CL11’,’MBATIM’,’’);
INSERT INTO Client VALUES(‘CL12’,’BATFER’,’Tunis’);
« Remplissage de la table Produit. »
INSERT INTO Produit VALUES(‘P001’,’Robinet’,’Gris’,5,18,1200);
INSERT INTO Produit VALUES(‘P002’,’Prise’,’Blanc’,1.2,1.5,1000);
INSERT INTO Produit VALUES(‘P003’,’Câble’,’Blanc’,2,25,1500);
INSERT INTO Produit VALUES(‘P004’,’Peinture’,’Blanc’,25,33,900);
INSERT INTO Produit VALUES(‘P005’,’Poignée’,’Gris’,3,12,1300);
INSERT INTO Produit VALUES(‘P006’,’Serrure’,’Jaune’,2,47,1250);
INSERT INTO Produit VALUES(‘P007’,’Verrou’,’Gris’,1.7,5.5,2000);
INSERT INTO Produit VALUES(‘P008’,’Fer’,’Noir’,50,90,800);
« Remplissage de la table Commande. »
INSERT INTO Commande VALUES(‘C001’, ‘10/12/2003’, ‘CL02’);
INSERT INTO Commande VALUES(‘C002’, ‘13/02/2004’, ‘CL05’);
INSERT INTO Commande VALUES(‘C003’, ‘15/01/2004’, ‘CL03’);
INSERT INTO Commande VALUES(‘C004’, ‘03/09/2003’, ’CL10’);
INSERT INTO Commande VALUES(‘C005’, ‘11/03/2004’, ’CL03’);
« Remplissage de la table Ligne_Cmd. »
INSERT INTO Ligne_CmdVALUES(‘C001’,’P001’,250);
INSERT INTO Ligne_CmdVALUES(‘C001’,’P004’,300);
INSERT INTO Ligne_CmdVALUES(‘C001’,’P006’,100);
INSERT INTO Ligne_CmdVALUES(‘C002’,’P002’,200);
INSERT INTO Ligne_CmdVALUES(‘C002’,’P007’,550);
INSERT INTO Ligne_CmdVALUES(‘C003’,’P001’,50);
INSERT INTO Ligne_CmdVALUES(‘C004’,’P002’,100);
INSERT INTO Ligne_CmdVALUES(‘C004’,’P004’,150);
INSERT INTO Ligne_CmdVALUES(‘C004’,’P005’,70);
INSERT INTO Ligne_CmdVALUES(‘C004’,’P008’,90);
INSERT INTO Ligne_CmdVALUES(‘C005’,’P001’,650);
INSERT INTO Ligne_CmdVALUES(‘C005’,’P002’,100);
13. Modifier le Poids du produit numéro P002 à 1
UPDATE Produit
SET Poids = 1
WHERE NP = ‘P002’;
14. Augmenter la quantité en stock des différents produits de 10%.
UPDATE Produit
SET Qtes = 1.1 * Qtes;
15. Afficher la liste des Clients
SELECT *
FROM Client ;
16. Lister l’ensemble des produits qui ont été commandé (NP)
SELECT NP
FROM Ligne_Cmd;
SELECT DISTINCT NP
FROM Ligne_Cmd;
17. Donner le numéro et le nom des clients de la ville Sousse
SELECT NCl, NomCl
FROM Client
WHERE AdrCl = ‘Sousse’;
18. Donner la liste des commandes dont la date est supérieure à ‘01/01/2004’.
SELECT *
FROM Commandes
WHERE DateCmd > ‘01/01/2004’;
19. Donner la liste des produits dont le prix est compris entre 20 et 50.
SELECT *
FROM Produit
WHERE PU BETWEEN 20 AND 50;
SELECT *
FROM Produit
WHERE (PU >= 20) AND (PU <= 50);
20. Donner la liste des clients dont les noms commencent par ‘B’.
SELECT *
FROM Client
WHERE NomCl LIKE ‘B%’;
21. Donner les numéros des clients dont les dates de leurs commandes se trouvent parmi les
datessuivantes : (‘10-12-03’, ‘10-12-04’,’13-02-04’,’11-03-04’)
SELECT DateCmd, NCl
FROM Commande
WHERE DateCmd IN (‘10-12-2003’,’10-12-2004’,’13-02-2004’,’11-03-2004’);
22. Donner les noms des clients qui n’ont pas d’adresse.
SELECT NomCl
FROM Client
WHERE AdrCl IS NULL;
23. Donner la valeur des produits en stock.
SELECT NP, (Qtes * PU) AS “Valeur Totale”
FROM Produit ;
SELECT NP, (Qtes* PU) “Valeur Totale”
FROM Produit ;
24. Donner la moyenne des prix unitaires des produits.
SELECT AVG(PU)
FROM Produit;
25. Donner les libellés des produits de la commande numéro ‘C002’.
SELECT DISTINCT LibP
FROM Produit, Ligne_Cmd
WHERE [Link] = Ligne_Cmd.NP
AND NCmd=‘C002’;
SELECT DISTINCT LibP
FROM Produit P, Ligne_Cmd L
WHERE [Link] = [Link]
AND NCmd=‘C002’;
26. Donner le nombre de commandes par client.
SELECT NCl, COUNT((NCmd) NbCmd
FROM Commande
GROUP BY NCl ;
27. Donner la quantité totale commandée par produit.
SELECT NP, SUM(Qte) Som
FROM Ligne_Cmd
GROUP BY NP;
28. Donner le nombre de produits par commande.
SELECT NCmd, COUNT(NP) NbProd
FROM Ligne_Cmd
GROUP BY NCmd;
29. Donner les commandes dont le nombre de produits dépasse 2.
SELECT NCmd, COUNT(NP) NbProd
FROM Ligne_Cmd
GROUP BY NCmd
HAVING COUNT(NP) > 2;
30. Donner le total des montants par commande.
SELECT NCmd, SUM(PU*Qte) TotMontant
FROM Produit P, Ligne_Cmd L
WHERE [Link] = [Link]
GROUP BY NCmd;
31. Donner les noms des clients suivant l’ordre décroissant.
SELECT NomCl
FROM Client
ORDER BY NomCl DESC ;
32. Donner le nombre de produits et la quantité totale par commande suivant l’ordre décroissant
dunombre de produits et l’ordre croissant de la quantité totale.
SELECT NCmd,
COUNT DISTINCT(NP) NbProd,
SUM(Qte) SomQte
FROM Ligne_Cmd
GROUP BY NCmd
ORDER BY NbProd DESC,
SomQte ASC ;
33. Donner les produits dont les prix unitaires dépassent la moyenne des prix.
SELECT *
FROM Produit
WHERE PU > (SELECT AVG(PU) FROM Produit);
34. Donner les commandes qui ont une date inférieure à chacune des commandes du client ‘CL03’.
SELECT *
FROM Commande
WHERE DateCmd< ALL
(SELECT DateCmd
FROM Commande
WHERE NCl = ‘CL03’);
35. Donner les commandes qui ont une date inférieure à au moins une des commandes du client‘CL03’.
SELECT *
FROM Commande
WHERE DateCmd< ANY
(SELECT DateCmd
FROM Commande
WHERE NCl = ‘CL03’);
36. Donner les clients qui ont passé au moins une commande
SELECT NomCl
FROM Client Cl
WHERE EXIST (SELECT * FROM Commande C WHERE [Link] =[Link]);
37. Donner des clients qui n’ont passé aucune commande.
SELECT NomCl
FROM Client Cl
WHERE NOT EXIST (SELECT * FROM Commande C WHERE [Link] =[Link]);
38. Donner l’ensemble des clients de Tunis et de Sousse qui ont des commandes.
SELECT [Link], NomCl, AdrCl
FROM Client Cl, Commande C
WHERE [Link]=[Link]
AND [Link]=‘Tunis’
UNION
SELECT [Link], NomCl, AdrCl
FROM Client Cl, Commande C
WHERE [Link]=[Link]
AND [Link]=‘Sousse’;
39. Donner l’ensemble des produits communs aux commandes C001 et C005.
SELECT [Link], LibP
FROM Produit P, Ligne_Cmd L
WHERE [Link]=[Link]
AND NCmd=‘C001’
INTERSECT
SELECT [Link], LibP
FROM Produit P, Ligne_Cmd L
WHERE [Link]=[Link]
AND NCmd=‘C005’ ;
40. Donner l’ensemble des produits qui n’ont pas été commandés.
SELECT NP, LibP
FROM Produit
MINUS
SELECT [Link], LibP
FROM Produit P, Ligne_Cmd L
WHERE [Link]=[Link];
MINUS = EXCEPT
41. Faire le produit cartésien entre la table Client et la table Produit.
SELECT *
FROM Client, Produit;
