Les employés - Exercice 3

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 3 : Variantes des requêtes

Pour chacune des requêtes de la partie 1, on vous demande de réaliser plusieurs variantes.

Les variantes à effectuer sont les suivantes :

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

   1. par ordre alphabétique
   2. par ordre alphabétique inverse
   3. en changeant le nom de la colonne ENAME par NOM de L'EMPLOYÉ

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

   1. par ordre chronologique (du plus ancien au plus récent).
   2. par ordre chronologique (du plus récent au plus ancien).
   3. en changeant le format de la date d’embauche en affichant que les années (Instruction à utiliser : strftime ).

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

   1. par ordre croissant de salaire.
   2. par ordre alphabétique des employés.

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

   1. en associant leur job par ordre alphabétique.
   2. en utilisant les commandes upper et lower.

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$ :

   1. par ordre alphabétique.
   2. par ordre de salaire décroissant.
   3. une version où seul le salaire intervient (c.à.d sans comptabiliser les commissions)
   4. une version où le salaire et les commissions interviennent (c.à.d en comptabilisant les commissions)

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

   1. Pas d’alternative pour cette requête

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

   1. indiquer leur nom et leur numéro de département par ordre croissant de département.

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

   1. en prenant en compte le salaire et les commissions cumulés.
   2. en prenant en compte le salaire et les commissions cumulés par ordre décroissant, puis par ordre alphabétique des employés.

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

   1. indiquer leur nom et leur revenu, leur service, le nom et le revenu de leur chef.