Université de Tunis El Manar

Faculté des Sciences de Tunis Correction 2011-2012

Département des Sciences de l’Informatique

FICHIER & BASES DE DONNEES

TD4 IF4

Soit le schéma relationnel de la base de données :

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

Exprimer les requêtes suivantes en SQL :

1. Ecrivez une requête pour afficher le nom, le numéro de département et le département de tous
les employés.
SQL> SELECT e.ename, e.deptno, d.dname
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno;

2. Créez une liste unique de tous les postes du département 30.

SQL> SELECT DISTINCT e.job, d.loc
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND e.deptno = 30;

3. Ecrivez une requête pour afficher le nom, le nom du département et la localisation de tous les
employés qui touchent une commission.
SQL> SELECT e.ename, d.dname, d.loc
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND e.comm IS NOT NULL;

4. Affichez le nom et le nom du département pour tous les employés dont le nom contient la
lettre 'A'.
SQL> SELECT e.ename, d.dname
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND e.ename LIKE '%A%';

5. Ecrivez une requête pour afficher le nom, le poste, le numéro de département et le nom du
département de tous les employés basés à 'DALLAS'.
SQL> SELECT e.ename, e.job, e.deptno, d.dname
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND d.loc = 'DALLAS';
6. Affichez le nom et le matricule des employés et de leur manager. Nommez les colonnes
Employés, N° Emp, Manager, et N° Mgr, respectivement.
SQL> SELECT e.ename "Employés", e.empno "N° Emp",
2 m.ename Manager, m.empno "N° Mgr"
3 FROM emp e, emp m
4 WHERE e.mgr = m.empno;

7. Modifiez la requête (6) pour afficher tous les employés, y compris King, n'ayant pas de manager.
SQL> SELECT e.ename "Employés", e.empno "N° Emp",
2 m.ename Manager, m.empno "N° Mgr"
3 FROM emp e, emp m
4 WHERE e.mgr = m.empno(+);

8. Créez une requête pour afficher le numéro de département et le nom de tous les employés qui
travaillent dans le même département qu'un certain employé. Donnez à chaque colonne un en-
tête approprié.
SQL> SELECT e.deptno "Département", e.ename "Employé",
2 c.ename "Collègue"
3 FROM emp e, emp c
4 WHERE e.deptno = c.deptno
5 AND e.empno <> c.empno
6 ORDER BY e.deptno, e.ename, c.ename;

9. Revenez à la structure de la table SALGRADE. Créez une requête pour afficher le nom, le
poste, le département, le salaire et l'échelon de tous les employés.
SQL> SELECT e.ename, e.job, d.dname, e.sal, s.grade
2 FROM emp e, dept d, salgrade s
3 WHERE e.deptno = d.deptno
4 AND e.sal BETWEEN s.losal AND s.hisal;

10. Créez une requête pour afficher le nom et la date d'embauche de tous les employés arrivés
avant l'employé 'Blake'. Trier les lignes sur la date d’embauche.
SQL> SELECT emp.ename, emp.hiredate
2 FROM emp, emp blake
3 WHERE blake.ename = 'BLAKE'
4 AND blake.hiredate > emp.hiredate
5 ORDER BY emp.hiredate;

11. Affichez les noms et date d'embauche des employés et de leur manager, pour tous les employés
ayant été embauchés avant leur manager. Nommez les colonnes Employé, Date Embauche Emp.,
Manager, Date Embauche Mang., respectivement.
SQL> SELECT e.ename "Employé", e.hiredate "Date Embauche Emp.",
2 m.ename "Manager", m.hiredate "Date Embauche Mang."
3 FROM emp e, emp m
4 WHERE e.mgr = m.empno
5 AND e.hiredate < m.hiredate;
 Université de Tunis El Manar
Faculté des Sciences de Tunis Correction 2011-2012
Département des Sciences de l’Informatique

FICHIER & BASES DE DONNEES

TD5 IF4

Soit le schéma relationnel de la base de données :

