SQL: Requêtes, Programmation et Triggers

1. SQL: Requêtes, Programmation et Triggers Chapitre 5, Sections 5.5—5.9

2. COUNT (*) COUNT ( [DISTINCT] A) SUM ( [DISTINCT] A) AVG ( [DISTINCT] A) MAX (A) MIN (A) Opérateurs d’Agrégat • Ces opérateurs sont une extension très significative de l’algèbre relationnelle. Une seule colonne SELECT COUNT (*) FROM Sailors S SELECT S.sname FROM Sailors S WHERE S.rating= (SELECT MAX(S2.rating) FROM Sailors S2) SELECT AVG (S.age) FROM Sailors S WHERE S.rating=10 SELECT COUNT (DISTINCT S.rating) FROM Sailors S WHERE S.sname=‘Bob’ SELECT AVG ( DISTINCT S.age) FROM Sailors S WHERE S.rating=10

3. Trouver le nom et l’age du (des) navigateur(s) le(s) plus vieux SELECT S.sname, MAX (S.age) FROM Sailors S • La première requête est illégale! (La raison sera examinée plutard.) • La troisième requête est équivalente à la seconde et est permise dans SQL/92, mais n’est pas supportée dans certains systèmes. SELECT S.sname, S.age FROM Sailors S WHERE S.age = (SELECT MAX (S2.age) FROM Sailors S2) SELECT S.sname, S.age FROM Sailors S WHERE (SELECT MAX (S2.age) FROM Sailors S2) = S.age

4. GROUP BY et HAVING • Jusqu’à maintenant, nous avons appliqué des opérateurs d’agrégat à tous les tuples qualifiés. Parfois, nous voulons les appliquer seulement à chacun parmi plusieurs groupes de tuples. Le nombre de groupes varie en fonction d’instances. • Considérez par exemple la requête: Trouver l’âge du plus jeune navigateur de chaque niveau. • En général, nous ne savons pas à l’avance combien de niveaux existent et ce que sont les valeurs de ces niveaux! • Supposez que nous savons que les valeurs des niveaux varient de 1 à 10; nous pouvons écrire 10 requêtes de la forme suivante: SELECT MIN (S.age) FROM Sailors S WHERE S.rating = i Pour i = 1, 2, ... , 10:

5. GROUP BY etHAVING (Suite) SELECT [DISTINCT] target-list FROMrelation-list WHERE qualification GROUP BYgrouping-list HAVING group-qualification • target-list contient (i) une liste d’attributset (ii) les termes avec opérations d’agrégat (p.ex., MIN (S.age)). • La liste d’attributs (i) doit être un sous-ensemble de la liste grouping-list. Intuitivement, chaque tuple de la réponse correspond à un groupe, et un groupe est un ensemble de tuples qui ont la même valeur pour tous les attributs dans grouping-list. • Si un attribut de target-list n’est pas dans grouping-list, plusieurs lignes d’un même groupe peuvent avoir différentes valeurs pour de tels attributs et il sera difficile de choisir les quelles parmi ces valeurs pour inclusion dans la réponse de la requête.

6. GROUP BY etHAVING (Suite) SELECT [DISTINCT] target-list FROMrelation-list WHERE qualification GROUP BYgrouping-list HAVING group-qualification • group-qualification contient une liste d’attributs. • La liste group-qualificationdoit contenir un attribut apparaissant comme argument d’un opérateur d’agrégat dans target-list. Cela garantit que les expressions qui apparaissent dans group-qualification auront une même valeur par groupe. • SQL/99 contient 2 nouvelles fonctions: EVERY et ANY applicables à chaque tuple dans un groupe. (Leur sémantique est claire.)

