Sousrequêtes

Les objectives du cet chapitre sont:

- on établit les types des problèmes que sont résolues avec les sousrequêtes;

- on définire les sousrequêtes;

- les types des sousrequêtes;

- on écrit des sousrequêtes mono ligne et multiple;

1. Problemes que sont résolues avec les sousrequêtes

L’utilisation des sousrequêtes, est équivalente a deux demandes séquentielles. Le placement d’une sousrequête dans une requête principale prend le résultat obtenu après on a résolue la sousrequête et l’on avait utilise dans la requête principale.

2.la syntaxe d’une sousrequête

Une sousrequête est une déclaration SELECT, qui est comprise dans l’intérieure d’une autre déclaration SELECT. La sousrequête est nommée SELECT intérieur.

SELECT la liste

FROM le tableau

WHERE l’opérateur

( SELECT la liste

FROM le tableau)

L’ opérateur = comparaison du type >, =, IN

Les opérateurs de comparaison sont divisées en deux classes :les opérateurs mono ligne (>,=,>=,<,<>,<=) et des opérateurs multiligne (IN, ANY, ALL);

- La sousrequête doit être introduite entre des parenthèses.

- La sousrequête doit être placée dans la partie droite de la comparaison

- La clause ORDER BY doit être utilisée seulement si on réalise l’analyse Top-N

- Les opérateurs mono ligne doivent être utilisées avec des requêtes et des opérateurs multiligne avec des sousrequêtes des plusieurs multilignes.

Exemple1: afficher le nomme d’employée qui a le salaire plus grand que l’employée Jones.

Pour résoudre cette problème est nécessaire d’utiliser 2 sousrequêtes : l’une qui donne comme résultat le salaire de JONES et l’autre qui donne les nommes des employées qui ont le salaire plus grand que Jones. On peut obtenir le résultat désire par la combinaison des les 2 requêtes par introduire une requête dans l’intérieur de l’autre.

3. Types des sousrequêtes

Les sousrequêtes peuvent être classifier en requêtes mono ligne et multiligne, en fonction du résultat lequel est retourne.

Les requêtes mono ligne sont des requêtes qui fournissent comme résultat une ligne et les requêtes multiligne retournent plusieurs lignes.

Les opérateurs utilises pour les requêtes mono ligne sont : =, >, >=, <, <=, <>.

Exemple2: afficher les nommes des employées qui ont la même profession avec l’employé Clark.

La demande intérieure donne la métier de Clark(manager), et l’extérieure donne les nommes des touts les employées qui ont cette profession (Jones, Blake, Clark).

L’ordre d’exécution des instructions est la suivante:

- on trouve la profession d’employé Clark (manager)

- après sont affiches tout les employées qui ont la métier manager.

4. Quand on utilise les fonctions de group dans les sous requêtes

Les fonctions de group sont utilisées dans les sous requêtes pour obtenir une valeur qui va servire comme élément de comparaison dans la requête principale.

Exemple3: afficher les nommes des salaries soubordones au manager King avec le salaire supérieur au salaire maximale du département 30.

La sousrequête qui utilise comme fonction de group MAX donne une seule valeur (2450) prélevée de la requête principale.

5. La clause HAVING dans les sous requêtes

Une autre clause que peut être utilisée avec la clause est la clause HAVING (après on a grouper avec GROUP BY).La première exécution de la sousrequête est de la requête. On va trouver le résultat dans la clause HAVING de la requête principale.

Exemple4: afficher le département avec le plus grand salaire moyenne.

6. Des exercices :

1. afficher le nomme, le salaire et le supérieur des employées avec le salaire plus grande que le salaire maximale du département 30 et qui n’ont pas le supérieur Clark

2. afficher l’emploi pour laquelle le salaire moyenne est minimum.

3: afficher les nommes des salaries employées dans la même jour que Ward et qui ont le salaire plus grand que Ward.

4. combien des employées du département 30 sont les subordonnes de King?

7. Des erreurs dans les sous requêtes:

Il y a une erreur aux sousrequêtes,tres commune, qui retourne plusieurs lignes pour une sousrequête d’une seule ligne, mais elle peut être correctee par changer l’opérateur « = » dans « I N », dans l ‘exemple suivant :

select empno,ename from emp

2 where sal=(select min(sal)

3 from emp

4 group by deptno);

