4 Le langage SQL Structured Query Language n

  • Slides: 105
Download presentation
4 Le langage SQL Structured Query Language n Norme établie pour SGBD relationnel n

4 Le langage SQL Structured Query Language n Norme établie pour SGBD relationnel n Partie LDD n – – – n Conceptuel : CREATE SCHEMA, TABLE, . . . Externe : CREATE VIEW, GRANT, … Interne : CREATE INDEX, CLUSTER, . . . Partie LMD – 10/09/2021 SELECT, INSERT, DELETE, UPDATE © Robert Godin. Tous droits réservés. 1

Quelques liens n n n Standard SQL: http: //www. jcc. com/SQLPages/jccs_sql. htm http: //www.

Quelques liens n n n Standard SQL: http: //www. jcc. com/SQLPages/jccs_sql. htm http: //www. cssinfo. com/ncitsgate. html Tutoriel et liens: http: //www. contrib. andrew. cmu. edu/~shadow/sql. html Syntaxe SQL 2 : http: //www. contrib. andrew. cmu. edu/~shadow/sql 2 bnf. aug 92. txt Syntaxe SQL: 1999 : http: //www. mkp. com/sql 99 http: //www. sql-zone. com/ 10/09/2021 © Robert Godin. Tous droits réservés. 2

Origine n IBM Research (San Jose) – – n SQUARE – – n développement

Origine n IBM Research (San Jose) – – n SQUARE – – n développement du prototype System/R (Astrahan et al. , 1976) algèbre relationnelle ~ anglais (Boyce, Chamberlin, King & Hammer, 1975) SEQUEL – – 10/09/2021 Structured English QUEry Language (Chamberlin, Astrahan, Eswaran, Chamberlin, Griffiths & Lorie, 1976) © Robert Godin. Tous droits réservés. 3

Standard ANSI/ISO n SQL 86 – n SQL 89 (SQL, SQL 1) – n

Standard ANSI/ISO n SQL 86 – n SQL 89 (SQL, SQL 1) – n niveau minimal supporté SQL 92 (SQL 2) – – – n version préliminaire support accru de l ’intégrité trois niveaux : entrée (SQL 1+), intermédiaire, complet de plus en plus supporté SQL: 1999 (SQL 3) – – 10/09/2021 extensions objet (UDT), TRIGGER, ROLE, SQL/PSM, . . . support très variable © Robert Godin. Tous droits réservés. 4

4. 1 Spécification schéma relationnel avec SQL (LDD SQL) n Niveau conceptuel – Schéma

4. 1 Spécification schéma relationnel avec SQL (LDD SQL) n Niveau conceptuel – Schéma des tables (TABLE) n – CREATE TABLE. Domaines (DOMAIN) n SQL 2 – Contraintes d'intégrité n 10/09/2021 intermédiaire : CREATE DOMAIN PRIMARY KEY, FOREIGN KEY, UNIQUE KEY, CHECK, ASSERTION, TRIGGER © Robert Godin. Tous droits réservés. 5

Spécification schéma relationnel avec SQL (suite) n Niveau externe – Vues (VIEW) n –

Spécification schéma relationnel avec SQL (suite) n Niveau externe – Vues (VIEW) n – définie à partir d'autres tables (CREATE VIEW). Elle apparaît à l'utilisateur comme une table normale alors qu'elle est en réalité dérivée à partir d'autres tables normales ou virtuelles. Privilèges d'accès n GRANT n Niveau interne – 10/09/2021 non standardisé (e. g. CREATE INDEX) © Robert Godin. Tous droits réservés. 6

4. 1. 1 Environnement, catalogue, schéma et utilisateur SQL 10/09/2021 © Robert Godin. Tous

4. 1. 1 Environnement, catalogue, schéma et utilisateur SQL 10/09/2021 © Robert Godin. Tous droits réservés. 7

Utilisateur (user) n Identificateur d ’utilisateur – – n Mécanisme d ’authentification – n

Utilisateur (user) n Identificateur d ’utilisateur – – n Mécanisme d ’authentification – n authorization. ID non standardisé e. g. mot de passe Utilisateur possède privilèges 10/09/2021 © Robert Godin. Tous droits réservés. 8

Dialecte Oracle n DATABASE ~ catalogue – n Nom du SCHEMA – n une

Dialecte Oracle n DATABASE ~ catalogue – n Nom du SCHEMA – n une instance Oracle monte une DATABASE à la fois = authorization. ID du propriétaire CLUSTER Oracle – – 10/09/2021 cluster de l ’environnement SQL 2 méthode d ’organisation de données par grappe © Robert Godin. Tous droits réservés. 9

Agent (client), serveur, session SQL n CONNECT spécifie – – – 10/09/2021 – identification/authentification

Agent (client), serveur, session SQL n CONNECT spécifie – – – 10/09/2021 – identification/authentification de l ’utilisateur (authorization. Id/mot de passe) identification du serveur SQL SCHEMA, TIME ZONE, CHARACTER SET valeurs de défaut© Robert Godin. Tous droits réservés. 10

