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

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.

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…

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

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)

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)

CREATE TABLE localisation (
    nom_sommet VARCHAR(20) REFERENCES sommet(nom),
    pays nom_pays_himalayen,
    PRIMARY KEY (nom_sommet, pays));

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));

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▲

VII-G-2. Requêtes monotable▲

SELECT nom
FROM sommet;

SELECT *
FROM sommet
WHERE altitude > 8500 AND
         (face = 'SE' OR face = 'SO');

SELECT *
FROM sommet
WHERE altitude > 8500 AND face IN ('S', 'N');

SELECT nom, EXTRACT('year' FROM date) AS année
FROM sommet;

SELECT nom, 3.29 * altitude AS altitude_en_pieds
FROM sommet
WHERE nom LIKE '%Peak%';

SELECT nom, altitude
FROM sommet
WHERE altitude BETWEEN 8100 AND 8500;

VII-G-3. Jointure et utilisation de synonymes▲

SELECT ascension.nom_grimpeur, sommet.nom, sommet.altitude
FROM ascension, sommet
WHERE ascension.nom_sommet = sommet.nom AND
         sommet.altitude > 8500;

SELECT a.nom_grimpeur, s.nom, s.altitude
FROM ascension a, sommet s
WHERE a.nom_sommet = s.nom AND s.altitude > 8500;

SELECT nom_grimpeur, nom, altitude
FROM ascension, sommet
WHERE nom_sommet = nom AND altitude > 8500;

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

SELECT DISTINCT pays
FROM grimpeur;

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▲

SELECT COUNT(*)
FROM sommet;

SELECT COUNT(DISTINCT pays)
FROM localisation;

SELECT MIN(altitude), AVG(altitude), MAX(altitude)
FROM sommet;

VII-G-6. Sous-requêtes▲

SELECT nom  FROM sommet  WHERE altitude >
    (SELECT altitude
     FROM sommet
     WHERE nom = 'Makalu');

SELECT DISTINCT pays
FROM grimpeur
WHERE (nom, prénom) IN
   (SELECT nom_grimpeur, prénom_grimpeur
    FROM ascension
    WHERE nom_sommet = 'Dhaulagiri');

SELECT nom, prénom
FROM grimpeur
WHERE pays NOT IN
    (SELECT pays
     FROM localisation);

SELECT nom
FROM sommet
WHERE altitude >=ALL
    (SELECT altitude
     FROM sommet);

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▲

(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▲

SELECT nom
FROM sommet
ORDER BY nom ASC;

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▲

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 1^re 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 1^re 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 1^re 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▲

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▲

SELECT prénom_grimpeur, nom_grimpeur, nom_sommet
FROM ascension NATURAL INNER JOIN localisation
WHERE pays = 'Inde';

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;

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 3^e valeur de vérité.

Supposons que la relation sommet ait l'extension suivante :

La requête :

SELECT COUNT(*)
FROM sommet
WHERE année < 1950

retourne le n-uplet (0).

La requête :

SELECT nom
FROM sommet
WHERE année IS NULL;

retourne le n-uplet ('Everest').

La requête :

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 ?

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)

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 1^re ascension a été réalisée par un grimpeur népalais et l'année de cette ascension.

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 1^re ascension a été réalisée par un grimpeur népalais ?

SELECT COUNT(*)
FROM ascension_népalaise;

Supprimer la vue ascension_népalaise.

DROP VIEW ascension_népalaise;

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

CREATE VIEW histo AS
SELECT pays, COUNT(*) AS nb
FROM localisation
GROUP BY pays;

SELECT pays
FROM histo
WHERE nb =
    (SELECT MAX(nb)
     FROM histo);

DROP VIEW histo;

Insérer le sommet Everest ?

INSERT INTO sommet
VALUES ('Everest ', 8850, 1953, 'SE')

Mettre à jour l'altitude de l'Everest ?

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.

ALTER TABLE sommet
RENAME altitude TO altitude_en_mètres;

ALTER TABLE sommet
ADD COLUMN altitude_en_pieds INTEGER;

UPDATE sommet
SET altitude_en_pieds = 3.28 * altitude_en_mètres;

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

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▲

CREATE ASSERTION nom CHECK (condition)

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▲

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

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);

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▲

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;

VII-M-2. GRANT et REVOKE▲

GRANT SELECT, UPDATE(salaire)
ON employe
TO 'Jean Dupont'

GRANT SELECT ON affectation TO PUBLIC

REVOKE UPDATE(salaire) ON employe TO 'Jean Dupont'

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 ?

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 » ?

#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;
  }