Base de données relationnelle

Système de gestion de base de

données SGBDR
SQL

Habiba Chaoui
Filière Génie Informatique

École Nationale des Sciences Appliquées de Kenitra

Université Ibn Tofail

Année Universitaire 2017/2018

LE LANGAGE DE REQUETES SQL

1 – Origines et évolutions
- SQL est dérivé de l’algèbre relationnelle et du langage SEQUEL ( Structured English Query Language )

- Il a été intégré dès 1986 aux plateformes SQL / DS, DB2 , puis ORACLE , INGRES ,
INFORMIX , etc…

- Il existe 3 versions normalisées , du plus simple au plus complexe :

* SQL 1 ( 1986 ) : le langage de base
* SQL 1 ( 1989 ) : addendum ( contraintes d’intégrité sur les données )
* SQL 2 ( 1992 ) : langage complet à 3 niveaux
* SQL 3 ( 1998 ) : Les évolutions orientées objet ( SQL 3 n’est pas encore le nouveau standard )

2 – Les 3 niveaux du langage SQL

* Langage de définition de données ( LDD ) : Il permet de spécifier un schéma conceptuel de BD
CREATE , ALTER et DROP ( tables , indexes et vues )

* Langage de manipulation de données ( LMD ) : Il permet d’interroger et de manipuler les données

SELECT , INSERT , UPDATE , DELETE , OPEN , FETCH , CLOSE , etc…

* Langage de contrôle de données ( LCD ) : Il permet de gérer la sécurité des données, les transactions
et les accès concurrents GRANT et REVOKE , BEGIN et END TRANSACTION , COMMIT et
ROLLBACK, etc…
2

1
 LE LANGAGE DE MANIPULATION DE DONNEES ( LMD )

Les ordres LMD sont des instructions SQL créées à partir des commandes suivantes :
Commande Rôle
SELECT Interroger une base de données en vue d’extraire les enregistrements qui répondent à
des critères particuliers
INSERT Insérer ( charger ) des lots de données dans la base de données en une seule opération
UPDATE Modifier ( mettre à jour ) des valeurs d’attributs dans une table ou bien des valeurs
d’enregistrements entiers répondant à des critères particuliers
DELETE Supprimer des enregistrements dans une table de base de données sélectionnés d’après
un critère donné .

Chaque commande peut utiliser une ou plusieurs clauses obligatoires et des clauses optionnelles .
Les clauses permettent de définir l’origine et la nature des données qu’il faut sélectionner ou manipuler .
Clause Rôle
FROM Nommer une ou plusieurs tables ou vues à partir desquelles les enregistrements doivent
être sélectionnés
WHERE Spécifier des conditions de jointure et / ou de sélection sur les enregistrements
GROUP BY Spécifier les attributs de regroupement lors d’une opération de calcul et / ou
regroupement
HAVING Spécifier des conditions de sélection sur les enregistrements obtenus après une
opération de regroupement
ORDER BY Trier les enregistrements sélectionnés pour être projetés dans un ordre particulier
3

COMMANDE SELECT : Forme générale

SELECT [DISTINCT] < liste d’attributs de projection simples , calculés ou renommés >
FROM < liste de tables ou vues >
[WHERE < critère de jointure naturelle, théta-jointure, jointure externe > ]
[ AND < critère de sélection simple > ]
[ AND < critère de sélection complexe appelant une sous-requête > ]
[GROUP BY < liste d’attributs de regroupement > ]
[HAVING < critère de sélection sur des attributs calculés ou regroupés > ]
[UNION / INTERSECT / EXCEPT ( autre requête SELECT ) ]
[ORDER BY < liste d’attributs de tri avec ordre de tri > ]

Critère de sélection simple

* arithmétique ( = , < , > , <= , >= , <> )
* textuel ( LIKE )
* sur intervalle ( BETWEEN )
Critère de sélection complexe ( imbrication d’ordres SELECT )
* clause IN ou NOT IN , EXISTS ou NOT EXISTS
* clauses ALL , ANY , SOME , etc…
* sous-requêtes dépendantes ou corrélées 4

2
 COMMANDE SELECT : Les sélections ( restrictions ) simples
Relation utilisée : PRODUIT ( N_Produit , Libellé_Produit , Prix_U , Poids , Couleur )

* Liste des données sur les produits dont le poids est supérieur à 15 Kg
SELECT * FROM Produit WHERE Poids > 15 ;

* Liste des libellés de produits différents dont le poids est compris entre 15 et 40 Kg
SELECT DISTINCT Libellé _Produit FROM Produit WHERE Poids BETWEEN 15 AND 40 ;

* Liste des produits dont le poids n’est pas compris entre 15 et 40 Kg et dont la couleur est
verte , rouge ou bleue
SELECT * FROM Produit
WHERE Poids NOT BETWEEN 15 AND 40
AND Couleur IN ( ‘Vert’, ‘Rouge’, ‘Bleu’ ) ;