Syntaxe des identificateurs n Régulier – Max. 128 caractères n lettres, – – chiffres,

Syntaxe des identificateurs n Régulier – Max. 128 caractères n lettres, – – chiffres, souligné (_) Débute par une lettre Pas de mot réservé n SELECT, – n CREATE, TABLE, ORDER, … Insensible à la casse (conversion en majuscules) Délimité – 10/09/2021 CREATE TABLE "TABLE". . . © Robert Godin. Tous droits réservés. 11

4. 1. 2 Création du schéma d'une table en SQL (CREATE TABLE) n Forme

4. 1. 2 Création du schéma d'une table en SQL (CREATE TABLE) n Forme simple n Transmise à l ’interprète du LDD – – vérification création de la table n schéma stocké dans dictionnaire de données n allocation des structures physiques – 10/09/2021 clause non standardisée pour organisation primaire © Robert Godin. Tous droits réservés. 12

Syntaxe générale du CREATE TABLE n Syntaxe de spécification. De. Colonne n Syntaxe de

Syntaxe générale du CREATE TABLE n Syntaxe de spécification. De. Colonne n Syntaxe de spécification. De. Contrainte 10/09/2021 © Robert Godin. Tous droits réservés. 13

Exemple Ventes. Plein. De. Foin (script Oracle) 10/09/2021 © Robert Godin. Tous droits réservés.

Exemple Ventes. Plein. De. Foin (script Oracle) 10/09/2021 © Robert Godin. Tous droits réservés. 14

4. 1. 3 Types SQL n Numérique – INTEGER (ou INT) n n –

4. 1. 3 Types SQL n Numérique – INTEGER (ou INT) n n – n Petit entier (précision non standardisée) Exemples : 2, 3, 459 NUMERIC(p, c) (ou DECIMAL(p, c) ou DEC(p, c)) n n 10/09/2021 Entier (précision non standardisée) Exemples : 2, 3, 459 SMALLINT n – exact Nombre décimal avec p chiffres significatifs (excluant le point) et c chiffres après le point Exemples : 2. 5, 456. 342, 6 © Robert Godin. Tous droits réservés. 15

