SQL : Un Langage Relationnel

1
SQL Un Langage Relationnel

• Witold LITWIN

2
Langage de base de données(Database Language)

• Un sous-langage de programmation
• Consiste traditionnellement de deux parties
• langage de définition de données
• langage de manipulation de données
• langage interactif (de requêtes)
• langage imbriqué (embedded)
• En pratique, les deux parties sont imbriquées
• définition de vues et des attributs hérités en
  général

3
SQL

• Inventé à IBM San Jose, 1974 (Boyce Chamberlin)
  pour System R
• Basé sur le calcul de tuple algèbre
  relationnelle
• relationnellement complet (et plus)
• Le langage de SGBD relationnels
• En évolution contrôlée par ANSI (SQL1, 2, 3...)
• Il existe aussi plusieurs dialectes
• Les possibilités basiques sont simples
• Celles avancées peuvent être fort complexes
• Signalées dans ce qui suit par

4
SQL Définition de Données

• CREATE TABLE CREATE VIEW CREATE INDEX
• ALTER TABLE
• DROP TABLE DROP VIEW DROP INDEX

5
SQL-2 et dialectes SGBD-Serveurs DB2,
SQL-Server, Sybase..

• CREATE (ALTER, DROP) DOMAIN
• CREATE (ALTER, DROP) SCHEMA
• une partie nommée dune base
• donne une autonomie de nommage
• une table T dans le schéma S1 et une table T dans
  le schéma S2 sont deux tables différentes
• nommées S1.T et S2.T dans la base
• peut être considérée une base logique
• CREATE (DROP) DATABASE
• clause hors standard SQL-2
• CREATE (DROP) ALIAS, TRIGGER, FUNCTION,
  PROCEDURE...

6
CREATE TABLE(clauses essentielles)