EMP (EMPNO, ENAME, JOB, #MGR, HIREDATE, SAL, COMM, #DEPTNO)
DEPT (DEPTNO, DNAME, LOC)

Déterminez si les affirmations suivantes sont vraies ou fausses :

1. Les fonctions de groupe agissent sur plusieurs lignes pour produire un seul résultat ?
(Vrai)

2. Les fonctions de groupe intègrent les valeurs NULL dans leurs calculs ?
(Faux) Les fonctions de groupe ignorent les valeurs NULL. Si vous
voulez inclure des valeurs NULL, utilisez la fonction NVL.

3. La clause WHERE restreint les lignes avant qu'elles soient incluses dans un calcul de groupe ?
(Vrai)

Exprimer les requêtes suivantes en SQL :

4. Affichez le salaire maximum, le salaire minimum, la somme des salaires et le salaire moyen de
tous les employés. Nommez respectivement les colonnes Maximum, Minimum, Somme et Moyenne.
Arrondissez les résultats à zéro décimale.
SQL> SELECT ROUND(MAX(sal),0) "Maximum",
2 ROUND(MIN(sal),0) "Minimum",
3 ROUND(SUM(sal),0) "Somme",
4 ROUND(AVG(sal),0) "Moyenne"
5 FROM emp;

5. Modifiez la requête (4) pour afficher le salaire maximum, le salaire minimum, la somme des
salaires et le salaire moyen pour chaque type de poste.
SQL> SELECT job, ROUND(MAX(sal),0) "Maximum",
2 ROUND(MIN(sal),0) "Minimum",
3 ROUND(SUM(sal),0) "Somme",
4 ROUND(AVG(sal),0) "Moyenne"
5 FROM emp
6 GROUP BY job

6. Ecrivez une requête pour afficher le nombre de personnes qui occupent le même poste.
SQL> SELECT job, COUNT(*)
2 FROM emp
3 GROUP BY job;
7. Déterminez le nombre de personnes ayant des subordonnés, sans en donner la liste. Nommez
la colonne "Nombre de Chefs".
SQL> SELECT COUNT(DISTINCT mgr) "Nombre de Chefs"
2 FROM emp;

8. Ecrivez une requête pour afficher la différence existant entre le salaire maximum et le salaire
minimum. Nommez la colonne DIFFERENCE.
SQL> SELECT MAX(sal) - MIN(sal) DIFFERENCE
2 FROM emp;

9. Affichez le matricule des différents managers et le niveau de salaire le plus bas de leurs
employés. Excluez toute ligne où le manager n'est pas identifié. Excluez tout groupe dans lequel
le salaire minimum est inférieur à 1 000. Triez les résultats par ordre décroissant des salaires.
SQL> SELECT mgr, MIN(sal)
2 FROM emp
3 WHERE mgr IS NOT NULL
4 GROUP BY mgr
5 HAVING MIN(sal) > 1000
6 ORDER BY MIN(sal) DESC;

10. Ecrivez une requête pour afficher le nom du département, la localisation, le nombre d'employés
et le salaire moyen pour tous les employés de ce département. Nommez les colonnes
Département, Localisation, Nombre d’Employés et Salaire, respectivement.
SQL> SELECT d.dname "Département", d.loc "Localisation",
2 COUNT(*) "Nombre d''Employés",
3 AVG(e.sal) "Salaire"
4 FROM emp e, dept d
5 WHERE e.deptno = d.deptno
6 GROUP BY d.dname, d.loc;

11. Créez une requête pour afficher le nombre total d'employés puis, parmi ces employés, ceux qui
ont été embauchés en 1980, 1981, 1982 et 1983. Nommez les colonnes de façon appropriée.
SQL> SELECT COUNT(*) total,
2 SUM(DECODE(TO_CHAR(hiredate, 'YYYY'),1980,1,0))"1980",
3 SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1981,1,0))"1981",
4 SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1982,1,0))"1982",
5 SUM(DECODE(TO_CHAR(hiredate, 'YYYY'),1983,1,0))"1983"
6 FROM emp;

12. Créez une requête pour afficher les postes, le salaire de ces postes par numéro de département
et le salaire total de ces postes incluant tous les départements. Nommez les colonnes de façon
appropriée.
SQL> SELECT job Poste,
2 SUM(DECODE(deptno, 10, sal)) "Dept 10",
3 SUM(DECODE(deptno, 20, sal)) "Dept 20",
4 SUM(DECODE(deptno, 30, sal)) "Dept 30",
5 SUM(sal) "Total"
6 FROM emp
7 GROUP BY job;