Introduction aux requêtes SQL

Michael J. Hernandez
Introduction
aux requêtes SQL
ISBN 2-212-09272-5
Avant-propos
« Le langage est par sa nature même un élément collectif :
il n’exprime jamais un concept précis
mais un compromis qui nous lie vous, moi et tous les autres. »
Thomas Ernest Hulme, Spéculations
La récupération d'informations sur une base de données est en général un exercice complexe.
Elle peut néanmoins se révéler relativement aisée à partir du moment où l'on comprend la
question posée à la base de données. Une fois comprise, la question peut être traduite vers le
langage utilisé par n'importe quel système de base de données. Dans la plupart des cas, il s'agit
de SQL (Structured Query Language). Votre requête doit prendre la forme d'une instruction
SQL pour que le système de base de données puisse identifier les informations que vous
souhaitez récupérer. SQL vous permet ainsi de communiquer avec votre système de base de
données.
Notre longue expérience de consultants sur les bases de données nous a permis de constater
que les individus souhaitant simplement récupérer des informations sur une base de données
sont nettement plus nombreux que ceux chargés de développer des programmes et des applications pour ces bases. Malheureusement, il n'existe aucun ouvrage consacré exclusivement à
la récupération d'informations, en particulier du point de vue du « simple mortel ». Il existe
naturellement un grand nombre d'ouvrages intéressants sur SQL mais la plupart traitent de la
programmation et du développement des bases de données.
C'est pourquoi nous avons pensé qu'il était temps de publier un ouvrage vous apprenant à
réaliser une requête correctement et efficacement. Vous avez entre les mains le fruit de notre
réflexion. Parmi les ouvrages traitant de SQL, celui-ci est unique en son genre dans le sens où
il ne concerne que les requêtes. Une fois que vous l'aurez lu, vous disposerez de toutes les
compétences nécessaires à la récupération des informations dont vous avez besoin.
La rédaction d'un tel ouvrage ne peut être l'œuvre d'une seule personne. Des rédacteurs, des
collègues, des amis et des parents nous ont témoigné tout leur soutien et nous ont prodigué de
2
Introduction aux requêtes SQL
précieux conseils au moment le plus opportun. Ces personnes nous ont encouragés et nous ont
aidés à rester motivés pour mener ce projet à bien.
Tout d'abord, nous souhaiterions remercier notre éditrice Mary O’Brien pour nous avoir
permis d'écrire ce livre. Elle a su deviner tout le potentiel de l'idée de départ et s'est entièrement consacrée à ce projet. Nous remercions Mary et son assistante Mariann Kourafas pour
leur infinie patience et leur soutien constant tout au long de la rédaction de cet ouvrage. Merci
également à Marilyn Rash et à l'équipe de production, et bravo à tous !
Nous souhaiterions également remercier nos rédacteurs techniques : Malcom C. Rubel,
Michael Blaha, Alexander Tarasul et Keith W. Hare. Malcom, comme toujours, travailler avec
toi fut un grand plaisir ! Michael et Alexander, merci pour tous vos commentaires et suggestions fort utiles. Nous remercions tout particulièrement Keith pour avoir corrigé quelques
petites erreurs dans l'historique de SQL et pour toutes les informations présentées dans la
section « Ce que le futur nous réserve » du Chapitre 3. Merci encore à vous tous pour le temps
et les efforts que vous avez consacrés au projet et qui nous ont permis de rédiger un traité
exhaustif sur les requêtes SQL.
Enfin, nous remercions tout particulièrement Joe Celko pour son avant-propos. Joe est non
seulement expert en SQL mais c'est également un collègue et un bon ami. Nous respectons
son savoir et son expertise sur le sujet et sommes fiers qu'il ait accepté de nous faire part de
son opinion et de ses commentaires en début d'ouvrage.
J'adresse mes plus sincères remerciements à mon ami et collègue John L. Viescas pour m'avoir
donné l'opportunité de rédiger cet ouvrage avec lui. C'est lui qui est à l'origine du projet et qui
m'a convaincu d’y prendre part. John n'en est pas à son coup d'essai dans le domaine et il est
un auteur respecté et reconnu. C'est donc un honneur pour moi d'avoir collaboré avec lui sur
ce projet.
Enfin, je souhaiterais remercier Kendra, mon épouse. Elle s'est une fois de plus montrée très
patiente alors que je travaillais d'arrache-pied sur ce projet. Son aide a été inestimable et je lui
en suis infiniment reconnaissant.
Michael J. Hernandez,
Bellevue, Washington
Eh bien, Mike ! Je suis flatté de tous ces compliments, mais n’exagérons rien… Je t'ai peutêtre convaincu de collaborer à la rédaction de cet ouvrage, mais c'est bien toi qui as donné
corps à un projet adapté à un public de « simples mortels ». Expliquer le monde complexe de
SQL (c'est d'ailleurs l'un de mes thèmes de prédilection) à un si large public s'est révélé être
une tâche à la fois amusante et intéressante. Merci de m'avoir permis de le faire.
Contrairement à la tienne, mon épouse Suzanne ne déteste pas les marques publiques d'affection. Nous lui devons tous les deux un grand merci, non seulement pour m'avoir supporté
pendant que je me consacrais entièrement à cet ouvrage, mais également pour ses excellents
commentaires critiques et ses corrections. Elle s'y connaît en ordinateurs, mais elle est bel et
bien une « simple mortelle » lorsqu'il s'agit de bases de données. Elle représentait donc la
cible type lorsqu'il s'agissait de tester les premiers jets des chapitres. J'ai seulement dû lui
promettre une chose : cet été, pour nos vacances à Hawaï, je laisserai mon ordinateur portable
à la maison !
John L.Viescas
Austin, Texas
4
Création d’une requête simple
« Pensez comme un sage, mais communiquez dans la langue des gens ordinaires. »
William Butler Yeats
Dans ce chapitre :
• La commande SELECT
• Opposition entre données et informations
• Traduction de la requête en SQL
• Suppression des doublons
• Tri des informations
• Sauvegarde
• Exemples de commandes
• Problèmes à résoudre
Introduction
Parmi toutes les commandes de SQL, SELECT a un statut vraiment particulier car c’est
l’instruction la plus puissante et la plus complexe de ce langage : elle permet de retrouver les
informations stockées dans les tables d’une base de données. On utilise SELECT conjointement avec des mots-clés et des clauses afin de trouver et de visualiser quasiment toutes les
informations possibles. SELECT permet de répondre à toutes les questions imaginables. Pour
peu que vous ayez conçu correctement votre base de données et que vous y ayez introduit des
informations fiables, vous obtiendrez toutes les réponses nécessaires pour prendre les bonnes
décisions et mener à bien votre projet.
SELECT peut être décomposé en trois opérations que nous appellerons commande SELECT,
expression SELECT et requête SELECT. (Cette décomposition a un but pédagogique : mieux
64
Les bases de SQL
PARTIE 2
vaut immédiatement appréhender la complexité de SELECT.) Chacune de ces opérations a
son propre ensemble de mots-clés et de clauses permettant de créer une commande SQL
finale. Comme vous l’apprendrez au fil de cet ouvrage, vous pouvez même combiner ces
opérations de différentes manières pour répondre à des questions très complexes.
Dans ce chapitre, nous commencerons par la commande SELECT et nous aborderons brièvement la requête SELECT. Nous approfondirons la commande SELECT dans les deux chapitres suivants.
INFO
Dans les livres ou des magazines traitant des bases de données relationnelles, on rencontre relativement
souvent les termes « enregistrement » et « champ » utilisés au lieu de ligne et de colonne. Dans ce livre,
nous employons exclusivement ligne et colonne afin de rester cohérents avec la norme SQL.
La commande SELECT
La commande SELECT est l’élément-clé de toute question posée à une base de données.
Quand on crée et exécute une commande SELECT, on interroge la base de données à l’aide
d’une requête. En fait, de nombreux SGBDR permettent de sauvegarder les commandes
SELECT sous la forme d’une requête, d’une vue ou d’une procédure stockée. En fonction du
SGBDR utilisé, les commandes SELECT peuvent être exécutées directement à partir d’une
ligne de commande, d’une grille QBE (Query By Example ou Requête par l’exemple) ou bien
à partir d’un bloc de code. Quelle que soit la manière dont elle est définie ou exécutée, la
syntaxe de la commande SELECT est toujours identique.
Principales clauses d’une commande SELECT
Une commande SELECT est composée de plusieurs mots-clés appelés clauses. On définit une
commande SELECT en utilisant diverses combinaisons de ces clauses pour retrouver l’information recherchée. Certaines clauses sont obligatoires alors que d’autres sont optionnelles.
De plus, chaque clause comporte un ou plusieurs mots-clés qui représentent des valeurs obligatoires ou optionnelles. La Figure 4.1 montre un diagramme de la commande SELECT et de
ses clauses.
INFO
Le diagramme de la Figure 4.1 montre une commande SELECT dont la syntaxe est rudimentaire. Nous
affinerons ce diagramme au fil de l’étude de SELECT. Les lecteurs connaissant déjà les notions élémentaires de SQL devront se montrer patients.
Voici un petit résumé des clauses d’une commande SELECT.
• SELECT – Il s’agit de la clause principale de la commande SELECT et elle est absolument obligatoire. On l’utilise pour spécifier les colonnes de la requête qui sont extraites de
la table ou de la vue spécifiée dans la clause FROM. (On peut aussi les extraire simultanément de plusieurs tables mais nous aborderons cet aspect dans la troisième partie de cet
ouvrage.) On peut aussi utiliser dans cette clause des fonctions de totalisation comme
Sum(HeuresTravaillées) ou des expressions mathématiques, comme Quantité ¥ Prix.
• FROM – C’est la deuxième clause la plus importante de la commande SELECT et elle est
également obligatoire. On utilise la clause FROM pour spécifier les tables dont on extrait
les colonnes listées dans la clause SELECT. On peut employer cette clause de manière plus
complexe mais nous verrons cet aspect ultérieurement.
Création d’une requête simple
CHAPITRE 4
Figure 4-1
Diagramme de la commande SELECT.
• WHERE – C’est une clause optionnelle qu’on utilise pour filtrer les lignes retournées par
la clause FROM. Le mot-clé WHERE est suivi d'une expression (dont l’appellation technique est prédicat) à laquelle on attribue la valeur Vrai, Faux ou Inconnu. On peut tester
l’expression en utilisant les opérateurs de comparaison standard, des opérateurs booléens
ou des opérateurs spéciaux. Nous aborderons les éléments de la clause WHERE au
Chapitre 6.
• GROUP BY – Quand on utilise des fonctions de totalisation dans la clause SELECT pour
produire une synthèse des informations, on emploie la clause GROUP BY pour diviser les
informations en groupes distincts. Votre base de données utilise n’importe quelle colonne
ou groupe de colonnes suivant la clause GROUP BY comme colonne de regroupement. La
clause GROUP BY est optionnelle et nous l’étudierons au Chapitre 13.
• HAVING – La clause HAVING est associée à la clause GROUP BY ; on l’utilise pour
filtrer les informations regroupées. Elle est similaire à la clause WHERE dans le sens où le
mot-clé HAVING est suivi d'une expression évaluée à Vrai, Faux ou Inconnu. On peut
tester l’expression en utilisant les opérateurs de comparaison standard, des opérateurs
booléens ou des opérateurs spéciaux. HAVING est également une clause optionnelle et
nous l’approfondirons au Chapitre 14.
Nous allons commencer par travailler avec une commande SELECT très basique et nous nous
concentrerons donc sur les clauses SELECT et FROM. Nous étudierons progressivement les
autres clauses au fil des chapitres afin de parvenir à une commande SELECT plus complexe.
65
66
Les bases de SQL
PARTIE 2
Opposition entre données et informations
Avant de commencer à interroger la base de données, il est nécessaire d’apporter une
précision : il existe une grande différence entre données et informations. Par définition, les
données sont les éléments stockés dans une base de données alors que les informations sont
les éléments recherchés dans une base de données. Rappelez-vous qu’une base de données est
conçue pour fournir des informations pertinentes. Toutefois, ces informations ne peuvent être
fournies que si des données appropriées ont été entrées dans la base et si la base elle-même a
été structurée correctement. Examinons cela de plus près.
Les valeurs stockées dans une base de données sont des données. Les données sont statiques
dans le sens où elles demeurent dans le même état tant qu’elles ne sont pas modifiées manuellement ou par un processus automatisé. La Figure 4.2 montre un exemple de données.
Catherine Martin 69005 Actif 89931
Figure 4-2
Exemple de données.
En apparence, ces données n’ont aucun sens. Il n’est pas facile, par exemple, de déterminer ce
que représente 89931. S’agit-il d’un code postal ? Est-ce un numéro d’article ? Même si vous
savez qu’il s’agit d’un numéro client, est-il associé à Catherine Martin ? Il n’y a aucun moyen
de le savoir tant que les données ne sont pas traitées. Les données ne deviennent des informations qu’une fois qu’elles ont été traitées et qu’elles sont devenues pertinentes et utiles. Les
informations sont dynamiques dans le sens où elles varient constamment par rapport aux
données stockées dans la base de données et qu’elles peuvent être traitées et présentées de
diverses manières. On peut montrer des informations résultant d’une commande SELECT, les
afficher dans un formulaire sur un écran d’ordinateur ou bien les imprimer sur du papier dans
un rapport. Aspect essentiel à retenir : vous devez traiter les données pour les transformer en
informations.
La Figure 4.3 montre les données de l’exemple précédent transformées en informations dans
une fiche client. Cet exemple illustre la manière dont les données peuvent être manipulées
pour sembler pertinentes à la personne qui les visualise.
Figure 4-3
Exemple
de données
transformées
en informations.
Quand on travaille avec une commande SELECT, on utilise ses clauses pour manipuler des
données mais la commande proprement dite retourne des informations.
Création d’une requête simple
CHAPITRE 4
Toutefois, il subsiste un problème. Quand on exécute une commande SELECT, elle retourne
une ou plusieurs lignes d’informations ; le nombre exact de lignes dépend de la procédure
utilisée pour construire la commande. On appelle ces lignes ensemble de résultats et c’est la
terminologie que nous emploierons tout au long de cet ouvrage. Ce terme est bien choisi parce
qu’on travaille toujours avec des ensembles de données quand on utilise une base de données
relationnelle (rappelez-vous que le modèle relationnel repose sur la théorie des ensembles).
On peut facilement visualiser les informations d’un ensemble de résultats et, dans de
nombreux cas, en modifier les données.
Mais revenons à notre propos et commençons à utiliser la commande SELECT.
Traduction de la requête en SQL
Quand on interroge la base de données, c’est en général sous la forme d’une question ou bien
d’une commande qui implique une question. Par exemple, on peut formuler les commandes
de la manière suivante :
« Dans quelles villes habitent nos clients ? »
« Montrez-moi la liste de nos employés avec leurs numéros de téléphone. »
« Quels types de cours proposons-nous ? »
« Donnez-moi le nom de nos employés avec leur date d’embauche. »
Une fois que vous savez ce que vous voulez demander, vous pouvez traduire votre requête en
une instruction plus formelle. Cette traduction revêt la forme suivante :
Sélectionner <élément> dans la <source>
Commencez par remplacer les mots et les phrases comme « Montrez-moi », « Listez »,
« Quels », « Qui » par le mot Sélectionner. Identifiez ensuite tous les noms de votre requête et
déterminez si un nom donné représente un élément que vous voulez voir ou si le nom est celui
d’une table dans laquelle un élément peut être stocké. S’il s’agit d’un élément, utilisez-le à la
place de <élément> dans l’instruction de traduction ; s’il s’agit d’un nom de table, utilisez-le
à la place de <source>. Si vous traduisez la première question de nos exemples précédents,
vous obtenez ce résultat :
Sélectionner ville dans la table clients
Une fois que vous avez défini votre instruction de traduction, vous devez la transformer en une
commande SELECT proprement dite en utilisant la syntaxe SQL illustrée à la Figure 4.4.
Toutefois, la première étape consiste à nettoyer l’instruction de traduction. Pour ce faire, vous
devez barrer tous les mots qui ne représentent pas le nom d’une colonne ou d’une table ou qui
ne sont pas des mots spécifiquement employés par la syntaxe SQL. Voici le résultat obtenu
après la phase de nettoyage :
Sélectionner ville dans la table clients
Il apparaîtra clairement au lecteur anglophone que la phase de nettoyage fonctionne beaucoup
mieux en anglais dans la mesure où elle permet de générer la commande SQL exacte. Les
différentes traductions des termes anglais (« sélectionner » par SELECT, « dans » par
FROM…) imposent au lecteur français une ultime transformation pour passer à la requête
67
68
Les bases de SQL
PARTIE 2
SQL réelle. Par conséquent, nous avons préféré introduire les commandes et mots-clés correspondants entre crochets dans la phase de nettoyage pour en faciliter la compréhension :
Sélectionner [SELECT] ville dans [FROM] la table clients
Supprimez alors les mots que vous avez barrés et remplacez les termes français
« sélectionner » et « dans » par les mots-clés SQL figurant entre crochets. Vous obtenez ainsi
la commande SQL complète :
SELECT Ville FROM Client
Cette technique comptant trois étapes s’applique à toute question posée à la base de données.
En fait, nous emploierons cette technique dans la majeure partie du livre et nous vous encourageons à l’utiliser au début de votre apprentissage de SQL. Quand vous serez plus au fait des
commandes SELECT, vous les construirez en une seule étape.
Figure 4-4
Exemple d’une commande SELECT simple.
Gardez à l'esprit que vous travaillerez généralement avec des colonnes et des tables au début
de votre apprentissage de SQL. Le diagramme de la syntaxe de la Figure 4.4 reflète cet état de
fait en utilisant nom de colonne dans la clause SELECT et nom de table dans la clause FROM.
Au chapitre suivant, vous apprendrez à utiliser d’autres termes dans ces clauses pour créer des
commandes SELECT plus complexes.
Vous avez probablement remarqué que la requête employée dans l’exemple précédent est relativement simple. En effet, la redéfinition sous forme de traduction ainsi que l’identification
des noms de colonnes présentes dans la commande ne posaient pas de problème. Mais que se
passe-t-il si la requête est plus complexe ou que les colonnes sont moins faciles à identifier ?
Il faut affiner votre requête et la rendre plus spécifique. Par exemple, vous pouvez affiner la
requête suivante :
« Montrez-moi les informations concernant nos clients. »
en la reformulant plus clairement :
« Listez le nom, la ville et le numéro de téléphone de chacun de nos clients. »
Si le problème n’est pas résolu, vous avez toujours deux autres solutions. Il faut commencer par
déterminer si la table spécifiée dans la clause FROM de la commande SELECT contient des
noms de colonnes qui peuvent vous aider à clarifier la requête et donc faciliter sa traduction.
Deuxième solution : examiner la requête de plus près et déterminer si un mot ou une phrase
implique des noms de colonne. L’emploi de ces deux méthodes dépend de la requête propre-
Création d’une requête simple
CHAPITRE 4
ment dite. Prenons un exemple pour mieux comprendre cet aspect. Pour illustrer la première
technique, supposons que l’on tente de traduire la requête suivante :
« J’ai besoin des noms et adresses de tous nos employés. »
Cette requête paraît simple. Mais en y regardant de plus près, vous constaterez un léger
problème : alors qu’on peut déterminer la table nécessaire (Employés) pour traduire la
commande, rien dans la requête n'indique précisément les colonnes à spécifier pour la clause
SELECT. Bien que les mots « noms » et « adresses » apparaissent dans la requête, ce sont des
termes trop généraux. On peut résoudre ce problème en examinant la table identifiée dans la
requête et en déterminant si elle contient des colonnes que l’on peut substituer à ces termes.
Dans ce cas, utilisez les noms des colonnes dans la phase de traduction. Vous pouvez choisir
d’utiliser les versions génériques des noms de colonnes dans la phase de traduction si cela vous
aide à visualiser plus clairement la commande ; cependant, vous devrez utiliser les noms réels
de colonnes dans la syntaxe SQL. Dans ce cas, cherchez les noms des colonnes de la table
Employés qui peuvent être utilisés au lieu des mots « noms » et « adresses ». Utilisez la table
Employés montrée à la Figure 4.5 et demandez-vous si vous pouvez utiliser ses colonnes.
Figure 4-5
Structure de la table Employés.
Vous utiliserez en fait cinq colonnes de cette table. EmpNom et EmpPrénom remplaceront
« noms » dans la requête alors que EmpAdresse, EmpVille, EmpCodePostal remplaceront
« adresses ». Appliquons à présent l'ensemble du processus de traduction à la requête. Nous
utilisons la forme générique des noms de colonnes (prénom, nom…) dans la phase de traduction et les noms réels de colonne (EmpPrénom, EmpNom…) dans la syntaxe SQL. Enfin, de
manière à rester plus proche de l’anglais FROM, nous emploierons désormais l’expression « à
partir de la table… » dans la phase de traduction, plutôt que « dans la table… ».
Énoncé
Traduction
Nettoyage
« J’ai besoin des noms et des adresses de tous nos employés. »
Sélectionner le prénom, le nom, l’adresse, la ville et le code postal à partir de
la table des employés
Sélectionner [SELECT] le prénom, le nom, l’adresse, la ville, et le code
postal à partir de [FROM] la table des employés
SQL
SELECT EmpPrénom, EmpNom, EmpAdresse, EmpVille, EmpCodePostal
FROM Employés
INFO
Cet exemple illustre clairement l'utilisation de plusieurs colonnes dans une clause SELECT. Nous reviendrons plus en détail sur cette technique dans cette section.
69
70
Les bases de SQL
PARTIE 2
L’exemple suivant illustre la seconde technique qui nécessite la recherche des colonnes impliquées dans la requête. Supposons que vous vouliez traduire la requête suivante :
« Quels types de cours proposons-nous actuellement ? »
À première vue, il peut sembler difficile de traduire cette requête. Il n’y a aucun nom de
colonne et il est impossible de créer une requête s’il n’y a même pas un élément à sélectionner.
Que faire à présent ? Il faut examiner plus précisément chaque mot de la requête et déterminer
s’il y en a un qui implique un nom de colonne dans la table Cours. Avant d’aller plus loin,
prenons un instant pour réétudier la requête. Pouvez-vous trouver un tel mot ?
Dans notre exemple, le mot « types » peut impliquer un nom de colonne dans la table Cours.
Pourquoi ? Parce qu'un type de cours peut aussi prendre le sens de catégorie de cours. S’il y a
une colonne catégorie dans la table Cours, vous devrez alors compléter la traduction et donc
la commande SELECT. Supposons qu’il y ait une colonne catégorie dans la table Cours ;
recommencez le processus de traduction en trois étapes :
Énoncé
Traduction
Nettoyage
« Quels types de cours proposons-nous actuellement ? »
Sélectionner la catégorie à partir de la table des cours
Sélectionner [SELECT] la catégorie à partir de [FROM] la table des cours
SQL
SELECT Catégorie
FROM Cours
Comme le montre cet exemple, cette technique implique l’utilisation de synonymes pour
remplacer certains mots ou expressions dans la requête. Si vous identifiez un mot ou une
expression qui peut impliquer un nom de colonne, essayez de le remplacer par un synonyme
qui peut être une colonne de la base de données. Mais si le premier synonyme qui vous vient
à l’esprit ne fonctionne pas, essayez-en un autre. Continuez ainsi jusqu’à ce que vous trouviez
un synonyme qui correspond à un nom de colonne.
INFO
Sauf spécification contraire, les noms des colonnes et des tables utilisés dans ces exemples sont extraits
des exemples de base de données que vous trouverez sur le CD.
Élargissement du champ de vision
La commande SELECT permet également d’extraire plusieurs colonnes et l’opération sera
aussi simple que pour une colonne unique. Listez les noms des colonnes que vous voulez
utiliser dans la clause SELECT et séparez chaque nom de la liste par une virgule. Dans le
diagramme présenté sur la Figure 4.6, l’option permettant d’utiliser plus d’une colonne est
indiquée par une ligne allant de droite à gauche sous nom de colonne. La virgule se trouvant
au milieu de la ligne indique que vous devez insérer une virgule avant l’autre nom de colonne
à utiliser dans la clause SELECT.
Cette option, qui permet d’utiliser plusieurs colonnes dans une commande SELECT, vous
donne la possibilité de répondre à des questions similaires à celles-ci :
Énoncé
Traduction
« Montrez-moi la liste actuelle de nos employés et leurs numéros de
téléphone. »
Sélectionner le nom, le prénom et le numéro de téléphone de tous nos
employés à partir de la table des employés
Création d’une requête simple
CHAPITRE 4
Figure 4-6
Utilisation de plusieurs colonnes
dans une clause SELECT.
Nettoyage
Sélectionner [SELECT] le nom, le prénom et le numéro de téléphone de
tous nos employés à partir de [FROM] la table des employés
SQL
SELECT EmpNom, EmpPrénom, EmpTéléphone
FROM Employés
Énoncé
« Quels sont les noms et les prix des produits que nous vendons et dans quelle
catégorie chaque produit est-il listé ? »
Sélectionner le nom, le prix et la catégorie de chaque produit à partir de la
table des produits
Sélectionner [SELECT] le nom, le prix et la catégorie de chaque produit à
partir de [FROM] la table des produits
Traduction
Nettoyage
SQL
SELECT NomProduit, PrixPublic, Catégorie
FROM Produits
L’ordre des colonnes dans la clause SELECT n’a pas d’importance et vous pouvez lister les
colonnes dans l’ordre que vous souhaitez. Cela vous donne la possibilité de voir les mêmes
informations sous des angles différents.
Supposons par exemple que vous travaillez avec la table présentée à la Figure 4.7, et qu’on
vous demande de poser la question suivante à la base de données :
« Montrez-moi la liste des sujets, la catégorie à laquelle ils appartiennent et le code que
nous utilisons dans notre catalogue. Mais j’aimerais tout d’abord voir le nom, suivi de la
catégorie et enfin le code. »
Figure 4-7
Structure de la table Sujets.
Vous pouvez toujours transformer cette requête pour que les colonnes apparaissent dans
l’ordre souhaité. Il suffit de lister les noms des colonnes dans l’ordre spécifié quand on définit
la commande. Voici l’aspect pris par le processus quand vous transformez cette requête en une
commande SELECT.
71
72
Les bases de SQL
PARTIE 2
Traduction
Sélectionner le nom du sujet, le numéro de la catégorie et le code du sujet à
partir de la table des sujets
Nettoyage
Sélectionner [SELECT] le nom du sujet, le numéro de la catégorie et le
code du sujet à partir de [FROM] la table des sujets
Il n’y a pas de limite quant au nombre de colonnes spécifiées dans la clause SELECT et vous
pouvez en fait lister toutes les colonnes de la table source. L’exemple suivant montre la
commande SELECT qui spécifie toutes les colonnes de la table Sujets de la Figure 4.7.
SQL
SELECT NumSujet, NumCatégorie, CodeSujet, NomSujet, DescriptionSujet
FROM Sujets
Quand vous spécifiez toutes les colonnes de la table source, vous devez saisir de nombreux
caractères si la table contient beaucoup de colonnes ! Heureusement, la norme SQL propose
un raccourci, l’astérisque, qui allège considérablement la commande. Le diagramme de la
Figure 4.8 montre que l’on peut utiliser l’astérisque au lieu de lister toutes les colonnes dans
la clause SELECT.
Figure 4-8
Utilisation d’un astérisque pour spécifier
toutes les colonnes de la table source.
Placez l’astérisque immédiatement après la clause SELECT quand vous voulez spécifier
toutes les colonnes de la table source dans la clause FROM. Par exemple, la commande précédente prend cet aspect quand on utilise le raccourci :
SQL
SELECT *
FROM Sujets
Avouez que le nombre de caractères à taper est nettement réduit ! Toutefois, un problème se
pose quand vous créez des commandes SELECT de cette manière : l’astérisque représente
toutes les colonnes qui existent actuellement dans la table source ; or, l’ajout ou la suppression de colonnes affecte ce que vous voyez dans l’ensemble de résultats de la commande
SELECT. (Étrangement, la norme SQL stipule que l’ajout ou la suppression de colonnes ne
doit pas affecter l’ensemble des résultats.) Ce problème n’est important que si vous devez
voir systématiquement les mêmes colonnes dans l’ensemble de résultats. Votre base de
données ne vous avertira pas si des colonnes ont été supprimées lorsque vous utilisez l’astérisque dans la clause SELECT mais elle émettra un message d’erreur quand elle ne trouvera
pas une colonne que vous avez spécifiée explicitement. Bien que cela ne constitue pas un réel
problème pour notre propos, cette difficulté prend des proportions beaucoup plus importantes dans le cadre de la programmation en SQL. Nous vous conseillons de réserver l’astérisque aux requêtes devant être créées rapidement pour voir toutes les informations d’une
Création d’une requête simple
CHAPITRE 4
table donnée. Dans le cas contraire, spécifiez toutes les colonnes dont vous avez besoin dans
la requête. Finalement, la requête retournera exactement les informations nécessaires et en
outre, elle sera auto-documentée.
Suppression des doublons
Quand on travaille avec des commandes SELECT, on rencontre inévitablement des ensembles
de résultats qui comportent des doublons. Il n’y a aucune raison de s’inquiéter dans ce cas.
Utilisez le mot-clé DISTINCT dans votre commande SELECT et l’ensemble de résultats ne
comportera aucune ligne en double. La Figure 4.9 montre la syntaxe du mot-clé DISTINCT.
Figure 4-9
Syntaxe du mot-clé DISTINCT.
Comme le montre le diagramme, DISTINCT est un mot-clé optionnel qui précède la liste des
colonnes spécifiées dans la clause SELECT. Le mot-clé DISTINCT demande à votre base de
données d’évaluer la valeur de toutes les colonnes comme des unités simples dans une comparaison ligne à ligne et d’éliminer toutes les redondances qu’elle rencontre. Les lignes uniques
restantes sont ensuite retournées dans l’ensemble de résultats. L’exemple suivant montre la
différence engendrée par l’emploi du mot-clé DISTINCT.
Imaginons la question suivante posée à notre base de données:
Énoncé
« Quelles sont les villes représentées par les membres de notre ligue de
bowling ? »
La question paraît assez simple et nous allons la traduire :
Traduction
Sélectionner la ville à partir de la table des joueurs de bowling
Nettoyage
Sélectionner [SELECT] la ville à partir de [FROM] la table des joueurs de
bowling
SQL
SELECT Ville
FROM Joueurs
Le problème est que l’ensemble de résultats de cette commande SELECT montre toutes les
occurrences de chaque nom de ville trouvé dans la table Joueurs. Par exemple, s’il y a
20 joueurs de Paris, 7 joueurs de Marseille et 14 joueurs de Lyon, l’ensemble de résultats
affiche 20 occurrences de Paris, 7 occurrences de Marseille et 14 occurrences de Lyon. Il est
évident que cette redondance ne nous intéresse pas ; seule une occurrence de chaque nom de
73
74
Les bases de SQL
PARTIE 2
ville trouvé dans le table Joueurs est nécessaire. On résout ce problème en utilisant le mot-clé
DISTINCT dans la commande SELECT pour éliminer les informations redondantes.
SQL
SELECT DISTINCT Ville
FROM Joueurs
L’ensemble de résultats de cette commande SELECT affiche exactement ce que vous
souhaitez : une seule occurrence de chaque ville distincte (ou unique) trouvée dans la table
Joueurs.
On peut également utiliser le mot-clé DISTINCT sur plusieurs colonnes. Modifions l’exemple
précédent en demandant à la fois la ville et le département dans la table Joueurs. Notre
nouvelle commande SELECT se présente sous cette forme :
SQL
SELECT DISTINCT Département, Ville
FROM Joueurs
Cette commande SELECT retourne un ensemble de résultats qui contient des enregistrements
uniques ; si des villes de départements différents portent le même nom, elles ne sont pas considérées comme des doublons et sont toutes affichées.
Le mot-clé DISTINCT est un outil très puissant et son utilisation est limitée au cas où vous
voulez réellement voir des lignes uniques dans votre ensemble de résultats.
Tri des informations
Au début de ce chapitre, nous avons dit que l’opération SELECT pouvait être divisée en trois
opérations : la commande SELECT, l’expression SELECT et la requête SELECT. Nous avons
également dit qu’il était possible de combiner ces opérations de différentes manières afin de
répondre à des requêtes complexes. Toutefois, il faut combiner ces opérations pour trier les
lignes d’un ensemble de résultats.
Par définition, les lignes d’un ensemble de résultats qui sont retournées par une commande
SELECT ne sont pas triées ; leur ordre d’apparition dépend de leur position physique dans la
table. Le seul moyen de trier l’ensemble de résultats est d’incorporer la commande SELECT
au sein d’une requête SELECT, comme le montre la Figure 4.10. On définit une requête
SELECT comme une commande SELECT accompagnée d’une clause ORDER BY. C’est la
clause ORDER BY de la requête SELECT qui permet de spécifier l’ordre des lignes de
l’ensemble de résultats final. Comme vous l’apprendrez par la suite, on peut incorporer une
commande SELECT au sein d’une autre commande SELECT ou d’une expression SELECT
pour répondre à des questions très complexes. En revanche, la requête SELECT ne peut pas
être incorporée à n’importe quel niveau.
INFO
Dans ce livre, nous utilisons la terminologie de la norme SQL ANSI ou encore celle en usage dans les
bases de données les plus courantes. Cependant, la norme SQL ANSI ne définit la clause ORDER BY
que comme élément d’un curseur, à savoir un objet créé à l’intérieur d’un programme d’application. Une
étude approfondie des curseurs dépasse le cadre de cet ouvrage. Dans la mesure où de nombreuses
mises en œuvre de SQL autorisent l’inclusion d’une clause ORDER BY à la fin d’une commande
SELECT, nous avons inventé le terme requête SELECT pour décrire ce type de commande.
Création d’une requête simple
CHAPITRE 4
Figure 4-10
Syntaxe de la requête SELECT.
La clause ORDER BY permet de trier l’ensemble de résultats sur une ou plusieurs colonnes
en précisant pour chaque colonne si l’ordre est croissant ou décroissant. Les seules colonnes
pouvant être utilisées dans la clause ORDER BY sont celles qui sont actuellement présentées
dans la clause SELECT. (Bien que cette exigence soit spécifiée dans la norme SQL, certains
logiciels l’ignorent totalement. Dans les exemples de ce livre, nous suivons la norme SQL).
Quand vous utilisez plusieurs colonnes dans une clause ORDER BY, séparez chaque colonne
par une virgule. La requête SELECT retourne un ensemble de résultats définitif une fois que
le tri est terminé.
INFO
La clause ORDER BY n’affecte pas l’ordre physique des lignes de la table. Si vous avez besoin de modifier l’ordre physique des lignes, reportez-vous à la documentation de vos logiciels.
Ordre de tri
Avant d’étudier des exemples de requêtes SELECT, il faut examiner le concept de séquence
de classement (ou ordre de tri).
La manière dont la clause ORDER BY trie les informations dépend de l’ordre de tri utilisé par
votre logiciel de base de données. La séquence de classement détermine l’ordre de préséance
de chaque caractère listé dans le jeu de caractères de la langue utilisé par votre système
d’exploitation. Par exemple, elle décide si les lettres minuscules sont triées avant les lettres
majuscules ou bien si la différence entre les minuscules et les majuscules est appliquée.
Vérifiez la documentation de votre base de données et consultez éventuellement votre administrateur de base de données pour déterminer l’ordre de tri par défaut.
Ordonner
La clause ORDER BY permet de présenter de manière plus pertinente les informations
extraites de la base de données. Ce principe s’applique aussi bien aux requêtes simples qu’aux
requêtes complexes. Vous pouvez à présent reformuler les requêtes pour qu’elles soient triées.
Par exemple, une question telle que :
« Quels sont les types de cours que nous proposons actuellement ? »
peut être reformulée ainsi :
« Listez les types de cours que nous proposons et montrez-les dans l’ordre alphabétique. »
75
76
Les bases de SQL
PARTIE 2
Avant d’étudier les requêtes SELECT, il faut rectifier la manière dont nous définissons une
commande de traduction. Cela implique l’ajout d’une nouvelle section à la fin de la
commande de traduction pour prendre en compte les nouvelles exigences de tri spécifiées
dans la requête. Utilisez cette nouvelle forme pour définir la commande de traduction.
Sélectionner <élément> dans la <source> et triez par <colonne(s)>
Maintenant que votre requête va inclure des expressions telles que « Triez les résultats par
ville », « Montrez-les dans l’ordre chronologique » ou bien « Listez-les par nom et prénom »,
il nous faut étudier soigneusement la requête pour déterminer les colonnes devant être utilisées à des fins de tri. Vous y parviendrez facilement car la plupart des gens utilisent ce genre
d’expressions et les colonnes nécessaires pour le tri sont habituellement évidentes. Une fois
que vous avez identifié la ou les colonnes appropriées, utilisez-les pour remplacer
<colonne(s)> dans la commande de traduction. Prenons un exemple pour mieux comprendre
le fonctionnement :
Énoncé
« Listez les types de cours que nous proposons et montrez-les dans l’ordre
alphabétique. »
Traduction
Sélectionner la catégorie à partir de la table des cours et trier par catégorie
Nettoyage
Sélectionner [SELECT] la catégorie à partir de [FROM] la table des cours
et trier par [ORDER BY] catégorie
SQL
SELECT Catégorie
FROM Cours
ORDER BY Catégorie
Dans cet exemple, on suppose que Catégorie sera utilisé pour le tri car c’est la seule colonne
indiquée dans la requête. On peut également supposer que le tri doit être réalisé dans l’ordre
croissant car rien n’indique le contraire dans la requête. Cette supposition est tout à fait
fondée. Selon la norme SQL, le tri croissant est utilisé par défaut si aucun ordre de tri n’est
spécifié. Toutefois, si vos voulez être parfaitement explicite, il suffit d’insérer ASC après Catégorie dans la clause ORDER BY.
Dans la requête suivante, la colonne de tri est définie plus clairement :
Énoncé
« Montrez-moi une liste des fournisseurs selon l’ordre de leur code postal »
Traduction
Sélectionner le nom du fournisseur et son code postal à partir de la table des
fournisseurs et trier par code postal
Nettoyage
Sélectionner [SELECT] le nom du fournisseur et son code postal à partir de
[FROM] la table des fournisseurs et trier par [ORDER BY] code postal
SQL
SELECT NomFournisseur, CodePostalFournisseur
FROM Fournissseurs
ORDER BY CodePostalFournisseur
En règle générale, les personnes concernées vous diront si elles veulent voir les informations
dans l’ordre décroissant. Dans ce cas, il faut afficher l’ensemble de résultats dans l’ordre
inverse : insérez le mot-clé DESC après la colonne appropriée dans la clause ORDER BY. Par
exemple, voici comment modifier la commande SELECT de l’exemple précédent pour voir
les informations triées par code postal dans l’ordre décroissant :
Création d’une requête simple
CHAPITRE 4
SQL
SELECT NomFournisseur, CodePostalFournisseur
FROM Fournisseurs
ORDER BY CodePostalFournisseur DESC
L’exemple suivant illustre une requête plus complexe qui nécessite un tri sur plusieurs
colonnes. La seule différence entre cet exemple et les deux précédents est qu’il utilise deux
colonnes dans la clause ORDER BY. Notez que les colonnes sont séparées par des virgules,
ce qui est conforme à la syntaxe exposée dans le diagramme de la Figure 4.10.
Énoncé
« Affichez les noms de nos employés avec leur numéro de téléphone et leur
matricule et listez les par ordre alphabétique de nom et de prénom »
Traduction
Sélectionner le nom, le prénom, le téléphone et le numéro d’employé à partir
de la table des employés et trier par le nom et le prénom
Nettoyage
Sélectionner [SELECT] le nom, le prénom, le téléphone et le numéro
d’employé à partir de [FROM] la table des employés et trier par [ORDER
BY] le nom et le prénom
SQL
SELECT Nom, Prénom, Téléphone, NumEmployé
FROM Employés
ORDER BY Nom, Prénom
Une des opérations les plus intéressantes pouvant être réalisée avec les colonnes dans une
clause ORDER BY est la spécification d’un ordre de tri différent pour chaque colonne. Dans
l’exemple précédent, vous pouvez spécifier un ordre décroissant pour le nom et un ordre croissant pour le prénom. La commande SELECT se présente alors sous cette forme :
SQL
SELECT Nom, Prénom, Téléphone, NumEmployé
FROM Employés
ORDER BY Nom DESC, Prénom ASC
Bien qu’il ne soit pas nécessaire d’utiliser explicitement le mot-clé ASC, la commande est
plus lisible en sa présence.
L’exemple précédent permet de se poser une question intéressante : l’ordre des colonnes dans
la clause ORDER BY a-t-il de l’importance ? La réponse est Oui ! L’ordre est important car
votre base de données évalue les colonnes de la clause ORDER BY de la gauche vers la droite.
Sauvegarde
Vous devez sauvegarder vos commandes SELECT et tous les bons logiciels de base de
données en donnent la possibilité. La sauvegarde de vos commandes permet de ne pas les
recréer chaque fois que vous posez la même question à la base de données. Quand vous sauvegardez une commande SELECT, donnez-lui un nom significatif qui vous aidera à vous
souvenir du type d’information délivré par la commande. Si votre logiciel de base de données
le permet, écrivez un bref commentaire décrivant l’objet de la requête. Cette description vous
rafraîchira la mémoire quand vous serez de nouveau confronté à une commande SELECT
écrite plusieurs mois auparavant.
77
78
Les bases de SQL
PARTIE 2
Une commande SELECT sauvegardée est parfois appelée requête dans certains logiciels et
vue dans d’autres. Quelle que soit la dénomination, chaque logiciel de base de données permet
d’exécuter la commande sauvegardée et de travailler avec son ensemble de résultats.
INFO
Dans cet ouvrage, nous employons le terme requête pour représenter une commande SELECT sauvegardée.
Deux méthodes peuvent être utilisées pour exécuter une requête. La première est interactive
et implique une barre d’outils ou une grille de saisie. La seconde utilise un éditeur de code.
Vous utiliserez essentiellement la première méthode ; l’autre méthode ne sera envisagée que
quand vous commencerez à étudier le langage de programmation de votre base de données.
Nous nous limiterons ici à vous apprendre comment créer et utiliser des commandes SQL.
Exemples de commandes
Après avoir étudié les caractéristiques de base de la commande SELECT, nous allons
examiner quelques exemples en utilisant les bases de données proposées sur le CD-Rom.
Après chaque commande, nous avons inclus un exemple de l’ensemble de résultats retourné
par la commande. Le nom qui apparaît immédiatement au-dessus de l’ensemble de résultats a
deux objectifs : il est utilisé pour identifier l’ensemble de résultats lui-même, et c’est le nom
assigné à la commande SQL de l’exemple.
Au cas où vous vous demanderiez pourquoi nous avons assigné un nom à chaque commande
SQL, c’est tout simplement parce que nous les avons sauvegardés ! En fait, nous avons sauvegardé toutes les commandes SQL qui apparaissent dans les exemples de ce livre. Chaque
commande est stockée dans la base de données appropriée; et vous pouvez recopier sur votre
disque dur les bases de données qui se trouvent sur le CD-Rom. Cela vous permet de travailler
de manière interactive avec ces commandes avant d’essayer de les écrire vous-même.
Base de données Ventes
Énoncé
Traduction
Nettoyage
« Montrez-moi les noms de tous nos fournisseurs »
Sélectionner le nom du fournisseur à partir de la table des fournisseurs
Sélectionner [SELECT] le nom du fournisseur à partir de [FROM] la table
des fournisseurs
SQL
SELECT NomFournisseur
FROM Fournisseurs
Énoncé
Traduction
Nettoyage
« Quels sont les noms et les prix de tous les produits que nous vendons ? »
Sélectionner le nom du produit, le prix public à partir de la table des produits
Sélectionner [SELECT] le nom du produit, le prix public à partir de
[FROM] la table des produits
SQL
SELECT NomProduit, PrixPublic
FROM Produits
Création d’une requête simple
CHAPITRE 4
Énoncé
« Dans quelles villes habitent nos clients ? »
Traduction
Sélectionner sans doublon la ville à partir de la table des clients
Nettoyage
Sélectionner [SELECT] sans doublon [DISTINCT] la ville à partir de
[FROM] la table des clients
SQL
SELECT DISTINCT VilleClient
FROM Clients
Nom des fournisseurs (10 lignes)
NomFournisseur
Shinoman
Taupe Bike
Le guidon dans les nuages
ProFormance
Pédale douce
Vélo 2000
Le paradis du VTT
Vélo en gros
VTT Star
Poulie d'or
Produits vendus (40 lignes)
NomProduit
PrixPublic
Villes des clients (17 lignes)
VilleClient
1 200,00 €
Auxerre
VTT Eagle FS-3
1 800,00 €
Bordeaux
Compteur Dog Ear
75,00 €
Chalon-sur-Saône
Pneus tout temps Victoria Pro
54,95 €
Dijon
Rétroviseur Dog Ear
7,45 €
Etretat
VTT Viscount
635,00 €
Lille
Compteur sans fil Viscount C-500
49,00 €
Lyon
Antivol Kryptonite Advanced 2000
50,00 €
Marseille
Antivol Nikoma Lok-Tight
33,00 €
Metz
Casque Viscount Microshell
36,00 €
Montpellier
VTT Trek 9000
Lignes supplémentaires
Paris
Périgueux
Saint-Etienne
Strasbourg
Valence
Villeurbanne
79
80
Les bases de SQL
PARTIE 2
Base de données AgenceSpectables
Énoncé
« Listez tous les groupes et la ville où ils sont basés en triant par ville et par
nom de scène dans l’ordre croissant »
Sélectionner la ville et le nom de scène à partir de la table des groupes et trier
par ordre alphabétique de ville et nom de scène
Sélectionner [SELECT] la ville et le nom de scène à partir de [FROM] la
table des groupes et trier par [ORDER BY] ordre alphabétique [ASC] de
ville et nom de scène
Traduction
Nettoyage
SQL
SELECT VilleGroupe, NomScène
FROM Groupes
ORDER BY VilleGroupe ASC, NomScène ASC
Énoncé
« Donnez-moi une des dates d’engagement dédoublonnée ; je ne souhaite pas
savoir combien il y a d’engagements par date »
Sélectionner sans doublon la date de début à partir de la table des engagements
Sélectionner [SELECT] sans doublon [DISTINCT] la date de début à partir
de [FROM] la table des engagements
Traduction
Nettoyage
SQL
SELECT DISTINCT DateDébut
FROM Engagements
Villes des groupes (13 lignes)
VilleGroupe
NomScène
Dates d’engagement (66 lignes)
DateDébut
Besançon
Albert Tillon
01-07-1999
Lille
Danses du ballet
10-07-1999
Lyon
Hélène De La Mole
11-07-1999
Lyon
Les chaussettes rouges
15-07-1999
Lyon
Quartet Devos
17-07-1999
Marseille
Les haricots noirs
18-07-1999
Marseille
Suzie Baker
24-07-1999
Marseille
Topaze
29-07-1999
Montpellier
Trio urbain
30-07-1999
Paris
Blues de vache
31-07-1999
Paris
Country Porter
Lignes supplémentaires
Paris
Julie Martin
Paris
Le lièvre du samedi soir
Création d’une requête simple
CHAPITRE 4
Base de données EmploiDuTemps
Énoncé
Traduction
Nettoyage
« Pouvons-nous visualiser toutes les informations sur le planning des cours ? »
Sélectionner toutes les colonnes à partir de la table du planning
Sélectionner [SELECT] toutes les colonnes [*] à partir de [FROM] la table
du planning
SQL
SELECT *
FROM Planning
Informations planning (76 lignes)
NumCours
UV
Salle
Coefficient
HeureDébut
1000
11
1231
5
10:00
1002
12
1619
4
15:30
1004
13
1627
4
08:00
1006
13
1627
4
09:00
1012
14
1627
4
13:00
1020
15
3404
4
13:00
1030
16
1231
5
11:00
1031
16
1231
5
14:00
1156
37
3443
5
08:00
1162
37
3443
5
09:00
1168
37
3445
5
11:00
1180
38
3446
5
11:30
Autres colonnes
Lignes supplémentaires
Énoncé
Traduction
Nettoyage
SQL
« Donnez-moi la liste des bâtiments de l’université et le nombre d’étages de
chaque bâtiment. Classez-moi la liste par bâtiment dans l’ordre croissant. »
Sélectionner le nom et le nombre d’étages à partir de la table des bâtiments,
trier par nom de bâtiment dans l’ordre croissant
Sélectionner [SELECT] le nom et le nombre d’étages à partir de [FROM]
la table des bâtiments, trier par [ORDER BY] nom de bâtiment dans l’ordre
croissant [ASC]
SELECT NomBâtiment, NombreEtages
FROM Bâtiments
ORDER BY NomBâtiment ASC
81
82
Les bases de SQL
PARTIE 2
Liste des bâtiments (6 lignes)
NomBâtiment
NombreEtages
Arts et sciences
3
Bâtiment central
3
Bibliothèque
2
Salle des sports
1
Sciences humaines
3
Technologie
2
Base de données Bowling
Énoncé
Traduction
Nettoyage
« Où ont lieu nos tournois ? »
Sélectionner sans doublon les lieux des tournois à partir de la table des tournois
Sélectionner [SELECT] sans doublon [DISTINCT] les lieux des tournois à
partir de [FROM] la table des tournois
SQL
SELECT DISTINCT LieuTournoi
FROM Tournois
Énoncé
« Donnez-moi la liste de toutes les dates de tournois et leur emplacement.
J’ai besoin des dates dans l’ordre décroissant et des lieux dans l’ordre alphabétique. »
Sélectionner la date du tournoi et l’emplacement à partir de la table des tournois et trier par date de tournoi dans l’ordre décroissant et par lieu de tournoi
dans l’ordre croissant
Sélectionner [SELECT] la date du tournoi et l’emplacement à partir de
[FROM] la table des tournois et trier par [ORDER BY] date de tournoi dans
l’ordre décroissant [DESC] et par lieu de tournoi dans l’ordre croissant
[ASC]
Traduction
Nettoyage
SQL
SELECT DateTournoi, LieuTournoi
FROM Tournois
ORDER BY DateTournoi DESC, LieuTournoi ASC
Création d’une requête simple
CHAPITRE 4
Lieux des tournois (11 lignes)
LieuTournoi
Dates des tournois (14 lignes)
DateTournoi
LieuTournoi
Au Paradis du Bowling
04-09-1999
Bowling Acapulco
Bowling Acapulco
28-08-1999
Bowling République
Bowling des Acacias
21-08-1999
Salle Omnisports
Bowling des Rois
14-08-1999
Bowling Impérial
Bowling du Marais
07-08-1999
Salle Lebowsky
Bowling Impérial
31-07-1999
Bowling République
Bowling République
24-07-1999
Les couloirs de la mort
Les couloirs de la mort
17-07-1999
Bowling Acapulco
Salle Lebowsky
10-07-1999
Bowling des Rois
Salle Omnisports
03-07-1999
Au Paradis du Bowling
Salle Polyvalente
26-06-1999
Salle Polyvalente
19-06-1999
Bowling des Acacias
12-06-1999
Salle Omnisports
05-06-1999
Bowling du Marais
Base de données Recettes
Énoncé
Traduction
Nettoyage
« Quels sont les types de recettes et quels sont les noms des recettes pour
chaque type ? Peut-on trier ces informations par type et par nom de
recette ? »
Sélectionner le numéro de type de recette et le nom de la recette à partir de la
table des recettes et trier par numéro de type de recette et par nom de recette
Sélectionner [SELECT] le numéro de type de recette et le nom de la recette
à partir de [FROM] la table des recettes et trier par [ORDER BY] numéro
de type de recette et par nom de recette
SQL
SELECTNumTypeRecette, NomRecette
FROM Recettes
ORDER BY NumTypeRecette, NomRecette
Énoncé
Traduction
« Montrez-moi une liste sans doublon des numéros des types de recettes. »
Sélectionner sans doublon les numéros des types de recettes à partir de la
table des recettes
Sélectionner [SELECT] sans doublon [DISTINCT] les numéros des types
de recettes à partir de [FROM] la table des recettes
Nettoyage
SQL
SELECT DISTINCT NumTypeRecette
FROM Recettes
83
84
Les bases de SQL
PARTIE 2
Recettes par type de recette (15 lignes)
NumTypeRecette
NomRecette
Type de recettes (6 lignes)
NumTypeRecette
1
Fettuccini Alfredo
1
1
Filets de saumon en papillotes
2
1
Pollo Picoso
3
1
Ragoût irlandais
4
1
Roast Beef
5
1
Tourtière
6
1
Vivaneau
2
Asperges
2
Haricots verts à l'ail
3
Yorkshire Pudding
4
Salade d'été
5
Machos Nachos
5
Salsa Buena
6
Coupe Colonel
6
Trifle
Problèmes à résoudre
Vous trouverez ci-dessous quelques problèmes pour vous entraîner à écrire des requêtes SQL.
La solution de ces problèmes est fournie sur le CD-Rom d’accompagnement aux formats
Access 2000 et Access 97. Après avoir dézippé le fichier requetessql.zip fourni dans le répertoire RequetesSQL, vous trouverez les solutions dans le répertoire \Solutions\Access2000
(respectivement \Solutions\Access97). Par exemple, la solution du premier des problèmes
portant sur la base de données Ventes est fournie dans le fichier Ventes.mdb (requête intitulée
Chapitre 4-1). Ne vous inquiétez pas si votre propre solution ne correspond pas exactement à
celle que nous proposons – il y a souvent plusieurs syntaxes possibles pour une même requête
SQL. L’essentiel est que l’ensemble de résultat soit le même.
Base de données Ventes
1. « Montrez-moi toutes les informations sur nos employés. »
2. « Montrez-moi une liste des villes par ordre alphabétique où sont situés nos fournisseurs et
incluez le nom des fournisseurs avec lesquels nous travaillons dans chaque ville. »
Base de données AgenceSpectacles
1. « Donnez-moi le nom, le prénom et le numéro de téléphone de nos agents, et listez-les dans
l’ordre nom/prénom. »
2. « Donnez-moi des informations sur tous nos engagements. »
3. « Listez tous les engagements et leur date de début associée. Triez les lignes par date en
ordre décroissant et par engagement dans l’ordre croissant. »
Création d’une requête simple
CHAPITRE 4
Base de données EmploiDuTemps
1. « Montrez-moi une liste complète de toutes les UV que nous offrons. »
2. « Quels sont les grades des enseignants ? »
3. « Listez le nom, le prénom et le numéro de téléphone de tout notre personnel, et triez-les
par nom et prénom. »
Base de données Bowling
1. « Listez toutes équipes en ordre alphabétique. »
2. « Montrez-moi tous les scores de chacun de nos membres. »
3. « Montrez-moi une liste des joueurs avec leur moyenne et leur handicap, et triez-la par
ordre alphabétique. »
Base de données Recettes
1. « Montrez-moi une liste de tous les ingrédients dont nous disposons. »
2. « Montrez-moi toutes les informations sur nos recettes et triez cette liste par nom de recette
dans l’ordre alphabétique. »
85
11
Sous-requêtes
« Il est impossible de résoudre des problèmes
en utilisant le mécanisme de pensée qui nous a permis de les créer. »
Albert Einstein
Dans ce chapitre :
• Qu'est-ce qu'une sous-requête ?
• Sous-requêtes et expressions
• Sous-requêtes et filtrage
• Utilisation des sous-requêtes
• Exemples de commandes.
Dans les trois chapitres précédents, nous vous avons présenté plusieurs méthodes pour traiter
des données issues de plusieurs tables. Toutes ces techniques visent à mettre en relation des
sous-ensembles d'informations (on utilisera une ou plusieurs colonnes et une ou plusieurs
lignes d'une table entière, une requête intégrée à l’aide d'une clause FROM ou de l'opérateur
UNION). Dans ce chapitre, nous allons vous montrer comment récupérer une colonne unique
à partir d'une table ou d'une requête et comment l'utiliser comme expression de valeur dans
une clause SELECT ou WHERE.
Vous devrez retenir deux choses de ce chapitre :
1. En SQL, un problème peut toujours être résolu de plusieurs manières. Nous vous montrerons donc comment résoudre des problèmes déjà exposés dans les chapitres précédents en
utilisant des méthodes différentes de celles choisies auparavant.
2. Dans une clause FROM, il est possible de créer des filtres complexes ne reposant pas sur les
tables. Ce concept est fondamental étant donné que l'utilisation d'une sous-requête dans
une clause WHERE est le seul moyen d'obtenir un nombre de lignes exact lorsqu'il s'agit de
286
Les tables multiples
PARTIE 3
récupérer les lignes d'une table à partir du contenu filtré d'autres tables liées. Tout ceci vous
sera expliqué en détail par la suite dans ce chapitre.
INFO
Ce chapitre porte sur des concepts complexes et sa bonne compréhension implique que vous ayez lu et
compris les Chapitres 7, 8 et 9, respectivement intitulés « Penser en termes d'ensembles », « Jointures
internes » et « Jointures externes ».
Qu'est-ce qu'une sous-requête ?
En des termes simples, une sous-requête est une expression SELECT intégrée à l'une des
clauses d'une commande SELECT afin de formuler une requête finale. Dans ce chapitre, nous
définirons une sous-requête de manière plus formelle et vous montrerons comment l'utiliser
ailleurs que dans une clause FROM.
La norme SQL définit trois sortes de sous-requêtes :
1. SOUS-REQUÊTE DE LIGNE – il s'agit d'une expression SELECT intégrée qui retourne
plus d'une colonne, mais pas plus d'une ligne.
2. SOUS-REQUÊTE DE TABLE – cette expression SELECT intégrée retourne une ou
plusieurs colonnes et zéro ou plusieurs lignes.
3. SOUS-REQUÊTE SCALAIRE – il s'agit d'une expression SELECT qui ne retourne
qu'une colonne et pas plus d'une ligne.
Sous-requêtes de lignes
Au Chapitre 6, vous avez appris à filtrer les données retournées par une requête, c'est-à-dire à
intégrer un prédicat de comparaison à une clause WHERE qui compare la valeur d'une colonne
à un littéral, une expression ou une autre colonne. Vous vous souvenez sans doute qu'une
requête simple utilisant un prédicat de comparaison unique se présenterait sous cette forme :
SQL
SELECT Clients.NomClient
FROM Clients
WHERE Clients.CA > 500
La norme SQL définit un constructeur de valeur de ligne à inclure dans les prédicats de conditions de recherche des clauses WHERE, HAVING ou ON. Malheureusement, les produits
disponibles à la vente et prenant en charge cette syntaxe sont peu nombreux. Voici un exemple
de clause WHERE comprenant un tel constructeur :
SQL
SELECT Clients.NomClient
FROM Clients
WHERE
(Clients.CA, Clients.CodePostalClient)
> (500, '75000')
Cette clause WHERE retourne les lignes dans lesquelles la combinaison ClientsCA et CodePostalClient est supérieure à la combinaison 500 et 75000. Elle équivaut à la requête suivante :
Sous-requêtes
CHAPITRE 11
SQL
287
SELECT Clients.NomClient
FROM Clients
WHERE (Clients.CA > 500)
OR ((Clients.CA = 500)
AND (Clients.CodePostalClient > '75000'))
Dans ce cas, on pourrait remplacer la deuxième partie de la comparaison par une commande
SELECT qui retournerait une ligne unique de deux colonnes (autrement dit par une sousrequête de ligne). La plupart des bases de données disponibles dans le commerce ne prennent
en charge ni les constructeurs de valeurs de lignes, ni les sous-requêtes de lignes. Nous n'en
dirons donc pas plus à ce sujet dans ce chapitre.
Sous-requêtes de tables
Même si nous ne l'avons pas dit explicitement, nous vous avons déjà montré, dans les trois
chapitres précédents, comment intégrer une expression SELECT permettant de retourner
plusieurs lignes et colonnes à une clause FROM. Nous avons en effet utilisé des sous-requêtes
de tables pour définir un résultat complexe qui est ensuite intégré à la clause FROM d'une autre
requête. Dans ce chapitre, nous allons vous montrer à utiliser une sous-requête de table en tant
que source de la liste des valeurs de comparaison d'un prédicat IN, dont nous vous avons déjà
parlé au Chapitre 6. Nous vous apprendrons également quelques mots-clés nouveaux des
prédicats de comparaison qui s'utilisent uniquement dans les sous-requêtes de tables.
Sous-requêtes scalaires
Dans ce chapitre, nous vous montrerons également comment utiliser une sous-requête scalaire
chaque fois qu'elle peut remplacer une expression de valeur. Ce type de sous-requête permet
de récupérer une seule colonne ou une seule expression calculée dans une table qui n'est pas
nécessairement incluse dans la clause FROM de la requête principale. La valeur unique
retournée par une sous-requête scalaire peut être utilisée dans la liste des colonnes demandées
au sein d'une clause SELECT ou en tant que valeur de comparaison dans une clause WHERE.
Sous-requêtes et expressions
Au Chapitre 5, nous avons vu en détail comment utiliser des expressions pour générer des
colonnes calculées devant être retournées par une requête. Nous avons toutefois omis de vous
signaler qu'un type particulier de commande SELECT peut être utilisé pour récupérer les
données d'une autre table, même lorsqu’elle ne figure pas dans votre clause FROM : il s'agit
des sous-requêtes.
Syntaxe
Revenons aux connaissances de base que vous avez déjà assimilées et examinons la commande
SELECT simple présentée sur la Figure 11.1.
Les choses sont loin d'être aussi simples qu'elles ne le paraissent. En fait, l'expression d'une
valeur peut se révéler complexe. La Figure 11.2 montre toutes les options pouvant constituer
l'expression de valeur.
288
Les tables multiples
PARTIE 3
Figure 11-1
Commande SELECT simple.
Figure 11-2
Diagramme d’une expression de valeur.
Au Chapitre 5, nous vous avons montré à créer des expressions de valeurs simples en utilisant
des valeurs littérales, des références de colonnes et des fonctions. Notez que l'expression
SELECT vient s'ajouter à cette liste. Par conséquent, il est possible d'intégrer une sousrequête scalaire à la liste des expressions qui suivent immédiatement le mot-clé SELECT.
Comme nous l'avons mentionné auparavant, les requêtes scalaires sont des expressions
SELECT qui retournent une seule colonne et pas plus d'une ligne. Tout ceci est logique dans
la mesure où cette sous-requête peut remplacer les noms de colonnes uniques ou les expressions retournant une colonne unique.
Vous vous demandez peut-être quelle est l'utilité de cette possibilité. Lorsqu'elle est utilisée
ainsi, une sous-requête permet de recueillir une valeur unique provenant d'une autre table ou
requête ; cette valeur sera incluse dans les résultats retournés par votre requête. La table ou
requête source de cette valeur est nommée dans la clause FROM de la sous-requête et il n'est
Sous-requêtes
CHAPITRE 11
289
pas nécessaire de la référencer dans la clause FROM de la requête externe. En général, il faut
ajouter des critères à la clause WHERE de la sous-requête pour qu’elle ne retourne pas plus
d'une ligne. Ces critères peuvent même référencer une valeur retournée par la requête externe
afin d'obtenir des données relatives à la ligne considérée.
Examinons quelques exemples simples tirés de la base de données Ventes et utilisant les tables
Clients et Commandes. La Figure 11.3 montre la relation établie entre ces deux tables.
Figure 11-3
Tables Clients et Commandes.
Nous allons maintenant construire une requête permettant de lister les commandes passées à
une date précise et qui récupère le nom de client associé dans la table Clients en utilisant une
sous-requête.
INFO
Dans ce chapitre, nous utiliserons à nouveau la technique « Requête/Traduction/Nettoyage/SQL »
présentée au Chapitre 4. En outre, nous mettrons les sous-requêtes entre parenthèses dans l'étape
« Nettoyage » et présenterons les sous-requêtes en retrait dès que possible pour vous aider à comprendre leur utilisation.
Énoncé
Traduction
Nettoyage
SQL
« Montrez-moi toutes les commandes envoyées le 24 décembre 1999 et le nom
de client qui leur est associé. »
Sélectionner le numéro de commande, la date de commande, la date d'expédition et sélectionner également le nom de client associé à partir de la table
des clients, à partir de la table des commandes où la date d'envoi est le 24
décembre 1999
Sélectionner [SELECT] le numéro de commande, la date de commande, la
date d'expédition et (sélectionner [SELECT] également le nom de client
associé à partir de [FROM] la table des clients), à partir de [FROM] la table
des commandes où [WHERE] la date d'envoi est le = 24 décembre 1999
SELECT Commandes.NumCommande, Commandes.DateCommande,
Commandes.DateExpédition,
(SELECT Clients.NomClient
FROM Clients
WHERE Clients.NumClient =
Commandes.NumClient)
290
Les tables multiples
PARTIE 3
FROM Commandes
WHERE Commandes.DateExpédition = '24-12-1999'
Notez que nous avons dû limiter la valeur du numéro de client figurant dans la sous-requête à
celle du numéro de client dans chaque ligne récupérée dans la table Commandes, sans quoi la
requête retournerait toutes les lignes de la table Clients. Souvenez-vous qu'il doit s'agir d'une
sous-requête scalaire ; le résultat retourné ne devra donc pas contenir plus d’une ligne.
Ceux d'entre vous que le concept de jointure interne présenté au Chapitre 8 a interpellé se
demandent probablement pourquoi on souhaite résoudre ce problème en procédant ainsi au
lieu de faire une jointure entre Commandes et Clients dans la clause FROM de la requête
externe. En fait, il s'agit simplement de donner un exemple simple et ce problème pourrait
également être résolu à l'aide de la requête suivante, qui comprend une jointure interne.
SQL
SELECT Commandes.NumCommande, Commandes.DateCommande,
Commandes.DateExpédition, Clients.NomClient
FROM Clients INNER JOIN Commandes
ON Clients.NumClient = Commandes.NumCommande
WHERE Commandes.DateExpédition = '24-12-1999'
Introduction aux fonctions d'agrégation : les opérateurs COUNT et MAX
Maintenant que vous savez pourquoi on utilise une sous-requête pour générer une colonne de
résultats, nous allons voir dans quelle mesure cette fonctionnalité peut être utile. Commençons par en apprendre davantage sur deux fonctions d’agrégation (nous verrons l'intégralité de
ces fonctions dans le chapitre suivant).
La norme SQL définit plusieurs fonctions qui permettent de calculer des valeurs dans une
requête. Parmi ces fonctions, les fonctions d’agrégation permettent de calculer une valeur
unique pour un groupe de lignes d'un ensemble de résultats. Par exemple, une fonction d’agrégation peut être utilisée pour compter des lignes, trouver la valeur la plus élevée ou la plus
faible dans un ensemble de lignes ou encore faire la moyenne ou le total de valeurs ou expressions de l'ensemble de résultats.
La Figure 11.4 montre le diagramme des fonctions COUNT et MAX qui permettent de
générer une colonne de résultats dans une clause SELECT.
Figure 11-4
Utilisation des fonctions d’agrégation
COUNT et MAX.
COUNT peut être utilisé pour déterminer le nombre de lignes ou de valeurs autres que Null
dans un ensemble de résultats. Utilisez COUNT(*) pour savoir combien de lignes constituent
Sous-requêtes
CHAPITRE 11
291
l'ensemble. Si vous précisez une colonne particulière dans l'ensemble de résultats en utilisant
COUNT(nom_colonne), le système de base de données compte le nombre de lignes dont la
valeur n'est pas Null dans cette colonne. COUNT permet également de ne compter que les
valeurs uniques lorsqu'on l'associe au mot-clé DISTINCT.
De même, la valeur la plus élevée d'une colonne peut être identifiée à l'aide de MAX. Si l'expression d'une valeur est numérique, vous obtiendrez la valeur numérique la plus élevée de la
colonne ou de l'expression spécifiée. Si une expression de valeur retourne une donnée alphabétique, la valeur la plus élevée dépend de la séquence de classement du système de base de
données.
Nous allons maintenant intégrer ces fonctions à une sous-requête afin de résoudre deux
problèmes intéressants.
Énoncé
Traduction
Nettoyage
SQL
« Listez tous les noms de clients et le nombre de commandes qu'ils ont
passées. »
Sélectionner le prénom du client et le nom du client, sélectionner également
le nombre de commandes dans la table des commandes pour ce client dans la
table des clients
Sélectionner [SELECT] le prénom du client et le nom du client, (sélectionner [SELECT] également le nombre de commandes [COUNT(*)] à
partir de [FROM] la table des commandes pour ce où [WHERE] numéro
client = numéro client) à partir de [FROM] la table des clients
SELECT Clients.PrénomClient,
Clients.NomClient,
(SELECT COUNT(*)
FROM Commandes
WHERE Commandes.NumClient =
Clients.NumClient)
AS NombreDeCommandes
FROM Clients
Dans les trois chapitres suivants, vous en apprendrez plus sur les multiples utilisations des
fonctions d'agrégat. Cependant, si vous souhaitez tout simplement compter des lignes mises
en relation, les sous-requêtes représentent une solution optimale. Examinons maintenant un
autre problème qui peut être résolu à l'aide de la fonction d’agrégation MAX :
Énoncé
Traduction
Nettoyage
« Montrez-moi la liste des clients et leurs dates de commande les plus
récentes. »
Sélectionner le prénom du client, le nom du client, sélectionner également la
date de commande la plus élevée à partir de la table des commandes pour ce
client dans la table des clients
Sélectionner [SELECT] le prénom du client, le nom du client, (sélectionner
[SELECT MAX] également la (date de commande) la plus élevée à partir de
[FROM] la table des commandes où [WHERE] numéro de client = numéro
de client) pour ce client à partir de [FROM] la table des clients
292
Les tables multiples
PARTIE 3
SQL
SELECT Clients.PrénomClient,
Clients.NomClient,
(SELECT MAX(DateCommande)
FROM Commandes
WHERE Commandes.NumClient =
Clients.NumClient)
AS DernièreDateCommande
FROM Clients
Comme vous pouvez l'imaginer, cette utilisation de MAX fonctionne bien lorsqu'il s'agit de
trouver la valeur la plus élevée ou la « dernière » valeur d'une table. Nous vous montrerons les
autres utilisations possibles de ces fonctions par la suite dans ce chapitre, dans la section intitulée « Exemples de commandes ».
Sous-requêtes et filtrage
Au Chapitre 6, vous avez appris à filtrer les informations retournées en ajoutant une clause
WHERE à vos requêtes. Vous avez également vu comment utiliser les comparaisons simples et
complexes pour que seules les lignes désirées figurent dans l'ensemble de résultats. Nous allons
maintenant élargir vos connaissances et vous montrer comment utiliser une sous-requête en
tant qu'argument de comparaison afin d'améliorer le filtrage.
Syntaxe
Revenons à la commande SELECT précédente et examinons la syntaxe de construction d'une
requête comprenant un prédicat de comparaison simple intégré à une clause WHERE. La
Figure 11.5 représente un diagramme simplifié de cette requête.
Comme nous l'avons vu sur la Figure 11.2, une expression de valeur peut se présenter sous la
forme d'une sous-requête. L'exemple de la Figure 11.5 permet de comparer une expression de
valeur à une colonne unique. Cette figure montre que l'expression de valeur doit être une
valeur unique, autrement dit une sous-requête scalaire qui retourne exactement une colonne et
pas plus d'une ligne. Résolvons un problème simple nécessitant une comparaison à une valeur
retournée par une sous-requête. Dans cet exemple, nous allons rechercher tous les détails des
commandes des clients, mais nous ne souhaiterons voir que la dernière commande de chacun
de ces clients. La Figure 11.6 montre les tables nécessaires à la résolution de cette requête.
Énoncé
Traduction
Nettoyage
« Listez les clients et tous les détails de leur dernière commande. »
Sélectionner le prénom du client, le nom du client, le numéro de commande,
la date de commande, le numéro de produit, le nom du produit et la quantité
commandée à partir de la table des clients en faisant une jointure avec la table
des commandes sur le numéro de client, puis en faisant une jointure avec la
table du détail des commandes sur le numéro de commande, et enfin en
faisant une jointure avec la table des produits sur le numéro de produit où la
date de commande est égale à la date de commande maximale à partir de la
table des commandes pour ce client
Sélectionner [SELECT] le prénom du client, le nom du client, le numéro de
commande, la date de commande, le numéro de produit, le nom du produit
Sous-requêtes
CHAPITRE 11
Figure 11-5
Filtrage d’un résultat en utilisant
un simple prédicat de comparaison.
Figure 11-6
Tables nécessaires pour lister toutes
les informations d’une commande.
293
294
Les tables multiples
PARTIE 3
et la quantité commandée à partir de [FROM] la table des clients en faisant
une jointure [INNER JOIN] avec la table des commandes sur [ON] le
numéro de client, puis en faisant une jointure [INNER JOIN] avec la table
du détail des commandes sur [ON] le numéro de commande, et enfin en
faisant une jointure [INNER JOIN] avec la table des produits sur [ON] le
numéro de produit où [WHERE] la date de commande est égale à la =
(Sélectionner [SELECT MAX] (date de commande) maximale à partir de
[FROM] la table des commandes pour ce client où [WHERE]
commandes.numéro de commande = clients.numéro de client)
SQL
SELECT Clients.PrénomClient,
Clients.NomClient, Commandes.NumCommande,
Commandes.DateCommande,
DétailCommandes.NumProduit,
Produits.NomProduit,
DétailCommandes.QuantitéCommandée
FROM ((Clients
INNER JOIN Commandes
ON Clients.NumClient = Commandes.NumClient)
INNER JOIN DétailCommandes
ON Commandes.OrderID = DétailCommandes.OrderID)
INNER JOIN Produits
ON Produits.NumProduit =
DétailCommandes.NumProduit
WHERE Commandes.DateCommande =
(SELECT MAX(DateCommande)
FROM Commandes AS O2
WHERE O2.NumClient = Clients.NumClient)
Avez-vous remarqué que nous avons défini un alias pour la seconde référence à la table
Commandes (autrement dit pour la table Commandes de la sous-requête) ? Même si l'on omet
cet alias, la plupart des systèmes de bases de données sauront que vous parlez de la copie de
la table Commandes citée dans la sous-requête. En fait, dans la norme SQL, toute référence
non définie doit tout d'abord être traitée à partir de la requête la plus interne. Nous avons
malgré tout inclus l'alias afin d'indiquer clairement que la copie de la table Commandes référencée dans la clause WHERE de la sous-requête est celle qui se trouve dans la clause FROM
de la sous-requête. Si vous procédez ainsi, vos requêtes seront beaucoup plus claires et faciles
à comprendre.
Sous-requêtes
CHAPITRE 11
295
Mots-clés de prédicats spécifiques aux sous-requêtes
La norme SQL définit un certain nombre de mots-clés de prédicats utilisables dans une clause
WHERE en même temps qu'une sous-requête.
Appartenance définie : l'opérateur IN
Au Chapitre 6, vous avez appris à utiliser le mot-clé IN dans une clause WHERE afin de
comparer une colonne ou une expression à une liste de valeurs. Vous savez maintenant que
chaque expression d'une valeur de la liste IN peut être une sous-requête scalaire. Comme le
montre la Figure 11.7, il est également possible d'utiliser une sous-requête pour générer la
liste dans son intégralité.
Figure 11-7
Utilisation d’une sous-requête
avec un prédicat IN.
Dans ce cas, on peut utiliser une sous-requête de table qui retourne une colonne et toutes les
lignes nécessaires pour construire la liste. Utilisons maintenant la base de données Recettes à
titre d'exemple. La Figure 11.8 montre les tables qui nous intéressent.
Supposons que vous ayez invité à dîner un ami qui adore les poissons. Vous savez que vous
disposez d'un certain nombre de recettes contenant des poissons mais vous ne connaissez pas
tous les noms d'ingrédients de votre base de données. Vous savez que les poissons correspondent à une DescriptionTypeIngrédient, vous pouvez donc faire une jointure de toutes les tables
et filtrer les données obtenues sur DescriptionTypeIngrédient ; vous pouvez également utiliser
les sous-requêtes et le prédicat IN :
296
Les tables multiples
PARTIE 3
Figure 11-8
Tables pour lister les recettes et leurs ingrédients.
Énoncé
« Listez toutes mes recettes contenant des poissons. »
Traduction
Sélectionner le nom de recette à partir de la table des recettes où le numéro de
recette est dans la sélection de numéros de recettes de la table des ingrédients
de recettes où le numéro d'ingrédient est dans la sélection des numéros
d'ingrédients de la table des ingrédients en faisant une jointure avec la table
des types d'ingrédients sur le numéro de type d'ingrédient où la description du
type d'ingrédient est « Poisson»
Nettoyage
Sélectionner [SELECT] le nom de recette à partir de [FROM] la table des
recettes où [WHERE] le numéro de recette est dans [IN] la (sélection
[SELECT] de numéros de recettes à partir de [FROM] la table des ingrédients de recettes où [WHERE] le numéro d'ingrédient est dans [IN] la
(sélection [SELECT] des numéros d'ingrédients à partir de [FROM] la
table des ingrédients en faisant une jointure [INNER JOIN] avec la table des
types d'ingrédients sur [ON] le numéro de type d'ingrédient où [WHERE]
la description du type d'ingrédient est = 'Poisson'))
SQL
SELECT NomRecette
FROM Recettes
WHERE Recettes.NumRecette IN
(SELECT NumRecette
FROM IngrédientsRecette
WHERE IngrédientsRecette.NumIngrédient IN
(SELECT NumIngrédient
FROM Ingrédients
INNER JOIN TypesIngrédients
ON Ingrédients.NumTypeIngrédient =
TypesIngrédients.NumTypeIngrédient
Sous-requêtes
CHAPITRE 11
297
WHERE
TypesIngrédients.DescriptionTypeIngrédient
= 'Poisson'))
Avez-vous remarqué qu'il était possible d'inclure une sous-requête à l'intérieur d'une autre
sous-requête ? Nous aurions même pu aller plus loin en éliminant la jointure interne figurant
dans la deuxième sous-requête et en utilisant la syntaxe suivante :
SQL
(SELECT NumIngrédient
FROM Ingrédients
WHERE Ingrédients.NumTypeIngrédient IN
(SELECT NumTypeIngrédient
FROM TypesIngrédients
WHERE
TypesIngrédients.DescriptionTypeIngrédient
= 'Poisson'))
Utiliser cette requête reviendrait cependant à faire preuve d'un zèle excessif étant donné que
l'intégration de clauses IN dans d'autres clauses IN la rend plus difficile à lire. Mieux vaut
donc utiliser un seul prédicat IN et une jointure plus complexe dans la sous-requête. La
requête ci-dessus pourrait également être formulée ainsi :
SQL
SELECT NomRecette
FROM Recettes
WHERE Recettes.NumRecette IN
(SELECT NumRecette
FROM (IngrédientsRecette
INNER JOIN Ingrédients
ON IngrédientsRecette.NumIngrédient =
Ingrédients.NumIngrédient)
INNER JOIN TypesIngrédients
ON Ingrédients.NumTypeIngrédient =
TypesIngrédients.NumTypeIngrédient
WHERE
TypesIngrédients.DescriptionTypeIngrédient
= 'Poisson')
Vous vous demandez peut-être pourquoi nous nous donnons tant de mal. Ne pourrait-on pas
en effet se contenter de faire la jointure dans la requête externe ? La réponse est non étant
donné que la requête retournerait effectivement toutes les lignes de la table Recettes pour les
recettes de poissons, mais que l'on obtiendrait également des doublons. Pour savoir pourquoi
des doublons sont retournés, essayons maintenant de résoudre ce problème sans inclure de
sous-requête.
SQL
SELECT NomRecette
FROM ((Recettes
298
Les tables multiples
PARTIE 3
INNER JOIN IngrédientsRecette
ON Recettes.NumRecette =
IngrédientsRecette.NumRecette)
INNER JOIN Ingrédients
ON IngrédientsRecette.NumIngrédient =
Ingrédients.NumIngrédient)
INNER JOIN TypesIngrédients
ON Ingrédients.NumTypeIngrédient =
TypesIngrédients.NumTypeIngrédient
WHERE
TypesIngrédients.DescriptionTypeIngrédient
= 'Poisson')
Revenons à la Figure 11.8 : comme vous pouvez le constater, dans la table IngrédientsRecette,
plusieurs lignes peuvent correspondre à une ligne de la table Recettes. L'ensemble de résultats
défini par la clause FROM contiendra au moins autant de lignes que la table IngrédientsRecette et la valeur de la colonne NomRecette apparaîtra plusieurs fois. Même si l'on ajoute
le filtre pour limiter le résultat aux ingrédients du type Poisson, on obtiendra toujours plus
d'une ligne par recette pour toutes les recettes comprenant plusieurs sortes de poissons.
L'utilisation des sous-requêtes est également à préconiser lorsqu'il s'agit de lister des informations ne se limitant pas au titre de la recette. Supposons par exemple que vous souhaitiez lister
tous les ingrédients des recettes contenant des poissons. Si l'on utilise une jointure complexe
dans la requête externe et si l'on filtre le type d'ingrédients de la manière indiquée ci-dessus,
nous n'obtiendrons que les ingrédients qui sont des poissons et tous les autres ingrédients
seront exclus. Intéressons-nous maintenant à une requête légèrement plus complexe :
Énoncé
« Listez toutes mes recettes et tous les ingrédients des recettes contenant des
poissons. »
Traduction
Sélectionner le nom de recette et le nom d'ingrédient à partir de la table des
recettes en faisant une jointure avec la table IngrédientsRecette sur le numéro
de recette, puis en faisant une jointure avec la table des ingrédients sur le
numéro d'ingrédient où le numéro de recette est dans la sélection des numéros
de recettes dans la table IngrédientsRecette en faisant une jointure avec la
table des ingrédients sur le numéro d'ingrédient, puis en faisant une jointure
avec la table TypesIngrédients sur le numéro de type d'ingrédient où la
description du type d'ingrédient est « Poisson»
Nettoyage
Sélectionner [SELECT] le nom de recette et le nom d'ingrédient à partir de
[FROM] la table des recettes en faisant une jointure [INNER JOIN] avec la
table IngrédientsRecette sur [ON] le numéro de recette, puis en faisant une
jointure [INNER JOIN] avec la table des ingrédients sur [ON] le numéro
d'ingrédient où [WHERE] le numéro de recette est dans [IN] la (sélection
[SELECT] des numéros de recettes à partir de [FROM] la table IngrédientsRecette en faisant une jointure [INNER JOIN] avec la table des ingrédients sur [ON] le numéro d'ingrédient, puis en faisant une jointure [INNER
JOIN] avec la table TypesIngrédients sur [ON] le numéro de type d'ingrédient où [WHERE] la description du type d'ingrédient est = 'Poisson')
Sous-requêtes
CHAPITRE 11
SQL
299
SELECT Recettes.NomRecette,
Ingrédients.NomIngrédient
FROM (Recettes
INNER JOIN IngrédientsRecette
ON Recettes.NumRecette =
IngrédientsRecette.NumRecette)
INNER JOIN Ingrédients
ON Ingrédients.NumIngrédient =
IngrédientsRecette.NumIngrédient
WHERE Recettes.NumRecette IN
(SELECT NumRecette
FROM (IngrédientsRecette
INNER JOIN Ingrédients
ON IngrédientsRecette.NumIngrédient =
Ingrédients.NumIngrédient)
INNER JOIN TypesIngrédients
ON Ingrédients.NumTypeIngrédient =
TypesIngrédients.NumTypeIngrédient
WHERE
TypesIngrédients.DescriptionTypeIngrédient
= 'Poisson')
En l’occurrence, la jointure externe complexe qui se trouve dans la partie principale de la
requête retournera tous les ingrédients des recettes sélectionnées ; la sous-requête complexe
retournera la liste des numéros de recettes pour les recettes contenant des poissons. En fait,
nous avons tout simplement réalisé deux jointures complexes.
Prédicats quantifiés : ALL/SOME/ANY
Comme nous venons de le voir, le prédicat IN permet de comparer une colonne ou une expression à une liste afin de savoir si cette colonne ou expression se trouve dans cette liste. En
d'autres termes, la colonne ou expression est un membre de la liste. Pour savoir si cette
colonne ou expression est supérieure ou inférieure à un ou plusieurs des éléments de la liste,
on peut utiliser un prédicat quantifié. La Figure 11.9 montre la syntaxe à utiliser.
En l’occurrence, l'expression SELECT doit être une sous-requête de table qui retournera une
colonne unique et zéro ligne ou plus. Les valeurs des lignes forment une liste lorsque la sousrequête retourne plus d'une ligne. Comme vous pouvez le voir, ce prédicat combine un opérateur de comparaison à un mot-clé indiquant au système de base de données comment appliquer l'opérateur aux membres de la liste. Lorsqu'on utilise le mot-clé ALL, la comparaison
doit avoir la valeur Vrai pour toutes les valeurs retournées par la sous-requête ; lorsqu'on
utilise les mots-clés SOME ou ANY, il suffit que la comparaison ait la valeur Vrai pour une
des valeurs de la liste.
En examinant la situation de plus près, on constate que lorsqu'une sous-requête retourne
plusieurs lignes, = ALL aura toujours la valeur Faux, sauf si toutes les valeurs retournées par
la sous-requête sont identiques et si l'expression d'une valeur se trouvant à gauche de la
comparaison est égale à toutes ces valeurs. Si l'on suit la même logique, on pourrait penser que
300
Les tables multiples
PARTIE 3
Commande SELECT
Expression de valeur
SELECT
alias
,
FROM
WHERE
AS
Référence de table
,
Expression de valeur
=
<>
<
>
<=
>=
ALL
SOME
ANY
(Expression SELECT)
Figure 11-9
Utilisation d’un prédicat quantifié
dans une commande SELECT.
< > ANY aura toujours la valeur Faux si l'expression d'une valeur se trouvant à gauche de la
comparaison est égale à l'une des valeurs de la liste. En fait, la norme SQL considère SOME
et ANY de la même manière. Par conséquent, si l'on utilise < > SOME ou < > ANY, le prédicat
aura la valeur Vrai si l'expression de valeur figurant à gauche de la comparaison n'est égale à
aucune des valeurs de la liste. En outre, si la sous-requête ne retourne aucune ligne, tous les
prédicats de comparaison comportant le mot-clé ALL auront la valeur Vrai et tous les prédicats de comparaison comportant le mot-clé SOME ou ANY auront la valeur Faux.
INFO
Comme nous l'avons mentionné à propos des sous-requêtes de lignes, la norme SQL définit le concept
de constructeur de valeur de ligne. Si votre base de données prend en charge ces constructeurs, l'expression SELECT incluse dans un prédicat quantifié pourra retourner plus d'une colonne. Dans ce cas,
l'élément se trouvant à gauche de la comparaison devra être une liste d'expressions de valeurs séparées
par des virgules et mises entre parenthèses. En outre, l'expression SELECT doit retourner un nombre de
colonnes égal au nombre d'expressions de valeurs situées à gauche. On peut par exemple construire une
commande SQL se présentant sous cette forme :
SELECT *
FROM MaTable
WHERE (MaTable.Colonne1, MaTable.Colonne2) > ALL
(SELECT ColonneA, ColonneB FROM OtherTable)
Pour savoir si votre base de données prend en charge cette syntaxe, reportez-vous à la documentation
fournie avec le système.
Sous-requêtes
CHAPITRE 11
301
Nous allons maintenant mettre en œuvre les prédicats quantifiés en construisant deux requêtes.
Nous utiliserons tout d'abord la base de données Recettes. Reportez-vous à la Figure 11.8 pour
savoir quelles tables vont être utilisées.
Énoncé
Traduction
Nettoyage
SQL
« Montrez-moi les recettes contenant du bœuf ou de l'ail. »
Sélectionner le nom de recette à partir de la table des recettes où le numéro de
recette est dans la sélection des numéros de recettes à partir de la table des
ingrédients de recettes où le numéro d'ingrédient est égal à l'un de ceux de la
sélection de numéros d'ingrédients à partir de la table des ingrédients où le
nom d'ingrédient est « bœuf » ou « ail »
Sélectionner [SELECT] le nom de recette à partir de [FROM] la table des
recettes où [WHERE] le numéro de recette est dans [IN] la (sélection
[SELECT] des numéros de recettes à partir de [FROM] la table des ingrédients de recettes où [WHERE] le numéro d'ingrédient est égal à = l'un
[ANY] de ceux de la (sélection [SELECT] de numéros d'ingrédients à partir
de [FROM] la table des ingrédients où [WHERE] le nom d'ingrédient est
dans [IN] 'bœuf' ou ' ail'))
SELECT Recettes.NomRecette
FROM Recettes
WHERE Recettes.NumRecette IN
(SELECT IngrédientsRecette.NumRecette
FROM IngrédientsRecette
WHERE IngrédientsRecette.NumIngrédient = ANY
(SELECT Ingrédients.NumIngrédient
FROM Ingrédients
WHERE Ingrédients.NomIngrédient
IN ('Bœuf', 'Ail')))
Si vous pensez qu'il aurait été possible d'utiliser IN au lieu de = ANY, sachez que vous avez
raison. Nous aurions également pu faire une jointure entre IngrédientsRecette et Ingrédients
dans la première sous-requête afin de retourner la liste de numéros de recettes requise.
Comme nous l'avons souligné en début de chapitre, il existe presque toujours plusieurs
manières de résoudre un problème en SQL. Parfois, l'utilisation d'un prédicat quantifié vous
permettra d'améliorer la clarté de vos requêtes.
Résolvons maintenant un problème un peu plus complexe pour que vous puissiez réaliser
combien les prédicats quantifiés peuvent être utiles. Cet exemple utilise la base de données
Ventes et les tables concernées sont répertoriées dans la Figure 11.10.
Énoncé
Traduction
« Trouvez tous les accessoires dont le prix est supérieur à celui de n'importe
quel vêtement. »
Sélectionner le nom de produit et le prix public à partir de la table des
produits en faisant une jointure avec la table des catégories sur le numéro de
catégorie où la description de la catégorie est « accessoires » et le prix public
est supérieur à toute la sélection des prix publics à partir de la table des
produits en faisant une jointure avec la table des catégories sur le numéro de
catégorie où le nom de catégorie est « vêtements »
302
Les tables multiples
PARTIE 3
Figure 11-10
Catégories et les produits qui s’y rapportent.
Nettoyage
Sélectionner [SELECT] le nom de produit et le prix public à partir de
[FROM] la table des produits en faisant une jointure [INNER JOIN] avec la
table des catégories sur [ON] le numéro de catégorie où [WHERE] la
description de la catégorie est = 'Accessoires' et [AND] le prix public est
supérieur à > toute [ALL] la (sélection [SELECT] des prix publics à partir
de [FROM] la table des produits en faisant une jointure [INNER JOIN]
avec la table des catégories sur [ON] le numéro de catégorie où le nom de
catégorie est = 'Vêtements')
SQL
SELECT Produits.NomProduit,
Produits.PrixPublic
FROM Produits
INNER JOIN Catégories
ON Produits.NumCatégorie = Catégories.NumCatégorie
WHERE Catégories.DescriptionCatégorie =
'Accessoires'
AND Produits.PrixPublic > ALL
(SELECT Produits.PrixPublic
FROM Produits
INNER JOIN Catégories
ON Produits.NumCatégorie =
Catégories.NumCatégorie
WHERE Catégories.DescriptionCatégorie =
'Vêtements')
Que se passe-t-il ici ? La sous-requête récupère tous les prix des vêtements. La requête
externe liste alors tous les accessoires dont le prix est supérieur à tous les prix retournés par la
sous-requête.
Prédicat d'existence : EXISTS
Les prédicats d'appartenance définie (IN) et quantifiée (SOME/ANY/ALL) effectuent une
comparaison avec l'expression de valeur (il s’agit généralement d'une colonne de la source
définie dans la clause FROM de la requête externe). Le prédicat EXISTS permet de savoir si
une ligne correspondante existe (EXISTS) dans l'ensemble de résultats retourné par une sous-
Sous-requêtes
CHAPITRE 11
303
requête. Au Chapitre 8, nous vous avons montré comment résoudre les problèmes avec l’opérateur AND en utilisant des jointures internes complexes. Ces problèmes peuvent également être
résolus à l'aide de EXISTS. Nous allons réexaminer l'un des problèmes déjà vus au Chapitre 8.
Énoncé
« Trouvez tous les clients qui ont commandé un VTT et également un
casque. »
Traduction
Sélectionner le numéro de client, le prénom du client et le nom du client à
partir de la table des clients où il existe une ligne dans la table des commandes
en faisant une jointure avec la table du détail des commandes sur le numéro de
commande, puis en faisant une jointure avec la table des produits sur le
numéro de produit où le nom de produit contient « VTT » et le numéro de
client la table des commandes est égal au numéro de client dans la table des
clients ; il existe également une ligne à partir de la table des commandes en
jointure avec la table des détails des commandes sur le numéro de commande,
puis en jointure avec la table des produits sur le numéro de produit où le nom
de produit contient « casque » et le numéro de client dans la table des
commandes est égal au numéro de commande dans la table des clients
Nettoyage
Sélectionner [SELECT] le numéro de client, le prénom du client et le nom
du client à partir de [FROM] la table des clients où [WHERE] il existe
[EXISTS] une ligne (Sélectionner [SELECT] * à partir de [FROM] la table
des commandes en faisant une jointure [INNER JOIN] avec la table du
détail des commandes sur [ON] le numéro de commande, puis en faisant
une jointure [INNER JOIN] avec la table des produits sur [ON] le numéro
de produit où [WHERE] le nom de produit contient [LIKE] 'VTT' et
[AND] le numéro de client dans la table des commandes est égal au =
numéro de client dans la table des clients); il existe [EXISTS] également une
ligne (Sélectionner [SELECT] * à partir de [FROM] la table des
commandes en jointure [INNER JOIN] avec la table des détails des
commandes sur [ON] le numéro de commande, puis en jointure [INNER
JOIN] avec la table des produits sur [ON] le numéro de produit où
[WHERE] le nom de produit contient [LIKE] 'casque' et [AND] le
numéro de client dans la table des commandes est égal au = numéro de
commande dans la table des clients)
SQL
SELECT Clients.NumClient,
Clients.PrénomClient,
Clients.NomClient
FROM Clients
WHERE EXISTS
(SELECT *
FROM (Commandes
INNER JOIN DétailCommandes
ON Commandes.NumCommande =
DétailCommandes.NumCommande)
INNER JOIN Produits
ON Produits.NumProduit =
304
Les tables multiples
PARTIE 3
DétailCommandes.NumProduit
WHERE Produits.NomProduit Like '%VTT%'
AND Commandes.NumClient =
Clients.NumClient)
AND EXISTS
(SELECT *
FROM (Commandes
INNER JOIN DétailCommandes
ON Commandes.NumCommande =
DétailCommandes.NumCommande)
INNER JOIN Produits
ON Produits.NumProduit =
DétailCommandes.NumProduit
WHERE Produits.NomProduit Like '%Casque%'
AND Commandes.NumClient =
Clients.NumClient)
Notez qu'il est possible d'utiliser n'importe quelle colonne de n'importe quelle table dans la
clause FROM en tant que colonne à retourner par la clause SELECT de la sous-requête. Nous
avons utilisé « * » pour symboliser toutes les colonnes. En d'autres termes, cette requête pourrait être formulée ainsi : « Donnez-moi les clients pour lesquels il existe une ligne
« bicyclette » dans le détail des commandes et pour lesquels il existe également une ligne
« casque » dans le détail des commandes. » Étant donné que nous n'avons pas établi de correspondance sur le numéro de commande, peu importe que la bicyclette et le casque aient fait
l'objet de deux commandes différentes.
INFO
Étant donné l'intérêt de la requête ci-dessus, nous avons enregistré cette solution sous le nom « EXISTS
commandes de VTT et de casques » dans la base de données fournie à titre d'exemple. La solution utilisant une jointure interne est enregistrée sous le nom « Union commandes de VTT et de casques. »
Utilisation des sous-requêtes
En principe, vous en savez maintenant beaucoup sur l'utilisation des sous-requêtes pour
générer une colonne de résultats ou pour effectuer une comparaison complexe au sein d'une
clause WHERE. Pour vous donner une idée de toutes les utilisations que l'on peut faire des
sous-requêtes, nous allons vous soumettre quelques problèmes pouvant être résolus à l'aide de
celles-ci.
Expressions d'une colonne
Comme nous l'avons déjà mentionné dans ce chapitre, l'utilisation d'une sous-requête pour
récupérer une valeur unique dans une table est des plus efficaces lorsqu'on inclut une jointure.
Cependant, en ce qui concerne les fonctions d'agrégat, les sous-requêtes permettent également
d'obtenir le résultat d'un calcul de fonction. Nous nous intéresserons à cet aspect des fonctions
d’agrégation dans le prochain chapitre. En attendant, voici quelques problèmes pouvant être
résolus grâce aux sous-requêtes :
Sous-requêtes
CHAPITRE 11
305
«Listez nos fournisseurs et le nombre de produits qu'ils nous ont vendus. »
« Affichez les produits et leurs dernières dates de commande. »
« Montrez-moi les groupes et le nombre d'engagements de chaque groupe. »
« Affichez chaque client et la date de la dernière réservation effectuée. »
« Listez tous les membres du personnel et le nombre de cours dispensés par chacun. »
« Affichez toutes les matières et le nombre de cours pour chaque matière ayant lieu le lundi. »
« Montrez-moi tous les joueurs et le nombre de jeux joués par chacun. »
« Affichez les joueurs et le meilleur score qu'ils aient jamais eu. »
« Listez toutes les viandes et le nombre de recettes dans lesquelles chacune d'entre elles
apparaît. »
« Montrez-moi les types de recettes et le nombre de recettes pour chacun de ces types. »
Filtres
Votre connaissance des sous-requêtes va vous permettre de résoudre plus efficacement des
requêtes complexes. Nous avons vu dans ce chapitre plusieurs manières d'utiliser les sousrequêtes en tant que filtres dans des clauses WHERE. Au Chapitre 14, nous vous montrerons
comment utiliser des sous-requêtes pour filtrer des groupes d'informations dans les clauses
HAVING.
Voici certains problèmes qui peuvent être résolus à l'aide de sous-requêtes utilisées pour filtrer
des lignes dans des clauses WHERE. Notez que nous avons déjà résolu plusieurs de ces
problèmes dans les chapitres précédents en utilisant d'autres méthodes.
INFO
Pour vous guider, nous avons ajouté le(s) mot(s)-clé(s) pouvant être utilisés pour résoudre le problème
figurant entre parenthèses après chaque énoncé.
« Listez les clients ayant commandé une bicyclette. » (IN)
« Affichez les clients ayant commandé des vêtements ou des accessoires. » (= SOME)
« Trouvez tous les clients ayant commandé un casque. » (IN)
« Trouvez tous les clients ayant commandé une bicyclette, mais pas de casque. » (NOT
EXISTS)
« Quels sont les produits qui n'ont jamais été commandés ? » (NOT IN)
« Listez les clients qui ont réservé des groupes jouant de la country ou du country rock. »
(IN)
« Trouvez les groupes qui ont eu des engagements chez les clients Dupont ou Lenoir. »
(= SOME)
« Affichez les agents qui n'ont fait engager aucun groupe. » (NOT IN)
« Listez les groupes ayant eu des engagements chez les clients Dupont et Lenoir. »
(EXISTS)
« Affichez les étudiants inscrits à des cours se déroulant le mardi. » (IN)
« Montrez-moi les étudiants dont la moyenne est égale ou supérieure à 17 en arts plastiques
et en informatique. » (EXISTS)
306
Les tables multiples
PARTIE 3
« Affichez les étudiants qui n'ont jamais abandonné aucun cours. » (NOT IN)
« Listez les matières enseignées le mercredi. » (IN)
« Affichez tous les capitaines d'équipes dont la moyenne est supérieure à celle de tous les
autres membres de leurs équipes. » (> ALL)
« Montrez-moi les tournois qui n'ont pas encore été joués. » (NOT IN)
« Trouvez les joueurs dont le score est de 170 ou plus au Bowling République et au Bowling
Acapulco. » (EXISTS)
« Listez tous les joueurs dont la moyenne est inférieure à celle de tous les autres membres
de leurs équipes. » (< ALL)
« Montrez-moi les recettes qui contiennent du bœuf et de l'ail. » (EXISTS)
« Affichez tous les ingrédients des recettes contenant des carottes. » (IN)
« Listez les ingrédients utilisés dans les recettes où la mesure utilisée n'est pas la mesure
pas défaut. » (<> SOME)
« Listez les ingrédients qui n'ont encore été utilisés dans aucune recette. » (NOT IN)
Exemples de commandes
Vous savez maintenant construire des requêtes à l'aide de sous-requêtes et savez également
avec quel type de requêtes elles doivent être utilisées. Examinons maintenant quelques exemples qui impliquent l'utilisation d'une ou plusieurs sous-requêtes.
À la suite de la ligne de syntaxe SQL, nous avons également inclus des exemples d'ensembles
de résultats retournés par ces opérations. Le nom qui apparaît juste au dessus de chaque
ensemble de résultat est celui que nous avons donné à chaque requête sur le CD qui accompagne cet ouvrage.
INFO
Souvenez-vous que tous les noms de colonnes et de tables utilisés dans ces exemples sont tirés des
structures des exemples de bases de données présentées dans l'Annexe B. Étant donné que bon nombre
de ces exemples mettent en œuvre des jointures complexes, il se peut que votre système de base de
données choisisse une autre manière de résoudre la requête en question. C'est pourquoi les premières
lignes retournées ne correspondront peut-être pas complètement au résultat que vous obtiendrez, cependant le nombre total de lignes sera le même. Par souci de simplification, nous avons combiné les étapes
« Traduction » et « Nettoyage » dans tous les exemples suivants.
Sous-requêtes dans des expressions
Base de données Ventes
Énoncé
« Listez les fournisseurs et le nombre des produits qu’ils nous revendent. »
Traduction et
Nettoyage
Sélectionner [SELECT] le nom du fournisseur et aussi (Sélectionner
[SELECT] le total [COUNT] (*) des produits à partir de [FROM] la table
du catalogue des fournisseurs pour ce fournisseur où [WHERE] le numéro
de fournisseur = numéro du fournisseur du produit) à partir de [FROM] la
table des fournisseurs
Sous-requêtes
CHAPITRE 11
SQL
307
SELECT NomFournisseur,
(SELECT COUNT(*)
FROM CatalogueFournisseurs
WHERE CatalogueFournisseurs.NumFournisseur =
Fournisseurs.NumFournisseur)
AS ProduitsVendus
FROM Fournisseurs
Fournisseurs et total produits vendus (10 lignes)
NomFournisseur
ProduitsVendus
Shinoman
3
Taupe Bike
6
Le guidon dans les nuages
5
ProFormance
3
Pédale douce
1
Vélo 2000
22
Le paradis du VTT
9
Vélo en gros
5
VTT Star
30
Poulie d'or
6
Base de données AgenceSpectacles
Énoncé
« Afficher chaque client et la date de la dernière réservation qu’ils ont
effectuée. »
Traduction et
Nettoyage
Sélectionner [SELECT] le prénom du client, le nom du client, et aussi
(Sélectionner [SELECT] la plus haute [MAX] (date de début) à partir de
[FROM] la table des engagements pour ce client où [WHERE] le numéro
client dans la table des engagements = numéro client) à partir de [FROM]
la table des clients.
SQL
INFO
SELECT Clients.PrénomClient,
Clients.NomClient,
(SELECT MAX(DateDébut)
FROM Engagements
WHERE Engagements.NumClient =
Clients.NumClient)
AS DernièreRéservation
FROM Clients
La colonne DernièreRéservation de certains clients est vide car ces clients n’ont fait aucune réservation.
308
Les tables multiples
PARTIE 3
Dernière réservation (15 lignes)
PrénomClient
NomClient
DernièreRéservation
Céline
Calisson
23/12/1999
Anne
Fourastié
17/12/1999
Jérémy
Lingois
26/12/1999
Karl
Picard
24/12/1999
Eline
Mercier
19/12/1999
Thomas
Fuchs
23/12/1999
Amélie
Bouchard
19/12/1999
Samuel
Poulain
19/12/1999
Sarah
Thorez
24/12/1999
Richard
Rouland
24/12/1999
Base de données EmploiDuTemps
Énoncé
« Affichez toutes les UV et comptez les cours pour chaque UV le lundi. »
Traduction et
Nettoyage
Sélectionner [SELECT] le nom de l’UV et aussi(Sélectionner [SELECT] le
total [COUNT] (*) des cours à partir de [FROM] la table des cours où
[WHERE] Lundi est = vrai [-1] pour cette UV et [AND] le numéro de cours
de l’UV = numéro de l’UV) à partir de [FROM] la table des UV
SQL
INFO
SELECT UV.NomUV,
(SELECT Count(*)
FROM Planning
WHERE Lundi = -1
AND Planning.NumUV = UV.NumUV)
AS TotalLundi
FROM UV
Assurez-vous d’utiliser le bon test pour la valeur vrai. Certains SGBD nécessitent la comparaison au mot
clé TRUE alors que d’autres demandent la comparaison au chiffre 1.
Au lieu de retourner une valeur nulle quand il n’y a pas de lignes, la fonction d’agrégation COUNT
retourne un zéro.
Base de données Bowling
Énoncé
« Affichez les joueurs et leur score le plus élevé. »
Traduction et
Nettoyage
Sélectionner [SELECT] le prénom du joueur, le nom du joueur, et aussi
(Sélectionner [SELECT] le plus élevé [MAX](score) à partir de [FROM] la
table des scores des joueurs pour ce joueur où [WHERE] le numéro du
joueur dans la table des scores des joueurs = numéro du joueur) à partir de
[FROM] la table des joueurs
Sous-requêtes
CHAPITRE 11
Total UV le lundi (56 lignes)
SQL
NomUV
TotalLundi
Introduction à la comptabilité 1
2
Introduction à la comptabilité 2
1
Introduction à la gestion financière
1
Comptabilité
1
Fiscalité
1
Introduction au management
0
Développement d'un business plan
0
Introduction à la gestion d'entreprise
1
Technologies de l'information 1
1
Technologies de l'information 2
0
Introduction à l'art
1
SELECT Joueurs.PrénomJoueur, Joueurs.NomJoueur,
(SELECT MAX(Score)
FROM ScoresJoueurs
WHERE ScoresJoueurs.NumJoueur =
Joueurs.NumJoueur)
AS MeilleurScore
FROM Joueurs
Meilleur score (32 lignes)
PrénomJoueur
NomJoueur
MeilleurScore
Barbara
Fournier
164
David
Fournier
178
Jean
Gamblin
191
Sarah
Gamblin
149
Anne
Patisson
165
Yves
Patisson
179
Carole
Bichat
195
David
Bichat
150
Aline
Blanchard
164
David
Blanchard
180
Suzanne
Martin
194
309
310
Les tables multiples
PARTIE 3
Base de données Recettes
Énoncé
« Listez toutes les viandes et le nombre de fois où elles apparaissent dans une
recette. »
Traduction et
Nettoyage
Sélectionner [SELECT] la description du type d’ingrédient, le nom de
l’ingrédient, et aussi (Sélectionner [SELECT] le total [COUNT] (*) des
lignes à partir de [FROM] la tables des ingrédients des recettes pour cet
ingrédient où [WHERE] le numéro d’ingrédient de la recette = le numéro
d’ingrédient) à partir de [FROM] la table des types d’ingrédient reliée à
[INNER JOIN] la table des ingrédients en se basant sur [ON] le numéro du
type d’ingrédient où [WHERE] la description du type d’ingrédient est =
'Viande'
SQL
SELECT TypesIngrédients.DescriptionTypeIngrédient,
Ingrédients.NomIngrédient,
(SELECT COUNT(*)
FROM IngrédientsRecette
WHERE IngrédientsRecette.NumIngrédient =
Ingrédients.NumIngrédient)
AS NombreRecette
FROM TypesIngrédients
INNER JOIN Ingrédients
ON TypesIngrédients.NumTypeIngrédient =
Ingrédients.NumTypeIngrédient
WHERE
TypesIngrédients.DescriptionTypeIngrédient
='Viande'
Viande contenue dans des recettes (11 lignes)
DescriptionTypeIngrédient
NomIngrédient
NombreRecette
Viande
Bœuf
2
Viande
Poulet
0
Viande
Bacon
0
Viande
Poulet découpé
0
Viande
T-bone
0
Viande
Blanc de poulet
0
Viande
Avant cuisse de poulet
1
Viande
Aile de poulet
0
Viande
Cuisse de poulet
1
Viande
Steak
0
Viande
Farce
1
Sous-requêtes
CHAPITRE 11
311
Sous-requêtes dans des filtres
Base de données Ventes
Énoncé
« Affichez les clients qui ont commandé des vêtements ou des accessoires. »
Traduction et
Nettoyage
Sélectionner [SELECT] le numéro client, le prénom du client, le nom du
client à partir de [FROM] la table des clients où [WHERE] le numéro client
est = tout [ANY] numéro client (Sélectionner [SELECT] le numéro client à
partir de [FROM] la table des commandes reliée à [INNER JOIN] la table
des détails de commandes en se basant sur [ON] le numéro de commande,
reliée [INNER JOIN] ensuite à la table des produits en se basant sur [ON] le
numéro du produit, et reliée à [INNER JOIN] la table des catégories en se
basant sur [ON] le numéro de catégorie où [WHERE] la description de la
catégorie est = 'Vêtements' ou [OR] description de la catégorie est =
'Accessoires'
SQL
SELECT Clients.NumClient,
Clients.PrénomClient,
Clients.NomClient
FROM Clients
WHERE Clients.NumClient = ANY
(SELECT Commandes.NumClient
FROM ((Commandes
INNER JOIN DétailCommandes
ON Commandes.NumCommande =
DétailCommandes.NumCommande)
INNER JOIN Produits
ON Produits.NumProduit =
DétailCommandes.NumProduit)
INNER JOIN Catégories
ON Catégories.NumCatégorie =
Produits.NumCatégorie
WHERE Catégories.DescriptionCatégorie
='Vêtements'
OR Catégories.DescriptionCatégorie
= 'Accessoires')
Base de données AgenceSpectacles
Énoncé
INFO
« Listez les groupes qui ont joué pour les clients Mercier et Rouland. »
Nous avons résolu ce problème au Chapitre 8 avec une jointure de deux sous-requêtes de table complexes. À présent, nous utilisons EXISTS.
312
Les tables multiples
PARTIE 3
Clients ayant commandé des vêtements ou des accessoires (27 lignes)
NumClient
PrénomClient
NomClient
1001
Suzanne
Vericel
1002
Gérard
Thomson
1003
Gustave
Haller
1004
Michel
Douillet
1005
Karine
Perrin
1006
Jean
Vistule
1007
Laura
Callahan
1008
Nelly
Pattisson
1009
Georges
Dahan
1010
Marcel
Biron
Traduction et
Nettoyage
Sélectionner [SELECT] le numéro de groupe, et le nom de scène du groupe
à partir de [FROM] la table des groupes où [WHERE] il existe [EXISTS]
(Sélectionner [SELECT] * une ligne à partir de [FROM] la table des clients
reliée à [INNER JOIN] la table des engagements en se basant sur [ON] le
numéro client où [WHERE] le nom du client est = 'Rouland' et [AND] le
numéro du groupe dans la table des groupes est égal = le numéro du groupe
dans la table des engagements), et [AND] il existe [EXISTS] aussi (Sélectionner [SELECT] * une ligne à partir de [FROM] la table des clients reliée
à [INNER JOIN] la table des engagements en se basant sur [ON] le numéro
client où [WHERE] le nom du client est = 'Mercier' et [AND] le numéro
du groupe dans la table des groupes est égal = le numéro du groupe dans la
table des engagements)
SQL
SELECT Groupes.NumGroupe,
Groupes.NomScène
FROM Groupes
WHERE EXISTS
(SELECT *
FROM Clients
INNER JOIN Engagements
ON Clients.NumClient =
Engagements.NumClient
WHERE Clients.NomClient='Rouland'
AND Engagements.NumGroupe =
Groupes.NumGroupe)
AND EXISTS
(SELECT *
FROM Clients
Sous-requêtes
CHAPITRE 11
313
INNER JOIN Engagements
ON Clients.NumClient =
Engagements.NumClient
WHERE Clients.NomClient='Mercier'
AND Engagements.NumGroupe =
Groupes.NumGroupe)
EXISTS Groupes pour Mercier et Rouland (3 lignes)
NumGroupe
NomScène
1001
Trio urbain
1003
Les chaussettes rouges
1006
Danses du ballet
Base de données EmploiDuTemps
Énoncé
« Affichez les étudiants qui n’ont jamais abandonné un cours. »
Traduction et
Nettoyage
Sélectionner [SELECT] le numéro d’étudiant, prénom étudiant, et nom
étudiant à partir de [FROM] la tables des étudiants où [WHERE] le
numéro d’étudiant n’est pas dans [NOT IN] la (Sélectionner [SELECT] le
numéro d’étudiant à partir de [FROM] la table du planning des étudiants
reliée [INNER JOIN] à la table du statut des cours des étudiants en se
basant sur [ON] le statut du cours où [WHERE] la description du statut du
cours est = 'Abandon'
SQL
INFO
SELECT Etudiants.NumEtudiant,
Etudiants.PrénomEtudiant,
Etudiants.NomEtudiant
FROM Etudiants
WHERE Etudiants.NumEtudiant NOT IN
(SELECT PlanningEtudiants.NumEtudiant
FROM PlanningEtudiants
INNER JOIN StatutEtudiant
ON PlanningEtudiants.NumStatut =
StatutEtudiant.NumStatut
WHERE
StatutEtudiant.DescriptionStatut
= 'Abandon')
Cette requête plutôt simple trouve tous les étudiants qui ont échoué à un cours dans une sous-requête et
demande ensuite tous les étudiants qui ne sont pas dans cette liste. Songez à la manière dont on pourrait
résoudre cela avec une jointure externe.
314
Les tables multiples
PARTIE 3
Etudiants jamais abandonné (15 lignes)
NumEtudiant
PrénomEtudiant
NomEtudiant
1002
André
Fumagali
1003
Sarah
Lemaire
1004
Carole
Pinson
1005
Céline
Carouge
1006
Stéphane
Bouchery
1007
Elisabeth
Marchand
1008
Claire
Coste
1010
Marie-Laurence
Portal
1011
Philippe
Thoiron
1012
Sandrine
Testut
Base de données Bowling
Énoncé
« Affichez les capitaines d’équipe qui ont une moyenne plus élevée que les
autres membres de leur équipe. »
Traduction et
Nettoyage
Sélectionner [INNER JOIN] le nom de l’équipe, le numéro du joueur, le
prénom du joueur, le nom du joueur, et la moyenne à partir de [FROM] la
table des joueurs reliée à [INNER JOIN] la table des équipes en se basant
sur [ON] numéro du joueur du match = numéro du capitaine où [WHERE]
la moyenne est supérieure à > toutes [ALL] de la (sélection de la moyenne à
partir de [FROM] la table des joueurs où [WHERE] le numéro du joueur
n’est pas égal <> au numéro du joueur actuel et [AND] le numéro d’équipe
est égal = au numéro d’équipe actuel
SQL
INFO
SELECT Equipes.NomEquipe, Joueurs.NumJoueur,
Joueurs.PrénomJoueur,
Joueurs.NomJoueur,
Joueurs.Moyenne
FROM Joueurs
INNER JOIN Equipes
ON Joueurs.NumJoueur = Equipes.NumCapitaine
WHERE Joueurs.Moyenne > All
(SELECT B2.Moyenne
FROM Joueurs AS B2
WHERE B2.NumJoueur <> Joueurs.NumJoueur
AND B2.NumEquipe = Joueurs.NumEquipe)
Nous avons donné un alias à la seconde copie de la table Joueurs dans la sous-requête afin de rendre les
choses plus claires. Nous ne voulons pas comparer la comparer à la moyenne du capitaine (cela provoquerait l’échec du prédicat > ALL). Nous ne voulons la comparer qu’aux autres joueurs de la même
équipe.
Sous-requêtes
CHAPITRE 11
315
Meilleure moyenne des capitaines (2 lignes)
NomEquipe
NumJoueur
PrénomJoueur
NomJoueur
Moyenne
Requins
5
Anne
Patisson
170,00
Renards
16
Richard
Fayolle
165,00
Base de données Recettes
Énoncé
INFO
« Affichez tous les ingrédients des recettes qui contiennent des carottes. »
Nous vous avions promis au Chapitre 8 de vous montrer comment résoudre ce problème avec une sousrequête. Nous tenons nos promesses !
Traduction et
Nettoyage
Sélectionner [SELECT] le nom de la recette et le nom de l’ingrédient à
partir de [FROM] la table des recettes reliée à [INNER JOIN] la table des
ingrédients des recettes en se basant sur [ON] le numéro, et ensuite reliée à
[INNER JOIN] la table des ingrédients en se basant sur [ON] le numéro
d’ingrédient où [WHERE] le numéro de recette est dans [IN] la
(sélect[SELECT] ion du numéro de recette à partir de [FROM] la table des
ingrédients reliée à [INNER JOIN] la table des ingrédients des recettes en
se basant sur [ON] le numéro d’ingrédient où [WHERE] le nom d’ingrédient est= 'Carotte'
SQL
INFO
SELECT Recettes.NomRecette,
Ingrédients.NomIngrédient
FROM (Recettes
INNER JOIN IngrédientsRecette
ON Recettes.NumRecette =
IngrédientsRecette.NumRecette)
INNER JOIN Ingrédients
ON Ingrédients.NumIngrédient =
IngrédientsRecette.NumIngrédient
WHERE Recettes.NumRecette
IN
(SELECT IngrédientsRecette.NumRecette
FROM Ingrédients
INNER JOIN IngrédientsRecette
ON Ingrédients.NumIngrédient =
IngrédientsRecette.NumIngrédient
WHERE Ingrédients.NomIngrédient = 'Carotte')
Si vous placez le filtre pour les ‘carottes’ dans une requête externe, vous ne verrez que les ingrédients
carottes dans la sortie. Dans ce problème, nous voulons voir tous les ingrédients de toutes les recettes qui
utilisent des carottes, et la sous-requête est donc un bon moyen de résoudre cela.
316
Les tables multiples
PARTIE 3
Recettes contenant des carottes (16 lignes)
NomRecette
NomIngrédient
Ragoût irlandais
Bœuf
Ragoût irlandais
Oignon
Ragoût irlandais
Pomme de terre
Ragoût irlandais
Carotte
Ragoût irlandais
Eau
Ragoût irlandais
Bière Guinness
Filets de saumon en papillotes
Saumon
Filets de saumon en papillotes
Carotte
Filets de saumon en papillotes
Poireau
Filets de saumon en papillotes
Poivre rouge
Filets de saumon en papillotes
Beurre
Problèmes à résoudre
La solution de ces problèmes est fournie sur le CD-Rom d’accompagnement aux formats
Access 2000 et Access 97. Après avoir dézippé le fichier requetessql.zip fourni dans le répertoire RequetesSQL, vous trouverez les solutions dans le répertoire \Solutions\Access2000
(respectivement \Solutions\Access97). Par exemple, la solution du deuxième des problèmes
portant sur la base de données Ventes est fournie dans le fichier Ventes.mdb (requête intitulée
Chapitre 11-2). Ne vous inquiétez pas si votre propre solution ne correspond pas exactement
à celle que nous proposons – il y a souvent plusieurs syntaxes possibles pour une même
requête SQL. L’essentiel est que l’ensemble de résultat soit le même.
Base de données Ventes
1. « Affichez les produits et leur dernière date de commande. » (Indice : utilisez la fonction
MAX.)
2. « Listez les clients qui ont commandé des VTT. » (Indice : créez un filtre en utilisant IN.)
3. « Trouvez tous les clients qui ont commandé un VTT mais qui n’ont pas commandé de
casque. » (Indice : commencez comme pour la précédente requête et ajoutez un filtre en
utilisant NOT EXISTS.)
4. « Quels produits n’ont jamais été commandés ? » (Indice : créez un filtre en utilisant NOT
IN.)
Base de données AgenceSpectacles
1. « Montrez-moi les groupes et le nombre de leurs engagements. »
2. « Listez les clients qui ont réservé pour des spectacles de jazz ou de karaoke. »
3. « Trouvez les groupes qui ont joué pour les clients Mercier ou Rouland. »
4. « Affichez les agents qui n’ont jamais engagé un groupe. »
Sous-requêtes
CHAPITRE 11
317
Base de données EmploiDuTemps
1. « Listez tous les professeurs et le nombre de cours qu’ils dispensent. »
2. « Affichez les étudiants inscrits à un cours le mardi. »
3. « Montrez-moi les étudiants qui ont une note supérieure ou égale à 85 en arts et qui ont
également une note supérieure ou égale à 85 en informatique. » (Indice : créez un filtre en
utilisant EXISTS.)
Base de données Bowling
1. « Montrez-moi tous les joueurs et le nombre de jeux que chacun a disputés. »
2. « Montrez-moi les tournois qui n’ont pas encore été joués. »
3. « Trouvez les joueurs qui ont un score d’au moins 170 à la fois au Bowling Acapulco et à
la salle Omnisports. »
4. « Listez tous les joueurs qui ont une moyenne qui est inférieure à celle de tous les autres
joueurs de la même équipe. »
Base de données Recettes
1. « Montrez-moi les types de recettes et le nombre de recettes de chaque type. »
2. « Montrez-moi les recettes qui contiennent du bœuf et de l’ail. » (Indice : créez un filtre en
utilisant EXISTS.)
3. « Listez les ingrédients qui sont utilisés dans une recette où la quantité utilisée dans la
recette n’est pas la mesure par défaut. » (Indice : créez un filtre en utilisant <> SOME.)
4. « Les ingrédients qui ne sont utilisés dans aucune recette. » (Indice : créez un filtre en utilisant NOT IN.)