1-MMPSI-2023 / 2024 ENSA_Tanger
TP_RDB_SQL Queries
Gestion des Employés et Départements
Introduction.
Ce Travaux Pratiques (TP) dédié à la gestion des employés et des départements à l'aide du
langage SQL. Ce TP vise à vous familiariser avec les opérations fondamentales de manipulation
des données dans une base de données relationnelle.
Contexte.
Dans le cadre de ce TP, nous travaillerons avec deux tables clés : la table EMPLOYEE qui
contient des informations sur les employés d'une organisation, et la table DEPARTMENT qui
stocke des détails sur les différents départements au sein de cette organisation. Ces données
simulées serviront de base pour comprendre les principes de la modélisation de base de données
et l'exécution de requêtes SQL.
Objectifs.
1. Créer une base de données dédiée pour nos opérations (SQLTest).
2. Définir la structure de nos tables (EMPLOYEE et DEPARTMENT) avec des clés primaires
et étrangères appropriées.
3. Effectuer des opérations d'insertion, de mise à jour et de suppression de données pour
comprendre la gestion des données dans une base de données SQL.
4. Explorer les capacités des requêtes SQL pour extraire des informations précises de notre
base de données, telles que la récupération de données spécifiques, les opérations de
filtrage et de tri, et la réalisation de jointures entre les tables.
5. Comprendre l'importance des contraintes de clé primaire et étrangère pour garantir
l'intégrité des données.
Instructions.
Suivez attentivement chaque étape du TP pour développer une compréhension approfondie des
concepts SQL.
Utilisez les exemples de données fournies pour exécuter les requêtes SQL et vérifier les résultats.
1|Page A. ERREZGOUNY
1-MMPSI-2023 / 2024 ENSA_Tanger
Soit les entités suivantes présentés par les tableaux :
Tableau EMPLOYEE :
EmpCode EmpFName EmpLName Job Manager HireDate Salary Commission DEPTCODE
SOFTWARE
9369 TONY STARK 7902 1980-12-17 2800 0 20
ENGINEER
9499 TIM ADOLF SALESMAN 7698 1981-02-20 1600 300 30
9566 KIM JARVIS MANAGER 7839 1981-04-02 3570 0 20
9654 SAM MILES SALESMAN 7698 1981-09-28 1250 1400 30
9782 KEVIN HILL MANAGER 7839 1981-06-09 2940 0 10
9788 CONNIE SMITH ANALYST 7566 1982-12-09 3000 0 20
9839 ALFRED KINSLEY PRESIDENT 7566 1981-11-17 5000 0 10
9844 PAUL TIMOTHY SALESMAN 7698 1981-09-08 1500 0 30
SOFTWARE
9876 JOHN ASGHAR 7788 1983-01-12 3100 0 20
ENGINEER
TECHNICAL
9900 ROSE SUMMERS 7698 1981-12-03 2950 0 20
LEAD
9902 ANDREW FAULKNER ANALYST 7566 1981-12-03 3000 0 10
SOFTWARE
9934 KAREN MATTHEWS 7782 1982-01-23 3300 0 20
ENGINEER
9591 WENDY SHAWN SALESMAN 7698 1981-02-22 500 0 30
9698 BELLA SWAN MANAGER 7839 1981-05-01 3420 0 30
9777 MADII HIMBURY ANALYST 7839 1981-05-01 2000 200 NULL
9860 ATHENA WILSON ANALYST 7839 1992-06-21 7000 100 50
9861 JENNIFER HUETTE ANALYST 7839 1996-07-01 5000 100 50
Tableau DEPARTMENT :
DEPTCODE DeptName LOCATION
10 FINANCE TANGER
20 SOFTWARE KNITRA
30 SALES RABAT
40 MARKETING CASABLANCA
50 ADMIN TETOUAN
Utiliser les deux tableaux comme une base de données pour insérer et exploiter les requêtes
SQL. Et le model conceptuel des données (MCD) est représenté comme suite :
• L'entité DEPARTMENT a trois attributs : DEPTCODE (clé primaire), DeptName, et
LOCATION.
2|Page A. ERREZGOUNY
1-MMPSI-2023 / 2024 ENSA_Tanger
• L'entité EMPLOYEE a plusieurs attributs, dont EmpCode (clé primaire), EmpFName,
EmpLName, Job, Manager (référence à l'EmpCode du manager), HireDate, Salary,
Commission, et DEPTCODE (référence à DEPTCODE dans l'entité DEPARTMENT).
Un département peut avoir plusieurs employés, tandis qu'un employé est associé à un seul
département.
Les Requêtes SQL à tester.
Étape 1 (Création de la table)
Il est préférable de créer les données de test dans une base de données dédiée et distincte.
Commençons par créer une base de données à des fins de test
CREATE DATABASE SQLTest; CREATE TABLE EMPLOYEE
USE SQLTest; (
EmpCode INT (4),
CREATE TABLE DEPARTMENT
EmpFName VARCHAR(15),
(
EmpLName VARCHAR(15),
DEPTCODE INT(10), Job VARCHAR(45),
DeptName CHAR(30),
Manager CHAR(4),
LOCATION VARCHAR(33)
HireDate DATE,
);
Salary INT(6),
Commission INT(6),
DEPTCODE INT(2)
);
Étape 2 (Modifier la table)
Ensuite, exécutez les commandes suivantes pour modifier la structure des tables.
ALTER TABLE DEPARTMENT ALTER TABLE DEPARTMENT
ADD PRIMARY KEY (DEPTCODE); MODIFY COLUMN LOCATION
VARCHAR(33) NOT NULL;
ALTER TABLE DEPARTMENT
ALTER TABLE EMPLOYEE
MODIFY COLUMN DEPTCODE INT(10) NOT
NULL; ADD PRIMARY KEY (EmpCode);
ALTER TABLE DEPARTMENT ALTER TABLE EMPLOYEE
MODIFY COLUMN DeptName VARCHAR(15) MODIFY COLUMN EmpCode INT(4) NOT
UNIQUE; NULL;
3|Page A. ERREZGOUNY
1-MMPSI-2023 / 2024 ENSA_Tanger
ALTER TABLE EMPLOYEE ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DEPTCODE) ADD COLUMN DOB DATE
REFERENCES DEPARTMENT(DEPTCODE); AFTER EmpLName;
ALTER TABLE EMPLOYEE ALTER TABLE EMPLOYEE
MODIFY COLUMN Salary DECIMAL(6,2); DROP COLUMN DOB;
Étape 3 (Remplir la table).
La déclaration INSERT ci-dessous remplira les tables ci-dessus avec des données de
démonstration que vous pouvez utiliser pour exécuter des requêtes.
INSERT INTO DEPARTMENT VALUES INSERT INTO EMPLOYEE
(10, 'FINANCE', 'TANGER'), VALUES
(20,'SOFTWARE','KNITRA'), (9369, 'TONY', 'STARK', 'SOFTWARE
ENGINEER', 7902, '1980-12-17', 2800,0,20),
(30, 'SALES', 'RABAT'),
(9499, 'TIM', 'ADOLF', 'SALESMAN', 7698,
(40,'MARKETING', 'CASABLANCA'),
'1981-02-20', 1600, 300,30),
(50,'ADMIN', 'TETOUAN');
-- Ajoutez d'autres données ici...
Vous pouvez compléter la déclaration INSERT avec d'autres données et à ajuster les exemples
en fonction de vos besoins spécifiques.
Les requêtes SQL.
1. Quels sont les noms et salaires des employés dont le salaire est supérieur à 3000 ?
2. Quels sont les employés qui n'ont pas de manager ?
3. Quels sont les employés embauchés avant l'année 1985 ?
4. Affichez les employés qui ont une commission.
5. Quels sont les employés dont le nom commence par la lettre 'A' ?
6. Affichez les employés qui ont été embauchés entre 1980 et 1985 inclusivement.
7. Affichez les employés dont le salaire est supérieur à la moyenne des salaires.
8. Combien d'employés ont un manager ?
9. Affichez les employés triés par ordre décroissant de salaire.
Les requêtes de jointure dans SQL.
1. Affichez tous les employés dans le département de 'SOFTWARE'.
2. Affichez le nombre total d'employés dans chaque département.
3. Quel est le salaire moyen dans le département de 'SALES' ?
4. Quel est le nombre total d'employés par ville ?
5. Quels sont les départements avec au moins un employé ayant un salaire supérieur à
4000 ?
4|Page A. ERREZGOUNY