Sous-requêtes imbriquées et liées dans SQL, prédicat EXISTS. Utilisation de l'opérateur EXISTS Requêtes utilisant la fonction exist

OÙ EXISTE

La sous-requête est vérifiée pour la présence d'une ou plusieurs lignes. Si au moins une ligne correspond à la requête, la valeur booléenne TRUE est renvoyée. Lorsque le mot clé facultatif NOT est spécifié, la valeur booléenne TRUE est renvoyée si la sous-requête ne renvoie aucune ligne correspondante.

sous-requête

Sur la base de la sous-requête entièrement formée, l'ensemble de données résultant est récupéré.

Règles générales

L'opérateur EXISTS teste l'existence d'une ou plusieurs lignes dans une sous-requête d'une requête parent.

SELECT * FROM jobs WHERE NOT EXISTS (SELECT * FROM employé WHERE jobs.job_id=employye.job_id);

Cet exemple vérifie la sous-requête d'enregistrements à l'aide du mot clé NOT supplémentaire. L'exemple suivant recherche des enregistrements spécifiques dans une sous-requête pour récupérer le jeu de résultats principal.

SELECT au_lname FROM auteurs OÙ EXISTE (SELECT * FROM éditeurs WHERE auteurs.city=publishers.city);

Cette requête renvoie les noms de famille des auteurs (au_lname) qui vivent dans la même ville que les éditeurs. Notez que vous pouvez utiliser un astérisque dans la sous-requête car la sous-requête ne doit renvoyer qu'un seul enregistrement avec la valeur booléenne TRUE. Dans de tels cas, les colonnes n'ont pas d'importance. Le point clé est l’existence de la chaîne.

Dans de nombreuses requêtes, l'opérateur EXISTS remplit la même fonction que ANY. L'opérateur EXISTS est généralement plus efficace lorsqu'il est utilisé avec des requêtes corrélées.

L'opérateur EXISTS est sémantiquement équivalent à l'opérateur ANY.

Une sous-requête dans une instruction EXISTS effectue généralement l'un des deux types de recherche suivants. La première option consiste à utiliser un caractère générique - un astérisque (par exemple, SELECT * FROM...), auquel cas vous ne récupérez aucune colonne ou valeur spécifique. L'astérisque signifie ici « n'importe quelle colonne ». La deuxième option consiste à sélectionner une seule colonne spécifique dans la sous-requête (par exemple, SELECT aujd FROM). Certaines plates-formes individuelles autorisent les sous-requêtes sur plusieurs colonnes (par exemple SELECT aujd, aujname FROM...). Cependant, cette fonctionnalité est rare et doit être évitée dans le code qui doit être porté sur d'autres plates-formes.

Différences entre les plateformes

Toutes les plates-formes prennent en charge l'opérateur EXISTS sous la forme décrite ci-dessus.

"C'était plus facile avant" - ai-je pensé en m'asseyant pour optimiser la prochaine requête en SQL studio de gestion. Quand j'écrivais sous MySQL, tout était vraiment plus simple : ça marche ou ça ne marche pas. Soit ça ralentit, soit ça ne ralentit pas. Expliquez que j'ai résolu tous mes problèmes, rien de plus n'était nécessaire. Je dispose désormais d'un environnement puissant pour développer, déboguer et optimiser les requêtes et les procédures/fonctions, et tout cet encombrement ne fait que créer davantage de problèmes à mon avis. Et pourquoi tout ? Parce que l'optimiseur de requêtes intégré est mauvais. Si dans MySQL et PostgreSQL j'écris

Sélectionnez * parmi a, b, c où a.id = b.id, b.id = c.id

et chacune des tablettes aura au moins 5 000 lignes - tout va geler. Et Dieu merci ! Car sinon le développeur, au mieux, développe la paresse d'écrire correctement, et au pire, il ne comprend pas du tout ce qu'il fait ! Après tout, la même requête dans MSSQL fonctionnera de la même manière

Sélectionnez * à partir d'une jointure b sur a.id = b.id joins c sur b.id = c.id

L'optimiseur intégré passera au peigne fin les requêtes redondantes et tout ira bien.

Il décidera également lui-même de ce qu'il est préférable de faire : exister ou rejoindre et bien plus encore. Et tout fonctionnera de la manière la plus optimale possible.

Il n'y a qu'un seul MAIS. À un moment donné, l'optimiseur trébuchera requête complexe et passe, et alors vous avez un énorme problème. Et vous ne l'obtiendrez peut-être pas tout de suite, mais lorsque le poids des tables atteint une masse critique.

Voici donc le point de l’article. existe et fait l'objet d'opérations très lourdes. Il s'agit en fait d'une sous-requête distincte pour chaque lignes de résultat. Et s’il y a aussi de la nidification, il s’agit généralement d’éteindre les lumières. Tout ira bien lorsque 1, 10, 50 lignes seront renvoyées. Vous ne sentirez pas la différence et peut-être que la connexion sera encore plus lente. Mais quand 500 sont retirés, les problèmes commencent. 500 sous-requêtes dans une seule requête, c'est sérieux.

Bien que du point de vue de la compréhension humaine, in et exist soient meilleurs, mais du point de vue des coûts de temps pour les requêtes renvoyant plus de 50 lignes, ils ne sont pas acceptables.

Il faut faire une réserve : naturellement, s'il diminue quelque part, il doit arriver quelque part. Oui, la jointure nécessite plus de mémoire, car conserver la table entière de valeurs à la fois et l'utiliser coûte plus cher que d'exécuter des sous-requêtes pour chaque ligne, libérant ainsi rapidement de la mémoire. Vous devez examiner spécifiquement la demande et mesurer si l'utilisation de mémoire supplémentaire pour gagner du temps sera critique ou non.

Je vais donner des exemples d'analogies complètes. D'une manière générale, je n'ai pas encore rencontré de requêtes d'un tel degré de complexité qui ne pourraient pas être développées en une cascade de jointures. Cela peut prendre une journée, mais tout peut être révélé.

Sélectionnez * à partir de a où a.id dans (sélectionnez l'identifiant dans b) sélectionnez * à partir de a où existe (sélectionnez top 1 1 à partir de b où b.id = a.id) sélectionnez * à partir d'une jointure b sur a.id = b. id sélectionnez * à partir de a où a.id n'est pas dans (sélectionnez l'identifiant dans b) sélectionnez * à partir de a où n'existe pas (sélectionnez top 1 1 à partir de b où b.id = a.id) sélectionnez * à partir d'une jointure gauche b sur a. id = b.id où b.id est nul

Je le répète - l'optimiseur MSSQL optimise ces exemples pour performance maximum et il n'y aura jamais de gens stupides avec des demandes aussi simples.

Considérons maintenant un exemple de requête réelle qui a dû être réécrite car elle s'est simplement figée sur certains échantillons (la structure est très simplifiée et les concepts ont été remplacés, il n'y a pas lieu d'avoir peur d'une structure non optimale de la base de données ).

Vous devez supprimer tous les « produits » en double dans différents comptes, en vous concentrant sur les paramètres du produit, son groupe et son groupe parent, le cas échéant.

