Restriction et tri des données

Les fonctionnalités essentielles de l'instruction SELECT sont la sélection, la projection et la jonction. L'affichage de colonnes spécifiques à partir d'une table est appelé opération de projet. Nous allons maintenant nous concentrer sur l'affichage de lignes de sortie spécifiques. C'est ce qu'on appelle une opération de sélection. Des lignes spécifiques peuvent être sélectionnées en ajoutant une clause WHERE à une requête SELECT. En fait, la clause WHERE apparaît juste après la clause FROM dans la hiérarchie de requêtes SELECT. La séquence doit être maintenue dans tous les scénarios. En cas de violation, Oracle lève une exception.

Syntaxe:

SELECT *|{[DISTINCT] column| expression [alias],..}
FROM table
[WHERE condition(s)]

Dans la syntaxe,

  • La clause WHERE est le mot-clé

  • [condition] contient des noms de colonnes, des expressions, des constantes, des littéraux et un opérateur de comparaison.

Supposons que votre responsable travaille sur le budget trimestriel de votre organisation. Dans le cadre de cette activité, il est nécessaire de produire une liste des détails essentiels de chaque employé, mais uniquement pour les employés qui sont payés au moins 25 000 $ annuellement. La requête SQL ci-dessous accomplit cette tâche. Notez l'utilisation de la clause WHERE en gras.

SELECT Employee_ID, Last_Name, First_Name, Salary
FROM employees
WHERE Salary >= 25000;  

EMPLOYEE_ID  LAST_NAME        FIRST_NAME       SALARY
----------   ---------------  ---------------  -----------
88303        Jones            Quincey          $30,550.00
88404        Barlow           William          $27,500.00
88505        Smith            Susan            $32,500.00

3 rows selected

Points à noter -

  • Une clause SELECT ne peut contenir qu'une seule clause WHERE. Cependant, plusieurs conditions de filtre peuvent être ajoutées à la clause WHERE à l'aide de l'opérateur AND ou OR.

  • Les colonnes, littéraux ou expressions d'une clause de prédicat doivent être de types de données similaires ou interconvertibles.

  • L'alias de colonne ne peut pas être utilisé dans la clause WHERE.

  • Les caractères littéraux doivent être placés entre guillemets simples et sont sensibles à la casse.

  • Les littéraux de date doivent être placés entre guillemets simples et sont sensibles au format. Le format par défaut estDD-MON-RR.

Opérateurs de comparaison

Les opérateurs de comparaison sont utilisés dans les prédicats pour comparer un terme ou un opérande avec un autre terme. SQL propose un ensemble complet d'opérateurs d'égalité, d'inégalité et divers. Ils peuvent être utilisés en fonction des données et de la logique de condition de filtre dans la requête SELECT. Lorsque vous utilisez des opérateurs de comparaison dans une clause WHERE, les arguments (objets ou valeurs que vous comparez) des deux côtés de l'opérateur doivent être soit un nom de colonne, soit une valeur spécifique. Si une valeur spécifique est utilisée, la valeur doit être une valeur numérique ou une chaîne littérale. Si la valeur est une chaîne de caractères ou une date, vous devez entrer la valeur entre guillemets simples ('').

Oracle a neuf opérateurs de comparaison à utiliser dans des conditions d'égalité ou d'inégalité.

Operator  Meaning
=         equal to 
<         less than
>         greater than
>=        greater than or equal to
<=        less than or equal to
!=        not equal to
<>        not equal to

Les autres opérateurs Oracle sont BETWEEN..AND, IN, LIKE et IS NULL.

L'opérateur BETWEEN

L'opérateur BETWEEN peut être utilisé pour comparer une valeur de colonne dans une plage définie. La plage spécifiée doit avoir une limite inférieure et supérieure où les deux sont inclusifs lors de la comparaison. Son utilisation est similaire à l'opérateur d'inégalité composite (<= et> =). Il peut être utilisé avec des valeurs de type numérique, caractère et date.

Par exemple, la condition WHERE SALARY BETWEEN 1500 AND 2500 dans une requête SELECT listera les employés dont le salaire est compris entre 1500 et 2500.

L'opérateur IN

L'opérateur IN est utilisé pour tester une valeur de colonne dans un ensemble de valeurs donné. Si la colonne peut être assimilée à l'une des valeurs de l'ensemble donné, la condition est validée. La condition définie à l'aide de l'opérateur IN est également appelée condition d'appartenance.

Par exemple, la condition WHERE SALARY IN (1500, 3000, 2500) dans une requête SELECT restreindra les lignes où le salaire est de 1500, 3000 ou 2500.

