IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Cours complet pour apprendre les différents types de bases de données et le langage SQL


précédentsommairesuivant

VII. SQL

VII-A. Introduction

Le langage SQL est la version commercialisée du langage SEQUEL du prototype System R développé à IBM San José à partir de 1975.

Il a depuis fait l'objet de plusieurs normes dont la plus récente est SQL-1992, qui est implantée plus ou moins complètement par tous les SGBD relationnels actuellement disponibles.

Une nouvelle norme SQL:1999 a vu le jour, qui concerne les SGBD objet-relationnels.

Le langage décrit dans ce chapitre est conforme à la norme SQL-1992.

VII-B. Objectifs

SQL est un langage qui permet de manipuler (c'est-à-dire créer, modifier, supprimer, interroger) tous les types d'objets d'une BD relationnelle :

  • la BD ;
  • les domaines ;
  • les tables ;
  • les règles ;
  • les vues ;
  • les index…

VII-C. Panorama des commandes SQL

Sur les objets :

  • CREATE t n pour créer un objet de type t et de nom n
  • ALTER t n pour modifier le schéma d'un objet de type t et de nom n
  • DROP t n pour supprimer un objet de type t et de nom n

Sur les tables :

  • INSERT pour ajouter des lignes à une table
  • REPLACE pour modifier des lignes d'une table
  • DELETE pour supprimer des lignes d'une table
  • SELECT pour extraire des données à partir de tables existantes

VII-D. Principaux types de valeur

Catégorie

Type

Instance

Constante littérale (exemples)

Booléens

BOOLEAN

vrai ou faux

TRUE FALSE

Numériques

INT ou INTEGER

nombre entier

1515 -269

REAL ou FLOAT

nombre flottant

273.15 2731.5E-1

DECIMAL(n, d)

nombre décimal à n chiffres à d chiffres après la virgule

3.14 est une instance du type DECIMAL (3, 2)

Textuels

CHAR(n)

chaîne de caractères de longueur fixe n

'BD' (pourra être complété par n - 2 blancs si n > 2)

VARCHAR(n)

chaîne de caractères de longueur maximum n

'bonjour'

Temporels

DATE

date : jour, mois et année

DATE '2007-14-02'

TIME

temps : heure, minutes, secondes

TIME '13:15:00'

TIMESTAMP

date + temps

TIMESTAMP '2007-14-02 13:15:00'

INTERVAL

durée en années, mois, jours, heures, minutes ou secondes

INTERVAL '6 days 4 hours'

VII-E. Création de domaine

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE DOMAIN orientation
    CHAR(2)
    CHECK (VALUE IN ('N', 'S', 'O', 'E',
                     'NO', 'SO','NE', 'SE'));
CREATE DOMAIN nom_pays_himalayen
    VARCHAR(20)
    CHECK (VALUE IN ('Chine', 'Inde', 'Népal',
                     'Pakistan'));
CREATE DOMAIN nom_pays
    VARCHAR(20)
    CHECK (VALUE IN ('Afghanistan', 'Afrique du Sud',
                     'Albanie', …));

VII-F. Création de table

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE nom (
    nom_attribut type valeur_par_défaut contrainte
    …
    nom_attribut type valeur_par_défaut contrainte
    contrainte de table
    …
    contrainte de table)
 
Sélectionnez
1.
2.
3.
4.
5.
CREATE TABLE sommet (
    nom VARCHAR(20) PRIMARY KEY,
    altitude INTEGER CHECK (altitude >= 8000),
    date DATE,
    face orientation)

Si l'on avait voulu que l'altitude soit exprimée en mètres et centimètres, on aurait pu définir l'attribut altitude par :

  • altitude DECIMAL(6, 2)
 
Sélectionnez
1.
2.
3.
4.
CREATE TABLE localisation (
    nom_sommet VARCHAR(20) REFERENCES sommet(nom),
    pays nom_pays_himalayen,
    PRIMARY KEY (nom_sommet, pays));
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE grimpeur (
    nom VARCHAR(20),
    prénom VARCHAR(20),
    pays nom_pays,
    PRIMARY KEY (nom, prénom))

CREATE TABLE ascension (
    nom_grimpeur VARCHAR(20) ,
    prénom_grimpeur VARCHAR(20) ,
    nom_sommet VARCHAR(20) REFERENCES sommet(nom),
    PRIMARY KEY (nom_grimpeur,
                 prénom_grimpeur,
                 nom_sommet),
    FOREIGN KEY (nom_grimpeur, prénom_grimpeur)
      REFERENCES grimpeur (nom, prénom));

VII-G. Structure d'une requête SQL

Une requête SQL est composée à partir :

  • d'un opérateur de base :

    • SELECT
    • FROM
    • WHERE
    • GROUP BY
    • HAVING
    • ORDER BY

    qui produit une table ;

  • d'opérateurs d'union, d'intersection ou de différence de tables ;
  • des opérateurs de jointures.

VII-G-1. L'opérateur SELECT…FROM…WHERE

Image non disponible

VII-G-2. Requêtes monotable

Q1. Noms des sommets de plus 8000 m et année de leur 1re ascension ?

 
Sélectionnez
1.
2.
SELECT nom
FROM sommet;

Q2a. Sommets de plus de 8500 m dont la 1re ascension a été réalisée après 1955 sur une face sud ou sud-est ?

 
Sélectionnez
1.
2.
3.
4.
SELECT *
FROM sommet
WHERE altitude > 8500 AND
         (face = 'SE' OR face = 'SO');

Q2b. Idem avec l'opérateur IN

 
Sélectionnez
1.
2.
3.
SELECT *
FROM sommet
WHERE altitude > 8500 AND face IN ('S', 'N');

Q3. Nom et année de la 1re ascension de chaque sommet de plus de 8000 m ?

 
Sélectionnez
1.
2.
SELECT nom, EXTRACT('year' FROM date) AS année
FROM sommet;

Q4. Nom et altitude en pieds des sommets de plus 8000 m dont le nom contient le mot « Peak » ?

 
Sélectionnez
1.
2.
3.
SELECT nom, 3.29 * altitude AS altitude_en_pieds
FROM sommet
WHERE nom LIKE '%Peak%';

Q5. Nom et altitude des sommets dont l'altitude est comprise entre 8100 m et 8500 m ?

 
Sélectionnez
1.
2.
3.
SELECT nom, altitude
FROM sommet
WHERE altitude BETWEEN 8100 AND 8500;

VII-G-3. Jointure et utilisation de synonymes

Q6a. Donner pour chaque 1re ascension d'un sommet de plus de 8500 m le nom du grimpeur, le nom du sommet et son altitude ?

 
Sélectionnez
1.
2.
3.
4.
SELECT ascension.nom_grimpeur, sommet.nom, sommet.altitude
FROM ascension, sommet
WHERE ascension.nom_sommet = sommet.nom AND
         sommet.altitude > 8500;

Q6b. Idem en désignant les tables ascension et sommet par les synonymes a et s ?

 
Sélectionnez
1.
2.
3.
SELECT a.nom_grimpeur, s.nom, s.altitude
FROM ascension a, sommet s
WHERE a.nom_sommet = s.nom AND s.altitude > 8500;

Q6c. Idem sans qualifier les attributs, car il n'y a pas d'ambiguïté sur leur table de provenance ?

 
Sélectionnez
1.
2.
3.
SELECT nom_grimpeur, nom, altitude
FROM ascension, sommet
WHERE nom_sommet = nom AND altitude > 8500;

VII-G-4. Éliminer les doubles : la clause DISTINCT

Q7. Nom des pays dont un grimpeur a réalisé la 1re ascension d'un sommet de plus 8000 m ?

 
Sélectionnez
1.
2.
SELECT DISTINCT pays
FROM grimpeur;

Q8. Nom et prénom des grimpeurs ayant réalisé la 1re ascension d'un sommet de plus de 8000 m du Pakistan ?

 
Sélectionnez
1.
2.
3.
4.
SELECT DISTINCT a.nom_grimpeur, a.prénom_grimpeur
FROM ascension a, localisation l
WHERE a.nom_sommet = l.nom_sommet AND
      l.pays = 'Pakistan';

VII-G-5. Opérateurs d'agrégation

Q9. Nombre de sommets de plus de 8000 m ?

 
Sélectionnez
1.
2.
SELECT COUNT(*)
FROM sommet;

Q10. Nombre de pays possédant un sommet de plus de 8000 m ?

 
Sélectionnez
1.
2.
SELECT COUNT(DISTINCT pays)
FROM localisation;

Q11. Altitudes minimale, moyenne et maximale des sommets de plus de 8000 m ?

 
Sélectionnez
1.
2.
SELECT MIN(altitude), AVG(altitude), MAX(altitude)
FROM sommet;

VII-G-6. Sous-requêtes

VII-G-6-a. Comparaison valeur/table

Q12. Nom des sommets dont l'altitude est supérieure à l'altitude du Makalu ?

 
Sélectionnez
1.
2.
3.
4.
SELECT nom  FROM sommet  WHERE altitude >
    (SELECT altitude
     FROM sommet
     WHERE nom = 'Makalu');
VII-G-6-b. IN et NOT IN

Q13. Nom des pays dont un grimpeur a réalisé la 1re ascension du Dhaulagiri ?

 
Sélectionnez
1.
2.
3.
4.
5.
6.
SELECT DISTINCT pays
FROM grimpeur
WHERE (nom, prénom) IN
   (SELECT nom_grimpeur, prénom_grimpeur
    FROM ascension
    WHERE nom_sommet = 'Dhaulagiri');

Q14. Nom et prénom des grimpeurs n'appartenant pas à un pays possédant un sommet de plus 8000 m ?

 
Sélectionnez
1.
2.
3.
4.
5.
SELECT nom, prénom
FROM grimpeur
WHERE pays NOT IN
    (SELECT pays
     FROM localisation);
VII-G-6-c. Opérateur ALL

Q15. Nom du sommet le plus haut ?

 
Sélectionnez
1.
2.
3.
4.
5.
SELECT nom
FROM sommet
WHERE altitude >=ALL
    (SELECT altitude
     FROM sommet);
VII-G-6-d. Quantification existentielle

Q16. Nom des grimpeurs ayant réalisé une 1re ascension d'un sommet de plus de 8000 m avec Hermann Buhl ?

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT DISTINCT a1.nom_grimpeur,
                a1.prénom_grimpeur
FROM ascension a1
WHERE EXISTS
    (SELECT *
     FROM ascension a2
     WHERE a2.nom_grimpeur = 'Buhl' AND
           a2.prénom_grimpeur = 'Hermann' AND
           a2.nom_grimpeur <> a1.nom_grimpeur AND
           a2.prénom_grimpeur <>a1.prénom_grimpeur AND
           a2.nom_sommet = a1.nom_sommet);

VII-G-7. Union, intersection et différence

Q17. Nom des sommets de plus de 8500 m situés au Népal mais pas sur la frontière avec la Chine ?

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
(SELECT nom
 FROM sommet
 WHERE altitude > 8500)
INTERSECT
  (SELECT nom_sommet
   FROM localisation
   WHERE pays = 'Népal')
EXCEPT
  (SELECT nom_sommet
   FROM localisation
   WHERE pays = 'Chine');

VII-G-8. Tri

Q18. Lister les noms des sommets de plus de 8000 m par ordre alphabétique.

 
Sélectionnez
1.
2.
3.
SELECT nom
FROM sommet
ORDER BY nom ASC;

Q19. Lister les triplets (p, s, a) où p est un nom de pays, s est le nom d'un sommet de plus de 8000 m de p et a est l'altitude de s. Trier le résultat par ordre alphabétique de pays et par ordre décroissant d'altitude.

 
Sélectionnez
1.
2.
3.
4.
SELECT l.pays, s.nom, s.altitude
FROM localisation l, sommet s
WHERE l.nom_sommet = s.nom
ORDER BY l.pays ASC, s.altitude DESC;

VII-G-9. Groupement et agrégation

Image non disponible

Q20. Nombre de sommets de plus de 8000 m de chaque pays en possédant ?

 
Sélectionnez
1.
2.
3.
SELECT pays, COUNT(*)
FROM localisation
GROUP BY pays;

Q21. Donner pour chaque pays : son nom et le nombre de sommets de plus de 8000 m dont la 1re ascension a été réalisée par un grimpeur de ce pays. Trier le résultat par nombre décroissant de sommets ?

 
Sélectionnez
1.
2.
3.
4.
5.
SELECT g.pays, COUNT(DISTINCT a.nom_sommet)
FROM ascension a, grimpeur g
WHERE a.nom_grimpeur = g.nom
GROUP BY g.pays
ORDER BY 2 DESC;

Q22. Noms des sommets de plus de 8000 m dont la 1re ascension a été réalisée par plus de cinq grimpeurs et nombre de ces grimpeurs ?

 
Sélectionnez
1.
2.
3.
4.
SELECT nom_sommet, COUNT(*)
FROM ascension
GROUP BY nom_sommet
HAVING COUNT(*) > 5;

Q23. Nom et prénom des grimpeurs ayant réalisé la 1re ascension de plus d'un sommet de plus de 8000 m ?

 
Sélectionnez
1.
2.
3.
4.
5.
6.
SELECT g.nom, g.prénom
FROM ascension a, grimpeur g
WHERE a.nom_grimpeur = g.nom AND
      a.prénom_grimpeur = g.prénom
GROUP BY g.nom, g.prénom
HAVING COUNT(*) > 1;

VII-G-10. Construction de tables dans la clause FROM

Q25. Nombre maximum de sommets de plus 8000 m localisés dans un même pays ?

 
Sélectionnez
1.
2.
3.
4.
SELECT max(histo.nb)
FROM (SELECT pays, COUNT(*) AS nb
      FROM localisation
      GROUP BY pays) AS histo;

VII-G-11. Opérateurs de jointure dans la clause FROM

Q26. Donner le prénom, le nom et le nom du sommet pour chaque grimpeur ayant réalisé la 1re ascension d'un sommet de plus de 8000 m de l'Inde ?

 
Sélectionnez
1.
2.
3.
SELECT prénom_grimpeur, nom_grimpeur, nom_sommet
FROM ascension NATURAL INNER JOIN localisation
WHERE pays = 'Inde';

Q27. Parmi tous les grimpeurs ayant réalisé la 1re ascension d'un sommet de plus de 8000 m, donner le pourcentage de ceux qui l'ont fait sur un sommet du Népal ?

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
SELECT (CAST(COUNT(pays) AS FLOAT) / COUNT(*)) * 100
FROM (SELECT *
         FROM ascension) AS a
      NATURAL LEFT OUTER JOIN
       (SELECT *
        FROM localisation
        WHERE pays = 'Népal') AS l;

VII-H. Manipulation des valeurs nulles

Les comparateurs IS NULL et IS NOT NULL permettent de tester si une valeur est nulle :

  • e IS NULL a la valeur TRUE si e a la valeur NULL, elle a la valeur FALSE sinon ;
  • e IS NOT NULL a la valeur FALSE si e a la valeur NULL, elle a la valeur TRUE sinon.

Si l'un des opérandes d'un opérateur a la valeur NULL, le résultat de l'opération est la valeur NULL.

Pour traiter les comparateurs (autres que IS NULL et IS NOT NULL) et les connecteurs logiques, on introduit une troisième valeur de vérité : UNKNOWN, en plus des valeurs TRUE et FALSE.

  • La comparaison entre la valeur NULL et toute autre valeur y compris NULL a pour valeur UNKNOWN.
  • On redéfinit les tables de vérité des trois connecteurs logiques en tenant compte de cette 3e valeur de vérité.
Image non disponible
Tables de vérité à 3 valeurs des connecteurs logiques

Supposons que la relation sommet ait l'extension suivante :

sommet

nom

altitude

année

face

Everest
K2

8848
8611

-
1954

N
SE

La requête :

 
Sélectionnez
1.
2.
3.
SELECT COUNT(*)
FROM sommet
WHERE année < 1950

retourne le n-uplet (0).

La requête :

 
Sélectionnez
1.
2.
3.
SELECT nom
FROM sommet
WHERE année IS NULL;

retourne le n-uplet ('Everest').

La requête :

 
Sélectionnez
1.
2.
3.
SELECT nom
FROM sommet
WHERE année IS NOT NULL;

retourne le n-uplet ('K2').

Q28. Noms des sommets de plus de 8000 m qui ne sont pas situés au Népal ?

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
SELECT s.nom
FROM sommet AS s
        LEFT OUTER JOIN
         (SELECT DISTINCT nom_sommet
          FROM localisation
          WHERE pays = 'Népal') AS l
        ON s.nom = l.nom_sommet
WHERE l.nom_sommet IS NULL;

(on sélectionne les noms des sommets qui ne joignent pas avec les sommets du Népal)

VII-I. Vues

Une vue est une relation virtuelle, exprimée en termes d'une requête SQL.

Les vues permettent :

  • de simplifier l'expression des requêtes ;
  • de définir des sous-ensembles de la BD sur lesquels il sera possible de spécifier des droits d'accès.

Créer une vue ascension_népalaise contenant le nom des sommets de plus de 8000 m dont la 1re ascension a été réalisée par un grimpeur népalais et l'année de cette ascension.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
CREATE VIEW ascension_népalaise(sommet, année) AS
SELECT DISTINCT a.nom_sommet, s.année
FROM ascension a, grimpeur g, sommet s
WHERE a.nom_grimpeur = g.nom AND
      a.prénom_grimpeur = g.prénom AND
      a.nom_sommet = s.nom AND
      g.pays = 'Népal';

Nombre de sommets de plus de 8000 m dont la 1re ascension a été réalisée par un grimpeur népalais ?

 
Sélectionnez
1.
2.
SELECT COUNT(*)
FROM ascension_népalaise;

Supprimer la vue ascension_népalaise.

 
Sélectionnez
1.
DROP VIEW ascension_népalaise;

VII-J. Utilisation d'une vue pour décomposer une requête complexe

Nom des pays possédant le plus de sommets de plus de 8000 m ?

 
Sélectionnez
1.
2.
3.
4.
CREATE VIEW histo AS
SELECT pays, COUNT(*) AS nb
FROM localisation
GROUP BY pays;
 
Sélectionnez
1.
2.
3.
4.
5.
SELECT pays
FROM histo
WHERE nb =
    (SELECT MAX(nb)
     FROM histo);
 
Sélectionnez
1.
DROP VIEW histo;

VII-K. Mise à jour d'une table

Insérer le sommet Everest ?

 
Sélectionnez
1.
2.
INSERT INTO sommet
VALUES ('Everest ', 8850, 1953, 'SE')

Mettre à jour l'altitude de l'Everest ?

 
Sélectionnez
1.
2.
3.
UPDATE sommet
SET altitude = 8848
WHERE nom = 'Everest';

Renommer altitude_en_mètres la colonne altitude, ajouter une colonne altitude_en_pieds et y insérer les altitudes en pieds des sommets calculées à partir des altitudes en mètres.

 
Sélectionnez
1.
2.
ALTER TABLE sommet
RENAME altitude TO altitude_en_mètres;
 
Sélectionnez
1.
2.
ALTER TABLE sommet
ADD COLUMN altitude_en_pieds INTEGER;
 
Sélectionnez
1.
2.
UPDATE sommet
SET altitude_en_pieds = 3.28 * altitude_en_mètres;

VII-K-1. Mise à jour au travers d'une vue

Pour être utilisée pour une mise à jour, une vue doit vérifier les conditions suivantes :

  • La vue ne doit pas inclure la clause DISTINCT.
  • Chaque élément de la clause SELECT doit être un nom de colonne.
  • La clause FROM ne doit contenir qu'une seule table, elle-même modifiable.
  • La clause WHERE ne doit pas contenir de sous-requête.
  • La vue ne doit contenir ni clause GROUP BY, ni clause HAVING.

VII-L. Maintien de l'intégrité

Il existe plusieurs façons d'exprimer les contraintes d'intégrité d'une BD :

  • définition du domaine ou du type d'un attribut ;
  • condition sur les valeurs des attributs d'un n-uplet (clause CHECK de SQL) ;
  • définition de la clé primaire et des clés étrangères d'une relation ;
  • assertions générales ;
  • déclencheurs.

Une assertion est une formule logique qui doit être vraie quelle que soit l'extension de la BD.

Un déclencheur est une règle, dite active, de la forme :

  • « Événement - condition - action ».
  • L'action est déclenchée à la suite de l'événement, si la condition est vérifiée.
  • Une action peut être une vérification ou une mise à jour.

VII-L-1. Assertions

Une assertion est une formule logique qui doit être vraie quelle que soit l'extension de la BD.

Attention ! la vérification d'une assertion peut être une opération coûteuse, car elle doit être mise en œuvre après chaque mise à jour de la BD.

Une assertion est créée par la commande SQL :

 
Sélectionnez
1.
CREATE ASSERTION nom CHECK (condition)

Soit la BD contenant les deux relations suivantes :

  • employé(nom_emp, nom_dept, salaire) ;
  • département(nom_dept, directeur, nb_emp).

La contrainte d'intégrité : « Tout employé du département 'Recherche' doit avoir un salaire supérieur à 3000 € », s'exprime par l'assertion suivante :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE ASSERTION CHECK
  (NOT EXISTS
    (SELECT *
     FROM employe
     WHERE salaire <= 3000 AND
           nom_dept =
            (SELECT nom_dept
             FROM departement
             WHERE nom_dept = 'Recherche')))

VII-L-2. Déclencheurs

Un déclencheur est une règle, dite active, de la forme :

  • « Événement-condition-action ».
  • L'action est déclenchée à la suite de l'événement et si la condition est vérifiée.
  • Une action peut être une vérification ou une mise à jour.

Un déclencheur est activé par une requête de mise à jour.

Pour définir un déclencheur, il faut :

  • spécifier l'événement qui déclenche l'action en indiquant le type de la mise à jour (INSERT, UPDATE, DELETE), le nom de la relation et éventuellement le nom des attributs mis à jour ;
  • indiquer si l'action est réalisée avant, après ou à la place de la mise à jour ;
  • donner un nom à l'ancien et au nouveau n-uplet (uniquement le nouveau en cas d'insertion et uniquement l'ancien en cas de suppression) ;
  • décrire la condition sous laquelle se déclenche l'événement sous la forme d'une expression SQL booléenne, c'est-à-dire une expression pouvant être placée dans une clause WHERE ;
  • décrire l'action à réaliser sous la forme d'une procédure SQL ;
  • indiquer si l'action est réalisée pour chaque n-uplet mis à jour ou une seule fois pour la requête.

Création d'un déclencheur qui signale les mises à jour de salaire qui sont des diminutions et les empêche.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
CREATE TRIGGER diminution_salaire
BEFORE UPDATE OF salaire ON employe
REFERENCING OLD ROW AS a
            NEW ROW AS n
FOR EACH ROW
WHEN n.salaire < a.salaire

Procédure SQL signalant que le nouveau salaire est trop bas et empêchant la mise à jour.

Création d'un déclencheur mettant automatiquement à jour le nombre d'employés d'un département à la suite de l'insertion d'un nouvel employé.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
CREATE TRIGGER incrémenter_nombre_employés
AFTER INSERT ON employé
REFERENCING NEW ROW AS e
FOR EACH ROW
  SET nb_emp = nb_emp + 1
  WHERE num_dept = e.num_dept);

