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