TP Bases de Données

SMI S5
Exercice2
• Créer une requête pour afficher la liste de tous les jobs différents des
employés repris dans la DB
• Afficher une liste des employés en reprenant leur nom et leur job
séparé par une virgule et un espace; donner Ie nom « Employés et
titres » cette colonne
• Une requête pour afficher toutes les données de la table « emp »; Ie
nom de la colonne est « La sortie »; à une ligne doit correspondre UN
employé
Restriction et tris de données

4
Exercices
• 1. Créer une requête pour afficher le nom et le salaire des employés
gagnant plus de 2850; enregistrer la requête dans un fichier [Link]
• 2. Créer une requête pour afficher le nom et le numéro de
département de l'employé possédant le numéro 7566
• 3. Créer une requête pour afficher le nom et la salaire des employés
dont le salaire n'est pas compris entre 1500 et 2850

5
Exercices
• 4. Afficher le nom, le métier et la date d'engagement des employés
engagés entre le 20 février 1981 et le 1er mars 1981.
• Trier le résultat par ordre d'arrivée dans la société .
• 5. Afficher le nom et le numéro de département des employés dont le
numéro de département est soit 10 soit 30.
• Trier le résultat par ordre alphabétique du nom

6
Exercices
• 6. Afficher le nom et le salaire des employés qui gagnent plus de 1500
et sont dans le département 10 ou 30.
• Libeller les colonnes « Employés» et « Salaire mensuel»
• 7. Afficher le nom et la date d'engagement des employés arrivés en
1982

7
Exercices
• 8. Afficher le nom, le salaire et la commission de tous les employés
ayant une commission.
• Trier les résultats par ordre croissant de salaire et de commission
• 9. Quels sont les employés dont la 3ème lettre du nom est 'A' ?
• 10. Afficher les employés dont le nom contient la séquence 'LL', qui
appartiennent au département 30 ou dont le manager porte le N°
7782

8
Exercices
• 11. Afficher le nom et le titre de tous les employés n'ayant pas de
manager
• 12. Afficher le nom, le titre et le salaire de tous les employés dont le
job n'est ni 'CLERK' ni 'ANALYST' et dont le salaire n'est ni 1000, ni
3000, ni 5000.
• Donner 2 solutions

9
Fonctions single row

10
Exercices
• 1. Afficher le n°, le nom, le salaire et le salaire augmenté de 15% (en
valeur entière) des employés
• 2. Sur base de cette requête, ajouter une information reprenant la
différence de salaire entre l'ancien et le nouveau salaire (en entier)
• 3. Afficher le nom de l'employé, sa date d'engagement et sa date de
révision de salaire qui est le 1er lundi après 6 mois de service (libellé
« Révision », format jour, le xx mois YYYY)

11
Exercices
• 4. Pour chaque employé, calculer le nombre de mois écoulés entre
leur date d'engagement et aujourd'hui.
• Libeller cette colonne « Mois»
• Trier le résultat de l'employé le plus ancien vers le plus récent
• 5. Afficher les employés:
• dont le nom commence par J, A ou M;
• Mettre leur nom en minuscule avec la première lettre en majuscule
• indiquer le nombre de caractères composant leur nom

12
Exercices
• 6. Ecrire une requête affichant pour chaque employé:
• <nom> gagne <salaire> par mois, mais voudrait gagner <3 x salaire>
• Libeller cette colonne « Salaire de rêve»
• 7. Ecrire une requête affichant le nom et le salaire de tous les
employés; justifier à droite le salaire sur une largeur de 15 caractères
en complétant par des points. Libeller cette colonne « Salaire»
• 8. Afficher le nom, la date d'engagement et le jour de la semaine
correspondant

13
Requêtes sur tables multiples

14
Exercices
• 1. Afficher le nom, le numéro de département et le nom du
département de tous les employés

• 2. Afficher le nom, le nom du département et la ville de travail de

tous les employés ne possédant pas de commission

• 3. Afficher le job et la localité de travail des employés du

département 30

15
Exercices
• 4. Afficher le nom et le département de tous les employés dont le
nom contient la lettre 'A'

