cours1_7 sous requetes

1
Systèmes de
Gestion de
Bases de
Données
129
Requêtes et
sous requêtes
Systèmes de
Gestion de
Bases de
Données
130
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.
Sous requêtes
Systèmes de
Gestion de
Bases de
Données
131
Cas 1: sous requêtes pour l’affichage de données
select …(select … from … where)
from … where …
Sous requêtes
select … from (select … from … where)
where …
select …from …
where (select … from … where)
Cas 2: sous requêtes comme source de données
Cas 3: sous requêtes comme condition de sélection
Systèmes de
Gestion de
Bases de
Données
132
Ecart entre le prix de chaque produit et le prix moyen de
l’ensemble
select id, prix-(select avg(prix)
from produit) as ecart
from produit;
Exemples
48.93P05
144.06P04
-45.93P03
-135.48P02
-11.58P01
ecartid
Systèmes de
Gestion de
Bases de
Données
133
Même résultat que l’exemple précédent, en utilisant une sous
requête comme source de données
select id,prix-moyenne as ecart
from (select avg(prix)as moyenne
from produit) pdt,
produit;
Exemples
Systèmes de
Gestion de
Bases de
Données
134
Les produits dont le prix est supérieur à la moyenne
select id, prix
from produit
where prix-(select avg(prix)
from produit) >0;
Exemples
214.36P05
309.49P04
prixid

2
Systèmes de
Gestion de
Bases de
Données
135
Requêtes équivalentes
select id, prix
from produit
where prix > (select avg(prix) from produit);
Exemples
select id, prix
from produit, (select avg(prix) as moy
from produit) pdt
where prix > moy;
Systèmes de
Gestion de
Bases de
Données
136
Les produits qui n’apparaissent dans aucune commande
select nom
from produit
where id not in (select idProduit from ligne);
Exemples
select id from produit
except
select idProduit from ligne;
Attention : il n’est pas possible d’afficher le nom du produit
Solution:
Systèmes de
Gestion de
Bases de
Données
137
Exemples
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’
)
)
);
Systèmes de
Gestion de
Bases de
Données
138
Pour chaque commande, date et nombre de lignes
Exemples
select idCommande,dateCommande,
count(*) as nbc
from ligne inner join commande
on commande.id=ligne.idCommande
group by idCommande, dateCommande
order by count(*);
Solution « standard » :
Systèmes de
Gestion de
Bases de
Données
139
Exemples
select idCommande, dateCommande, nbc
from (select idCommande, count(*) as nbc
from ligne group by idCommande) lgn
inner join commande
on idCommande=commande.id
order by nbc;
Solution équivalente avec sous requête :
Systèmes de
Gestion de
Bases de
Données
140
• Les opérateurs traditionnels de comparaison de valeur
permettent des tests entre deux valeurs simples et de même type.
•ALL et ANY permettent de comparer une valeur à une liste de
valeurs, cette liste pouvant être retournée par une sous-requête.
• Pour vérifier si v1 est supérieur à au moins une valeur:
v1 > ANY (SELECT … FROM …)
• Pour vérifier qu’il est supérieur à toutes les valeurs :
v1 > ALL (SELECT … FROM …).
Opérateurs ALL et ANY

3
Systèmes de
Gestion de
Bases de
Données
141
Equivalences
col1 =(select
min(col2) from …)
col1 <= all (select
col2 from …)
col1 <=(select
max(col2) from …)
col1 <= any (select
col2 from …)
col1 =(select
max(col2) from …)
col1 >= all (select
col2 from …)
col1 >= (select
min(col2) from …)
col1 >= any (select
col2 from …)
requête équivalenterequête
Systèmes de
Gestion de
Bases de
Données
142
select *
from produit
where prix = (select max(prix) from produit);
Le nom du produit le plus cher
Exemple
select *
from produit
where prix >=all (select prix from produit);
Systèmes de
Gestion de
Bases de
Données
143
Id du produit le plus commandé
Sous requête et Having
select idProduit
from ligne
Group by idProduit
Having count(*) >=all(select count(*)
from ligne
group by idProduit);
Attention: dans ce cas on ne peut pas remplacer « >= all » par
« … = select max (..) … »
Systèmes de
Gestion de
Bases de
Données
144
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 ;
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 ;
Sous-requêtes : exercices
Systèmes de
Gestion de
Bases de
Données
145
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 : exercices
Systèmes de
Gestion de
Bases de
Données
146
•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.
Sous-requêtes corrélatives

4
Systèmes de
Gestion de
Bases de
Données
147
• 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.
SELECT …
FROM table1
WHERE … (SELECT colonne
FROM table2
WHERE table1.col = table2.col);
Sous-requêtes corrélatives
Systèmes de
Gestion de
Bases de
Données
148
Le produit le plus cher de chaque marque
Exemple
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 ».
Systèmes de
Gestion de
Bases de
Données
149
Nom et prénom des personnes qui ont passé une commande
Exemple avec « exists »
select id
from personne p
where exists (select *
from commande c
where p.id = c.idPersonne)
Le prédicat « exists » renvoie « vrai » si la requête à laquelle
on l’applique a renvoyé au moins une ligne, et renvoie « faux »
sinon.
Systèmes de
Gestion de
Bases de
Données
150
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 ;
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 ;
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.
Sous-requêtes : exercices