M1 IDSM-Kharkiv Année 2019-2020 Bases de données Exercices

M1 IDSM - Bases de données avancées - Exercices pratiques (2) 1/3
M1 IDSM-Kharkiv - Année 2019-2020
Bases de données
Exercices pratiques (2) : SQL
J. Darmont (
Base de données
Considérons la base de données dont le schéma et l'extension sont donnés ci-dessous.
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.
EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-dec-80 800.00 NULL 20
7499 ALLEN SALESMAN 7698 20-feb-81 1600.00 300.00 30
7521 WARD SALESMAN 7698 22-feb-81 1250.00 500.00 30
7566 JONES MANAGER 7839 02-apr-81 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 28-sep-81 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 01-may-81 2850.00 NULL 30
7782 CLARK MANAGER 7839 09-jun-81 2450.00 NULL 10
7839 KING PRESIDENT NULL 17-nov-81 5000.00 NULL 10
7844 TURNER SALESMAN 7698 08-sep-81 1500.00 0.00 30
7876 ADAMS CLERK 7788 23-sep-87 1100.00 NULL 20
7900 JAMES CLERK 7698 03-dec-81 950.00 NULL 30
7902 FORD ANALYST 7566 03-dec-81 3000.00 NULL 20
7934 MILLER CLERK 7782 23-jan-82 1300.00 NULL 10
DEPT
DEPTNO DNAME LOC
10 ACCOUNTING NEW-YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
M1 IDSM - Bases de données avancées - Exercices pratiques (2) 2/3
Exercice 1 : Création de la base de données, contraintes d'intégrité
1. Créer la table DEPT (menu Design / Add table). Le numéro de département DEPTNO est la clé
primaire. Sauvegarder le script SQL (Save as script) et exécuter le script (Run script).
2. Remplir la table DEPT avec des commandes SQL INSERT INTO (SQL Worksheet).
3. Télécharger le script SQL
http://eric.univ-lyon2.fr/jdarmont/docs/emp.sql (menu My Scripts / Upload
Script). Exécuter le script. Il crée la table EMP.
4. À partir de maintenant, on travaille uniquement dans la SQL Worksheet. Dans EMP, ajouter les nouveaux employés :
Remarques ?
Exercice 2 : Mise à jour de la base de données
1. Changer la LOCalisation du département SALES de CHICAGO à PITTSBURGH.
2. Dans EMP, augmenter de 10 % le SALaire des vendeurs (SALESMAN) dont la COMMission est
supérieure à 50 % du salaire.
3. Dans EMP, donner aux employés en poste avant le 01/01/1982 (HIREDATE) et ayant une
commission non spécifiée (NULL) une commission égale à la moyenne des commissions.
4. Dans DEPT, supprimer le département n° 20 (DEPTNO). Remarque ?
Exercice 3 : Interrogation de la base de données
Exprimer en SQL les requêtes suivantes.
1. Nom (ENAME), salaire, commission, salaire + commission de tous les vendeurs.
2. Nom des vendeurs par ordre décroissant du ratio commission/salaire.
3. Nom des vendeurs dont la commission est inférieure à 25 % de leur salaire.
4. Nombre d'employés du département n° 10.
5. Nombre d'employés ayant une commission.
6. Nombre de fonctions (JOB) différentes.
7. Salaire moyen par fonction (sans tenir compte des commissions).
8. Total des salaires du département SALES.
9. Nom des employés avec le nom de leur département.
10. Nom, fonction et salaire de l'employé qui ont le salaire le plus grand.
11. Nom des employés qui gagnent plus que JONES.
12. Nom des employés qui ont la même fonction que JONES.
13. Nom des employés qui ont le même manager que CLARK.
14. Nom et fonction des employés qui ont la même fonction et le même manager que TURNER.
15. Nom des employés qui ont été embauchés avant tous les employés du département n° 10.
16. Liste des employés avec leur nom et celui de son manager.
17. Nom des employés qui ne travaillent pas dans le même département que leur manager.
M1 IDSM - Bases de données avancées - Exercices pratiques (2) 3/3
Requêtes hiérarchiques
18. Structure hiérarchique des employés (NOM, JOB, EMPNO, MGR). L'employé au sommet de la
hiérarchie n'a pas de manager.
19. Liste des employés qui dépendent de JONES. Indiquer leur niveau (LEVEL) dans la hiérarchie.
20. Salaire moyen pour chaque niveau d'employé.
21. Liste des employés qui travaillent pour JONES, sauf SCOTT.
22. Liste des employés qui travaillent pour JONES, sauf SCOTT et ceux qui travaillent pour SCOTT.
Exercice 4 : Vues et catalogue du système
1. Créer la vue EMPDIR (EMPNO, ENAME) à partir de la table EMP. Vérifier son contenu.
2. Depuis la vue EMPDIR, modifier le nom du pilote n° 7839 en 'DARMONT'. Consulter le contenu de
la vue EMPDIR et de la table EMP.
3. Créer la vue EMPDEPT (EMPNO, ENAME, DEPTNO, DNAME) partir des tables EMP et DEPT.
Vérifier son contenu. Quel est l'intérêt de définir cette vue ?
4. À travers la vue EMPDEPT, modifier le nom de l'employé n° 7698 en 'SINBAD'. Que se passe-t-il ?
5. Insérer un n-uplet quelconque dans la vue EMPDEPT. Que se passe-t-il ?
6. Lister toutes les tables qui vous sont accessibles (nom et propriétaire) en interrogeant la vue
système ALL_TABLES.
7. Lister les tables et les vues de votre compte, ainsi que leurs types (table ou vue), à l'aide de la vue
système USER_CATALOG.
8. Lister toutes les contraintes d'intégrité définies sur vos tables à l'aide de la vue système
USER_CONSTRAINTS. Afficher pour chaque contrainte son nom, la table à laquelle elle s'applique,
son type, et sa " condition de recherche ».
9. À partir de la vue système USER_TAB_COLUMNS, afficher les attributs de la table EMP.
10. À partir de la vue système USER_TAB_COLUMNS, afficher le nom des tables et des vues qui ont
pour attribut DEPTNO. Commentaire ?