• 5. Afficher le nom, le job, le numéro et le nom de département des

employés travaillant à Dallas

• 6. Afficher le nom de l'employé, de son responsable ainsi que leur

numéro respectif

• 7. Idem 6 mais voir les employés sans manager

16
Exercices
• 8. Afficher le nom et le numéro de département de tous les
employés et pour chaque employé, l'ensemble de ses collègues

• 9. Afficher le nom, le job, le nom de département, le salaire et le

grade de tous les employés

• 10. Afficher le nom et la date d'engagement des employés arrivés

après BLAKE

17
Exercices
• 11. Afficher les employés plus ancien que leur manager respectif

• 12. Afficher le nom de l'employé et son salaire exprimé«

graphiquement» sous la forme d'une succession d'astérisques
(chaque astérisque valant 100)
• Trier en commençant par les salaires les plus élevés

18
Fonctions de groupe

19
Exercices
• 1. Afficher en arrondissant:
• le plus grand salaire
• le plus petit salaire
• la somme de tous les salaires
• la moyenne des salaires
• 2. Afficher le minimum, le maximum, la somme et la moyenne des
salaires pour chaque job
• 3. Afficher le nombre de personnes ayant le même travail (et ce
travail)

20
Exercices
• 4. Afficher le nombre de personnes ayant un manager
• 5. Afficher la différence entre le plus grand et le plus petit salaire
• 6. Afficher le numéro du manager et le plus petit salaire des employés
sous sa direction.
• Exclure les employés sans manager.
• Exclure les employés ayant un salaire < 1000
• Trier par ordre décroissant de salaire

21
Exercices
• 7. Créer une requête affichant:
• le nom du département
• sa localisation
• le nombre d'employés
• la moyenne salariale de tous les employés de chaque département

• 8. Créer une requête affichant:

• le nombre total d'employés
• le nombre d'employés engagés en 1981, 1982 et 1983

22
Sous-requêtes (subqueries)

23
Exercices
• 1. Afficher le nom et la date d'engagement de tous les employés du
même département que celui de 'BLAKE', celui-ci étant exclu
• 2. Afficher le numéro et le nom de tous les employés qui ont un
salaire supérieur au salaire moyen; trier par ordre de salaire
décroissant
• Afficher le numéro et le nom de tous les employés qui travaillent
dans un département contenant au moins un employé dont le nom
contient 't'

24
Exercices
• 4. Afficher le nom de l'employé, le numéro de département et le job
de tous les employés dont la localisation du département est
DALLAS
• 5. Afficher le nom et le salaire des employés dirigés par KING
• 6. Afficher le numéro de département, le nom et le job de tous les
employés du département SALES

25
Exercices
• 7. Afficher le numéro, le nom et le salaire de tous les employés qui
ont un salaire supérieur au salaire moyen et qui travaillent dans le
département des employés dont le nom contient la lettre 'T'

26
SQL*PLUS

27
Gestion des données

28
Exercices
• 1- Exécuter le script [Link] pour construire la table MY_EMPLOYEE

• 2- Ajouter les 5 lignes suivantes:

• 1 PATEL • RALPH -rpatel ·795
• 2 DANES -BETTY· bdanes • 860
• 3·Biri -Ben -bbiri -1100
• 4 Newman -Chad -cnewman • 750
• 5 Ropeburn • Audry -aropebur -1550

29
Exercices
• 3- Vérifier que les nouvelles données sont bien MAJ
• 4- Rendre ces modifications permanentes
• 5- Modifier le champ « last_name » de l'employé 3 en Drexler
• 6- Modifier le salaire de tous les employés ayant moins de 900;
vérifier les changements
• 7- Effacer « Betty Danes »; rendre ce changement permanent

30
Exercices
• 8- Afficher le contenu de la table; définir un point intermédiaire de
transaction; vider entièrement la table; vérifier que la table est bien
vide; annuler ce changement; vérifier que les données sont bien là;
rendre les changements définitifs.

31
Gestion des tables

32
Exercices
• 1. Créer la table DEPARTEMENT comme suit;
• vérifier que la table est bien créée
• ID NUMBER(7)
• NAME VARCHAR2(25)

