Bases de données : SQL
Support de formation SQL bases de données
...
Structure et contenu des tables
AVION ( NumAvion, TypeAvion, BaseAeroport)
NumAvion : numéro d’avion (clé primaire, numérique)
TypeAvion : type d’avion : A320, B707… (Clé étrangère vers la colonne TypeAvion de la table TYPE, alphanumérique)
BaseAeroport : identificateur de l’aéroport où est basé l’avion (clé étrangère vers la colonne IdAeroport de la table AEROPORT, 3 lettres)
...
Place de SQL dans les SGBDR
La plupart des SGBDR du marché (DB2-IBM, ORACLE, INFORMIX, SYBASE...) offrent plus qu'un langage de requête puissant (en général SQL). On y trouve toute une panoplie d'outils dits de 4ème génération :
* générateur d'écrans : facilitant la saisie et l'affichage de données par imbrication de requêtes,
* générateur d'états : pour la sortie d'état de la BD sur papier ou écran,
* générateur d'application : qui fait appel aux écrans et états précédents, et qui permet la création de menus ainsi que des traitements sur la BD par requêtes simples (en SQL) ou en imbriquant des appels à des programmes développés en L3G (ce qui permet de combiner puissance des outils de 4ème génération et souplesse des L3G).
* générateur de schéma conceptuel : pour la description de la structure des données (entités, attributs, liens...). A tous les niveaux, le langage SQL normalisé par l'ANSI est reconnu comme le langage de requête par excellence qui permet la création, la manipulation et le contrôle des données ; il constitue le lien entre les divers composants du SGBDR comme le montre le schéma suivant :
- DECOUVERTE PRATIQUE DU LANGAGE SQL
2.1 Connexion à la base de données PUBLI
- Outils disponibles dans le menu Microsoft SQL Server 7.0 : Analyseur de requête SQL (interpréteur SQL interactif), Enterprise Manager (administration des bases de données en réseau)
- Pour l’initiation à SQL, utilisez Analyseur de requête SQL
- Choisissez « Utilisez l’authentification SQL Server» :
- Nom de connexion : Stage
- Mot de passe : stage
- A la connexion, vous êtes dans la base de données par défaut master
- Pour accéder à la base de données de démonstration publi,sélectionnez la dans la liste déroulante, ou utilisez l'instruction USE:
USE PUBLI
- Pour vérifier la connexion, faites une interrogation simple :
- Dans l’onglet Requête tapez :
select *
from magasins
Cliquez sur la flèche verte pour lancer la requête, qui affiche la description des magasins : id_mag nom_mag adresse_mag ville pays code_postal
------ ---------------------------------------- ---------------------------------------- -------------------- ---- -----------
6380 Eric the Read Books 788, Catamaugus Ave. Seattle WA 98056
7066 Librairie spécialisée 567, Av. de la Victoire Paris FR 75016
7067 Moissons livresques 577, Boulevard Anspach. Bruxelles BE 1000
..etc…
2.2 Description de la base de données PUBLI
- Il y a parfois des petites différences entre la documentation et le contenu réel des tables => pensez à vérifier vos programmes en visualisant les contenus des tables par des requêtes simples
- A partir de SQL Server 7.0, la base originale sur laquelle s’appuient tous les exemples de la documentation Microsoft, est en anglais : PUBS. Vous pouvez l’utiliser pour tester des syntaxes documentées dans l’aide, mais les exercices porteront toujours sur la « version française » de la base : PUBLI
Publi décrit la base de données d’un groupe d’édition.
- Tous les éditeurs appartenant au groupe sont décrits dans la table éditeurs.
- Les éditeurs ont des logo (table pub_info), emploient du personnel (table employé), et éditent des livres (table titre).
- Chaque employé occupe un emploi (table emplois)
- Chaque livre est écrit par un ou plusieurs auteurs (table auteurs et table intermédiaire titreauteur)
- Pour chaque livre vendu, son/ses auteurs touchent des droits, qui sont définis en pourcentage du prix de vente, par tranche, en fonction de la quantité de livres vendus (table droits_prévus).
- Les livres sont vendus dans des magasins (table ventes et magasins)
- Différents types de remise sont consentis sur les livres vendus (table remises)
Les commentaires de détail seront donnés table par table.
Table éditeurs
Nom_colonne Type de données NULL Par défaut Check Clé/index
id_éditeur char(4) non oui (1) CP, ordonné.
nom_éditeur varchar(40) oui
ville varchar(20) oui
région char(2) oui
pays varchar(30) oui 'USA'
(1) La contrainte CHECK id_éditeur est définie comme (id_éditeur in ('1389', '0736', '0877', '1622', '1756')
OR id_éditeur LIKE '99[0-9][0-9]')
id_éditeur nom_éditeur ville région pays
0736 New Moon Books Boston MA USA
0877 Binnet & Hardley Washington DC USA
1389 Algodata Infosystems Bruxelles NULL BE
1622 Five Lakes Publishing Chicago IL USA
1756 Ramona éditeur Lausanne NULL CH
9901 GGG&G Munich NULL GER
9952 Scootney Books New York NY USA
9999 Editions Lucerne Paris NULL FR
Table pub_info
Nom_colonne Type de données NULL Par défaut Check Clé/index
pub_id char(4) non CP, ordonné., CE éditeurs(id_éditeur)
logo image oui
pr_info text oui
id_éditeur logo (1) info_rp (2)
0736 NEWMOON.BMP Exemple de données de type text pour New Moon Books, éditeur 0736 dans la base de données pubs. New Moon Books est situé à Boston, Massachusetts.
0877 BINNET.BMP Exemple de données de type text pour Binnet & Hardley, éditeur 0877 dans la base de données pubs. Binnet & Hardley est situé à Washington, D.C.
1389 ALGODATA.BMP Exemple de données de type text pour Algodata Infosystems, éditeur 1389 dans la base de données pubs. Algodata Infosystems est situé à Bruxelles, Belgique.
1622 5LAKES.BMP Exemple de données de type text pour Five Lakes Publishing, éditeur 1622 dans la base de données pubs. Five Lakes Publishing est situé à Chicago, Illinois.
1756 RAMONA.BMP Exemple de données de type text pour Ramona éditeur, éditeur 1756 dans la base de données pubs. Ramona éditeur est situé à Lausanne, Suisse.
9901 GGGG.BMP Exemple de données de type text pour GGG&G, éditeur 9901 dans la base de données pubs. GGG&G est situé à Munich, Allemagne.
9952 SCOOTNEY.BMP Exemple de données de type text pour Scootney Books, éditeur 9952 dans la base de données pubs. Scootney Books est situé à New York City, New York.
9999 LUCERNE.BMP Exemple de données de type text pour Éditions Lucerne, éditeur 9999 dans la base de données pubs. Les Éditions Lucerne sont situées à Paris, France.
(1) Les informations présentées ici NE sont PAS les données réelles. Il s'agit du nom du fichier d'où provient le bitmap (données graphiques).
(2) Le texte présenté ici NE constitue PAS la totalité des données. Lors de l'affichage de données text, l'affichage est limité à un nombre fini de caractères. Ces informations présentent les 120 premiers caractères de la colonne de texte.
Table employé
Tous les employés ont un coefficient actuel (colonne position_employé), compris entre le coefficient minimum et le coefficient maximum correspondant à leur type d’emploi (niv_min et niv_max dans la table emplois)
Nom_colonne Type de données NULL Par défaut Check Clé/index
id_employé empid non oui (1) CP, non ordonné.
pn_employé varchar(20) non Composé, ordonné. (2)
init_centrale char(1) oui Composé, ordonné. (2)
nom_employé varchar(30) non Composé, ordonné. (2)
id_emploi smallint non 1 CE emplois(id_emploi)
position_employé tinyint non 10
id_éditeur char(4) non '9952' CE éditeurs(id_éditeur)
date_embauche datetime non GETDATE( )
(1) La contrainte CHECK est définie comme (id_employé LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]') OR
(id_employé LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
(2) L'index composé, ordonné est défini sur nom_employé, pn_employé, init_centrale.
Les tableaux suivants présentent le contenu de la table employé. La première colonne (id_employé) est répétée dans la liste du dessous, devant les colonnes 6 à 8. Elle n'est répétée qu'à des fins de lisibilité.
id_employé (#1) pn_employé (#2) init_centrale (#3) nom_employé (#4) id_emploi (#5)
...
Consultation d’une base de données
Doc. en Ligne : chapitre « Accéder aux données et les modifier »
Requête Select simple sur une seule table
Doc. en Ligne : « Principe de bases des requêtes»
SQL 2 : p. 43-60
- L'instruction SELECT spécifie les colonnes que vous voulez récupérer. La clause FROM spécifie les tables dans lesquelles se trouvent les colonnes. La clause WHERE spécifie les lignes que vous voulez visualiser dans les tables. Syntaxe simplifiée de l'instruction SELECT :
SELECT liste_de_sélection
FROM liste_de_tables
WHERE critères_de_sélection
Par exemple, l'instruction SELECT suivante extrait les nom et prénom des écrivains de la table auteurs vivant à Paris.
SELECT pn_auteur, nom_auteur
FROM auteurs
WHERE ville = 'Paris'
pn_auteur nom_auteur
--------- --------------
Charles Mathieu
Patricia Merrell
Alain D'Autricourt
Marc Jalabert
Jean-Rémy Facq
Exercice 1 : Afficher le nom, la ville et la région de tous les éditeurs
- La syntaxe complète de l'instruction SELECT :
SELECT [ALL | DISTINCT] liste_de_sélection
[INTO [nouveau_nom_de_table]]
[FROM {nom_de_table | nom_de_vue}[(conseils-optimiseur)]
[[, {nom_de_table2 | nom_de_vue2}[(conseils-optimiseur)]
[..., {nom_de_table16 | nom_de_vue16}[(( conseils-optimiseur)]]]
[clause WHERE]
[clause GROUP BY]
[clause HAVING]
[clause ORDER BY]
[clause COMPUTE]
[FOR BROWSE ]
- Les clauses d'une instruction SELECT doivent être utilisées dans l'ordre décrit ci-dessus. (Par exemple, si l'instruction comprend une clause GROUP BY et une clause ORDER BY, la clause GROUP BY précède la clause ORDER BY).
- Le nom des objets de la base de données doit être déterminé si le choix d'un objet est ambigu : vous pouvez alors préciser le nom de la base de données, du propriétaire, de la table, en les séparant par des points :
base_de_données.propriétaire.nom_de_table.nom_de_colonne
Par exemple, si l'utilisateur suzanne possède la table auteurs dans la base de données pubs, l'identificateur unique de la colonne ville dans cette table est:
pubs.suzanne.auteurs.ville
- Vous pouvez demander un tri en fonction du nom de colonne ou de la position de colonne dans la liste de sélection. Les deux requêtes suivantes sont équivalentes:
SELECT pn_auteur, nom_auteur
FROM auteurs
ORDER BY nom_auteur
ORDER BY 2
Pour plus d'informations sur SELECT, consultez l'instruction SELECT dans le Manuel de référence Transact-SQL (choisissez Select Examples (T-SQL) )
Sélection de lignes : clause Where
Les critères de sélection, ou conditions, de la clause WHERE peuvent inclure:
- Des opérateurs de comparaison (tels que =, < >, <, et >)
WHERE avance * 2 > cumulannuel_ventes * prix
- Des intervalles (BETWEEN et NOT BETWEEN)
WHERE cumulannuel_ventes between 4095 and 12000
- Des listes (IN, NOT IN)
WHERE state in ('BE', 'CH', 'LU')
- Des concordances avec des modèles (LIKE et NOT LIKE)
WHERE téléphone not like '45%'
% Toute chaîne de zéro caractère ou plus
_ Tout caractère unique
[a-f] Tout caractère de l'intervalle ([a-f]) ou de l'ensemble spécifié ([abcdef])
[^a-f] Tout caractère en dehors de l'intervalle ([^a-f]) ou de l'ensemble spécifié ([^abcdef])
- Des valeurs inconnues (IS NULL et IS NOT NULL)
where avance is null
- Des combinaisons de ces critères (AND, OR)
where avance < 30000 or (cumulannuel_ventes > 2000 and cumulannuel_ventes < 2500)
Exercice 2 : LIKE, BETWEEN, AND
Afficher le nom, le prénom, et la date d’embauche des employés embauchés en 90, dont le nom commence par ‘L’, et la position est comprise entre 10 et 100
Exercice 3 : ORDER BY
Afficher le nom et la date d’embauche des employés, classés par leur identificateur d’éditeur, puis par leur nom de famille (sous-critère)
Exercice 4 : IN, ORDER BY
Afficher le nom, le pays et l’adresse des auteurs Français, Suisse ou Belge, classés par pays.
Expressions et fonctions
- Les 4 opérateurs arithmétiques de base peuvent être utilisés dans les clauses SELECT, WHERE et ORDER pour affiner les recherches partout où il est possible d'utiliser une valeur d'attribut :
Livres qui reçoivent une avance sur vente supérieure à 500 fois leur prix :
SELECT titre, prix, avance
FROM titres
WHERE avance > = 500 * prix
- Suivant les systèmes la gamme des opérateurs et des fonctions utilisables peut être très évoluée : elle comporte toujours des fonctions de traitement de chaînes de caractères, des opérateurs sur les dates, etc...
Cet exemple détermine la différence en jours entre la date courante et la date de publication :
SELECT newdate = DATEDIFF(day, datepub, getdate())
FROM titres
Requête sur les groupes : GROUP BY, fonctions de groupe, HAVING
Doc. en ligne « Concepts de requêtes avancées » : « Regroupement de lignes à l’aide de
Group by »
- La clause GROUP BY est employée dans les instructions SELECT pour diviser une table en groupes. Vous pouvez regrouper vos données par nom de colonne ou en fonction des résultats des colonnes calculées lorsque vous utilisez des données numériques. L'instruction suivante calcule l'avance moyenne et la somme des ventes annuelles cumulées pour chaque type de livre:
SELECT type, AVG(avance), SUM(cumulannuel_ventes)
FROM titres
GROUP BY type
type
------------ ----------------------- -----------
cui_moderne 51,000.00 24278
cui_traditio 43,000.00 19566
gestion 43,000.00 30788
informatique 51,000.00 12875
psychologie 29,000.00 9939
SANS TITRE (null) (null)
(6 ligne(s) affectée(s))
- Les fonctions de groupe (ou «d’agrégation ») effectuent un calcul sur l'ensemble des valeurs d'un attribut d'un groupe de tuples. Un groupe est un sous ensemble des tuples d'une table tel que la valeur d'un attribut y reste constante ; un groupe est spécifié au moyen de la clause GROUP BY suivi du nom de l'attribut à l'origine du groupement. En l'absence de cette clause tous les tuples sélectionnés forment le groupe.
* COUNT, compte les occurrences pour un attribut,
* SUM, somme les valeurs de l'attribut (de type numérique),
* AVG, fait la moyenne (Average) des valeurs de l'attribut,
* MAX, MIN, donne la valeur MAX et la valeur MIN de l'attribut.
- Ces fonctions imposent la spécification de l'attribut en tant qu'argument. Si cet argument est précédé du mot clé DISTINCT les répétitions sont éliminées. D'autre part, les valeurs indéterminées (= NULL) ne sont pas prises en compte.
- La clause HAVING est équivalente à WHERE mais elle se rapporte aux groupes. Elle porte en général sur la valeur d’une fonction de groupe : seuls les groupes répondant au critère spécifié par HAVING feront parti du résultat.
Regrouper les titres en fonction du type, en éliminant les groupes qui contienne un seul livre
SELECT type
FROM titres
GROUP BY type
HAVING COUNT(*) > 1
type
------------
cui_moderne
cui_traditio
gestion
informatique
psychologie
Regrouper les titres en fonction du type, en se limitant aux types qui débutent pas par la lettre «c»
SELECT type
FROM titres
GROUP BY type
HAVING type LIKE 'c%'
type
------------
cui_moderne
cui_traditio
Regrouper les titres en fonction du type par éditeur, en incluant seulement les éditeurs dont le numéro d'identification est supérieur à 0800 et qui ont consenti des avances pour un total supérieur à 90 000 FF, et qui vendent des livres pour un prix moyen inférieur à 150 FF:
SELECT id_éditeur, SUM(avance), AVG(prix)
FROM titres
GROUP BY id_éditeur
HAVING SUM(avance) > 90000
AND AVG(prix) < 150
AND id_éditeur > '0800'
Même exemple, en éliminant les titres dont le prix est inférieur à 60 FF, et en triant les résultats en fonction des numéros d'identification des éditeurs:
SELECT id_éditeur, SUM(avance), AVG(prix)
FROM titres
WHERE prix >= 60
GROUP BY id_éditeur
HAVING SUM(avance) > 90000
AND AVG(prix) < 150
AND id_éditeur > '0800'
ORDER BY id_éditeur
Exercice 5 : GROUP BY, COUNT, MIN, MAX
Pour chaque niveau d’emploi (table employés, colonne position_employé) afficher le nombre d’employés de ce niveau, la date d’embauche du salarié le plus ancien et du plus récent dans le niveau
Exercice 6 : GROUP BY, MAX
Pour chaque Identificateur de titre, calculer les droits prévus maximum (table droits_prévus, colonne droits)
Exercice 7 : GROUP BY, clause sur un sous-ensemble HAVING
Afficher le nombre des éditeurs regroupés par pays, en se limitant aux pays dont le nom contient un 'S' ou un 'R'
Infos de détails ET infos globales : COMPUTE
- La clause COMPUTE est employée dans des instructions SELECT avec des fonctions d'agrégation ligne SUM, AVG, MIN, MAX et COUNT pour générer des valeurs statistiques à partir de valeurs contenues dans des groupes de lignes.
- Ces valeurs statistiques apparaissent sous forme de lignes supplémentaires dans les résultats de la requête, ce qui vous permet de visualiser les lignes détail et les lignes de statistiques dans un seul ensemble de résultats..
Exemple : somme des prix des différents types de livres de cuisine
SELECT type, prix
FROM titres
WHERE type like 'cui%'
ORDER BY type, prix
COMPUTE SUM(prix) BY type
type prix
------------ --------------------------
cui_moderne 21.00
cui_moderne 136.00
sum
==========================
157.00
Exercice 8 : COMPUTE
Pour chaque niveau de droits, afficher les identificateurs des livres correspondant, le min et la max de ventes donnant droit à ce niveau (infos de détails), et le nombre de livres dans le niveau (récapitulatif)
droits id_titre minimum maximum
----------- -------- ----------- -----------
10 BU1032 0 5000
10 PC1035 0 2000
10 BU2075 0 1000
count
========
3
..etc…
Afficher les noms d’auteurs, leur pays et leur ville en les classant par ville (infos de détail), et le nombre d’auteurs par pays (récapitulatif)
Travail sur plusieurs tables : les jointures
Doc. en ligne : « Principe de base des jointures »
SQL2 : p. 61 à 78
- Les opérations de jointure permettent d'extraire des données à partir de plusieurs tables ou vues dans la même base de données ou dans des bases différentes en n'effectuant qu'une seule opération. Joindre deux ou plusieurs tables revient à comparer les données de colonnes spécifiques, et ensuite à utiliser les lignes sélectionnées dans les résultats de cette comparaison pour créer une nouvelle table.
- Une instruction de jointure:
- spécifie une colonne dans chaque table ;
- compare les valeurs de ces colonnes ligne par ligne ;
- forme de nouvelles lignes en combinant les lignes qui contiennent les valeurs retenues dans la comparaison.
Exemple de jointure : nom des auteurs et des éditeurs vivant dans la même ville :
SELECT pn_auteur, nom_auteur, nom_éditeur
FROM auteurs, éditeurs
WHERE auteurs.ville = éditeurs.ville
pn_auteur nom_auteur nom_éditeur
------------ ------------------ -------------------
Xavier Schildwachter Algodata Infosystems
Alain D'Autricourt Editions Lucerne
Charles Mathieu Editions Lucerne
Jean-Rémy Facq Editions Lucerne
Marc Jalabert Editions Lucerne
Patricia Merrell Editions Lucerne
Ou avec l’opérateur JOIN dans la syntaxe SQL 2 :
SELECT pn_auteur, nom_auteur, nom_éditeur
FROM auteurs
JOIN éditeurs on auteurs.ville = éditeurs.ville
Exercice 9 : Jointure entre trois tables
Afficher les noms des auteurs parisiens, les titres et les prix de leurs livres
Exercice 10 : Jointure sur quatre tables, ORDER BY, COMPUTE
Pour chaque éditeur, afficher le nom de l’éditeur, les titres des livres qu’il publie, les noms des magasins où ils sont vendus, le nombre d’exemplaires vendus dans chaque magasin, et le nombre d’exemplaires vendus au total par chaque éditeur :
nom_éditeur titre nom_mag qt
---------------------------------------- --------------------------------------------------------------------------------
Algodata Infosystems Guide des bases de données du gestionnaire pressé Eric the Read Books 5
Algodata Infosystems Guide des bases de données du gestionnaire pressé Bookbeat 10
Algodata Infosystems La cuisine - l'ordinateur : bilans clandestins Bookbeat 25
Algodata Infosystems Toute la vérité sur les ordinateurs Fricative Bookshop 15
sum
======
55
nom_éditeur titre nom_mag qt
Binnet & Hardley Les festins de Parly 2 Fricative Bookshop 10
Binnet & Hardley Les micro-ondes par gourmandise Doc-U-Mat: Quality Laundry and Books 25
…
Exercice 11 : jointure sur 4 tables, GROUP BY, HAVING, SUM
Afficher les noms des auteurs qui ont vendu au moins 20 livres, et le nombre de livres qu’ils ont vendus (tables auteurs, titreauteur, titres, ventes)
Les sous-requêtes
Doc. en ligne : « Concepts de requêtes avancées » : « Principe de base des sous-
requêtes »
- Une sous-requête est une instruction SELECT imbriquée à l'intérieur d'une instruction SELECT, INSERT, UPDATE ou DELETE, ou d'une autre sous-requête : une sous-requête peut porter sur la même table que la requête externe ou sur une autre table.
- Dans Transact-SQL, une sous-requête qui renvoie une seule valeur peut s'employer dans toutes les circonstances où une expression est autorisée.
- Les instructions SELECT qui incluent au moins une sous-requête sont parfois appelées requêtes imbriquées ou instructions SELECT imbriquées. Le fait d'imbriquer une instruction SELECT à l'intérieur d'une autre explique la présence du mot «structuré» dans l'expression «langage d'interrogation structuré» (SQL, Structured Query Language).
- Une sous-requête imbriquée dans une instruction SELECT externe présente la syntaxe suivante:
(SELECT [ALL | DISTINCT] liste_de_sélection_de_la_sous-requête
[FROM {nom_de_table | nom_de_vue}[conseils-optimiseur]
[[, {nom_de_table2 | nom_de_vue2}[conseils-optimiseur]
[..., {nom_de_table16 | nom_de_vue16}[conseils-optimiseur]]]
[clause WHERE]
[clause GROUP BY]
[clause HAVING])
- L'instruction SELECT d'une sous-requête se place toujours entre parenthèses. Elle ne peut pas contenir de clause ORDER BY ou COMPUTE.
- Une sous-requête peut s'imbriquer dans une clause WHERE, ou HAVING d'une instruction externe SELECT, INSERT, UPDATE ou DELETE, ou dans une autre sous-requête. Le niveau d'imbrication n'est pas limité.
- A noter le cas particulier des requêtes imbriquées placées après le SELECT, qui permettent d’afficher une colonne calculée (Cf. Exercice 14).
- Il existe deux catégories de sous-requêtes : les sous-requêtes qui renvoient une et une seule valeur directement manipulable par des opérateurs de comparaison, et les requêtes ensemblistes qui renvoient des listes manipulables par les opérateurs ensemblistes IN, ANY , ALL, EXISTS
Méthode de construction des sous-requêtes renvoyant une et une seule valeur
Pour trouver tous les livres de même prix que le livre « Toute la vérité sur les ordinateurs », on peut procéder en deux étapes, en cherchant d’abord le prix du livre « Toute la vérité sur les ordinateurs » :
SELECT prix
FROM titres
WHERE titre = 'Toute la vérité sur les ordinateurs'
prix
------
136,00
Puis, en utilisant ce résultat dans une seconde requête pour trouver les livres qui ont le même prix :
SELECT titre, prix
FROM titres
WHERE prix = 136
titre prix
--------------------- ------------------------- -------------
Toute la vérité sur les ordinateurs 136,00
Les festins de Parly 2 136,00
Les secrets de la Silicon Valley 136,00
Privation durable d'informations : étude… 136,00
En substituant à la constante 136, la requête qui calcule ce prix, on obtient la solution avec sous-requête :
SELECT titre, prix
FROM titres
WHERE prix = (SELECT prix FROM titres
WHERE titre = 'Toute la vérité sur les ordinateurs')
Avec la même démarche de construction progressive, on peut répondre à des questions plus complexes : dans tous les cas, il faut repérer dans la question, la proposition principale qui fournit la requête principale et les propositions subordonnées qui fournissent les critères de choix ; chaque subordonnée devient une requête imbriquée qu'il faudra d'abord tester indépendamment, sur un cas particulier.
Exemple : Afficher les titres des livres dont le prix est supérieur ou égal au tiers du prix maximum, et inférieur ou égal à la moyenne des prix.
-- 1) trouver le prix maximum
SELECT max(prix)
FROM titres
-- => 156.00
-- 2) trouver la moyenne des prix
SELECT avg(prix)
FROM titres
-- => 99.4
-- 3) Ecrire la requête principale avec ces constantes
SELECT titre
FROM titres
WHERE (prix >= 156.0/3)
and (prix <= 99.4)
-- 4) Réécrire la requête principale en substituant les requêtes imbriquées aux 2 constantes
SELECT titre
FROM titres
WHERE prix >= (SELECT max(prix)
FROM titres ) /3
and prix <= (SELECT avg(prix)
FROM titres )
Sous-requêtes ensemblistes
- Utilisation de IN et EXISTS pour tester l’appartenance d’un nuple à une liste construite par une requête imbriquée
Exemple : Afficher les noms et les prénoms des auteurs qui ont écrit au moins un livre (donc qui figurent dans la table titreauteur)
Avec l’opérateur IN :
SELECT nom_auteur, pn_auteur
FROM auteurs au
WHERE id_auteur IN (SELECT id_auteur FROM titreauteur)
Order by nom_auteur
Avec l’opérateur EXISTS :
SELECT nom_auteur, pn_auteur
FROM auteurs au
WHERE EXISTS (SELECT id_auteur FROM titreauteur
WHERE id_auteur = au.id_auteur)
Order by nom_auteur
Remarquer la jointure supplémentaire entre la table auteurs de la requête principale et la table titreauteur de la requête imbriquée, par rapport à la solution avec l’opérateur IN
Dans ce cas particulier, le problème est réductible à une requête simple, sans sous-requête :
SELECT DISTINCT nom_auteur, pn_auteur
FROM auteurs au, titreauteur ti
WHERE au.id_auteur = ti.id_auteur
ORDER BY nom_auteur, pn_auteur
- Sous-requêtes opérant sur des listes, introduites par un opérateur de comparaison modifié par ANY ou ALL :
Exemple 1 : Afficher les noms et les prénoms des auteurs dont tous les livres ont un prix de 136 F
SELECT nom_auteur, pn_auteur
FROM auteurs au
WHERE 136 = ALL (SELECT prix
FROM titres t, titreauteur ta
WHERE t.id_titre = ta.id_titre
AND au.id_auteur = ta.id_auteur)
ORDER BY nom_auteur, pn_auteur
Lorsqu’une requête imbriquée ne renvoie rien, toutes les expressions avec l’opérateur ALL sont vraies : on peut tout dire de l’ensemble vide. La requête ci-dessus trouve donc les auteurs recherchés, dont tous les livres valent 136 F, mais aussi les auteurs qui n’ont pas encore publié de livres (et ne figurent donc pas dans la table titreauteur)
Pour s’assurer du bon fonctionnement de l’opérateur ALL, il faut toujours doubler la sous-requête avec ALL d’une sous-requête d’existence :
SELECT nom_auteur, pn_auteur
FROM auteurs au
WHERE id_auteur IN (SELECT id_auteur FROM titreauteur)
and 136 = ALL ( SELECT prix
FROM titres t, titreauteur ta
WHERE t.id_titre = ta.id_titre
AND au.id_auteur = ta.id_auteur)
ORDER BY nom_auteur, pn_auteur
Exemple 2 : Afficher les noms et les prénoms des auteurs dont au moins un livre a un prix de 136F
SELECT nom_auteur, pn_auteur