• Définit la table réelle (de base)
• CREATE TABLE table
• (column ,column...
• , primary key
• column name type NOT NULL
• type INTEGER, CHAR (n), GRAPHIC, ICON, DATE,
  TIME, TIMESTAMP

7
EXAMPLE

• CREATE TABLE S(S CHAR (5) NOT NULL,SNAME
  CHAR (20),STATUS INT,CITY CHAR (15),
• PRIMARY KEY (S) )

8
CREATE TABLEClause CONSTRAINT

• Clauses CONSTRAINT sont nouvelles dans SQL
• Permettent de définir
• les clés étrangères FOREIGN KEY
• les contraintes d'intégrité CHECK
• sur un attribut
• inter-attribut dune table
• Autres
• La puissance expressive varie entre les dialectes
• le standard est le plus puissant
• notamment permet tout SELECT dans CHECK
• les dialectes ne permettent que
• aucun CHECK (MsAccess)
• contrainte sur les valeurs dun même tuple (DB-2)
• une par attribut (SQL-Server, DB2)
• pas de sous-requêtes (SQL-Server)

9
CREATE TABLE(multibase)

• On peut créer une table dans une autre base que
  celle courante (ouverte)
• SQL Server, SQL (seulement) de MsAccess, SQL-2
• CREATE TABLE AUTRE-BASE.S(S CHAR (5) NOT
  NULL,SNAME CHAR (20),STATUS INT,CITY CHAR
  (15),
• PRIMARY KEY (S) )

Autre-Base
Base courante
10
SQL-2 Domaines

• Une version limitée du concept du domaine
  relationnel.
• surtout au niveau de types de données
• Une source commune pour la définition d'attributs
• Supporte les contraintes d'intégrité
• Pas une découverte
• le SGBD MRDS (Multics) supportait les domaines il
  y a déjà 15 ans

11
Les domaines SQL-2

• CREATE DOMAIN nom AS type CONSTRAINT def
  DEFAULT VALUE value
• Exemple
• CREATE DOMAIN S_DOM AS CHAR (4)
• On peut alors déclarer
• CREATE TABLE S (S S_DOM...)
• CREATE TABLE SP (S S_DOM...)
• Les deux attributs S ont la définition
• S CHAR (4)

S
S.S
SP.S
12
Domaines avec contraintes

• CREATE DOMAIN CITY AS CHAR (15)
• CONSTRAINT VALID_CITIES
• CHECK (VALUE IN ('???', 'Athens', 'Paris',
  London'))
• Les valeurs légales sont les quatre ??et NULL !
• à moins d'ajouter la clause
• AND VALUE IS NOT NULL
• Travail bâclé au niveau du standard !

13
Domaines avec contraintes

• Clause VALUE peut être aussi complexe qu'une
  expression SELECT de SQL
• Notamment, on peut référencer les valeurs qui
  changent dans le temps
• cette possibilité fait hurler certains (C.J.
  DATE)
• On peut ajouter la clause définissant une valeur
  par défaut dans le domaine
• DEFAULT VALUE ('???')
• DEFAULT VALUE CURRENT-TIME
• DEFAULT VALUE CURRENT-DATE
• Cette valeur devient celle par défaut de tout
  attribut basé sur ce domaine.

14
Domaines avec contraintes

• CREATE DOMAIN INT2 AS INTEGERCONSTRAINT
  TWO_BYTES CHECK (VALUE gt -32768 AND VALUE lt
  32767)
• CREATE TABLE T ... CREATE DOMAIN D CHECK
  (VALUE IN (SELECT C FROM T))
• CREATE DOMAIN INT1 AS INT2CONSTRAINT ONE_BYTE
  CHECK (VALUE gt -128AND VALUE lt 127)

Légal dans SQL2Peut être mise à jour
Illegal dans SQL2 (pas de sous-types)
Pour sur les domaines voir un livre sur SQL-2
15
ALTER DROP TABLE

• ALTER TABLE S ADD DISCOUNT SMALLINT
• certains systèmes
• ALTER TABLE S DROP DISCOUNT SMALLINT
• ALTER TABLE S RENAME SNAME NAME
• .....
• DROP TABLE P

16
Indexes

• CREATE UNIQUE INDEX index
• ON table ( column order , column...)
• CLUSTER
• CREATE UNIQUE INDEX XS ON S (S)
• CREATE UNIQUE INDEX XSP ON SP (S ASC, P DESC)
• UNIQUE pas de duplicata de valeurs indexées
• indexes uniques obligatoires pour les clés dans
  le DB2

17
Option CLUSTER
Page 2 de C

• CREATE INDEX C ON S (CITY) CLUSTER
• Même ordre physique
• des entrées de l'indexe CITY
• des tuples de S dans leurs pages

2 Aberdeen 3, Athens 3 London 3, Milan 4, Moscou
4, Paris 5, Rome 8....
Page 3 de S
3 1 Smith Aberdeen 100 ... 1 McLeod Aberdeen 100
... 2 Selis Athens 150 3 Thacher London 3...
18
Indexes

• Définition des indexes ne devait pas être à ce
  niveau de SQL (c'est le schéma interne)
• En principe, une table peut avoir un nombre
  quelconque d'indexes
• Les indexes accélèrent les recherches
• Mais, les indexes pénalisent les mises à jour !
• Pourquoi ?

19
Un dialecte de SQLSQL-MsAccess

• Le dialecte le plus répandu aujourd'hui
• Définition de données est considérablement plus
  élaborée que dans le SQL Standard
• Certaines options du standard sont toutefois
• sous restriction
• s'expriment sous mots-clés différents
• voir MsAccess Aide
• pas toujours nécessaires

20
Example Table P de S-P

• Attention Type Counter -gt Autonumber in
  MsAccess-97

21
MsAccess Types de Données

• Text
• limité par défaut à 50 caractères
• clause FIELD SIZE permet 256 caractères
• supporte les prédicats SQL QBE
• Memo
• taille lt 64K caractères
• supporte seulement la sélection SQL QBE
• Date/Time
• supporte l arithmétique de dates/temps
• 21/3 - 21/2 28
• 21/4 - 21/3 31 ?
• prévu pour 21-ème siècle (Access97)
• 1/1/00 à 31/12/29 signifie 1/1/2000 à
  31/12/2029
• 1/1/30 à 31/12/99 signifie 1/1/1930 à 31/12/1999
• Autonumber
• compteur automatique ou OID (option random dans
  NewValues)

22
MsAccess Types de Données

• Hyperlink
• comme son l indique
• nom symbolique lt 2048 octets
• URL ou UNClt 2048 octets
• sous-adresse (dans le fichier ou la page)
• Cajun Delightshttp//www.cajundelights.comPrice
• supporte seulement la sélection SQL QBE
• OLE objet
• tout objet Windows
• multimédia ou programme
• peut être copié ou référencé
• il faut double-cliquer sur sa description
  textuelle dans le tuple pour le voir

23
MsAccess Champ Number Currency clause Field
size

• Byte 0 à 255
• Integer -32,768 à 32,767, 2 octets.
• Long Integer -2,147,483,648 à 2,147,483,647. 4
  octets.
• Single Six digits de precision -3.402823E38 à
  3.402823E38. 4 octets.
• Double (Default) 10 digits de precision
  1.79769313486232E308 à 1.79769313486232E308. 8
  octets.
• Replication ID
• Pour les bases dupliquées - 16 octets
• un OID
• peut être aussi dans le type Autonumber

24
MsAccess Champ Yes/No

• A utiliser comme son nom l indique
• Yes/No ou On/Off ou True/False
• fixé par le champ Format
• visualisé par défaut par Check-box
• mais, il y a d autres possibilités
• taille 1 octet

25
MsAccess domaines

• On peut les simuler (en QBE) par
• une table D source de valeurs
• table de la base ou une liste de valeurs
• une zone de texte ou zone de texte modifiable
  (combo-box) sur lattribut A à valeurs dans D
• déclaré dans la définition de A (partie Liste de
  choix /Lookup)
• une requête déclarée dans la définition de A
  (dans   contenu / row source )

26
MsAccess surprises

• Seules les valeurs apparaissant dans la 1-ère
  colonne du box et donc dans D peuvent être dans A
• Même si lon indique une autre  colonne liée 
• Type de données Assistant Liste de choix / Lookup
  Wizard réalise cette manipulation
• Drôle de type de données
• Attention aux bugs de cet assistant
• Aussi à l option  Limiter à la liste / Limit to
  List 
• On peut la faire aussi sans cet assistant (et
  mieux)

27
MsAccess surprises

• La table peut hériter lattribut A si lon
  déclare
• Lattribut héritant à le même nom que le 1èr
  attribut de D déclaré dans SELECT
• Lattribut A est le 2-ème dans SELECT
• La 1-ère longueur de colonne 0cm
• ? Dans notre exemple DB, SP peut ainsi hériter
  SNAME
• ? Quarrive til aux tuples existants si lon
  sélectionne une autre colonne liée
• Par ex. on utilise dabord pour saisir les
  valeurs de P.CITY la requête
• SELECT S.City, S.SName FROM S
• Puis, on la remplace par
• SELECT S.Sname, S.City FROM S

28
MsAccess CREATE TABLECONSTRAINT INDEX

• CREATE TABLE Friends (First Name TEXT, Last
  Name TEXT)
• CREATE TABLE Friends1 (First Name TEXT, Last
  Name TEXT, Date of Birth DATETIME, CONSTRAINT
  MyTableConstraint UNIQUE (First Name, Last
  Name, Date of Birth))
• CREATE TABLE Friends2 (First Name TEXT, Last
  Name TEXT, SSN INTEGER CONSTRAINT
  MyFieldConstraint PRIMARY KEY)
• SSN est la clé primaire.
• On peut créer une table sans clé primaire
• alors elle accepte des duplicata
• contrairement à la théorie du relationnel
• Pas d'indexes CLUSTER sous MsAccess

29
MsAccess CREATE TABLECONSTRAINT Contraintes
d'intégrité

• Contrainte sur attribut unique
• CONSTRAINT nom PRIMARY KEY UNIQUE NOT NULL
  
• REFERENCES foreigntable (foreignfield1,
  foreignfield2)
• Contrainte sur plusieurs attributs
• CONSTRAINT name
• PRIMARY KEY (primary1, primary2 , ...)
• UNIQUE (unique1, unique2 , ...)
• NOT NULL (notnull1, notnull2 , ...)
• FOREIGN KEY (ref1, ref2 , ...) REFERENCES
  foreigntable (foreignfield1 , foreignfield2 ,
  ...)
• foreigntable la table avec la clé primaire
  référencée
• le langage de définition de QBE d MsAccess
  permet de définir davantage de contraintes (comme
  on a vu en partie)
• surtout les contraintes prédicatives d intégrité
• mono ou multi-attribut, mais mono-table.

30
La table nommée P_1 est en fait la table P
Les clauses CASCADE n existent quen QBE de
MsAccess
31
Exercice que veulent dire ces contraintes ?
Les clauses is Nullgt dans les Validation
Rules sont-elles utiles ?
32
Sous-Tables en MsAccess

• Une table peut avoir une sous-table
• dite sous-feuille
• La sous-table auto contient la clé étrangère
  de sa table (feuille)
• Alternativement, la sous-table est choisie à
  travers un lien sémantique défini manuellement
• Table ou requête

33
Sous-Tables en MsAccess

• Dans la base SP
• Table SP est automatiquement la sous-table de S
• Table S peut être choisie manuellement comme
  sous-table de SP
• Avec le champs père SP.S et champs fils S.S
• Suggérés par MsAccess
• Les liens S -gt SP -gt S sont alors transitifs

34
Sous-Tables en MsAccess

• Dans la base SP
• Soit la requête Quantité / fournisseur
• Select Sum (Qty) From SP Group by S
• Cette requête peut être rendue sous-feuille de SP
• Elle matérialise alors le concept de lattribut
  dérivé dUML pour SP
• Le formulaire résultant apparaît comme entité
  structurée

35
Sous-Tables en MsAccess

• On crée une sous-table
• Sur le menu propriétés dune table
• Auto / Aucune / Nom de la table / requête
• On peut fixer la hauteur de la sous-fenêtre ou la
  laisser auto (option 0 cm)
• La sous-feuille peut apparaître in extenso (ligne
  étendue oui) ou par seulement
• à cliquer pour la voir éténdue
• Sur le menu Insertion de la vue de la table
  ouverte
• La sous-feuille est signalée par seulement

36
Sous-Tables en MsAccess
37
Sous-Tables en MsAccess
38
Sous-Tables en MsAccess
Réalisation limitée dune table à attributs
hérités. Litwin, W. Ketabchi M. Risch, T.
Relations with Inherited Attributes HPL Tech
Rep. HPL-DTD-92-45, April. 1992), 30.
39
CREATE INDEX(MSAccess)

• Création dun index qui na pas été fait par la
  clause de contrainte
• Création dun index qui ne peut pas être fait par
  la clause de contrainte
• Options dordre
• Création alternative de la clé primaire
• Création dun pseudo-index sur la table liée

40
CREATE INDEX(MSAccess)

• La syntaxe
• CREATE UNIQUE INDEX index    ON table
  (champ ASCDESC, champ ASCDESC,
  ...)    WITH PRIMARY DISALLOW NULL
  IGNORE NULL
• Exemple
• create index xp on P (city desc, pname asc)

41
CREATE INDEX(MSAccess)

• Il existe lalternative QBE
• Commande Index dans le Menu Affichage ou bouton
  droit
• Quand la table est ouverte

42
ALTER TABLE(MSAccess)

• La syntaxe
• ALTER TABLE table ADD COLUMN type
  champ(taille) NOT NULL     CONSTRAINT index
      ALTER COLUMN type champ(size)
      CONSTRAINT indexmultichamp     DROP
  COLUMN champ I CONSTRAINT nomindex
• Permet dajouter / supprimer les attributs et les
  contraintes
• les indexes notamment

43
DROP (MSAccess)

• La syntaxe
• DROP TABLE table INDEX index ON table
  PROCEDURE procédure VIEW vue
• Permet de supprimer
• une table
• un index
• une vue
• une procédure
• Un choix hors standard SQL de MS

44
Contraintes référentielles mutuelles en SQL-2

• On utilise CREATE SCHEMA ou combinaison de CREATE
  TABLE et ALTER TABLE
• CREATE SCHEMA AUTHORIZATION Witold
• CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT
  REFERENCES t2(c1))
• CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT
  REFERENCES t1(c1))

45
SQL manipulation de données

• expression générale de sélection
• SELECT DISTINCT attribut(s)
• FROM table(s)
• WHERE condition
• GROUP BY field(s)
• HAVING condition
• ORDER BY attribute(s)
• basée sur le calcul de tuple
• produit une table temporaire (en général avec des
  duplicata)

46
Examples

• En pratique sur MsAccess
• Sauf ceux spécifiques à
• SQL-Server
• DB2

47
Exemple canon
SP
S
P
48
MsAccess SELECT

• SELECT predicate table. table.field1
  , table.field2., ...
• AS alias1 , alias2 , ...
• FROM tableexpression , ...
• IN externaldatabase
• WHERE...
• GROUP BY...
• HAVING...
• ORDER BY...
• WITH OWNERACCESS OPTION
• Predicat ALL, DISTINCT, DISTINCTROW, TOP.

49
Interrogations (vraiment) simples

• Projections d'une table sur certains attributs
• SELECT S FROM S

Combien de lignes de programmation faudrait-il
pour cette requête en Cobol ? 20 ? 50 ? 100 ?
S s1 s2 s3 s4 s5
MsAccess

• L'ordre de présentation est fixé par le SGBD et
  peut changer dans le temps

50
Selection avec duplicata

• SELECT CITY FROM S

• Le résultat peut avoir les duplicata
• alors, il n'est pas une relation, mais un bag

51
Elimination de duplicata

• SELECT DISTINCT CITY FROM S

Combien de lignes de programmation faudrait-il
pour cette requête en C ? 20 ? 50 ? 100 ?
CITY Athens London Paris

• DISTINCT est optionnel pour deux raisons
• éliminer les duplicata coûte en temps de réponse
• les fonctions agrégats en ont besoin.

52
Selections multiples

• Les attributs apparaissent dans lordre de leur
  énumération dans la clause SELECT
• SELECT S, CITY, SNAME FROM S

S City SName s1 Paris Smith s2 Paris Jones s3 P
aris Blake s4 London Clark s5 Athens Adam
53
SELECT

• Tout sur toutes les fournitures
• SELECT S, P, QTY FROM SP
• Formulation plus courante
• SELECT FROM SP
• Ordre d'attributs est celui de CREATE TABLE
• Cette formulation est plus simple, mais
  deconseillée pour les programmes d'application
• pourquoi ?

54
ORDER BY

• SELECT FROM SP
• ORDER BY QTY DESC, S

55
ORDER BY

• SELECT FROM SP
• ORDER BY QTY DESC, S

S p Qty s1 p3 400 s2 p2 400 s4 p5 400
s1 p1 300 s2 p1 300 s4 p4 300 s1 p4 200 s1 p2 200
s3 p2 200 s4 p2 200 s1 p6 100 s1 p5 100
Combien de lignes de programmation faudrait-il
pour cette requête en PL1 ? 20 ? 50 ? 100 ?
56
ORDER BY

• SELECT FROM SP
• ORDER BY QTY DESC, S

S p Qty s1 p3 400 s2 p2 400 s4 p5 400
s1 p1 300 s2 p1 300 s4 p4 300 s1 p4 200 s1 p2 200
s3 p2 200 s4 p2 200 s1 p6 100 s1 p5 100
Et la quantité nulle serait où ?
57
TOP

• SELECT top 3 S AS Les petits, P, QTY
• FROM SP
• ORDER BY QTY ASC, S

58
TOP

• SELECT top 3 S AS Les petits, P, QTY
• FROM SP
• ORDER BY QTY ASC, S
• Les petits Product ID QTY
• s1 p6 100
• s1 p5 100
• s1 p4 200
• s1 p2 200

59
TOP

• SELECT top 3 S AS Les petits, P, QTY
• FROM SP
• ORDER BY QTY ASC, S
• Les petits Product ID QTY
• s1 p6 100
• s1 p5 100
• s1 p4 200
• s1 p2 200
• Mot-clé utile, mais pas dans SQL standard
  (MsAccess)
• - essaye de formuler cette requête en SQL
  standard
• Pas de distinction entre les duplicata par
  rapport au critère d'ordre QTY, S (les 3 tops
  sont devenus 4 tuples)