Types SQL (suite) n Numérique – REAL n n – Point flottant (précision non

Types SQL (suite) n Numérique – REAL n n – Point flottant (précision non standardisée) Exemples : 3. 27 E-4, 24 E 5 DOUBLE PRECISION n n – Point flottant à double précision (non standardisée) Exemples : 3. 27265378426 E-4, 24 E 12 FLOAT(n) n Point flottant – n 10/09/2021 approximatif précision minimale est de n chiffres pour la mantisse Exemples : 3. 27 E-4, 24 E 5 © Robert Godin. Tous droits réservés. 16

Types SQL (suite) n Chaîne de caractères (VARYING et NATIONAL : SQL 2 intermédiaire)

Types SQL (suite) n Chaîne de caractères (VARYING et NATIONAL : SQL 2 intermédiaire) – CHARACTER(n) (ou CHAR(n)) n n – CHARACTER VARYING (n) (ou VARCHAR(n)) n – Chaîne de caractère de taille fixe égale à n Exemples : 'G. Lemoyne-Allaire', 'Paul L''Heureux ’ Taille variable (max de n caractères) Mécanismes d ’internalionalisation n n Ensemble de caractères par défaut NATIONAL CHARACTER(n) – n NATIONAL CHARACTER VARYING(n) – n Taille variable Création d ’ensembles de caractères alternatifs – – 10/09/2021 Ensemble de caractères alternatif spécifique à l'implémentation CREATE CHARACTER SET COLLATION : relation d ’ordre des caractères © Robert Godin. Tous droits réservés. 17

Types SQL (suite) n Date et temps (SQL 2 intermédiaire; précision p: SQL 2

Types SQL (suite) n Date et temps (SQL 2 intermédiaire; précision p: SQL 2 complet) – DATE n n – TIME[(p)] n n – n DATE + TIME Exemple : TIMESTAMP '1998 -08 -25 14: 04: 32. 25' INTERVAL n n 10/09/2021 heure (2 chiffres), minutes (2 chiffres), secondes (2 +p chiffres) Exemple : TIME '14: 04: 32. 25' TIMESTAMP[(p)] n – année (quatre chiffres), mois (2 chiffres) et jour (2 chiffres) Exemple : DATE '1998 -08 -25' Représente un intervalle de temps Exemple : INTERVAL '2' DAY (intervalle de deux jours) © Robert Godin. Tous droits réservés. 18

Types SQL (suite) n Booléen (SQL 2 complet) – BIT (n) n n –

Types SQL (suite) n Booléen (SQL 2 complet) – BIT (n) n n – BIT VARYING (n) n n Vecteur de n bits. Exemples : B'00100110', X'9 F' taille variable (max = n) Données de grande taille (LOB SQL: 1999) – BINARY LARGE OBJECT (n) (BLOB(n)) n n – – 10/09/2021 n : taille en octets (ex: 1024, 5 K, 3 M, 2 G) Exemple : X ’ 52 CF 4 ’ (hexadecimal) CHARACTER LARGE OBJECT (n) (CLOB(n)) NATIONAL CHARACTER LARGE OBJECT (n) (NCLOB(n)) © Robert Godin. Tous droits réservés. 19

UDT (User Defined Type) SQL: 1999 n Voir chapitre 17 10/09/2021 © Robert Godin.

UDT (User Defined Type) SQL: 1999 n Voir chapitre 17 10/09/2021 © Robert Godin. Tous droits réservés. 20

Dialecte Oracle n NUMBER(p, [c]) – – numérique exact; p entre 1 et 38

Dialecte Oracle n NUMBER(p, [c]) – – numérique exact; p entre 1 et 38 c doit être entre -84 et +127 (défaut, c =0) n n n VARCHAR 2(n) : n £ 4000 RAW(n) – n Binaire de taille n octets (n £ 2000). LONG(n) – – n valeur négative signifie un arrondissement. Chaîne de caractères de taille variable (n £ 2 G) Maximum une colonne LONG par table LONG RAW(n) – – 10/09/2021 Binaire de taille variable (n £ 2 G). Maximum une colonne de type LONG RAW par table © Robert Godin. Tous droits réservés. 21

Dialecte Oracle (suite) n ROWID : identifiant de ligne composé de – – –

Dialecte Oracle (suite) n ROWID : identifiant de ligne composé de – – – n n identificateur de fichier identificateur de bloc relatif au fichier identificateur de ligne relatif au bloc UROWID – identificateur universel de ligne (à partir de la version 8. 1). – distingue index primaire (ORGANIZATION INDEX) Conversions implicites 10/09/2021 © Robert Godin. Tous droits réservés. 22

Dialecte Oracle (suite) n DATE – n ~TIMESTAMP SQL 2 Mécanisme d ’internationalisation –

Dialecte Oracle (suite) n DATE – n ~TIMESTAMP SQL 2 Mécanisme d ’internationalisation – Paramètre de configuration NLS_LANG n n ALTER SESSION – n n CHARACTER SET DATE_FORMAT. . . pour modifier LOB : taille max 4 G BFILE : fichier externe 10/09/2021 © Robert Godin. Tous droits réservés. 23

4. 1. 4 Suppression d’une table (DROP TABLE) n RESTRICT – rejet si élément

4. 1. 4 Suppression d’une table (DROP TABLE) n RESTRICT – rejet si élément dépendant existe n ex: n FOREIGN KEY CASCADE – 10/09/2021 supprime élément dépendant © Robert Godin. Tous droits réservés. 24

4. 1. 5 Modification du schéma de table (ALTER TABLE) n Syntaxe 10/09/2021 ©

4. 1. 5 Modification du schéma de table (ALTER TABLE) n Syntaxe 10/09/2021 © Robert Godin. Tous droits réservés. 25

4. 1. 6 Le dictionnaire de données SQL (INFORMATION_SCHEMA) Normalisé en SQL 2 n

4. 1. 6 Le dictionnaire de données SQL (INFORMATION_SCHEMA) Normalisé en SQL 2 n BD relationnelle n – n DEFINITION_SCHEMA – n contient les méta-données d ’un CATALOG tables INFORMATION_SCHEMA – 10/09/2021 VIEWS sur les tables du DEFINITION_SCHEMA © Robert Godin. Tous droits réservés. 26

Exemples de VIEWS du INFORMATION_SCHEMA n SCHEMATA – n DOMAINS – n contraintes CHECK

Exemples de VIEWS du INFORMATION_SCHEMA n SCHEMATA – n DOMAINS – n contraintes CHECK des TABLE créées par CURRENT_USER ASSERTIONS – n contraintes des TABLE créées par CURRENT_USER CHECK_CONSTRAINTS – n les colonnes des TABLE accessibles par CURRENT_USER ou PUBLIC TABLE_CONSTRAINTS – n les vues accessibles par CURRENT_USER ou PUBLIC COLUMNS – n les noms des tables accessibles par CURRENT_USER ou PUBLIC VIEWS – n les DOMAIN accessibles par CURRENT_USER ou PUBLIC TABLES – n les SCHEMA créés par CURRENT_USER ASSERTION créées par CURRENT_USER TABLE_PRIVILEGES – 10/09/2021 privilèges accordés par CURRENT_USER, à CURRENT_USER, ou à PUBLIC © Robert Godin. Tous droits réservés. 27

Dictionnaire de données Oracle avec SQL*plus 10/09/2021 © Robert Godin. Tous droits réservés. 28

Dictionnaire de données Oracle avec SQL*plus 10/09/2021 © Robert Godin. Tous droits réservés. 28

Recherche d ’une table du dictionnaire de données 10/09/2021 © Robert Godin. Tous droits

Recherche d ’une table du dictionnaire de données 10/09/2021 © Robert Godin. Tous droits réservés. 29

4. 2 Requêtes SQL (SELECT) n Syntaxe de requête. SQL n Syntaxe du select.

4. 2 Requêtes SQL (SELECT) n Syntaxe de requête. SQL n Syntaxe du select. SQL n . . . 10/09/2021 © Robert Godin. Tous droits réservés. 30

4. 2. 1 Projection d'une table et la clause DISTINCT n Produire les no.

4. 2. 1 Projection d'une table et la clause DISTINCT n Produire les no. Client et date. Commande de toutes les Commandes Multi-ensemble ! 10/09/2021 © Robert Godin. Tous droits réservés. 31

Clause DISTINCT n Produire les no. Client et date. Commande de toutes les Commandes

Clause DISTINCT n Produire les no. Client et date. Commande de toutes les Commandes 10/09/2021 no. Client, date. Commande (Commande) © Robert Godin. Tous droits réservés. 32

4. 2. 2 Sélection sur une table (WHERE) n Sélectionner les Articles dont le

4. 2. 2 Sélection sur une table (WHERE) n Sélectionner les Articles dont le prix est inférieur à $20. 00 et le numéro est supérieur à 30 prix. Unitaire < 20. 00 ET no. Article > 30 (Article) 10/09/2021 © Robert Godin. Tous droits réservés. 33

Syntaxe de condition. SQL n Syntaxe (incomplète) de la condition. Simple : 10/09/2021 ©

Syntaxe de condition. SQL n Syntaxe (incomplète) de la condition. Simple : 10/09/2021 © Robert Godin. Tous droits réservés. 34

Condition. SQL - BETWEEN n Sélectionner les Commandes du mois de juin de l'année

Condition. SQL - BETWEEN n Sélectionner les Commandes du mois de juin de l'année 2000 10/09/2021 © Robert Godin. Tous droits réservés. 35

Condition. SQL - IN n Sélectionner les Commandes du Client dont le no. Client

Condition. SQL - IN n Sélectionner les Commandes du Client dont le no. Client est 10 ou 40 ou 80 10/09/2021 © Robert Godin. Tous droits réservés. 36

Condition. SQL - LIKE n Sélectionner les Clients dont le nom. Client contient le

Condition. SQL - LIKE n Sélectionner les Clients dont le nom. Client contient le mot Le n 2 ième lettre du nom. Client = o et dernière lettre est un k 10/09/2021 © Robert Godin. Tous droits réservés. 37

Condition. SQL - IS NOT NULL n Sélectionner les Articles dont la description n'est

Condition. SQL - IS NOT NULL n Sélectionner les Articles dont la description n'est pas une valeur nulle 10/09/2021 © Robert Godin. Tous droits réservés. 38

4. 2. 3 Sélectionprojection sur une table n Produire les no. Client et date.

4. 2. 3 Sélectionprojection sur une table n Produire les no. Client et date. Commande des Commandes dont la date est supérieure au 05/07/2000 Laboratoire Créer le schéma de la BD Plein. De. Foin : Schema. Ventes. Plein. De. Foin. sql Exercices 1 a), b) , c) , n), o) 10/09/2021 © Robert Godin. Tous droits réservés. 39

