Matière : Atelier Base de Données Enseignante : Mme BOUGHAMOURA

Hela
Direction Générale des Niveau:
Etudes Technologique MDW31
TP2 : Ecriture des requêtes
Iset Mahdia Année universitaire
SQL 2019/2020
Département TI Semestre 1

Il faudra garder une trace du travail fait en TP : pensez à sauvegarder dans des fichiers vos requêtes
ainsi que les résultats.

Schémas des tables de HR

Dans la version Oracle installée, il existe un utilisateur nommé HR propriétaire d’un ensemble de
tables accessibles à tout le monde dont les schémas sont les suivants :

Table EMPLOYEES
Nom NULL ? Type
------------------ ------- -------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
Table COUNTRIES
Nom NULL ? Type
------------------ ------- -------------------
COUNTRY_ID NOT NULL CHAR(2)
COUNTRY_NAME VARCHAR2(40)
REGION_ID NUMBER

1
 Table DEPARTMENTS
Nom NULL ? Type
------------------ ------- -------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)

Table JOBS
Nom NULL ? Type
------------------ ------- -------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)

Table JOB_HISTORY
Nom NULL ? Type
------------------ ------- -------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
START_DATE NOT NULL DATE
END_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)

Table LOCATIONS
Nom NULL ? Type
------------------ ------- -------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE CITY VARCHAR2(12)
STATE_PROVINCE NOT NULL VARCHAR2(30)
COUNTRY_ID VARCHAR2(25)
CHAR(2)

Table REGIONS
Nom NULL ? Type
------------------ ------- -------------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)

2
 I. Requêtes d’interrogation des tables de HR

Ecrire puis exécuter les requêtes permettant de répondre aux questions suivantes sur les tables de HR.
Ne pas oublier pas qu’elles appartiennent à l’utilisateur HR. De ce fait, dans les requêtes, il faut
préfixer le nom des tables par HR.

1- Afficher les noms des employés dont le salaire est supérieur à 10 000 $. (15)
2- Afficher les noms des employés dont la date d’embauche est comprise entre 17/02/1997 et
30/10/1997. (21)
3- Afficher les noms des employés commençant par la lettre ‘J’. (2)
4- Afficher les noms des employés dont le nom contient deux fois la lettre ‘a’. (10)
5- Afficher les noms des employés dont le numéro du chef est 114. (5)
5- Afficher les noms des services dont le numéro du chef est 114 ou qui n’ont pas de chef. (1)
6- Afficher les noms des services qui ne sont pas localisés à ‘Seattle’. (6)
7- Afficher les noms des employés et leur commission. Afficher ‘pas de commission’ dans la
deuxième colonne lorsque c’est le cas. Ordonner la liste par rapport aux noms. (107)

Pour la requête précédentes, deux fonctions sont utilisables :

- La fonction NVL (exp1, exp2) qui

a. retourne exp1 si exp1 ne vaut pas null

Il faut que les types de exp1 et exp2 soient compatibles.

- La fonction TO_CHAR (exp [,format]) convertit l’expression numérique exp (dont on peut préciser
le format) en chaîne de caractères.

3
 8-Afficher, par ordre alphabétique, les noms des employés qui ont un salaire supérieur au salaire
moyen. (51)
9- Afficher les noms des employés qui ont au moins une personne sous leurs ordres. (18)

10- 11- Afficher les noms des services dans lesquels il n’y a aucun employé. (16)
12- Donner le nombre d’employés pour chaque poste. (19)
13- Afficher, pour tous les employés qui ont été embauchés avant ‘Weiss’, leur nom, l’adresse et la
ville de leur service. (24)
14- Pour chaque poste, donner le nombre d’employés dont le salaire se trouve entre le minimum et
le maximum des salaires prévus pour ce poste. (19)
15- Pour chaque poste, donner la somme des salaires lorsque cette somme est inférieure à 10000$.
(5).

4
5