Oracle Database 11g : Les fondamentaux du langage SQL I - 1

Exercice Introduction
Exécuter la démonstration d'Oracle SQL Developer : Créer une connexion de base de
données
Démarrer Oracle SQL Developer
Créer une connexion de base de données pour Oracle SQL Developer
• Pour créer une connexion de base de données, cliquez avec le bouton droit de la souris sur
Connections dans le navigateur de connexions (Connections Navigator). Dans le menu,
sélectionnez New Connection. La boîte de dialogue New/Select Database Connection
apparaît.
• Créez une connexion de base de données à l'aide des informations suivantes :
- a. Connection Name : myconnection.
- b. Username : HR
- Password : HR
- d. Hostname :
- Port : 1521
- SID :
Tester et utiliser la connexion de base de données pour Oracle SQL Developer
• Testez la nouvelle connexion.
• Si le statut est Success, connectez-vous à la base de données à l'aide de cette nouvelle
connexion.
Naviguer au sein des tables dans le navigateur de connexions
• Dans le navigateur de connexions, visualisez les objets à votre disposition sous le noeud
Tables. Vérifiez que les tables suivantes sont présentes :
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOB_GRADES
JOB_HISTORY
JOBS
LOCATIONS
REGIONS
• Naviguez dans la structure de la table EMPLOYEES.
• Visualisez les données de la table DEPARTMENTS.
Ouvrir une feuille de calcul SQL Worksheet
• Ouvrez une nouvelle feuille de calcul SQL Worksheet. Examinez les icônes de raccourci
disponibles pour cette feuille de calcul.

Oracle Database 11g : Les fondamentaux du langage SQL I - 2

Exercice 1
Première partie
Testez vos connaissances :
1. L'instruction SELECT suivante est exécutée avec succès :
SELECT last_name, job_id, salary AS Sal
FROM employees;

Vrai/Faux
2. L'instruction SELECT suivante est exécutée avec succès :
SELECT *
FROM job_grades;

Vrai/Faux
3. L'instruction suivante présente quatre erreurs de codage. Pouvez-vous les identifier ?
SELECT employee_id, last_name
sal x 12 ANNUAL SALARY
FROM employees;
Deuxième partie
Avant de commencer les exercices, notez les points suivants :
• Enregistrez tous vos fichiers d'exercice à l'emplacement suivant : D:\labs\SQL1\labs
• Entrez vos instructions SQL dans une feuille de calcul SQL Worksheet. Pour enregistrer un
script dans SQL Developer, vérifiez que la feuille de calcul SQL requise est active puis
sélectionnez Save As dans le menu File. Vous pouvez aussi cliquer avec le bouton droit de
la souris dans la feuille de calcul SQL Worksheet et sélectionner Save file pour enregistrer
votre instruction SQL sous la forme d'un script nommé
lab_<num_chapitre>_<num_étape>.sql. Lorsque vous modifiez un script existant, veillez à
utiliser l'option Save As pour l'enregistrer sous un autre nom.
• Pour exécuter l'interrogation, cliquez sur l'icône Execute Statement dans la feuille de calcul
SQL Worksheet. Vous pouvez également appuyer sur [F9]. Pour les instructions LMD et
LDD, utilisez l'icône Run Script ou appuyez sur [F5].
• Une fois l'interrogation exécutée, veillez à ne pas entrer l'interrogation suivante dans la
même feuille de calcul. Ouvrez une nouvelle feuille.
Vous avez été embauché en tant que programmeur SQL par la société Acme Corporation. Votre
première tâche consiste à créer des états à partir des données des tables du département des
ressources humaines.
4. Vous devez d'abord déterminer la structure et le contenu de la table DEPARTMENTS.

Oracle Database 11g : Les fondamentaux du langage SQL 1 - 3

Exercice 1 (suite)

5. Vous devez déterminer la structure de la table EMPLOYEES.

Le département des ressources humaines souhaite une interrogation affichant le nom,

l'ID de poste, la date d'embauche et l'ID d'employé de chaque employé, l'ID d'employé
apparaissant en premier. Associez l'alias STARTDATE à la colonne HIRE_DATE.
Enregistrez votre instruction SQL dans un fichier nommé lab_01_05.sql afin de
pouvoir fournir ce fichier au département des ressources humaines.

Oracle Database 11g : Les fondamentaux du langage SQL 1 - 4

