orale lesson 06
DESCRIPTION
DBAI Orale lesson 06TRANSCRIPT
<Lesson Title>Sous-interrogations
6-*
Objectifs
décrire les types de problème que les
sous-interrogations permettent de résoudre
écrire des interrogations monolignes et multilignes
But du chapitre
Ce chapitre vous présente des fonctions avancées de l'instruction SELECT. Vous pouvez écrire des sous-interrogations dans la clause WHERE d'une autre instruction SQL pour obtenir des valeurs basées sur une valeur conditionnelle inconnue. Ce chapitre présente les interrogations monolignes et multilignes.
6-*
Résoudre un problème
Quels employés touchent un salaire supérieur à celui d'Abel ?
Interrogation principale :
?
Supposons que vous souhaitez écrire une interrogation pour identifier les employés qui touchent un salaire supérieur à celui d'Abel.
Pour résoudre ce problème, vous devez écrire deux interrogations : l'une pour déterminer le salaire d'Abel et l'autre pour déterminer qui touche un salaire plus élevé.
Vous pouvez placer l'une des interrogations dans l'autre interrogation.
L'interrogation interne ou sous-interrogation renvoie une valeur qui est utilisée par l'interrogation externe ou interrogation principale. Utiliser une sous-interrogation équivaut à exécuter deux interrogations séquentielles et à utiliser le résultat de la première interrogation comme valeur de recherche dans la seconde.
6-*
Syntaxe des sous-interrogations
L'interrogation principale (interrogation externe) utilise le résultat de la sous-interrogation.
SELECT select_list
FROM table
Une sous-interrogation est une instruction SELECT imbriquée dans une clause d'une autre instruction SELECT. Vous pouvez créer des instructions puissantes à partir d'instructions simples en utilisant des sous-interrogations. Ces dernières peuvent s'avérer très utiles lorsque vous souhaitez sélectionner des lignes dans une table selon une condition qui dépend des données de cette table.
Vous pouvez, par exemple, placer une sous-interrogation dans les clauses suivantes :
Clause WHERE
Clause HAVING
Clause FROM
operator inclut une condition de comparaison telles que >, = ou IN
Remarque : Les conditions de comparaison se déclinent en deux catégories : les opérateurs monolignes (>, =, >=, <, <>, <=) et les opérateurs multilignes (IN, ANY, ALL).
La sous-interrogation est souvent désignée comme une instruction SELECT imbriquée, une
sous-instruction SELECT ou une instruction SELECT interne. Elle s'exécute généralement en premier et son résultat permet d'exécuter la condition de l'interrogation principale.
6-*
SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
Utiliser une sous-interrogation
Dans la diapositive, l'interrogation interne détermine le salaire de l'employé Abel. L'interrogation externe utilise le résultat de l'interrogation interne pour afficher le nom de tous les employés qui touchent un salaire supérieur à ce montant.
6-*
Règles d'utilisation des
Placez les sous-interrogations entre parenthèses.
Placez les sous-interrogations dans la partie droite de la condition de comparaison.
La clause ORDER BY de la sous-interrogation n'est requise que si vous effectuez une analyse de type n-premiers.
Utilisez des opérateurs monolignes dans les
sous-interrogations monolignes et des opérateurs multilignes dans les sous-interrogations multilignes.
Règles d'utilisation des sous-interrogations
Pour une meilleure lisibilité, placez la sous-interrogation dans la partie droite de la condition de comparaison.
Dans les versions antérieures à Oracle8i, les sous-interrogations ne peuvent pas contenir de clause ORDER BY. Une instruction SELECT ne peut contenir qu'une clause ORDER BY, et si cette dernière est précisée, elle ne doit être suivie d'aucune autre clause dans l'instruction SELECT principale. A partir de la version Oracle8i, l'utilisation d'une clause ORDER BY est autorisée dans une sous-interrogation, voire même obligatoire si vous effectuez une analyse de type n-premiers (Top-N).
Deux catégories de conditions de comparaison sont utilisées dans les sous-interrogations : les opérateurs monolignes et les opérateurs multilignes.
6-*
Types de sous-interrogation
Interrogations multilignes : interrogations qui renvoient plusieurs lignes de l'instruction SELECT interne
Remarque : Il existe également des sous-interrogations multicolonnes, qui renvoient plusieurs colonnes de l'instruction SELECT interne.
6-*
Sous-interrogations monolignes
Opérateur
Différent de
Sous-interrogations monolignes
Une sous-interrogation monoligne renvoie une seule ligne de l'instruction SELECT interne. Ce type de sous-interrogation utilise un opérateur monoligne parmi ceux présentés dans la diapositive.
Exemple
Affichez le nom des employés dont l'ID de poste correspond à celui de l'employé 141.
SELECT last_name, job_id
SELECT last_name, job_id, salary
Exécuter des sous-interrogations monolignes
Une instruction SELECT peut être considérée comme un bloc d'interrogation. L'exemple de la diapositive permet d'afficher le nom des employés dont l'ID de poste correspond à celui de l'employé 141 et dont le salaire est supérieur à celui de l'employé 143.
Cet exemple est constitué de trois blocs d'interrogation : l'interrogation externe et deux interrogations internes. Les blocs d'interrogation internes sont exécutés en premier et génèrent les résultats ST_CLERK et 2600. Le bloc d'interrogation externe est ensuite traité et utilise les valeurs renvoyées par les interrogations internes pour exécuter ses conditions de recherche.
Les interrogations internes renvoient une seule valeur (respectivement ST_CLERK et 2600). L'instruction SQL est donc une sous-interrogation monoligne.
Remarque : Les interrogations internes et externes peuvent renvoyer des données issues de tables différentes.
6-*
SELECT last_name, job_id, salary
2500
Utiliser des fonctions de groupe dans une sous-interrogation
Vous pouvez afficher des données d'une interrogation principale en utilisant une fonction de groupe dans une sous-interrogation pour renvoyer une seule ligne. La sous-interrogation est placée entre parenthèses, après la condition de comparaison.
L'exemple de la diapositive permet d'afficher le nom, l'ID de poste et le salaire de tous les employés dont le salaire correspond au salaire minimal. La fonction de groupe MIN renvoie une seule valeur (2500) à l'interrogation externe.
6-*
Clause HAVING et sous-interrogations
sous-interrogations.
Le serveur Oracle renvoie les résultats dans la clause HAVING de l'interrogation principale.
SELECT department_id, MIN(salary)
Clause HAVING et sous-interrogations
Vous pouvez utiliser des sous-interrogations dans la clause WHERE, mais également dans la clause HAVING. Le serveur Oracle exécute la sous-interrogation et les résultats sont renvoyés dans la clause HAVING de l'interrogation principale.
L'instruction SQL de la diapositive affiche tous les services qui présentent un salaire minimal supérieur à celui du service 50.
Exemple
Recherchez le poste qui présente le salaire moyen le plus faible.
SELECT job_id, AVG(salary)
Exemple
Recherchez le poste qui présente le salaire moyen le plus faible.
Clause HAVING et sous-interrogations
SELECT employee_id, last_name
one row
Erreurs dans les sous-interrogations
Il arrive fréquemment que plusieurs lignes soient renvoyées pour une sous-interrogation monoligne.
Dans l'instruction SQL de la diapositive, la sous-interrogation contient une clause GROUP BY, qui implique le renvoi de plusieurs lignes (une pour chaque groupe détecté). Dans ce cas, le résultat de la sous-interrogation sera 4400, 6000, 2500, 4200, 7000, 17000 et 8300.
L'interrogation externe utilise les résultats de la sous-interrogation (4400, 6000, 2500, 4200, 7000, 17000, 8300) dans sa clause WHERE. Cette dernière contient un opérateur égal (=), c'est-à-dire un opérateur de comparaison monoligne qui n'attend qu'une seule valeur. L'opérateur = ne peut pas accepter plusieurs valeurs de la sous-interrogation et génère donc une erreur.
Pour corriger cette erreur, remplacez l'opérateur = par IN.
6-*
Problèmes liés aux sous-interrogations
Problèmes liés aux sous-interrogations
Il arrive couramment dans les sous-interrogations que l'interrogation interne ne renvoie aucune ligne.
Dans l'instruction SQL de la diapositive, la sous-interrogation contient une clause WHERE. L'objectif est vraisemblablement de rechercher l'employé qui porte le nom Haas. L'instruction est correcte, mais son exécution ne sélectionne aucune ligne.
Il n'existe aucune employé nommé Haas, c'est pourquoi la sous-interrogation ne renvoie aucune ligne. L'interrogation externe utilise les résultats de la sous-interrogation (NULL) dans sa clause WHERE. Elle ne trouve aucun employé dont la valeur de l'ID de poste est NULL et ne renvoie donc aucune ligne. Si un poste possédant une valeur NULL avait existé, la ligne n'aurait pas été renvoyée, car la comparaison de deux valeurs NULL aboutit à une valeur NULL (la valeur de la condition WHERE n'est pas TRUE).
6-*
Sous-interrogations multilignes
Opérateur
IN
ANY
ALL
Signification
Compare la valeur à chaque valeur renvoyée par la sous-interrogation
Compare la valeur à toutes les valeurs renvoyées par la sous-interrogation
Sous-interrogations multilignes
SELECT last_name, salary, department_id
FROM employees
Exemple
Recherchez les employés qui touchent le salaire minimal dans chaque service.
L'interrogation interne est exécutée la première et génère un résultat d'interrogation. Le bloc d'interrogation principal est ensuite traité et utilise les valeurs renvoyées par l'interrogation interne pour exécuter sa condition de recherche. Au niveau du serveur Oracle, l'interrogation principale se présenterait comme suit :
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);
6-*
Exemple
Recherchez les employés qui touchent le salaire minimal dans chaque service.
Sous-interrogations multilignes
Utiliser l'opérateur ANY dans
FROM employees
Sous-interrogations multilignes (suite)
L'opérateur ANY (et son synonyme, l'opérateur SOME) compare une valeur à chaque valeur renvoyée par une sous-interrogation. L'exemple de la diapositive permet d'afficher le nom des employés qui ne sont pas programmeurs et dont le salaire est inférieur à celui d'un programmeur. Le salaire maximal d'un programmeur s'élève à 9 000 $.
<ANY signifie inférieur au maximum, >ANY, supérieur au minimum et =ANY, égal à IN.
<ALL signifie inférieur au maximum et >ALL supérieur au minimum.
6-*
SELECT employee_id, last_name, job_id, salary
FROM employees
les sous-interrogations multilignes
9000, 6000, 4200
Sous-interrogations multilignes (suite)
L'opérateur ALL compare une valeur à toutes les valeurs renvoyées par une sous-interrogation. L'exemple de la diapositive permet d'afficher le nom des employés dont le salaire est inférieur au salaire des employés dont l'ID de poste est IT_PROG et dont la fonction n'est pas IT_PROG.
>ALL signifie supérieur au maximum et <ALL inférieur au minimum .
L'opérateur NOT ne peut pas être utilisé avec les opérateurs IN, ANY et ALL.
6-*
Afficher les employés qui n’ont pas des subordonnés
6-*
Valeurs NULL dans une
Présence de valeurs NULL dans l'ensemble de résultats d'une sous-interrogation
L'instruction SQL de la diapositive vise à afficher tous les employés n'ayant pas de subordonné. Elle ne renvoie aucune ligne bien que logiquement, elle aurait dû en renvoyer 12. En fait, l'une des valeurs renvoyées par l'interrogation interne étant une valeur NULL, l'interrogation ne renvoie aucune ligne. En effet, toutes les conditions qui comparent une valeur NULL renvoient une valeur NULL. C'est pourquoi vous ne devez pas utiliser l'opérateur NOT IN lorsque l'ensemble de résultats d'une sous-interrogation risque de contenir des valeurs NULL. L'opérateur NOT IN équivaut à <> ALL.
En revanche, la présence d'une valeur NULL dans l'ensemble de résultats d'une sous-interrogation ne posera pas de problème si vous utilisez l'opérateur IN. Cet opérateur est l'équivalent de =ANY. Par exemple, pour afficher les employés qui ont des subordonnés, utilisez l'instruction SQL suivante :
SELECT emp.last_name
FROM employees mgr);
Vous pouvez également inclure une clause WHERE dans la sous-interrogation pour afficher tous les employés qui n'ont pas de subordonné :
SELECT last_name FROM employees
WHERE employee_id NOT IN
6-*
Synthèse
déterminer quand une sous-interrogation peut aider à résoudre un problème
écrire des sous-interrogations lorsqu'une interrogation est basée sur des valeurs inconnues
SELECT select_list
FROM table
Synthèse
Ce chapitre vous a permis d'apprendre à utiliser des sous-interrogations. Une sous-interrogation est une instruction SELECT imbriquée dans une clause d'une autre instruction SQL. Les sous-interrogations sont utiles lorsqu'une interrogation est basée sur un critère de recherche qui inclut des valeurs intermédiaires inconnues.
Les sous-interrogations :
peuvent transmettre une ligne de données à une instruction principale qui contient un opérateur monoligne tel que =, <>, >, >=, < ou <= ;
peuvent transmettre plusieurs lignes de données à une instruction principale qui contient un opérateur multiligne tel que IN ;
sont traitées en priorité par le serveur Oracle (la clause WHERE ou HAVING utilise ensuite les résultats) ;
peuvent contenir des fonctions de groupe.
6-*
Présentation de l'exercice 6
Dans cet exercice, vous allez :
créer des sous-interrogations pour interroger des valeurs basées sur un critère inconnu,
utiliser des sous-interrogations pour déterminer quelles valeurs existent dans un ensemble de données et pas dans un autre.
Présentation de l'exercice 6
Dans cet exercice, vous allez écrire des interrogations complexes à l'aide d'instructions SELECT imbriquées.
Questions écrites
Vous souhaiterez peut-être créer l'interrogation interne avant de répondre à ces questions.
Assurez-vous qu'elle fonctionne et qu'elle fournit les données attendues avant de coder l'interrogation externe.
6-*
Exercice 6
1. Ecrivez une interrogation en vue d'afficher le nom et la date d'embauche des employés appartenant au même service que Zlotkey. Excluez Zlotkey.
2. Créez une interrogation en vue d'afficher le numéro et le nom de tous les employés qui touchent un salaire supérieur au salaire moyen. Triez les résultats dans l'ordre croissant des salaires.
3. Ecrivez une instruction en vue d'afficher le numéro et le nom de tous les employés qui travaillent dans le même service qu'un employé dont le nom contient la lettre u. Placez votre instruction dans un fichier texte nommé lab6_3.sql, puis exécutez-la.
6-*
Exercice 6 (suite)
4. Affichez le nom, le numéro de service et l'ID de poste de tous les employés sont l'ID de lieu du service est 1700.
5. Affichez le nom et le salaire de tous les employés qui dépendent de King.
6. Affichez le numéro de service, le nom et l'ID de poste de tous les employés du service Executive.
S'il vous reste du temps, effectuez l'exercice suivant :
7. Modifiez l'instruction du fichier lab6_3.sql pour afficher le numéro, le nom et le salaire de tous les employés dont le salaire est supérieur au salaire moyen et qui travaillent dans le même service qu'un employé dont le nom contient la lettre u. Enregistrez le fichier lab6_3.sql sous le nom lab6_7.sql, puis exécutez l'instruction du fichier lab6_7.sql.
6-*
6-*
Objectifs
décrire les types de problème que les
sous-interrogations permettent de résoudre
écrire des interrogations monolignes et multilignes
But du chapitre
Ce chapitre vous présente des fonctions avancées de l'instruction SELECT. Vous pouvez écrire des sous-interrogations dans la clause WHERE d'une autre instruction SQL pour obtenir des valeurs basées sur une valeur conditionnelle inconnue. Ce chapitre présente les interrogations monolignes et multilignes.
6-*
Résoudre un problème
Quels employés touchent un salaire supérieur à celui d'Abel ?
Interrogation principale :
?
Supposons que vous souhaitez écrire une interrogation pour identifier les employés qui touchent un salaire supérieur à celui d'Abel.
Pour résoudre ce problème, vous devez écrire deux interrogations : l'une pour déterminer le salaire d'Abel et l'autre pour déterminer qui touche un salaire plus élevé.
Vous pouvez placer l'une des interrogations dans l'autre interrogation.
L'interrogation interne ou sous-interrogation renvoie une valeur qui est utilisée par l'interrogation externe ou interrogation principale. Utiliser une sous-interrogation équivaut à exécuter deux interrogations séquentielles et à utiliser le résultat de la première interrogation comme valeur de recherche dans la seconde.
6-*
Syntaxe des sous-interrogations
L'interrogation principale (interrogation externe) utilise le résultat de la sous-interrogation.
SELECT select_list
FROM table
Une sous-interrogation est une instruction SELECT imbriquée dans une clause d'une autre instruction SELECT. Vous pouvez créer des instructions puissantes à partir d'instructions simples en utilisant des sous-interrogations. Ces dernières peuvent s'avérer très utiles lorsque vous souhaitez sélectionner des lignes dans une table selon une condition qui dépend des données de cette table.
Vous pouvez, par exemple, placer une sous-interrogation dans les clauses suivantes :
Clause WHERE
Clause HAVING
Clause FROM
operator inclut une condition de comparaison telles que >, = ou IN
Remarque : Les conditions de comparaison se déclinent en deux catégories : les opérateurs monolignes (>, =, >=, <, <>, <=) et les opérateurs multilignes (IN, ANY, ALL).
La sous-interrogation est souvent désignée comme une instruction SELECT imbriquée, une
sous-instruction SELECT ou une instruction SELECT interne. Elle s'exécute généralement en premier et son résultat permet d'exécuter la condition de l'interrogation principale.
6-*
SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
Utiliser une sous-interrogation
Dans la diapositive, l'interrogation interne détermine le salaire de l'employé Abel. L'interrogation externe utilise le résultat de l'interrogation interne pour afficher le nom de tous les employés qui touchent un salaire supérieur à ce montant.
6-*
Règles d'utilisation des
Placez les sous-interrogations entre parenthèses.
Placez les sous-interrogations dans la partie droite de la condition de comparaison.
La clause ORDER BY de la sous-interrogation n'est requise que si vous effectuez une analyse de type n-premiers.
Utilisez des opérateurs monolignes dans les
sous-interrogations monolignes et des opérateurs multilignes dans les sous-interrogations multilignes.
Règles d'utilisation des sous-interrogations
Pour une meilleure lisibilité, placez la sous-interrogation dans la partie droite de la condition de comparaison.
Dans les versions antérieures à Oracle8i, les sous-interrogations ne peuvent pas contenir de clause ORDER BY. Une instruction SELECT ne peut contenir qu'une clause ORDER BY, et si cette dernière est précisée, elle ne doit être suivie d'aucune autre clause dans l'instruction SELECT principale. A partir de la version Oracle8i, l'utilisation d'une clause ORDER BY est autorisée dans une sous-interrogation, voire même obligatoire si vous effectuez une analyse de type n-premiers (Top-N).
Deux catégories de conditions de comparaison sont utilisées dans les sous-interrogations : les opérateurs monolignes et les opérateurs multilignes.
6-*
Types de sous-interrogation
Interrogations multilignes : interrogations qui renvoient plusieurs lignes de l'instruction SELECT interne
Remarque : Il existe également des sous-interrogations multicolonnes, qui renvoient plusieurs colonnes de l'instruction SELECT interne.
6-*
Sous-interrogations monolignes
Opérateur
Différent de
Sous-interrogations monolignes
Une sous-interrogation monoligne renvoie une seule ligne de l'instruction SELECT interne. Ce type de sous-interrogation utilise un opérateur monoligne parmi ceux présentés dans la diapositive.
Exemple
Affichez le nom des employés dont l'ID de poste correspond à celui de l'employé 141.
SELECT last_name, job_id
SELECT last_name, job_id, salary
Exécuter des sous-interrogations monolignes
Une instruction SELECT peut être considérée comme un bloc d'interrogation. L'exemple de la diapositive permet d'afficher le nom des employés dont l'ID de poste correspond à celui de l'employé 141 et dont le salaire est supérieur à celui de l'employé 143.
Cet exemple est constitué de trois blocs d'interrogation : l'interrogation externe et deux interrogations internes. Les blocs d'interrogation internes sont exécutés en premier et génèrent les résultats ST_CLERK et 2600. Le bloc d'interrogation externe est ensuite traité et utilise les valeurs renvoyées par les interrogations internes pour exécuter ses conditions de recherche.
Les interrogations internes renvoient une seule valeur (respectivement ST_CLERK et 2600). L'instruction SQL est donc une sous-interrogation monoligne.
Remarque : Les interrogations internes et externes peuvent renvoyer des données issues de tables différentes.
6-*
SELECT last_name, job_id, salary
2500
Utiliser des fonctions de groupe dans une sous-interrogation
Vous pouvez afficher des données d'une interrogation principale en utilisant une fonction de groupe dans une sous-interrogation pour renvoyer une seule ligne. La sous-interrogation est placée entre parenthèses, après la condition de comparaison.
L'exemple de la diapositive permet d'afficher le nom, l'ID de poste et le salaire de tous les employés dont le salaire correspond au salaire minimal. La fonction de groupe MIN renvoie une seule valeur (2500) à l'interrogation externe.
6-*
Clause HAVING et sous-interrogations
sous-interrogations.
Le serveur Oracle renvoie les résultats dans la clause HAVING de l'interrogation principale.
SELECT department_id, MIN(salary)
Clause HAVING et sous-interrogations
Vous pouvez utiliser des sous-interrogations dans la clause WHERE, mais également dans la clause HAVING. Le serveur Oracle exécute la sous-interrogation et les résultats sont renvoyés dans la clause HAVING de l'interrogation principale.
L'instruction SQL de la diapositive affiche tous les services qui présentent un salaire minimal supérieur à celui du service 50.
Exemple
Recherchez le poste qui présente le salaire moyen le plus faible.
SELECT job_id, AVG(salary)
Exemple
Recherchez le poste qui présente le salaire moyen le plus faible.
Clause HAVING et sous-interrogations
SELECT employee_id, last_name
one row
Erreurs dans les sous-interrogations
Il arrive fréquemment que plusieurs lignes soient renvoyées pour une sous-interrogation monoligne.
Dans l'instruction SQL de la diapositive, la sous-interrogation contient une clause GROUP BY, qui implique le renvoi de plusieurs lignes (une pour chaque groupe détecté). Dans ce cas, le résultat de la sous-interrogation sera 4400, 6000, 2500, 4200, 7000, 17000 et 8300.
L'interrogation externe utilise les résultats de la sous-interrogation (4400, 6000, 2500, 4200, 7000, 17000, 8300) dans sa clause WHERE. Cette dernière contient un opérateur égal (=), c'est-à-dire un opérateur de comparaison monoligne qui n'attend qu'une seule valeur. L'opérateur = ne peut pas accepter plusieurs valeurs de la sous-interrogation et génère donc une erreur.
Pour corriger cette erreur, remplacez l'opérateur = par IN.
6-*
Problèmes liés aux sous-interrogations
Problèmes liés aux sous-interrogations
Il arrive couramment dans les sous-interrogations que l'interrogation interne ne renvoie aucune ligne.
Dans l'instruction SQL de la diapositive, la sous-interrogation contient une clause WHERE. L'objectif est vraisemblablement de rechercher l'employé qui porte le nom Haas. L'instruction est correcte, mais son exécution ne sélectionne aucune ligne.
Il n'existe aucune employé nommé Haas, c'est pourquoi la sous-interrogation ne renvoie aucune ligne. L'interrogation externe utilise les résultats de la sous-interrogation (NULL) dans sa clause WHERE. Elle ne trouve aucun employé dont la valeur de l'ID de poste est NULL et ne renvoie donc aucune ligne. Si un poste possédant une valeur NULL avait existé, la ligne n'aurait pas été renvoyée, car la comparaison de deux valeurs NULL aboutit à une valeur NULL (la valeur de la condition WHERE n'est pas TRUE).
6-*
Sous-interrogations multilignes
Opérateur
IN
ANY
ALL
Signification
Compare la valeur à chaque valeur renvoyée par la sous-interrogation
Compare la valeur à toutes les valeurs renvoyées par la sous-interrogation
Sous-interrogations multilignes
SELECT last_name, salary, department_id
FROM employees
Exemple
Recherchez les employés qui touchent le salaire minimal dans chaque service.
L'interrogation interne est exécutée la première et génère un résultat d'interrogation. Le bloc d'interrogation principal est ensuite traité et utilise les valeurs renvoyées par l'interrogation interne pour exécuter sa condition de recherche. Au niveau du serveur Oracle, l'interrogation principale se présenterait comme suit :
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);
6-*
Exemple
Recherchez les employés qui touchent le salaire minimal dans chaque service.
Sous-interrogations multilignes
Utiliser l'opérateur ANY dans
FROM employees
Sous-interrogations multilignes (suite)
L'opérateur ANY (et son synonyme, l'opérateur SOME) compare une valeur à chaque valeur renvoyée par une sous-interrogation. L'exemple de la diapositive permet d'afficher le nom des employés qui ne sont pas programmeurs et dont le salaire est inférieur à celui d'un programmeur. Le salaire maximal d'un programmeur s'élève à 9 000 $.
<ANY signifie inférieur au maximum, >ANY, supérieur au minimum et =ANY, égal à IN.
<ALL signifie inférieur au maximum et >ALL supérieur au minimum.
6-*
SELECT employee_id, last_name, job_id, salary
FROM employees
les sous-interrogations multilignes
9000, 6000, 4200
Sous-interrogations multilignes (suite)
L'opérateur ALL compare une valeur à toutes les valeurs renvoyées par une sous-interrogation. L'exemple de la diapositive permet d'afficher le nom des employés dont le salaire est inférieur au salaire des employés dont l'ID de poste est IT_PROG et dont la fonction n'est pas IT_PROG.
>ALL signifie supérieur au maximum et <ALL inférieur au minimum .
L'opérateur NOT ne peut pas être utilisé avec les opérateurs IN, ANY et ALL.
6-*
Afficher les employés qui n’ont pas des subordonnés
6-*
Valeurs NULL dans une
Présence de valeurs NULL dans l'ensemble de résultats d'une sous-interrogation
L'instruction SQL de la diapositive vise à afficher tous les employés n'ayant pas de subordonné. Elle ne renvoie aucune ligne bien que logiquement, elle aurait dû en renvoyer 12. En fait, l'une des valeurs renvoyées par l'interrogation interne étant une valeur NULL, l'interrogation ne renvoie aucune ligne. En effet, toutes les conditions qui comparent une valeur NULL renvoient une valeur NULL. C'est pourquoi vous ne devez pas utiliser l'opérateur NOT IN lorsque l'ensemble de résultats d'une sous-interrogation risque de contenir des valeurs NULL. L'opérateur NOT IN équivaut à <> ALL.
En revanche, la présence d'une valeur NULL dans l'ensemble de résultats d'une sous-interrogation ne posera pas de problème si vous utilisez l'opérateur IN. Cet opérateur est l'équivalent de =ANY. Par exemple, pour afficher les employés qui ont des subordonnés, utilisez l'instruction SQL suivante :
SELECT emp.last_name
FROM employees mgr);
Vous pouvez également inclure une clause WHERE dans la sous-interrogation pour afficher tous les employés qui n'ont pas de subordonné :
SELECT last_name FROM employees
WHERE employee_id NOT IN
6-*
Synthèse
déterminer quand une sous-interrogation peut aider à résoudre un problème
écrire des sous-interrogations lorsqu'une interrogation est basée sur des valeurs inconnues
SELECT select_list
FROM table
Synthèse
Ce chapitre vous a permis d'apprendre à utiliser des sous-interrogations. Une sous-interrogation est une instruction SELECT imbriquée dans une clause d'une autre instruction SQL. Les sous-interrogations sont utiles lorsqu'une interrogation est basée sur un critère de recherche qui inclut des valeurs intermédiaires inconnues.
Les sous-interrogations :
peuvent transmettre une ligne de données à une instruction principale qui contient un opérateur monoligne tel que =, <>, >, >=, < ou <= ;
peuvent transmettre plusieurs lignes de données à une instruction principale qui contient un opérateur multiligne tel que IN ;
sont traitées en priorité par le serveur Oracle (la clause WHERE ou HAVING utilise ensuite les résultats) ;
peuvent contenir des fonctions de groupe.
6-*
Présentation de l'exercice 6
Dans cet exercice, vous allez :
créer des sous-interrogations pour interroger des valeurs basées sur un critère inconnu,
utiliser des sous-interrogations pour déterminer quelles valeurs existent dans un ensemble de données et pas dans un autre.
Présentation de l'exercice 6
Dans cet exercice, vous allez écrire des interrogations complexes à l'aide d'instructions SELECT imbriquées.
Questions écrites
Vous souhaiterez peut-être créer l'interrogation interne avant de répondre à ces questions.
Assurez-vous qu'elle fonctionne et qu'elle fournit les données attendues avant de coder l'interrogation externe.
6-*
Exercice 6
1. Ecrivez une interrogation en vue d'afficher le nom et la date d'embauche des employés appartenant au même service que Zlotkey. Excluez Zlotkey.
2. Créez une interrogation en vue d'afficher le numéro et le nom de tous les employés qui touchent un salaire supérieur au salaire moyen. Triez les résultats dans l'ordre croissant des salaires.
3. Ecrivez une instruction en vue d'afficher le numéro et le nom de tous les employés qui travaillent dans le même service qu'un employé dont le nom contient la lettre u. Placez votre instruction dans un fichier texte nommé lab6_3.sql, puis exécutez-la.
6-*
Exercice 6 (suite)
4. Affichez le nom, le numéro de service et l'ID de poste de tous les employés sont l'ID de lieu du service est 1700.
5. Affichez le nom et le salaire de tous les employés qui dépendent de King.
6. Affichez le numéro de service, le nom et l'ID de poste de tous les employés du service Executive.
S'il vous reste du temps, effectuez l'exercice suivant :
7. Modifiez l'instruction du fichier lab6_3.sql pour afficher le numéro, le nom et le salaire de tous les employés dont le salaire est supérieur au salaire moyen et qui travaillent dans le même service qu'un employé dont le nom contient la lettre u. Enregistrez le fichier lab6_3.sql sous le nom lab6_7.sql, puis exécutez l'instruction du fichier lab6_7.sql.
6-*