444121661-CORRIGE-REVISION5

EXERCICE
Soit la Base de Données PERSONNEL composée des relations suivantes :
EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, #DEPTNO)
DEPT (DEPTNO, DNAME, LOC)
Clés primaires
Clés étrangères#
MGR est le numéro d'employé (EMPNO) du manager de l'employé courant
TABLE EMP
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7369
BOUNKHALA
CLERK
7902
17-DEC-2010
80 000
20
7499
KHEROUA
SALESMAN
7698
20-FEB-2011
160 000
30 000
30
7521
GOURARI
SALESMAN
7698
22-FEB-2011
125 000
50 000
30
7566
MAAMAR
MANAGER
7839
02-APR-2011
295 000
20
7654
OULD SAADI
SALESMAN
7698
28-SEP-2011
125 000
140 000
30
7698
BENYETTOU
MANAGER
7839
01-MAY-2011
285 000
30
7782
HACHEMANI
MANAGER
7839
09-JUN-2011
245 000
10
7839
AMEZIANE
PRESIDENT
17-NOV-2011
500 000
10
7844
RAHMOUNI
SALESMAN
7698
08-SEP-2011
150 000
30
7876
DEKHISSI
CLERK
7788
23-SEP-2017
110 000
20
7900
HAMOUCHE
CLERK
7698
03-DEC-2011
95 000
30
7902
LOUHIBI
ANALYST
7566
03-DEC-2011
300 000
20
7934
LAKEHAL
CLERK
7782
23-JAN-2012
130 000
10
TABLE DEPT
DEPTNO
DNAME
LOC
10
ACCOUNTING
ALGER
20
RESEARCH
ORAN
30
SALES
CONSTANTINE
40
OPERATIONS
ANNABA
Création de la base de données
1. créer les tables EMP et DEPT
2. Remplir la table DEPT avec des commandes INSERT INTO
3. Dans EMP, ajouter les nouveaux employés
<7369,'TALEB','MANAGER'7839,'17-NOV-2011,350 000,60000,10>;
<7657,'TALEB','MANAGER'7839,'17-NOV-2011,350 000,60000,50>;
<7657,'TALEB','MANAGER'7000,'17-NOV-2011,350 000,60000,10>;
<7657,'TALEB','MANAGER'7839,'17-NOV-2011,350 000,60000,10>;
Remarques?

INSERT INTO EMP VALUES
(7657,'TALEB', 'MANAGER', 7839, '17-NOV-2011', 350000, 60000, 10)
Mise à jour de la base de données
1.Dans EMP ajouter une colonne "observation" de 20 caractères
ALTER TABLE EMP ADD OBSERVATION CHAR(20)
2. Dans DEPT renommer la colonne LOC en LOCATION
ALTER TABLE DEPT CHANGE LOC LOCATION CHAR(20)
3. Dans EMP supprimer la colonne Observation
ALTER TABLE EMP DROP OBSERVATION
4. Changer la LOCalisation du département SALES de Constantine
à SETIF
UPDATE DEPT SET LOC='SETIF' WHERE DNAME=CONSTANTINE
5. Dans EMP augmenter de 10% le SALaire des vendeurs
(SALESMAN) dont la COMMission est supérieur à 50% du salaire
UPDATE EMP SET SAL+SAL*1,1 WHERE COMM>0.5 * SAL
6. Dans EMP, donner aux employés en poste avant le 01/01/2012
(HIREDATE) une commission égale à la moyenne des
commissions.
UPDATE EMP SET COMM=(SELECT AVG (COMM) FROM EMP
WHERE HIREDATE <01-JAN-2012'
7. Dans DEPT, supprimer le département n°20 (DEPTNO)
DELETE FROM DEPT WHERE DEPTNO=20
8. Supprimer toutes les données en conservant la table EMP
TRUNCATE TABLE EMP
9. Supprimer la table DEPT.
DROP TABLE DEPT

Interrogation de la base de données
Exprimer en SQL les requêtes suivantes/
1. Salaire moyen par fonction (sans tenir compte des commissions)
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB
2. Total des salaires du département SALES
SELECT SUM(SAL) FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND DNAME='SALES'
3. Nom, fonction et salaire de l'employé qui a le salaire le
plus grand
SELECT ENAME, JOB, SAL FROM EMP
WHERE SAL=(SELECT MAX(SAL) FROM EMP)
4. Nom des employés qui gagnent plus que MAAMAR
SELECT ENAME FROM EMP WHERE SAL >(SELECT SAL FROM EMP
WHERE ENAME='MAAMAR')
5. Nom des employés qui ont le même manger que HACHEMANI
SELECT ENAME FROM EMP WHERE MGR =(SELECT MGR FROM EMP
WHERE ENAME='HACHEMANI')