SQL: Requ

1
SQL Requêtes, Programmation et Triggers

• Chapitre 5, Sections 5.55.9

2
Opérateurs dAgrégat
COUNT () COUNT ( DISTINCT A) SUM ( DISTINCT
A) AVG ( DISTINCT A) MAX (A) MIN (A)

• Ces opérateurs sont une extension très
  significative de lalgè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.rating10
SELECT COUNT (DISTINCT S.rating) FROM Sailors
S WHERE S.snameBob
SELECT AVG ( DISTINCT S.age) FROM Sailors
S WHERE S.rating10
3
Trouver le nom et lage 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 nest
  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 dagré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 dinstances.
• 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 à lavance 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 et HAVING (Suite)
SELECT DISTINCT target-list FROM
relation-list WHERE qualification GROUP
BY grouping-list HAVING group-qualification

• target-list contient (i) une liste dattributs
  et (ii) les termes avec opérations dagrégat
  (p.ex., MIN (S.age)).
• La liste dattributs (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 nest pas dans
  grouping-list, plusieurs lignes dun 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 et HAVING (Suite)
SELECT DISTINCT target-list FROM
relation-list WHERE qualification GROUP
BY grouping-list HAVING group-qualification

• group-qualification contient une liste
  dattributs.
• La liste group-qualification doit contenir un
  attribut apparaissant comme argument dun
  opérateur dagré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-qualification
  qui nest pas un argument dun opérateur
  dagré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 lage du plus jeune navigateur en age de
voter (i.e. âgé dau 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
gt 18 GROUP BY S.rating HAVING COUNT () gt 1

• Seul S.rating et S.age sont mentionnés les
  clauses SELECT, GROUP BY ou HAVING dautres
  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.sidR.sid AND
R.bidB.bid AND B.colorred GROUP BY B.bid

• Grouping utilisant le join de 3 relations.
• Quobtenons nous si nous enlevons B.colorred
  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 lage du plus jeune navigateur âgé de
plus de 18 ans pour chaque niveau avec au moins
deux navigateurs
SELECT S.rating, MIN (S.age) FROM Sailors
S WHERE S.age gt 18 GROUP BY S.rating HAVING 1
lt (SELECT COUNT ()
FROM Sailors S2 WHERE
S.ratingS2.rating)

• Ceci montre que la clause HAVING peut aussi
  contenir une sousrequête.
• Et si nous remplaçons la clause HAVING par
• HAVING COUNT() gt1

11
Trouver les niveaux pour lesquels lage moyen est
le minimum de tous les niveaux

• On ne peut pas imbriquer des opérations
  dagré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 doperateurs speciaux pour contrôler si
  une valeur est ou nest pas nulle.
• Laffirmation ratinggt8 est-elle vraie ou fausse
  lorsque rating est null? Et la signification de
  AND, OR et NOT?
• Besoin dune 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 lacceptation 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 lalgè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 dattributs
  inconnues et comportent beaucoup de
  complications.