Correction Examen de Base de Données

Youcef Bourouba

Solutions pour les 20 premières questions :

Sélectionnez le prénom et le nom de tous les employés :

1 SELECT p r n o m , nom FROM E m p l o y s ;
 

Affichez le nom de tous les départements :


1 SELECT n o m _ d p a r t e m e n t FROM D p a r t e m e n t s ;
 

Obtenez l’identifiant de chaque commande et son montant total :


1 SELECT order_id , montant_total FROM Commandes ;
 

Liste des noms de tous les produits avec leur prix :


1 SELECT nom_produit , prix FROM Produits ;
 

Sélectionnez les prénoms et noms des employés qui travaillent dans le département
”Ventes” :

1 SELECT e . p r n o m , e . nom
2 FROM Employ s e
3 JOIN D p a r t e m e n t s d ON e . department_id = d . department_id
4 WHERE d . n o m _ d p a r t e m e n t = ’ Ventes ’;
 

Trouvez le montant total des commandes pour chaque client :


1 SELECT customer_id , SUM ( montant_total ) AS total_commandes
2 FROM Commandes
3 GROUP BY customer_id ;
 

Affichez les noms des produits qui ont une quantité de stock inférieure à 10 :

1 SELECT nom_produit
2 FROM Produits
3 WHERE q u a n t i t _ s t o c k < 10;
 

1
 Sélectionnez les noms des départements avec le nombre d’employés dans
chaque département :

1 SELECT d . n o m _ d p a r t e m e n t , COUNT ( e . employee_id ) AS n o m b r e _ e m p l o y s
2 FROM D partements d
3 LEFT JOIN E m p l o y s e ON d . department_id = e . department_id
4 GROUP BY d . n o m _ d p a r t e m e n t ;
 

Obtenez les noms des clients qui ont passé des commandes pour plus de 1000
€:

1 SELECT customer_id
2 FROM Commandes
3 GROUP BY customer_id
4 HAVING SUM ( montant_total ) > 1000;
 

Liste des noms des produits commandés au cours du mois dernier :


1 SELECT DISTINCT p . nom_produit
2 FROM Produits p
3 JOIN Co mmande _Produ its cp ON p . product_id = cp . product_id
4 JOIN Commandes c ON cp . order_id = c . order_id
5 WHERE c . date_commande >= DATE_SUB ( CURDATE () , INTERVAL 1 MONTH ) ;
 

Sélectionnez les prénoms et noms des employés qui ont un salaire supérieur
à 5000 € :

1 SELECT p r n o m , nom
2 FROM Employ s
3 WHERE salaire > 5000;
 

Affichez les noms des produits avec leur prix triés par ordre décroissant de
prix :

1 SELECT nom_produit , prix
2 FROM Produits
3 ORDER BY prix DESC ;
 

Trouvez le montant total des commandes pour chaque mois de l’année :


1 SELECT DATE_FORMAT ( date_commande , ’%Y -% m ’) AS mois , SUM ( montant_total )
AS total_commandes
2 FROM Commandes
3 GROUP BY DATE_FORMAT ( date_commande , ’%Y -% m ’) ;
 

Sélectionnez les prénoms et noms des employés avec le nom de leur département
:

1 SELECT e . p r n o m , e . nom , d . n o m _ d p a r t e m e n t
2 FROM Employ s e
3 JOIN D p a r t e m e n t s d ON e . department_id = d . department_id ;
 

2
 Obtenez le nombre total de produits dans le stock pour chaque catégorie :

1 SELECT c . nom_categorie , SUM ( p . q u a n t i t _ s t o c k ) AS total_stock
2 FROM Produits p
3 JOIN Categories c ON p . category_id = c . category_id
4 GROUP BY c . nom_categorie ;
 

Liste des noms des clients avec le nombre de commandes passées par chacun
:

1 SELECT customer_id , COUNT ( order_id ) AS nombre_commandes
2 FROM Commandes
3 GROUP BY customer_id ;
 

Affichez les noms des produits avec leur prix pour la catégorie ”Électronique”
:

1 SELECT p . nom_produit , p . prix
2 FROM Produits p
3 JOIN Categories c ON p . category_id = c . category_id
4 WHERE c . nom_categorie = ’ lectronique ’;
 

