Cours complet pour apprendre les systèmes de gestion de bases de données

SQL1 permet de créer des relations sous forme de tables et de définir lors de la création des contraintes d'intégrité variées sur les attributs. Ainsi, une commande de création permet de spécifier le nom de la table et de définir les éléments de table correspondant aux colonnes ou aux contraintes, selon la syntaxe suivante :

CREATE TABLE <nom de table> (<élément de table>+)

Un nom de table peut être un nom simple (par exemple VINS) ou un nom composé d'un nom de schéma (identifiant d'autorisation d'accès à la base, par exemple DEGUSTATION) suivi par le nom simple de table en notation pointée (par exemple, DEGUSTATION.VINS).

Un élément de table est soit une définition de colonne, soit une définition de contrainte, comme suit :

<ÉLÉMENT DE TABLE> ::= <DÉFINITION DE COLONNE> |
                       <CONTRAINTE DE TABLE>

Une colonne est définie par un nom et un type de données. Une valeur par défaut peut être précisée. Une contrainte de colonne peut aussi être définie à ce niveau. On obtient donc la syntaxe suivante :

<DÉFINITION DE COLONNE> : := <NOM DE COLONNE> <TYPE DE DONNÉES>
                             [<CLAUSE DÉFAUT>] [<CONTRAINTE DE COLONNE>]

Les types de données supportés sont les chaînes de caractères de longueurs fixes - CHAR(<longueur>) -, la valeur par défaut de la longueur étant 1, les numériques exacts - NUMERIC et DECIMAL avec précision et échelle optionnelles, INTEGER et SMALLINT -, les numériques approchés - FLOAT avec précision optionnelle, REAL et DOUBLE PRECISION.

La clause défaut permet simplement de spécifier une valeur par défaut selon la syntaxe DEFAULT <valeur>, la valeur NULL étant permise. Nous examinerons les contraintes d'intégrité de table et de colonne dans la section qui suit.

Afin d'illustrer les possibilités introduites, la figure VII.1 présente les commandes permettant de créer la base dégustation, pour l'instant sans contrainte d'intégrité. Le schéma de la base obtenu est composé des trois relations suivantes :

VINS (NV, CRU, MILLESIME, DEGRE, QUALITE)
BUVEURS (NB, NOM, ADRESSE, TYPE)
ABUS (NB, NV, DATE, QUANTITE)

CREATE SCHEMA AUTHORIZATION DEGUSTATION
CREATE TABLE VINS (NV INT, CRU CHAR(12), MILLESIME INT, DEGRE DEC(3,1), QUALITE CHAR)
CREATE TABLE BUVEURS (NB INT, NOM CHAR(20), ADRESSE CHAR(30), TYPE CHAR(4))
CREATE TABLE ABUS(NB INT, NV INT, DATE DEC(6), QUANTITE SMALLINT)

Les contraintes de colonnes permettent de spécifier différentes contraintes d'intégrité portant sur un seul attribut, y compris les contraintes référentielles. Les différentes variantes possibles sont :

• valeur nulle impossible (syntaxe NOT NULL) ;
• unicité de l'attribut (syntaxe UNIQUE ou PRIMARY KEY) ;
• contrainte référentielle - syntaxe REFERENCES <table référencée> [(<colonne référencée>)] -, le nom de la colonne référencée étant optionnel s'il est identique à celui de la colonne référençante ;
• contrainte générale (syntaxe CHECK <condition>) ; la condition est une condition pouvant spécifier des plages ou des listes de valeurs possibles (voir condition de recherche ci-dessous).

Les contraintes de relations peuvent porter sur plusieurs attributs. Ce peuvent être des contraintes d'unicité, référentielles ou générales. Elles sont exprimées à l'aide des phrases suivantes :

• contrainte d'unicité UNIQUE <attribut>⁺ ;
• contrainte référentielle, permettant de spécifier quelles colonnes référencent celles d'une autre table, [FOREIGN KEY (<colonne référençante>⁺)] REFERENCES <table référencée> [(<colonne référencée>⁺)] ;
• contrainte générale CHECK <condition>.

Par exemple, la création de la relation ABUS avec contraintes d'intégrité pourra être effectuée par la commande de la figure VII.2. Cette commande précise que les attributs NB et NV ne doivent pas être nuls et doivent exister dans les relations BUVEURS et VINS respectivement, que la date est comprise entre le premier janvier 80 et le 31 décembre 99, que la quantité doit être comprise entre 1 et 100 avec une valeur par défaut de 1.

CREATE TABLE ABUS (
    NB INT NOT NULL,
    NV INT NOT NULL REFERENCES VINS(NV),
    DATE DEC(6) CHECK (DATE BETWEEN 010180 AND 311299),
    QUANTITE SMALLINT DEFAULT 1,
    PRIMARY KEY(NB, NV, DATE),
    FOREIGN KEY NB REFERENCES BUVEURS,
    CHECK (QUANTITE BETWEEN 1 AND 100))

SQL1 permet de définir des vues au niveau du schéma. Rappelons qu'une vue est une table virtuelle calculée à partir des tables de base par une question. La syntaxe de la commande de création de vues est la suivante :

