1 Algèbre - SQL : Employés - Départements
Soit la base de données suivante :
EMP(ENO, ENOM, PROF, DATEEMB, SAL, COMM, DNO)
ENO : numéro d’employé, clé primaire
ENOM : nom de l’employé
PROF : profession (directeur n’est pas une profession)
DATEEMB : date d’embauche
SAL : salaire
COMM : commission (un employé peut ne pas avoir de commission)
DNO : numéro de département auquel appartient l’employé
DEPT(DNO, DNOM, DIR, VILLE)
DNO : numéro de département, clé primaire
DNOM : nom du département
DIR : directeur du département (clé étrangère → EMP(ENO))
VILLE : lieu du département (ville)
1.1 Interrogation d’une seule relation
1. Donner tous les n-uplets de DEPT.
Correction : Algèbre : DEP T
SQL :
SELECT * FROM DEPT;
2. Donner tous les n-uplets de EMP.
Correction : Algèbre : EM P
SQL :
SELECT * FROM EMP;
3. Donner les noms et les salaires des employés.
Correction : Algèbre : πEN OM,SAL (EM P )
SQL :
SELECT ENOM, SAL
FROM EMP;
4. Donner les professions des employés (après élimination des duplicats).
Correction : Algèbre : πP ROF (EM P )
SQL :
SELECT DISTINCT PROF
FROM EMP;
5. Donner les dates d’embauche des techniciens.
Correction : Algèbre : πDAT EEM B (σP ROF =0 T ECHN ICIEN 0 (EM P ))
SQL :
1
SELECT DATEEMB
FROM EMP
WHERE PROF=’TECHNICIEN’;
1.2 Jointures
6. Faire le produit cartésien entre EMP et DEPT.
Correction : Algèbre : EM P × DEP T
SQL :
SELECT *
FROM EMP, DEPT;
7. Donner les noms des employés et les noms de leur département.
Correction : Algèbre : πEN OM,DN OM (EM P o
n DEP T )
SQL :
SELECT ENOM, DNOM
FROM EMP, DEPT
WHERE [Link]=[Link];
8. Donner les numéros des employés travaillant à BOSTON.
Correction : Algèbre : πEN O (EM P o
n σV ILLE=0 BOST ON 0 (DEP T ))
SQL :
SELECT ENO
FROM EMP, DEPT
WHERE [Link]=[Link] AND VILLE=’BOSTON’;
9. Donner les noms des directeurs des départements 1 et 3. Attention : directeur n’est pas une profession !
Correction : Algèbre : πEN OM (σDN O=1∨DN O=3 (DEP T ) o
nDIR=EN O EM P )
SQL :
SELECT ENOM
FROM EMP, DEPT
WHERE ([Link]=1 OR [Link]=3) AND DIR = ENO;
ou
SELECT ENOM
FROM EMP, DEPT
WHERE [Link] IN (1,3) AND DIR = ENO;
10. Donner les noms des employés travaillant dans un département avec au moins un ingÃľnieur.
Correction : Algèbre :
R1 := πDN O (σP ROF =0 IN GEN IEU R0 (EM P ))
R2 := πEN OM (EM P o
n R1)
SQL :
2
SELECT [Link]
FROM EMP E1, EMP E2
WHERE [Link] = [Link]
AND [Link] = ’INGÃĽNIEUR’;
11. Donner le salaire et le nom des employés gagnant plus qu’un (au moins un) ingÃľnieur.
Correction : Algèbre :
R1 := ρSAL/SAL1 (πSAL (σP ROF =0 IN GEN IEU R0 (EM P )))
R2 := πEN OM,SAL (EM P o
nSAL>SAL1 R1)
SQL :
SELECT [Link], [Link]
FROM EMP E1, EMP E2
WHERE [Link]=’INGENIEUR’
AND [Link] > [Link];
ou
SELECT ENOM, SAL FROM EMP
WHERE SAL > ANY (SELECT SAL
FROM EMP
WHERE PROF=’INGENIEUR’);
12. Donner le salaire et le nom des employés gagnant plus que tous les ingÃľnieurs.
Correction : SQL :
SELECT ENOM, SAL FROM EMP
WHERE SAL > ALL (SELECT SAL
FROM EMP
WHERE PROF=’INGENIEUR’);
13. Donner les noms des employés et les noms de leurs directeurs.
Correction : Algèbre :
R1 := ρEN OM/DIRN OM (πEN OM,DN O (EM P o
nEN O=DIR DEP T ))
R2 := πEN OM,DN O EM P
R3 := πEN OM,DIRN OM (R1 o
n R2)
— R1(DIRNOM,DNO) : les départements avec les noms de leur directeur
— R2(ENOM,DNO) : les employés avec les numéros de leur département
— R3(ENOM,DIRNOM) : les employés (ENOM) avec les noms de leur directeur
SQL :
SELECT [Link], [Link]
FROM EMP E1, EMP E2, DEPT D
WHERE [Link]=[Link] AND [Link] = [Link];
14. Trouver les noms des employés ayant le même directeur que JIM. Attention : un employé peut être
directeur de plusieurs départements.
Correction : Algèbre :
R1 := πDIR (σEN OM =0 JIM 0 (EM P ) o
n DEP T )
R2 := DEP T o
n R1
R3 := πEN OM (σEN OM <>0 JIM 0 (EM P ) o
n R2)
3
— R1(DIR) : le numéro du directeur de JIM
— R2(...) : les départements avec le même directeur
— R3(ENOM) : les noms des employés ayant le même directeur que JIM.
SQL :
SELECT ENOM
FROM EMP
WHERE ENOM <> ’JIM’
AND DNO IN (SELECT [Link]
FROM EMP,
DEPT D1, DEPT D2
WHERE ENOM=’JIM’
AND [Link] = [Link]
AND [Link] = [Link]);
15. Donner le nom et la date d’embauche des employés embauchés avant leur directeur ; donner également
le nom et la date d’embauche de leur directeur.
Correction : Algèbre :
R1 := πDN O,EN OM,DAT EEM B (EM P o
nDIR=EN O DEP T )
R2 := ρEN OM/DIRN OM,DAT EEM B/DIRDAT E (R1)
R3 := πEN OM,DAT EEM B,DIRN OM,DIRDAT E (σDIRDAT E<DAT EEM B (EM P o
n R2))
— R1(DNO,ENOM,DATEEMB) : le nom et la date d’embauche du directeur du dept. DNO.
— R2(DNO,DIRNOM,DIRDATE) : renommage des attributs
— R3(ENOM,DATEEMB,DIRNOM,DIRDATE) : résultat
SQL :
SELECT [Link], [Link], [Link], [Link]
FROM EMP E1, EMP E2, DEPT D
WHERE [Link]=[Link]
AND [Link]=[Link]
AND [Link]<[Link];
16. Donner les départements qui n’ont pas d’employés.
Correction : Algèbre : DEP T − (DEP T n EM P )
SQL :
SELECT *
FROM DEPT
WHERE DNO NOT IN (SELECT DNO FROM EMP);
17. Donner les noms des employés du département COMMERCIAL embauchés le même jour qu’un em-
ployé du département PRODUCTION.
Correction : Algèbre :
R1 := πDAT EEM B (EM P o
n σDN OM =0 P RODU CT ION 0 (DEP T ))
R2 := πEN OM ((EM P o
n σDN OM =0 COM M ERCIAL0 DEP T ) o
n R1)
SQL :
SELECT DISTINCT ENOM
FROM EMP E1, DEPT D1, EMP E2, DEPT D2
WHERE [Link]=[Link]
4
AND [Link]=[Link]
AND [Link]=’COMMERCIAL’
AND [Link]=’PRODUCTION’
AND [Link]=[Link]
ou
SELECT ENOM
FROM EMP, DEPT
WHERE [Link]=[Link]
AND DNOM=’COMMERCIAL’
AND DATEEMB IN (SELECT DATEEMB
FROM EMP, DEPT
WHERE [Link]=[Link]
AND DNOM=’PRODUCTION’);
18. Donner les noms des employés embauchés avant tous les employés du département 1.
Correction : Algèbre :
R1 := ρDAT EEM B/DAT E1 (πDAT EEM B (σDN O=1 (EM P )))
R2 := πEN OM (EM P − (EM P nDAT EEM B>=DAT E1 R1))
SQL :
SELECT ENOM
FROM EMP
WHERE DATEEMB < ALL (SELECT DATEEMB
FROM EMP
WHERE DNO=1);
19. Donner les noms des employés ayant le même emploi et le même directeur que JOE.
Correction : Algèbre :
R1 := πDIR,P ROF (σEN OM =0 JOE 0 (EM P ) o
n DEP T )
R2 := πEN OM ((EM P o
n DEP T ) o
n R1)
— R1(DIR,PROF) : le directeur de Joe et sa profession
— R2(ENOM) : résultat
SQL :
SELECT ENOM
FROM EMP, DEPT
WHERE ENOM <> ’JOE’
AND [Link] = [Link]
AND (PROF, DIR) = (SELECT PROF, DIR
FROM EMP, DEPT
WHERE ENOM=’JOE’
AND [Link] = [Link]);
1.3 Valeurs Nulles, Tris, Groupes, Agrégats et Expressions
20. Donner la liste des employés ayant une commission.
Correction :
5
SELECT *
FROM EMP
WHERE COMM IS NOT NULL;
21. Donner les noms, emplois et salaires des employés par emploi croissant et, pour chaque emploi, par
salaire décroissant.
Correction :
SELECT ENOM, PROF, SAL
FROM EMP
ORDER BY PROF ASC, SAL DESC;
22. Donner le salaire moyen des employés.
Correction :
SELECT AVG(SAL) AS ’SALAIRE MOYEN’
FROM EMP;
23. Donner le nombre d’employés du département PRODUCTION.
Correction :
SELECT COUNT(EMP.*)
FROM EMP, DEPT
WHERE [Link] = [Link]
AND [Link] = ’PRODUCTION’;
24. Les numÃľros de dÃľpartement et leur salaire maximum ?
Correction :
SELECT DNO, MAX(SAL)
FROM EMP
GROUP BY DNO;
25. Donner les noms des employés ayant le salaire maximum de chaque département.
Correction :
SELECT ENOM
FROM EMP
WHERE (DNO, SAL) IN (SELECT DNO, MAX(SAL)
FROM EMP
GROUP BY DNO);
ou
SELECT ENOM
FROM EMP E
WHERE SAL = (SELECT MAX(SAL)
FROM EMP F
WHERE [Link] = [Link]);
26. Les professions et leur salaire moyen ?
Correction :
SELECT PROF, AVG(SAL)
FROM EMP
GROUP BY PROF;
6
27. Le salaire moyen le plus bas (par profession) ?
Correction :
SELECT MIN(AVG(SAL))
FROM EMP
GROUP BY PROF;
28. Donner les emplois ayant le salaire moyen le plus bas ; donnez aussi leur salaire moyen.
Correction :
SELECT PROF, AVG(SAL)
FROM EMP
GROUP BY PROF
HAVING AVG(SAL) = (SELECT MIN(AVG(SAL))
FROM EMP
GROUP BY PROF);