4. 2. 4 Produit cartésien avec SELECT-FROM n Produire toutes les combinaisons possibles de

4. 2. 4 Produit cartésien avec SELECT-FROM n Produire toutes les combinaisons possibles de lignes de Client et de Commande. . . Client Commande 10/09/2021 © Robert Godin. Tous droits réservés. 40

4. 2. 5 Jointure naturelle avec SELECT-FROM-WHERE n Produire les informations au sujet des

4. 2. 5 Jointure naturelle avec SELECT-FROM-WHERE n Produire les informations au sujet des Clients et de leurs Commandes Client. no. Client, nom. Client, no. Téléphone, no. Commande, date. Commande ( Client. no. Cliente = Commande. no. Client (Client Commande)) 10/09/2021 © Robert Godin. Tous droits réservés. 41

4. 2. 6 Jointure avec JOIN (SQL 2 intermédiaire) n Produire les informations au

4. 2. 6 Jointure avec JOIN (SQL 2 intermédiaire) n Produire les informations au sujet des Clients et de leurs Commandes Client Commande n Jointure- (si noms de colonnes de jointure sont différents) 10/09/2021 © Robert Godin. Tous droits réservés. 42

4. 2. 7 n Jointure de plusieurs tables Sélectionner les nom. Client des Clients