Sélectionnez d.PRODUCT_ID parmi PRODUCT s, PRODUCT_GROUP sg gauche rejoindre M_PG_DEPENDENCY sd sur (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg gauche rejoindre M_PG_DEPENDENCY dd sur (dg.PRODUCT_GROUP_ID dd.M_PG_DEPENDENCY_CHILD_ID) où s.PRODUCT _GROUP_ID=sg . PRODUCT_GROUP_ID et d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID et sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC et sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME et s.PRODUCT_NAME=d.PRODUCT_N AME et s.PRODUCT_TYPE=d.PRODUCT_TYPE et s.PRODUCT_IS_SECURE=d. PRODUIT_IS_SECURE et s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT et dg.PRODUCT_GROUP_IS_TMPL=0 et ((sd.M_PG_DEPENDENCY_CHILD_ID est nul et dd.M_PG_DEPENDENCY_CHILD_ID est nul) ou existe (sélectionnez 1 dans PRODUCT_GROUP sg1, PRODUCT_GROUP dg1 où sd.M_PG_DEPENDENCY_PARENT_ID = sg1.PRODUCT_GROUP_ID et jj .M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID et sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC et sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME et))

C'est donc le cas lorsque l'optimiseur a abandonné. Et pour chaque ligne, un heavy exist a été exécuté, ce qui a tué la base de données.

Sélectionnez d.PRODUCT_ID dans PRODUCT, rejoignez PRODUCT d sur s.PRODUCT_TYPE=d.PRODUCT_TYPE et s.PRODUCT_NAME=d.PRODUCT_NAME et s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE et s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT rejoignez PRODUCT_GRO UP sg sur s.PRODUCT_GROUP_ID. = sg.PRODUCT_GROUP_ID rejoint PRODUCT_GROUP dg sur d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID et sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME et sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC quittent M_PG_DEP ENDENCY sd sur sg.PRODUCT_GROUP_ID =. M_PG_DEPENDENCY_CHILD_ID gauche rejoint M_PG_DEPENDENCY dd sur dg .PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID gauche rejoindre PRODUCT_GROUP sgp sur sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID gauche rejoindre PRODUCT_GROUP dgp sur dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID et sgp.PROD _NAME = dgp.PRODUCT_GROUP_NAME et isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull (dgp. PRODUCT_GROUP_IS_TMPL, 0) où (sd.M_PG_DEPENDENCY_CHILD_ID est nul et dd.M_PG_DEPENDENCY_CHILD_ID est nul) ou (sgp.PRODUCT_GROUP_NAME n'est pas nul et dgp.PRODUCT_GROUP_NAME n'est pas nul) allez

Après ces transformations, les performances de la vue augmentaient de façon exponentielle avec le nombre de produits trouvés. Ou plutôt, le temps de recherche restait pratiquement indépendant du nombre de correspondances et était toujours très réduit. Comme cela devrait être.

Ceci est un exemple clair de la façon dont faire confiance à l’optimiseur MSSQL peut être une blague cruelle. Ne lui faites pas confiance, ne soyez pas paresseux, rejoignez manuellement, pensez à chaque fois à ce qui est mieux dans une situation donnée - exister, participer ou rejoindre.

Le prédicat du langage SQL EXISTS effectue une tâche logique. DANS Requêtes SQL ce prédicat est utilisé dans les expressions de la forme

EXISTE (SELECT * FROM TABLE_NAME ...).

Cette expression renvoie vrai lorsque la requête trouve une ou plusieurs lignes qui correspondent à la condition, et faux lorsqu'aucune ligne n'est trouvée.

Pour NOT EXISTS, c'est l'inverse. Expression

N'EXISTE PAS (SELECT * FROM TABLE_NAME ...)

renvoie true lorsqu'aucune ligne n'est trouvée dans la requête et false lorsqu'au moins une ligne est trouvée.

Les requêtes les plus simples avec le prédicat SQL EXISTS

Dans les exemples, nous travaillons avec la base de données de la bibliothèque et ses tables « Book in Use » (BOOKINUSE) et « User » (USER). Pour l’instant, nous n’avons besoin que du tableau « Book in Use » (BOOKINUSE).

Auteur	Titre	Année de publication	Inv_No	ID de l'utilisateur
Tolstoï	Guerre et Paix	2005	28	65
Tchekhov	Le verger de cerisiers	2000	17	31
Tchekhov	Histoires sélectionnées	2011	19	120
Tchekhov	Le verger de cerisiers	1991	5	65
Ilf et Petrov	Les douze chaises	1985	3	31
Maïakovski	Poèmes	1983	2	120
Panais	Docteur Jivago	2006	69	120
Tolstoï	Dimanche	2006	77	47
Tolstoï	Anna Karénine	1989	7	205
Pouchkine	La fille du capitaine	2004	25	47
Gogol	Pièces	2007	81	47
Tchekhov	Histoires sélectionnées	1987	4	205
Panais	Favoris	2000	137	18

Exemple 1. Déterminez les identifiants des utilisateurs qui ont reçu les livres de Tolstoï et qui ont également reçu les livres de Tchekhov. La requête externe sélectionne les données sur les utilisateurs qui ont reçu les livres de Tolstoï, et le prédicat EXISTS spécifie une condition supplémentaire qui est vérifiée dans la requête interne : les utilisateurs qui ont reçu les livres de Tchekhov. Une condition supplémentaire dans la demande interne est que les ID utilisateur des demandes externes et internes correspondent : User_ID=tols_user.user_id. La demande sera la suivante :

Cette requête renverra le résultat suivant :

Différences entre les prédicats EXISTS et IN

Au premier coup d'œil sur les requêtes avec le prédicat EXISTS, vous pouvez avoir l'impression qu'il est identique prédicat DANS. C'est faux. Même s'ils sont très similaires. Le prédicat IN recherche les valeurs de la plage spécifiée dans son argument, et s'il existe de telles valeurs, alors toutes les lignes correspondant à cette plage sont sélectionnées. Le résultat du prédicat EXISTS est une réponse « oui » ou « non » à la question de savoir s'il existe des valeurs correspondant à celles spécifiées dans l'argument. De plus, le prédicat IN est précédé du nom de la colonne par laquelle rechercher les lignes correspondant aux valeurs de la plage. Regardons un exemple montrant la différence entre le prédicat EXISTS et le prédicat IN, ainsi que le problème résolu à l'aide du prédicat IN.

Exemple 4. Déterminez les identifiants des utilisateurs qui ont reçu des livres par des auteurs dont les livres ont été attribués à l'utilisateur avec l'ID 31. La demande sera la suivante :

ID de l'utilisateur

120

65

205

Une requête interne (après IN) sélectionne les auteurs : Tchekhov ; Ilf et Petrov. La requête externe sélectionne tous les utilisateurs qui ont reçu des livres de ces auteurs. On voit que, contrairement au prédicat EXISTS, le prédicat IN est précédé du nom de la colonne, en l'occurrence - Auteur.

Requêtes avec prédicat EXISTS et conditions supplémentaires

Si, en plus du prédicat EXISTS dans la requête, vous appliquez au moins une condition supplémentaire, par exemple spécifiée à l'aide de fonctions d'agrégation, alors ces requêtes peuvent servir à une analyse simple des données. Montrons cela avec l'exemple suivant.

Exemple 5. Déterminez les identifiants des utilisateurs qui ont reçu au moins un livre par Pasternak et qui ont reçu plus de 2 livres. Nous écrivons la requête suivante, dans laquelle la première condition est spécifiée par le prédicat EXISTS avec une requête imbriquée, et la deuxième condition avec l'opérateur HAVING doit toujours suivre la requête imbriquée :

Résultat de la demande :

ID de l'utilisateur

120

Comme le montre la table BOOKINUSE, le livre de Pasternak a également été délivré à l'utilisateur portant l'ID 18, mais il n'a reçu qu'un seul livre et n'est pas inclus dans l'échantillon. Si vous appliquez à nouveau la fonction COUNT à une requête similaire, mais cette fois pour compter les lignes sélectionnées (pratiquez-vous vous-même), vous pouvez obtenir des informations sur le nombre d'utilisateurs qui lisent les livres de Pasternak lisent également des livres d'autres auteurs. Cela vient déjà du domaine de l’analyse des données.

Requêtes avec le prédicat EXISTS sur deux tables

Les requêtes avec le prédicat EXISTS peuvent récupérer les données de plusieurs tables. De nombreux problèmes peuvent être résolus avec le même résultat en utilisant Opérateur REJOINDRE, mais dans certains cas, l'utilisation de EXISTS vous permet de créer une requête moins lourde. Il est préférable d'utiliser EXISTS dans les cas où la table résultante contiendra les colonnes d'une seule table.

Dans l'exemple suivant, à partir de la même base de données, en plus de la table BOOKINUSE, vous aurez également besoin d'une table USER.

Le résultat de la requête sera le tableau suivant :

Auteur

Tchekhov

Maïakovski

Panais

Comme pour l'utilisation de l'opérateur JOIN, dans les cas où il existe plusieurs tables, vous devez utiliser des alias de table pour vérifier que les valeurs des clés reliant les tables correspondent. Dans notre exemple, les alias des tables sont bk et us, et la clé reliant les tables est User_ID.

Prédicat EXISTS dans les jointures de plus de deux tables

Nous allons maintenant voir plus en détail pourquoi il est préférable d'utiliser EXISTS dans les cas où la table résultante contiendra les colonnes d'une seule table.

Nous travaillons avec la base de données "Immobilier". La table Deal contient des données sur les transactions. Pour nos tâches, la colonne Type avec des données sur le type de transaction - vente ou location - sera importante dans ce tableau. La table Objet contient des données sur les objets. Dans ce tableau, nous aurons besoin des valeurs des colonnes Pièces (nombre de pièces) et LogBalc, qui contiennent des données sur la présence d'une loggia ou d'un balcon au format booléen : 1 (oui) ou 0 (non). Les tables Client, Gestionnaire et Propriétaire contiennent respectivement des données sur les clients, les chefs d'entreprise et les propriétaires immobiliers. Dans ces tableaux, FName et LName sont respectivement le prénom et le nom.

Exemple 7. Identifiez les clients qui ont acheté ou loué des biens immobiliers sans loggia ni balcon. Nous écrivons la requête suivante, dans laquelle le prédicat EXISTS spécifie un accès au résultat de la jointure de deux tables :

Étant donné que les colonnes sont sélectionnées dans la table Client à l'aide de l'opérateur astérisque, toutes les colonnes de cette table seront affichées, qui auront autant de lignes qu'il y a de clients correspondant à la condition spécifiée par le prédicat EXISTS. Nous n'avons pas besoin de générer de colonnes à partir des tables dont la jointure est accessible par la sous-requête. Par conséquent, pour gagner du temps machine, une seule colonne est récupérée. Pour ce faire, une unité est écrite après le mot SELECT. La même technique est utilisée dans les requêtes des exemples suivants.

Écrivez vous-même une requête SQL avec le prédicat EXISTS, puis examinez la solution

Nous continuons à écrire des requêtes SQL avec le prédicat EXISTS

Exemple 9. Déterminez les propriétaires des objets loués. Nous écrivons la requête suivante, dans laquelle le prédicat EXISTS spécifie également un accès au résultat de la jointure de deux tables :

Comme dans l'exemple précédent, tous les champs de la table accédée par la requête externe seront renvoyés.

Exemple 10. Déterminez le nombre de propriétaires dont les propriétés étaient gérées par le gestionnaire Savelyev. Nous écrivons une requête dans laquelle la requête externe accède à une jointure de trois tables et le prédicat EXISTS spécifie l'accès à une seule table :

Toutes les requêtes sont vérifiées par rapport à une base de données existante. Utilisation réussie !

Bases de données relationnelles et Langage SQL

Académie d'État d'économie et de gestion de Novossibirsk

STAGE DE LABORATOIRE SUR LA DISCIPLINE

"BASE DE DONNÉES"

Travail de laboratoire n°7

"Langage de base de données SQL : commandes de manipulation de données»

NOVOSSIBIRSK 2000

SQL est une abréviation de Structured Query Language. D'après le nom du langage, il ressort clairement que son objectif principal est de générer des requêtes pour obtenir des informations à partir d'une base de données. Les commandes permettant de récupérer des données constituent la base du langage de manipulation de données DML, qui fait partie intégrante du langage SQL. Cependant, DML comprend bien plus que de simples commandes permettant de récupérer des données à partir d'une base de données. Il existe également des commandes pour la modification des données, la gestion des données et autres.

Le travail de laboratoire examine les outils de base du langage DML. En cours travail de laboratoire nous nous en tiendrons au standard SQL2.

Étant donné que SQL est un langage volumineux, nous ne considérerons que les commandes de base. Divers outils SQL spécifiques sont abordés dans les ateliers suivants.

Pour effectuer des travaux de laboratoire, une connaissance des bases du modèle de données relationnelles, des bases de l'algèbre relationnelle et du calcul relationnel et des principes de travail avec le SGBD MS SQL Server est requise.

À l'issue des travaux de laboratoire, vous maîtriserez les méthodes de manipulation des données à l'aide des commandes du langage SQL, considérerez le dialecte du langage implémenté dans le SGBD MS SQL Server.

INTRODUCTION

SQL contient un large éventail de fonctionnalités de manipulation de données, à la fois pour créer des requêtes et mettre à jour la base de données. Ces capacités reposent uniquement sur la structure logique de la base de données, et non sur sa structure physique, qui est cohérente avec les exigences du modèle relationnel.

La structure originale de la syntaxe SQL était (ou du moins semblait être) basée sur le calcul relationnel de Codd. La seule opération prise en charge en algèbre relationnelle était l’union.

En plus de la syntaxe de type calcul relationnel développée dans la norme précédente, SQL2 implémente directement les opérations union, intersection, différence et jointure. Les opérations de sélection, de projet et de produit étaient (et continuent d'être) prises en charge presque directement, tandis que les opérations de division et d'affectation sont prises en charge sous une forme plus lourde.

Nous décrirons dans un premier temps le langage de requête SQL puis ses opérations de saisie et de modification de données. Les opérations de modification de données seront décrites en dernier lieu, puisque leur structure repose dans une certaine mesure sur la structure du langage de requête.

Requêtes simples

Pour nous simple demande il y aura une requête qui accèdera à une seule table de la base de données. Des requêtes simples nous aideront à illustrer la structure de base de SQL.

Demande simple. Une requête qui accède à une seule table de base de données.

Demande: Qui travaille comme plâtrier ?

WHERE SKILL_TYPE = "Plâtrier"

Résultat:

G. Rickover

Cette requête illustre les trois plus courantes phrases SQL : SELECT, FROM et OÙ. Bien que dans notre exemple nous les ayons placés sur des lignes différentes, ils peuvent tous apparaître sur la même ligne. Ils peuvent également être indentés différemment et les mots dans les phrases peuvent être séparés par un nombre arbitraire d'espaces. Examinons les caractéristiques de chaque phrase.

Sélectionner. La clause SELECT répertorie les colonnes qui doivent apparaître dans le tableau résultant. Ce sont toujours des colonnes d'une table relationnelle. Dans notre exemple, le tableau résultant est constitué d'une seule colonne (NOM), mais en général il peut contenir plusieurs colonnes ; il peut également contenir des valeurs calculées ou des constantes. Nous donnerons des exemples de chacune de ces options. Si le tableau résultant doit contenir plus d'une colonne, toutes les colonnes requises sont répertoriées après Commandes SÉLECTIONNER séparé par des virgules. Par exemple, l'expression SELECT WORKER_ID, NAME donnera lieu à un tableau composé des colonnes WORKER_ID et NAME.

Clause SELECT. Spécifie les colonnes de la table résultante.

Depuis. La clause FROM spécifie une ou plusieurs tables auxquelles la requête doit accéder. Toutes les colonnes répertoriées dans les clauses SELECT et WHERE doivent exister dans l'une des tables répertoriées dans la commande FROM. Dans SQL2, ces tables peuvent être directement définies dans le schéma en tant que tables de base ou vues de données, ou elles peuvent elles-mêmes être des tables sans nom résultant de requêtes SQL. Dans ce dernier cas, la requête est explicitement donnée dans la commande FROM.

L'expression DE. Spécifie les tables existantes auxquelles la requête accède.

Où. La clause WHERE contient une condition. sur la base duquel les lignes du ou des tableaux sont sélectionnées. Dans notre exemple, la condition est que la colonne SKILL_TYPE doit contenir la constante "Plâtrier" entourée d'apostrophes, comme cela se fait toujours avec les constantes texte en SQL. La clause WHERE est la commande SQL la plus volatile ; il peut contenir de nombreuses conditions différentes. Une grande partie de notre discussion sera consacrée à l'illustration des différentes constructions autorisées dans la commande WHERE.

Clause OÙ. Spécifie la condition en fonction de laquelle les lignes sont sélectionnées dans les tables spécifiées.

La requête SQL ci-dessus est traitée par le système dans l'ordre suivant : FROM, WHERE, SELECT. Autrement dit, les lignes de la table spécifiée dans la commande FROM sont placées dans l'espace de travail pour traitement. La clause WHERE est ensuite appliquée à chaque ligne dans l'ordre. Toutes les lignes qui ne satisfont pas à la condition WHERE sont exclues de la prise en compte. Ensuite, les lignes qui satisfont à la condition WHERE sont traitées par l'instruction SELECT. Dans notre exemple, NOM est sélectionné dans chacune de ces lignes et toutes les valeurs sélectionnées sont affichées en tant que résultats de requête.

Demande: Fournir toutes les informations sur les immeubles de bureaux.

OÙ TYPE = "Bureau"

Résultat:

BLDG IDADDRESSTYPEQLTY LEVELSTATUS

312, rue Elm, 123 Bureau 2 2

210, rue Berezovaya. 1011 Bureau Z 1

111, rue Osinovaya. 1213 Bureau 4 1

Un astérisque (*) dans une commande SELECT signifie « ligne entière ». Il s’agit d’un raccourci pratique que nous utiliserons souvent.

Demande: Quel est le salaire hebdomadaire de chaque électricien ?

SELECT NOM, "Salaire hebdomadaire = ", 40 * HRLY_RATE

WHERE SKILL_TYPE = "Électricien"

Résultat:

M. Faraday Salaire hebdomadaire = 500,00

H.Columbus Salaire hebdomadaire = 620,00

Cette requête illustre l'utilisation de constantes de caractères (dans notre exemple « Salaire hebdomadaire = ») et de calculs dans la commande SELECT. Dans l'instruction SELECT, vous pouvez effectuer des calculs qui utilisent des colonnes numériques et des constantes numériques, ainsi que des opérateurs arithmétiques standard (. +, -, *, /), regroupés selon les besoins à l'aide de parenthèses. Nous avons également inclus un nouveau commande COMMANDER BY, qui trie le résultat de la requête par ordre alphanumérique croissant selon la colonne spécifiée. Si vous souhaitez classer les résultats par ordre décroissant, vous devez ajouter DESC à la commande. La clause ORDER BY peut trier les résultats sur plusieurs colonnes, certaines par ordre croissant et d'autres par ordre décroissant. La colonne de clé primaire du tri est répertoriée en premier.

Caractère constant. Constante composée de lettres, de chiffres et de caractères « spéciaux ».

Demande: Qui a un taux horaire de 10 $ à 12 $ ?

OÙ HRLY_RATE > = 10 ET HRLY_RATE< - 12

Résultat:

ID TRAVAILLEUR NOM HRLY_RATE SKILL_TYPE SUPV_ID

Cette requête illustre certaines des fonctionnalités supplémentaires de l'instruction WHERE : les opérateurs de comparaison et l'opérateur booléen AND. Six opérateurs de comparaison (=,<>(inégal),<, >, <=, >=). Les opérateurs booléens AND, OR et NOT peuvent être utilisés pour créer des conditions composées ou pour annuler une condition. Les parenthèses peuvent être utilisées pour regrouper des conditions, comme cela est courant dans les langages de programmation.

Opérateurs de comparaison =,<>, <, >, <=, >=.

Opérations booléennes ET (ET), OU (OU) et NON (IL) .

Vous pouvez également utiliser l'opérateur BETWEEN (entre) pour formuler cette requête :

OÙ HRLY_RATE ENTRE 10 ET 12

BETWEEN peut être utilisé pour comparer une quantité avec deux autres quantités dont la première est inférieure à la seconde, si la quantité comparée peut être égale à chacune de ces quantités ou à toute valeur intermédiaire.

Demande : Répertorier les plâtriers, couvreurs et électriciens.

WHERE SKILL_TYPE IN ("Plâtrier", "Couvreur", "Électricien")

Résultat:

WORKER_ID NOM HRLY_RATE SKILL_TYPE SUPV_ID

1412 K.Nemo 13.75 Plâtrier 1520

2920 R. Garrett 10,00 Couvreur 2920

1520 G. Rickover 11,75 Plâtrier 1520

Cette requête explique l'utilisation de l'opérateur de comparaison IN (B). La condition WHERE est considérée comme vraie si le type de spécialité de la ligne se trouve à l'intérieur de l'ensemble spécifié entre parenthèses, c'est-à-dire si le type de spécialité est plâtrier, couvreur ou électricien. Nous reverrons l'opérateur IN dans les sous-requêtes.

Supposons que nous ne puissions pas nous souvenir exactement de l'orthographe de notre spécialité : « électricien » ou « ingénieur en électronique » ou autre chose. Les caractères génériques, qui remplacent les chaînes de caractères non définies, facilitent la recherche d'orthographe inexacte dans une requête.

Symboles de modèle. Caractères qui remplacent des chaînes de caractères non définies.

Demande: Répertoriez les employés dont le type de spécialité commence par « Elek ».

OÙ SKILL_TYPE LIKE ("Elect%")

Résultat:

ID TRAVAILLEUR NOM HRLY_RATE SKILL_TYPE SUPV_ID

1235 M. Faraday 12h50 Électricien 1311

1311 H. Columbus 15,50 Électrique 1311

SQL comporte deux caractères génériques : % (pourcentage) et _ (trait de soulignement). Le trait de soulignement remplace exactement un caractère non défini. Le pourcentage remplace un nombre arbitraire de caractères, en commençant par zéro. Lorsque des caractères génériques sont utilisés, un opérateur LIKE est requis pour comparer les variables de caractères aux constantes. Autres exemples :

NOM COMME "__Columbus"

NOM COMME "__K%"

La condition du premier exemple est vraie si NAME est composé de deux caractères suivis de « Columbus ». Dans la table WORKER, tous les noms commencent par une première initiale et un point. Ainsi, en utilisant cette condition, nous. Trouvons tous les employés portant le nom de famille « Columbus ». La condition du deuxième exemple permet de retrouver tous les salariés dont le nom commence par la lettre « K ».

Demande: Trouvez tous les emplois qui commencent dans les deux prochaines semaines.

OÙ LE START_DATE ENTRE LE CURRENT_DATE ET

Résultat:(Supposons que la date actuelle soit CURRENT DATE = 10.10)

WORKER_ID BLDG_ID START_DATE NUM_DAYS

1235 312 10.10 5

1235 515 17.10 22

3231 111 10.10 8

1412 435 15.10 15

3231 312 24.10 20

1311 460 23.10 24

Cette requête illustre l'utilisation de l'opérateur BETWEEN avec des valeurs de date et d'intervalle. CURRENT_DATE est une fonction qui renvoie toujours la date du jour. Expression

CURRENT_DATE + INTERVAL "14" JOUR

ajoute une période de deux semaines à la date actuelle. Ainsi, ASSIGNMENT est sélectionné (en supposant que nous sommes aujourd'hui le 10/10) si la valeur de sa colonne START_DATE est comprise entre 10/10 et 10/24. À partir de là, nous pouvons voir que nous pouvons ajouter des valeurs d'intervalle aux champs de date. De plus, on peut multiplier les valeurs des intervalles par des valeurs entières. Par exemple, supposons que nous voulions savoir quel sera le nombre dans un certain nombre de semaines (indiqué par la variable NUM_WEEKS). Nous pouvons procéder ainsi :

CURRENT_DATE + INTERVAL "7" JOUR * NUM_WEEKS

2. Requêtes multi-tables

La possibilité de relier des éléments de données au-delà des limites d'une seule table est importante pour tout langage de base de données. En algèbre relationnelle, cette fonction est réalisée par l'opération de jointure. Bien qu'une grande partie de SQL soit basée directement sur le calcul relationnel, SQL connecte les données de différentes tables de la même manière que l'opération de jointure de l'algèbre relationnelle. Nous allons maintenant montrer comment cela se fait. Considérez la demande :

Demande:

Les données nécessaires à la réponse se trouvent dans deux tableaux : WORKER et ASSIGNMENT. La solution SQL nécessite de lister les deux tables dans la commande FROM et de spécifier un type spécial de clause WHERE :

SÉLECTIONNER SKILL_TYPE

DU TRAVAILLEUR, AFFECTATION

OÙ WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID

ET BLDG_ID = 435

Que se passe t-il ici? Il faut considérer deux étapes dans la façon dont le système traite cette demande.

1. Comme d'habitude, la clause FROM est traitée en premier. Cependant, dans ce cas, comme la commande spécifie deux tables, le système crée un produit cartésien des lignes de ces tables. Cela signifie qu'une grande table est créée (logiquement) composée de colonnes des deux tables, chaque ligne d'une table étant associée à chaque ligne de l'autre table. Dans notre exemple, comme la table WORKER comporte cinq colonnes et la table ASSIGNMENT quatre colonnes, le produit cartésien généré par la commande FROM aura neuf colonnes. Le nombre total de lignes du produit cartésien est égal à m * n, où m est le nombre de lignes de la table WORKER ; et n est le nombre de lignes dans la table ASSIGNMENT. Puisque la table WORKER comporte 7 lignes et la table ASSIGNMENT 19 lignes, le produit cartésien contiendra 7x19 ou 133 lignes. Si la commande FROM répertorie plus de deux tables, un produit cartésien de toutes les tables spécifiées dans la commande est créé.

produit cartésien. Le résultat de la jointure de chaque ligne d'une table avec chaque rangée d'un autre tableau.

2. Après avoir créé la table relationnelle géante, le système utilise la commande WHERE comme auparavant. Chaque ligne du tableau créée par la commande FROM. est vérifié pour voir si la condition WHERE est satisfaite. Les lignes qui ne satisfont pas à la condition sont exclues de la considération. La clause SELECT est ensuite appliquée aux lignes restantes.

La clause WHERE de notre requête contient deux conditions :

1. TRAVAILLEUR. WORKER_ID = ASSIGNMENT.WORKER_ID

2. BLDG_ID = 435

La première de ces conditions est la condition de jointure. Notez que puisque les tables WORKER et ASSIGNMENT contiennent une colonne nommée WORKER_ID, leur produit cartésien contiendra deux colonnes portant ce nom. Pour les différencier, on fait précéder le nom de la colonne du nom de la table source, séparé par un point.

La première condition signifie que dans toute ligne sélectionnée, la valeur de la colonne WORKER_ID de la table WORKER doit correspondre à la valeur de la colonne WORKER_ID de la table ASSIGNMENT. En réalité, nous joignons deux tables par WORKER_ID. Toutes les lignes dans lesquelles les valeurs de ces deux colonnes ne sont pas égales sont exclues de la table des produits. Exactement la même chose se produit lors de l’exécution de l’opération de jointure naturelle de l’algèbre relationnelle. (Cependant, il existe encore une différence par rapport à une jointure naturelle : SQL ne supprime pas automatiquement la colonne WORKER_ID supplémentaire). La jointure complète de ces deux tables avec la condition supplémentaire BLDG_ID = 435 est illustrée à la Fig. 1. L'utilisation de la commande SELECT donnera finalement le résultat de requête suivant :

TYPE DE COMPÉTENCE

Plâtrier

Couvreur

Électricien

Riz. 1. Rejoindre les tables WORKER et ASSIGNMENT

Nous allons maintenant montrer comment joindre une table à elle-même en SQL.

Demande: Répertoriez les employés en indiquant les noms de leurs managers.

SELECT A.WORKER_NAME, B.WORKER_NAME

DU TRAVAILLEUR A, TRAVAILLEUR B

OÙ B.WORKER_ID = A.SUPV_ID

La clause FROM dans cet exemple crée deux « copies » de la table WORKER, en leur attribuant les alias A et B. Un alias est un nom alternatif donné à la table. Ensuite, les copies A et B de la table WORKER sont jointes par la commande WHERE basée sur la condition d'égalité de WORKER_ID dans B et SUPV_ID dans A. Ainsi, chaque ligne de A est jointe à la ligne B, qui contient des informations sur le gestionnaire de la ligne A. (Fig.2).

Riz. 2. Joindre deux copies de la table WORKER

En sélectionnant deux noms d'employés dans chaque ligne, nous obtenons la liste recherchée :

A.NOMB.NOM

M. Faraday H. Columbus

K.Nemo G.Rickover R.Garrett R.Garrett

P. Mason P. Mason G. Rickover G. Rickover H. Columbus H. Columbus J. Barrister P. Mason

Surnom. Un nom alternatif donné à la table.

A.WORKER_NAME représente le travailleur et B.WORKER_NAME représente le manager. Veuillez noter que certains travailleurs sont leurs propres managers, ce qui découle de l'égalité WORKER_ID - SUPV_ID dans leurs lignes.

En SQL, vous pouvez lier plus de deux tables à la fois :

Demande

SELECT WORKER_NAME

DU TRAVAILLEUR, AFFECTATION, BÂTIMENT

OÙ WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID ET ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID ET

TYPE = "Bureau"

Résultat:

M. Faraday

G. Rickover

J.Barrister

Notez que si un nom de colonne (par exemple, WORKER_ID ou BLDG_ID) apparaît dans plusieurs tables, alors pour éviter toute ambiguïté, nous devons faire précéder le nom de la colonne du nom de la table d'origine. Mais si le nom de la colonne apparaît dans une seule table, comme TYPE dans notre exemple, alors il n'y a aucune ambiguïté et le nom de la table n'a donc pas besoin d'être spécifié.

Les commandes SQL de cette requête créent une table à partir de trois tables de base de données relationnelles. Les deux premières tables sont jointes par WORKER_ID, après quoi la troisième table est jointe par BLDG_ID à la table résultante. Condition

TYPE = "Bureau"

La clause WHERE entraîne l'exclusion de toutes les lignes, à l'exception de celles des immeubles de bureaux. Cela répond aux exigences de la demande.

3. Sous-requêtes

Sous-requête. Requête dans une requête

Une sous-requête peut être placée dans la clause WHERE d'une requête, étendant ainsi les capacités de la clause WHERE. Regardons un exemple.

Demande: Quelles sont les spécialités des ouvriers affectés au bâtiment 435 ?

SÉLECTIONNER SKTLL_TYPE

FROM WORKER WHERE WORKER_ID IN

(SELECT WORKER_ID

OÙ BLDG_ID = 435)

Sous-requête dans cet exemple

(SELECT WORKER_ID

OÙ BLDG_ID = 435)

Une requête qui contient une sous-requête est appelée demande externe ou demande principale. La sous-requête entraîne la création de l'ensemble d'ID d'employé suivant :

ID TRAVAILLEUR

Demande externe. La requête principale, qui contient toutes les sous-requêtes.

Cet ensemble d'identifiants remplace alors une sous-requête dans la requête externe. A partir de ce moment, la requête externe est exécutée en utilisant l'ensemble créé par la sous-requête. La requête externe traite chaque ligne de la table WORKER selon la clause WHERE. Si le WORKER_ID d'une ligne se trouve dans l'ensemble (IN) créé par la sous-requête, alors le SKILL_TYPE de la ligne est sélectionné et affiché dans le tableau résultant :

TYPE DE COMPÉTENCE

Plâtrier

Couvreur

Électricien

Il est très important que la clause SELECT de la sous-requête contienne le WORKER_ID et uniquement le WORKER_ID. Sinon, la clause WHERE de la requête externe, signifiant que WORKER_ID est dans l'ensemble des ID de travailleur, n'aurait aucune signification.

Notez qu'une sous-requête peut logiquement être exécutée avant qu'au moins une ligne ne soit prise en compte par la requête principale. En un sens, une sous-requête est indépendante de la requête principale. Il peut être exécuté comme une requête complète. On dit qu’une telle sous-requête n’est pas corrélée à la requête principale. Comme nous le verrons bientôt, les sous-requêtes peuvent être corrélées.

Sous-requête non corrélée. Une sous-requête dont la valeur est indépendante de toute requête externe.

Voici un exemple de sous-requête dans une sous-requête.

Demande: Dressez la liste des employés affectés aux immeubles de bureaux.

Examinons à nouveau la requête avec laquelle nous avons examiné la connexion.

SELECT WORKER_MAME

OÙ WORKER_ID DANS

(SELECT WORKER_ID

OÙ BLDG_ID DANS

OÙ TYPE = "Bureau"))

Résultat:

M. Faraday

G. Rickover

J.Barrister

Notez que nous n'avons pas besoin de préfixer les noms de colonnes avec les noms de tables, puisque chaque sous-requête traite une et une seule table, donc aucune ambiguïté ne peut survenir.

L'exécution des requêtes s'effectue dans un ordre inversé. Autrement dit, la requête la plus interne (ou « la plus basse ») est exécutée en premier, puis la sous-requête la contenant est exécutée, puis la requête externe.

Sous-requêtes corrélées. Toutes les sous-requêtes évoquées ci-dessus étaient indépendantes des requêtes principales dans lesquelles elles étaient utilisées. Par indépendant, nous entendons que les sous-requêtes peuvent être exécutées seules en tant que requêtes complètes. Nous passons maintenant à considérer une classe de sous-requêtes dont les résultats d'exécution peuvent dépendre de la ligne considérée par la requête principale. De telles sous-requêtes sont appelées sous-requêtes corrélées.

Sous-requête corrélée. Une sous-requête dont le résultat dépend de la ligne considérée par la requête principale.

Demande: Répertoriez les employés dont les taux horaires sont supérieurs à ceux de leurs managers.

SELECT WORKER_NAME

OÙ A.HRLY_RATE >

(SELECT B.HRLY_RATE

OÙ B.WORKER_ID = A.SUPV_ID)

Résultat:

Les étapes logiques pour exécuter cette requête sont :

1. Le système crée deux copies de la table WORKER : la copie A et la copie B. Selon la façon dont nous les avons définies, A fait référence à l'employé, B fait référence au manager.

2. Le système considère ensuite chaque ligne A. Une ligne donnée est sélectionnée si elle satisfait à la condition WHERE. Cette condition signifie qu'une ligne sera sélectionnée si sa valeur HRLY_RATE est supérieure au HRLY_RATE généré par la sous-requête.

3. La sous-requête sélectionne la valeur HRLY_RATE de la ligne B, dont WORKER_ID est égal à SUPV_ID de la ligne A, dans ce moment pris en compte par la demande principale. Il s'agit du HRLY_RATE du manager.

Notez que puisque A.HRLY_RATE ne peut être comparé qu'à une seule valeur, la sous-requête ne doit renvoyer qu'une seule valeur. Cette valeur change en fonction de la ligne A considérée. Ainsi, la sous-requête est corrélée à la requête principale. Nous verrons d'autres exemples de sous-requêtes corrélées plus tard lorsque nous étudierons les fonctions intégrées.

Opérateurs EXISTS et NOT EXISTS

Supposons que nous souhaitions identifier les travailleurs qui ne sont pas affectés à travailler dans un certain bâtiment. À première vue, il semble qu’une telle demande puisse être facilement satisfaite en rejetant simplement la version affirmative de la demande. Supposons, par exemple, que nous soyons intéressés par un bâtiment avec BLDG_ID 435. Considérons la demande :

SELECT WORKER_ID

OÙ BLDG_ID PAS 435

Malheureusement, il s’agit d’une formulation incorrecte de la solution. La demande nous donnera simplement les identifiants des travailleurs travaillant dans d’autres bâtiments. Bien évidemment, certains d’entre eux peuvent également être affectés au bâtiment 435.

Une solution correctement formulée utilise l'opérateur NOT EXISTS :

SELECT WORKER_ID

OÙ N'EXISTE PAS

OÙ ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID ET

Résultat:

WORKER_ID

Les opérateurs EXISTS et NOT EXISTS sont toujours placés avant la sous-requête. EXISTS est évalué à true si l'ensemble généré par la sous-requête n'est pas vide. Si l'ensemble généré par la sous-requête est vide, alors EXISTS prend la valeur « false ». L’opérateur NOT EXISTS, bien sûr, fonctionne exactement à l’opposé. C'est vrai si le résultat de la sous-requête est vide, et faux dans le cas contraire.

Opérateur EXISTE. Renvoie vrai si le jeu de résultats n’est pas vide.

Opérateur NON EXISTE. Renvoie vrai si le jeu de résultats est vide.

Dans cet exemple, nous avons utilisé l'opérateur NOT EXISTS. La sous-requête sélectionne toutes les lignes de la table ASSIGNMENT dans lesquelles le WORKER_ID a la même valeur que la ligne considérée par la requête principale, et le BLDG_ID est égal à 435. Si cet ensemble est vide, alors la ligne du travailleur considérée par la requête principale est sélectionné, puisque cela signifie que Cet employé ne travaille pas dans le bâtiment 435.

Dans la solution que nous avons fournie, nous avons utilisé une sous-requête corrélée. Si nous utilisons l'opérateur IN au lieu de NOT EXISTS, nous pouvons nous contenter d'une sous-requête non corrélée :

SELECT WORKER_ID

OÙ WORKER_ID PAS DANS

(SELECT WORKER_ID

OÙ BLDG_ID = 435)

Cette solution est plus simple que la solution avec l'opérateur NOT EXISTS. Une question naturelle se pose : pourquoi avons-nous besoin d'EXISTE et de NE PAS EXISTE du tout ? La réponse est que NOT EXISTS est le seul moyen de résoudre les requêtes contenant le mot « every » dans la condition. De telles requêtes sont résolues en algèbre relationnelle à l'aide de l'opération de division et en calcul relationnel à l'aide du quantificateur universel. Voici un exemple de requête avec le mot « every » dans sa condition :

Demande:Énumérez les employés affectés à chaque bâtiment.

Cette question peut être implémentée en SQL en utilisant des doubles négations. Nous reformulerons la requête pour inclure un double négatif :

Demande:Énumérez les employés pour lesquels Pas il y a un bâtiment auquel ils ne sont pas affectés.

Nous avons souligné le double point négatif. Force est de constater que cette demande est logiquement équivalente à la précédente.

Nous voulons maintenant formuler la solution en SQL. Pour faciliter la compréhension de la solution finale, nous donnons d'abord une solution à un problème préliminaire : le problème de l'identification de tous les bâtiments pour lesquels un ouvrier hypothétique, "1234" Pas nommé.

(I) SÉLECTIONNER BLDG_ID

OÙ N'EXISTE PAS

ASSIGNMENT.WORKER_ID = 1234)

Nous avons marqué cette requête (I) car nous y reviendrons plus tard. S'il n'y a aucun bâtiment qui satisfait à cette demande, alors le travailleur 1234 est affecté à chaque bâtiment et satisfait donc aux conditions de la demande initiale. Afin d'obtenir une solution à la requête d'origine, nous devons généraliser la requête (I) d'un travailleur spécifique 1234 à la variable WORKER_ID et transformer cette requête modifiée en une sous-requête de la requête plus large. Voici la solution :

(II) SÉLECTIONNER WORKER_ID

OÙ N'EXISTE PAS

OÙ N'EXISTE PAS

OÙ ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID ET

ASSIGNMENT.WORKER_ID = TRAVAILLEUR.WORKER_ID)

