1

SQL: Requêtes, Programmation et Triggers

Chapitre 5, Sections 5.5—5.9

2

Opérateurs d’Agrégat Ces opérateurs sont une

extension très significative de l’algèbre relationnelle.

COUNT (*)COUNT ( [DISTINCT] A)SUM ( [DISTINCT] A)AVG ( [DISTINCT] A)MAX (A)MIN (A)

SELECT AVG (S.age)FROM Sailors SWHERE S.rating=10

SELECT COUNT (*)FROM Sailors S

SELECT AVG ( DISTINCT S.age)FROM Sailors SWHERE S.rating=10

SELECT S.snameFROM Sailors SWHERE S.rating= (SELECT MAX(S2.rating) FROM Sailors S2)

Une seule colonne

SELECT COUNT (DISTINCT S.rating)FROM Sailors SWHERE S.sname=‘Bob’

3

Trouver le nom et l’age du (des) navigateur(s) le(s) plus vieux

La première requête est illégale!

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, MAX (S.age)FROM Sailors S

SELECT S.sname, S.ageFROM Sailors SWHERE S.age = (SELECT MAX (S2.age) FROM Sailors S2)

SELECT S.sname, S.ageFROM Sailors SWHERE (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 SWHERE S.rating = i

Pour i = 1, 2, ... , 10:

5

GROUP BY et HAVING (Suite)

target-list contient (i) une liste d’attributs et (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.

SELECT [DISTINCT] target-listFROM relation-listWHERE qualificationGROUP BY grouping-listHAVING group-qualification

6

GROUP BY et HAVING (Suite)

group-qualification contient une liste d’attributs. La liste group-qualification doit 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.)

SELECT [DISTINCT] target-listFROM relation-listWHERE qualificationGROUP BY grouping-listHAVING group-qualification

7

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

Seul S.rating et S.age sont mentionnés dans 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.)

SELECT S.rating, MIN (S.age) AS mageFROM Sailors SWHERE S.age >= 18GROUP BY S.ratingHAVING COUNT (*) > 1

sid sname rating age22 dustin 7 45.031 lubber 8 55.571 zorba 10 16.064 horatio 7 35.029 brutus 1 33.058 rusty 10 35.0

rating age1 33.07 45.07 35.08 55.510 35.0

rating mage 7 35.0

Réponse

8

É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-qualification qui 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é.

9

Trouver le nombre de réservations faites pour chaque bateau rouge

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?

SELECT B.bid, COUNT (*) AS scountFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’GROUP BY B.bid

10

Trouver l’age du plus jeune navigateur âgé de plus de 18 ans pour chaque niveau avec au moins deux navigateurs

Ceci montre que la clause HAVING peut aussi contenir une sousrequête.

Et si nous remplaçons la clause HAVING par: HAVING COUNT(*) >1

SELECT S.rating, MIN (S.age)FROM Sailors SWHERE S.age > 18GROUP BY S.ratingHAVING 1 < (SELECT COUNT (*) FROM Sailors S2 WHERE S.rating=S2.rating)

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.ratingFROM Sailors SWHERE S.age = (SELECT MIN (AVG (S2.age)) FROM Sailors S2)

SELECT Temp.rating, Temp.avgageFROM (SELECT S.rating, AVG (S.age) AS avgage FROM Sailors S GROUP BY S.rating) AS TempWHERE Temp.avgage = (SELECT MIN (Temp.avgage) FROM Temp)

Solution correcte (en SQL/92):

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.