4. 2. 7 n Jointure de plusieurs tables Sélectionner les nom. Client des Clients qui ont commandé au moins un plant d'herbe à puce nom. Client ( description = “Herbe à puce ” (Client Commande Ligne. Commande Article)) 10/09/2021 © Robert Godin. Tous droits réservés. 43

4. 2. 8 Formulations équivalentes, performance et indépendance des données n ~Algèbre relationnelle n

4. 2. 8 Formulations équivalentes, performance et indépendance des données n ~Algèbre relationnelle n Ordre quelconque des tables du FROM – n n la plupart du temps… AND commutatif… Processus d ’évaluation de requête 10/09/2021 © Robert Godin. Tous droits réservés. 44

4. 2. 9 Définition d'un alias (clause AS) n ~ renommer ( ) Laboratoire

4. 2. 9 Définition d'un alias (clause AS) n ~ renommer ( ) Laboratoire Exercices 1 d) e) f) h) 10/09/2021 © Robert Godin. Tous droits réservés. 45

Auto-jointure n Quels sont les Clients qui ont le même numéro de téléphone? Client.

Auto-jointure n Quels sont les Clients qui ont le même numéro de téléphone? Client. no. Client, Client 2. no. Client, ( Client. no. Téléphone = Client 2. no. Téléphone (Client 2 (Client))) no. Client, no. Client 2 (Client 2(no. Client 2, nom. Client 2, no. Téléphone) (Client)) 10/09/2021 © Robert Godin. Tous droits réservés. 46

4. 2. 10 Jointure externe (OUTER JOIN) n Produire les informations au sujet des

4. 2. 10 Jointure externe (OUTER JOIN) n Produire les informations au sujet des Clients et de leurs Commandes incluant les informations sur les Clients qui n’ont pas placé de Commande Client = Commande n Oracle – 10/09/2021 « + » après colonne pour inclure la valeur NULL © Robert Godin. Tous droits réservés. 47

4. 2. 11 Opérations ensemblistes (UNION, INTERSECT, EXCEPT) n Produire les noms et numéros

4. 2. 11 Opérations ensemblistes (UNION, INTERSECT, EXCEPT) n Produire les noms et numéros de téléphone des Employés qui sont aussi des Clients de la pépinière Laboratoire Exercices 1 g) i) j) 10/09/2021 © Robert Godin. Tous droits réservés. 48

4. 2. 12 Expressions générales sur les colonnes n La liste des no. Article

4. 2. 12 Expressions générales sur les colonnes n La liste des no. Article avec le prix. Unitaire avant et après inclusion de la taxe de 15% 10/09/2021 © Robert Godin. Tous droits réservés. 49

4. 2. 12 Expressions (suite) n Produire le détail de chacun des Articles commandés

4. 2. 12 Expressions (suite) n Produire le détail de chacun des Articles commandés la Commande #1 incluant le prix total avant et après la taxe de 15% pour chacun des Articles commandés 10/09/2021 © Robert Godin. Tous droits réservés. 50

Expression sur colonne du WHERE n Les Articles dont le prix. Unitaire incluant la

Expression sur colonne du WHERE n Les Articles dont le prix. Unitaire incluant la taxe de 15% est inférieur à $16. 00 10/09/2021 © Robert Godin. Tous droits réservés. 51

Opérateurs n Conversions automatiques entre types compatibles 10/09/2021 © Robert Godin. Tous droits réservés.

Opérateurs n Conversions automatiques entre types compatibles 10/09/2021 © Robert Godin. Tous droits réservés. 52

Pseudo-colonnes n Les Commandes de la journée 10/09/2021 © Robert Godin. Tous droits réservés.

Pseudo-colonnes n Les Commandes de la journée 10/09/2021 © Robert Godin. Tous droits réservés. 53

Priorité en ordre décroissant 10/09/2021 © Robert Godin. Tous droits réservés. 54

Priorité en ordre décroissant 10/09/2021 © Robert Godin. Tous droits réservés. 54

Sélection par un CASE n Produire la quantité qui a été livrée pour l'Article

Sélection par un CASE n Produire la quantité qui a été livrée pour l'Article #50 de la Commande #4 10/09/2021 © Robert Godin. Tous droits réservés. 55

