/* =============================================================================
SCRIPT SQL SERVER : BASE DE DONNÉES GESCOM
Description : Création, Insertion et Requêtes d'analyse
Version : 1.0
================================================================================
*/
-- 1. Création de la base de données
IF NOT EXISTS (SELECT * FROM [Link] WHERE name = 'GESCOM')
BEGIN
CREATE DATABASE GESCOM;
END
GO
USE GESCOM;
GO
-- ---------------------------------------------------------
-- 1. CRÉATION DES TABLES
-- ---------------------------------------------------------
-- Table CATEGORIES
CREATE TABLE CATEGORIES (
id_categorie INT PRIMARY KEY,
nom_categorie VARCHAR(100) NOT NULL
);
-- Table PRODUITS
CREATE TABLE PRODUITS (
id_produit INT PRIMARY KEY,
nom_produit VARCHAR(150) NOT NULL,
prix DECIMAL(10, 2),
stock INT,
id_categorie INT
);
-- Table EMPLOYES
CREATE TABLE EMPLOYES (
id_employe INT PRIMARY KEY,
nom VARCHAR(100),
prenom VARCHAR(100),
poste VARCHAR(100),
1
salaire DECIMAL(10, 2)
);
-- Table CLIENTS
CREATE TABLE CLIENTS (
id_client INT PRIMARY KEY,
nom_client VARCHAR(150),
telephone VARCHAR(20)
);
-- Table VENTES
CREATE TABLE VENTES (
id_vente INT PRIMARY KEY,
date_vente DATE,
id_client INT,
id_employe INT,
total DECIMAL(10, 2)
);
-- Table DETAIL_VENTE
CREATE TABLE DETAIL_VENTE (
id_detail INT PRIMARY KEY,
id_vente INT,
id_produit INT,
quantite INT,
prix_unitaire DECIMAL(10, 2)
);
GO
-- ---------------------------------------------------------
-- 2. AJOUT DES CLÉS ÉTRANGÈRES
-- ---------------------------------------------------------
-- Relation PRODUITS -> CATEGORIES
ALTER TABLE PRODUITS
ADD CONSTRAINT FK_PRODUITS_CATEGORIES
FOREIGN KEY (id_categorie) REFERENCES CATEGORIES(id_categorie);
-- Relations VENTES -> CLIENTS et EMPLOYES
ALTER TABLE VENTES
ADD CONSTRAINT FK_VENTES_CLIENTS
FOREIGN KEY (id_client) REFERENCES CLIENTS(id_client);
2
ALTER TABLE VENTES
ADD CONSTRAINT FK_VENTES_EMPLOYES
FOREIGN KEY (id_employe) REFERENCES EMPLOYES(id_employe);
-- Relations DETAIL_VENTE -> VENTES et PRODUITS
ALTER TABLE DETAIL_VENTE
ADD CONSTRAINT FK_DETAIL_VENTE_VENTES
FOREIGN KEY (id_vente) REFERENCES VENTES(id_vente);
ALTER TABLE DETAIL_VENTE
ADD CONSTRAINT FK_DETAIL_VENTE_PRODUITS
FOREIGN KEY (id_produit) REFERENCES PRODUITS(id_produit);
GO
-- ---------------------
-- INSERTION DES DONNÉES
-- ---------------------
-- Insertion CATEGORIES
INSERT INTO CATEGORIES (id_categorie, nom_categorie) VALUES
(1, 'Boissons'), (2, 'Produits laitiers'), (3, 'Céréales'), (4, 'Conserves'), (5, 'Fruits'),
(6, 'Légumes'), (7, 'Viandes'), (8, 'Poissons'), (9, 'Boulangerie'), (10, 'Confiserie'),
(11, 'Épicerie'), (12, 'Surgelés'), (13, 'Produits ménagers'), (14, 'Hygiène'), (15, 'Bébé');
-- Insertion PRODUITS
INSERT INTO PRODUITS (id_produit, nom_produit, prix, stock, id_categorie) VALUES
(1, 'Coca-Cola 50cl', 600, 120, 1), (2, 'Fanta Orange', 500, 90, 1),
(3, 'Lait Peak', 1200, 40, 2), (4, 'Yaourt Dolait', 300, 60, 2),
(5, 'Riz parfumé', 1500, 200, 3), (6, 'Spaghetti', 900, 180, 3),
(7, 'Sardine Titus', 700, 150, 4), (8, 'Thon Nature', 1100, 70, 4),
(9, 'Banane', 100, 300, 5), (10, 'Orange', 150, 250, 5),
(11, 'Tomate', 200, 220, 6), (12, 'Carotte', 250, 180, 6),
(13, 'Poulet entier', 3500, 40, 7), (14, 'Bœuf', 3000, 35, 7),
(15, 'Poisson braisé', 2500, 30, 8);
-- Insertion EMPLOYES
INSERT INTO EMPLOYES (id_employe, nom, prenom, poste, salaire) VALUES
(1, 'Tchouta', 'Wilfried', 'Gérant', 250000), (2, 'Ndzié', 'Serge', 'Caissier', 120000),
(3, 'Mbarga', 'Alain', 'Caissier', 120000), (4, 'Essomba', 'Mireille', 'Vendeuse', 110000),
(5, 'Ndzié', 'Chantal', 'Vendeuse', 110000), (6, 'Fokou', 'Junior', 'Magasinier', 130000),
(7, 'Ndzié', 'Pascal', 'Sécurité', 100000), (8, 'Ngassa', 'Brice', 'Sécurité', 100000),
3
(9, 'Nkoum', 'Eric', 'Caissier', 120000), (10, 'Ndzié', 'Arlette', 'Vendeuse', 110000),
(11, 'Ndzié', 'Christian', 'Vendeur', 110000), (12, 'Ndzié', 'Patrick', 'Magasinier', 130000),
(13, 'Ndzié', 'Sandrine', 'Caissière', 120000), (14, 'Ndzié', 'Mireille', 'Caissière', 120000),
(15, 'Ndzié', 'Joel', 'Sécurité', 100000);
-- Insertion CLIENTS
INSERT INTO CLIENTS (id_client, nom_client, telephone) VALUES
(1, 'Jean Mbiya', '690000001'), (2, 'Paul Ndzié', '690000002'), (3, 'Marie Essomba', '690000003'),
(4, 'Alain Mbarga', '690000004'), (5, 'Sophie Ndzié', '690000005'), (6, 'Eric Ndzié', '690000006'),
(7, 'Claude Ndzié', '690000007'), (8, 'Sandrine Ndzié', '690000008'), (9, 'Kevin Ndzié', '690000009'),
(10, 'Ruth Ndzié', '690000010'), (11, 'Brice Ngassa', '690000011'), (12, 'Mireille Essomba', '690000012'),
(13, 'Patrick Ndzié', '690000013'), (14, 'Franck Ndzié', '690000014'), (15, 'Laure Ndzié', '690000015');
-- Insertion VENTES
INSERT INTO VENTES (id_vente, date_vente, id_client, id_employe, total) VALUES
(1, '2025-01-01', 1, 2, 5000), (2, '2025-01-01', 2, 3, 3000),
(3, '2025-01-02', 3, 2, 4500), (4, '2025-01-02', 4, 4, 6000),
(5, '2025-01-03', 5, 5, 2500), (6, '2025-01-03', 6, 6, 8000),
(7, '2025-01-04', 7, 7, 2000), (8, '2025-01-04', 8, 8, 3500),
(9, '2025-01-05', 9, 9, 9000), (10, '2025-01-05', 10, 10, 4000),
(11, '2025-01-06', 11, 11, 3000), (12, '2025-01-06', 12, 12, 7500),
(13, '2025-01-07', 13, 13, 5000), (14, '2025-01-07', 14, 14, 6000),
(15, '2025-01-08', 15, 15, 2000);
-- Insertion DETAIL_VENTE
INSERT INTO DETAIL_VENTE (id_detail, id_vente, id_produit, quantite, prix_unitaire) VALUES
(1, 1, 1, 3, 600), (2, 1, 5, 2, 1500), (3, 2, 3, 1, 1200), (4, 2, 9, 5, 100),
(5, 3, 7, 2, 700), (6, 3, 10, 3, 150), (7, 4, 13, 1, 3500), (8, 4, 11, 5, 200),
(9, 5, 14, 4, 300), (10, 6, 14, 2, 3000), (11, 7, 10, 10, 50), (12, 8, 2, 3, 500),
(13, 9, 15, 2, 2500), (14, 10, 6, 3, 900), (15, 11, 11, 4, 200);
GO
-- ---------
-- SOLUTIONS
-- ---------
-- 3. Requête qui affiche la liste des produits avec leur catégorie.
SELECT P.nom_produit, C.nom_categorie
FROM PRODUITS P
JOIN CATEGORIES C ON P.id_categorie = C.id_categorie;
GO
4
-- 4. Afficher tous les produits dont le stock est inférieur à une valeur (ex: 50).
DECLARE @SeuilStock INT = 50;
SELECT * FROM PRODUITS WHERE stock < @SeuilStock;
GO
-- 5. Afficher le nombre total de produits par catégorie.
SELECT C.nom_categorie, COUNT(P.id_produit) AS total_produits
FROM CATEGORIES C
LEFT JOIN PRODUITS P ON C.id_categorie = P.id_categorie
GROUP BY C.nom_categorie;
GO
-- 6. Afficher la liste des ventes avec le nom du client et le nom de l'employé.
SELECT V.id_vente, V.date_vente, CL.nom_client, [Link] AS nom_employe, [Link]
FROM VENTES V
JOIN CLIENTS CL ON V.id_client = CL.id_client
JOIN EMPLOYES E ON V.id_employe = E.id_employe;
GO
-- 7. Afficher le total des ventes réalisées par chaque employé.
SELECT [Link], [Link], SUM([Link]) AS chiffre_affaire_employe
FROM EMPLOYES E
LEFT JOIN VENTES V ON E.id_employe = V.id_employe
GROUP BY E.id_employe, [Link], [Link];
GO
-- 8. Afficher les clients qui ont effectué plus d’une vente.
SELECT CL.nom_client, COUNT(V.id_vente) AS nb_ventes
FROM CLIENTS CL
JOIN VENTES V ON CL.id_client = V.id_client
GROUP BY CL.id_client, CL.nom_client
HAVING COUNT(V.id_vente) > 1;
GO
-- 9. Afficher les produits les plus vendus (en quantité).
SELECT TOP 5 P.nom_produit, SUM([Link]) AS quantite_totale
FROM PRODUITS P
JOIN DETAIL_VENTE DV ON P.id_produit = DV.id_produit
GROUP BY P.id_produit, P.nom_produit
ORDER BY quantite_totale DESC;
GO
5
-- 10. Procédure stockée : afficher les ventes d'un client donné.
CREATE PROCEDURE sp_VentesParClient
@idClient INT
AS
BEGIN
SELECT * FROM VENTES WHERE id_client = @idClient;
END;
GO
-- Test: EXEC sp_VentesParClient 1;
-- 11. Procédure stockée : calculer le chiffre d'affaires entre deux dates.
CREATE PROCEDURE sp_ChiffreAffaireDates
@DateDebut DATE,
@DateFin DATE
AS
BEGIN
SELECT SUM(total) AS chiffre_affaires_total
FROM VENTES
WHERE date_vente BETWEEN @DateDebut AND @DateFin;
END;
GO
-- Test: EXEC sp_ChiffreAffaireDates '2025-01-01', '2025-01-05';
-- 12. Créer une vue qui affiche les détails complets d'une vente.
CREATE VIEW vw_DetailsVentesComplets AS
SELECT
V.id_vente,
V.date_vente,
CL.nom_client,
[Link] AS nom_vendeur,
P.nom_produit,
[Link],
DV.prix_unitaire,
([Link] * DV.prix_unitaire) AS sous_total
FROM VENTES V
JOIN CLIENTS CL ON V.id_client = CL.id_client
JOIN EMPLOYES E ON V.id_employe = E.id_employe
JOIN DETAIL_VENTE DV ON V.id_vente = DV.id_vente
JOIN PRODUITS P ON DV.id_produit = P.id_produit;
GO
-- 13. Créer un utilisateur Peter (au niveau serveur puis base).
6
-- Note : Ces commandes nécessitent des droits SysAdmin.
CREATE LOGIN Peter WITH PASSWORD = 'Password123!';
CREATE USER Peter FOR LOGIN Peter;
GO
-- 14. Attribuer le rôle lecture seule à Peter.
ALTER ROLE db_datareader ADD MEMBER Peter;
-- Pour un rôle admin, on utiliserait : ALTER ROLE db_owner ADD MEMBER Peter;
GO
-- 15. Requête qui affiche les employés n'ayant effectué aucune vente.
SELECT [Link], [Link], [Link]
FROM EMPLOYES E
LEFT JOIN VENTES V ON E.id_employe = V.id_employe
WHERE V.id_vente IS NULL;
GO