Product ID ?
60
Restrictions simples

• SELECT P, PNAME FROM P WHERE COLOR 'RED'

61
Restrictions simples

• SELECT P, PNAME FROM P WHERE COLOR 'RED'
• Product ID Product Name
• p1 nuts
• p4 screw
• p6 cog
• Les noms d'attributs sont les légendes créées à
  la création de P
• L'ordre de tuples délivrés est définit par le
  SGBD et peut changer d'une exécution à l'autre
• Est-il possible de faire
• SELECT Product ID, Product Name

62
Restrictions composées

• SELECT P, PNAME, CITY FROM P WHERE COLOR
  'RED' AND NOT CITY 'PARIS'

63
Restrictions composées

• SELECT P, PNAME, CITY FROM P WHERE COLOR
  'RED' AND NOT CITY 'PARIS'
• Product ID Product Name city
• p1 nuts london
• p4 screw london
• p6 cog london

64
Restrictions composées

• Les formulations
• SELECT P, PNAME, CITY FROM P WHERE COLOR
  'RED' AND CITY THIERS'
• et
• SELECT P, PNAME, CITY FROM P WHERE CITY
  THIERS' AND COLOR 'RED'
• ne donnent pas toujours les mêmes temps
  dexécution.
• Bien quelles soient logiquement équivalentes
• Notamment si les deux attributs sont indexés
• Il vaut mieux mettre dabord la clause en
  général plus sélective