Quelques fonctions SQL 2 n POSITION(patron IN chaîne) CHARACTER_LENGTH(chaîne) OCTET_LENGTH (chaîne) BIT_LENGTH(chaîne) EXTRACT(champ FROM

Quelques fonctions SQL 2 n POSITION(patron IN chaîne) CHARACTER_LENGTH(chaîne) OCTET_LENGTH (chaîne) BIT_LENGTH(chaîne) EXTRACT(champ FROM date. Ou. Time) SUBSTRING (chaîne FROM indice. Début FOR nombre. Caractères) UPPER | LOWER (chaîne) TRIM ([LEADING|TRAILING|BOTH] caractère FROM chaîne) CAST(expression AS type) … n Voir documentation du SGBD n n n n n 10/09/2021 © Robert Godin. Tous droits réservés. 56

Expressions de DATE Oracle 10/09/2021 © Robert Godin. Tous droits réservés. 57

Expressions de DATE Oracle 10/09/2021 © Robert Godin. Tous droits réservés. 57

Opérations sur DATE 10/09/2021 © Robert Godin. Tous droits réservés. 58

Opérations sur DATE 10/09/2021 © Robert Godin. Tous droits réservés. 58

4. 2. 13 Expressions et conditions sur les valeurs nulles (NULL) n Arithmétique –

4. 2. 13 Expressions et conditions sur les valeurs nulles (NULL) n Arithmétique – n opérande NULL => NULL Comparaison (>, <, …) – 10/09/2021 opérande NULL => UNKNOWN © Robert Godin. Tous droits réservés. 59

NULL pour les nuls ? n Si x est NULL – 10/09/2021 UNKNOWN OR

NULL pour les nuls ? n Si x est NULL – 10/09/2021 UNKNOWN OR UNKNOWN = UNKNOWN n pas dans le résultat ! © Robert Godin. Tous droits réservés. 60

4. 2. 14 Fonctions de groupe n Le nombre d'Articles différents à vendre ainsi

4. 2. 14 Fonctions de groupe n Le nombre d'Articles différents à vendre ainsi que le prix. Unitaire moyen des Articles 10/09/2021 © Robert Godin. Tous droits réservés. 61

suite 10/09/2021 © Robert Godin. Tous droits réservés. 62

suite 10/09/2021 © Robert Godin. Tous droits réservés. 62

4. 2. 15 Partition d'une table avec la clause GROUP BY n Produire le

4. 2. 15 Partition d'une table avec la clause GROUP BY n Produire le nombre de Commandes passées par chacun des Clients qui ont passé au moins une Commande 10/09/2021 © Robert Godin. Tous droits réservés. 63

Pour chacune des Ligne. Commande pour lesquelles au moins une Livraison a été effectuée,

Pour chacune des Ligne. Commande pour lesquelles au moins une Livraison a été effectuée, produire le no. Commande et no. Article, la quantité totale livrée et le nombre de Livraisons effectuées Calcul de plusieurs agrégats à la fois avec CUBE et ROLLUP SQL: 1999 10/09/2021 © Robert Godin. Tous droits réservés. 64

4. 2. 16 n Clause HAVING Produire le nombre de Commandes passées par chacun

4. 2. 16 n Clause HAVING Produire le nombre de Commandes passées par chacun des Clients qui ont passé deux Commandes ou plus 10/09/2021 © Robert Godin. Tous droits réservés. 65

Produire le nombre de Commandes passées par chacun des Clients qui ont passé deux

Produire le nombre de Commandes passées par chacun des Clients qui ont passé deux Commandes ou plus après le 02/06/2000 10/09/2021 © Robert Godin. Tous droits réservés. 66

4. 2. 17 Tri du résultat (ORDER BY) n Les Clients en ordre alphabétique

4. 2. 17 Tri du résultat (ORDER BY) n Les Clients en ordre alphabétique du nom Laboratoire Exercices 1 q) s) t) v) 10/09/2021 © Robert Godin. Tous droits réservés. 67

4. 2. 18 SELECT imbriqué 4. 2. 18. 1 Opération élément de (IN) n

4. 2. 18 SELECT imbriqué 4. 2. 18. 1 Opération élément de (IN) n Les Clients qui ont passé au moins une Commande Client Commande 10/09/2021 © Robert Godin. Tous droits réservés. 68

Ligne à plusieurs colonnes n Chercher les Ligne. Commandes pour lesquelles au moins une

Ligne à plusieurs colonnes n Chercher les Ligne. Commandes pour lesquelles au moins une Livraison a été effectuée 10/09/2021 © Robert Godin. Tous droits réservés. 69

4. 2. 18. 2 SELECT imbriqué qui retourne une ligne n Sélectionner les Commandes

4. 2. 18. 2 SELECT imbriqué qui retourne une ligne n Sélectionner les Commandes du Client Hugh Paycheck n Exception si plusieurs lignes retournées par SELECT imbriqué 10/09/2021 © Robert Godin. Tous droits réservés. 70

4. 2. 18. 3 SELECT imbriqué corrélé n Produire les informations au sujet des

4. 2. 18. 3 SELECT imbriqué corrélé n Produire les informations au sujet des Clients qui ont passé au moins une Commande Référence à une colonne non locale 10/09/2021 © Robert Godin. Tous droits réservés. 71

4. 2. 18. 4 Test d’ensemble vide (EXISTS) n Produire les informations au sujet

4. 2. 18. 4 Test d’ensemble vide (EXISTS) n Produire les informations au sujet des Clients qui ont passé au moins une Commande 10/09/2021 © Robert Godin. Tous droits réservés. 72

4. 2. 18. 5 Test de double (UNIQUE) n Vérifier s'il y a plus