7. Évaluation Conceptuelle • Le produit Cartésien de relation-list est calculé, les tuples qui ne passent pas la qualification sont éliminés, les attributs `nonnecessaires’ (i.e. non mentionnés dans SELECT, GROUP BY ou HAVING) sont effacés et le reste des tuples est reparti en groupes selon la valeur des attributs dans grouping-list. • group-qualification est enfin appliqué pour éliminer quelques groupes. Les expressions dans group-qualification doivent avoir une seule valeur par groupe! En effet, un attribut de group-qualificationqui n’est pas un argument d’un opérateur d’agrégat apparaît aussi dans grouping-list. (La sémantique des clés primaires est ignorée ici!) • Un tuple réponse est généré par groupe qualifié.

8. Trouver l’age du plus jeune navigateur en age de voter (i.e. âgé d’au moins 18 ans) pour chaque niveau ayant au moins 2 tels navigateurs SELECT S.rating, MIN (S.age) AS mage FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating HAVINGCOUNT (*) > 1 • Seul S.rating et S.age sont mentionnés les clauses SELECT, GROUP BY ou HAVING; d’autres attributs sont `nonnécessaires’. • La 2ème colonne du résultat reste sans nom par défaut. (Utiliser AS pour la nommer.) Réponse

9. Trouver le nombre de réservations faites pour chaque bateau rouge SELECT B.bid, COUNT (*) AS scount FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ GROUP BY B.bid • Grouping utilisant le join de 3 relations. • Qu’obtenons nous si nous enlevons ‘B.color=‘red’ de la clause WHERE et ajoutons une clause HAVING avec cette condition? • Et si nous enlevons la relation Sailors et la condition contenant S.sid?

10. Trouver l’age du plus jeune navigateur âgé de plus de 18 ans pour chaque niveau avec au moins deux navigateurs SELECTS.rating, MIN (S.age) FROM Sailors S WHERE S.age > 18 GROUP BY S.rating HAVING 1 < (SELECT COUNT (*) FROM Sailors S2 WHERES.rating=S2.rating) • Ceci montre que la clause HAVING peut aussi contenir une sousrequête. • Et si nous remplaçons la clause HAVING par: • HAVING COUNT(*) >1

11. Trouver les niveaux pour lesquels l’age moyen est le minimum de tous les niveaux • On ne peut pas imbriquer des opérations d’agrégats! La requête suivante est incorrecte: SELECT S.rating FROM Sailors S WHERE S.age = (SELECT MIN (AVG (S2.age)) FROM Sailors S2) • Solution correcte (en SQL/92): SELECT Temp.rating, Temp.avgage FROM (SELECT S.rating, AVG (S.age) AS avgage FROM Sailors S GROUP BY S.rating) AS Temp WHERE Temp.avgage = (SELECT MIN (Temp.avgage) FROM Temp)

12. Valeurs Nulles • Les valeurs des attributs dans un tuple sont parfois inconnues(p.ex., un niveau non assigné) ou inapplicables(p.ex., aucun nom d’épouse). • SQL a une valeur spéciale null pour de telles situations. • La présence de null complique bien de choses! P.ex.: • Besoin d’operateurs speciaux pour contrôler si une valeur est ou n’est pas nulle. • L’affirmation rating>8 est-elle vraie ou fausse lorsque rating est null? Et la signification de AND, OR et NOT? • Besoin d’une logique à 3 valeurs(vrai, faux et inconnu). • La signification des clauses doit être définie soigneusement. (p.ex., WHERE élimine des lignes n’évaluant pas à vrai.) • Nouveaux opérateurs (p.ex. «outer joins») possibles.

13. Résumé • SQL fut un facteur qui a joué un rôle important dans l’acceptation rapide du modèle relationnel; car il est plus naturel que les premiers langages de requêtes procéduraux. • SQL jouit de la complétude relationnelle; en fait il est même plus expressif que l’algèbre relationnel. • même les requêtes exprimables en algèbre relationnelle peuvent être exprimées de manière plus naturelle en SQL. • Beaucoup de voies alternatives d’écrire une requête existent; un optimisateur devrait chercher le plan d’évaluation le plus performant. • En pratique, les utilisateurs ont besoin d’être conscients de la manière dont les requêtes sont optimisées et évaluées pour de meilleurs résultats. • NULL est utilisé pour les valeurs d’attributs inconnues et comportent beaucoup de complications.