Résultat:

ID TRAVAILLEUR

Notez que la sous-requête commençant sur la quatrième ligne de la requête (II) est identique à la requête (I), avec "1234" remplacé par WORKER.WORKER_ID. La requête (II) peut se lire comme suit :

Sélectionnez WORKER_ID dans WORKER s'il n'y a aucun bâtiment auquel WORKER_ID n'est pas attribué.

Cela correspond aux conditions de la demande initiale.

Nous voyons que l'opérateur NOT EXISTS peut être utilisé pour formuler les requêtes qui nécessitent une opération de division en algèbre relationnelle et un quantificateur universel en calcul relationnel. Du point de vue de la facilité d'utilisation, l'opérateur NOT EXISTS n'offre aucun avantage particulier, ce qui signifie que les requêtes SQL qui utilisent NOT EXISTS deux fois ne sont pas plus faciles à comprendre que les solutions d'algèbre relationnelle avec division ou les solutions de calcul relationnel avec quantificateurs universels. Des recherches supplémentaires seront nécessaires pour créer des constructions linguistiques permettant de résoudre ces requêtes plus naturellement.

Fonctions intégrées

Considérons des questions de ce type :

Quels sont les taux horaires maximum et minimum ? Quel est le nombre moyen de jours de travail des employés dans le bâtiment 435 ? Quel est le nombre total de jours alloués aux travaux de plâtrerie du bâtiment 312 ? Combien y a-t-il de spécialités différentes ?