VII-M. Confidentialité

Une BD doit être protégée des accès malveillants.

La solution adoptée classiquement consiste à n'autoriser un utilisateur à effectuer une opération sur un objet que s'il en a obtenu le droit (ou le privilège).

L'identification des utilisateurs se fait en général par leur nom et par un mot de passe. Des procédés plus sophistiqués peuvent être utilisés comme les cartes à puce ou la reconnaissance des empreintes digitales ou rétiniennes.

Dans un SGBD relationnel :

  • les objets à protéger sont les tables et les vues ;
  • les opérations sont SELECT, INSERT, UPDATE ou DELETE.

L'attribution des droits peut être :

  • centralisée : l'administrateur a tous les droits sur tous les objets de la BD et peut transmettre certains de ces droits à d'autres utilisateurs ;
  • décentralisée : l'utilisateur qui crée un objet a tous les droits sur cet objet et peut les transmettre en totalité ou en partie à d'autres utilisateurs.

VII-M-1. Protection par les vues

Les vues jouent un rôle important pour la confidentialité en permettant de spécifier de façon très fine les données auxquels un utilisateur a le droit d'accéder.

On pourra spécifier un accès à l'affectation des employés dans les départements en définissant la vue suivante :

 
Sélectionnez
1.
2.
3.
4.
CREATE VIEW affectation(nom_emp, nom_dept) AS
  SELECT e.nom_emp, d.nom_dept
  FROM employe AS e, departement AS d
  WHERE e.nom_dept = d.nom_dept;