• 2. Remplir cette table avec les données de la table DEPT; utiliser

uniquement les champs nécessaires

33
Exercices
• 3. Créer la table EMPLOYEE comme suit;
• vérifier que la table est bien créée
• ID NUMBER(7)
• LAST_NAME VARCHAR2(25)
• FIRST_NAME VARCHAR2(25)
• DEPTID NUMBER(7)

• 4. Augmenter la taille du champ LAST_NAME à 50 caractères;

confirmer la modification

34
Exercices
• 5. Examiner le DATA DICTIONARY pour voir que les tables sont bien
créées
• 6. Créer la table EMPLOYEE2 basée sur la structure de la table EMP
mais ne reprendre que les colonnes suivantes en changeant leur nom:
• EMPNO ID
• ENAME LAST_NAME
• DEPTNO DEPTID

35
Exercices
• 7. Effacer la table EMPLOYEE
• 8. Renommer la table EMPLOYEE2 en EMPLOYEE

36
Contraintes

37
Exercices
• 1. Définir le champ ID de la table EMPLOYEE comme PK

• 2. Définir également le champ ID de la table DEPARTMENT comme PK

• 3. Ajouter une FK sur la table EMPLOYEE qui assure qu'un employé

appartient nécessairement à un département existant

38
Exercices
• 4. Visualiser les contraintes créées dans le DATA DICTIONARY

• 5 . Ajouter dans la table EMPLOYEE une colonne SALARY de type

NUMBER(7,2)

• 6. Y associer une contrainte vérifiant que le salaire n'est pas inférieur

à 5OO et ne dépasse pas 5OOO

39
Vues

40
Exercices
• 1. Créer une vue EMP_VU basée sur le n° et le nom de l'employé et
son numéro de département

• 2. Changer l'en-tête des noms des employés en EMPLOYEE

• 3. Afficher le contenu de cette vue

• 4. Voir cette vue dans le DATA DICTIONARY

41
Exercices
• 5. Utiliser la vue pour extraire le nom des employés et leur numéro de
département

• 6. Créer une vue DEPT20 contenant pour les employés du département 20:
• le numéro (libeller EMPID)
• le nom (libeller EMPNAME)
• le n° de département (libeller DEPTID)
• Interdire de pouvoir déplacer un employé dans un autre département au travers de
cette vue

• 7. Utiliser la vue et essayer de déplacer un employé dans le département

30

42
Exercices
• 8. Créer une vue SAL_VU basée sur
• le nom des employés (libeller Employee)
• le nom de son département (libeller Department)
• son salaire (libeller Salary)
• le niveau de son salaire (libeller Grade)

• 9. Voir le contenu de cette vue

43
Autres objets ORACLE

44
Exercices
• 1. Créer une séquence DEPTID_SEQ utilisée avec la PK de la table
DEPARTMENT
Faire commencer cette séquence à 60, avec un MAX de 200
Incrémenter de 10

• 2. Ecrire un script SQL*PLUS qui affiche les infos suivantes sur n'importe
quelle séquence:
• leur nom
• la valeur maximale
• l'incrément
• la valeur courante

45
Exercices
• 3. Ecrire un script SQL*PLUS interactif permettant d'ajouter des lignes à la
table DEPARTMENT
Utiliser la séquence précédente
Demander le nom du département
Exécuter ce script et ajouter les départements « Education» et «
Administration»

• 4. Créer un index « non-unique» sur le champ contenant la FK de la table

EMPLOYEE

• 5. Afficher tous les indexes de la table EMPLOYEE

46
Sécurité d’accès

47
Exercices
• 4. Quelle commande faut-il utiliser pour changer votre mot de passe?

• 5. Donner les droits d'accès en lecture sur la table DEPT à l'utilisateur

USER1

• 6. Comment afficher toutes les lignes de la table DEPT

48
Exercices
• 7. Créer un synonyme DEP sur la table DEPT

• 8. Afficher toutes les données de cette table en utilisant le synonyme

• 9. Afficher les informations sur les tables auxquelles vous avez accès

• 10. Ne pas prendre les tables pour lesquelles vous n'avez pas accès

49