4. 2. 18. 5 Test de double (UNIQUE) n Vérifier s'il y a plus d’un Client qui porte le même nom n Clients qui ont passé au moins deux Commandes 10/09/2021 © Robert Godin. Tous droits réservés. 73

4. 2. 18. 6 Quantificateurs (ALL, SOME/ANY) n Commandes passées après la dernière Livraison

4. 2. 18. 6 Quantificateurs (ALL, SOME/ANY) n Commandes passées après la dernière Livraison (date ultérieure) n Commandes passées après au moins une des Livraisons 10/09/2021 © Robert Godin. Tous droits réservés. 74

4. 2. 18. 7 Test d'inclusion entre deux tables et division n T 1

4. 2. 18. 7 Test d'inclusion entre deux tables et division n T 1 T 2 10/09/2021 © Robert Godin. Tous droits réservés. 75

Quelles sont les no. Commande des Commandes qui incluent tous les Articles dont le

Quelles sont les no. Commande des Commandes qui incluent tous les Articles dont le prix. Unitaire est $10. 99 10/09/2021 © Robert Godin. Tous droits réservés. Lab Exercices 1 e) f) j) k) p) u) m) 76

4. 2. 18. 8 SELECT imbriqué dans le FROM n Produire les no. Client

4. 2. 18. 8 SELECT imbriqué dans le FROM n Produire les no. Client et date. Commande des Commandes dont la date. Commande est supérieure au 05/07/2000 Laboratoire Exercices 2 a) b) 10/09/2021 © Robert Godin. Tous droits réservés. 77

4. 2. 19 Récursivité en SQL: 1999 10/09/2021 © Robert Godin. Tous droits réservés.

4. 2. 19 Récursivité en SQL: 1999 10/09/2021 © Robert Godin. Tous droits réservés. 78

CONNECT BY Oracle n Chemins à partir de 1 10/09/2021 © Robert Godin. Tous

CONNECT BY Oracle n Chemins à partir de 1 10/09/2021 © Robert Godin. Tous droits réservés. 79

4. 3 Opérations de mise à jour des tables en SQL n. Insert n.

4. 3 Opérations de mise à jour des tables en SQL n. Insert n. Delete n. Update 10/09/2021 © Robert Godin. Tous droits réservés. 80

4. 3. 1 Insertion dans une table (INSERT) n Insérer une nouvelle ligne dans

4. 3. 1 Insertion dans une table (INSERT) n Insérer une nouvelle ligne dans la table Client n Changer l ’ordre de défaut 10/09/2021 © Robert Godin. Tous droits réservés. 81

Insertion d ’une partie des colonnes 10/09/2021 © Robert Godin. Tous droits réservés. 82

Insertion d ’une partie des colonnes 10/09/2021 © Robert Godin. Tous droits réservés. 82

Insertion à partir d ’un SELECT n Produire les lignes de Détail. Livraison pour

Insertion à partir d ’un SELECT n Produire les lignes de Détail. Livraison pour la Livraison #106 à partir des Ligne. Commandes de la Commande #7 10/09/2021 © Robert Godin. Tous droits réservés. 83

4. 3. 2 Suppression de lignes (DELETE) n Supprimer toutes lignes de la table

4. 3. 2 Suppression de lignes (DELETE) n Supprimer toutes lignes de la table Client n Supprimer le Client #70 de la table Client n Supprimer les Clients qui n'ont passé de Commande 10/09/2021 © Robert Godin. Tous droits réservés. 84

4. 3. 3 Modification de lignes (UPDATE) n Changer le no. Téléphone du Client

4. 3. 3 Modification de lignes (UPDATE) n Changer le no. Téléphone du Client #10 pour (222)222 -2222 n Augmenter tous les prix. Unitaires des Articles de 10% n Modification de plusieurs colonnes à la fois Laboratoire Exercices 1 x) y) z) 10/09/2021 © Robert Godin. Tous droits réservés. 85

4. 3. 4 Gestion des transactions en SQL n COMMIT WORK – n ROLLBACK

4. 3. 4 Gestion des transactions en SQL n COMMIT WORK – n ROLLBACK WORK – n annule la transaction en cours Début de transaction implicite – – n confirme la transaction en cours début de session fin de la précédente Commande LDD provoque un COMMIT 10/09/2021 © Robert Godin. Tous droits réservés. 86

Session et transaction 10/09/2021 © Robert Godin. Tous droits réservés. 87

Session et transaction 10/09/2021 © Robert Godin. Tous droits réservés. 87

4. 4 Niveau externe du schéma en SQL n Gestion de la sécurité –

4. 4 Niveau externe du schéma en SQL n Gestion de la sécurité – n GRANT Tables virtuelles – 10/09/2021 VIEWS © Robert Godin. Tous droits réservés. 88

4. 4. 1. 1 n Sécurité en SQL (GRANT) Identification et authentification Identification des