CREATE VIEW <NOM DE TABLE> [(<NOM DE COLONNE>+)]
AS <SPÉCIFICATION DE QUESTION>
[WITH CHECK OPTION]

Une vue est modifiable s'il est possible d'insérer et de supprimer des tuples dans la base au travers de la vue. Dans ce cas, les tuples sont insérés ou supprimés dans la première table référencée par la question. La vue doit alors contenir toutes les colonnes de cette table. L'option WITH CHECK OPTION permet de s'assurer que les tuples insérés vérifient les conditions exprimées dans la question, c'est-à-dire qu'ils appartiennent bien à la vue. Cela permet d'imposer des contraintes d'intégrité lors des mises à jour au travers de la vue (par exemple, le fait qu'un tuple de la vue doive référencer un tuple d'une autre table).

À titre d'illustration, voici une vue GROS-BUVEURS définie simplement comme la table virtuelle contenant le nom et le prénom des buveurs de type « GROS ». Cette vue n'est pas modifiable. Cette définition montre déjà un premier exemple de question SQL très simple, de type sélection.

CREATE VIEW GROS-BUVEURS (NOM, PRENOM)
AS   SELECT NOM, PRENOM
FROM BUVEURS
WHERE TYPE = "GROS"

La suppression des tables n'est pas permise en SQL1. La plupart des systèmes permettent cependant de détruire une relation par la commande :

DROP TABLE <NOM DE TABLE>

Par exemple, la destruction de la relation VINS s'effectuera par la commande :

DROP TABLE VINS

La gestion des droits d'accès aux tables est décentralisée : il n'existe pas d'administrateur global attribuant des droits. Chaque créateur de table obtient tous les droits d'accès à cette table, en particulier les droits d'effectuer les actions de sélection (SELECT), d'insertion (INSERT), de suppression (DELETE), de mise à jour (UPDATE) et aussi de référencer la table dans une contrainte (REFERENCES). Il peut ensuite passer ses droits sélectivement à d'autres utilisateurs ou à tout le monde (PUBLIC). Un droit peut être passé avec le droit de le transmettre (WITH GRANT OPTION) ou non.

SQL1 propose ainsi une commande de passation de droits dont la syntaxe est la suivante :

GRANT <PRIVILÈGES> ON <NOM DE TABLE> TO <RÉCEPTEUR>+
[WITH GRANT OPTION]

avec :

<PRIVILÈGES> ::= ALL PRIVILEGES | <ACTION>+
<ACTION>   ::= SELECT | INSERT | UPDATE [(<NOM DE COLONNE>+)]
                  | REFERENCE [(<NOM DE COLONNE>+)]
<RÉCEPTEUR> ::= PUBLIC | <IDENTIFIANT D'AUTORISATION>

L'ensemble des privilèges (ALL PRIVILEGES) inclut les droits d'administration (changement de schéma et destruction de la relation). Le récepteur peut être un utilisateur ou un groupe d'utilisateurs, selon l'identifiant d'autorisation donné.

Par exemple, la passation des droits de consultation et mise à jour de la table VINS à l'utilisateur Poivrot s'effectuera comme indiqué ci-dessous. La présence de l'option de passation (GRANT OPTION) permet à Poivrot de passer ce droit.

GRANT SELECT, UPDATE ON VINS TO POIVROT
WITH GRANT OPTION

Bien que non prévue dans la norme de 1989, la commande REVOKE permet de retirer un droit à un utilisateur. Sa syntaxe est la suivante :

REVOKE <privilèges> ON <nom de table> FROM <récepteur>