Un utilisateur dont les droits d'accès se limitent à la vue affectation ne pourra connaître ni le numéro et le salaire d'un employé, ni le numéro et le directeur d'un département.

VII-M-2. GRANT et REVOKE

Une utilisatrice ayant créé les relations employe et departement ainsi que la vue affectation pourra :

Transmettre à l'utilisateur Jean Dupont le droit de consulter les employés et de modifier le salaire d'un employé :

 
Sélectionnez
1.
2.
3.
GRANT SELECT, UPDATE(salaire)
ON employe
TO 'Jean Dupont'

Transmettre à tous les utilisateurs le droit de consulter les affectations des employés :

 
Sélectionnez
1.
GRANT SELECT ON affectation TO PUBLIC

Retirer à Jean Dupont le droit de modifier le salaire d'un employé :

 
Sélectionnez
1.
REVOKE UPDATE(salaire) ON employe TO 'Jean Dupont'

(Notons que Jean Dupont possède encore le droit de consulter les employés.)

VII-N. Intégration de SQL dans un programme

Les principes sont les suivants :

Chaque instruction SQL est préfixée par EXEC SQL.

Ces instructions peuvent contenir des variables du programme qui :

  • sont préfixées par« : » ;
  • doivent avoir un type compatible avec les valeurs d'attributs qui leur seront affectées.