4. 4. 1. 1 n Sécurité en SQL (GRANT) Identification et authentification Identification des utilisateurs – authorization. ID n PUBLIC n Authentification – – n : tous les utilisateurs mot de passe … Oracle – SYS, SYSTEM n CREATE 10/09/2021 USER authorization. ID. . . © Robert Godin. Tous droits réservés. 89

4. 4. 1. 2 n privilège : n objet : 10/09/2021 Privilèges © Robert

4. 4. 1. 2 n privilège : n objet : 10/09/2021 Privilèges © Robert Godin. Tous droits réservés. 90

Exemples 10/09/2021 © Robert Godin. Tous droits réservés. 91

Exemples 10/09/2021 © Robert Godin. Tous droits réservés. 91

Privilèges (suite) n Commandes LDD – n Création d ’une VIEW sur T –

Privilèges (suite) n Commandes LDD – n Création d ’une VIEW sur T – n SELECT sur T FOREIGN KEY sur T – n propriétaire du schéma privilège REFERENCES sur T SQL: 1999 – – ROLE = ensemble de privilèges, Oracle nouveaux privilèges n n n 10/09/2021 TRIGGER ON TABLE nom. Table EXECUTE ON PROCEDURE/FUNCTION nom. Proc. Ou. Func UNDER ON TYPE nom. Type © Robert Godin. Tous droits réservés. 92

4. 4. 1. 3 Suppression de privilèges 10/09/2021 © Robert Godin. Tous droits réservés.

4. 4. 1. 3 Suppression de privilèges 10/09/2021 © Robert Godin. Tous droits réservés. 93

4. 4. 2 Table virtuelle (VIEW) 10/09/2021 © Robert Godin. Tous droits réservés. 94

4. 4. 2 Table virtuelle (VIEW) 10/09/2021 © Robert Godin. Tous droits réservés. 94

4. 4. 2. 1 Implémentation des tables virtuelles n Résolution des vues par modification

4. 4. 2. 1 Implémentation des tables virtuelles n Résolution des vues par modification de requête 10/09/2021 © Robert Godin. Tous droits réservés. 95

Résolution des vues par matérialisation n Table stockée n Redondance n Maintenance de la

Résolution des vues par matérialisation n Table stockée n Redondance n Maintenance de la cohérence n Meilleure performance du SELECT n Moins bonne performance des mises à jour n Entrepôts de données 10/09/2021 © Robert Godin. Tous droits réservés. 96

4. 4. 2. 2 Mise à jour de tables virtuelles n SQL 2 –

4. 4. 2. 2 Mise à jour de tables virtuelles n SQL 2 – – n une seule table sans DISTINCT colonnes simples pas de SELECT imbriqué SQL: 1999 – 10/09/2021 spécification très complexe © Robert Godin. Tous droits réservés. 97

Exemple de mise à jour par modification de requête 10/09/2021 © Robert Godin. Tous

Exemple de mise à jour par modification de requête 10/09/2021 © Robert Godin. Tous droits réservés. 98

4. 4. 2. 3 Problèmes de mise à jour d'une table virtuelle n Sémantique

4. 4. 2. 3 Problèmes de mise à jour d'une table virtuelle n Sémantique incohérente. . . 10/09/2021 © Robert Godin. Tous droits réservés. 99

Rejet de mise à jour incohérente avec WITH CHECK OPTION 10/09/2021 © Robert Godin.

Rejet de mise à jour incohérente avec WITH CHECK OPTION 10/09/2021 © Robert Godin. Tous droits réservés. 100

4. 4. 2. 4 Hiérarchie de tables virtuelles 10/09/2021 © Robert Godin. Tous droits

4. 4. 2. 4 Hiérarchie de tables virtuelles 10/09/2021 © Robert Godin. Tous droits réservés. 101

4. 4. 2. 5 Renommer les colonnes d'une VIEW N. B. Modification interdite 10/09/2021

4. 4. 2. 5 Renommer les colonnes d'une VIEW N. B. Modification interdite 10/09/2021 © Robert Godin. Tous droits réservés. 102

4. 4. 2. 6 Indépendance logique des données et encapsulation par les tables virtuelles

4. 4. 2. 6 Indépendance logique des données et encapsulation par les tables virtuelles n Catalogue prix. Unitaire) n Inventaire (no. Article, quantitéEn. Stock) 10/09/2021 (no. Article, © Robert Godin. Tous droits réservés. description, 103

4. 4. 2. 7 Sécurité par les tables virtuelles 10/09/2021 © Robert Godin. Tous

4. 4. 2. 7 Sécurité par les tables virtuelles 10/09/2021 © Robert Godin. Tous droits réservés. 104

4. 5 Schéma interne n Non standardisé – – 10/09/2021 organisation primaire de la

4. 5 Schéma interne n Non standardisé – – 10/09/2021 organisation primaire de la table organisations secondaires (INDEX) © Robert Godin. Tous droits réservés. 105