Par exemple, la commande qui suit permet de retirer le droit donné ci-dessus ainsi que tous les droits qui en dépendent (c'est-à-dire ceux de sélection ou mise à jour de la table VINS passés par Poivrot).

REVOKE SELECT, UPDATE ON VINS FROM POIVROT

Rappelons qu'une projection effectue l'extraction de colonnes (attributs) spécifiées d'une relation, puis élimine les tuples en double. SQL n'élimine pas les doubles, à moins que cela soit explicitement demandé par le mot clé DISTINCT, l'option par défaut étant ALL. SQL généralise la projection en ce sens qu'il est possible d'appliquer des fonctions de calculs sur les colonnes extraites. Les fonctions de calculs permises sont en particulier les fonctions arithmétiques d'addition, soustraction, multiplication et division. Une projection s'exprime à l'aide du langage SQL par la clause :

SELECT [ALL|DISTINCT] <EXPRESSION DE VALEURS>+
FROM <NOM DE TABLE> [<NOM DE VARIABLE>]

Une expression de valeurs est une expression arithmétique (composée avec les opérateurs binaires +, -, * et /), éventuellement parenthésée, de spécifications de constantes ou de colonnes. Une spécification de constante est soit une constante, une variable de programme ou le nom de l'usager (mot clé USER). Une spécification de colonne désigne le nom d'une colonne précédé d'un désignateur de relation éventuel (nom de table ou variable), comme suit :

<SPÉCIFICATION DE COLONNE> ::= [<DÉSIGNATEUR>.] <NOM DE COLONNE> <DÉSIGNATEUR> ::= <NOM DE TABLE> | <NOM DE VARIABLE>

L'usage d'un nom de variable nécessite de définir dans la clause FROM une variable dite de corrélation, permettant de désigner la table par cette variable. De telles variables évitent de répéter le nom de la table dans le cas de questions portant sur plusieurs tables, comme nous le verrons ci-dessous. Notez aussi qu'il est possible d'utiliser une étoile (*) à la place de la liste d'expression de valeurs, cela signifiant simplement que l'on désire lister tous les attributs de la table référencée dans la clause FROM.

Nous illustrons la projection en utilisant la table VINS dont le schéma a été défini ci-dessus. La première question (Q1) indiquée figure VII.4 permet d'obtenir pour tous les vins les crus, millésimes et quantité d'alcool pur contenue dans 1 000 litres, avec doubles éventuels. La deuxième question (Q2) effectue la recherche de tous les tuples de la table VINS sans double.

(Q1) SELECT CRU, MILLESIME, (DEGRE/100)*1000 FROM VINS
(Q2) SELECT DISTINCT *
     FROM VINS

Une sélection est une combinaison d'une restriction suivie d'une projection. Une sélection s'exprime comme une projection avec en plus une condition de recherche selon la syntaxe suivante :

SELECT [ALL|DISTINCT] {<expression de valeurs>+ | *}
FROM <nom de table> [<nom de variable>]
WHERE <condition de recherche>

Une condition de recherche définit un critère qui, appliqué à un tuple, est vrai, faux ou inconnu, selon le résultat de l'application d'opérateurs booléens (ET, OU, NOT) à des conditions élémentaires. L'expression booléenne de conditions élémentaires peut être parenthésée. La figure VII.5 donne les tables de vérité permettant de calculer la valeur de vérité d'une condition de recherche. En principe, les seuls tuples satisfaisant la condition de recherche sont sélectionnés par la requête.

Une condition de recherche élémentaire est appelée prédicat en SQL. Un prédicat de restriction permet de comparer deux expressions de valeurs ; la première contenant des spécifications de colonnes est appelée terme ; la seconde contenant seulement des spécifications de constantes est appelée constante. Il existe une grande diversité de prédicats en SQL1. On trouve en effet :

1. un prédicat de comparaison permettant de comparer un terme à une constante à l'aide des opérateurs {=, ≠, <, >, <=, >=} ;
2. un prédicat d'intervalle BETWEEN permettant de tester si la valeur d'un terme est comprise entre la valeur de deux constantes ;
3. un prédicat de comparaison de texte LIKE permettant de tester si un terme de type chaîne de caractères contient une ou plusieurs sous-chaînes ;
4. un prédicat de test de nullité qui permet de tester si un terme a une valeur convenue NULL, signifiant que sa valeur est inconnue ;
5. un prédicat d'appartenance IN qui permet de tester si la valeur d'un terme appartient à une liste de constantes.

La figure VII.6 donne quelques exemples de sélections illustrant ces différents types de prédicats. La question (Q3) effectue la restriction de la relation VINS par la qualification Millésime = 1977 et Degré > 13. La question (Q4) délivre les crus et degrés des vins de millésime 1977 et de degré compris entre 11 et 13. La question (Q5) retrouve les crus, années de production (millésime diminué de 1900) et qualité des Beaujolais. Elle illustre le prédicat de recherche textuel (LIKE) ; le caractère % signifie dans le cas du LIKE une quelconque sous-chaîne de caractères ; par exemple, BEAUJOLAIS NOUVEAUX et NOUVEAUX BEAUJOLAIS rendent le prédicat CRU LIKE "?AUJOLAIS%" vrai. La question (Q6) recherche tous les vins de degré nul. La question (Q7) délivre les crus des vins de qualité A, B ou C.

(Q3) SELECT *
     FROM VINS
     WHERE MILLESIME = 1977
       AND DEGRE > 13

(Q4) SELECT CRU, DEGRE
     FROM VINS
     WHERE MILLESIME = 1977
       AND DEGRE BETWEEN 11 AND 13

(Q5) SELECT DISTINCT CRU, MILLESIME - 1900, QUALITE
     FROM VINS
     WHERE CRU LIKE "?AUJOLAIS%"

(Q6) SELECT *
     FROM VINS
     WHERE CRU IS NULL

(Q7) SELECT CRU
     FROM VINS
     WHERE QUALITE IN (A,B,C)

Un cas particulier simple de jointure sans qualification est le produit cartésien. Celui-ci s'exprime très simplement en incluant plusieurs relations dans la clause FROM. Par exemple, le produit cartésien des relations VINS et ABUS se calcule à l'aide de la question (Q8) représentée figure VII.7.

(Q8) SELECT *
     FROM VINS, ABUS

La jointure avec qualification peut s'exprimer de plusieurs manières. Une première expression naturelle est la restriction du produit cartésien par un prédicat permettant de comparer deux termes. Les prédicats de comparaison (=, ≠, ≤, ≥, <, >), d'intervalle (BETWEEN), d'appartenance (IN) et de comparaison textuelle (LIKE) sont utilisables. La combinaison des opérations de jointures, restrictions et projections peut être effectuée à l'intérieur d'un même bloc SELECT.

La figure VII.8 illustre différents cas de jointures. La question (Q9) effectue la jointure de VINS et ABUS sur l'attribut numéro de vins (NV) et permet de lister en préfixe de chaque tuple de ABUS le vin correspondant. La question (Q10) recherche les buveurs ayant un nom similaire à celui d'un cru. La question (Q11) retourne le nom des buveurs ayant bu du Chablis, sans double. Notez l'usage des variables B, V et A comme alias des relations BUVEURS, VINS et ABUS, afin d'éviter de répéter le nom complet des relations dans le critère.

(Q9) SELECT *
     FROM VINS, ABUS
     WHERE VINS.NV = ABUS.NV

(Q10) SELECT NB, NOM
      FROM BUVEURS, VINS
      WHERE NOM LIKE CRU

(Q11) SELECT DISTINCT NOM
      FROM BUVEURS B, VINS V, ABUS A
      WHERE B.NB = A. NB
        AND A.NV = V.NV
        AND V.CRU = "Chablis"

SQL permet l'imbrication de sous-questions au niveau de la clause WHERE, si bien que l'on peut écrire des questions du type SELECT … FROM … WHERE … SELECT …. En effet, le résultat d'une question peut être considéré comme une valeur simple ou comme un ensemble de valeurs avec doubles éventuels (multiensemble) ; dans ce dernier cas, chaque valeur de l'ensemble correspond à un tuple du résultat. Ainsi, il est possible de considérer une sous-question comme argument particulier des prédicats de comparaison (=, ≠, <, >, ≥, ≤) et d'appartenance à une liste (IN). Toute sous-question peut elle-même invoquer des sous-questions, si bien qu'il est possible d'imbriquer des blocs SELECT à plusieurs niveaux. L'imbrication de blocs SELECT par le prédicat IN permet d'exprimer en particulier des jointures d'une manière plus procédurale.

Par exemple, la question (Q12) de la figure VII.9 effectue la jointure des tables VINS et ABUS sur numéro de vin et sélectionne les crus des vins résultants sans double. Elle est équivalente à la question (Q13). Il est aussi possible d'utiliser des variables définies dans un bloc interne au niveau d'un bloc externe. On parle alors de variable de corrélation. La question (Q14) illustre l'usage d'une variable de corrélation pour retrouver cette fois le cru du vin, mais aussi la quantité bue à partir de la jointure de VINS et ABUS. Finalement, la question (Q15) recherche les noms des buveurs de Chablis. Elle est équivalente à la question (Q11), mais est écrite de manière plus procédurale avec trois blocs imbriqués.

(Q12) SELECT DISTINCT CRU
      FROM VINS
      WHERE NV IN
              SELECT NV
              FROM ABUS

(Q13) SELECT DISTINCT CRU
      FROM VINS V, ABUS A
      WHERE V.NV = A.NV

(Q14) SELECT DISTINCT CRU, A.QUANTITE
      FROM VINS
      WHERE NV IN
              SELECT NV
              FROM ABUS A

(Q15) SELECT DISTINCT NOM
      FROM BUVEURS
      WHERE NB IN
              SELECT NB
              FROM ABUS
              WHERE NV IN
                      SELECT NV
                      FROM VINS
                      WHERE CRU = "CHABLIS"

Il est aussi possible de vouloir comparer une expression de valeurs à tous les résultats d'une sous-question ou seulement à l'une quelconque des valeurs générées. SQL propose pour cela l'usage de sous-questions quantifiées par « quel que soit » (ALL) ou « il existe » (ANY ou SOME). Ces quantificateurs permettent de tester si la valeur d'un terme satisfait un opérateur de comparaison avec tous (ALL) ou au moins un (ANY ou SOME) des résultats d'une sous-question. Un prédicat quantifié par ALL est vrai s'il est vérifié pour tous les éléments de l'ensemble. Un prédicat quantifié par ANY ou SOME est vrai s'il est vérifié par au moins un élément de l'ensemble.

Ainsi, la question (Q16) recherche les noms des buveurs n'ayant commis que des abus en quantité supérieure ou égale à toutes les quantités bues, alors que la question (Q17) recherche ceux ayant commis au moins un abus en quantité supérieure ou égale à toutes les quantités bues. La première n'aura probablement pas de réponse alors que la deuxième éditera le nom de la personne ayant effectué le plus gros abus.

SQL offre une autre possibilité de quantification pour tester si le résultat d'une sous-question est vide ou non. Il s'agit du prédicat d'existence EXISTS. EXISTS <sous-question> est vrai si et seulement si le résultat de la sous-question est non vide. Ainsi, la question (Q18) recherche les buveurs ayant bu du Volnay alors que la question (Q19) recherche ceux n'ayant bu que du Volnay.

(Q16) SELECT NOM
      FROM BUVEURS B, ABUS A
      WHERE B.NB = A.NB 
        AND A.QUANTITE ≥ ALL
              SELECT QUANTITE
              FROM ABUS

(Q17) SELECT B.NOM
      FROM BUVEURS B, ABUS A
      WHERE B.NB = A.NB
        AND A.QUANTITE ≥ ANY
              SELECT QUANTITE
              FROM ABUS

(Q18) SELECT B.NOM
      FROM BUVEURS B
      WHERE EXISTS
              SELECT *
              FROM ABUS A, VINS V
              WHERE A.NV = V.NV
                AND A.NB = B.NB
                AND CRU = "VOLNAY"
 
(Q19) SELECT B.NOM
      FROM BUVEURS B
      WHERE NOT EXISTS
              SELECT *
              FROM ABUS A, VINS V
              WHERE A.NV = V.NV
                AND A.NB = B.NB
                AND CRU = "VOLNAY"

SQL1 permet également d'exprimer l'opération d'union. Par exemple, l'obtention des crus de degré supérieur à 13 ou de millésime 1977 peut s'effectuer par la question (Q20) représentée figure VII.11.

(Q20) SELECT CRU
      FROM VINS
      WHERE DEGRE > 13
    UNION
      SELECT CRU
      FROM VINS
      WHERE MILLESIME = 1977

Au-delà de l'algèbre relationnelle, des possibilités de calcul de fonctions existent. Les fonctions implantées sont :

• COUNT qui permet de compter le nombre de valeurs d'un ensemble ;
• SUM qui permet de sommer les valeurs d'un ensemble ;
• AVG qui permet de calculer la valeur moyenne d'un ensemble ;
• MAX qui permet de calculer la valeur maximale d'un ensemble ;
• MIN qui permet de calculer la valeur minimale d'un ensemble.

Les fonctions peuvent être utilisées dans la clause SELECT, par exemple pour calculer le degré moyen des « Chablis » comme dans la question (Q21) figure VII.12. Les fonctions sont aussi utilisables pour effectuer des calculs d'agrégats.

Rappelons qu'un agrégat est un partitionnement horizontal d'une table en sous-tables en fonction des valeurs d'un ou de plusieurs attributs de partitionnement, suivi de l'application d'une fonction de calculs à chaque attribut des sous-tables obtenues. Cette fonction est choisie parmi celles indiquées ci-dessus. En SQL, le partitionnement s'exprime par la clause :

GROUP BY <SPÉCIFICATION DE COLONNE>+

Cette dernière permet de préciser les attributs de partitionnement, alors que les fonctions de calculs appliquées aux ensembles générés sont directement indiquées dans les expressions de valeurs suivant le SELECT.

Une restriction peut être appliquée avant calcul de l'agrégat au niveau de la clause WHERE, mais aussi après calcul de l'agrégat sur les résultats de ce dernier. Pour cela, une clause spéciale est ajoutée à la requête SELECT :

HAVING <EXPRESSION DE VALEURS>+

La figure VII.12 propose quelques exemples de calculs d'agrégats. La question (Q21) calcule simplement la moyenne des degrés des Chablis. La question (Q22) mixte jointure et agrégat : elle affiche pour chaque cru la somme des quantités bues ainsi que la moyenne des degrés des vins du cru. Les questions (Q23) et (Q24) combinent agrégats et restrictions : (Q23) calcule la moyenne des degrés pour tous les crus dont le degré minimal est supérieur à 12 ; (Q24) recherche tous les numéros de vins bus en quantité supérieure à 10 par plus de 100 buveurs. La question (Q25) démontre une combinaison de blocs imbriqués et d'agrégats avec restriction portant sur des calculs ; elle retrouve les noms des buveurs ayant bu depuis 1983 une quantité d'alcool supérieure à 100.

(Q21) SELECT AVG(DEGRE)
      FROM VINS
      WHERE CRU = "Chablis"

(Q22) SELECT CRU, SUM(QUANTITE), AVG(DEGRE)
      FROM VINS V, ABUS A
      WHERE V.NV = A.NV
      GROUP BY CRU

(Q23) SELECT CRU, AVG(DEGRE)
      FROM VINS
      GROUP BY CRU
      HAVING MIN(DEGRE)>12

(Q24) SELECT NV
      FROM ABUS
      WHERE QUANTITE>10
      GROUP BY NV
      HAVING COUNT(NB)>100

(Q25) SELECT NOM
      FROM BUVEURS
      WHERE NB IN
          SELECT NB
          FROM ABUS A, VINS V
          WHERE A.NV = V.NV
            AND DATE > 01-01-83
          GROUP BY NB
          HAVING SUM(QUANTITE*DEGRE/100)>100

L'insertion de tuples dans une relation permet de créer de nouvelles lignes. Elle peut s'effectuer soit par fourniture directe au terminal d'un tuple à insérer (ou d'une partie de tuple, les valeurs inconnues étant positionnées à NULL), soit par construction à partir d'une question des tuples à insérer. La première variante est l'insertion directe, la deuxième l'insertion via question. La syntaxe de la commande d'insertion de SQL1 est :