Répondre à ces questions nécessite des fonctions statistiques qui examinent de nombreuses lignes d'un tableau et renvoient une valeur unique. Il existe cinq fonctions de ce type dans SQL, appelées fonctions intégrées ou fonctions définies. Ces fonctions sont SUM (somme), AVG (moyenne), COUNT (quantité), MAX (maximum) et MIN (minimum).

Fonction intégrée (fonction définie). Une fonction statistique qui opère sur plusieurs lignes : SUM (somme), AVG (moyenne), COUNT (quantité), MAX (maximum), MIN (minimum).

Demande: Quels sont les taux horaires maximum et minimum ?

SÉLECTIONNER MAX(HRLY_RATE), MIN(HRLY_RATE)

Résultat: 17.40, 8.20

Fonctions MAX et MIN fonctionnent sur une colonne du tableau. Ils sélectionnent respectivement la valeur maximale ou minimale dans cette colonne. Notre formulation de requête ne contient pas de clause WHERE. Pour la plupart des requêtes, cela peut ne pas être le cas, comme le montre notre exemple suivant.

Demande: Quel est le nombre moyen de jours de travail des employés dans le bâtiment 435 ?

SÉLECTIONNER MOYENNE (NUM_DAYS)

OÙ BLDG_ID =435

Résultat: 12.33