Un mécanisme de curseur permet de parcourir une table ligne par ligne et d'affecter les valeurs de chaque ligne à des variables du programme.

Le programme doit posséder une variable numérique SQLCODE dont la valeur résume l'exécution, correcte ou incorrecte, d'une instruction SQL.

Affecter à la variable salaire le salaire de l'employé dont le nom est donné dans la variable nom ?

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
EXEC SQL BEGIN DECLARE SECTION;
char nom[20];
int salaire;
EXEC SQL END DECLARE SECTION;
main()
  {
    scanf("%s", nom);
    EXEC SQL CONNECT TO ma_bd;
    EXEC SQL
      SELECT salaire INTO :salaire
      FROM employe
      WHERE nom_emp = :nom;
    printf("salaire = %d", salaire);
    EXEC SQL DISCONNECT;
  }

Afficher le salaire en dollars de chaque employé du département « Recherche » ?

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
#include <stdio.h>
EXEC SQL BEGIN DECLARE SECTION;
char nom_emp[20];
int salaire;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE employe CURSOR FOR
  SELECT nom_emp, salaire
  FROM employe
  WHERE nom_dept IN
          (SELECT nom_dept
           FROM departement
           WHERE nom_dept = 'Recherche');
main()
  {
    EXEC SQL CONNECT TO ma_bd;
    EXEC SQL OPEN employe;
    EXEC SQL WHENEVER NOT FOUND DO BREAK;
    while (1)
      {
        EXEC SQL FETCH employe INTO :nom_emp, :salaire;
        printf("salaire en dollars de %s = %.2f\n",
               nom, 1.3 * :salaire);
      }
    EXEC SQL DISCONNECT;
  }

précédentsommairesuivant