INSERT INTO <NOM DE TABLE> [(<NOM DE COLONNE> +)]
{VALUES (<CONSTANTE>+) | <COMMANDE DE RECHERCHE>}

Dans le cas où la liste de colonnes n'est pas spécifiée, tous les attributs de la relation doivent être fournis dans l'ordre de déclaration. Si seulement certaines colonnes sont spécifiées, les autres sont insérées avec la valeur nulle.

Une insertion à partir d'une commande de recherche permet de composer une relation à partir des tuples d'une relation existante, par recherche dans la base.

En guise d'illustration, la commande d'insertion d'un Juliénas 1983 de degré inconnu sous le numéro de vins 112 est représentée figure VII.13 (requête (I1)). Nous donnons aussi la commande (I2) insérant dans une table BONSBUVEURS tous les buveurs ayant bu des vins de qualité A. La table BONSBUVEURS de schéma (NB, NOM, PRENOM) a dû être créée auparavant.

(I1) INSERT INTO VINS (NV, CRU, MILLESIME)
     VALUE (112, JULIENAS, 1983)

(I2) INSERT INTO BONSBUVEURS
     SELECT  NB, NOM, PRENOM
     FROM   BUVEURS B, ABUS A, VINS V
     WHERE  B.NB = A.NB
        AND A.NV = V.NV
        AND V.QUALITE = "A"

