Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

• Requêtes imbriquées • Agrégat • Regroupement Requêtes d’interrogation SQL 1 M

• Requêtes imbriquées • Agrégat • Regroupement Requêtes d’interrogation SQL 1 Motivation 2 • Les requêtes déjà vues ne correspondent qu’à des opérations simples • Vers le PL/SQL • Requête imbriquée Parfois la valeur d’un test doit être calculé comme le résultat d’une autre requête • Agrégat Opération destinée à agréger plusieurs valeurs de tuples en une seule valeur (en les additionnant, en calculant leur moyenne, etc.) • Regroupement Décomposition d’une table en plusieurs sous-tables sur lesquels des opérations seront effectuées sous-table par sous-table (des agrégats par Requêtes imbriquées 3 SQL : Requêtes imbriquées • Requête imbriquée dans la clause WHERE d'une requête externe: • Opérations ensemblistes Le résultat d’une requête (imbriquée) est une table, c’est-à-dire un ensemble de tuples. Les opérations possibles sont donc ensemblistes. SELECT … FROM … WHERE [Expression] Opérateur (SELECT … FROM … WHERE …); Requête imbriquée Requête externe 4 Opérateurs ensemblistes • IN appartenance ensembliste (A1,…An) IN <sous-req> • EXISTS test d’existence  EXISTS <sous-req> • COMPARAISON comparaison avec chaque élément d’un ensemble  (A1,…An) ALL <sous-req>  opérateur 5 de comparaison (<,>,<=,>=,<>) Expression IN 6 • Sémantique : la condition est vraie si tuple désigné par (A1,…, appartient au résultat de la requête interne. • Algorithme An)de la requête externe Pour chaque tuple des tables de la requête externe, extraire et calculez le sous-tuple (A1,…An) Si le tuple obtenu appartient au résultat de la requête imbriquée, calculez les expressions de projection de la clause SELECT. SELECT … FROM … WHERE (A1,…,An)IN (SELECT B1,…,Bn FROM … WHERE …); Expression NOT IN 7 • Sémantique : la condition est vraie si tuple désigné par (A1,…, An)de la requête externe n’appartient pas au résultat de la requête interne. • Algorithme Pour chaque tuple des tables de la requête externe, extraire et calculez le sous-tuple (A1,…An) Si le tuple obtenu n’appartient pas au résultat de la requête imbriquée, calculez les expressions de projection de la clause SELECT. SELECT … FROM … WHERE (A1,…,An)NOT IN (SELECT B1,…,Bn FROM … WHERE …); Exemple avec IN 8 • Noms des employés qui travaillent dans des villes où il y a des projets de budget inférieur à 50? SELECT Ename FROM WHERE Emp City IN (SELECT City FROM Project WHERE Budget < 50); Emp (Eno, Ename, Title, City) Pay(Title, Salary) Project(Pno, Pname, Budget, City) Works(Eno, Pno, Resp, Dur) • Noms des employés qui travaillent dans des villes où il n’y a pas de projets de budget inférieur à 50? SELECT Ename FROM Emp WHERE City NOT IN (SELECT City FROM Project WHERE Budget < 50); ALL • est une comparaison : • Sémantique {<, <=, >, >=, <>} ALL ( ) : la condition est alors vraie si la comparaison est vraie pour tous les tuples résultats de la requête interne. • Algorithme Pour chaque tuple des tables de la requête externe, extraire et calculez le sous-tuple (A1,…An) Si la comparaison de (A1,..,An) est vraie avec tous les tuples de la requête imbriquée, alors calculez les expressions de projection de la clause SELECT. SELECT … FROM … WHERE (A1,…,An) ALL (SELECT B1, …,Bn FROM … WHERE …); 9 Exemple avec ALL • Noms des projets qui ont le plus gros budget ? SELECT DISTINCT Pname FROM Project WHERE Budget >= ALL (SELECT Budget FROM Project) Emp (Eno, Ename, Title, City) Pay(Title, Salary) Project(Pno, Pname, Budget, City) Works(Eno, Pno, Resp, Dur) 10 ANY • est une comparaison : • Sémantique {<, <=, >, >=, <>} ANY: la condition est alors vraie si la comparaison est vraie avec au moins un tuple résultats de la requête interne. • Algorithme Pour chaque tuple des tables de la requête externe, extraire et calculez le sous-tuple (A1,…An) Si la comparaison de (A1,..,An) est vraie avec au moins un tuple de la requête imbriquée, alors calculez les expressions de projection de la clause SELECT. SELECT … FROM … WHERE (A1,…,An) ANY (SELECT B1, …,Bn FROM … WHERE …); 11 Exemple avec ANY 12 • Noms des villes qui ont au moins un projet de budget supérieur à 50000 ? SELECT DISTINCT City FROM Project WHERE Budget = ANY (SELECT Budget FROM Project WHERE Budget > 50000) Emp (Eno, Ename, Title, City) Pay(Title, Salary) Project(Pno, Pname, Budget, City) Works(Eno, Pno, Resp, Dur) EXISTS • EXISTS retourne VRAI ou FAUX • Sémantique : La condition EXISTS est vraie si la requête imbriquée n’est pas vide. • Algorithme 1. Pour chaque tuple des tables de la requête externe, extraire et calculez le sous-tuple (A1,…An) 2. Si le résultat de la requête imbriquée n’est pas vide, alors calculez les expressions de projection de la clause SELECT. SELECT … FROM R1 x… WHERE EXISTS (SELECT B1,…,Bn FROM … WHERE Condition(x) …); Les deux requêtes 13 sont généralement corrélées, la requête imbriquée dépend de la valeur de x. Exemple avec EXISTS 14 • Noms des employés qui travaillent dans une ville où il y a au moins un projet? SELECT FROM WHERE e.Ename Emp e EXISTS (SELECT FROM * Project WHERE e.City=Project.City) • Noms des projets qui emploient des ‘Elect Eng’ ? SELECT FROM WHERE p.Pname Project p EXISTS (SELECT * FROM Works w, Emp e WHERE w.Pno=p.Pno and e.Eno=w.Eno and Emp (Eno, Ename, Title, City) Pay(Title, Salary) Project(Pno, Pname, Budget, City) Works(Eno, Pno, Resp, Dur) Exemple avec EXISTS La requête imbriquée ne dépend pas de p : elle retourne l’ensemble des employés de titre ‘Elect. Eng.’, indépendamment du projet sélectionné dans la requête externe. La clause EXISTS retourne donc toujours vraie (on suppose qu’il existe un tel employé) et la requête externe retourne tous les noms de projets. Emp (Eno, Ename, Title, City) Pay(Title, Salary) Project(Pno, Pname, Budget, City) Works(Eno, Pno, Resp, Dur) 15 SELECT p.Pname FROM Project p WHERE EXISTS (SELECT * FROM Works w, Emp e WHERE e.Eno=w.Eno Ande.Title=’Elect.Eng.’) NOT EXISTS • NOT EXISTS retourne VRAI ou FAUX • Sémantique : La condition NOT EXISTS est vraie si la requête imbriquée est vide. • Algorithme 1. Pour chaque tuple des tables de la requête externe, extraire et calculez le sous-tuple (A1,…An) 2. Si le résultat de la requête imbriquée est vide, alors calculez les expressions de projection de la clause SELECT. SELECT … FROM R1 x… WHERE NOT EXISTS (SELECT B1,…,Bn FROM … WHERE Condition(x)); Les deux requêtes 16 sont généralement corrélées, la requête imbriquée dépend de la valeur de x. Exemple avec NOT EXISTS 17 • Noms des projets qui n’emploient aucun ‘Elect Eng’ ? SELECT FROM WHERE p.Pname Project p NOT EXISTS (SELECT * FROM Works w, Emp e WHERE w.Pno=p.Pno and e.Eno=w.Eno and e.Title=’Elect.Eng.’); Emp (Eno, Ename, Title, City) Pay(Title, Salary) Project(Pno, Pname, Budget, City) Works(Eno, Pno, Resp, Dur) Agrégat 18 Fonctions d'agrégation • Une fonction d’agrégation permet de calculer une seule valeur numérique à partir des valeurs de plusieurs tuples pour un attribut donné  Exemple: la somme des budgets des projets • Une fonction d’agrégation prend un attribut en paramètre • On utilise une fonction d’agrégation  dans la clause SELECT pour effectuer un calcul post-projection  dans la clause WHERE pour remplacer une valeur par un calcul dans une condition. Ce calcul est le résultat de l’agrégation d’une requête imbriquée. SELECT FROM WHERE 19 AggFunc(Ai), …, AggFunc(Aj) R1, ..., Rm conditions; Fonctions d’agrégation 20 Fonction Description COUNT([DISTINCT]x,y,…) Décompte des tuples du résultat par projection sur le ou les attributs spécifiés (ou tous avec ‘*’). L’option DISTINCT élimine les doublons. MIN(x), MAX(x), AVG(x), SUM(x) Calculent respectivement le minimum, le maximum, la moyenne et la somme des valeurs de l’attribut X. Exécution d’une requête d’agrégation 21 1. La requête est exécutée classiquement et retourne une table résultat temporaire dont les colonnes sont les attributs Ai, …, Aj utilisées dans les fonctions d’agrégation 2. Les fonctions d’agrégation sont appliquées sur les colonnes de la table résultat 3. Le résultat de la requête est une table  dont les colonnes sont les noms des expressions de la clause SELECT  contenant un seul tuple Requête d’agrégation Personnes SELECT sum(salaire) FROM Personnes Sum(salaire) 9700 Etape 1) Table-Temp = SELECT salaire FROM Personnes Table- Temp Etape 2) Table-Resultat = SELECT sum(salaire) FROM Table-Temp agrégation Table- Resultat 22 nom prénom salaire Martin Pierre 2500 Dupond Jean 3000 Dupond Marc 4200 salaire 2500 3000 4200 Exemples d'agrégation dans la clause SELECT 23 • Budget max des projets de Paris? SELECT FROM WHERE MAX(Budget) Project City = ’Paris’; • Affichage du nombre d’employés SELECT COUNT(*) FROM Emp; Emp (Eno, Ename, Title, City) Project(Pno, Pname, Budget, City) Pay(Title, Salary) Works(Eno, Pno, Resp, Dur) • Nombre de villes où il y a un projet avec l'employé E4? SELECT FROM WHERE AND COUNT(DISTINCT City) Project, Works Project.Pno = Works.Pno Works.Eno = ’E4’; Exemple d'agrégation dans la clause WHERE 24 Emp (Eno, Ename, Title, City) Project(Pno, Pname, Budget, City) Pay(Title, Salary) Works(Eno, Pno, Resp, Dur) • Noms des projets dont le budget est supérieur au budget moyen? SELECT Pname FROM Projec t WHERE Budget > (SELECT AVG(Budget) FROM Project); Exemples d'agrégation • Budget max des projets de Paris avec identifiant ? SELECT Pno, MAX(Budget) FROM Project WHERE City uploads/s3/ bd-avance-chap3.pdf