* Liste des produits dont le libellé ne commence pas par la lettre D

SELECT * FROM Produit WHERE Libellé _Produit NOT LIKE ‘D%’

* Liste des produits dont la couleur est renseignée

SELECT * FROM Produit WHERE Couleur IS NOT NULL ;

* Liste des produits dont la couleur est n’est pas renseignée

SELECT * FROM Produit WHERE Couleur IS NULL ;
5

COMMANDE SELECT : Les Sélections ( restrictions ) avec sous-requête

Schéma relationnel : DEPOT ( N_Dépôt, Nom_Dépôt, Ville )
STOCKER ( N_Dépôt # , N_Produit # , Qté_Stockée )
PRODUIT ( N_Produit , Libellé_Produit , Prix_U, Poids, Couleur )

•Sous-requête renvoyant une seule valeur ( relation à une seule ligne et une seule colonne ) :

Liste des dépôts situés dans la même ville que le dépôt N° 12

SELECT Nom_Dépôt FROM Dépôt
WHERE Ville = ( SELECT Ville FROM Dépôt WHERE N_Dépôt = 12 )
Liste des produits dont le prix unitaire est supérieur à celui du produit N° 36
SELECT Libellé_Produit FROM Produit
WHERE Prix_U > ( SELECT Prix_U FROM Produit WHERE N_Produit = 36 )

•Sous-requête renvoyant plusieurs valeurs ( relation à une seule colonne et plusieurs lignes ) :

L’opérateur : IN
Liste des produits dont la couleur est la même que celle de l’une des tables
SELECT Libellé_Produit FROM Produit
WHERE Couleur IN ( SELECT Couleur FROM Produit WHERE Libellé_Produit = ‘Table’ )
Liste des produits dont le prix unitaire est différent de celui de toutes les armoires
SELECT Libellé_Produit FROM Produit
WHERE Prix_U NOT IN ( SELECT Prix_U FROM Produit WHERE Libellé_Produit = ‘Armoire’ )
6

3
 COMMANDE SELECT : Les Sélections ( restrictions ) avec sous-requête ( suite 1 )

* Sous-requête renvoyant plusieurs valeurs ( relation à une seule colonne et plusieurs lignes ) :

Les opérateurs : ANY et ALL

ANY : la comparaison est vraie si elle est vraie pour au moins un des éléments de l'ensemble.
ALL : la comparaison sera vraie si elle est vraie pour tous les éléments de l'ensemble.
La forme ( = ANY ) est équivalente à la forme ( IN )
Les formes ( <> ANY ) et ( <> ALL ) sont équivalentes à la forme ( NOT IN )
=> Liste des produits dont le prix unitaire est inférieure à celui de l’armoire la plus chère
SELECT Libellé_Produit FROM Produit
WHERE Prix_U < ANY ( SELECT Prix_U FROM Produit WHERE Libellé_Produit = ‘Armoire’ )
=> Liste des produits dont le prix unitaire est inférieure à celui de l’armoire la moins chère
SELECT Libellé_Produit FROM Produit
WHERE Prix_U < ALL ( SELECT Prix_U FROM Produit WHERE Libellé_Produit = ‘Armoire’ )

* Sous-requête renvoyant plusieurs colonnes ( relation à une plusieurs colonnes ) :

=> Liste des produits dont le poids et la couleur sont identiques à ceux de l’article N° 125
SELECT Libellé_Produit FROM Produit
WHERE ( Poids , Couleur ) = ( SELECT Poids , Couleur FROM Produit WHERE N_Produit = 125 )

COMMANDE SELECT : Les Sélections ( restrictions ) avec sous-requête ( suite 2 )

* Sous-requête renvoyant au moins 1 ligne ( relation à 1 ou plusieurs colonnes comportant au moins 1 ligne ) :

L’opérateur : EXISTS
=> Liste des produits stockés dans au moins un dépôt avec une quantité supérieure à 1000 unités ?
SELECT Libellé_Produit FROM Produit AS P
WHERE EXISTS ( SELECT * FROM STOCKER WHERE [N_Produit#] = P. N_Produit
AND Qté_Stockée > 1000 )
=> Liste des produits qui ne sont stockés dans aucun dépôt ?
SELECT Libellé_Produit FROM Produit AS P
WHERE NOT EXISTS ( SELECT * FROM STOCKER WHERE [N_Produit#] = P. N_Produit )

* Sous-requêtes multiples

Lorsque les attributs de projection appartiennent tous à la requête principale , on peut utiliser plusieurs
sous-requêtes imbriquées au lieu d’utiliser des jointures
=> Liste des produits ( Libellé, Prix_U et Poids ) stockés à Tanger dans le dépôt ‘Grossisterie Znibar‘ ?
SELECT Libellé_Produit, Prix_U, Poids FROM Produit
WHERE N_Produit IN ( SELECT [ N_Produit# ] FROM STOCKER
WHERE [ N_Dépôt# ] IN ( SELECT N_Dépôt FROM Dépôt
WHERE Ville = ‘Tanger’
AND Nom_Dépôt = ‘Grossisterie Znibar’ )
8

4
 COMMANDE SELECT : Les Sélections ( restrictions ) avec sous-requête ( suite 3 )

•Les sous-requêtes dépendantes ou corrélées

C’est une forme très puissante des sous-requêtes .

La requête principale fournit plusieurs groupes de valeurs à la sous-requête . Pour chaque groupe de valeurs ,
la sous-requête est évaluée pour donner un résultat .
=> Liste des produits disponibles dans plusieurs couleurs ( au moins 2 )
SELECT Libellé_Produit FROM Produit AS P1
WHERE Libellé_Produit IN ( SELECT Libellé_Produit FROM Produit As P2
WHERE [Link] <> [Link] )
*la sous requête prend plusieurs résultats, et cela dépend

=> Liste des prix unitaires les plus élevés de chaque type de produit des valeurs de la requête,

SELECT Libellé_Produit, Prix_U FROM Produit AS P1 *on lu, chacun des produits à un nb de couleurs
WHERE Prix = ( SELECT MAX ( Prix_U ) FROM PRODUIT P2 *chaque type à un prix max
WHERE P2. Libellé_Produit = P1. Libellé_Produit ) * je veux afficher pour chaque ligne ses

propres
=> Liste des dépôts stockant tous les produits ( simulation de l’opérateur relationnel valeurs
‘Division‘ ) des colonnes
SELECT Nom_Dépôt , Ville FROM Dépôt AS D
WHERE NOT EXISTS ( SELECT * FROM Produit AS P
WHERE NOT EXISTS ( SELECT * FROM STOCKER S
WHERE S. [N_Produit#] = P. N_Produit
AND S. [N_Dépôt#] = D. N_Dépôt )

COMMANDE SELECT : Les Fonctions Statistiques

Le langage SQL offre la possibilité de récupérer des données chiffrées sur des tables ou des vues .
On peut par exemple obtenir le nombre de tuples répondant à un critère de sélection avec la fonction
COUNT , la valeur moyenne d’une colonne avec la fonction AVG , la valeur maximale ou minimale
et la somme d’une colonne avec les fonctions MAX , MIN et SUM .

=> Compter le nombre de dépôts où le produit N° 122 est stocké

SELECT COUNT(*) AS Compte FROM STOCKER WHERE [ N_Produit# ] = 122

=> Calculer la somme globale , la moyenne , le maximum et le minimum des quantités stockées du produit
N° 122

SELECT SUM ( Qté_Stockée ) , AVG ( Qté_Stockée ) , MAX ( Qté_Stockée ) , MIN ( Qté_Stockée )

FROM STOCKER WHERE [ N_Produit# ] = 122

=> Compter les noms de produits différents

SELECT COUNT ( DISTINCT Libellé_Produit ) AS Compte FROM Produit

10

5
 COMMANDE SELECT : Les Regroupements
On appelle « Groupe » un ensemble de lignes ( tuples ) dans une relation qui possèdent une valeur identique dans
une ou plusieurs colonnes . Cette colonne ( ou ensemble de colonnes ) peut être définie comme un
« facteur de regroupement » à l’aide de la clause « GROUP BY » de la commande SELECT .
SQL permet alors d’effectuer des calculs sur les autres colonnes ( qui ne sont pas des facteurs de regroupement )
en utilisant les fonctions statistiques ( fonctions d’agrégation ) : COUNT , SUM , AVG, MAX et MIN .
La clause « HAVING » permet d’appliquer une condition de sélection ( restriction ) à chaque groupe dans la
relation résultat de la requête au niveau des colonnes de regroupements et / ou de calcul .
Remarque : Dans la commande SELECT , les colonnes de calcul sont toujours spécifiées dans la liste des
attributs de projection . Toutes les autres colonnes ( non calculées ) figurant dans cette liste sont alors
considérées comme des facteurs de regroupement et doivent figurer dans la clause « GROUP BY » .

=> Liste du nombre de produits par type de produit

SELECT Libellé_Produit , COUNT(*) AS [Nombre articles ] FROM PRODUIT
GROUP BY Libellé_Produit ORDER BY 1 ;
=> Liste des N° de produits stockés dans plus de 2 dépôts
SELECT [ N_Produit# ] , COUNT(*) AS [Nombre dépôts] FROM STOCKER
GROUP BY [ N_Produit# ]
HAVING COUNT(*) > 2 ORDER BY 1 ;
=> Lister les dépôts de la ville de Rabat dont la valeur marchande est supérieure à 100 000 DH
SELECT Nom_Dépôt , SUM ( Prix_U*Qté_Stockée ) As [ Valeur en DH ]
FROM Dépôt As D , Stocker As S , Produit As P
WHERE D.N_Dépôt = S.[N_Dépôt#] AND S.[N_Produit#] = P.N_Produit AND Ville = ‘ Rabat ’
GROUP BY Nom_Dépôt
HAVING SUM ( Prix_U*Qté_Stockée ) > 100 000 ORDER BY 2 DESC 11

COMMANDE INSERT : Forme générale

1ère Forme
INSERT [INTO] < Nom de Table >
[ < Liste d’attributs entre parenthèses > ]
VALUES < Liste de valeurs correspondant aux attributs entre parenthèses >
2ème Forme
INSERT [INTO] < Nom de Table >
[ < Liste n° 1 d’attributs entre parenthèses > ]
SELECT < Liste n°2 d’attributs correspondant en type à ceux de la Liste n°1 >
FROM < liste de tables ou vues >
[WHERE < critère de jointure naturelle, théta-jointure, jointure externe > ]
[ AND < critère de sélection simple > ]
[ AND < critère de sélection complexe appelant une sous-requête > ]
[etc… ]
Remarques :
- Les attributs non spécifiés dans la liste n°1 restent à NULL ou à leur valeur par défaut après l’insertion de tuples
- On doit toujours fournir une valeur dans l’ordre INSERT pour les attributs déclarés NOT NULL
( déclaration effectuée lors de la création de la table )
12

6
 COMMANDE INSERT : Exemples
Schéma relationnel : PRODUIT ( N_Produit , Libellé_Produit , Prix_U , Poids , Couleur )
ARTICLE ( N_Article , Désignation , Prix_U )

* Insertion d’une ligne ( un tuple ) dans la table PRODUIT

INSERT INTO PRODUIT VALUES ( 20 , ‘VASE DE CHINE’ , 250 , 15 , ‘BLEU’ )

* Insertion de 2 lignes ( 2 tuples ) dans la table PRODUIT avec certaines valeurs nulles
INSERT INTO PRODUIT VALUES ( 21 , ‘VERRE CRISTAL’ , 50 , 0.25 , NULL ) ,
( 22 , ‘FOURCHETTE INOX’, 10 , NULL , NULL )

* Insertion de 2 lignes dans la table PRODUIT avec spécification des attributs d’insertion
INSERT INTO PRODUIT ( N_Produit , Libellé_Produit )
VALUES ( 23 , ‘CUILLERE INOX’ ) , ( 24 , ‘COUTEAU INOX’ )

* Insertion de tous les tuples de la table PRODUIT dont le prix est supérieur à 200 DH dans la Table ARTICLE :
( la structure des colonnes dans la table cible doit être la même que celle des colonnes dans la table source )
INSERT INTO ARTICLE
SELECT N_Produit , Libellé_Produit , Prix_U FROM PRODUIT
WHERE Prix_U > 200 ;

* Requête interdite : la duplication des tuples d’une table par un INSERT avec une sous-requête sur la même table
INSERT INTO PRODUIT
SELECT * FROM PRODUIT ;
13

COMMANDE UPDATE : Forme générale

1ème Forme
UPDATE < Nom de Table >
SET < Attribut1 = Valeur1 > ,
< Attribut2 = Valeur2 > , etc …
[WHERE < critère de sélection simple
ou critère de sélection complexe appelant une sous-requête > ]
2ème Forme
UPDATE < Nom de Table >
SET < Attribut1 = Valeur1 > ,
< Attribut2 = Valeur2 > , etc …
FROM < liste de tables ou vues >
WHERE < critère de jointure naturelle, théta-jointure, jointure externe >
[ AND < critère de sélection simple > ]
[ AND < critère de sélection complexe appelant une sous-requête > ]

14

7
 COMMANDE UPDATE : Exemples
Schéma relationnel : PRODUIT ( N_Produit , Libellé_Produit , Prix_U , Qté_Stock )
ACHETER ( N_Produit , N_Client , Qté_Achetée , Date_Achat )
CLIENT ( N_Client , Nom , Adresse , Tél , Chiffre_Affaire )

* Mise à jour du prix de tous les produits pour tenir compte d’une augmentation de 10 DH
UPDATE PRODUIT SET Prix_U = Prix_U + 10 ;

* Mise à jour des produits de luxe dont le prix est supérieur à 1000 DH seulement ( augmentation de 15 % )
UPDATE PRODUIT SET Prix_U = Prix_U * 1.15
WHERE Prix_U > 1000 ;

* Mise à la valeur nulle des adresses et téléphones et initialisation du chiffre d’affaires réalisé avec tous les clients
dont le nom commence par la lettre B ( dans le but de recommencer leur saisie )
UPDATE CLIENT SET Adresse = Null , Tél = Null , Chiffre_Affaire = 0
WHERE Nom LIKE ‘ B% ’ ;

* Mise à jour de la Qté en stock de tous les produits ayant fait l’objet de ventes durant la journée
du 10/01/2001 ( seule une vente par produit sera prise en compte à cette date )
UPDATE PRODUIT SET Qté_Stock = Qté_Stock - [Link]é_Achetée
FROM PRODUIT P , ACHETER A
WHERE P.N_Produit = A.N_Produit
AND A.Date_Achat = ‘ 10/01/01’

15

COMMANDE UPDATE : Exemples ( suite )

Attention !
Une instruction UPDATE ne met jamais à jour une même ligne à deux reprises.
Le schéma relationnel considéré suppose qu'un produit peut être vendu à plusieurs reprises
( acheté par plusieurs clients ) le même jour .
Avec l’ordre UPDATE précédent , il y aura exécution sans erreur, mais chaque produit ne sera mis à jour
qu'avec une seule vente, en dépit du nombre de ventes ayant réellement eu lieu à la date spécifiée .

Solution du problème :
Au cas où plusieurs ventes ont lieu le même jour pour un produit donné, toutes les ventes de chaque
produit doivent être additionnées dans l’ordre UPDATE et le stock mis à jour à l’aide de la somme obtenue ,
comme le montre l’ordre SQL suivant :

UPDATE PRODUIT P
SET Qté_Stock = Qté_Stock -
( SELECT SUM ( Qté_Achetée )
FROM ACHETER A
WHERE P.N_Produit = A.N_Produit
AND Date_Achat = ‘ 10/01/01’ ) ;

Autre requête à exprimer :

Mettre à jour le chiffre d’affaires réalisé avec tous les clients pour tenir compte des ventes
effectuées le dernier jour d’ouverture du magasin ? 16

8
 COMMANDE DELETE : Forme générale et Exemples

DELETE FROM < Nom de Table >

[WHERE < critère de sélection simple
ou critère de sélection complexe appelant une sous-requête > ]

Exemple : PILOTE ( N_Pilote , Nom , Prénom , Adresse , Salaire )

AVION ( N_Avion , Type , Capacité )
VOL ( N_Pilote , N_Avion , Date_Vol , Heure_Dép , Heure_Arr , Ville_Dép , Ville_Arr )

* Suppression de tous les vols ( tous les tuples de la table VOL )

DELETE FROM VOL ;
* Suppression de tous les vols qui datent d’avant le 28 Février 2000
DELETE FROM VOL WHERE Date_Vol < ‘ 28/02/01’ ;
* Suppression de tous les vols assurés par le pilote ‘ DRISSI ’
DELETE FROM VOL WHERE N_Pilote IN
( SELECT N_Pilote FROM PILOTE WHERE Nom = ‘ DRISSI’ )
* Suppression de tous les pilotes qui n’ont effectué aucun vol
DELETE FROM PILOTE P WHERE NOT EXISTS
( SELECT * FROM VOL WHERE N_Pilote = P.N_Pilote )
17

Exercices de compréhension
EXO
Soit la base de données définie par le schéma relationnel suivant:
Programmes (CodProg, Nom ,chaine, type)
Artistes (CProgramme, NomArt)
Sondages (CodProg, Auditel)
Les attributs Cprogrammae et CodProg sont des clés étrangères des programmes.
Exprimez les questions suivantes:
a. (*) Renvoie le code Auditel du programme avec le code P17

b. (**) Retourne r le code Auditel des programmes de Adam

c. (***) Retourner les noms de programme avec au moins deux artistes

d. (***) Renvoie les noms de programme avec un artiste.

18

9
 Exercices de compréhension
Soit le schéma de base de données relationnel le suivant:
Véhicule (plaque, marque, déplacement, puissance, CodP*, CodAss *)
PROPRIÉTAIRES (CodF, Nom, Résidence)
ASSURANCE (CodAss, Nom, Lieu)
SINISTRE (CodS, Lieu, Date)
AUTO-IMPLIQUÉ (CodS *, plaque *, AmountDelay)
Écrivez les requêtes SQL qui renvoient les informations suivantes:
1- Nombre et marque de voitures d'une capacité supérieure à 2000 cm3 ou d'une puissance supérieure
à 120 CV
2- Nom du propriétaire et de la plaque d'immatriculation d'une capacité supérieure à 2 000 cm3 ou
d'une puissance supérieure à 120 CV
3- Plaque et nom du propriétaire des voitures d'une capacité supérieure à 2000 cm3 ou d'une
puissance supérieure à 120 CV, assuré auprès du « OVIA"
4- Plaque et nom du propriétaire des voitures assurées avec « OVIA" et impliquées dans des accidents
le 20/01/2019.
5- Pour chaque assurance, le nom, l'emplacement et le nombre de voitures assurées.
6- Pour chaque voiture « BMW », la plaque d'immatriculation et le nombre de sinistres dans lesquels
elle était impliquée.
7- Pour chaque voiture impliquée dans plus d'un accident, le numéro d'immatriculation, le nom de
l'assurance et le montant total des dommages déclarés.
8- les CodP et nom de ceux qui possèdent plus d'une voiture.
9- La plaque d'immatriculation des voitures n'ayant pas été impliquées dans des accidents après le
20/01 / 2019
10- Le code des sinistres dans lequel les voitures d'une cylindrée inférieure à 2 000 cm 3 n'étaient pas
impliquées. 19

LE LANGAGE DE DEFINITION DE DONNEES ( LDD )

Les ordres LDD sont des instructions SQL créées à partir des commandes suivantes :

Commande Rôle

CREATE Création de la structure d’une table , d’un index ou d’une vue de

données en spécifiant des contraintes structurelles ou d’intégrité
référentielle à respecter sur les données

DROP Supprimer une table , un index ou une vue ( structure et données )

ALTER Modifier la structure d’une table ( ajout , suppression ou mise à jour

d’un ou plusieurs attributs )

Chaque commande peut utiliser une ou plusieurs clauses obligatoires et des clauses optionnelles

20

10
COMMANDE CREATE TABLE : Forme générale

CREATE TABLE < Nom de Table >

[ < Nom attr. > < Type > ] , …
ou [ < Nom attr. > < Type > < Contrainte d’attr. > ] , …
ou [ < Nom attr. > < Type > CONSTRAINT <Nom contrainte> <Contrainte d’attr.> ] ,
ou [ CONSTRAINT < Nom contrainte > < Contrainte de Table > ] , etc ...

Les contraintes constituent une méthode normalisée par l’ANSI pour assurer l’intégrité des
données .
Chaque type d’intégrité ( de domaine , d’entité ou référentielle ) est mis en œuvre à l’aide de types
de contraintes spécifiques ( voir tableau ).

Les contraintes garantissent la validité des valeurs saisies dans les colonnes et le maintien des
relations entre les tables .

Les principales contraintes sont DEFAULT , CHECK , PRIMARY KEY , UNIQUE

et FOREIGN KEY .

L’écriture d’un ordre CREATE TABLE utilisant ces contraintes peut différer légèrement suivant
le SGBD utilisé ( ACCESS , SQL Server , ORACLE , SYBASE , INFORMIX , etc… )
21

COMMANDE CREATE TABLE : Les Contraintes d’intégrité

Type d’intégrité Type de Contrainte Description

-----------------------------------------------------------------------------------------------------------------------
Domaine DEFAULT Spécifie la valeur qui sera placée dans la colonne si aucune
valeur n’est spécifiée explicitement dans l’instruction
INSERT au moment de la saisie
CHECK Spécifie une règle de validité s’appliquant aux valeurs
d’une colonne .
FOREIGN KEY Les valeurs de colonne de clé étrangère doivent
correspondre aux valeurs des colonnes de clé primaire de la
table référencée .
Entité PRIMARY KEY Identifie chaque ligne de façon unique , interdit l’entrée de
valeurs en double et garantit la création d’un index pour
améliorer les performances . Les valeurs NULL ne sont pas
acceptées .
UNIQUE Empêche la création des doublons dans les colonnes qui ne
constituent pas une clé primaire et garantit la création d ’un
index pour améliorer les performances . Les valeurs NULL
sont acceptées .
Référentielle FOREIGN KEY Définit une colonne ou un ensemble de colonnes dont les
valeurs sont égales à la clé primaire de leur table ou d ’une
autre table .
Définie par CHECK Spécifie une règle de validité s’appliquant aux valeurs de
l’utilisateur colonnes .
-----------------------------------------------------------------------------------------------------------------------
22

11
 COMMANDE CREATE TABLE : Exemples
* Spécification de contraintes d’attributs
CREATE TABLE Etudiant
( Matricule INT NOT NULL CONSTRAINT Clé_Primaire PRIMARY KEY ,
Nom CHAR(25) NOT NULL ,
Prénom CHAR(25) NOT NULL ,
Sexe CHAR(1) NOT NULL CHECK ( Sexe IN ( ’M', ’F ’ ) ) ) ;

CREATE TABLE Employé

( Code CHAR(9) NOT NULL CONSTRAINT Clé_Primaire PRIMARY KEY NONCLUSTERED
CHECK ( Code LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][MF]' OR
Code LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9] [0-9][MF]’ ) ,
Nom VARCHAR(30) NOT NULL,
Prénom VARCHAR(30) NOT NULL,
N_Fonction SMALLINT NOT NULL DEFAULT 1 REFERENCES Fonction ( N_Fonction ) ,
Date_Embauche DATETIME NOT NULL DEFAULT ( GETDATE() ) ) ;

CREATE TABLE Fonction

( N_Fonction SMALLINT IDENTITY( 1 , 1) PRIMARY KEY CLUSTERED ,
Libellé_Fonction VARCHAR(50) NOT NULL DEFAULT ’ Fonction ? ',
Echelle TINYINT NOT NULL CHECK ( Echelle <= 11 ) ) ;

Remarque : L’utilisation du qualificatif IDENTITY(Valeur_initiale , Incrément ) permet de spécifier

un attribut qui doit faire l’objet d’une incrémentation automatique par le système à chaque ajout de tuple
23

COMMANDE CREATE TABLE : Exemples ( Suite )

* Spécification de contraintes de table
- Contrainte d’unicité : Il ne peut y avoir 2 dépôts de même nom dans la même ville :
CREATE TABLE DEPOT
( N_Dépôt INT NOT NULL CONSTRAINT Clé_Primaire PRIMARY KEY ,
Nom_Dépôt CHAR(25) NOT NULL ,
Ville VARCHAR(25) NOT NULL ,
CONSTRAINT C_Dépôt UNIQUE ( Nom_Dépôt , Ville ) ) ;

- Contrainte sur les domaines de valeurs de plusieurs attributs

CREATE TABLE Produit
( N_Produit INT NOT NULL CONSTRAINT Clé_Primaire PRIMARY KEY ,
Désignation CHAR(25) NOT NULL ,
Poids DECIMAL(8,2) NULL ,
Prix_U DECIMAL(10,2) NULL ,
CONSTRAINT C_Produit CHECK ( Poids > 0 AND Prix_U > 0 AND Prix_U <= 10000 ) ) ;

- Contrainte de type Clé primaire composée

CREATE TABLE STOCKER
( N_Produit INT NOT NULL REFERENCES Produit ( N_Produit ) ,
N_Dépôt INT NOT NULL REFERENCES Dépôt ( N_Dépôt ) ,
CONSTRAINT C_Stocker PRIMARY KEY (N_Produit , N_Dépôt ) ) ;
24

12
 COMMANDE ALTER TABLE : Forme générale
But : Modifier la définition d'une table en changeant, en ajoutant ou en supprimant des colonnes ou des contraintes

1ère Forme :
ALTER TABLE < Nom de Table >
[ ADD < Nom attr. > < Type > [ < Contrainte d’attr. > ] ] , …
ou [ ADD CONSTRAINT < Nom contrainte > < Contrainte > ] , …
2ème Forme :
ALTER TABLE < Nom de Table >
[ DROP COLUMN < Nom attr. > ] , ...
ou [ DROP CONSTRAINT < Nom contrainte > ] , …
3ème Forme :
ALTER TABLE < Nom de Table >
[ ALTER COLUMN < Nom attr. > < Nouveau Type > [ < Contrainte d’attr. > ] ] , .

25

COMMANDE ALTER TABLE : Exemples ( Suite )

- Ajout d’une colonne simple à la structure d’une table
ALTER TABLE Vente ADD Commission MONEY NULL ;

- Ajout d’une colonne ou de plusieurs colonnes avec contraintes

ALTER TABLE Client ADD Code_Client INT IDENTITY ( 1,1 )
CONSTRAINT PK_Client PRIMARY KEY ;

ALTER TABLE Employé ADD

N_Dép INT NOT NULL CONSTRAINT C_Dép REFERENCES Département ( N_Dép ) ,
Tél VARCHAR ( 10 ) NULL CONSTRAINT C_Tél CHECK ( Tél IS NULL OR
Tél LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
Tél LIKE " [0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9] " ) ,
Salaire DECIMAL ( 8, 2 ) CONSTRAINT C_Salaire DEFAULT 5000 ;

- Ajout d’une contrainte

ALTER TABLE Employé ADD CONSTRAINT C_Employé CHECK ( Date_Embauche > ‘ 01/01/98 ’ ) ;

- Suppression d’une colonne ou d’une contrainte

ALTER TABLE Personne DROP Age ;

ALTER TABLE Employé DROP CONSTRAINT C_Employé ;

26

13
 COMMANDE CREATE INDEX : Forme générale et Exemples

CREATE [UNIQUE ] INDEX < Nom de l’indexe >

ON < Nom de Table >
( < Nom Attr. > , < Nom attr. > , ….. )

On utilise un indexe sur un attribut ou un groupe d’attributs de table dans les situations suivantes :
* Pour implémenter l’intégrité de relation ( de table ) permettant de garantir l’unicité des valeurs
de la clé primaire ( Commande CREATE INDEX avec le qualificatif UNIQUE )
* Pour définir une ou plusieurs clés candidates ( attributs à valeurs distinctes ) dans une table
* Pour accélérer le temps de réponse de certaines opérations de traitement sur la base de données
lorsque le nombre d’enregistrements des tables est très important :
- Recherche ou Tri croissant / décroissant sur une ou plusieurs colonnes
- Requête de sélection utilisant un filtre sur une ou plusieurs colonnes avec une clause WHERE
- Requête utilisant des jointures sur certains attributs communs à 2 ou plusieurs tables
On définit alors un indexe sur la ou les colonnes en question ( colonnes de recherche , de tri ,
de sélection , de jointure , etc… )
Exemples :
CREATE UNIQUE INDEX PK_Pilote ON Pilote ( N_Pilote ) ; // Clé primaire simple
CREATE UNIQUE INDEX PK_Stocker ON Stocker ( N_Produit , N_Dépôt ) ; // Clé primaire composée
CREATE INDEX IX_Vente ON Vente ( Date_V DESC , Réf_Produit ) ; // Indexe sur un couple
d’attributs de tri
27

COMMANDE CREATE VIEW : Forme générale et Exemples

CREATE VIEW < Nom de Vue > ( < Nom Attr. > , < Nom attr. > , … )
AS < Instruction SELECT > ;

1 - Notion de Vue : Une vue est une table virtuelle déterminée à partir d’autres tables à l’aide d’une
requête de sélection . Les données affichées par une vue ne sont pas physiquement stockées dans la base de données
mais correspondent au résultat de l’exécution d’une requête d’interrogation sur les données stockées dans d’autres
tables .

2 - Avantages de l’utilisation des vues :

* Sauvegarde de requêtes complexes et simplification de l’accès aux données en masquant les opérations de
jointures et d’agrégation :
Création d’une vue : CREATE VIEW Commande_Produit AS
SELECT P.N_Produit , Désignation , Prix_U , Date_Commande , Qté_Stock
FROM Produit P , Commande C
WHERE P.N_Produit = C.N_Produit ;
Utilisation de la Vue : SELECT N_Produit , Désignation FROM Commande_Produit
WHERE Qté_Stock > 10 ;
* Support de l’indépendance logique :
Si la structure de la table Produit est mise à jour , la vue Commande_Produit doit être reconstituée mais les
requêtes qui utilisent cette vue n’ont pas à être remaniées .

28

14
 COMMANDE CREATE VIEW : Exemples ( Suite )
* Renforcement de la sécurité des données par masquage des attributs et des tuples dans les tables à certains
utilisateurs :
L’exemple suivant montre 2 vues définies pour 2 catégories de représentants commerciaux dans une entreprise :
ceux affectés à la région d’Agadir et ceux affectés à celle de Casa .
Chacune de ces vues permet d’obtenir les informations sur les clients d’une région mais ne donnent pas accès
aux données des autres régions ni à celles des autres attributs des tables Client et Commande .
CREATE VIEW Commande_Client_Agadir AS
SELECT A.N_Client , A.Nom_Client , B.N_Produit , B.Désignation , [Link]é_Commandée
FROM Client A , Commande B
WHERE A.N_Client = B.N_Client
AND [Link] = ‘’Agadir ‘’ ORDER BY 2 , 3 ;

CREATE VIEW Commande_Client_Casa AS

SELECT A.N_Client , A.Nom_Client , B.N_Produit , B.Désignation , [Link]é_Commandée
FROM Client A , Commande B
WHERE A.N_Client = B.N_Client
AND [Link] = ‘’Casa ‘’ ORDER BY 2 , 3 ;

3 - Problème de mise à jour des données à l’aide d’une vue :

La mise à jour des données via une vue pose des problèmes pour la plupart des SGBD relationnels .
Les conditions nécessaires pour que les données d’une vue soient accessibles en mise à jour et que cette mise à jour
se répercute sur les tables d’origine sont :
> Le mot clé DISTINCT doit être absent dans l’ordre SELECT et la clause FROM doit faire référence à une seule table
> Les attributs de projection de l’ordre SELECT doivent être de type simple ( pas d’agrégat ou d’attribut calculé )
> Les clauses de regroupement GROUP BY et de sélection de groupe HAVING doivent être absentes .
29

COMMANDE DROP : Forme générale et Exemples

DROP TABLE < Nom de table >

ou DROP INDEX < Nom d’indexe >
ou DROP VIEW < Nom de Vue >

But : Supprimer un objet table , index ou vue de la base de données en éliminant les
informations de structure liées à cet objet et les données qui lui sont attachées .

Exemples :

DROP TABLE Client ;

DROP INDEX Clé_Primaire_Client ;

DROP VIEW Commande_Client ;

30

15
 Exercices de compréhension
EXO
Donner les définitions SQL des tables
AUTEUR (Nom, Prénom, Date de naissance, Nationalité)
LIVRE (Titre, Nom de l'auteur, Prénom de l'auteur, Langue)
Pour la contrainte de clé étrangère, spécifiez une stratégie en cascade lors de la suppression et des
ensembles nuls lors des modifications.

31

16