La mise à jour permet de changer des valeurs d'attributs de tuples existants. La mise à jour d'une relation peut s'effectuer soit par fourniture directe des valeurs à modifier, soit par l'élaboration de ces valeurs à partir d'une expression. Les seuls tuples mis à jour sont ceux vérifiant une condition de recherche optionnelle fournie en argument d'une clause WHERE. Il est aussi possible de faire une mise à jour d'un seul tuple pointé par un curseur préalablement ouvert.

La syntaxe de la commande de mise à jour SQL1 est :

UPDATE <NOM DE TABLE>
SET {<NOM DE COLONNE> = {<EXPRESSION DE VALEUR> | NULL}}+
WHERE {<CONDITION DE RECHERCHE> | CURRENT OF <NOM DE CURSEUR>}

Par exemple, la mise à jour du degré du Juliénas 1983 par la valeur 13 s'effectuera par la requête (U1). L'accroissement des quantités bues de Volnay 1983 de 10% s'effectuera par la commande (U2).

(U1) UPDATE VINS
     SET DEGRE = 13
     WHERE CRU = "JULIENAS" AND MILLESIME = 1983

(U2) UPDATE ABUS
     SET QUANTITE = QUANTITE * 1.1
     WHERE NV IN
          SELECT NV
          FROM VINS
          WHERE CRUS = "JULIENAS"
            AND MILLESIME = "1983"

