CORRIGE REVISIONS-1 – CAS EMPLOYES

Manipulation des données et langage de définition de données

Création des tables :

create table dept

(deptno numeric(2) constraint pk_dept primary key,

dname varchar(30) not null,

loc varchar(20) not null);

create table emp

(empno numeric(4) constraint pk_emp primary key,

ename varchar(30) not null,

job varchar(30) not null,

mgr numeric(4),

hiredate date not null,

sal numeric (8,2) not null,

comm numeric(8,2),

deptno numeric(2) not null constraint fk_emp references dept(deptno));

Ecrire et exécuter les requêtes ci-dessous : 

1) Obtenir les départements (numéro, nom) ayant des employés.

Select d.deptno, dname

From dept d, emp e

Where e.deptno = d.deptno ;

Ou

Select d.deptno, dname

From dept d

Inner join emp e On e.deptno = d.deptno;

2) Donner la liste des employés (numéro, nom) non vendeurs.

Select empno, ename

From emp

Where job != 'SALESMAN';

3) Donner les employés embauchés entre le 01-01-1981 et le 30-06-81.

Select empno, ename

From emp

Where hiredate between '1981-01-01' and '1981-06-30';

4) Donner le salaire le plus élevé et le salaire le plus bas par département et job.

Select dname, job, max(sal) as Maximum, min(sal) as Minimum

From emp e

Inner join dept d On e.deptno = d.deptno

Group by dname, job;

5) Donner pour chaque département : son nom, le nombre d’employés et la moyenne des salaires.

Select dname, count(*), avg(sal)

From emp e

Inner join dept d On e.deptno = d.deptno

Where e.deptno = d.deptno

Group by dname;

6) Donner le montant total des commissions des vendeurs.

Select sum(comm) as Total_Commissions

From emp

Where job = ' SALESMAN';

7) Donner La liste des employés (son nom, sa fonction et le nom de son département) par ordre alphabétique.

Select ename, job, dname

From emp e

Inner Join dept d On e.deptno = d.deptno

Order by ename;

8) Donner le nom et le job de la dernière personne embauchée dans le département ‘ACCOUNTING’.

Select ename, job

From emp e

Inner Join dept d On e.deptno = d.deptno

And dname='ACCOUNTING'

And hiredate =

        (select max(hiredate)

        From emp e

Inner Join dept d On e.deptno = d.deptno

And dname='ACCOUNTING');

9) Lister les employés (no, nom) dont le salaire est supérieur à la moyenne des salaires.

Select empno, ename

From emp

Where sal >

        (select avg(sal)

from emp);

10) Donner le nombre d’employés n’ayant pas de responsable.

Select count(*)

From emp

Where mgr is null;

11) Donner la liste des employés (numéro, nom et job) avec le nom de leur responsable.

Select e1.empo, e1.ename, e1.job, e2.ename

From emp e1

Inner Join emp e2 On e1.mgr = e2.empo

12) On peut constater que le chef ne figure pas dans la liste de la question précédente (c'est logique car il n'a pas de responsable), il est demandé de l'ajouter dans cette liste (penser à UNION).

Select e1.empo, e1.ename, e1.job, e2.ename

From emp e1

Inner Join emp e2 On e1.mgr = e2.empo

Union

Select empno, ename, job, NULL

From emp

Where mgr is NULL;

13) Donner la liste des employés dont le nom commence par A.

Select empno, ename

From emp

Where ename like ' A%';

14) Donner la liste des employés (numéro, nom) avec leur salaire total ordonnée sur le nom de l'employé ; attention si on ajoute NULL à un nombre, le résultat vaut NULL.

Select empno, ename, sal

From emp

Where job != ' SALESMAN'

Union

Select empno, ename, sal + comm

From emp

Where job = ' SALESMAN'

Order by ename;

15) Augmenter les salaires des vendeurs de 1%.

Update emp

Set sal = sal *1.01

Where job = 'SALESMAN';

16) Insérer un département (la seule contrainte est que le numéro doit être égal au dernier + 10).

Insert into dept values(

(Select (max(d.deptno) + 10) from dept d), 'INFORMATIQUE', 'NEW-YORK');