Demande: Quel est le nombre total de jours alloués aux travaux de plâtrerie du bâtiment 312 ?

SELECT SUM(NUM_DAYS)

DE L'AFFECTATION, TRAVAILLEUR

OÙ WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID ET

SKILL_TYPE = "Plâtrier" ET

Résultat: 27

La solution utilise une jointure entre les tables ASSIGNMENT et WORKER. Cela est nécessaire car SKILL_TYPE se trouve dans la table WORKER et BLDG_ID se trouve dans la table ASSIGNMENT.

Demande: Combien y a-t-il de spécialités différentes ?

SELECT COUNT (DISTINCT SKILL_TYPE)

Résultat: 4

Étant donné que la même spécialité peut apparaître sur plusieurs lignes différentes, vous devez utiliser le mot-clé DISTINCT dans cette requête pour empêcher le système de compter plusieurs fois le même type de spécialité. L'opérateur DISTINCT peut être utilisé avec n'importe laquelle des fonctions intégrées, bien qu'il soit bien entendu redondant avec les fonctions MAX et MIN.

DISTINCT. Un opérateur qui élimine les lignes en double.

Les fonctions SUM et AVG ne doivent être utilisées qu'avec des colonnes numériques. D'autres fonctions peuvent être utilisées avec des données numériques et caractères. Toutes les fonctions, à l'exception de COUNT, peuvent être utilisées avec des expressions calculées. Par exemple:

