cours1_7 sous requetes

Systèmes de
Gestion de
Bases de
Données
Systèmes de
Gestion de
Bases de
Données
Sous requêtes
Il est parfois indispensable, pour extraire certaines
informations de la base de données, de pouvoir utiliser une
requête à l’intérieur d’une autre requête.
Cette requête incluse sera appelée « sous requête » (requête
interne).
Elle sera exécutée à l’intérieur de la requête « principale »
(requête externe) et les valeurs retournées pourront être utilisées
par la requête principale.
Ce même mécanisme peut être appliqué à l’intérieur de la
sous requête, générant ainsi des appels en cascade.
Requêtes et
sous requêtes
129
Systèmes de
Gestion de
Bases de
Données
130
Systèmes de
Gestion de
Bases de
Données
Sous requêtes
Cas 1: sous requêtes pour l’affichage de données
Ecart entre le prix de chaque produit et le prix moyen de
l’ensemble
select …(select … from … where)
from … where …
select id, prix-(select avg(prix)
from produit) as ecart
from produit;
Cas 2: sous requêtes comme source de données
select … from (select … from … where)
where …
id
Cas 3: sous requêtes comme condition de sélection
select …from …
where (select … from … where)
Systèmes de
Gestion de
Bases de
Données
Exemples
131
-11.58
P02
-135.48
P03
-45.93
P04
144.06
P05
48.93
Systèmes de
Gestion de
Bases de
Données
Exemples
Même résultat que l’exemple précédent, en utilisant une sous
requête comme source de données
ecart
P01
132
Exemples
Les produits dont le prix est supérieur à la moyenne
select id, prix
from produit
where prix-(select avg(prix)
from produit) >0;
select id,prix-moyenne as ecart
from (select avg(prix)as moyenne
from produit) pdt,
produit;
id
133
prix
P04
309.49
P05
214.36
134
1
Systèmes de
Gestion de
Bases de
Données
Systèmes de
Gestion de
Bases de
Données
Exemples
Requêtes équivalentes
Exemples
Les produits qui n’apparaissent dans aucune commande
select id from produit
except
select idProduit from ligne;
select id, prix
from produit
where prix > (select avg(prix) from produit);
Attention : il n’est pas possible d’afficher le nom du produit
select id, prix
from produit, (select avg(prix) as moy
from produit) pdt
where prix > moy;
Solution:
select nom
from produit
where id not in (select idProduit from ligne);
135
Systèmes de
Gestion de
Bases de
Données
136
Systèmes de
Gestion de
Bases de
Données
Exemples
Exemples
Pour chaque commande, date et nombre de lignes
select * from personne
where id in
(select idPersonne from commande
where id in
(select idCommande from ligne
where idProduit in
(select id from produit
where idMarque=‘M25’
)
)
);
Solution « standard » :
select idCommande,dateCommande,
count(*) as nbc
from ligne inner join commande
on commande.id=ligne.idCommande
group by idCommande, dateCommande
order by count(*);
137
Systèmes de
Gestion de
Bases de
Données
138
Systèmes de
Gestion de
Bases de
Données
Exemples
Opérateurs ALL et ANY
Solution équivalente avec sous requête :
• Les opérateurs traditionnels de comparaison de valeur
permettent des tests entre deux valeurs simples et de même type.
select idCommande, dateCommande, nbc
• ALL et ANY permettent de comparer une valeur à une liste de
valeurs, cette liste pouvant être retournée par une sous-requête.
from (select idCommande, count(*) as nbc
from ligne group by idCommande) lgn
• Pour vérifier si v1 est supérieur à au moins une valeur:
v1 > ANY (SELECT … FROM …)
inner join commande
on idCommande=commande.id
• Pour vérifier qu’il est supérieur à toutes les valeurs :
v1 > ALL (SELECT … FROM …).
order by nbc;
139
140
2
Systèmes de
Gestion de
Bases de
Données
Systèmes de
Gestion de
Bases de
Données
Equivalences
requête
Le nom du produit le plus cher
requête équivalente
col1 >= any (select
col2 from …)
col1 >= all (select
col2 from …)
col1 <= any (select
col2 from …)
col1 <= all (select
col2 from …)
Exemple
select *
from produit
where prix = (select max(prix) from produit);
col1 >= (select
min(col2) from …)
col1 = (select
max(col2) from …)
col1 <= (select
max(col2) from …)
col1 = (select
min(col2) from …)
select *
from produit
where prix >=all (select prix from produit);
141
Systèmes de
Gestion de
Bases de
Données
142
Systèmes de
Gestion de
Bases de
Données
Sous requête et Having
Sous-requêtes : exercices
1. nom et prix des produits qui n’ont jamais été
commandés ;
2. id et nom des personnes qui n’ont jamais passé de
commande ;
Id du produit le plus commandé
select idProduit
from ligne
Group by idProduit
Having count(*) >=all(select count(*)
from ligne
group by idProduit);
3. id de la commande la plus chère ;
4. id, nom et prix des produits plus chers que la
moyenne ;
5. id des marques qui n’ont jamais été commandées
par la personne dont l’id est « 752 » ;
6. id des commandes avec l’écart entre leur montant et
le montant de la commande la plus chère ;
Attention: dans ce cas on ne peut pas remplacer « >= all » par
« … = select max (..) … »
143
Systèmes de
Gestion de
Bases de
Données
144
Systèmes de
Gestion de
Bases de
Données
Sous-requêtes : exercices
7. Nombre de produits qui n’ont jamais été
commandés ;
8. Pourcentage du chiffre d’affaire pour chaque
marque.
9. Listes des produits triés dans l’ordre: de celui
dont le prix est le plus proche de la moyenne, à
celui dont le prix est le plus éloigné.
10. Id du produit médian. Le produit médian est
celui pour lequel on trouve autant de produits
plus chers que de produits moins chers.
Sous-requêtes corrélatives
•Les sous-requêtes étudiées jusqu’à présent sont indépendantes
de la requête externe à la quelle elles sont intégrées.
•Pour extraire certaines informations, il faut parfois avoir recours
à des sous-requêtes plus élaborées: les sous-requêtes corrélatives.
•On parle de sous-requête corrélative lorsqu’il y a corrélation
entre les données provenant de la requête principale et celles de la
sous-requête.
•La sous-requête ne peut plus s’exécuter indépendamment de la
requête principale.
145
146
3
Systèmes de
Gestion de
Bases de
Données
Systèmes de
Gestion de
Bases de
Données
Sous-requêtes corrélatives
SELECT …
FROM table1
WHERE … (SELECT colonne
FROM table2
WHERE table1.col = table2.col);
• Les tables internes et externes sont reliées par une
condition située dans la sous-requête.
• Il y a « corrélation » entre les sources de données
internes et externes.
Systèmes de
Gestion de
Bases de
Données
Exemple
Le produit le plus cher de chaque marque
select id
from produit p1
where prix = (select max(prix)
from produit p2
where p2.marque = p1.marque)
La condition qui apparaît dans la sous requête fait apparaître
une comparaison entre une colonne de la table « p1 » et une
colonne de la table « p2 ».
147
148
Systèmes de
Gestion de
Bases de
Données
Exemple avec « exists »
Nom et prénom des personnes qui ont passé une commande
Sous-requêtes : exercices
1. Id de la commande la plus récente de chaque personne ;
2. Id et montant de la commande la plus chère de chaque
personne ;
select id
from personne p
where exists (select *
from commande c
where p.id = c.idPersonne)
3. Nom et prénom des personnes qui n’ont jamais passé de
commande ;
4. Nom et prénom des personnes qui ont commandé
plusieurs fois le même produit ;
Le prédicat « exists » renvoie « vrai » si la requête à laquelle
on l’applique a renvoyé au moins une ligne, et renvoie « faux »
sinon.
149
5. Id des produits dont le prix est supérieur au prix moyen
des produits de la même marque ;
6. Pour chaque produit: écart entre son prix et le prix
moyens des produits de la même marque.
150
4