L'opérateur LIKE

L'opérateur LIKE est utilisé pour la correspondance de modèle et les recherches par caractères génériques dans une requête SELECT. Si une partie de la valeur de la colonne est inconnue, un caractère générique peut être utilisé pour remplacer la partie inconnue. Il utilise des opérateurs génériques pour créer la chaîne de recherche, la recherche est donc connue sous le nom de recherche générique. Ces deux opérateurs sont Percentile ('%') et Underscore ('_'). Le trait de soulignement ('_') remplace un seul caractère tandis que le centile ('%') remplace plus d'un caractère. Ils peuvent également être utilisés en combinaison.

Par exemple, la requête SELECT ci-dessous répertorie les prénoms des employés dont le nom commence par «SA». 

SELECT first_name
FROM employees
WHERE last_name LIKE 'SA%';

Conditions IS (NOT) NULL

À noter, les valeurs NULL ne peuvent pas être testées à l'aide de l'opérateur d'égalité. C'est parce que les valeurs NULL sont inconnues et non attribuées tandis que l'opérateur d'égalité teste une valeur définie. L'opérateur IS NULL sert d'opérateur d'égalité pour vérifier les valeurs NULL d'une colonne.

Par exemple, la condition WHERE COMMISSION_PCT IS NULL dans une requête SELECT listera les employés qui n'ont pas de pourcentage de commission.

Opérateurs logiques

Plusieurs conditions de filtre peuvent être ajoutées au prédicat de la clause WHERE. Plusieurs conditions peuvent être combinées à l'aide des opérateurs logiques ET, OU et NON.

  • AND: joint deux ou plusieurs conditions et renvoie les résultats uniquement lorsque toutes les conditions sont vraies.

  • OU: joint deux ou plusieurs conditions et renvoie des résultats lorsque l'une des conditions est vraie.

  • NOT: annule l'expression qui le suit.

L'opérateur AND lie deux ou plusieurs conditions dans une clause WHERE et renvoie TRUE uniquement si toutes les conditions sont vraies. Supposons qu'un manager ait besoin d'une liste d'employées féminines. De plus, la liste ne devrait inclure que les employés dont le nom de famille commence par la lettre «E» ou qui vient plus tard dans l'alphabet. En outre, la table de résultats doit être triée par nom de famille de l'employé. Il y a deux conditions simples à remplir. La clause WHERE peut être écrite comme suit: WHERE Gender = 'F' AND last_name> 'E'.

SELECT last_name "Last Name", first_name "First Name", Gender "Gender"
FROM employees
WHERE Gender = 'F' AND last_name > 'E'
ORDER BY last_name;

L'opérateur OR lie plus d'une condition dans une clause WHERE et retourne TRUE si l'une des conditions renvoie true. Supposons que les exigences de votre responsable organisationnel changent un peu. Une autre liste d'employés est nécessaire, mais dans cette liste les employés doivent: (1) être une femme ou (2) avoir un nom de famille commençant par la lettre «T» ou une lettre qui vient plus tard dans l'alphabet. Le tableau des résultats doit être trié par nom de famille de l'employé. Dans cette situation, l'une des deux conditions peut être remplie afin de satisfaire la requête. Les employées doivent figurer sur la liste avec les employés dont le nom satisfait à la deuxième condition.

L'opérateur NOT est utilisé pour nier une expression ou une conition.

La clause ORDER BY

Lorsque vous n'affichez que quelques lignes de données, il peut être inutile de trier la sortie; cependant, lorsque vous affichez de nombreuses lignes, les gestionnaires peuvent être aidés dans la prise de décision en triant les informations. La sortie d'une instruction SELECT peut être triée à l'aide de la clause ORDER BY facultative. Lorsque vous utilisez la clause ORDER BY, le nom de colonne sur laquelle vous commandez doit également être un nom de colonne spécifié dans la clause SELECT.

La requête SQL ci-dessous utilise une clause ORDER BY pour trier la table de résultats par la colonne last_name dans l'ordre croissant. L'ordre croissant est l'ordre de tri par défaut.

SELECT last_name, first_name
FROM employees
WHERE last_name >= 'J'
ORDER BY last_name;

last_name        first_name
---------------  ---------------
Jones            Quincey
Klepper          Robert
Quattromani      Toni
Schultheis       Robert

Le tri peut également être basé sur des valeurs numériques et de date. Le tri peut également être effectué sur la base de plusieurs colonnes.

