TP4 : SQL
1/
USE BD_gestion_des_commandes;
-- Table Client
CREATE TABLE Client (
Cin VARCHAR(10) PRIMARY KEY,
Nom VARCHAR(50),
Prenom VARCHAR(50),
Ville VARCHAR(50),
Catg VARCHAR(2)
);
-- Table Commandes
CREATE TABLE Commandes (
Num_cmd INT PRIMARY KEY,
Cin VARCHAR(10),
Date DATE,
FOREIGN KEY (Cin) REFERENCES Client(Cin)
);
-- Table Produits
CREATE TABLE Produits (
Ref VARCHAR(10) PRIMARY KEY,
Desg VARCHAR(50),
Pu DECIMAL(10,2)
);
-- Table Lignes
CREATE TABLE Lignes (
Num_cmd INT,
Ref VARCHAR(10),
Quantite INT,
PRIMARY KEY (Num_cmd, Ref),
FOREIGN KEY (Num_cmd) REFERENCES Commandes(Num_cmd),
FOREIGN KEY (Ref) REFERENCES Produits(Ref)
);
2/
INSERT INTO Client VALUES
('A100', 'Amani', 'Mohamed', 'Casablanca', 'C1'),
('B200', 'Sellami', 'Amine', 'Rabat', 'C1'),
('C300', 'Asmour', 'Jalil', 'Agadir', 'C3'),
('D400', 'Tounsi', 'Mostafa', 'Casablanca', 'C2'),
('E500', 'Kalami', 'Samira', 'Fes', 'C3'),
('F600', 'Alami', 'Sanaa', 'Casablanca', 'C2'),
('G700', 'Mounir', 'Hamid', 'Casablanca', 'C1'),
('H800', 'amine', 'soufiane', 'rabat', 'C2');
INSERT INTO Commandes VALUES
(1, 'A100', '2004-01-01'),
(2, 'A100', '2004-02-01'),
(3, 'A100', '2004-03-01'),
(4, 'B200', '2004-04-15'),
(5, 'B200', '2004-05-15'),
(6, 'B200', '2004-06-15'),
(7, 'C300', '2004-07-10'),
(8, 'C300', '2004-09-25'),
(9, 'D400', '2004-10-10'),
(10, 'E500', '2004-10-25'),
(11, 'F600', '2004-11-11'),
(12, 'F600', '2004-12-12'),
(13, 'G700', '2004-12-15'),
(14, 'H800', '2004-12-25');
INSERT INTO Produits VALUES
('PRD01', 'Claviers', 250),
('PRD02', 'Ecrans', 700),
('PRD03', 'Souris', 100),
('PRD04', 'Haut-parleurs', 100),
('PRD05', 'Imprimantes', 1200);
INSERT INTO Lignes VALUES
(1, 'PRD01', 10),
(1, 'PRD02', 2),
(2, 'PRD02', 5),
(2, 'PRD03', 20),
(3, 'PRD04', 30),
(4, 'PRD04', 15),
(5, 'PRD04', 20),
(6, 'PRD05', 10),
(7, 'PRD05', 20),
(8, 'PRD01', 5),
(9, 'PRD01', 5),
(10, 'PRD01', 1),
(11, 'PRD02', 3),
(11, 'PRD04', 2),
(12, 'PRD03', 25),
(12, 'PRD05', 10),
(13, 'PRD05', 5),
(14, 'PRD03', 6);
3/
-- a. Clients de Casablanca et Rabat
SELECT * FROM Client
WHERE LOWER(Ville) IN ('casablanca', 'rabat');
-- b. Commandes avec quantité > 25
SELECT * FROM Lignes
WHERE Quantite > 25;
-- c. Lignes de commandes PRD03 avec quantité >= 20
SELECT * FROM Lignes
WHERE Ref = 'PRD03' AND Quantite >= 20;
-- d. Clients dont le nom commence par A
SELECT * FROM Client
WHERE Nom LIKE 'A%';
-- e. Liste triée des clients de Casablanca
SELECT * FROM Client
WHERE LOWER(Ville) IN ('casa', 'casablanca')
ORDER BY Nom DESC;
-- f. Clients avec conditions spécifiques
SELECT Cin, Nom, Prenom, Ville
FROM Client
WHERE Nom LIKE 'S%'
AND Prenom LIKE '%M%'
AND LOWER(Ville) IN ('casa', 'rabat');
-- g. Commandes de mars, mai ou juin 2008
SELECT Num_cmd, Date
FROM Commandes
WHERE YEAR(Date) = 2008
AND MONTH(Date) IN (3, 5, 6);
-- h. Clients de Casablanca avec commandes avant 2009
SELECT DISTINCT [Link], [Link]
FROM Client c
JOIN Commandes cmd ON [Link] = [Link]
WHERE LOWER([Link]) = 'casablanca'
AND YEAR([Link]) < 2009;
-- i. Clients C1 avec commandes > 20
SELECT DISTINCT [Link], [Link], [Link]
FROM Client c
JOIN Commandes cmd ON [Link] = [Link]
JOIN Lignes l ON cmd.Num_cmd = l.Num_cmd
WHERE [Link] = 'C1' AND [Link] > 20;
-- j. Clients de Casablanca avec produits > 240 DHS
SELECT DISTINCT [Link], [Link]
FROM Client c
JOIN Commandes cmd ON [Link] = [Link]
JOIN Lignes l ON cmd.Num_cmd = l.Num_cmd
JOIN Produits p ON [Link] = [Link]
WHERE LOWER([Link]) = 'casablanca' AND [Link] > 240;
-- k. Nombre total de clients
SELECT COUNT(*) as NombreClients
FROM Client;
-- l. Nombre de clients par ville
SELECT Ville, COUNT(*) as NombreClients
FROM Client
GROUP BY Ville;
-- m. Statistiques des commandes de décembre
SELECT
l.Num_cmd,
AVG([Link]) as MoyenneQuantite,
MAX([Link]) as QuantiteMax,
MIN([Link]) as QuantiteMin
FROM Lignes l
JOIN Commandes c ON l.Num_cmd = c.Num_cmd
WHERE MONTH([Link]) = 12
GROUP BY l.Num_cmd;
-- n. Montants des commandes avec TVA
SELECT
c.Num_cmd,
[Link],
SUM([Link] * [Link]) as MontantHT,
SUM([Link] * [Link] * 0.20) as TVA,
SUM([Link] * [Link] * 1.20) as MontantTTC
FROM Commandes c
JOIN Lignes l ON c.Num_cmd = l.Num_cmd
JOIN Produits p ON [Link] = [Link]
GROUP BY c.Num_cmd, [Link];