Demande: Quel est le salaire hebdomadaire moyen ?

SÉLECTIONNER MOYENNE (40 * HRLY_RATE)

Résultat: 509.14

COUNT peut faire référence à une ligne entière plutôt qu'à une colonne individuelle :

Demande: Combien de bâtiments ont le niveau de qualité 3 ?

SÉLECTIONNER LE COMPTE (*)

DU BÂTIMENT OÙ

Résultat: 3

Comme le montrent tous ces exemples, si une commande SELECT contient une fonction intégrée, alors rien d'autre ne peut apparaître dans cette commande SELECT. La seule exception à cette règle concerne la clause GROUP BY, que nous allons examiner maintenant.

Clauses GROUP BY et HAVING

En gestion, des informations statistiques sur chaque groupe dans de nombreux groupes sont souvent nécessaires. Par exemple, considérons la requête suivante :

Demande: Pour chaque manager, découvrez le taux horaire maximum parmi ses subordonnés.

Afin de résoudre ce problème, nous devons diviser les travailleurs en groupes selon leurs managers. Nous déterminerons ensuite l'enchère maximale au sein de chaque groupe. En SQL, cela se fait de cette façon :

GROUPER PAR SUPV_ID

Résultat:

SUPV_IDMAX(TAUX HRLY)

