Requêtes d'interrogation SQL PDF

Requêtes d’interrogation SQL Requêtes imbriquées Agrégat Regroupement 1  Motivation Les requêtes déjà vues ne correspondent qu’à des opérations simples Vers le PL/SQL Requête imbriquée  Parfois la...

Requêtes d’interrogation SQL Requêtes imbriquées Agrégat Regroupement 1  Motivation 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 2 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: SELECT … Requête FROM … externe WHERE [Expression] Opérateur (SELECT … FROM … Requête WHERE …); imbriquée 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. 4  Opérateurs ensemblistes IN  appartenance ensembliste  (A1,…An) IN EXISTS  test d’existence  EXISTS COMPARAISON  comparaison avec chaque élément d’un ensemble  (A1,…An) ALL  opérateur de comparaison (,=,) 5  Expression IN SELECT … FROM … WHERE (A1,…,An)IN (SELECT B1,…,Bn FROM … WHERE …); Sémantique : An)de la requête externe  la condition est vraie si tuple désigné par (A1,…, appartient 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 appartient au résultat de la requête imbriquée, calculezet ou afficher les expressions de projection de la clause 6 SELECT.  Expression NOT IN SELECT … FROM … WHERE (A1,…,An)NOT IN (SELECT B1,…,Bn FROM … WHERE …); 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, calculer et/ou afficher les expressions de projection 7 de la clause SELECT.  Exemple avec IN Emp (Eno, Ename, Title, Project(Pno, Pname, Budget, City) City) Pay(Title, Salary) Works(Eno, Pno, Resp, Dur) Noms des employés qui travaillent Noms des employés qui travaillent dans des villes où il y a des projets dans des villes où il n’y a pas de budget inférieur à 50? de projets de budget inférieur à SELECT Ename 50? FROM Emp SELECT FROM Ename Emp WHERE City IN WHERE City NOT IN (SELECT City (SELECT City FROM Project FROM WHERE Budget < 50); Project WHERE Budget < 8 50);  ALL SELECT … FROM … WHERE (A1,…,An) ALL (SELECT B1, …,Bn FROM … WHERE …); 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 calculer et/ou aficher les expressions de projection de la clause SELECT. 9  Exemple avec ALL Emp (Eno, Ename, Title, Project(Pno, Pname, Budget, City) City) Pay(Title, Salary) Works(Eno, Pno, Resp, Dur) Noms des projets qui ont le plus gros budget ? SELECT DISTINCT Pname FROM Project WHERE Budget >= ALL (SELECT Budget FROM Project) 10  ANY SELECT … FROM … WHERE (A1,…,An) ANY (SELECT B1, …,Bn FROM … WHERE …); 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 calculer et/ou affi cher les expressions de projection de la clause SELECT. 11  Exemple avec ANY Emp (Eno, Ename, Title, Project(Pno, Pname, Budget, City) City) Pay(Title, Salary) Works(Eno, Pno, Resp, Dur) 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 > 50000) Budget 12  EXISTS Les deux requêtes SELECT … sont FROM R1 ,X, … WHERE EXISTS (SELECT B1,…,Bn généralement FROM … corrélées, la WHERE Condition(x) requête imbriquée …); dépend de la valeur de x. EXISTS retourne VRAI ou FAUX Sémantique : La condition EXISTS est vraie si la requête imbriquée n’est pas vide. Algorithme  Pour chaque tuple des tables de la requête externe, extraire et calculez le sous-tuple (A1,…An)  Si le résultat de la requête imbriquée n’est pas vide, alors calculer et/ou aficher les expressions de projection de la clause SELECT. 13  Exemple avec EXISTS Emp (Eno, Ename, Title, Project(Pno, Pname, Budget, City) City) Pay(Title, Salary) Works(Eno, Pno, Resp, Dur) Noms des employés qui travaillent dans une ville où il y a au moins un projet? SELECT e.Ename FROM Emp WHERE * e Project EXISTS WHERE e.City=Project.City) (SELECT Noms des projets quiFROM emploient des ‘Elect Eng’ ? SELECT p.Pname FROM Project p WHERE EXISTS (SELECT * FROM Works w, Emp e WHERE w.Pno=p.Pno and e.Eno=w.Eno 14 and  Exemple avec EXISTS Emp (Eno, Ename, Title, Project(Pno, Pname, Budget, City) City) Pay(Title, Salary) SELECT p.Pname Works(Eno, Pno, Resp, Dur) FROM Project p WHERE EXISTS (SELECT * FROM Works w, Emp e WHERE e.Eno=w.Eno Ande.Title=’Elect.Eng.’) 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. 15  NOT EXISTS Les deux requêtes SELECT … sont FROM R1 x… WHERE NOT EXISTS (SELECT B1,…,Bn généralement FROM … corrélées, la WHERE requête imbriquée dépend de la Condition(x)); valeur de x. NOT EXISTS retourne VRAI ou FAUX Sémantique : La condition NOT EXISTS est vraie si la requête imbriquée est vide. Algorithme Pour chaque tuple des tables de la requête externe, extraire et calculez le sous-tuple (A1,…An) Si le résultat de la requête imbriquée est vide, alors calculer et/ou aficher les expressions de projection de la clause SELECT. 16  Exemple avec NOT EXISTS Emp (Eno, Ename, Title, Project(Pno, Pname, Budget, City) City) Pay(Title, Salary) Works(Eno, Pno, Resp, Dur) Noms des projets qui n’emploient aucun ‘Elect Eng’ ? SELECT p.Pname FROM Project WHERE p (SELECT * NOT FROM Works w, Emp e EXISTS WHERE w.Pno=p.Pno and e.Eno=w.Eno and e.Title=’Elect.Eng.’); 17  Agrégat 18  Fonctions d'agrégation SELECT AggFunc(Ai), …, AggFunc(Aj) FROM R1,..., Rm WHERE conditions; 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. 19  Fonctions d’agrégation 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), Calculent respectivement le AVG(x), SUM(x) minimum, le maximum, la moyenne et la somme des valeurs de l’attribut X. 20  Exécution d’une requête d’agrégation 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 21  Requête d’agrégation Personnes nom prénom salaire Martin Pierre 2500 SELECT sum(salaire) FROM Personnes Dupond Jean 3000 Dupond Marc 4200 Etape 1) Etape 2) Table-Temp = Table-Resultat = SELECT salaire SELECT sum(salaire) FROM Personnes FROM Table-Temp salaire 2500 Sum(salaire) 3000 agrégation 9700 4200 Table- 22 Table- Resultat  Exemples d'agrégation dans la clause SELECT Emp (Eno, Ename, Title, City) Project(Pno, Pname, Budget, City) Pay(Title, Salary) Works(Eno, Pno, Resp, Dur) Budget max des projets de Paris? Nombre de villes où il y a un projet avec l'employé E4? SELECT MAX(Budget) Project FROM SELECT COUNT(DISTINCT WHERE City = ’Paris’; City) FROM Affichage du nombre Project, Works Project.Pno = Works.Pno WHERE d’employés AND Works.Eno = ’E4’; SELECT COUNT(*) FROM Emp; 23  Exemple d'agrégation dans la clause WHERE 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 Project WHERE Budget > (SELECT AVG(Budget) FROM Project); 24  Exemples d'agrégation Emp (Eno, Ename, Title, Project(Pno, Pname, Budget, City) City) Pay(Title, Salary) Works(Eno, Pno, Resp, Dur) Budget max des projets de Paris avec identifiant SELECT ? MAX(Budget) Pno, Impossible de mélanger une FROM Project fonction d’agrégation avec un attribut sans agrégation s’il n’y a WHERE City = ’Paris’; pas de clause GROUP BY ! Budget max des projets de Paris avec identifiant ? SELECT Pno, Budget FROM Project WHERE City = ’Paris’ AND Budget = (SELECT MAX(Budget) FROM Project and City=‘Paris’); 25  Regroupement 26  Requêtes de groupement : GROUP BY SELECT A1, …, AggFunc(An+1), …, AggFunc(An+p) FROM An, R1, …, WHERE Rm GROUP … BY A1 …, An Sémantique  Partitionne les tuples résultats en fonction des valeurs de certains attributs Algorithme 1. Calcul de la requête brute (sans regroupement ni agrégation) 2. Regroupement horizontal des lignes en sous-tables possédant les mêmes valeurs de regroupement Aj …, Ak 3. Pour chaque sous-table, calcul des valeurs agrégées pour les attributs 27 non  GROUP BY SELECT A1, B1, sum(A2) FROM R1, R2 WHERE A1 < 3 GROUP BY A1, B1 R1 A1 A2 2 6 A1 A2 B1 A1 A2 B1 A1 B1 A2* 1 1 2 6 a 2 6 a 1 a 1 1 1 a where 1 1 a group by 2 A1 B1 sum(A2) 2 8 select 1 a 3 3 3 from 2 8 a 2 8 a 2 a 6 3 3 a 1 2 a 8 2 a 14 1 2 3 3 1 2 a 3 3 a R2 B1 a 28  GROUP BY Règles de regroupement  Tous les attributs Ai, …, An dans la clause SELECT qui ne sont pas impliqués dans la clause GROUP BY doivent être inclus dans une opération d'agrégation SELECT Pno, count(Eno) FROM Works GROUP BY Pno; Sélection des tuples  La clause WHERE permet de sélectionner les tuples appartenant aux sous- tables. La condition est évaluée AVANT le regroupement. SELECT Pno, 29  Exemples de groupement Emp (Eno, Ename, Title, Project(Pno, Pname, Budget, City) City) Numéros desSalary) Pay(Title, Works(Eno, projets avec le nombre Pno, Resp, d’employés Dur) par impliqués projet ? SELECT Pno, Count(Eno) FROM Works GROUP BY Pno; Noms des villes avec la durée moyenne et la durée maximale de participation de tout les employé par ville et par nom de projet ? SELECT Pname, AVG(Dur), FROM MAX(Dur) Works, Project WHERE Works.Pno=Project.Pno GROUP BY City, Pname; 30  Exemple de groupement ordonné Classement des produits par la valeur totale vendue pour produits les SELECT DISTINCT produit.nom, SUM(vente.qt produit.prix) * AS total FROM produit, vente WHERE produit.id = GROUP vente.produit_idx BY ORDER produit.nom BY total; Le tri des résultats se fait APRES le regroupement et l’agrégation. 31  Sélection sur des groupes SELECT Ai, …, An, [AggFunc(Ai), …, AggFunc(Aj), …] FROM R1, …, Rm WHERE … GROUP BY Aj …, Ak HAVING condition Sémantique  Sélectionne les groupes (partitions) qui satisfont une certaine condition. Algorithme 1. Sélectionne tous les tuples qui satisfont les conditions de la clause WHERE. 2. Forme les groupes qui partagent les mêmes valeurs de la clause GROUP BY. 3. Supprime tous les groupes qui ne satisfont pas les conditions de la clause HAVING 4. Applique les fonctions d’agrégation pour chaque groupe. 5. Retourne les valeurs des colonnes et des agrégation de la clause SELECT. 32  Sélection sur des groupes Règle : La condition de la clause HAVING porte  Soit sur des valeurs atomiques retournées par un opérateur d'agrégation sur les attributs qui n’apparaissent pas dans le GROUP BY  Soit sur des opérations d’agrégation sur des attributs qui apparaissent dans le GROUP BY Exemple SELECT DISTINCT produit.nom, SUM(vente.qt*produit.prix) AS "total FROM produit", vente, produit WHERE produit.id = vente.produit_idx and nom like’a GROUP %’ HAVINGBY produit.nom MIN(vente.qt)>1; 33  Exemples de groupement avec sélection Emp (Eno, Ename, Title, Project(Pno, Pname, Budget, City) City) Pay(Title, Salary) Works(Eno, Pno, Resp, Dur) Villes dans lesquelles habitent plus de 2 employés? SELECT City FROM Emp GROUP BY City HAVING COUNT(ENO) > 2; Projets dans lesquels SELECT 2 DISTINCT Pno FROM Works where resp=‘Oui’ employés ou plus GROUP BY Pno, Resp partagent une responsabilité? HAVING COUNT(DISTINCT ENO) >= 2; 34  Exemples de groupement avec sélection Afficher Les modèles de voitures qui proposent un choix de plus de 10 couleurs. Voiture ( id_voiture, model, couleur,…) SELECT DISTINCT model FROM voiture GROUP BY model Est-ce Correcte ?! HAVING COUNT(couleur) > 10; Classement des produits par la valeur totale vendue pour les produits commençant par la lettre ‘M’. Produit( id, nom, prix,…) vente( id_vente, produit_idx*, qt, …,…) SELECT DISTINCT produit.nom, SUM(vente.qt * produit.prix) AS total FROM produit, vente WHERE produit.id=vente.produit_idx And produit.nom like ’M%’ GROUP BY produit.nom , produit_idx ORDER BY total; 35