Exercice 1 (suite)
6. Testez l'interrogation enregistrée dans le fichier lab_01_05.sql afin de vérifier
qu'elle s'exécute correctement.
Remarque : Une fois l'interrogation exécutée, veillez à ne pas entrer l'interrogation
suivante dans la même feuille de calcul. Ouvrez une nouvelle feuille.

7. Le département des ressources humaines souhaite une interrogation affichant tous les
ID de poste uniques de la table EMPLOYEES.

Oracle Database 11g : Les fondamentaux du langage SQL 1 - 5

Exercice 1 (suite)
Troisième partie
S'il vous reste du temps, effectuez les exercices suivants :
8. Le département des ressources humaines souhaite des en-têtes de colonne plus
descriptifs pour son état sur les employés. Copiez l'instruction enregistrée dans
lab_01_05.sql dans une nouvelle feuille de calcul SQL Worksheet. Nommez les
en-têtes de colonne respectivement Emp #, Employee, Job et Hire Date.
Exécutez à nouveau votre interrogation.

9. Le département des ressources humaines a demandé un état listant tous les employés
avec leur ID de poste. Affichez le nom concaténé avec l'ID de poste (en séparant les
deux par une virgule et un espace) et intitulez la colonne Employee and Title.

Oracle Database 11g : Les fondamentaux du langage SQL 1 - 6

Exercice 1 (suite)
Pour mettre en pratique vos connaissances de façon plus approfondie, effectuez l'exercice
suivant :
10. Pour vous familiariser avec le contenu de la table EMPLOYEES, créez une
interrogation affichant toutes les données de cette table. Séparez les colonnes de
résultat par une virgule. Attribuez le titre de colonne THE_OUTPUT.

Oracle Database 11g : Les fondamentaux du langage SQL 1 - 7

Exercice 2
Le département des ressources humaines a besoin de votre aide pour créer
des interrogations.
1. Pour des raisons budgétaires, ce département a besoin d'un état affichant le nom et le
salaire des employés qui gagnent plus de 12 000 $. Enregistrez vos instructions SQL
dans le fichier lab_02_01.sql. Exécutez votre interrogation.

2. Ouvrez une nouvelle feuille de calcul SQL Worksheet. Créez un état affichant le nom
et le numéro de département correspondant à l'ID d'employé 176. Exécutez
l'interrogation.

3. Le département des ressources humaines a besoin de connaître les employés dont

le salaire est élevé et ceux dont le salaire est faible. Modifiez le fichier
lab_02_01.sql pour afficher le nom et le salaire des employés dont le salaire
ne figure pas dans la plage de 5 000 $ à 12 000 $. Enregistrez votre instruction SQL
dans le fichier lab_02_03.sql.

Oracle Database 11g : Les fondamentaux du langage SQL 2 - 8

Exercice 2 (suite)
4. Créez un état affichant le nom, l'ID de poste et la date d'embauche des employés
nommés Matos et Taylor. Triez les données par ordre croissant en fonction de la date
d'embauche.

5. Affichez le nom et le numéro de département de tous les employés du département 20

ou 50 par ordre alphabétique croissant, en fonction du nom.

6. Modifiez le fichier lab_02_03.sql pour afficher le nom et le salaire des employés

qui gagnent entre 5 000 $ et 12 000 $, et travaillent dans le département 20 ou 50.
Intitulez respectivement les colonnes Employee et Monthly Salary. Enregistrez
le fichier lab_02_03.sql sous le nom lab_02_06.sql. Exécutez l'instruction
figurant dans lab_02_06.sql.

Oracle Database 11g : Les fondamentaux du langage SQL 2 - 9

Exercice 2 (suite)
7. Le département des ressources humaines a besoin d'un état affichant le nom et la date
d'embauche de tous les employés embauchés en 1994.

8. Créez un état affichant le nom et l'intitulé de poste de tous les employés qui n'ont pas
de manager.

9. Créez un état affichant le nom, le salaire et la commission de tous les employés qui
perçoivent des commissions. Triez les données par ordre décroissant en fonction du
salaire et des commissions.
Utilisez la position numérique de la colonne dans la clause ORDER BY.

10. Les membres du département des ressources humaines souhaitent davantage de

souplesse dans les interrogations que vous écrivez. Ils voudraient un état affichant le
nom et le salaire des employés qui gagnent plus qu'un montant saisi par l'utilisateur
en réponse à une invite. Enregistrez cette interrogation dans le fichier
lab_02_10.sql. Si, à l'invite, vous indiquez 12000, l'état affiche les résultats
suivants :