• vraie pour moins de tuples (ou aucun si possible)

65
Restrictions sur nuls

• Un nul nest pas une valeur
• Donc on a une clause spéciale
• IS NOT NULL
• Ex. Deux fournisseurs n ont pas de ville connue
• Requête est-ce que il y a dans S des villes
  inconnues?
• SELECT S.CityFROM S where city is null

A noter DISTINCT s applique aux nuls (à tort,
pourquoi ?)
CITY


Vous avez dit bizarre pour la table de nuls ?
66
Restrictions par clause IN

• SELECT s, city FROM S where city IN ('paris',
  'london')

S SName Status City s1 John 2 Paris s2 smith 10 P
aris s3 Blake 30 s4 Clark 20 London s5 Adam 30 At
hens s6 Bull 20 Paris s7 Ibm 100 Paris
s city s1 Paris s2 Paris s4 London s6 Paris s7 Pa
ris
? SELECT s, city FROM S where city NOT IN
('paris', 'london')
? SELECT s, city FROM S where city IN
('paris', 'london', null)
? SELECT s, city FROM S where city IN
('paris', 'london') or city is null
67
Expressions de valeur

• SELECT P, PNAME, 2.1 weight as Poids
• FROM P
• order by 2.1weight desc

Product ID Product Name Poids p6 cog
39.9 p3 screw 35.7 p2 bolt
35.7 p4 screw 29.4 p5 cam
25.2 p1 nuts 25.2
68
Expressions de valeur

• On peut utiliser - / et ( )
• On peut sélectionner tous les attributs et une
  expression de valeur
• SELECT , 2.1weight as Poids en KG, weight
  weight/5 - (weight2 - weight2.1) as un
  jeu FROM P order by 2.1weight desc
• On peut utiliser une expression de valeur comme
  argument dune clause de restriction
• . WHERE WEIGTH 200 2,1

69
Expressions de valeur

• On ne peut pas utiliser dans ORDER BY lalias
  défini pour une expression de valeur dan la
  clause SELECT
• Essayez ORDER BY Poids
• ? Vrai / Faux que les résultats des expressions
  de valeur suivantes sont toujours comme suit
• 0 QTY 0
• QTY QTY 0
• QTY / QTY 1 si QTY ltgt 0
• La 1ère équation est dimportance par exemple
  pour les requêtes où un attribut X sélectionné
  pour SELECT XQTY pourraient avoir la valeur
  0.

70
Jointures
Brancusi
Raphaël
71
Jointures

• SELECT distinct S.S, SNAME, P, Qty, City
• FROM S, SP
• where s.ssp.s and city ltgt 'London'

72
Equi-jointures

• SELECT distinct S.S, SNAME, P, Qty, City
• FROM S, SP
• where s.ssp.s and city ltgt 'London'
• S SNAME Product ID Qty City
• s2 Jones p1 300 Paris
• s2 Jones p2 400 Paris
• s3 Blake p2 200 Paris

73
Sémantique de la requête

• Forme le produit cartésien C de tables dans la
  clause FROM
• Sélectionne tout tuple t de C vérifiant le
  prédicat dans la clause WHERE (et seulement de
  tels tuples)
• Projette tout t sur les attributs dans SELECT
• Applique le mot-clé de SELECT
• La clause S.s SP.s s'appelle equi-jointure
  ou jointure naturelle
• Opération de jointure était inconnue, même
  conceptuellement, de SGF et de SGBD navigationels

74
Sémantique de jointures naturelles

• L'intention est de suivre les arcs du graphe de
  référence
• C'est cette opération qui permet la décomposition
  du modèle d'un object en plusieurs relations
• selon les propriétés
• fonctionnelles
• non-fonctionnelles

S
S
P
P
S
SP
P
75
Sémantique de jointures naturelles

• Le prix a payer
• redondance de certaines valeurs
• données plates de forme peu naturelle
• S SNAME Product ID Qty City
• s2 Jones p1 300 Paris
• s2 Jones p2 400 Paris
• s3 Blake p2 200 Paris
• forme plus naturelle
• S SNAME Product ID Qty City
• s2 Jones p1 300 Paris
• p2 400 Paris
• s3 Blake p2 200 Paris

76
Equi-jointures

• L'évaluation de requêtes à partir de C serait en
  général inefficace
• Pour la matérialisation en mémoire
• En utilisation de CPU
• Si card (R1) card (R2) 1000, alors card(R1
  x R2) 1 000 000 tuples !
• En pratique, on ne crée que les tuples de la
  jointure
• plusieurs méthodes de calcul existent
• Nested loop (boucles imbriquées)
• Sort-merge (tri-interclassement)
• Index-joins Hash-Joins in particular
• Linear Hash-Joins

77
Equi-jointures m-aires

• SELECT s.s, p.P, Qty, PnameFROM S, SP, P
  where s.ssp.s and sp.pp.p and
  s.s between 's1' and 's3' order by s.S,
  qty desc

78
Equi-jointures m-aires

• SELECT s.s, p.P, Qty, PnameFROM S, SP, P
  where s.ssp.s and sp.pp.p and
  s.s between 's1' and 's3' order by s.S,
  qty desc
• s Product ID Qty Product Name
• s1 p3 400 screw
• s1 p1 300 nuts
• s1 p4 200 screw
• s1 p2 200 bolt
• s1 p6 100 cog
• s1 p5 100 cam
• s2 p2 400 bolt
• s2 p1 300 nuts
• s3 p2 200 bolt

79
Clause BETWEEN

• Le type d attribut détermine l évaluation de la
  clause
• 20 n est pas BETWEEN 1 and 3 pour Number
• 20 est BETWEEN 1 and 3 pour Text
• Date/Time ou Currency ?
• Et les nuls
• sont ils sélectionnes par les clauses ci-dessous
• SELECT FROM P where weight between 0 and 19
• SELECT FROM P where weight between null and 19
• SELECT FROM P where weight between 0 and null
• peut-on faire encore autrement pour trouver les
  poids entre 10 et 19 ou inconnus ?

80
Equi-jointures m-aires(avec )

• Tous les attributs de toutes les tables dans la
  clause FROM