Sélectionnez les prénoms et noms des employés avec le montant total des
commandes qu’ils ont gérées :

1 SELECT e . p r n o m , e . nom , SUM ( c . montant_total ) AS total_commandes
2 FROM Employ s e
3 JOIN Commandes c ON e . employee_id = c . employee_id
4 GROUP BY e . employee_id ;
 

Obtenez les noms des départements avec le nombre total d’employés et le

montant total de salaires pour chaque département :

1 SELECT d . n o m _ d p a r t e m e n t , COUNT ( e . employee_id ) AS n o m b r e _ e m p l o y s
, SUM ( e . salaire ) AS total_salaires
2 FROM D p a r t e m e n t s d
3 JOIN E m p l o y s e ON d . department_id = e . department_id
4 GROUP BY d . n o m _ d p a r t e m e n t ;
 

Liste des noms des produits commandés par le client avec l’identifiant ”12345”
:

1 SELECT p . nom_produit
2 FROM Produits p
3 JOIN Com mande_ Produ its cp ON p . product_id = cp . product_id
4 JOIN Commandes c ON cp . order_id = c . order_id
5 WHERE c . customer_id = 12345;
 

3
 Solutions pour les 5 questions supplémentaires :
Écrivez une requête pour trouver les clients qui ont passé des commandes
pour au moins cinq produits différents :

1 SELECT c . customer_id
2 FROM Commandes c
3 JOIN Com mande_ Produ its cp ON c . order_id = cp . order_id
4 GROUP BY c . customer_id
5 HAVING COUNT ( DISTINCT cp . product_id ) >= 5;
 

Sélectionnez les produits qui ont été commandés dans chaque mois de l’année
:

1 SELECT p . nom_produit , DATE_FORMAT ( c . date_commande , ’%Y -% m ’) AS mois
2 FROM Produits p
3 JOIN Com mande_ Produ its cp ON p . product_id = cp . product_id
4 JOIN Commandes c ON cp . order_id = c . order_id
5 GROUP BY p . nom_produit , DATE_FORMAT ( c . date_commande , ’%Y -% m ’) ;
 

Trouvez les employés qui ont travaillé dans plus de trois départements différents
au cours de leur carrière :

1 SELECT employee_id
2 FROM H i s t o r i q u e _ D e p a r t e m e n t s
3 GROUP BY employee_id
4 HAVING COUNT ( DISTINCT department_id ) > 3;
 

Affichez les clients dont le montant total des commandes dépasse la moyenne
de tous les clients :

1 SELECT customer_id
2 FROM Commandes
3 GROUP BY customer_id
4 HAVING SUM ( montant_total ) > (
5 SELECT AVG ( total_commandes )
6 FROM (
7 SELECT SUM ( montant_total ) AS total_commandes
8 FROM Commandes
9 GROUP BY customer_id
10 ) AS sous_requete
11 );
 

Sélectionnez les fournisseurs qui n’ont jamais fourni de produits dans une
catégorie donnée :

1 SELECT f . fournisseur_id
2 FROM Fournisseurs f
3 WHERE NOT EXISTS (
4 SELECT 1
5 FROM Produits p
6 JOIN Categories c ON p . category_id = c . category_id

4
 7 WHERE p . fournisseur_id = f . fournisseur_id
8 AND c . nom_categorie = ’ C a t g o r i e _ D o n n e ’
9 );
 

Solutions pour les procédures stockées :

Procédure d’augmentation de salaire :

1 DELIMITER //
2 CREATE PROCEDURE au gmente r_sala ire ( IN emp_id INT , IN pourcentage
DOUBLE , OUT nouveau_salaire DECIMAL (10 ,2) )
3 BEGIN
4 UPDATE E m p l o y s
5 SET salaire = salaire * (1 + pourcentage / 100)
6 WHERE employee_id = emp_id ;
7

8 SELECT salaire INTO nouveau_salaire

9 FROM E m p l o y s
10 WHERE employee_id = emp_id ;
11 END //
12 DELIMITER ;
 

Procédure de rapport de ventes :