Par défaut, la clause ORDER BY trie les lignes de sortie dans la table de résultats dans l'ordre croissant. Nous pouvons utiliser le mot-clé DESC (abréviation de décroissant) pour activer le tri décroissant. La valeur par défaut alternative est ASC qui trie par ordre croissant, mais le mot clé ASC est rarement utilisé car c'est la valeur par défaut. Lorsque le mot clé facultatif ASC ou DESC est utilisé, il doit suivre le nom de la colonne sur laquelle vous effectuez le tri dans la clause WHERE.

Positional Sorting - La position numérique de la colonne dans la liste de colonnes sélectionnée peut être indiquée dans la clause ORDER BY, au lieu du nom de la colonne. Il est principalement utilisé dans les requêtes UNION (abordées plus loin). La requête classe l'ensemble de résultats par salaire car il apparaît en deuxième position dans la liste des colonnes.

SELECT  first_name, salary
FROM employees
ORDER BY 2;

Variables de substitution

Lorsqu'une requête SQL doit être exécutée plus d'une fois pour les différents ensembles d'entrées, des variables de substitution peuvent être utilisées. Les variables de substitution peuvent être utilisées pour demander des entrées utilisateur avant l'exécution de la requête. Ils sont largement utilisés dans la génération de rapports basés sur des requêtes qui prend la plage de données des utilisateurs comme entrée pour le filtrage conditionnel et l'affichage des données. Les variables de substitution sont préfixées par un seul symbole esperluette (&) pour stocker temporairement les valeurs. Par exemple,

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM employees
WHERE LAST_NAME = &last_name
OR EMPLOYEE_ID = &EMPNO;

Lorsque la requête SELECT ci-dessus est exécutée, oracle identifie le '&' comme variable de substitution. Il invite l'utilisateur à entrer la valeur pour «nom_de_nom» et «EMPNO» comme ci-dessous.

Enter value for last_name:
Enter value for empno:

Une fois que l'utilisateur fournit des entrées aux deux variables, les valeurs sont remplacées, la requête est vérifiée et exécutée.

Points à noter -

  • Si la variable est destinée à remplacer un caractère ou une valeur de date, le littéral doit être placé entre guillemets simples. Une technique utile consiste à placer la variable de substitution esperluette entre guillemets simples lors du traitement des valeurs de caractère et de date.

  • SQL Developer et SQL * Plus prennent en charge les variables de substitution et les commandes DEFINE / UNDEFINE. Bien que SQL Developer ou SQL * Plus ne prenne pas en charge les contrôles de validation (sauf pour le type de données) sur les entrées utilisateur.

  • Vous pouvez utiliser les variables de substitution non seulement dans la clause WHERE d'une instruction SQL, mais également comme substitution de noms de colonnes, d'expressions ou de texte.

Utilisation de la variable de substitution Double-Ampersand

Lorsque la même variable de substitution est utilisée à plus d'un endroit, pour éviter de ressaisir les mêmes données, nous utilisons une double substitution d'esperluette. Dans de tels cas, la valeur de la variable de substitution, une fois saisie, serait substituée à tous les instants d'utilisation.

SELECT first_name, HIRE_DATE, SEPARATION_DATE
FROM employees
WHERE HIRE_DATE LIKE '%&DT%' AND SEPARATION_DATE '%&&DT%'

Notez que la même valeur de & DT est remplacée deux fois dans la requête ci-dessus. Ainsi, sa valeur une fois donnée par l'utilisateur sera substituée à deux endroits.

Les commandes DEFINE et VERIFY

La définition de la définition des variables dans une session est définie par la fonction DEFINE de SQL * Plus. Les variables peuvent être définies dans la session, afin d'éviter un arrêt lors de l'exécution de la requête. Oracle lit la même variable chaque fois qu'elle est rencontrée dans une requête SQL. Il est à l'état ON par défaut. Avec l'aide de la clause DEFINE, on peut déclarer une variable en ligne de commande avant l'exécution de la requête commeDEFINE variable=value;.

La commande Verify vérifie la substitution ci-dessus en tant qu'ancienne et nouvelle instruction. Il est désactivé par défaut et peut être réglé sur ON à l'aide de la commande SET.

SQL> SET DEFINE ON
SQL> SET VERIFY ON
SQL> DEFINE NAME = MARTIN'
SQL> SELECT first_name, SALARY 
FROM employees
WHERE first_name = '&NAME';
OLD   1: select first_name, sal from employee where first_name = '&first_name'
new   1: select first_name, sal from employee where first_name = 'MARTIN'

first_name     SALARY
-------        -------
MARTIN         5000