• SELECT
• FROM S, SP, P
• where s.ssp.s and p.psp.p and
  s.city ltgt 'London'
• On peut aussi SELECT S., SP., P. FROM S,SP, P
  bien-sûr
• On peut ajouter des attributs additionnels
• SELECT , 'Mecs dEurostar' as D'ou viennent
  t'ils ?
• FROM S, SP, P
• where s.ssp.s and p.psp.p and
  s.city ltgt 'London'

81
Equi-jointures

• Equi-jointures peuvent être formulées pour tout
  attribut
• Mais, les types de données à joindre doivent être
  compatibles
• SELECT s.s, p.P, Qty, Pname, s.city,
  p.cityFROM S, SP, Pwhere s.ssp.s and
  sp.pp.p and s.cityp.cityorder by s.city,
  s.s
• s Product ID Qty Product Name S.city P.city
• s1 p6 100 cog London london
• s1 p4 200 screw London london
• s1 p1 300 nuts London london
• s4 p4 300 screw London london
• s2 p2 400 bolt Paris paris
• s3 p2 200 bolt Paris paris

82
Equi-jointures dans la clause FROM

• Possibilité nouvelle dans SQL2 (et MsAccess)
• Prévue dans le nouvel SQL standard
• SQL-2
• Permet de standardiser la formulation de
  jointures externes
• On les verra plus tard
• Permet aussi de fixer explicitement lordre de
  jointures
• Pour optimiser la requête

83
Equi-jointures dans la clause FROM

• SELECT S.S, P.p, SP.Qty, PName, S.City,
  P.City FROM S INNER JOIN (P INNER JOIN SP ON
  P.P SP.p) ON (S.City P.City) AND
  (S.S SP.S)ORDER BY S.City, S.S

s Product ID Qty Product Name S.city P.city s1
p6 100 cog London london s1 p4 200 screw Londo
n london s1 p1 300 nuts London london s4 p4 300
screw London london s2 p2 400 bolt Paris pari
s s3 p2 200 bolt Paris paris
84
Theta-jointures Self-jointures

• L'opérateur T de comparaison dans une clause de
  jointure peut-être en fait
• T????, lt, lt??gt, gt???ltgt
• Une table peut-être jointe avec elle-même
• On suppose que les noms de fournisseurs sont
  tos différents
• SELECT s1.s, s1.sname, s2.s, s2.sname,
  s1.cityFROM s s1, s s2 / s1, s2 sont des
  aliasesWHERE s1.city s2.city and s1.sname lt
  s2.sname
• s1.s s1.sname s2.s s2.sname city
• s4 Clark s1 Smith London
• s3 Blake s2 Jones Paris

85
Jointures externes

• Conserve les tuples sans corresp. sur les
  attributs de jointure
• - jointure gauche (LEFT) conserve les tuples à
  gauche
• - jointure droite (RIGHT) conserve les tuples à
  droite
• SELECT S.S, city, SP.QtyFROM S LEFT JOIN SP
  ON S.S SP.Swhere (qty gt 200 or qty is
  null) and not city 'london'
• S city Qty
• s2 Paris 300
• s2 Paris 400
• s5 Athens

s5
s7 p6 100
86
Jointures externes(propriétés algébriques)

• Les jointures classiques dites internes sont
  associatives
• Celle externes ne sont pas
• A démontrer
• La notation dans la clause WHERE pourrait être
  ambiguë
• Pourquoi ?
• Doù la notation algébrique dans la clause FROM
• Introduite par SQL-2
• Elles sappliquent aussi aux jointures classiques
  dites dès lors internes

87
Jointure externe complète

• SELECT pname, S.SName, S.City, P.CityFROM P
  RIGHT JOIN S on P.City S.CityunionSELECT
  pname, S.SName, S.City, P.CityFROM P left JOIN S
  ON P.City S.City

pname SName S.City P.City Adams Athens bolt Blak
e Paris paris bolt Jones Paris paris bolt smith Pa
ris paris cam Blake Paris paris cam Jones Paris pa
ris cam smith Paris paris cog Clark London london
nuts Clark London london screw rome screw Clark
London london
88
Jointure externe complète avec une sélection

• SELECT pname, S.SName, S.City, P.CityFROM P
  RIGHT JOIN S ON P.City S.Citywhere p.city ltgt
  'london' or p.city is nullUNION SELECT
  pname, S.SName,S.City, P.CityFROM P left JOIN S
  ON P.City S.Citywhere s.city ltgt 'london' or
  s.city is null

pname SName S.City P.City Adams Athens bolt Blak
e Paris paris bolt Jones Paris paris bolt smith Pa
ris paris cam Blake Paris paris cam Jones Paris pa
ris cam smith Paris paris screw rome
89
Jointure externe self ou avec theta-comparaison

• Self-jointure externe est possible
• p.e. SP left joint SP
• N de tout fournisseur qui fournit une pièce en
  quantité la même que celle dun autre fournisseur
  ou est inconnue
• Les opérateurs T sappliquent aussi aux jointures
  externes
• T????, lt, lt??gt, gt???ltgt
• Ns de tout fournisseur qui fournit une pièce en
  quantité moindre quun autre fournisseur dune
  même pièce ou en quantité inconnue.

90
Jointures externes DB2 SQL-Server SQL-2

• On utilise les déclarations
• LEFT, RIGHT FULL OUTER JOIN
• note OUTER

SELECT pname, S.SName, S.City, P.City FROM P
FULL OUTER JOIN S ON P.City S.Citywhere
p.city ltgt 'london' or p.city is null
pname SName S.City P.City Adams Athens bolt Blak
e Paris paris bolt Jones Paris paris bolt smith Pa
ris paris cam Blake Paris paris cam Jones Paris pa
ris cam smith Paris paris screw rome