L'opération de suppression permet d'enlever d'une relation des tuples existants. Les tuples sont spécifiés à l'aide d'une condition de recherche, à moins que l'on désire supprimer tous les tuples d'une relation. La syntaxe de la commande de suppression est :

DELETE FROM <NOM DE TABLE>
[WHERE {<CONDITION DE RECHERCHE> | CURRENT OF <NOM DE CURSEUR>}]

Par exemple, la suppression de tous les abus de vins de degré inconnu s'effectuera par la commande (D1), et la suppression de tous les vins bus par MARTIN par la commande (D2).

(D1) DELETE
       FROM ABUS
       WHERE NV IN
          SELECT NV
          FROM VINS
          WHERE DEGRE IS NULL

(D2) DELETE
        FROM VINS
        WHERE NV IN
          SELECT NV
          FROM BUVEURS, ABUS
          WHERE ABUS.NB = BUVEURS.NB
            AND BUVEURS.NOM = "MARTIN"

Une transaction est initiée par un appel de procédure base de données, si une transaction n'est pas déjà active. Elle est terminée soit avec succès par un ordre de validation des mises à jour COMMIT WORK, soit en échec suite à un problème par un ordre ROLLBACK WORK. Tout se passe comme si les mises à jour étaient préparées seulement en mémoire lors des commandes UPDATE, INSERT et DELETE, puis intégrées de manière atomique à la base de données par l'ordre COMMIT WORK, ou annulées par l'ordre ROLLBACK WORK (voir figure VII.16).

La programmation de transaction dans un langage hôte nécessite le travail tuple à tuple. Pour cela, SQL propose la notion de curseur. Un curseur permet de repérer un tuple dans la base de données à l'aide d'une commande SELECT et d'un balayage séquentiel des tuples résultats. Il s'agit du moyen offert par SQL1 à la fois pour balayer séquentiellement les tuples résultats d'une question, et pour repérer un tuple dans la base. Un curseur est déclaré par la commande suivante :

DECLARE <NOM DE CURSEUR> CURSOR
FOR <COMMANDE DE RECHERCHE>
[ORDER BY {<SPÉCIFICATION DE COLONNE> [{ASC | DESC}]}+]

L'utilisation d'un curseur nécessite son ouverture par la commande :

OPEN <NOM DE CURSEUR>

