SQL Un Langage Relationnel Witold LITWIN 1 Langage

  • Slides: 141
Download presentation
SQL : Un Langage Relationnel Witold LITWIN 1

SQL : Un Langage Relationnel Witold LITWIN 1

Langage de base de données (Database Language) n n Un sous-langage de programmation Consiste

Langage de base de données (Database Language) n n 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) n En pratique, les deux parties sont imbriquées – définition de vues et des attributs hérités en général 2

SQL n n n n Inventé à IBM San Jose, 1974 (Boyce & Chamberlin)

SQL n n n n Inventé à IBM San Jose, 1974 (Boyce & Chamberlin) pour System R Basé sur le calcul de tuple & algèbre relationnellement complet (et plus) Le langage de SGBD relationnels En évolution contrôlée par ANSI (SQL 1, 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 3

SQL: Définition de Données n CREATE TABLE CREATE VIEW CREATE INDEX n ALTER TABLE

SQL: Définition de Données n CREATE TABLE CREATE VIEW CREATE INDEX n ALTER TABLE n DROP TABLE DROP VIEW DROP INDEX 4

CREATE TABLE (clauses essentielles) Définit la table réelle (de base) CREATE TABLE table (column

CREATE TABLE (clauses essentielles) Définit la table réelle (de base) CREATE TABLE table (column [, column]. . . [, primary key] ; n column : = name type [NOT NULL] type : = INTEGER, CHAR (n), GRAPHIC, ICON, DATE, TIME, TIMESTAMP 6

EXAMPLE CREATE TABLE S (S# CHAR (5) NOT NULL, SNAME CHAR (20), STATUS INT,

EXAMPLE CREATE TABLE S (S# CHAR (5) NOT NULL, SNAME CHAR (20), STATUS INT, CITY CHAR (15), PRIMARY KEY (S#) ) ; 7

CREATE TABLE Clause CONSTRAINT n n Clauses CONSTRAINT sont nouvelles dans SQL Permettent de

CREATE TABLE Clause CONSTRAINT n n 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 d’une table – Autres n 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 (Ms. Access) contrainte sur les valeurs d’un même tuple (DB-2) une par attribut (SQL-Server, DB 2) pas de sous-requêtes (SQL-Server) 8

CREATE TABLE (multibase) n On peut créer une table dans une autre base que

CREATE TABLE (multibase) n On peut créer une table dans une autre base que celle courante (ouverte) – SQL Server, SQL (seulement) de Ms. Access, SQL-2 CREATE TABLE AUTRE-BASE. S (S# CHAR (5) NOT NULL, SNAME CHAR (20), STATUS INT, CITY CHAR (15), PRIMARY KEY (S#) ) ; Base courante Autre-Base 9

ALTER & DROP TABLE ALTER TABLE S ADD DISCOUNT SMALLINT ; n certains systèmes:

ALTER & DROP TABLE ALTER TABLE S ADD DISCOUNT SMALLINT ; n certains systèmes: ALTER TABLE S DROP DISCOUNT SMALLINT ; ALTER TABLE S RENAME SNAME ; . . . DROP TABLE P ; 15

Indexes CREATE [UNIQUE] INDEX index ON table ( column [order] [, column. . .

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) ; n n UNIQUE = pas de duplicata de valeurs indexées indexes uniques obligatoires pour les clés dans le DB 2 16

Indexes n n Définition des indexes ne devait pas être à ce niveau de

Indexes n n 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 ? 18

Un dialecte de SQL-Ms. Access Le dialecte le plus répandu aujourd'hui n Définition de

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

Example: Table P de S-P • Attention: Type Counter -> Autonumber in Ms. Access-97

Example: Table P de S-P • Attention: Type Counter -> Autonumber in Ms. Access-97

Ms. Access: Types de Données n Text – limité par défaut à 50 caractères

Ms. Access: Types de Données n Text – limité par défaut à 50 caractères » clause FIELD SIZE permet 256 caractères – supporte les prédicats SQL & QBE n Memo – taille < 64 K caractères – supporte seulement la sélection SQL & QBE n 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 (Access 97) » 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 n Autonumber – compteur automatique ou OID (option random dans New. Values) 21

Ms. Access: Types de Données n Hyperlink – comme son l ’indique » nom

Ms. Access: Types de Données n Hyperlink – comme son l ’indique » nom symbolique < 2048 octets » URL ou UNC< 2048 octets » sous-adresse (dans le fichier ou la page) Cajun Delights#http: //www. cajundelights. com#Price » supporte seulement la sélection SQL & QBE n 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 22

Ms. Access: Champ Number & Currency clause Field size n n n Byte 0

Ms. Access: Champ Number & Currency clause Field size n n n 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. 402823 E 38 à 3. 402823 E 38. 4 octets. Double (Default) 10 digits de precision 1. 79769313486232 E 308 à 1. 79769313486232 E 308. 8 octets. Replication ID – – – Pour les bases dupliquées - 16 octets un OID peut être aussi dans le type Autonumber 23

Ms. Access: Champ Yes/No A utiliser comme son nom l ’indique – Yes/No ou

Ms. Access: 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 24

Ms. Access : domaines n On peut les simuler (en QBE) par : –

Ms. Access : domaines n 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 l’attribut 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 » ) 25

Ms. Access : surprises n n Seules valeurs apparaissant dans la 1 -ère colonne

Ms. Access : surprises n n Seules valeurs apparaissant dans la 1 -ère colonne du box et donc dans D peuvent être dans A – Même si l’on indique une autre « colonne liée » Type de données Assistant Liste de choix / Lookup Wizard réalise cette manipulation – – – n 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) 26

Ms. Access : surprises n La table peut hériter l’attribut A si l’on déclare:

Ms. Access : surprises n La table peut hériter l’attribut A si l’on déclare: – L’attribut héritant à le même nom que le 1èr attribut de D déclaré dans SELECT – L’attribut A est le 2 -ème dans SELECT – La 1 -ère longueur de colonne = 0 cm Dans notre exemple DB, SP peut ainsi hériter SNAME Qu’arrive t’il aux tuples existants si l’on sélectionne une autre colonne liée – Par ex. on utilise d’abord 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; 27

Ms. Access : CREATE TABLE CONSTRAINT = INDEX CREATE TABLE [Friends] ([First Name] TEXT,

Ms. Access : CREATE TABLE CONSTRAINT = INDEX CREATE TABLE [Friends] ([First Name] TEXT, [Last Name] TEXT); CREATE TABLE Friends 1 ([First Name] TEXT, [Last Name] TEXT, [Date of Birth] DATETIME, CONSTRAINT My. Table. Constraint UNIQUE ([First Name], [Last Name], [Date of Birth])); CREATE TABLE Friends 2 ([First Name] TEXT, [Last Name] TEXT, SSN INTEGER CONSTRAINT My. Field. Constraint PRIMARY KEY); n n 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 n Pas d'indexes CLUSTER sous Ms. Access 28

Ms. Access : CREATE TABLE CONSTRAINT = Contraintes d'intégrité n Contrainte sur attribut unique:

Ms. Access : CREATE TABLE CONSTRAINT = Contraintes d'intégrité n Contrainte sur attribut unique: CONSTRAINT nom {PRIMARY KEY | UNIQUE | NOT NULL | REFERENCES foreigntable [(foreignfield 1, foreignfield 2)]} n Contrainte sur plusieurs attributs: CONSTRAINT name {PRIMARY KEY (primary 1[, primary 2 [, . . . ]]) | UNIQUE (unique 1[, unique 2 [, . . . ]]) | NOT NULL (notnull 1[, notnull 2 [, . . . ]]) | FOREIGN KEY (ref 1[, ref 2 [, . . . ]]) REFERENCES foreigntable [(foreignfield 1 [, foreignfield 2 [, . . . ]])]} n n foreigntable = la table avec la clé primaire référencée le langage de définition de QBE d ’Ms. Access 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. 29

La table nommée P_1 est en fait la table P Les clauses CASCADE n

La table nommée P_1 est en fait la table P Les clauses CASCADE n ’existent qu’en QBE de Ms. Access 30

Exercice : que veulent dire ces contraintes ? Les clauses «is Null> dans les

Exercice : que veulent dire ces contraintes ? Les clauses «is Null> dans les Validation Rules sont-elles utiles ? 31

Sous-Tables en Ms. Access n Une table peut avoir une sous-table – dite sous-feuille

Sous-Tables en Ms. Access n Une table peut avoir une sous-table – dite sous-feuille La sous-table « auto » contient la clé étrangère de sa table (feuille) n Alternativement, la sous-table est choisie à travers un lien sémantique défini manuellement n – Table ou requête 32

Sous-Tables en Ms. Access n Dans la base SP – Table SP est automatiquement

Sous-Tables en Ms. Access n 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 Ms. Access – Les liens S -> SP -> S sont alors transitifs 33

Sous-Tables en Ms. Access n Dans la base SP – Soit la requête «

Sous-Tables en Ms. Access n 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 l’attribut dérivé d’UML pour SP – Le formulaire résultant apparaît comme entité structurée 34

Sous-Tables en Ms. Access n On crée une sous-table – Sur le menu propriétés

Sous-Tables en Ms. Access n On crée une sous-table – Sur le menu propriétés d’une 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 étendue – Sur le menu Insertion de la vue de la table ouverte » La sous-feuille est signalée par « + » seulement 35

Sous-Tables en Ms. Access Réalisation limitée d’une table à attributs hérités. Litwin, W. Ketabchi

Sous-Tables en Ms. Access Réalisation limitée d’une 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. 36

Sous-Table Requête 37

Sous-Table Requête 37

Sous-Table Requête Attribut dérivé 38

Sous-Table Requête Attribut dérivé 38

Sous-Tables Imbriquées 39

Sous-Tables Imbriquées 39

CREATE INDEX (MSAccess) Création d’un index qui n’a pas été fait par la clause

CREATE INDEX (MSAccess) Création d’un index qui n’a pas été fait par la clause de contrainte n Création d’un index qui ne peut pas être fait par la clause de contrainte n – Options d’ordre… Création alternative de la clé primaire n Création d’un pseudo-index sur la table liée n 40

CREATE INDEX (MSAccess) n Il existe l’alternative QBE – Commande Index dans le Menu

CREATE INDEX (MSAccess) n Il existe l’alternative QBE – Commande Index dans le Menu Affichage ou bouton droit » Quand la table est ouverte 42

SQL: manipulation de données n expression générale de sélection: SELECT [DISTINCT] attribut(s) FROM table(s)

SQL: manipulation de données n 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)] n n basée sur le calcul de tuple produit une table temporaire (en général avec des duplicata) 46

Examples En pratique sur Ms. Access n Sauf ceux spécifiques à n – SQL-Server

Examples En pratique sur Ms. Access n Sauf ceux spécifiques à n – SQL-Server – DB 2 47

Exemple canon S SP P 48

Exemple canon S SP P 48

Ms. Access SELECT [predicate] { * | table. * | [table. ]field 1 [,

Ms. Access SELECT [predicate] { * | table. * | [table. ]field 1 [, table. ]field 2. [, . . . ]]} [AS alias 1 [, alias 2 [, . . . ]]] FROM tableexpression [, . . . ] [IN externaldatabase] [WHERE. . . ] [GROUP BY. . . ] [HAVING. . . ] [ORDER BY. . . ] [WITH OWNERACCESS OPTION] Predicat: ALL, DISTINCTROW, TOP. 49

Interrogations (vraiment) simples Projections d'une table sur certains attributs: SELECT [S#] FROM S n

Interrogations (vraiment) simples Projections d'une table sur certains attributs: SELECT [S#] FROM S n S# s 1 s 2 s 3 s 4 s 5 Ms. Access Combien de lignes de programmation faudrait-il pour cette requête en Cobol ? 20 ? 50 ? 100 ? n L'ordre de présentation est fixé par le SGBD et peut changer dans le temps 50

Selection avec duplicata SELECT CITY FROM S; v. Le résultat peut avoir les duplicata

Selection avec duplicata SELECT CITY FROM S; v. Le résultat peut avoir les duplicata v alors, il n'est pas une relation, mais un bag 51

Elimination de duplicata SELECT DISTINCT CITY FROM S; CITY Athens London Paris Combien de

Elimination de duplicata SELECT DISTINCT CITY FROM S; CITY Athens London Paris Combien de lignes de programmation faudrait-il pour cette requête en C ? 20 ? 50 ? 100 ? n 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 n Les attributs apparaissent dans l’ordre de leur énumération dans la clause

Selections multiples n Les attributs apparaissent dans l’ordre de leur énumération dans la clause SELECT [S#], CITY, SNAME FROM S; S# City SName s 1 s 2 s 3 s 4 s 5 Paris Smith Paris Jones Paris Blake London Clark Athens Adam 53

SELECT * n Tout sur toutes les fournitures : SELECT S#, P#, QTY FROM

SELECT * n Tout sur toutes les fournitures : SELECT S#, P#, QTY FROM SP; n Formulation plus courante : SELECT * FROM SP; Ordre d'attributs est celui de CREATE TABLE MCette formulation est plus simple, mais deconseillée pour les programmes d'application pourquoi ? n 54

ORDER BY SELECT * FROM SP ORDER BY QTY DESC, [S#]; 55

ORDER BY SELECT * FROM SP ORDER BY QTY DESC, [S#]; 55

ORDER BY SELECT * FROM SP ORDER BY QTY DESC, [S#]; S# s 1

ORDER BY SELECT * FROM SP ORDER BY QTY DESC, [S#]; S# s 1 s 2 s 4 s 1 s 3 s 4 s 1 p# p 3 p 2 p 5 p 1 p 4 p 2 p 2 p 6 p 5 Qty 400 400 300 300 200 200 100 Combien de lignes de programmation faudrait-il pour cette requête en PL 1 ? 20 ? 50 ? 100 ? 56

ORDER BY SELECT * FROM SP ORDER BY QTY DESC, [S#]; S# s 1

ORDER BY SELECT * FROM SP ORDER BY QTY DESC, [S#]; S# s 1 s 2 s 4 s 1 s 3 s 4 s 1 p# p 3 p 2 p 5 p 1 p 4 p 2 p 2 p 6 p 5 Qty 400 400 300 300 200 200 100 Et la quantité nulle serait où ? 57

TOP SELECT top 3 [S#] AS [Les petits], [P#], QTY FROM SP ORDER BY

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

TOP SELECT top 3 [S#] AS [Les petits], [P#], QTY FROM SP ORDER BY QTY ASC, [S#] ; Les petits s 1 s 1 Product ID p 6 p 5 p 4 p 2 QTY 100 200 59

TOP SELECT top 3 [S#] AS [Les petits], [P#], QTY FROM SP ORDER BY

TOP SELECT top 3 [S#] AS [Les petits], [P#], QTY FROM SP ORDER BY QTY ASC, [S#] ; Les petits s 1 s 1 n Product ID p 6 p 5 p 4 p 2 QTY 100 200 Product ID ? Mot-clé utile, mais pas dans SQL standard (Ms. Access) $ - essaye de formuler cette requête en SQL standard n Pas de distinction entre les duplicata par rapport au critère d'ordre QTY, S# (les 3 tops sont devenus 4 tuples) 60

Restrictions simples SELECT [P#], PNAME FROM P WHERE COLOR = 'RED'; 61

Restrictions simples SELECT [P#], PNAME FROM P WHERE COLOR = 'RED'; 61

Restrictions simples SELECT [P#], PNAME FROM P WHERE COLOR = 'RED'; Product ID p

Restrictions simples SELECT [P#], PNAME FROM P WHERE COLOR = 'RED'; Product ID p 1 p 4 p 6 Product Name nuts screw cog Les noms d'attributs sont les légendes créées à la création de P n 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]… n 62

Restrictions composées n SELECT [P#], PNAME, CITY FROM P WHERE COLOR = 'RED' AND

Restrictions composées n SELECT [P#], PNAME, CITY FROM P WHERE COLOR = 'RED' AND NOT CITY = 'PARIS'; 63

Restrictions composées n SELECT [P#], PNAME, CITY FROM P WHERE COLOR = 'RED' AND

Restrictions composées n SELECT [P#], PNAME, CITY FROM P WHERE COLOR = 'RED' AND NOT CITY = 'PARIS'; Product ID p 1 p 4 p 6 Product Name nuts screw cog city london On peut utiliser les opérateurs AND, OR, NOT ainsi que IMP et XOR 64

Restrictions composées n Les formulations : SELECT [P#], PNAME, CITY FROM P WHERE COLOR

Restrictions composées n 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 d’exécution. n Bien qu’elles soient logiquement équivalentes n Notamment si les deux attributs sont indexés n Il vaut mieux mettre d’abord la clause en général plus sélective – vraie pour moins de tuples (ou aucun si possible) 65

Restrictions sur nuls Un nul n’est pas une valeur n Donc on a une

Restrictions sur nuls Un nul n’est pas une valeur n Donc on a une clause spéciale n n – 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. City FROM S where city is null; CITY A noter: DISTINCT s ’applique aux nuls (à tort, pourquoi ? ) Vous avez dit bizarre pour la table de nuls ? 66

Restrictions par clause IN SELECT [s#], city FROM S where city IN ('paris', 'london');

Restrictions par clause IN SELECT [s#], city FROM S where city IN ('paris', 'london'); s# s 1 s 2 s 4 s 6 s 7 city Paris London Paris S# s 1 s 2 s 3 s 4 s 5 s 6 s 7 SName John smith Blake Clark Adam Bull Ibm Status 2 10 30 20 100 City Paris London Athens Paris ? 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

Expressions de valeur SELECT [P#], PNAME, 2. 1* weight as [Poids £ ] FROM P order by 2. 1*weight desc; Product ID p 6 p 3 p 2 p 4 p 5 p 1 Product Name cog screw bolt screw cam nuts Poids £ 39. 9 35. 7 29. 4 25. 2 68

Expressions de valeur n En général on peut employer les opérateurs: + - *

Expressions de valeur n En général on peut employer les opérateurs: + - * / ^ et ( ) – - peut être un-aire n On dispose aussi sous Ms. Access de : » modulo : A mod c » division entière symbolisée par SELECT S. SName, [S]. [Status]/9 AS Div, [status]9 AS [Div ent], S. Status, [status] Mod 9 AS Mod, -[status] AS moins, S. City FROM S WHERE [status]=20 Xor [city]="paris" ; 69

Expressions de valeur n On peut sélectionner tous les attributs et une expression de

Expressions de valeur n On peut sélectionner tous les attributs et une expression de valeur SELECT *, 2. 1*weight as [Poids en KG], weight + weight/5 - (weight^2 - weight*2. 1) as [un jeu] FROM P order by 2. 1*weight desc; n On peut utiliser une expression de valeur comme argument d’une clause de restriction …. WHERE WEIGTH = 200 *2, 1 n On peut créer les expressions de valeur sur les attributs dynamiques SELECT sp. qty AS q, q+2 AS q 1, log(q 1)+3 AS q 2 FROM sp; 70

Expressions de valeur n On ne peut pas utiliser dans ORDER BY l’alias défini

Expressions de valeur n On ne peut pas utiliser dans ORDER BY l’alias 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 <> 0 n La 1ère équation est d’importance par exemple pour les requêtes où un attribut X sélectionné pour SELECT … X*QTY… pourraient avoir la valeur 0. 71

Jointures Brancusi Raphaël 72

Jointures Brancusi Raphaël 72

Jointures SELECT distinct S. [S#], SNAME, [P#], Qty, City FROM S, SP where s.

Jointures SELECT distinct S. [S#], SNAME, [P#], Qty, City FROM S, SP where s. [s#]=sp. [s#] and city <> 'London'; 73

Equi-jointures SELECT distinct S. [S#], SNAME, [P#], Qty, City FROM S, SP where s.

Equi-jointures SELECT distinct S. [S#], SNAME, [P#], Qty, City FROM S, SP where s. [s#]=sp. [s#] and city <> 'London'; S# s 2 s 3 SNAME Jones Blake Product ID p 1 p 2 Qty 300 400 200 City Paris 74

Equi-jointures m-aires SELECT s. [s#], p. [P#], Qty, Pname FROM S, SP, P where

Equi-jointures m-aires SELECT s. [s#], p. [P#], Qty, Pname FROM S, SP, P where s. [s#]=sp. [s#] and sp. [p#]=p. [p#] and s. [s#] between 's 1' and 's 3' order by s. [S#], qty desc; 79

Equi-jointures m-aires SELECT s. [s#], p. [P#], Qty, Pname FROM S, SP, P where

Equi-jointures m-aires SELECT s. [s#], p. [P#], Qty, Pname FROM S, SP, P where s. [s#]=sp. [s#] and sp. [p#]=p. [p#] and s. [s#] between 's 1' and 's 3' order by s. [S#], qty desc; s# Product ID s 1 p 3 s 1 p 1 s 1 p 4 s 1 p 2 s 1 p 6 s 1 p 5 s 2 p 2 s 2 p 1 s 3 p 2 Qty 400 300 200 100 400 300 200 Product Name screw nuts screw bolt cog cam bolt nuts bolt 80

Clause BETWEEN n Le type d ’attribut détermine l évaluation de la clause: –

Clause BETWEEN n 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 ? 81

Equi-jointures m-aires (avec *) n Tous les attributs de toutes les tables dans la

Equi-jointures m-aires (avec *) n Tous les attributs de toutes les tables dans la clause FROM SELECT * FROM S, SP, P where s. [s#]=sp. [s#] and p. [p#]=sp. [p#] and s. city <> 'London'; n On peut aussi SELECT S. *, SP. *, P. * FROM S, SP, P bien-sûr n On peut ajouter des attributs additionnels SELECT *, 'Mecs d’Eurostar' as [D'ou viennent t'ils ? ] FROM S, SP, P where s. [s#]=sp. [s#] and p. [p#]=sp. [p#] and s. city <> 'London'; 82

Equi-jointures peuvent être formulées pour tout attribut: : Mais, les types de données à

Equi-jointures peuvent être formulées pour tout attribut: : Mais, les types de données à joindre doivent être = compatibles n SELECT s. [s#], p. [P#], Qty, Pname, s. city, p. city FROM S, SP, P where s. [s#]=sp. [s#] and sp. [p#]=p. [p#] and s. city=p. city order by s. city, s. [s#]; s# Product ID s 1 p 6 s 1 p 4 s 1 p 1 s 4 p 4 s 2 p 2 s 3 p 2 Qty 100 200 300 400 200 Product Name cog screw nuts screw bolt S. city London Paris P. city london paris 83

Equi-jointures dans la clause FROM n n Possibilité nouvelle dans SQL 2 (et Ms.

Equi-jointures dans la clause FROM n n Possibilité nouvelle dans SQL 2 (et Ms. Access) Prévue dans le nouvel SQL standard – SQL-2 n Permet de standardiser la formulation de jointures externes – On les verra plus tard n Permet aussi de fixer explicitement l’ordre de jointures – Pour optimiser la requête 84

Equi-jointures dans la clause FROM SELECT S. [S#], P. [p#], SP. Qty, PName, S.

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 s 1 p 6 s 1 p 4 s 1 p 1 s 4 p 4 s 2 p 2 s 3 p 2 Qty 100 200 300 400 200 Product Name cog screw nuts screw bolt S. city London Paris P. city london paris 85

Theta-jointures & Self-jointures n n L'opérateur T de comparaison dans une clause de jointure

Theta-jointures & Self-jointures n n L'opérateur T de comparaison dans une clause de jointure peut-être en fait : – T =, <, <= >, >= <>} Une table peut-être jointe avec elle-même – On suppose que les noms de fournisseurs sont tos différents SELECT s 1. [s#], s 1. sname, s 2. [s#], s 2. sname, s 1. city FROM s s 1, s s 2 /* s 1, s 2 sont des aliases WHERE s 1. city = s 2. city and s 1. sname < s 2. sname; s 1. s# s 4 s 3 s 1. sname Clark Blake s 2. s# s 1 s 2. sname Smith Jones city London Paris 86

Jointures externes Conserve les tuples sans corresp. sur les attributs de jointure - jointure

Jointures externes Conserve les tuples sans corresp. sur les attributs de jointure - jointure gauche (LEFT) conserve les tuples à gauche s 5 - jointure droite (RIGHT) conserve les tuples à droite n s 7 p 6 100 SELECT S. [S#], city, SP. Qty FROM S LEFT JOIN SP ON S. [S#] = SP. [S#] where (qty > 200 or qty is null) and not city = 'london'; S# s 2 s 5 city Qty Paris 300 Paris 400 Athens 87

Jointures externes (propriétés algébriques) v Les jointures classiques dites internes sont associatives v Celle

Jointures externes (propriétés algébriques) v Les jointures classiques dites internes sont associatives v Celle externes ne sont pas ü A démontrer v La notation dans la clause WHERE pourrait être ambiguë ü Pourquoi ? Ø D’où la notation algébrique dans la clause FROM Ø Introduite par SQL-2 Ø Elles s’appliquent aussi aux jointures classiques dites dès lors internes 88

Jointure externe complète SELECT pname, S. SName, S. City, P. City FROM P RIGHT

Jointure externe complète SELECT pname, S. SName, S. City, P. City FROM P RIGHT JOIN S on P. City = S. City union SELECT pname, S. SName, S. City, P. City FROM P left JOIN S ON P. City = S. City ; pname SName S. City P. City Adams Athens bolt Blake Paris paris bolt Jones Paris paris bolt smith Paris paris cam Blake Paris paris cam Jones Paris paris cam smith Paris paris cog Clark London london nuts Clark London london screw rome screw Clark London london 89

Jointure externe complète avec une sélection SELECT pname, S. SName, S. City, P. City

Jointure externe complète avec une sélection SELECT pname, S. SName, S. City, P. City FROM P RIGHT JOIN S ON P. City = S. City where p. city <> 'london' or p. city is null UNION SELECT pname, S. SName, S. City, P. City FROM P left JOIN S ON P. City = S. City where s. city <> 'london' or s. city is null; pname SName Adams bolt Blake bolt Jones bolt smith cam Blake cam Jones cam smith screw S. City Athens Paris Paris P. City paris paris rome 90

Jointure externe self ou avec theta-comparaison n Self-jointure externe est possible – p. e.

Jointure externe self ou avec theta-comparaison n 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 d’un autre fournisseur ou est inconnue n Les opérateurs T s’appliquent aussi aux jointures externes T =, <, <= >, >= <>} Ø N°s de tout fournisseur qui fournit une pièce en quantité moindre qu’un autre fournisseur d’une même pièce ou en quantité inconnue. 91

Jointures externes DB 2 & SQL-Server & SQL-2… n On utilise les déclarations –

Jointures externes DB 2 & SQL-Server & SQL-2… n 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. City where p. city <> 'london' or p. city is null 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 (+) pname SName Adams bolt Blake bolt Jones bolt smith cam Blake cam Jones cam smith screw S. City Athens Paris Paris P. City paris paris rome 92

Mélange de jointures externes et internes M Explosif (sous Ms. Access surtout): J OK:

Mélange de jointures externes et internes M Explosif (sous Ms. Access surtout): J OK: SELECT s. P. Qty, s. [S#], s. City, s. P. [p#] FROM s RIGHT JOIN (p INNER JOIN s. P ON p. [P#] = s. P. [p#]) ON s. P. [S#] = s. [S#]; L interdit : SELECT s. P. Qty, s. [S#], s. City, s. P. [p#] FROM s LEFT JOIN (p INNER JOIN s. P ON p. [P#] = s. P. [p#]) ON s. P. [S#] = s. [S#]; 93

Mélange de jointures externes et internes En deux requêtes c'est OK par contre: Query-scratch

Mélange de jointures externes et internes En deux requêtes c'est OK par contre: Query-scratch 1: SELECT * FROM p INNER JOIN sp ON p. [P#] = sp. [p#]; : SELECT s. [s#], qty, [Query-scratch 1]. color FROM s left JOIN [Query-scratch 1] ON [Query scratch 1]. [S#] = S. [S#]; n 94

Résultat s# qty s 1 100 s 1 200 s 1 400 s 1

Résultat s# qty s 1 100 s 1 200 s 1 400 s 1 200 s 1 300 s 2 400 s 2 300 s 3 200 s 4 400 s 4 300 s 4 200 s 5 color red blue green red green blue red green 95

Jointures implicites Simplifient la formulation de la requête n Générées par Ms. Access à

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

Declaration de jointures implicites On a cliqué d'abord ici On a cliqué ensuite ici

Declaration de jointures implicites On a cliqué d'abord ici On a cliqué ensuite ici

Formulation de la requête avec les jointures implicites en QBE Puis, clique et. .

Formulation de la requête avec les jointures implicites en QBE Puis, clique et. . . Tires avec la souris, Ecris la restriction

Résultat SQL SELECT DISTINCTROW S. SName, P. City FROM P INNER JOIN (S INNER

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#] Jointure impl. WHERE ((P. City="paris")); générée automatiquement 99

Limitations n Si les tables choisies ne sont pas directement en relation, alors, il

Limitations n 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 l’on ne sélectionne aucun attribut de cette table 100

Limitations n L’ordre de clauses résulte de celui de sélection de tables, mais seulement

Limitations n L’ordre de clauses résulte de celui de sélection de tables, mais seulement si l’on suit les relations directes. – Essayez ajouter les tables et regarder le résultat SQL, selon les permutations suivants: » P, S puis S, SP, P, puis P, S, SP puis S, P, SP – Conclusion ? 101

Limitations n Une correspondance déclarée entre les attributs d'une même relation ne génère pas

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

103

103

Limitations n Une correspondance multiple entre deux tables – donne lieu à AND entre

Limitations n 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 n C'est un "bug" – Ms. Access 2 n Pourquoi ? – bonne question à Microsoft n 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. 104

105

105

106

106

107

107

108

108

109

109

110

110

Jointure Automatique Une jointure implicite entre deux attributs de deux tables différentes sélectionnées pour

Jointure Automatique Une jointure implicite entre deux attributs de deux tables différentes sélectionnées pour une requête : – – n n sans lien sémantique dans le schéma de type compatible d’un même nom avec au moins étant la clé primaire A ne pas confondre avec une self-jointure Existent dans Ms. Access 2000 – en option 111

Fonctions agrégats n Un nombre très limité: – COUNT, SUM, AVG, MAX, MIN, »

Fonctions agrégats n Un nombre très limité: – COUNT, SUM, AVG, MAX, MIN, » MIN, MAX s’applique aux Nuls ( à tort) » Ms. Access: St. Dev, Var, First, Last » Ms. Access 97: Var. P, St. Dev. P – calcul sur la population, pendant que Var, St. Dev utilisent un échantillon n A mettre dans SELECT sum(P. Weight) AS Poids. Cumule FROM P; Poids. Cumule 91 114

Fonctions agrégats n SELECT Count (*) FROM S WHERE… compte tous les tuples n

Fonctions agrégats n SELECT Count (*) FROM S WHERE… compte tous les tuples n SELECT Count (CITY) FROM S ne compte pas de nulls – mais compte les doubles n SELECT COUNT (DISTINCT (CITY)) FROM S; – Possible avec SQL ANSI, mais pas Ms. Access » Pourquoi ? - Très bonne question à Microsoft – Possible dans SQL-Server ou DB 2 ? – Formulable autrement avec Ms. Access ? n On peut compter sur plusieurs champs, pourvus qu'ils ne soient pas tous nuls dans le tuple (Ms. Access) SELECT Count ("City & Status") FROM S; $ Compte les fournisseurs sans ville connue $ Compte le nombre de villes avec un fournisseur (Ms. Access) 115

Fonctions agrégats SELECT Varp(SP. Qty) AS Varp, Var(SP. Qty) AS Var, St. Dev(SP. Qty)

Fonctions agrégats SELECT Varp(SP. Qty) AS Varp, Var(SP. Qty) AS Var, St. Dev(SP. Qty) AS St. Dev, St. Devp(SP. Qty) AS St. Devp FROM SP; Varp Var St. Devp 15644. 6280991736 17209. 090909 131. 183424673588 125. 078487755383 116

GROUP BY n n n Permet d'appliquer les fonctions agrégats aux sous -tables, dites

GROUP BY n n n 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) n A été introduite par Peter Gray d'Univ. d'Aberdeen (je crois). 117

GROUP BY SELECT top 50 percent [p#], sum (qty) as [tot-qty] from sp GROUP

GROUP BY SELECT top 50 percent [p#], sum (qty) as [tot-qty] from sp GROUP BY [p#] Order by sum (qty) desc; p# p 2 p 1 p 5 p 4 tot-qty 1000 600 500 118

GROUP BY attributs multiples n Tous les attributs sélectionnés non-agrégés forment le GROUP BY

GROUP BY attributs multiples n 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 > 100 GROUP BY S. SName, S. [S#] Sname Clark Jones Smith Somme 900 700 200 1100 S# s 4 s 2 s 3 s 1 119

HAVING n Permet de spécifier les prédicats sur les groupes de GROUP BY –

HAVING n 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 [p#] HAVING SUM (QTY) > 200 ORDER BY SUM (QTY) DESC; p# p 2 p 1 p 5 p 4 p 3 tot-qty 1000 600 500 400 120

Sous-requêtes n n Une expression alternative de jointures Permet une optimisation manuelle – la

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

Sous-requêtes SELECT [s#], sname from S where s. [s#] in (select [s#] from sp

Sous-requêtes SELECT [s#], sname from S where s. [s#] in (select [s#] from sp where qty > 200); SELECT [s#], sname, status from S where s. status = (select max (status) from s as S 1); s# s 1 s 3 s 5 sname Smith Blake Adams status 30 30 30 122

Sous-requêtes n La requête à sous-requête : SELECT [S#], SNAME FROM S WHERE STATUS

Sous-requêtes n La requête à sous-requête : SELECT [S#], SNAME FROM S WHERE STATUS > 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 > 100 AND CITY = ‘PARIS’; n Le temps d’exécution est plus petit. – – – n Si les deux attributs sont indexés La plupart de fournisseurs est à Paris Il y a peu de fournisseurs de Statut > 100 Bien que les deux requêtes soient logiquement équivalentes 123

EXISTS SELECT [s#], sname, status from S where exists (select * from sp where

EXISTS SELECT [s#], sname, status from S where exists (select * from sp where [s#]=sp. [s#] and sp. [p#]='p 2'); s# s 1 s 2 s 3 s 4 s 5 sname Smith Jones Blake Clark Adams status 30 10 30 20 30 124

FORALL <-> NOT (NOT EXISTS) SELECT [s#], sname from S where not exists (select

FORALL <-> NOT (NOT EXISTS) SELECT [s#], sname from S where not exists (select * from p where not exists ( select * from sp where [s#]=s. [s#] and [p#]=p. [p#] )); s# s 1 sname Smith 125

NOT. . . NOT EXISTS SELECT distinct [s#] from SP X where not exists

NOT. . . NOT EXISTS SELECT distinct [s#] from SP X where not exists (select * from sp y where [s#]='s 2' and not exists (select * from sp z where z. [s#]=x. [s#] and z. [p#]=y. [p#] )); s# s 1 s 2 % C'est quoi ? % Tous les fournisseurs qui fournissent au moins les pièces du fournisseur 'S 2'. 126

NOT. . . NOT EXISTS SELECT distinct [s#] from SP X where not exists

NOT. . . NOT EXISTS SELECT distinct [s#] from SP X where not exists (select * from sp y where [s#]='s 2' and not exists (select * from sp z where z. [s#]=x. [s#] and z. [p#]=y. [p#] )); s# SQL c'est simple s 1 car non-procedural: s 2 une intention = une requête % C'est quoi ? % Tous les fournisseurs qui fournissent au moins les pièces du fournisseur 'S 2'. 127

UNION SELECT [P#], CITY FROM P WHERE CITY LIKE '[L-S]' UNION SELECT [P#], CITY

UNION SELECT [P#], CITY FROM P WHERE CITY LIKE '[L-S]' UNION SELECT [P#], CITY FROM SP, S WHERE SP. [S#]=S. [S#] AND CITY >= 'B'; 128

UNION SELECT [P#], CITY FROM P WHERE CITY LIKE '[L-S]' UNION SELECT [P#], CITY

UNION SELECT [P#], CITY FROM P WHERE CITY LIKE '[L-S]' UNION SELECT [P#], CITY FROM SP, S WHERE SP. [S#]=S. [S#] AND CITY >= 'B'; P# p 1 p 2 p 3 p 4 p 5 p 6 city London Paris London Tous les duplicata sont éliminés Comment faire alors pour les agrégats ? 129

UNION Ms. Access n Les tables ou vues entières union-compatibles peuvent être référencées explicitement

UNION Ms. Access n Les tables ou vues entières union-compatibles peuvent être référencées explicitement TABLE Customers UNION TABLE Suppliers n On ne peut pas sélectionner d’attributs de type MEMO ou OLE – Y compris par * » Déjà déconseillé pour les programmes d’application n Pas d opérateurs INTERSECT, EXCEPT $ Comment faire alors ? 130

UNION ALL Préserve les duplicata n Nécessaire pour appliquer des agrégations n Mais, souvent

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

UNION ALL SELECT weight, p. city FROM P WHERE City like 'l*' UNION ALL

UNION ALL SELECT weight, p. city FROM P WHERE City like 'l*' UNION ALL SELECT weight, s. city FROM p, SP, S WHERE p. [p#]=sp. [p#] and sp. [s#]=s. [s#] and s. City like 'p*'; weight 12 14 19 12 17 17 city london Paris 132

UNION ALL SELECT weight, p. city FROM P WHERE City like 'l*' UNION ALL

UNION ALL SELECT weight, p. city FROM P WHERE City like 'l*' UNION ALL SELECT weight, s. city FROM p, SP, S WHERE p. [p#]=sp. [p#] and sp. [s#]=s. [s#] and s. City like 'p*'; weight 12 14 19 12 17 17 city london Paris SELECT AVG(WEIGHT) AS [AVG POIDS], VAR(WEIGHT) AS [VAR-POIDS], MAX(WEIGHT) AS [POIDS-MAX] FROM [UNION-ALL]; avg poids var poids-max 15. 16666667 8. 56666667 19 133

Clause FROM imbriquée n Définit une table dans la clause FROM d’une expression de

Clause FROM imbriquée n Définit une table dans la clause FROM d’une expression de sélection SQL (SQL-Select) – Cette dernière peut-être imbriquée à son tour n Select attrs…FROM [tbls], (SQL-Select) Where …. ; Clause non-documentée sous Ms. Access – La traduction SQL-QBE est boguée » À essayer 134

Clause FROM imbriquée Possibilités: – Agrégations par-dessus UNION ou UNION ALL – Imbrication des

Clause FROM imbriquée Possibilités: – Agrégations par-dessus UNION ou UNION ALL – Imbrication des expressions de valeur – Calcul de COUNT (DISTINCT) » Ms. Access – Récursivité limitée 135

Clause FROM imbriquée SELECT sum(weight) AS [poids-total] FROM (SELECT weight, p. city FROM P

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. [p#]=sp. [p#] and sp. [s#]=s. [s#] and s. City like 'p*'); 136

Clause FROM imbriquée select avg(moy 1) as [moyenne-des-moyennes] FROM (SELECT avg(weight) as moy 1

Clause FROM imbriquée select avg(moy 1) as [moyenne-des-moyennes] FROM (SELECT avg(weight) as moy 1 FROM P WHERE City like 'l*' UNION ALL SELECT avg(weight) as moy 1 FROM p, SP, S WHERE p. [p#]=sp. [p#] and sp. [s#]=s. [s#] and s. City like 'p*'); 137

Requêtes à paramètres n un paramètre : un [texte visualisé] dans la clause WHERE

Requêtes à paramètres n un paramètre : un [texte visualisé] dans la clause WHERE pour que l'usager indique une valeur – le texte peut être sans [], s’il ne désigne pas d’attribut et n ’a pas de blancs, # etc. » Possibilité à éviter à cause de conflit de noms possible – "Paris" est une constante Paris serait un paramètre n n 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) 138

Requêtes à paramètres n On peut utiliser plusieurs paramètres – pour une clause BETWEEN

Requêtes à paramètres n On peut utiliser plusieurs paramètres – pour une clause BETWEEN [Qty Min ? ] AND [Max ? ] n On peut utiliser la clause LIKE [City ? ] n 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 n Alternativement on peut ajouter les caractères génériques à la réponse d'usager – P. e. LIKE [City ? ] & "*" 139

Requêtes à paramètres n Le nom dans le paramètre a la priorité sur le

Requêtes à paramètres n 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] Long; SELECT P. Weight, p. Weight+3/2, P. Color FROM P WHERE p. Weight+3/2 > weight and weight + 6 > p. weight; On évite le conflit par l’emploi de P. Weight Note: il n ’y a ci-dessus qu’un seul param. weight ? Est-ce que c’est la même requête n n PARAMETERS [weight] Long; SELECT Weight, Weight+3/2, P. Color FROM P WHERE Weight+3/2 > [weight] and [weight] + 6 > weight; 140

Requêtes à paramètres q Expression de paramètre peut être celle de valeur …WHERE. .

Requêtes à paramètres q 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 > [100] GROUP BY S. SName, S. [S#] 141

Fonctions scalaires n S ’appliquent aux valeurs individuelles – d ’attributs – d agrégations

Fonctions scalaires n S ’appliquent aux valeurs individuelles – d ’attributs – d agrégations SQL n Il y a plusieurs catégories – – mathématiques financières chaînes de caractères dates… » voir le cours « SQL Subtilités » n Varient entre les dialectes – Ms. Access possède UCASE, pas DB 2 – DB 2 possède LOG 10, pas Ms. Access 142

Fonctions scalaires n Peuvent s’imbriquer – contrairement aux agrégats SQL SELECT log((sum([qty]^2)^(1/2))) as exemple

Fonctions scalaires n Peuvent s’imbriquer – contrairement aux agrégats SQL SELECT log((sum([qty]^2)^(1/2))) as exemple FROM SP group by [p#] having int(log(sum([qty]))) = 5 exemple 5. 70875764008279 5. 99146454710798 143

Tabulations Croisées (Crosstab queries) n Présentent les résultat sous forme habituelle de feuilles de

Tabulations Croisées (Crosstab queries) n 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 n Transforment les valeurs d'attributs en attributs – Par exemple » les valeurs de P# trouvés pour un même S# deviennent les attributs P 1, P 2, . . . » les valeurs de P 1, P 2. . sont les QTY (par ex. ) correspondants 144

Tabulations Croisées TRANSFORM Sum(SP. Qty) SELECT SP. [S#], Sum(SP. Qty) AS [Total Qty] FROM

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 145

Tabulations Croisées S# Total Qty p 1 p 2 p 3 p 4 p

Tabulations Croisées S# Total Qty p 1 p 2 p 3 p 4 p 5 p 6 s 1 s 2 s 3 s 4 300 200 400 200 100 300 400 1300 700 200 900 L'intitulé Total Qty est mis par défaut par Ms. Access 146

Tabulations Croisées n La fonction agrégat dans la clause TRANSFORM est obligatoire – bien

Tabulations Croisées n La fonction agrégat dans la clause TRANSFORM est obligatoire – bien que SUM(QTY) = AVG(QTY) = QTY – mais, COUNT(QTY) = 1 n On peut générer une expression de valeur TRANSFORM SUM(0. 5*QTY) AS [Q 2] SELECT Sum(SP. [Q 2]) AS [Qte tot. dans 1 mois], Avg(P. [Q 2]) AS [Qte moy. dans 1 mois] FROM SP GROUP BY SP. [S#] PIVOT SP. [p#]; 147

Tabulations Croisées On peut utiliser la clause WHERE P# IN ('P 1', 'P 2')

Tabulations Croisées On peut utiliser la clause WHERE P# IN ('P 1', 'P 2') n n Alors les fonctions ne calculent les agrégats que sur P 1 et P 2. On peut aussi restreindre la tabulation seulement PIVOT SP. [p#] IN ('P 1', P 2') n Mais, cette clause n'affecte pas les calculs des agrégats ü Peut-on appliquer la clause ORDER BY ? ü Si oui, quel serait l’effet 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 ? 148

Mise à jour update P set color = 'green', weight = weight+10, city =

Mise à jour update P set color = 'green', weight = weight+10, city = null where [p#] < 'p 3'; inconsistance / à la sémantique de nuls pour les l'interrogations 179

Mise à jour update P set color = 'green', weight = weight+10, city =

Mise à jour update P set color = 'green', weight = weight+10, city = null where [p#] < 'p 3'; inconsistance / à la sémantique de nuls pour les l'interrogations update SP une sous-requête set qty = '10' est nécessaire where 20 = (select status from S where s. [s#]=sp. [s#]) ; 180

Mise à jour Transfert de fonds update Account 1 set balance = balance -

Mise à jour Transfert de fonds update Account 1 set balance = balance - 100 where [c#] = '123'; update Account 2 set balance = balance + 100 where [c#] = '123'; n M - et si une de requêtes se casse ? – il faut des transactions 181

DELETE n Comme pour UPDATE: DELETE [*] FROM table [ WHERE condition ] ;

DELETE n Comme pour UPDATE: DELETE [*] FROM table [ WHERE condition ] ; n On retrouve aussi le besoin de transactions Notamment pour l'intégrité référentielle + et + souvent gérée par SGBD à partir de la déclaration dans LDD (ex. Ms. Access) 182

INSERT INTO P VALUES ('P 8', 'nut', 'pink', 15, 'Nice') ; n pas bonne

INSERT INTO P VALUES ('P 8', 'nut', 'pink', 15, 'Nice') ; n pas bonne idée d'utiliser cette forme d'INSERT INTO P (weight, [P#] ) VALUES ( 100, 'P 8') ; n les valeurs non-sélectionnées ne doivent pas être non-nulles INSERT INTO TEMP ([P#], TQTY) SELECT (P#, SUM (QTY) FROM SP GROUP BY [P#] n TEMP doit être préalablement crée ? Avec ou sans clé primaire ? Quelle différence pour INSERT 183

INSERT n SELECT. . . INTO de Ms. Access est équivalent à simple INSERT

INSERT n SELECT. . . INTO de Ms. Access est équivalent à simple INSERT précédé de CREATE TABLE SELECT field 1[, field 2[, . . . ]] INTO newtable [IN externaldatabase] FROM source SELECT S. [S#], S. SName INTO [db 2. mdb]. s 1 FROM S WHERE ((S. SName<>'paris')); n + en + souvent le SGBD peut vérifier pour INSERT l'intégrité référentielle (Ms. Access) 184

MAJ & Vues n On peut mettre à jour une table à travers sa

MAJ & Vues n On peut mettre à jour une table à travers sa vue – Toute vue incluant la clé primaire – Pas d’expression de valeur sur les attributs MAJ n A expérimenter sur Ms. Access 185

Conclusion n. SQL est un langage assertionnel – relationnellement complet – + expressions de

Conclusion n. SQL est un langage assertionnel – relationnellement complet – + expressions de valeur et agrégats – + mises à jour n. Mais ce n'est pas un langage de programmation complet n. Il y a des défauts de conception et inconsistances n. Néanmoins c'est un MUST pour un informaticien aujourd'hui n. On voit, néanmoins aussi que SQL n'est pas le langage pour les usagers ad-hoc ! n. C'est n'est pas tout pour SQL, il y en a encore ! 186

FIN 187

FIN 187

188

188