• SQL-2 a le verbe USING pour les attr. de jointure
  d un même nom (USING (CITY). Les mots FULL ou
  INNER sont optionnels
• certains dialectes remplacent, LEFT, RIGHT, FULL
  par
• P.City S.City P.City S.City ()

91
Mélange de jointures externes et internes

• Explosif (sous MsAccess surtout)
• OK
• SELECT sP.Qty, s.S, s.City, sP.pFROM s
  RIGHT JOIN (p INNER JOIN sP ON p.P sP.p)
  ON sP.S s.S
• interdit
• SELECT sP.Qty, s.S, s.City, sP.pFROM s
  LEFT JOIN (p INNER JOIN sP ON p.P sP.p)
  ON sP.S s.S

92
Mélange de jointures externes et internes

• En deux requêtes c'est OK par contre
• Query-scratch1
• SELECT
• FROM p INNER JOIN sp ON p.P sp.p
• SELECT s.s, qty, Query-scratch1.color FROM
  s left JOIN Query-scratch1 ON
  Queryscratch1.S S.S

93
Résultat

• s qty color
• s1 100 red
• s1 100 blue
• s1 200 red
• s1 400 blue
• s1 200 green
• s1 300 red
• s2 400 green
• s2 300 red
• s3 200 green
• s4 400 blue
• s4 300 red
• s4 200 green
• s5

94
Jointures implicites

• Simplifient la formulation de la requête
• Générées par MsAccess à partir de contraintes
  d'intégrité référentielles et les liens
  sémantiques
• jointures naturelles (internes)
• jointures externes
• Générées dans SQL, mais uniquement quand la
  requête est formulée en QBE

95
Declaration de jointures implicites
On a cliqué d'abord ici
On a cliqué ensuite ici
96
Formulation de la requête avec les jointures
implicites en QBE
Puis, clique et...
Tires avec la souris,
Ecris la restriction
97
Résultat SQL

• SELECT DISTINCTROW S.SName, P.City
• FROM P INNER JOIN (S INNER JOIN SP ON S.S
  SP.S) ON P.P SP.p
• WHERE ((P.City"paris"))

Jointure impl. générée automatiquement
98
Limitations

• Si les tables choisies ne sont pas directement
  en relation, alors, il faut ajouter aussi sous
  QBE toutes les tables intermédiaires
• Pour formuler SELECT sname, pname FROM S,P avec
  les jointure implicites, il faut aussi inclure
  sous QBE la table SP
• Bien que lon ne sélectionne aucun attribut de
  cette table

99
Limitations

• Lordre de clauses résulte de celui de sélection
  de tables, mais seulement si lon suit les
  relations directes.
• Essayez ajouter les tables et regarder le
  résultat SQL, selon les permutations suivants
• P,SP,S puis S,SP,P, puis P, S, SP puis S,P, SP
• Conclusion ?

100
Limitations

• Une correspondance déclarée entre les attributs
  d'une même relation ne génère pas de jointure
  implicite
• sous MsAccess 2
• Pourquoi cette limitation ?
• une bonne question
• sans bonne réponse de ma part
• à adresser à Microsoft

101
(No Transcript)
102
Limitations

• Une correspondance multiple entre deux tables
• donne lieu à AND entre les clauses
  correspondantes
• c'est bien
• mais, peut donner lieu à une génération erronée
• une jointure implicite invisible sur l'image QBE
• C'est un "bug"
• MsAccess 2
• Pourquoi ?
• bonne question à Microsoft
• Pour en savoir sur les jointures implicites en
  général
• Implicit Joins in the Structural Data Model.
  IEEE-COMPSAC, Kyoto, (Sep. 1991). With Suk Lee,
  B., Wiederhold, G.

103
(No Transcript)
104
(No Transcript)
105
(No Transcript)
106
(No Transcript)
107
(No Transcript)
108
(No Transcript)
109
Jointure Automatique

• Une jointure implicite entre deux attributs de
  deux tables différentes sélectionnées pour une
  requête
• sans lien sémantique dans le schéma
• de type compatible
• dun même nom
• avec au moins étant la clé primaire
• A ne pas confondre avec une self-jointure
• Existent dans MsAccess2000
• en option

110
Auto-jointures DB2

• Seulement en V 7 DB 2 Data Warehouse Center
• Le terme recouvre
• Les jointures implicites
• Les jointures automatiques entre tous attributs
  compatibles
• Pas plus quune jointure par table

111
Clauses de jointures dans le Standard SQL-2

• Il y a plusieurs clauses non-implementées
  explicitement dans aucun SGBD
• T1 NATURAL JOIN T2
• remplace T1 INNER JOIN T2 ON T1.J1 T2.J1 AND
  T1.Jn T2.Jn
• S NATURAL FULL OUTER JOIN SP
• Crée une seul attribut S
• Comment simuler sous MsAccess ?
• S CROSS JOIN SP
• Produit cartésien
• Voir le standard
• Jointures implicites et jointures auto sont une
  forme implicite de ces clauses
• pas toujours, pourquoi ?

112
Fonctions agrégats

• Un nombre très limité
• COUNT, SUM, AVG, MAX, MIN,
• MIN, MAX sapplique aux Nuls ( à tort)
• MsAccess StDev, Var, First, Last
• MsAccess97 VarP, StDevP
• calcul sur la population, pendant que Var, StDev
  utilisent un échantillon
• A mettre dans SELECT
• SELECT sum(P.Weight) AS PoidsCumuleFROM P

PoidsCumule 91
113
Fonctions agrégats

• SELECT Count () FROM S WHERE compte tous les
  tuples
• SELECT Count (CITY) FROM S ne compte pas de nulls
• mais compte les doubles
• SELECT COUNT (DISTINCT (CITY)) FROM S
• Possible avec SQL ANSI, mais pas MsAccess
• Pourquoi ?
• - Très bonne question à Microsoft
• Possible dans SQL-Server ou DB2 ?
• Formulable autrement avec MsAccess ?
• On peut compter sur plusieurs champs, pourvus
  qu'ils ne soient pas tous nuls dans le tuple
  (MsAccess)
• SELECT Count ("City Status") FROM S
• Compte les fournisseurs sans ville connue
• Compte le nombre de villes avec un fournisseur
  (MsAccess)

114
Fonctions agrégats

• SELECT Varp(SP.Qty) AS Varp, Var(SP.Qty) AS Var,
  StDev(SP.Qty) AS StDev, StDevp(SP.Qty) AS
  StDevpFROM SP

Varp Var StDev StDevp 15644.6280991736 17209.0
909090909 131.183424673588 125.078487755383
115
GROUP BY

• Permet d'appliquer les fonctions agrégats aux
  sous-tables, dites groupes, définies par
  l'égalité de certains attributs
• Inexistant dans SQL originel (et le modèle
  relationnel)
• Est populaire mais redondante
• ce qui est peu connu (voir le cours sur les
  Subtilités de SQL)
• A été introduite par Peter Gray d'Univ.
  d'Aberdeen (je crois).

116
GROUP BY

• SELECT top 50 percent p, sum (qty) as
  tot-qty from sp GROUP BY pOrder by sum
  (qty) desc
• p tot-qty
• p2 1000
• p1 600
• p5 500
• p4 500

117
GROUP BYattributs multiples

• Tous les attributs sélectionnés non-agrégés
  forment le GROUP BY
• SELECT S.SName, Sum(SP.Qty) as Somme, S.S
• FROM S INNER JOIN SP ON S.S SP.S
• WHERE SP.Qty gt 100
• GROUP BY S.SName, S.S
• Sname Somme S
• Clark 900 s4
• Jones 700 s2
• Jones 200 s3
• Smith 1100 s1

118
HAVING

• Permet de spécifier les prédicats sur les groupes
  de GROUP BY
• et sur les attributs non agrégés,
• double emploi avec WHERE
• SELECT p, sum (qty) as tot-qty from sp
  GROUP BY pHAVING SUM (QTY) gt 200ORDER BY
  SUM (QTY) DESC
• p tot-qty
• p2 1000
• p1 600
• p5 500
• p4 500
• p3 400

119
Sous-requêtes

• Une expression alternative de jointures
• Permet une optimisation manuelle
• la sous-requête est exécutée d'abord
• Permet d'appliquer les fonctions agrégats dans la
  clause WHERE
• Permet d'appliquer le quantificateur EXISTS
• et donc, indirectement, le quantificateur FORALL
  (universel)

120
Sous-requêtes

• SELECT s, sname from S where s.s in
  (select s from sp where qty gt 200)
• SELECT s, sname, statusfrom S where s.status
  (select max (status) from s as S1)
• s sname status
• s1 Smith 30
• s3 Blake 30
• s5 Adams 30

121
Sous-requêtes

• La requête à sous-requête
• SELECT S, SNAME FROM S
• WHERE STATUS gt 100 AND CITY IN
• (SELECT CITY FROM S WHERE CITY PARIS)
• est en général préférable à celle plus naturelle
  à restrictions composées
• SELECT S, SNAME FROM S
• WHERE STATUS gt 100 AND CITY PARIS
• Le temps dexécution est plus petit.
• Si les deux attributs sont indexés
• La plupart de fournisseurs est à Paris
• Il y a peu de fournisseurs de Statut gt 100
• Bien que les deux requêtes soient logiquement
  équivalentes

122
EXISTS

• SELECT s, sname, status from S where exists
  (select from sp where ssp.sand
  sp.p'p2')
• s sname status
• s1 Smith 30
• s2 Jones 10
• s3 Blake 30
• s4 Clark 20
• s5 Adams 30

123
FORALL lt-gt NOT (NOT EXISTS)

• SELECT s, snamefrom S where not exists
  (select from p where not exists ( select
  from sp where ss.s and pp.p ))
• s sname
• s1 Smith

124
NOT...NOT EXISTS

• SELECT distinct s from SP X where not exists
  (select from sp y where s's2' and
  not exists (select from sp z
  where z.sx.s and z.py.p ))
• s
• s1
• s2
• C'est quoi ?
• Tous les fournisseurs qui fournissent au moins
  les pièces du fournisseur 'S2'.

125
NOT...NOT EXISTS

• SELECT distinct s from SP X where not exists
  (select from sp y where s's2' and
  not exists (select from sp z
  where z.sx.s and z.py.p ))
• s
• s1
• s2
• C'est quoi ?
• Tous les fournisseurs qui fournissent au moins
  les pièces du fournisseur 'S2'.

SQL c'est simple car non-procedural une
intention une requête
126
UNION

• SELECT P, CITY FROM P WHERE CITY LIKE
  'L-S'UNION SELECT P, CITY FROM SP, S
  WHERE SP.SS.S AND CITY gt 'B'

127
UNION

• SELECT P, CITY FROM P WHERE CITY LIKE
  'L-S'UNION SELECT P, CITY FROM SP, S
  WHERE SP.SS.S AND CITY gt 'B'
• P city
• p1 London
• p1 Paris
• p2 London
• p2 Paris
• p3 London
• p4 London
• p5 London
• p6 London

Tous les duplicata sont éliminés
Comment faire alors pour les agrégats ?
128
UNION MsAccess

• Les tables ou vues entières union-compatibles
  peuvent être référencées explicitement
• TABLE Customers UNION TABLE Suppliers
• On ne peut pas sélectionner dattributs de type
  MEMO ou OLE
• Y compris par
• Déjà déconseillé pour les programmes
  dapplication
• Pas d opérateurs INTERSECT, EXCEPT
• Comment faire alors ?

129
UNION ALL

• Préserve les duplicata
• Nécessaire pour appliquer des agrégations
• Mais, souvent il faut néanmoins dans ce but au
  moins 2 requêtes SQL
• Défaut de conception SQL
• Solutions pratiques
• clause FROM imbriquée
• Une autre (DB2) voir cours SQL2
• Dans l exemple qui suit, sous MsAccess, on veut
  des agrégations sur WEIGHT
• la 1ere requête définie une vue appelée UNION-ALL
• la 2eme requête calcule les agrégations voulues

130
UNION ALL

• SELECT weight, p.city FROM P WHERE City like
  'l'UNION ALL SELECT weight, s.city FROM p, SP,
  S WHEREp.psp.p and sp.ss.s and
  s.City like 'p'
• weight city
• 12 london
• 14 london
• 19 london
• 12 Paris
• 17 Paris
• 17 Paris

131
UNION ALL

• SELECT weight, p.city FROM P WHERE City like
  'l'UNION ALL SELECT weight, s.city FROM p, SP,
  S WHEREp.psp.p and sp.ss.s and
  s.City like 'p'
• weight city
• 12 london
• 14 london
• 19 london
• 12 Paris
• 17 Paris
• 17 Paris
• SELECT AVG(WEIGHT) AS AVG POIDS, VAR(WEIGHT) AS
  VAR-POIDS, MAX(WEIGHT) AS POIDS-MAXFROM
  UNION-ALL
• avg poids var poids poids-max
• 15.1666666666667 8.56666666666667 19

132
Clause FROM imbriquée

• Définit une table dans la clause FROM dune
  expression de sélection SQL (SQL-Select)
• Cette dernière peut-être imbriquée à son tour
• Select attrsFROM tbls, (SQL-Select)
  Where .
• Clause non-documentée sous MsAccess
• La traduction SQL-QBE est boguée
• À essayer

133
Clause FROM imbriquée

• Possibilités
• Agrégations par-dessus UNION ou UNION ALL
• Imbrication des expressions de valeur
• Calcul de COUNT (DISTINCT)
• MsAccess
• Récursivité limitée

134
Clause FROM imbriquée

• SELECT sum(weight) AS poids-total
• FROM (SELECT weight, p.city FROM P WHERE City
  like 'l'
• UNION ALL SELECT weight, s.city FROM p, SP, S
  WHERE p.psp.p and sp.ss.s and
  s.City like 'p')

135
Clause FROM imbriquée

• select avg(moy1) as moyenne-des-moyennes
• FROM
• (SELECT avg(weight) as moy1 FROM P WHERE City
  like 'l'
• UNION ALL SELECT avg(weight) as moy1 FROM p, SP,
  S WHERE p.psp.p and sp.ss.s and
  s.City like 'p')

136
Requêtes à paramètres

• un paramètre un texte visualisé dans la
  clause WHERE pour que l'usager indique une valeur
• le texte peut être sans , sil ne désigne pas
  dattribut et n a pas de blancs, etc.
• Possibilité à éviter à cause de conflit de noms
  possible
• "Paris" est une constante Paris serait un
  paramètre
• Le type de données d'un paramètre par défaut est
  texte.
• On peut-être déclarer un type différent par la
  clause PARAMETER
• recommandée pour un paramètre dans une expression
  de valeur (et obligatoire dans la requête qui
  suit)

137
Requêtes à paramètres

• On peut utiliser plusieurs paramètres
• pour une clause BETWEEN Qty Min ? AND Max ?
• On peut utiliser la clause LIKE City ?
• Alors la réponse doit être selon la sémantique de
  la clause LIKE,
• P. e., L-P signifiera  toutes les villes qui
  commencent par une lettre entre L et P, inclus
• Alternativement on peut ajouter les caractères
  génériques à la réponse d'usager
• P.e. LIKE City ? ""

138
Requêtes à paramètres

• Le nom dans le paramètre a la priorité sur le
  nom de l'attribut, si on génère un conflit de
  noms
• PARAMETERS weight LongSELECT P.Weight,
  p.Weight3/2, P.ColorFROM PWHERE p.Weight3/2 gt
  weight and weight 6 gt p.weight
• On évite le conflit par lemploi de P.Weight
• Note il n y a ci-dessus quun seul param.
  weight
• Est-ce que cest la même requête
• PARAMETERS weight LongSELECT Weight,
  Weight3/2, P.ColorFROM PWHERE Weight3/2 gt
  weight and weight 6 gt weight

139
Requêtes à paramètres

• Expression de paramètre peut être celle de
  valeur
• WHERE ... Prix Prix HT svp 1,2
• Est-ce une requête à paramètre
• SELECT S.SName, Sum(SP.Qty) as somme, S.S
• FROM S INNER JOIN SP ON S.S SP.S
• WHERE SP.Qty gt 100
• GROUP BY S.SName, S.S

140
Fonctions scalaires

• S appliquent aux valeurs individuelles
• d attributs
• d agrégations SQL
• Il y a plusieurs catégories
• mathématiques
• financières
• chaînes de caractères
• .
• Varient entre les dialectes
• MsAccess possède UCASE, pas DB2
• DB2 possède LOG10, pas MsAccess

141
Fonctions scalaires

• Peuvent simbriquer
• contrairement aux agrégats SQL
• SELECT log((sum(qty2)(1/2))) as
  exempleFROM SP group by phaving
  int(log(sum(qty))) 5

exemple 5.70875764008279 5.99146454710798
142
Tabulations Croisées(Crosstab queries)

• Présentent les résultat sous forme habituelle de
  feuilles de calculs
• Les agrégats SUM, AVG.. de GROUP BY et les
  valeurs individuelles en même temps
• Impossible avec SQL standard
• Transforment les valeurs d'attributs en attributs
• Par exemple
• les valeurs de P trouvés pour un même S
  deviennent les attributs P1, P2,...
• les valeurs de P1, P2.. sont les QTY (par ex.)
  correspondants

143
Tabulations Croisées

• TRANSFORM Sum(SP.Qty)
• SELECT SP.S, Sum(SP.Qty) AS Total Qty
• FROM SP
• GROUP BY SP.S
• PIVOT SP.p

Nouvelles colonnes
144
Tabulations Croisées

• S Total Qty p1 p2 p3 p4 p5 p6
• s1 1300 300 200 400 200 100 100
• s2 700 300 400
• s3 200 200
• s4 900 200 300 400

L'intitulé Total Qty est mis par défaut par
MsAccess
145
Tabulations Croisées

• La fonction agrégat dans la clause TRANSFORM est
  obligatoire
• bien que SUM(QTY) AVG(QTY) QTY
• mais, COUNT(QTY) 1
• On peut générer une expression de valeur
• TRANSFORM SUM(0.5QTY) AS Q2SELECT
  Sum(SP.Q2) AS Qte tot. dans 1 mois,
  Avg(P.Q2) AS Qte moy. dans 1 moisFROM
  SPGROUP BY SP.SPIVOT SP.p

146
Tabulations Croisées

• On peut utiliser la clause WHERE
• WHERE P IN ('P1', 'P2')
• Alors les fonctions ne calculent les agrégats que
  sur P1 et P2.
• On peut aussi restreindre la tabulation seulement
  
• PIVOT SP.p IN ('P1', P2')
• Mais, cette clause n'affecte pas les calculs des
  agrégats
• Peut-on appliquer la clause ORDER BY ?
• Si oui, quel serait leffet sur les valeurs
  pivotées ?
• Peut-on ordonner par rapport à une fonction
  agrégat ?
• Comme on a fait pour les requêtes à GROUP BY ?
• Peut-on appliquer la clause HAVING ?

147
Clauses COMPUTE FOR BROWSE(SQL Server 6.5 et
7)

• expression générale de sélection
• SELECT DISTINCT attribut(s)
• FROM table(s)
• WHERE condition
• GROUP BY ROLLUP CUBE field(s)
• HAVING condition
• ORDER BY attribute(s)COMPUTE row-agg
  (colonne), row-agg (colonne)... BY (colonne),
  (colonne)...FOR BROWSE
• Clause FOR BROWSE permet une MAJ de données
  dynamiquement visualisées

148
Compute

• select title, type, price, advancefrom titles
  where ytd_sales is not nullorder by type
  desc compute AVG(price), SUM(advance) by
  type compute SUM(price), SUM(advance)go
• title type price advancefifty
  years... trad_cook 11.95 4000Onions.. trad_coo
  k 20.95 7000Sushi... trad_cook
  14.99 8000 avg 15.96 sum 1900
  0 title type price advanceComputer... psyc
  hology 21.95 7000... psychology
  .... .... avg 15.96 sum 19000
  sum sum 236.00 88400

149
Fonctions Agrégat pour OLAPDB2

• CountBig
• Pour le nombre de tuples gt 231
• Covariance
• entre des attributs ou des expressions de valeur
• Correlation
• entre des attributs ou des expressions de valeur
• Regression functions
• 10 fonctions
• Les paramètres de la droite de régression entre
  des attributs ou des expressions de valeur
• Rank, Dense_Rank
• Rank en présence de duplicata 1,2,2,4,4,6
• Dense_Rank en présence de duplicata
  1,2,2,3,3,4

150
Fonctions Agrégat pour OLAPDB2

• Fonctions spécifiques à DB2 Data Warehouse
  Center
• Analyse de la variance (ANOVA)
• Distributions de Fisher-F
• Valeur P
• Sous-totaux par période de temps
• Test de Chi-carré
• Statistique T pour n paires de valeurs et à
  partir du coeff. de corrélation r
• T r ((n 2 ) / (1 - r 2 ))0.5
• T ? 0 indique une corrélation entre les
  attributs

151
Fonctions Agrégat pour OLAPTeradata

• RANK
• CSUM (Cumulative (Running) Sums)
• MAVG and MSUM
• MDIFF (Moving Difference)
• QUANTILE
• QUALIFY
• SAMPLE

152
SELECT EmployeeName, (HireDate - DATE) AS
ServiceDays, RANK ( ServiceDays ) as Seniority
FROM Employee ORDER BY EmployeeName
EmployeeName
Service Days Seniority
Robyn Baker 9931
2
Nick Garrison 9931
2 Kyle
McVicker 9408 5
Eva OMalley
10248 1
Norma Powers
9409 4
153
SELECT Item, SalesDate, CSUM(Revenue,Item,SalesDat
e) as CumulativeSales FROM (SELECT Item,
SalesDate, Sum( Sales ) as Revenue FROM
DailySales WHERE StoreId5 AND SalesDate
Between 1/1/1999 and 1/31/1999 GROUP BY
Item, SalesDate) AS ItemSales ORDER BY SalesDate
Item SalesDate
CumulativeSales InstantCOW
01/01/1999 972.99
InstantCOW 01/02/1999