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 7369 7499 7521 7566 7654 7698 7782 7839 7844 7876 7900 7902 7934 ENAME BOUNKHALA KHEROUA GOURARI MAAMAR OULD SAADI BENYETTOU HACHEMANI AMEZIANE RAHMOUNI DEKHISSI HAMOUCHE LOUHIBI LAKEHAL TABLE DEPTNO 10 20 30 40 DEPT JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK DNAME ACCOUNTING RESEARCH SALES OPERATIONS MGR 7902 7698 7698 7839 7698 7839 7839 7698 7788 7698 7566 7782 HIREDATE 17-DEC-2010 20-FEB-2011 22-FEB-2011 02-APR-2011 28-SEP-2011 01-MAY-2011 09-JUN-2011 17-NOV-2011 08-SEP-2011 23-SEP-2017 03-DEC-2011 03-DEC-2011 23-JAN-2012 SAL 80 000 160 000 125 000 295 000 125 000 285 000 245 000 500 000 150 000 110 000 95 000 300 000 130 000 COMM 30 000 50 000 140 000 DEPTNO 20 30 30 20 30 30 10 10 30 20 30 20 10 LOC ALGER ORAN CONSTANTINE 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')