Oracle Database 11g : Les fondamentaux du langage SQL 2 - 10

Exercice 2 (suite)
11. Le département des ressources humaines souhaite exécuter des états sur la base d'un
manager. Créez une interrogation invitant l'utilisateur à indiquer un ID de manager, et
générant l'ID d'employé, le nom, le salaire et le département des employés de ce
manager. Le département des ressources humaines souhaite pouvoir trier l'état en
fonction d'une colonne sélectionnée. Vous pouvez tester les données avec les valeurs
suivantes :

manager_id = 103, tri en fonction de last_name :

manager_id = 201, tri en fonction de salary :

manager_id = 124, tri en fonction de employee_id :

Oracle Database 11g : Les fondamentaux du langage SQL 2 - 11

Exercice 2 (suite)
S'il vous reste du temps, effectuez les exercices suivants :
12. Affichez le nom de tous les employés dont le nom comprend la lettre "a" en troisième
position.

13. Affichez le nom de tous les employés dont le nom comporte un "a" et un "e".

Pour mettre en pratique vos connaissances de façon plus approfondie, effectuez

les exercices suivants :
14. Affichez le nom, le poste et le salaire de tous les employés dont le poste est vendeur
ou commis aux stocks, et dont le salaire n'est pas égal à 2 500 $, 3 500 $ ou 7 000 $.

15. Modifiez lab_02_06.sql pour afficher le nom, le salaire et la commission de tous

les employés dont la commission est de 20 %. Enregistrez le fichier
lab_02_06.sql sous le nom lab_02_15.sql. Exécutez l'instruction figurant
dans lab_02_15.sql.

Oracle Database 11g : Les fondamentaux du langage SQL 2 - 12

Exercice 3
Première partie
1. Ecrivez une interrogation permettant d'afficher la date système. Nommez la colonne
Date.
Remarque : Si votre base de données est distante et située dans un autre fuseau horaire,
le résultat sera la date correspondant au système d'exploitation sur lequel réside la base
de données.

2. Le département des ressources humaines a besoin d'un état permettant d'afficher le

numéro d'employé, le nom, le salaire et le salaire augmenté de 15,5 % (exprimé sous la
forme d'un nombre entier) pour chaque employé. Nommez la colonne New Salary.
Enregistrez l'instruction SQL dans le fichier lab_03_02.sql.
3. Exécutez l'interrogation figurant dans le fichier lab_03_02.sql.

4. Modifiez l'interrogation lab_03_02.sql pour ajouter une colonne permettant de

soustraire l'ancien salaire du nouveau. Nommez la colonne Increase. Enregistrez le
contenu du fichier sous le nom lab_03_04.sql. Exécutez l'interrogation modifiée.

Oracle Database 11g : Les fondamentaux du langage SQL 3 - 13

Exercice 3 (suite)
5. Ecrivez une interrogation permettant d'afficher le nom (la première lettre en
majuscule et toutes les autres lettres en minuscules) et la longueur du nom de tous les
employés dont le nom commence par les lettres "J", "A" ou "M". Attribuez à chaque
colonne un libellé approprié. Triez les résultats en fonction du nom des employés.

Réécrivez l'interrogation de sorte que l'utilisateur soit invité à saisir la lettre par
laquelle le nom doit commencer. Par exemple, si l'utilisateur saisit "H" (en
majuscule) à l'invite, le résultat doit afficher tous les employés dont le nom
commence par la lettre "H".

Modifiez l'interrogation de sorte que la casse de la lettre saisie n'affecte pas le

résultat. La lettre saisie doit être convertie en majuscule avant traitement par
l'interrogation SELECT.

Oracle Database 11g : Les fondamentaux du langage SQL 3 - 14

Exercice 3 (suite)
6. Le département des ressources humaines souhaite connaître l'ancienneté de chaque
employé. Pour chacun d'eux, affichez le nom et calculez le nombre de mois entre la
date du jour et la date d'embauche de l'employé. Nommez la colonne
MONTHS_WORKED. Triez les résultats sur la base du nombre de mois d'ancienneté.
Arrondissez le nombre de mois au nombre entier supérieur le plus proche.
Remarque : Cette interrogation dépendant de sa date d'exécution, les valeurs de la
colonne MONTHS_WORKED seront différentes dans votre cas.

Oracle Database 11g : Les fondamentaux du langage SQL 3 - 15