Lors du traitement de cette requête, le système divise d'abord les lignes de la table WORKER en groupes en utilisant la règle suivante. Les lignes sont placées dans le même groupe si et seulement si elles ont le même SUPV_ID. La clause SELECT est ensuite appliquée à chaque groupe. Puisqu’il n’y a qu’une seule valeur SUPV_ID dans ce groupe, il n’y a aucune incertitude SUPV_ID dans le groupe. Pour chaque groupe, la clause SELECT génère le SUPV_ID et calcule et génère également la valeur MAX(HRLY_RATE). Le résultat est présenté ci-dessus.

Dans une commande SELECT avec des fonctions intégrées, seules les colonnes incluses dans la clause GROUP BY peuvent apparaître. Notez que SUPV_ID peut être utilisé dans une commande SELECT car il est inclus dans la clause GROUP BY.

Clause GROUPE PAR. Indique que les lignes doivent être divisées en groupes avec des valeurs communes de la ou des colonnes spécifiées.

La clause GROUP BY permet d'effectuer certains calculs complexes. Par exemple, nous pourrions vouloir connaître la moyenne de ces enchères maximales. Cependant, le calcul avec des fonctions intégrées est restreint dans le sens où il ne permet pas d'utiliser des fonctions intégrées dans d'autres fonctions intégrées. Donc une expression comme

MOYENNE(MAX(HRLY_RATE))

interdit. La mise en œuvre d'une telle demande comprendra deux étapes. Nous devons d’abord mettre les offres maximales dans un nouveau tableau, et dans un deuxième temps, nous devons calculer leur moyenne.

Vous pouvez utiliser la clause WHERE avec la commande GROUP BY :

Demande: Pour chaque type de bâtiment découvrez niveau moyen qualité parmi les immeubles de statut 1.

SÉLECTIONNER LE TYPE, MOYENNE(QLTY_LEVEL)

OÙ STATUT = 1

Résultat:

TYPEAVG(QLTY_LEVEL)

Magasin 1

Immeuble résidentiel 3

La clause WHERE est exécutée avant l'instruction GROUP BY. Ainsi, aucun groupe ne peut contenir une ligne ayant un statut autre que 1. Les lignes de statut 1 sont regroupées par valeur TYPE, puis une clause SELECT est appliquée à chaque groupe.

AVOIR une expression. Impose des conditions aux groupes.

Nous pouvons également appliquer des conditions aux groupes créés par la clause GROUP BY. Cela se fait en utilisant la phrase HAVING. Supposons, par exemple, que nous décidions de rendre l'une des requêtes précédentes plus spécifique :

Demande: Pour chaque manager qui a plus d'un subordonné, découvrez le taux horaire maximum parmi ses subordonnés.

Nous pouvons refléter cette condition avec la commande HAVING appropriée :

SELECT SUPV_ID, MAX(HRLY_RATE)

DU GROUPE DE TRAVAIL PAR SUPV_ID

AYANT UN COMPTE(*) > 1

Résultat:

SUPV_ID MAX(HRLY_RATE)

La différence entre les clauses WHERE et HAVING est que WHERE s'applique aux lignes, tandis que HAVING s'applique aux groupes.

Une requête peut contenir à la fois une clause WHERE et une clause HAVING. Dans ce cas, la clause WHERE est exécutée en premier car elle est exécutée avant le regroupement. Par exemple, considérons la modification suivante de la requête précédente :

Demande: Pour chaque type de bâtiment, connaître le niveau de qualité moyen parmi les bâtiments de statut 1. Ne considérer que les types de bâtiments dont le niveau de qualité maximum ne dépasse pas 3.

SÉLECTIONNER LE TYPE, MOYENNE (QLTY_JLEVEL)

OÙ STATUT = 1

AYANT MAXIMUM(QLTY_LEVEL)<= 3

Résultat:

TYPE MOYENNE(QLTY_LEVEL)

Magasin 1

Immeuble résidentiel 3

Notez qu'en commençant par la clause FROM, les clauses sont exécutées dans l'ordre, puis la clause SELECT est appliquée. Ainsi, la clause WHERE est appliquée à la table BUILDING, et toutes les lignes dans lesquelles STATUS est différent de 1 sont supprimées. Les lignes restantes sont regroupées par TYPE ; toutes les lignes avec la même valeur TYPE se retrouvent dans le même groupe. Ainsi, plusieurs groupes sont créés, un pour chaque valeur TYPE. La clause HAVING est ensuite appliquée à chaque groupe et les groupes dont la valeur de niveau de qualité maximale dépasse 3 sont supprimés. Enfin, la clause SELECT est appliquée aux groupes restants.

