Les employés - Exercice 2

Soit le schéma relationnel suivant :

EMP (EMPNO, ENAME, JOB, MGR*, HIREDATE, SAL, COMM, DEPTNO*)
DEPT (DEPTNO, DNAME, LOC)
SALGRADE (GRADE, LOSAL, HISAL)

Remarques

• La relation EMP représente des employés.

  • Un employé est identifié par son numéro EMPNO, son nom ENAME, son emploi JOB, le numero de son chef hierarchique MGR, sa date d’embauche HIREDATE, son salaire mensuel SAL, le montant de ses commissions mensuelles COMM et le département auquel il appartient DEPTNO.
  • MGR est une clé étrangère qui référence le numéro d'un autre employé.
  • DEPTNO est une clé étrangère qui référence le numéro d'un département.

• La relation DEPT représente des départements.

  • Un département est identifié par son numéro DEPTNO, son nom DNAME et la ville LOC dans laquelle il se situe.

• La relation SALGRADE représente des grilles de salaire.

  • Les grilles de salaire sont identifiées par GRADE, le salaire minimum de la grille LOSAL et le salaire maximum HISAL.

Diagramme

erDiagram

EMP {
  string EMPNO
  string ENAME
  string JOB
  string MGR
  date HIREDATE
  float SAL
  float COMM
  string DEPTNO
}

DEPT {
  string DEPTNO
  string DNAME
  string LOC
}

SALGRADE {
  string GRADE
  float LOSAL
  float HISAL
}

EMP }o--|| EMP : "Manager"
EMP ||--o{ DEPT : "Département"

Données

DEPT
DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

SALGRADE
GRADE	LOSAL	HISAL
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

EMP
EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	2000-12-17	800		20
7499	ALLEN	SALESMAN	7698	1999-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1991-02-22	1250	500	30
7566	JONES	MANAGER	7839	2000-04-02	2975		20
7654	MARTIN	SALESMAN	7698	1998-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1997-05-01	2850		30
7782	CLARK	MANAGER	7839	1996-06-09	2450		10
7788	SCOTT	ANALYST	7566	1995-12-09	3000		20
7839	KING	PRESIDENT		1994-11-17	5000		10
7844	TURNER	SALESMAN	7698	1998-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1993-01-12	1100		20
7900	JAMES	CLERK	7698	2000-12-03	950		30
7902	FORD	ANALYST	7566	1992-12-03	3000		20
7934	MILLER	CLERK	7782	1999-01-23	1300		10

Téléchargement de la base : employes.sqlite

Exercice 2 : Requêtes SQL

Pour chaque requête de l’exercice 1, donner la ou les réponse(s) correspondante(s) en SQL.

Pour les requêtes 10, 11 et 12, on pourra définir des vues qui permettent de définir différents rangs : rang 1 pour le boss, rang 2 pour ses employés directs, etc.

Une vue (view en anglais) est une requête SFW stockée sous forme de table temporaire. Une vue peut être requêtée comme une table classique avec des requêtes SFW.

1. Donner le nom de tous les employés.

2. Donner le nom et la date d’embauche des employés du département 20.

3. Donner tous les salaires perçus par les employés de l’entreprise.

4. Donner le nom et le numéro du département des employés travaillant à Dallas.

5. Donner le nom et le salaire des employés dont le nom commence par un M et dont le salaire est supérieur à 1290$.

6. Donner les départements employant des CLERK, SALESMAN et des ANALYST.

7. Donner le nom des chefs dont les employés perçoivent des commissions.

8. Donner le nom et le salaire des employés des départements de Chicago et Dallas dont le salaire est supérieur à 1000$.

9. Donner le nom des employés qui gagnent plus que leur chef.

10. Donner la hiérarchie de l’entreprise

11. Donner le nombre d’employés par niveau de hiérarchie.

12. Donner la moyenne des salaires par niveau de hiérarchie.