et le résultat est : where sal=(select min(sal)

*

ERROR at line 2:

ORA-01427: single-row subquery returns more than one row

Après la correction on a:

Une autre erreur on a quand dans une sousrequête inclue on demande d ‘afficher quelque chose que n’existe pas dans la base de données, et aucune ligne est sélectionnée 

Par exemple les lignes suivants sont correctes mais elle ne retournent pas rien :

SQL> select ename,job

2 from emp

3 where job=(select job from emp

4 where ename='jones');

no rows selected

8. Les sous requêtes qui retournent plusieurs lignes

Elles utilisent des opérateurs de comparaison pour plusieurs lignes comme :

IN c’est le même pour n ‘importe quel membre de la liste

ANY fait une comparaison de la valeur avec chaque valeur retournée par la sousrequête

ALL fait une comparaison de la valeur avec quelle que valeur retournée par la sousrequête

Par exemple :

On demande de trouver tout les employées qui ont le même salaire minimum dans chaque département. S’exécute en avant la sousrequête de l’intérieur.

L’opérateur IN :

L’opérateur ANY fait une comparaison d’une valeur avec chaque valeur retournée par la sousrequête. Dans l’exemple suivant on va afficher tout les employées qui ne sont pas « analyste » et qui on un salaire plus petit qu’un employé qui a la fonction :analyste ».le salaire maximum pour un « analyste » est 3000.

Exemple :

>ALL signifie plus que maximum, et <ALL signifie peu que minimum.

L’opérateur NOT ne peut pas être utilise avec les opérateurs ANY, IN ou ALL.

9. L’existence d’une valeur nulle dans une sous requête

Dans l’exemple suivant on désire l’affichage des employées qui n’ont pas des sous employees, mais ne se retourne pas rien parce que l’une des lignes contient une valeur nulle.

Exemple :

Mais pour afficher les employées qui ont des sous employees on va suivi l’exemple :

On peut inclue une clause “where” dans la sousrequête pour afficher les employées qui n’ont pas des sous employees :

.

10. Des exercices:

1.on va afficher les nombre des département et le salaire le plus petit du département avec le plus grand salaire moyenne.

2.on va afficher le nombre du département, le nomme et la location des départements ou ne travail pas aucun « salesman ».

3.afficher le nombre et le nomme du dpartament et le nombre des employées qui travail dans chaque département qui a le plus grand nombre des employées

4.afficher le nombre et le nomme du département et le nombre des employées qui travail dans chaque département qui a le plus petit nombre des employées

Ex.1

SELECT ename, sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'JONES')

ENAME	SAL
SCOTT	3000
FORD	3000
KING	5000

Ex2.

SELECT ename, job FROM emp WHERE job= (SELECT job FROM emp where ename = 'CLARK');

ENAME	JOB
JONES	MANAGER
BLAKE	MANAGER
CLARK	MANAGER

Ex3.

SELECT ename, sal FROM emp WHERE mgr = (SELECT empno FROM emp WHERE ename = 'KING') and sal >(SELECT max(sal) FROM emp WHERE deptno =30)

ENAME	SAL
JONES	2975

Ex4.

SELECT deptno, avg(sal) FROM emp GROUP BY deptno HAVING avg(sal)= (SELECT MAX(avg(sal)) FROM emp GROUP BY deptno)

DEPTNO	AVG(SAL)
10	2916 .667

10. Des exercices:

Ex. 1 :

select deptno,min(sal) from emp group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno);

DEPTNO	MIN(SAL)
10	1300

Ex. 2 :

select * from dept where deptno not in (select deptno from emp where job='SALESMAN' and deptno is not null);

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
40	OPERATIONS	BOSTON

Ex. 3 :

SQL> select d.deptno,d.dname,count(*) from dept d,emp e where d.deptno=e.deptno group by d.deptno,d.dname having count(*)=(select max(count(*)) from emp group by deptno);

DEPTNO	DNAME)	COUNT(*)
30	SALES	6

Ex. 4 :

select d.deptno,d.dname,count(*) from dept d,emp e where d.deptno=e.deptno group by d.deptno,d.dname having count(*)=(select min(count(*)) from emp group by deptno);

DEPTNO	DNAME)	COUNT(*)
10	ACCOUNTING	3

L’opérateur IN :

SELECT ename,sal,deptno FROM emp WHERE sal in (950,800,1300);

ENAME	SAL	DEPTNO
SMITH	800	20
JAMES	950	30
MILLER	1300	10

6. Des exercices :

Exercice :

SELECT ename, sal, mgr FROM emp WHERE sal> (SELECT max(sal) FROM emp WHERE deptno=30) AND mgr<> (SELECT mgr FROM emp WHERE ename='CLARK')

ENAME	SAL	MGR
JONES	2975	7839
SCOTT	3000	7566
FORD	3000	7566

Exercice 2 :

SELECT job, AVG(sal) FROM emp GROUP BY job HAVING AVG (sal) = (SELECT MIN (AVG (sal)) FROM emp GROUP BY job)

JOB	AVG(SAL)
CLERK	1037.5

Exercice 3:

SELECT ename, sal FROM emp WHERE hiredate > (SELECT hiredate FROM emp WHERE ename='WARD') and sal> (SELECT sal FROM emp WHERE ename = 'WARD')

ENAME	SAL
JONES	2975
BLAKE	2850
CLARK	2450
SCOTT	3000
KING	5000
TURNER	1500
FORD	3000
MILLER	1300

Exercice 4:

SELECT COUNT(*) FROM emp WHERE deptno=30 AND mgr = (SELECT mgr FROM emp WHERE ename = 'KING');

COUNT(*)

1