7. Fonctions et sous-requêtes intégrées

Les fonctions intégrées ne peuvent être utilisées que dans une clause SELECT ou une commande HAVING. Cependant, une clause SELECT contenant une fonction en ligne peut faire partie d'une sous-requête. Regardons un exemple d'une telle sous-requête :

Demande: Quels travailleurs ont un taux horaire supérieur à la moyenne ?

SELECT WORKER_NAME

OÙ HRLY_RATE >

(SELECT AVG(HRLY_RATE)

Résultat:

H. Colomb

Notez que la sous-requête n'est pas corrélée à la requête principale. La sous-requête renvoie exactement une valeur : le taux horaire moyen. La requête principale sélectionne un travailleur uniquement si son taux est supérieur à la moyenne calculée.

Les requêtes corrélées peuvent également utiliser des fonctions intégrées :

Requête : Quel employé a un taux horaire supérieur au taux horaire moyen parmi les subordonnés du même manager ?

Dans ce cas, au lieu de calculer un taux horaire moyen pour tous les travailleurs, il faut calculer le taux moyen pour chaque groupe de travailleurs relevant du même gestionnaire. De plus, notre calcul doit être refait pour chaque travailleur considéré par la requête principale :

SELECT A. WORKER_NAME

SQL vous permet d'imbriquer les requêtes les unes dans les autres. Généralement, une sous-requête renvoie une valeur unique, qui est vérifiée pour voir si le prédicat est vrai.

Types de termes de recherche :
. Comparaison avec le résultat d'une sous-requête (=, >=)
. Vérification de l'appartenance aux résultats d'une sous-requête (IN)
. Contrôle d'existence (EXISTE)
. Comparaison multiple (quantitative) (TOUT, TOUS)

Notes sur les requêtes imbriquées :
. Une sous-requête ne doit sélectionner qu'une seule colonne (sauf pour une sous-requête avec un prédicat EXISTS) et son type de données de résultat doit correspondre au type de données de la valeur spécifiée dans le prédicat.
. Dans certains cas, vous pouvez utiliser le mot clé DISTINCT pour garantir qu'une seule valeur est renvoyée.
. Vous ne pouvez pas inclure de clause ORDER BY ou UNION dans une sous-requête.
. La sous-requête peut être située à gauche ou à droite de la condition de recherche.
. Les sous-requêtes peuvent utiliser des fonctions d'agrégation sans clause GROUP BY, qui renvoient automatiquement une valeur spéciale pour un nombre quelconque de lignes, un prédicat IN spécial et des expressions basées sur des colonnes.
. Dans la mesure du possible, vous devez utiliser des jointures de table JOIN au lieu de sous-requêtes.

Exemples de requêtes imbriquées:

SELECT * FROM Commandes WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
SELECT * FROM Orders WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City=’Londres’)
SELECT * FROM Commandes WHERE SNum=(SELECT DISTINCT SNum FROM Commandes WHERE CNum=2001)
SELECT * FROM Commandes WHERE Montant>(SELECT AVG(Montant) FROM Commandes WHERE Odate=10/04/1990)
SELECT * FROM Client WHERE CNum=(SELECT SNum+1000 FROM SalesPeople WHERE SName=’Serres’)

2) Sous-requêtes associées

En SQL, vous pouvez créer des sous-requêtes faisant référence à une table à partir d'une requête externe. Dans ce cas, la sous-requête est exécutée plusieurs fois, une fois pour chaque ligne de table de la requête externe. Il est donc important que la sous-requête utilise l'index. Une sous-requête peut accéder à la même table qu'une sous-requête externe. Si la requête externe renvoie un nombre relativement petit de lignes, alors la sous-requête liée sera plus rapide que la sous-requête non liée. Si une sous-requête renvoie un petit nombre de lignes, la requête associée sera plus lente que la requête non associée.

Exemples de sous-requêtes associées :

SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //renvoie toutes les commandes dont la valeur dépasse la valeur moyenne des commandes pour un client donné

3) Le prédicat EXISTE

Forme syntaxique : EXISTE ()

Le prédicat prend une sous-requête comme argument et est évalué à vrai si la sous-requête a une sortie et à faux dans le cas contraire. La sous-requête est exécutée une fois et peut contenir plusieurs colonnes, puisque leurs valeurs ne sont pas vérifiées, mais le résultat de la présence de lignes est simplement enregistré.

Notes sur le prédicat EXISTS :
. EXISTS est un prédicat qui renvoie TRUE ou FALSE et peut être utilisé seul ou avec d'autres expressions booléennes.
. EXISTS ne peut pas utiliser de fonctions d'agrégation dans sa sous-requête.
. Dans les sous-requêtes corrélées, le prédicat EXISTS est exécuté pour chaque ligne de la table externe.
. Vous pouvez combiner le prédicat EXISTS avec des jointures de table.

Exemples pour le prédicat EXISTS :

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City=’San Jose’) – renvoie tous les clients si l’un d’entre eux vit à San Jose.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum) – renvoie le nombre de vendeurs qui n’ont servi qu’un seul client.
SELECT DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – renvoie numéros, noms et villes de résidence de tous les vendeurs ayant servi plusieurs clients.
SELECT * FROM SalesPeople First WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) Prédicats de comparaison quantitative

Forme syntaxique : (=|>|=|) TOUT|TOUS ()

Ces prédicats utilisent une sous-requête comme argument, cependant, par rapport au prédicat EXISTS, ils sont utilisés conjointement avec des prédicats relationnels (=,>=). En ce sens, ils sont similaires au prédicat IN, mais ne sont utilisés qu'avec des sous-requêtes. La norme vous permet d'utiliser le mot-clé SOME au lieu de ANY, mais tous les SGBD ne le prennent pas en charge.

Notes sur les prédicats de comparaison :
. Le prédicat ALL est évalué à TRUE si chaque valeur sélectionnée lors de l'exécution de la sous-requête satisfait à la condition spécifiée dans le prédicat de requête externe. Il est le plus souvent utilisé avec les inégalités.
. Le prédicat ANY est évalué à TRUE si au moins une valeur sélectionnée lors de l'exécution de la sous-requête satisfait la condition spécifiée dans le prédicat de requête externe. Il est le plus souvent utilisé avec les inégalités.
. Si la sous-requête ne renvoie aucune ligne, alors ALL prend automatiquement la valeur TRUE (on considère que la condition de comparaison est satisfaite), et pour ANY elle prend la valeur FALSE.
. Si la comparaison est VRAIE pour aucune ligne et qu'il existe une ou plusieurs lignes avec une valeur NULL, alors ANY renvoie INCONNU.
. Si la comparaison est FALSE pour aucune ligne et qu'il y a une ou plusieurs lignes avec une valeur NULL, alors ALL renvoie INCONNU.

Exemples pour le prédicat de comparaison quantitative :

SELECT * FROM SalesPeople WHERE City=ANY (SELECT City FROM Client)
SELECT * FROM Commandes WHERE Amt ALL (SELECT Note FROM Client WHERE City='Rome')

5) Prédicat d'unicité

UNIQUE|DISTINCT ()

Le prédicat est utilisé pour vérifier l'unicité (absence de doublons) dans les données de sortie de la sous-requête. De plus, dans le prédicat UNIQUT, les chaînes avec des valeurs NULL sont considérées comme uniques, et dans le prédicat DISTINCT, deux valeurs non définies sont considérées comme égales l'une à l'autre.

6) Correspondances de prédicats

CORRESPONDRE ()

Le prédicat MATCH teste si la valeur d'une chaîne de requête correspondra à la valeur de n'importe quelle chaîne résultant de la sous-requête. Cette sous-requête diffère des prédicats IN AND ANY en ce sens qu'elle permet de traiter les correspondances « partielles » (PARTIAL) qui peuvent se produire entre des lignes comportant des valeurs NULL.

7) Requêtes dans la section FROM

En fait, il est légal d'utiliser une sous-requête partout où une référence à une table est autorisée.

SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE City='Londres' AND Customer.CNum=Orders.CNum
//sous-requête renvoie le montant total des commandes passées par chaque client depuis Londres.

8) Requêtes récursives

AVEC RÉCURSIF
Q1 AS SELECT … FROM … OÙ …
Q2 AS SELECT … FROM … OÙ …