1 DELIMITER //
2 CREATE PROCEDURE rapport_ventes ( IN date_debut DATE , IN date_fin
DATE , OUT total_ventes DECIMAL (10 ,2) )
3 BEGIN
4 SELECT SUM ( montant_total ) INTO total_ventes
5 FROM Commandes
6 WHERE date_commande BETWEEN date_debut AND date_fin ;
7 END //
8 DELIMITER ;
 

Procédure d’ajout de produit :


1 DELIMITER //
2 CREATE PROCEDURE ajouter_produit ( IN nom_produit VARCHAR (255) , IN
categorie_id INT , IN prix DECIMAL (10 ,2) , IN quantite INT , OUT
produit_id INT )
3 BEGIN
4 INSERT INTO Produits ( nom_produit , category_id , prix ,
quantit _stock )
5 VALUES ( nom_produit , categorie_id , prix , quantite ) ;
6

7 SET produit_id = LAST_INSERT_ID () ;

8 END //
9 DELIMITER ;
 

Procédure de réaffectation d’employés :

5
 
1 DELIMITER //
2 CREATE PROCEDURE r ea f f ec t e r_ e m pl o y es ( IN depart_id INT , IN
new_depart_id INT )
3 BEGIN
4 UPDATE E m p l o y s
5 SET department_id = new_depart_id
6 WHERE department_id = depart_id ;
7 END //
8 DELIMITER ;
 

Procédure de suppression de client :


1 DELIMITER //
2 CREATE PROCEDURE supprimer_client ( IN client_id INT )
3 BEGIN
4 DELETE FROM Commandes
5 WHERE customer_id = client_id ;
6

7 DELETE FROM Clients

8 WHERE customer_id = client_id ;
9 END //
10 DELIMITER ;
 

Solutions pour les triggers :

Insertion d’audit :

1 CREATE TRIGGER a u d i t _ e m p l o y e _ i n s e r t
2 AFTER INSERT ON E m p l o y s
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_log ( employee_id , action_time , ac ti on_ de sc ri pt io n
)
6 VALUES ( NEW . employee_id , NOW () , ’ Insertion d ’ e m p l o y ’) ;
7 END ;
 

Vérification de stock :

1 CREATE TRIGGER c h e c k _ s t o c k _ b e f o r e _ u p d a t e
2 BEFORE UPDATE ON Produits
3 FOR EACH ROW
4 BEGIN
5 IF NEW . q u a n t i t _ s t o c k < 0 THEN
6 SIGNAL SQLSTATE ’ 45000 ’
7 SET MESSAGE_TEXT = ’ La q u a n t i t de stock ne peut pas tre
n g a t i v e ’;
8 END IF ;
9 END ;
 

Calcul de la moyenne des salaires :

6
 
1 CREATE TRIGGER up date_a vg_sal ary
2 AFTER INSERT OR UPDATE ON E m p l o y s
3 FOR EACH ROW
4 BEGIN
5 UPDATE D p a r t e m e n t s
6 SET salaire_moyen = ( SELECT AVG ( salaire ) FROM E m p l o y s WHERE
department_id = NEW . department_id )
7 WHERE department_id = NEW . department_id ;
8 END ;
 

Historique des modifications :


1 CREATE TRIGGER lo g_sala ry_cha nge
2 BEFORE UPDATE ON E m p l o y s
3 FOR EACH ROW
4 BEGIN
5 IF NEW . salaire != OLD . salaire THEN
6 INSERT INTO sal ai re _h is to ri qu e ( employee_id , ancien_salaire ,
nouveau_salaire , date_m odific ation )
7 VALUES ( OLD . employee_id , OLD . salaire , NEW . salaire , NOW () ) ;
8 END IF ;
9 END ;
 

Suppression protégée :

1 CREATE TRIGGER p r e v e n t _ d e p a r t m e n t _ d e l e t i o n
2 BEFORE DELETE ON D p a r t e m e n t s
3 FOR EACH ROW
4 BEGIN
5 IF ( SELECT COUNT (*) FROM E m p l o y s WHERE department_id = OLD .
department_id ) > 0 THEN
6 SIGNAL SQLSTATE ’ 45000 ’
7 SET MESSAGE_TEXT = ’ Impossible de supprimer le d p a r t e m e n t car des
e m p l o y s y sont a f f e c t s ’;
8 END IF ;
9 END ;