Exercice 3 (suite)
S'il vous reste du temps, effectuez les exercices suivants :
7. Créez une interrogation permettant d'afficher le nom et le salaire de tous les
employés. Formatez le salaire de sorte qu'il contienne 15 caractères et soit complété à
gauche par le symbole $. Nommez la colonne SALARY.

8. Créez une interrogation permettant d'afficher les huit premiers caractères du nom des
employés, ainsi que le montant de leur salaire avec des astérisques. Chaque astérisque
représente un millier de dollars. Triez les données par salaire décroissant. Nommez la
colonne EMPLOYEES_AND_THEIR_SALARIES.

9. Créez une interrogation permettant d'afficher le nom et l'ancienneté en semaines de

tous les employés du département 90. Nommez TENURE la colonne contenant le
nombre de semaines. Tronquez le nombre de semaines de sorte qu'il n'y ait aucun
chiffre après la virgule. Affichez les enregistrements par ordre décroissant sur la base
de la colonne TENURE.
Remarque : La valeur TENURE diffère selon la date d'exécution de l'interrogation.

Oracle Database 11g : Les fondamentaux du langage SQL 3 - 16

Exercice 4
Indiquez la validité des trois affirmations ci-après. Entourez la réponse correcte.
1. Les fonctions de groupe opèrent sur plusieurs lignes et produisent un résultat par
groupe.
Vrai/Faux

2. Les fonctions de groupe prennent en compte les valeurs NULL dans les calculs.
Vrai/Faux

3. La clause WHERE restreint les lignes avant inclusion dans un calcul de groupe.
Vrai/Faux

Le département des ressources humaines a besoin des états suivants :

4. Déterminez le salaire le plus élevé, le salaire le plus bas, le salaire cumulé et le salaire
moyen pour tous les employés. Intitulez respectivement les colonnes Maximum,
Minimum, Sum et Average. Arrondissez les résultats à l'entier le plus proche.
Enregistrez l'instruction SQL dans le fichier lab_04_04.sql. Exécutez
l'interrogation.

5. Modifiez l'interrogation enregistrée dans le fichier lab_04_04.sql afin d'afficher

le salaire minimum, le salaire maximum, le salaire cumulé et le salaire moyen pour
chaque type de poste. Enregistrez lab_04_04.sql sous le nom
lab_04_05.sql. Exécutez l'instruction figurant dans lab_05_05.sql.

Oracle Database 11g : Les fondamentaux du langage SQL 4- 17

Exercice 4 (suite)
6. Ecrivez une interrogation permettant d'afficher le nombre de personnes occupant le
même poste.

Généralisez l'interrogation de sorte que l'utilisateur du département des ressources

humaines soit invité à indiquer un intitulé de poste. Enregistrez le script sous le nom
de fichier lab_04_06.sql. Exécutez l'interrogation. A l'invite, saisissez
IT_PROG.

7. Déterminez le nombre de managers sans les répertorier. Intitulez la colonne Number

of Managers. Indice : Utilisez la colonne MANAGER_ID pour déterminer le
nombre de managers.

8. Trouvez la différence entre le salaire le plus élevé et le salaire le plus bas. Intitulez la
colonne DIFFERENCE.

Oracle Database 11g : Les fondamentaux du langage SQL 4- 18

Exercice 4 (suite)
S'il vous reste du temps, effectuez les exercices suivants :
9. Créez un état permettant d'afficher le numéro de manager et le salaire de l'employé le
moins payé sous les ordres de ce manager. Excluez toute personne pour laquelle le
manager n'est pas connu. Excluez les groupes dans lesquels le salaire minimum est
inférieur ou égal à 6 000 $. Triez les résultats par ordre décroissant sur la base du
salaire.

Pour mettre en pratique vos connaissances de façon plus approfondie, effectuez les
exercices suivants :
10. Créez une interrogation permettant d'afficher le nombre total d'employés et, pour ce
total, le nombre d'employés embauchés en 1995, 1996, 1997 et 1998. Créez les en-
têtes de colonne appropriés.

11. Créez une interrogation de matrice permettant d'afficher le poste, le salaire

correspondant à ce poste sur la base du numéro de département et le salaire total
correspondant à ce poste, pour les départements 20, 50, 80 et 90, en intitulant chaque
colonne de façon appropriée.

Oracle Database 11g : Les fondamentaux du langage SQL 4- 19