Cette commande provoque l'exécution logique de la question et le positionnement du curseur sur le premier tuple résultat. Il est ensuite possible de lire successivement chaque tuple résultat et de faire avancer le curseur par la commande :

FETCH <NOM DE CURSEUR> INTO <NOM DE VARIABLE>+

La fin d'utilisation d'un curseur se signale au système par la commande :

CLOSE <NOM DE CURSEUR>

SQL1 permet de composer des modules, eux-mêmes composés de procédures constituées d'une commande SQL, pouvant inclure des curseurs. Une procédure peut posséder des paramètres d'appel ou de retour. Elle peut être exécutée depuis un langage hôte ou plus généralement par une commande d'appel du type :

EXEC <NOM DE PROCÉDURE> [(<PARAMÈTRES>+)]

En guise d'illustration, nous avons représenté figure VII.17 un module définissant trois procédures pour ouvrir, lire et fermer les bons vins (de qualité A). Nous touchons là à l'intégration de SQL dans un langage de programmation tel PASCAL, C ou FORTRAN, que nous détaillerons dans le paragraphe suivant. Les curseurs ont un rôle essentiel pour réaliser une telle intégration ; ils permettent d'effectuer des commandes de mises à jour tuple à tuple.

MODULE EXEMPLE
DECLARE BONVIN CURSOR
    FOR SELECT NV
    FROM VINS
    WHERE QUALITE = "A" ;
PROCEDURE OPENVIN ;
    OPEN BONVIN ;
PROCEDURE NEXTVIN(NV) NV INTEGER ;
    FETCH BONVIN INTO NV ;
PROCEDURE CLOSEVIN ;
    CLOSE BONVIN ;

L'intégration de SQL à un langage procédural tel que C, COBOL, FORTRAN, PASCAL ou PL1 pose différents problèmes :

1. L'exploitation tuple à tuple des résultats des requêtes nécessite l'usage de curseurs. La commande FETCH est alors utilisée dans une boucle pour lire un à un les tuples résultats. Des commandes de mises à jour avec l'option de recherche WHERE CURRENT OF <nom de curseur> peuvent être employées pour les mises à jour.
2. Les variables du langage de programmation doivent être passées au SGBD par l'intermédiaire des requêtes SQL. Toute constante peut ainsi être remplacée par une variable de programmation qui doit être déclarée dans une section de déclaration de variables SQL. Un résultat peut être assigné à une variable de programme déclarée à SQL en liste résultat de l'ordre FETCH.

La figure VII.18 donne un exemple de programme PASCAL utilisant SQL. Ce programme ajoute un abus à la base pour les buveurs de Lyon ayant participé à une réception le 10-08-96. Ceux-ci sont déterminés par interrogation de l'utilisateur qui doit répondre O (oui) suite à l'affichage du nom du buveur si celui-ci a participé à la réception. Il doit alors préciser la quantité bue ce jour par le buveur. Le vin bu est déterminé par la constante numvin.

PROGRAM EXEMPLE
REPONSE STRING;
RECORD TYPE BUVEUR
    NB INTEGER;
    NOM STRING;
    PRENOM STRING;
END RECORD;
EXEC SQL DECLARE SECTION END EXEC;
    CONS NUMVIN = 100;
    VAR B BUVEUR;
    VAR QUANTITÉ INTEGER;
    VAR CODE SQLCODE;
EXEC SQL END DECLARE SECTION END EXEC;
EXEC SQL DECLARE CURSOR PARTICIPANT FOR
    SELECT NB, NOM, PRENOM
    FROM BUVEURS
    WHERE ADRESSE LIKE "%LYON%"
END EXEC;
BEGIN
    EXEC SQL OPEN PARTICIPANT END EXEC;
    WHILE CODE ≠ 100 DO
        BEGIN
            EXEC SQL FETCH PARTICIPANT INTO B END EXEC;
            PRINT "NOM :", B.NOM, "PRENOM", B.PRENOM ;
            PRINT "CE BUVEUR A-T-IL PARTICIPÉ ?"
            READ REPONSE;
            IF REPONSE = "O" THEN
                BEGIN
PRINT "QUANTITÉ ?" ;
READ QUANTITÉ;
EXEC SQL INSERT INTO ABUS
(B.NB, NUMVIN, "10-08-96", QUANTITÉ) END EXEC;
                END;
        END;
    EXEC SQL CLOSE PARTICIPANT END EXEC;
END

En conclusion, on notera la lourdeur des programmes intégrant SQL à un langage de programmation. Ceci résulte tout d'abord de la différence de point de vue entre le modèle relationnel qui supporte des requêtes ensemblistes et les langages classiques qui sont avant tout séquentiels. Ces derniers permettent seulement d'écrire des programmes traitant un tuple (record) à la fois. D'où la nécessité de boucles complexes. La complexité résulte aussi de l'existence de deux systèmes de définition de types différents, dans notre exemple celui de PASCAL (RECORD TYPE) et celui de SQL (CREATE TABLE). D'où la nécessité de doubles déclarations. Tous ces problèmes sont souvent mieux résolus au niveau des langages de 4^e génération, qui malheureusement ne sont pas standardisés.