Exercice 5
Le département des ressources humaines a besoin d'instructions SQL permettant d'insérer,
de mettre à jour et de supprimer des données relatives aux employés. Vous allez tester ces
instructions sur la table MY_EMPLOYEE avant de les livrer.
Remarque : Pour exécuter une interrogation comprenant des instructions LMD, utilisez
l'icône Run Script (ou appuyez sur [F5]). Vous pouvez ainsi voir les messages renvoyés
dans l'onglet Script Output. Pour les interrogations SELECT, continuez à utiliser l'icône
Execute Statement ou à appuyer sur [F9] pour obtenir le résultat mis en forme dans l'onglet
Results.
Insérez des données dans la table MY_EMPLOYEE.
1. Exécutez l'instruction du script lab_05_01.sql pour générer la table
MY_EMPLOYEE utilisée dans cet exercice.
2. Affichez la structure de cette table afin d'identifier les noms de colonne.

3. Créez une instruction INSERT permettant d'ajouter à la table MY_EMPLOYEE la

première ligne de données du tableau ci-après. N'énumérez pas les colonnes dans la
clause INSERT. N'entrez pas encore toutes les lignes.

ID LAST_NAME FIRST_NAME USERID SALARY

1 Patel Ralph rpatel 895

2 Dancs Betty bdancs 860

3 Biri Ben bbiri 1100

4 Newman Chad cnewman 750

5 Ropeburn Audrey aropebur 1550

4. Insérez dans la table MY_EMPLOYEE la deuxième ligne de données du tableau qui

précède. Cette fois, énumérez les colonnes de façon explicite dans la clause INSERT.

Oracle Database 11g : Les fondamentaux du langage SQL 5- 20

Exercice 5 (suite)
5. Vérifiez les ajouts effectués dans la table.

6. Ecrivez dans un fichier script réutilisable et dynamique une instruction INSERT

permettant de charger les lignes restantes dans la table MY_EMPLOYEE. Le script doit
afficher une invite pour toutes les colonnes (ID, LAST_NAME, FIRST_NAME,
USERID et SALARY). Enregistrez ce script sous le nom lab_05_06.sql.
7. Insérez dans la table les deux lignes suivantes du tableau de données fourni à l'étape 3
en exécutant l'instruction INSERT du script que vous avez créé.
8. Vérifiez les ajouts effectués dans la table.

9. Rendez définitifs les ajouts de données.

Mettez à jour et supprimez des données dans la table MY_EMPLOYEE.

10. Remplacez le nom de l'employé 3 par Drexler.
11. Remplacez par 1 000 $ le salaire de tous les employés qui ont un salaire inférieur
à 900 $.
12. Vérifiez les modifications apportées à la table.

13. Supprimez Betty Dancs de la table MY_EMPLOYEE.

14. Vérifiez les modifications apportées à la table.

Oracle Database 11g : Les fondamentaux du langage SQL 5- 21

Exercice 5 (suite)
15. Validez (commit) toutes les modifications en attente.
Contrôlez les transactions de données appliquées à la table MY_EMPLOYEE.
16. Insérez dans la table la dernière ligne du tableau de données fourni à l'étape 3, à
l'aide des instructions du script créé à l'étape 6. Exécutez ces instructions.
17. Vérifiez les ajouts effectués dans la table.

18. Marquez un point intermédiaire dans le traitement de la transaction.

19. Supprimez toutes les lignes de la table MY_EMPLOYEE.
20. Vérifiez que la table a bien été vidée.
21. Annulez l'opération DELETE la plus récente, mais pas l'opération INSERT
précédente.
22. Vérifiez que la nouvelle ligne est toujours intacte.

23. Rendez définitif l'ajout de données.

S'il vous reste du temps, effectuez l'exercice suivant :

24. Modifiez le script lab_05_06.sql de sorte que la valeur USERID soit générée
automatiquement par concaténation de la première lettre du prénom et des sept
premiers caractères du nom. La valeur USERID doit être générée en minuscules.
Ainsi, le script n'invitera pas à saisir cette valeur. Enregistrez le script sous le nom
lab_05_24.sql.
25. Exécutez le script lab_05_24.sql afin d'insérer l'enregistrement suivant :

ID LAST_NAME FIRST_NAME USERID SALAR

Y

6 Anthony Mark manthon 1230

y
26. Vérifiez que la nouvelle ligne a été ajoutée avec le USERID correct.

Oracle Database 11g : Les fondamentaux du langage SQL 5 - 22