SQL2 entrée propose une standardisation des codes réponses en ajoutant une variable retour des commandes appelées SQLSTATE. En effet, avec SQL1, un seul code réponse est retourné dans une variable de type SQLCODE. Trois valeurs sont spécifiées : 0 pour exécution correcte, +100 pour absence de données et une valeur négative -n pour toute erreur, la valeur étant spécifiée par le concepteur du SGBD. Cela rend les programmes non portables. Afin d'augmenter la portabilité, un code retour SQLSTATE est ajouté (SQLCODE est gardé afin d'assurer la compatibilité avec SQL1). Le code SQLSTATE est composé de deux caractères spécifiant la classe d'erreur (par exemple 22 pour exception erreurs sur les données) et de trois caractères précisant la sous-classe (par exemple, 021 pour caractère invalide). Les codes classes et sous-classes sont partiellement standardisés.

Avec SQL2, il est possible de renommer des colonnes résultats. Cette fonctionnalité est très utile, par exemple lors du calcul d'un agrégat. Avec SQL1, la colonne de la table résultat prend souvent un nom dépendant du système. La clause AS de SQL2 permet de résoudre ce problème. Par exemple, une question calculant la moyenne des degrés par cru pourra maintenant spécifier un nom de colonne résultat MOYENNE comme suit :

SELECT   CRU, AVG(DEGRE) AS MOYENNE
FROM   VINS
GROUP BY CRU

Un autre ajout proposé à SQL1 au niveau de SQL2 entrée est la possibilité d'utiliser les mots clés de SQL comme des noms de table, d'attributs, etc. Pour ce faire, il suffit d'inclure ces mots clés entre doubles cotes. Par exemple, on pourra créer une table de nom SELECT par la commande :

CREATE TABLE "SELECT" (QUESTION CHAR(100))

En résumé, les extensions de SQL1 proposées au niveau de SQL2 entrée sont donc des corrections et clarifications nécessaires au langage. SQL2 entrée est donc le nouveau standard minimal que devrait à terme fournir les constructeurs de SGBD. Il permettra une meilleure portabilité des programmes. L'interface avec C est d'ailleurs aussi précisée au niveau de SQL2 entrée.

XII-6-2-1. Les extensions des types de données▲

SELECT   *
FROM   ABUS
WHERE   DATE - 21/08/1996 < N

CREATE DOMAINE MONNAIE IS DECIMAL(5,2)
DEFAULT (-1)
CHECK (VALUE = -1 OR VALUE > 0)
NOT NULL

CREATE TABLE FACTURE (NOM CHAR(5), MONTANT MONNAIE)

XII-6-2-2. Un meilleur support de l'intégrité▲

SET CONSTRAINTS <NOM DE CONTRAINTE>+ {DEFERRED | IMMEDIATE}

SET TRANSACTION <MODE>

XII-6-2-3. Une intégration étendue de l'algèbre relationnelle▲

SELECT *
FROM NONBUVEURS
OUTER UNION
      (SELECT *
       FROM BUVEURS
    EXCEPT
       SELECT *
       FROM BUVEURS
       WHERE TYPE = "GROS")

FROM <NOM DE TABLE> [NATURAL] [{LEFT | RIGHT}]
     JOIN <NOM DE TABLE>
     [ON (<SPÉCIFICATION DE COLONNE>+=<SPÉCIFICATION DE COLONNE>+)]

SELECT NB, NOM, SUM(QUANTITE)
FROM BUVEURS NATURAL LEFT JOIN ABUS
GROUP BY NB

XII-6-2-4. La possibilité de modifier les schémas▲

ALTER TABLE <nom de table> <altération>

ALTER TABLE VINS ADD COLUMN REGION CHAR VARYING

XII-6-2-5. L'exécution immédiate des commandes SQL▲

SQL2 offre un niveau complet (FULL SQL2) qui comporte en plus les fonctionnalités suivantes :

• type de données chaînes de bits (BIT) pour supporter des objets complexes tels des images ;
• extension du support des dates et temps, notamment avec la possibilité de définir des zones d'heures, des intervalles de temps d'échelles variées, etc. ;
• expressions de requêtes SELECT étendues avec correspondances de colonnes possibles ; par exemple, lors d'une union, deux colonnes de noms différents peuvent être transformées en une seule colonne ;
• support de tables temporaires privées détruites en fin de transaction ;
• possibilité de SELECT en argument d'un FROM afin de construire une table temporaire à l'intérieur d'une requête SQL ;
• maintenance de vues concrètes facilitant l'interrogation de vues peu modifiées, notamment de vues avec agrégats ;
• support de contraintes d'intégrité multitables à l'aide de sous-questions intégrables dans la clause CHECK de déclaration de contraintes.

Ces multiples extensions et quelques autres, par exemple pour généraliser la maintenance automatique des contraintes référentielles lors des mises à jour, font de SQL2 complet un langage plutôt complexe pour manipuler des bases de données relationnelles. La spécification de SQL2 comporte 522 pages de syntaxe.