Introduction au langage SQL

KINDO A AZIZE / UNIVERSITE NAZI BONI

1
Objectifs du cours
• Extraire des données de lignes et de colonnes à l’aide de l’instruction
SELECT
• Créer des états de données triées et restreintes
• Utiliser des fonctions SQL pour générer et extraire des données
personnalisées
• Exécuter des instruction LMD

KINDO A AZIZE / UNIVERSITE NAZI BONI 2

Présentation de SQL
• Origine
• langage SEQUEL du prototype de SGBD relationnel SYSTEM/R (74-76)
• laboratoire de recherche IBM à San José
• Normalisation ISO
• norme SQL1 (1986, 1989)
• norme SQL2 (1992)
• norme SQL3 évolutions objets (1998)
• Langage de requêtes des SGBD relationnels
• ORACLE (Oracle Corporation - 1977)
• INGRES (Ingres Technology - 1980)
• DB2 (IBM - 1984)
• INFORMIX (Informix Inc - 1981)
• SYBASE (Sybase Inc - 1984)
• MySQL (1995)
• Postgre SQl

KINDO A AZIZE / UNIVERSITE NAZI BONI 3

Caractéristiques
• SQL assure l'indépendance des données
• SQL est un langage déclaratif (par opposition à procédural)
• Mode interactif ou programmé
• interactif : instruction SQL tapée directement, instantanément traitée
• à l'intérieur d'un programme : appel de SQL dans un programme en
Pascal, PHP, C, ...
• instruction SQL = requête

KINDO A AZIZE / UNIVERSITE NAZI BONI 4

 Caractéristiques
• SQL est un langage à quatre dimension :
• le Langage de Definition de Données (LDD ) : commandes de SQL permettant
de créer, modifier ou effacer la définition (schéma) d'une BD ou d'une table.

• le Langage de Manipulation de Données (LMD ) : interrogation et modification

de l'information contenue dans les tables

• Le Langage de Controle de Données (LCD) : sécurité et confidentialité de la BD.

• Le Langage de contrôle des transactions(LCT ):pour la gestion des transactions

(validation ou annulation de modifications de données dans la BD)

KINDO A AZIZE / UNIVERSITE NAZI BONI 5

SQL1 - 86
• LANGAGE DE DEFINITIONS DE DONNEES
Exemple de commandes : CREATE, DROP, ALTER

• LANGAGE DE MANIPULATION DE DONNEES

Exemple de commandes : SELECT, INSERT, UPDATE, DELETE

• LANGAGE DE CONTROLE DE DONNEES

Exemple de commandes : GRANT et REVOKE

• LANGAGE DE CONTROLE DE TRANSACTION

Exemple de commandes : COMMIT, ROLLBACK
KINDO A AZIZE / UNIVERSITE NAZI BONI 6
Le Language de Manipulation des
Données : la consultation de données
• Instruction SELECT : 3 parties principales :
• clause (obligatoire) Select : précise les valeurs qui constituent chaque ligne du résultat
• clause (obligatoire) from : indique les tables desquelles le résultat tire ses valeurs
• clause where : donne la condition de sélection que doivent satisfaire les lignes qui
fournissent le résultat
SELECT schéma
FROM table
WHERE condition ;

KINDO A AZIZE / UNIVERSITE NAZI BONI 7

Interrogation en SQL
SELECT <liste de colonnes>
FROM <liste de tables>
[WHERE <critère de jointure> AND <critère de sélection>]
[GROUP BY <attributs de partitionnement>]
[HAVING <citère de restriction>]
• Exemple :
SELECT Nom, Prenom
FROM EMPLOYE
WHERE Matricule= 70458;

KINDO A AZIZE / UNIVERSITE NAZI BONI 8

Ordre SELECT Élémentaire
• SELECT indique quelles colonnes rapporter
• FROM indique dans quelle table rechercher

SELECT [DISTINCT] {*, column [alias],...}

FROM table;

KINDO A AZIZE / UNIVERSITE NAZI BONI 9

Écriture des Ordres SQL
• Les ordres SQL peuvent être écrits indifféremment en majuscules et/ou
minuscules.
• Les ordres SQL peuvent être écrits sur plusieurs lignes.
• Les mots-clés ne doivent pas être abrégés ni scindés sur deux lignes
différentes.
• Les clauses sont généralement placées sur des lignes distinctes.
• Les tabulations et indentations permettent une meilleure lisibilité.

KINDO A AZIZE / UNIVERSITE NAZI BONI 10

Sélection de Toutes
les Colonnes
•
SQL> SELECT * FROM dept;

KINDO A AZIZE / UNIVERSITE NAZI BONI 11

• SQL> SELECT DEPT, NOMD FROM dept;

DEPT NOMD
--------- -------------
10 FINANCES
20 RECHERCHES
30 VENTES
40 FABRICATION

KINDO A AZIZE / UNIVERSITE NAZI BONI 12

Expressions Arithmétiques

Possibilité de créer des expressions avec des

données de type NUMBER et DATE au moyen
d’opérateurs arithmétiques: addition
soustraction multiplication division + - * /

KINDO A AZIZE / UNIVERSITE NAZI BONI 13

 Exemple
SQL> SELECT NOME, SAL, SAL+300
FROM EMP;

KINDO A AZIZE / UNIVERSITE NAZI BONI 14

Utilisation des Parenthèses
• SELECT nome, sal, 12*(sal+300) FROM emp;

KINDO A AZIZE / UNIVERSITE NAZI BONI 15

La Valeur NULL

• NULL représente une valeur non disponible, non affectée,

inconnue ou inapplicable.
• La valeur NULL est différente du zéro ou de l’espace.
• Les expressions arithmétiques comportant une valeur NULL
sont évaluées à NULL
select Nome , 12*sal+comm
from emp
WHERE nome='SALIMATA';

KINDO A AZIZE / UNIVERSITE NAZI BONI 16

L’Alias de Colonne
• Renomme un en-tête de colonne
• Est utile dans les calculs
• Suit immédiatement le nom de la colonne ; le mot-clé AS placé entre le nom et l’alias est
optionnel
• Doit obligatoirement être inclus entre guillemets s’il contient des espaces, des caractères
spéciaux ou si les majuscules/minuscules doivent être différenciées
• SQL> SELECT nome AS name, sal salary FROM emp;
NAME SALARY
------------- ---------
• SQL> SELECT NOME "Name", sal*12 "Annual Salary" FROM
emp;
Name Annual Salary
------------- -------------

KINDO A AZIZE / UNIVERSITE NAZI BONI 17

L’Opérateur de Concaténation
• Concatène des colonnes ou chaînes de caractères avec d’autres colonnes
• Est représenté par deux barres verticales (||)
• La colonne résultante est une expression caractère
• SELECT Nome||PROF as employé FROM emp;

KINDO A AZIZE / UNIVERSITE NAZI BONI 18

 Littéral
• Un littéral est un caractère, une expression, ou un nombre inclus dans la liste
SELECT.
• Les valeurs littérales de type date et caractère doivent être placées entre
simples quotes.
• Chaque littéral apparaît sur chaque ligne ramenée.
• SELECT nome ||' '||'est un'||' '||PROF AS
"Employee Details" FROM emp;

KINDO A AZIZE / UNIVERSITE NAZI BONI 19

Doublons
• Par défaut, le résultat d’une requête affiche toutes les lignes, y
compris les doublons.
• SQL> SELECT DEPT
2 FROM emp;
Pour éliminer les doublons il faut ajouter le mot-clé DISTINCT à
la clause SELECT.
• SQL> SELECT DISTINCT dept
• 2 FROM emp;

KINDO A AZIZE / UNIVERSITE NAZI BONI 20

Sélection et Tri des Lignes
Retournées par un SELECT

KINDO A AZIZE / UNIVERSITE NAZI BONI 21

 Sélectionner les Lignes
• Restreindre la sélection au moyen de la clause WHERE.
• La clause WHERE se place après la clause FROM.
SELECT [DISTINCT] {*, column
[alias], ...}
FROM table
[WHERE condition(s)];
select Nome , PROF from emp WHERE nome='SALIMATA';
• Les constantes chaînes de caractères et dates doivent être placées entre
simples quotes.
• La recherche tient compte des majuscules et minuscules (pour les chaînes
de caractère) et du format (pour les dates.)
• Le format de date par défaut est
'DD-MON-YY'.

KINDO A AZIZE / UNIVERSITE NAZI BONI 22

Opérateurs de comparaisons
Opérateur Description
= Égale
<> Pas égale
!= Pas égale
> Supérieur à
< Inférieur à
>= Supérieur ou égale à
<= Inférieur ou égale à
IN Liste de plusieurs valeurs possibles
BETWEEN Valeur comprise dans un intervalle donnée (utile pour les nombres ou dates)
LIKE Recherche en spécifiant le début, milieu ou fin d'un mot.
IS NULL Valeur est nulle
IS NOT NULL Valeur n'est pas nulle

KINDO A AZIZE / UNIVERSITE NAZI BONI 23

Utilisation des Opérateurs de Comparaison
• SQL> SELECT Nome, sal, comm
2 FROM emp
3 WHERE sal<=comm;

• SQL> SELECT Nome, sal

2 FROM emp
3 WHERE sal BETWEEN 1000000 AND 1500000;

• SQL> SELECT matr, Nome, sal, sup

2 FROM emp
3 WHERE sup IN (7902, 7566, 7788);

KINDO A AZIZE / UNIVERSITE NAZI BONI 24

Utilisation de l’Opérateur LIKE
• LIKE permet de rechercher des chaînes de caractères à l'aide de
caractères génériques
• Les conditions de recherche peuvent contenir des caractères ou des
nombres littéraux.
• (%) représente zéro ou plusieurs caractères
• ( _ ) représente un caractère
• SQL> SELECT nome
2 FROM emp
3 WHERE nome LIKE 'S%';
Vous pouvez combiner plusieurs caractères génériques de recherche.
• SQL> SELECT nome Utilisation de l’Opérateur IS NULL
SQL> SELECT nome, sup
• 2 FROM emp 2 FROM emp
3 WHERE sup IS NULL;
• 3 WHERE nome LIKE '_A%';

KINDO A AZIZE / UNIVERSITE NAZI BONI 25

 Opérateurs Logiques
• OR, AND et NOT

SQL> SELECT Matr, NOME, PROF, sal SQL> SELECT matr, nome, PROF, sal
2 FROM emp 2 FROM emp
3 WHERE sal>=510000 3 WHERE sal>=510000
4 AND PROF='SECRETARIAT'; 4 OR PROF=‘SECRETARIAT';

SQL> SELECT nome, PROF

2 FROM emp
3 WHERE PROF NOT IN ('SECRETARIAT','MANAGER','ANALYSTE');

KINDO A AZIZE / UNIVERSITE NAZI BONI 26

Règles de Priorité

SQL> SELECT NOME, PROF, sal

2 FROM emp
3 WHERE PROF=‘ANALYSTE'
4 OR PROF='PRESIDENT'
5 AND sal>1500000;
Utilisation de parenthèses pour forcer la priorité.
SQL> SELECT NOME, PROF, sal
2 FROM emp
3 WHERE (PROF='SALESMAN'
4 OR PROF='PRESIDENT')
5 AND sal>1500000;

KINDO A AZIZE / UNIVERSITE NAZI BONI 27

Clause ORDER BY
• Tri des lignes avec la clause ORDER BY
• ASC : ordre croissant (par défaut)
• DESC : ordre décroissant
• La clause ORDER BY se place à la fin de l’ordre SELECT
• SQL> SELECT Nome, PROF, DEPT, DATEEMB
2 FROM emp
3 ORDER BY DATEEMB;
Tri par Ordre Décroissant
• SQL> SELECT Nome, PROF, DEPT, DATEEMB
2 FROM emp
3 ORDER BY DATEEMB desc;

KINDO A AZIZE / UNIVERSITE NAZI BONI 28

Tri sur l’Alias de Colonne
• SQL> SELECT matr, nome, sal*12 annsal
2 FROM emp
3 ORDER BY annsal;
• RESUME
SELECT [DISTINCT] {*, column [alias], ...}
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];

KINDO A AZIZE / UNIVERSITE NAZI BONI 29

Fonctions Mono-Ligne
• A la fin de cette partie, vous saurez :
• Décrire différents types de fonctions SQL
• Utiliser les fonctions caractère, numériques et date dans les ordres SELECT
• Expliquer les fonctions de conversion

KINDO A AZIZE / UNIVERSITE NAZI BONI 30

 Fonctions SQL
• Une fonction est une expression d’un type de données spécifique qui
fait partie d’une instruction utilisée pour calculer une valeur .

Entrée Sortie
Fonction

arg 1

arg 2
Valeur
résultante

arg n
KINDO A AZIZE / UNIVERSITE NAZI BONI 31
Deux Types de Fonctions SQL
•
Fonctions

Fonctions Fonctions
mono-ligne multi-ligne

KINDO A AZIZE / UNIVERSITE NAZI BONI 32

 Fonctions Mono-Ligne
• Manipulent des éléments de données Caractère
• Acceptent des arguments et ramènent une valeur
• Agissent sur chacune des lignes rapportées
• Ramènent un seul résultat par ligne
Générale Numérique
• Peuvent modifier les types de données Fonctions
• Peuvent être imbriquées mono-ligne

Conversion Date

KINDO A AZIZE / UNIVERSITE NAZI BONI

33
 Fonctions Caractère
• Fonction
caractère

Fonctions de conversion Fonctions de manipulation

majuscules/minuscules des caractères
LOWER CONCAT
UPPER SUBSTR
INITCAP LENGTH
INSTR
LPAD ...
KINDO A AZIZE / UNIVERSITE NAZI BONI 34
Fonctions de Conversion
Majuscules/Minuscules

Fonction Résultat
LOWER('Cours SQL') cours sql
UPPER('Cours SQL') COURS SQL
INITCAP('Cours SQL') Cours Sql

KINDO A AZIZE / UNIVERSITE NAZI BONI 35

 Fonctions de
Manipulation des Caractères
• Manipulation de chaînes de caractères

Fonction Résultat
CONCAT('Une', 'Chaîne') UneChaîne
SUBSTR('Chaîne',1,3) Cha
LENGTH('Chaîne') 6
INSTR('Chaîne', 'a') 3
LPAD(sal,10,'*') ******5000

KINDO A AZIZE / UNIVERSITE NAZI BONI 36

• SQL> SELECT matr, nome, dept
2 FROM emp
3 WHERE LOWER(nome) = ‘somé';

KINDO A AZIZE / UNIVERSITE NAZI BONI 37

Fonctions Numériques
• ROUND : Arrondit la valeur à la précision spécifiée
• TRUNC : Tronque la valeur à la précision spécifiée
• MOD : Ramène le reste d’une division
• ROUND(45.926, 2) → 45.93
• TRUNC(45.926, 2) → 45.92
• MOD(1600,300) → 100

KINDO A AZIZE / UNIVERSITE NAZI BONI 38

 EXEMPLE DE TABLE

EMP(MATR: entier, NOME: Chaine de caractère, PROF: Chaine de caractère, SUP: entier, DATEEMB: date, SAL:
décimal, COMM: décimal, DEPT: entier)
DEPT(DEPT: entier, NOMD: Chaine de caractère, LIEU: Chaine de caractère)

KINDO A AZIZE / UNIVERSITE NAZI BONI 39

Domaine de base
• Exercice 1: Proposer les types associés aux attributs de la table EMP.
Quel serait alors la taille d’un enregistrement?

KINDO A AZIZE / UNIVERSITE NAZI BONI 40

Création et Gestion
de Tables

KINDO A AZIZE / UNIVERSITE NAZI BONI 41

Objectifs
• A la fin de cette partie, vous saurez :
• Décrire les principaux objets d'une base de données
• Créer des tables
• Décrire les différents types de données utilisables
pour les définitions de colonne
• Modifier la définition des tables
• Supprimer, renommer et tronquer une table

KINDO A AZIZE / UNIVERSITE NAZI BONI 42

Schéma d’une relation
• Création
CREATE TABLE DEPT(
DEPT Integer,
NOMD Char(20)
)

• Mise à jour
• Ajout d'un attribut (norme SQL2 !)
ALTER TABLE DEPT ADD COLUMN Lieu Char(30)
• Suppression (norme SQL2 !)
DROP TABLE DEPT

KINDO A AZIZE / UNIVERSITE NAZI BONI 43

Objets d'une Base de Données

Objet Description
Table Unité de stockage élémentaire, composée de lignes
et de colonnes
Vue Représente de manière logique des sous-groupes
de données issues d'une ou plusieurs tables
Séquence Génère des valeurs de clés primaires
Index Améliore les performances de certaines requêtes
Synonyme Permet de donner un autre nom à un objet

KINDO A AZIZE / UNIVERSITE NAZI BONI 44

Conventions de Dénomination
Un nom :
• Doit commencer par une lettre
• Peut comporter de 1 à 30 caractères
• Ne peut contenir que les caractères A à Z, a à z, 0 à
9, _, $, et #
• Ne doit pas porter le nom d’un autre objet
appartenant au même utilisateur
• Ne doit pas être un mot réservé

KINDO A AZIZE / UNIVERSITE NAZI BONI 45

L'Ordre CREATE TABLE
• Vous devez posséder :
• Un privilège CREATE TABLE
• Un espace de stockage
• Spécifiez :
• Un nom de table
• Le nom, le type de données et la taille des colonnes.

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr],...

KINDO A AZIZE / UNIVERSITE NAZI BONI 46

Références aux Tables d'un Autre Utilisateur

• Les tables appartenant à d'autres utilisateurs ne

sont pas dans le schéma utilisateur.
• Le nom du propriétaire doit précéder le nom de la
table.

KINDO A AZIZE / UNIVERSITE NAZI BONI 47

 L'Option DEFAULT
• Spécifie la valeur par défaut d'une colonne.

… DATEEMB DATE DEFAULT SYSDATE, …

• Valeurs autorisées : littéraux, expressions et fonctions SQL

• Valeurs non autorisées: noms d’autres colonnes ou pseudo-colonnes
• Le type de données par défaut doit correspondre à celui de la colonne.

KINDO A AZIZE / UNIVERSITE NAZI BONI 48

Création de Tables
• Créer la table.
SQL> CREATE TABLE DEPT
2 (DEPT NUMBER(2),
3 dname VARCHAR2(14),
4 LIEU VARCHAR2(13));
Table created.

• Vérifier la création de la table.

SQL> DESCRIBE dept

Name NULL? Type

--------------------------- -------- ---------
DEPT NUMBER(2)
DNAME VARCHAR2(14)
LIEU VARCHAR2(13)

KINDO A AZIZE / UNIVERSITE NAZI BONI 49

 Types de Données

Types de données Description

VARCHAR2(size) Données caractères de longueur variable
CHAR(size) Données caractères de longueur fixe
NUMBER(p,s) Numérique de longueur variable
DATE Valeurs de date et d'heure
LONG Données caractères de longueur variable,
jusqu'à 2 giga-octets
CLOB Données caractères mono-octet,
jusqu'à 4 giga-octets
RAW et LONG RAW Binaire
BLOB Binaire, jusqu'à 4 giga-octets
BFILE Binaire, stocké dans un fichier
externe, jusqu'à 4 giga-octets

KINDO A AZIZE / UNIVERSITE NAZI BONI 50

Création d'une Table au Moyen d'une Sous-
Interrogation
SQL> CREATE TABLE dept30
2 AS
3 SELECT Matr, NOME, sal*12 ANNSAL, DATEEMB
4 FROM emp
5 WHERE DEPT = 30;
Table created.

SQL> DESCRIBE dept30

Name NULL? Type

---------------------------- -------- -----
Matr NUMBER(4)
NOME VARCHAR2(10)
ANNSAL NUMBER
DATEEMB DATE

KINDO A AZIZE / UNIVERSITE NAZI BONI 51

L'ordre ALTER TABLE
• Utilisez l'ordre ALTER TABLE pour :
• Ajouter une colonne
• Modifier une colonne existante
• Définir une valeur par défaut pour une nouvelle
colonne

ALTER TABLE table

ADD (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);

KINDO A AZIZE / UNIVERSITE NAZI BONI 52

 Ajout de Colonnes
Nouvelle
DEPT30 colonne "…ajouter
Matr NOME ANNSAL DATEEMB PRO une nouvelle
------ ---------- -------- F colonne à
7698 BLAKE 34200 01-MAY-81 la table
7654 MARTIN 15000 28-SEP-81
7499 ALLEN 19200 20-FEB-81 DEPT30…"
7844 TURNER 18000 08-SEP-81
...

DEPT30
Matr NOME ANNSAL DATEEMB PRO
------ ---------- -------- F
7698 BLAKE 34200 01-MAY-81
7654 MARTIN 15000 28-SEP-81
7499 ALLEN 19200 20-FEB-81
7844 TURNER 18000 08-SEP-81
...

KINDO A AZIZE / UNIVERSITE NAZI BONI 53

Ajout de Colonnes
• Utilisez la clause ADD pour ajouter des colonnes.

SQL> ALTER TABLE dept30

2 ADD (PROF VARCHAR2(9));
Table altered.

• La nouvelle colonne est placée à la fin.

Matr NOME ANNSAL DATEEMB PROF
--------- ---------- --------- --------- ----
7698 BLAKE 34200 01-MAY-81
7654 MARTIN 15000 28-SEP-81
7499 ALLEN 19200 20-FEB-81
7844 TURNER 18000 08-SEP-81
...
6 rows selected.

KINDO A AZIZE / UNIVERSITE NAZI BONI 54

Modification de Colonnes
• Vous pouvez modifier le type de données, la taille et
la valeur par défaut d'une colonne.

ALTER TABLE dept30

MODIFY (NOME VARCHAR2(15));
Table altered.

• la modification d’une valeur par défaut ne s’applique qu’aux

insertions ultérieures dans la table.

KINDO A AZIZE / UNIVERSITE NAZI BONI 55

Modification de colonnes
Vous pouvez :
• Augmenter la largeur ou la précision d’une colonne
numérique.
• Réduire la largeur d’une colonne si elle ne contient
pas de valeurs NULL ou si la table est vide.
• Modifier le type de données d’une colonne si elle
contient des valeurs NULL ou si la table est vide.
• Convertir de CHAR à VARCHAR ou inversement si la
colonne contient des valeurs NULL ou si la table est
vide.

KINDO A AZIZE / UNIVERSITE NAZI BONI 56

Suppression de Tables
• La structure et toutes les données de la table sont
supprimées.
• Tous les index sont supprimés.
• La transaction en instance est validée.
• Une suppression de table ne peut être annulée.

SQL> DROP TABLE dept30;

Table dropped.

KINDO A AZIZE / UNIVERSITE NAZI BONI 57

Modification du Nom d'un Objet
• Pour modifier le nom d'une table, d'une vue, d'une
séquence ou d'un synonyme, utilisez l'ordre
RENAME.

SQL> RENAME dept TO department;

Table RENAMED.
• Vous devez être propriétaire de l'objet.

KINDO A AZIZE / UNIVERSITE NAZI BONI 58

Vider une Table
• L'ordre TRUNCATE TABLE :
• Supprime toutes les lignes d'une table
• Libère l'espace de stockage utilisé par la table
SQL> TRUNCATE TABLE department;
Table truncated.

• Vous ne pouvez pas annuler un ordre TRUNCATE

• Vous pouvez aussi utiliser l'ordre DELETE pour
supprimer des lignes

KINDO A AZIZE / UNIVERSITE NAZI BONI 59

 Résumé

Ordre Description
CREATE TABLE Crée une table
ALTER TABLE Modifie la structure d'une table
DROP TABLE Supprime les lignes et la structure d'une table
RENAME • d'une vue,
Change le nom d'une table,
d'une séquence ou d'un synonyme
TRUNCATE Supprime toutes les lignes d'une table et
libère l'espace de stockage de cette table
COMMENT
•
Ajoute des commentaires à une table ou à une
vue
•

KINDO A AZIZE / UNIVERSITE NAZI BONI 60

Les Contraintes

KINDO A AZIZE / UNIVERSITE NAZI BONI 61

Objectifs
• A la fin de cette partie, vous saurez :
• Définir les contraintes
• Créer des contraintes et les maintenir

KINDO A AZIZE / UNIVERSITE NAZI BONI 62

 Contraintes d’intégrité
• règle qui définit la cohérence d'une donnée ou d'un
ensemble de données de la BD
• Contraintes définies en SQL
• non nullité des valeurs d'un attribut
• unicité de la valeur d'un attribut ou d'un groupe
d'attributs
• valeur par défaut pour un attribut
• contrainte de domaine
• clé primaire (un attribut ou un groupe)
• intégrité référentielle "minimale"
CREATE TABLE DEPT (
DEPT integer PRIMARY KEY,
NOMD char (40) NOT NULL,
Lieu integer,
Nbre number(4,2)
CONSTRAINT Cnbre CHECK (Nbre between 5 and 10))
KINDO A AZIZE / UNIVERSITE NAZI BONI 63
Les Contraintes
• Les contraintes contrôlent des règles de gestion au
niveau d'une table.
• Les contraintes empêchent la suppression d'une table
lorsqu'il existe des dépendances.
• Types de contraintes valides :
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

KINDO A AZIZE / UNIVERSITE NAZI BONI 64

Conventions Applicables aux Contraintes
• Vous pouvez créer une contrainte :
• En même temps que la création de la table
• Une fois que la table est créée
• une contrainte peut être définie au niveau table ou colonne.
• Consulter le dictionnaire de données pour retrouver une
contrainte.

KINDO A AZIZE / UNIVERSITE NAZI BONI 65

Les Contraintes
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
…
[table_constraint]);

CREATE TABLE emp(

Matr NUMBER(4),
NOME VARCHAR2(10),
…
DEPT NUMBER(2) NOT NULL,
CONSTRAINT emp_Matr_pk
PRIMARY KEY (Matr));

KINDO A AZIZE / UNIVERSITE NAZI BONI 66

Les Contraintes
• Contrainte au niveau colonne

column [CONSTRAINT constraint_name] constraint_type,

• Contrainte au niveau table

column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),

KINDO A AZIZE / UNIVERSITE NAZI BONI 67

La Contrainte NOT NULL
• Interdit la présence de valeurs NULL dans la
colonne
EMP
Matr NOME PROF ... COMM DEPT

7839 KING PRESIDENT 10

7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7566 JONES MANAGER 20
...

Contrainte NOT NULL Absence de contrainte Contrainte

(aucune ligne ne peut NOT NULL NOT NULL
avoir de valeur NULL (toute ligne peut avoir
dans cette colonne) une valeur NULL dans
cette colonne)

KINDO A AZIZE / UNIVERSITE NAZI BONI 68

La Contrainte NOT NULL
• Se définit au niveau colonne
SQL> CREATE TABLE emp(
2 Matr NUMBER(4),
3 NOME VARCHAR2(10) NOT NULL,
4 PROF VARCHAR2(9),
5 SUP NUMBER(4),
6 DATEEMB DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 DEPT NUMBER(2) NOT NULL);

KINDO A AZIZE / UNIVERSITE NAZI BONI 69

La Contrainte de Clé UNIQUE
contrainte de clé UNIQUE
DEPT
DEPT DNAME LIEU
------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

A insérer Interdit
50 SALES DETROIT (DNAME⎯SALES
existe déjà)
60 BOSTON Autorisé

KINDO A AZIZE / UNIVERSITE NAZI BONI 70

La Contrainte de Clé UNIQUE
• Se définit au niveau table ou colonne

SQL> CREATE TABLE dept(

2 DEPT NUMBER(2),
3 dname VARCHAR2(14),
4 LIEU VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE(dname));

KINDO A AZIZE / UNIVERSITE NAZI BONI 71

La Contrainte PRIMARY KEY
clé PRIMAIRE
DEPT
DEPT DNAME LIEU
------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

A insérer interdit (DEPT-20

20 MARKETING DALLAS existe déjà)

FINANCE NEW YORK interdit

(DEPT est NULL)

KINDO A AZIZE / UNIVERSITE NAZI BONI 72

La Contrainte PRIMARY KEY
• Se définit au niveau table ou colonne

SQL> CREATE TABLE dept(

2 DEPT NUMBER(2),
3 dname VARCHAR2(14),
4 LIEU VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE (dname),
6 CONSTRAINT dept_DEPT_pk PRIMARY KEY(DEPT));

KINDO A AZIZE / UNIVERSITE NAZI BONI 73

La Contrainte FOREIGN
DEPT
KEY
PRIMARY DEPT DNAME LIEU
KEY ------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
...
EMP
Matr NOME PROF ... COMM DEPT FOREIGN
KEY
7839 KING PRESIDENT 10
7698 BLAKE MANAGER 30
...
Interdit
(DEPT⎯
A insérer n'existe pas dans
la table DEPT
7571 FORD MANAGER ... 200 9
7571 FORD MANAGER ... 200 Autorisé

KINDO A AZIZE / UNIVERSITE NAZI BONI 74

La Contrainte FOREIGN KEY
• Se définit au niveau table ou colonne

SQL> CREATE TABLE emp(

2 Matr NUMBER(4),
3 NOME VARCHAR2(10) NOT NULL,
4 PROF VARCHAR2(9),
5 SUP NUMBER(4),
6 DATEEMB DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 DEPT NUMBER(2) NOT NULL,
10 CONSTRAINT emp_DEPT_fk FOREIGN KEY (DEPT)
11 REFERENCES dept (DEPT));

KINDO A AZIZE / UNIVERSITE NAZI BONI 75

Mots-clés Associés à la Contrainte
FOREIGN KEY
• FOREIGN KEY
• Définit la colonne dans la table détail dans une contrainte
de niveau table
• REFERENCES
• Identifie la table et la colonne de la table maître
• ON DELETE CASCADE
• Autorise la suppression d’une ligne dans la table maître et
des lignes dépendantes dans la table détail

KINDO A AZIZE / UNIVERSITE NAZI BONI 76

La Contrainte CHECK
• Définit une condition que chaque ligne doit
obligatoirement satisfaire
• Expressions interdites :
• Références aux pseudo-colonnes CURRVAL, NEXTVAL, LEVEL et
ROWNUM
• Appels aux fonctions SYSDATE, UID, USER et USERENV
• Requêtes faisant référence à d'autres valeurs dans d'autres lignes

..., DEPT NUMBER(2),

CONSTRAINT emp_DEPT_ck
CHECK (DEPT BETWEEN 10 AND 99),...

KINDO A AZIZE / UNIVERSITE NAZI BONI 77

Ajout d'une Contrainte
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);

• Vous pouvez ajouter ou supprimer une contrainte,

mais pas la modifier
• Vous pouvez activer ou désactiver des contraintes
• Pour ajouter une contrainte NOT NULL, utilisez la
clause MODIFY

KINDO A AZIZE / UNIVERSITE NAZI BONI 78

Ajout d'une Contrainte
• Ajouter une contrainte FOREIGN KEY à la table
EMP précisant qu'un manager doit déjà exister
dans la table EMP en tant qu'employé valide.

SQL> ALTER TABLE emp

2 ADD CONSTRAINT emp_SUP_fk
3 FOREIGN KEY(SUP) REFERENCES emp(Matr);
Table altered.

KINDO A AZIZE / UNIVERSITE NAZI BONI 79

Suppression d'une Contrainte
• Supprimer de la table EMP la contrainte concernant
le manager.
SQL> ALTER TABLE emp
2 DROP CONSTRAINT emp_SUP_fk;
Table altered.

• Supprimer la contrainte PRIMARY KEY de

la table DEPT, ainsi que la contrainte
FOREIGN KEY associée définie sur la
colonne [Link].
SQL> ALTER TABLE dept
2 DROP PRIMARY KEY CASCADE;
Table altered.

KINDO A AZIZE / UNIVERSITE NAZI BONI 80

Désactivation de Contraintes
• Pour désactiver une contrainte d'intégrité, utiliser la
clause DISABLE de l'ordre ALTER TABLE.
• Pour désactiver les contraintes d'intégrité
dépendantes, ajouter l'option CASCADE.

SQL> ALTER TABLE emp

2 DISABLE CONSTRAINT emp_Matr_pk CASCADE;
Table altered.

KINDO A AZIZE / UNIVERSITE NAZI BONI 81

Activation de Contraintes
• Pour activer une contrainte d'intégrité actuellement
désactivée dans la définition de la table, utiliser la
clause ENABLE.

SQL> ALTER TABLE emp

2 ENABLE CONSTRAINT emp_Matr_pk;
Table altered.
• Si vous activez une contrainte UNIQUE ou PRIMARY
KEY, un index correspondant est automatiquement
créé.

KINDO A AZIZE / UNIVERSITE NAZI BONI 82

Vérification des Contraintes
• Pour afficher les définitions et noms de toutes
les contraintes, interrogez la table
USER_CONSTRAINTS.

SQL> SELECT constraint_name, constraint_type,

2 search_condition
3 FROM user_constraints
4 WHERE table_name = 'EMP';

CONSTRAINT_NAME C SEARCH_CONDITION
------------------------ - -------------------------
SYS_C00674 C Matr IS NOT NULL
SYS_C00675 C DEPT IS NOT NULL
EMP_Matr_PK P
...

KINDO A AZIZE / UNIVERSITE NAZI BONI 83

 Résumé
• Vous pouvez créer des contraintes de type :
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• Utilisez la table USER_CONSTRAINTS pour afficher les noms et
définitions de toutes les contraintes.

KINDO A AZIZE / UNIVERSITE NAZI BONI 84

Jointure
• " la liste des employés avec leur département qui ont
été embauché en le 01 janvier 2000"
SELECT nomE, NomD
FROM EMP E, DEPT D
WHERE [Link] = [Link]
AND [Link]= ‘01/01/2000’

KINDO A AZIZE / UNIVERSITE NAZI BONI 85

Jointure procédurale
SELECT [Link], [Link]
FROM EMP E
WHERE DEPT IN (
SELECT DEPT
FROM DEPT D) and [Link]= ‘01/01/2000’;

KINDO A AZIZE / UNIVERSITE NAZI BONI 86

Auto-jointure
Donner les noms des employés travaillant dans un département avec
au moins un ingénieur
SELECT [Link]
FROM EMP E1, EMP E2
WHERE [Link] = [Link]
AND [Link] = 'INGÉNIEUR';

KINDO A AZIZE / UNIVERSITE NAZI BONI 87

Auto-jointure
• Jointure d’une relation avec elle-même
→ synonymes
• «Donner le salaire et le nom des employés gagnant plus qu'un (au
moins un) ingénieur
SELECT [Link], [Link]
FROM EMP E1, EMP E2
WHERE [Link]='INGENIEUR'
AND [Link] > [Link];

KINDO A AZIZE / UNIVERSITE NAZI BONI 88

Jointure (syntaxe SQL2)
• SQL2 propose une nouvelle syntaxe plus proche de l’algèbre
relationnelle (jointure directement exprimée dans le FROM)
• Supportée dans plusieurs SGBD (>= Oracle 9, MySQL, SQLServer, …)

KINDO A AZIZE / UNIVERSITE NAZI BONI 89

Exemples de jointure SQL2
• Produit cartésien
SELECT NOME, SAL, LIEU
FROM EMP CROSS JOIN DEPT
• Jointure
SELECT nom
FROM EMP E JOIN DEPT D ON ([Link] =[Link])
WHERE PROF= ‘SECRETAIRE’
• Jointure « naturelle »
• L’égalité sur les attributs de même nom peut être remplacée par NATURAL JOIN, ou
JOIN … USING (attrs)

KINDO A AZIZE / UNIVERSITE NAZI BONI 90

Opérateurs ensemblistes
• Union (norme SQL1)
• Élimination automatique des doublons
SELECT MATR FROM EMP1
UNION
SELECT MATR FROM EMP
• Intersection (norme SQL2 !)
SELECT MATR FROM EMP1
INTERSECT
SELECT MATR FROM EMP
• Différence (norme SQL2 !)
SELECT MATR FROM EMP1
MINUS (ou EXCEPT)
SELECT MATR FROM EMP

KINDO A AZIZE / UNIVERSITE NAZI BONI 91

Fonctions
• 5 fonctions prédéfinies : COUNT, SUM, MIN, MAX, AVG
• Principe :
• S'applique à l'ensemble des valeurs d'une colonne d'une relation
• Produit une valeur unique
• Pour une requête sans partionnement (plus tard) :
• uniquement dans le SELECT, jamais dans le WHERE
• Ne pas mélanger dans le SELECT les fonctions et les attributs
simples !

KINDO A AZIZE / UNIVERSITE NAZI BONI 92

Exemples avec fonctions
• "Donner la moyenne des salaires de tous les employés"
SELECT Avg(SAL)
FROM EMP

• "Donner la masse salariale des ingénieurs"

SELECT Sum (SAL)
FROM EMP
WHERE PROF= ‘INGENIEUR'

KINDO A AZIZE / UNIVERSITE NAZI BONI 93

Exemples avec fonctions (2)
• "Nombre de PROFs distinct"
SELECT COUNT(DISTINCT PROF)
FROM EMP

• "Nombre d’employés"
SELECT COUNT (*)
FROM EMP

KINDO A AZIZE / UNIVERSITE NAZI BONI 94

 Regrouper les Données
avec les Fonctions de Groupe

KINDO A AZIZE / UNIVERSITE NAZI BONI 95

Objectifs
• A la fin de cette partie, vous saurez :
• Identifier les fonctions de groupe disponibles
• Expliquer l'utilisation des fonctions de groupe
• Regrouper les données avec la clause GROUP BY
• Inclure ou exclure des groupes de lignes avec la
clause HAVING

KINDO A AZIZE / UNIVERSITE NAZI BONI 96

Partitionnement
• Principe
• partitionnement horizontal d'une relation, selon les valeurs d'un
attribut ou d'un groupe d'attributs qui est spécifié dans la clause
GROUP BY
• la relation est (logiquement) fragmentée en groupes de tuples, où
tous les tuples de chaque groupe ont la même valeur pour
l'attribut (ou le groupe d'attributs) de partitionnement
• Fonctions sur les groupes
• Restrictions sur les groupes
• application possible d'un critère de restriction sur les groupes
obtenus
• clause HAVING

KINDO A AZIZE / UNIVERSITE NAZI BONI 97

 Exemples de partitionnement
• " La moyenne des salaires par PROF…"
SELECT PROF, AVG(SAL)
FROM EMP
GROUP BY PROF;

• " … avec un tri décroissant"

SELECT PROF, AVG(SAL) sal1
FROM EMP
GROUP BY PROF
ORDER BY PROF desc;

KINDO A AZIZE / UNIVERSITE NAZI BONI 98

Fonctions de Groupe
• Les fonctions de groupe agissent sur des groupes de lignes et
donnent un résultat par groupe.
EMP
DEPT SAL
--------- ---------
10 2450
10 5000
10 1300
20 800
20 1100
"salaire maximum MAX(SAL)
20 3000
20 3000 de la table EMP" ---------
20 2975 5000
30 1600
30 2850
30 1250
30 950
30 1500
30 1250

KINDO A AZIZE / UNIVERSITE NAZI BONI 99

Types de Fonctions de Groupe
• AVG ([DISTINCT|ALL]n)
• COUNT ({ *|[DISTINCT|ALL]expr})
• MAX ([DISTINCT|ALL]expr)
• MIN ([DISTINCT|ALL]expr)
• STDDEV ([DISTINCT|ALL]n)
• SUM ([DISTINCT|ALL]n)
• VARIANCE ([DISTINCT|ALL]n)

KINDO A AZIZE / UNIVERSITE NAZI BONI 100

Fonctions AVG et SUM
• AVG et SUM s'utilisent avec des données numériques.

SQL> SELECT AVG(sal), MAX(sal),

2 MIN(sal), SUM(sal)
3 FROM emp
4 WHERE PROF LIKE 'SALES%';

AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)

-------- --------- --------- ---------
1400 1600 1250 5600

KINDO A AZIZE / UNIVERSITE NAZI BONI 101

Fonctions MIN et MAX
• MIN et MAX s'utilisent avec tous types de
données.
SQL> SELECT MIN(DATEEMB), MAX(DATEEMB)
2 FROM emp;

KINDO A AZIZE / UNIVERSITE NAZI BONI 102

Utilisation de la Fonction COUNT
• COUNT(*) ramène le nombre de lignes d'une table.
• COUNT(expr) ramène le nombre de lignes non NULL
SQL> SELECT COUNT(*)
2 FROM emp
3 WHERE DEPT = 30;

SQL> SELECT COUNT(comm)

2 FROM emp
3 WHERE DEPT = 30;

KINDO A AZIZE / UNIVERSITE NAZI BONI 103

Fonctions de Groupe et Valeurs NULL
• Les fonctions de groupe ignorent les valeurs
NULL des colonnes.
SQL> SELECT AVG(comm)
2 FROM emp;

AVG(COMM)
---------
550

KINDO A AZIZE / UNIVERSITE NAZI BONI 104

Utilisation de la Fonction NVL avec les Fonctions
de Groupe

• La fonction NVL force la prise en compte des

valeurs NULL dans les fonctions de groupe.

SQL> SELECT AVG(NVL(comm,0))

2 FROM emp;

AVG(NVL(COMM,0))
----------------
157.14286

KINDO A AZIZE / UNIVERSITE NAZI BONI 105

Création de Groupes de Données
EMP
DEPT SAL
--------- ---------
10 2450
10 5000 2916.6667
10 1300
20 800 "salaire DEPT AVG(SAL)
20 1100 moyen pour ------- ---------
20 3000 chaque
2175
10 2916.6667
20 3000 département
20 2975 de la table EMP" 20 2175
30 1600 30 1566.6667
30 2850
30 1250 1566.6667
30 950
30 1500
30 1250

KINDO A AZIZE / UNIVERSITE NAZI BONI 106

Création de Groupes de Données :
la Clause GROUP BY

SELECT column, group_function

FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

• Divisez une table en groupes de lignes avec la

clause GROUP BY.

KINDO A AZIZE / UNIVERSITE NAZI BONI 107

Utilisation de la Clause GROUP BY
• La clause GROUP BY doit inclure toutes les colonnes
de la liste SELECT qui ne figurent pas dans des
fonctions de groupe.
SQL> SELECT DEPT, AVG(sal)
2 FROM emp
3 GROUP BY DEPT;

DEPT AVG(SAL)
--------- ---------
10 2916.6667
20 2175
30 1566.6667

KINDO A AZIZE / UNIVERSITE NAZI BONI 108

Utilisation de la Clause GROUP BY
• La colonne citée en GROUP BY ne doit pas
nécessairement figurer dans la liste SELECT.
SQL> SELECT AVG(sal)
2 FROM emp
3 GROUP BY DEPT;

AVG(SAL)
---------
2916.6667
2175
1566.6667

KINDO A AZIZE / UNIVERSITE NAZI BONI 109

Regroupement sur Plusieurs Colonnes
EMP
DEPT PROF SAL
--------- --------- ---------
10 MANAGER 2450
DEPT PROF SUM(SAL)
10 PRESIDENT 5000
-------- --------- ---------
10 CLERK 1300
10 CLERK 1300
20 CLERK 800 '"somme des
10 MANAGER 2450
20 CLERK 1100 salaires
10 PRESIDENT 5000
20 ANALYST 3000 de la table EMP
20 ANALYST 6000
20 ANALYST 3000 pour chaque PROF,
20 CLERK 1900
20 MANAGER 2975 regroupés par
20 MANAGER 2975
30 SALESMAN 1600 département"
30 CLERK 950
30 MANAGER 2850
30 MANAGER 2850
30 SALESMAN 1250
30 SALESMAN 5600
30 CLERK 950
30 SALESMAN 1500
30 SALESMAN 1250

KINDO A AZIZE / UNIVERSITE NAZI BONI 110

Utilisation de la Clause GROUP BY sur
Plusieurs Colonnes
SQL> SELECT DEPT, PROF, sum(sal)
2 FROM emp
3 GROUP BY DEPT, PROF;

DEPT PROF SUM(SAL)

--------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
...
9 rows selected.

KINDO A AZIZE / UNIVERSITE NAZI BONI 111

Erreurs d'Utilisation des Fonctions
de Groupe dans une Requête

• Toute colonne ou expression de la liste SELECT

autre qu'une fonction de groupe, doit être incluse
dans la clause GROUP BY.

SQL> SELECT DEPT, COUNT(NOME)

2 FROM emp;

SELECT DEPT, COUNT(NOME)

*
ERROR at line 1:
ORA-00937: not a single-group group function

KINDO A AZIZE / UNIVERSITE NAZI BONI 112

Erreurs d'utilisation des Fonctions de
Groupe dans une Requête
• Vous ne pouvez utiliser la clause WHERE pour limiter les
groupes.
• Utilisez la clause HAVING.

SQL> SELECT dept, AVG(sal)

2 FROM emp
3 WHERE AVG(sal) > 200000
4 GROUP BY dept;

WHERE AVG(sal) > 2000

*
ERROR at line 3:
ORA-00934: group function is not allowed here

KINDO A AZIZE / UNIVERSITE NAZI BONI 113

Exclusion de Groupes
EMP
DEPT SAL
--------- ---------
10 2450
10 5000 5000
10 1300
20 800
20 1100 "salaire maximum DEPT MAX(SAL)
20 3000 supérieur à --------- ---------
3000
20 3000 $2900 dans 10 5000
20 2975 chaque département" 20 3000
30 1600
30 2850
30 1250
2850
30 950
30 1500
30 1250

KINDO A AZIZE / UNIVERSITE NAZI BONI 114

 Exclusion de Groupes : la Clause
HAVING
• Utilisez la clause HAVING pour restreindre les groupes
• Les lignes sont regroupées.
• La fonction de groupe est appliquée.
• Les groupes qui correspondent à la clause HAVING sont affichés.

SELECT column, group_function

FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

KINDO A AZIZE / UNIVERSITE NAZI BONI 115

Utilisation de la clause HAVING
SQL> SELECT DEPT, max(sal)
2 FROM emp
3 GROUP BY DEPT
4 HAVING max(sal)>2000000;

KINDO A AZIZE / UNIVERSITE NAZI BONI 116

Utilisation de la Clause HAVING
La somme des salaires des non commerciaux touchant plus de 500000
SQL> SELECT PROF as POSTE, SUM(sal) PAYROLL
2 FROM emp
3 WHERE PROF NOT LIKE ‘COM%'
3 GROUP BY PROF
4 HAVING SUM(sal)>500000
5 ORDER BY SUM(sal);

KINDO A AZIZE / UNIVERSITE NAZI BONI 117

Imbrication des Fonctions
de Groupe
• Afficher le salaire moyen maximum des départements.

SQL> SELECT max(avg(sal))

2 FROM emp
3 GROUP BY dept;

KINDO A AZIZE / UNIVERSITE NAZI BONI 118

Résumé
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

KINDO A AZIZE / UNIVERSITE NAZI BONI 119

Opérateurs Ensemblistes

KINDO A AZIZE / UNIVERSITE NAZI BONI 120

Objectifs
• A la fin de cette partie, vous saurez :
• Décrire les opérateurs ensemblistes
• Utiliser un opérateur ensembliste pour combiner
plusieurs requêtes en une seule
• Vérifier l'ordre des lignes ramenées

KINDO A AZIZE / UNIVERSITE NAZI BONI 121

Opérateurs
A B Ensemblistes
Intersect

A B A B

Union / Union All

A B

Minus

KINDO A AZIZE / UNIVERSITE NAZI BONI 122

EMP
Tables Utilisées dans cette Partie
Matr NOME PROF SUP DATEEMB SAL COMM DEPT
--------- ---------- --------- --------- --------- --------- --------- --------
-
7839 KING PRESIDENT 17-NOV-81 5000
10
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 1500
10
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 EMPID NAME TITLE DATE_OUT
7499 ALLEN SALESMAN DEPTID 7698 20-FEB-81 1600 300
30 --------- -------------------- --------- --------- --------
7844 TURNER SALESMAN - 7698 08-SEP-81 1500 0
30 6087 SPENCER OPERATOR 27-NOV-81
7900 JAMES CLERK 20 7698 03-DEC-81 950
30 EMP_HISTORY 6185 VANDYKE MANAGER 17-JAN-81
7521 WARD SALESMAN 10 7698 22-FEB-81 1250 500
30 6235 BALFORD CLERK 22-FEB-80
7902 FORD ANALYST 20 7566 03-DEC-81 3000
20 7788 SCOTT ANALYST 05-MAY-81
7369 SMITH CLERK 20 7902 17-DEC-80 800
20 KINDO7001 JEWELL
A AZIZE / UNIVERSITE NAZI BONI ANALYST 10-JUN-81 123
7788 SCOTT ANALYST 30 7566 09-DEC-82 3000
UNION
A B

KINDO A AZIZE / UNIVERSITE NAZI BONI 124

Utilisation de l'Opérateur UNION
• Affichez le nom, le PROF et le département de
tous les employés.
SQL> SELECT NOME, PROF, DEPT
2 FROM emp
3 UNION
4 SELECT name, title, deptid
5 FROM emp_history;

NOME PROF DEPT

---------- --------- ---------
ADAMS CLERK 30
ALLEN SALESMAN 30
ALLEN SALESMAN 20
BALFORD CLERK 20
BLAKE MANAGER 30
...
20 rows selected.

KINDO A AZIZE / UNIVERSITE NAZI BONI 125

Utilisation de l'Opérateur UNION
• Affichez le nom, le PROF et le département et le
salaire de tous les employés.
SQL> SELECT NOME, PROF, DEPT,sal
2 FROM emp
3 UNION
4 SELECT name, title, deptid,0
5 FROM emp_history;

NOME PROF DEPT SAL

---------- --------- --------- ---------
ADAMS CLERK 30 1100
ALLEN SALESMAN 30 0
ALLEN SALESMAN 20 1600
BALFORD CLERK 20 0
BLAKE MANAGER 30
...
20 rows selected.

KINDO A AZIZE / UNIVERSITE NAZI BONI 126

UNION ALL
A B

KINDO A AZIZE / UNIVERSITE NAZI BONI 127

Utilisation de l'Opérateur
UNION ALL
• Affichez le nom, le numéro et le PROF de tous les employés.
SQL> SELECT NOME, Matr, PROF
2 FROM emp
3 UNION ALL
4 SELECT name, empid, title
5 FROM emp_history;

NOME Matr PROF

---------- --------- ---------
KING 7839 PRESIDENT
BLAKE 7698 MANAGER
CLARK 7782 MANAGER
CLARK 7782 MANAGER
MARTIN 7654 SALESMAN
...
23 rows selected.

KINDO A AZIZE / UNIVERSITE NAZI BONI 128

INTERSECT
A B

KINDO A AZIZE / UNIVERSITE NAZI BONI 129

Utilisation de l'Opérateur INTERSECT
• Affichez les différents noms, numéros et PROFs des
employés présents dans les tables EMP et
EMP_HISTORY.
SQL> SELECT NOME, Matr, PROF
2 FROM emp
3 INTERSECT
4 SELECT name, empid, title
5 FROM emp_history;

NOME Matr PROF

---------- --------- ---------
ALLEN 7499 SALESMAN
CLARK 7782 MANAGER
SCOTT 7788 ANALYST

KINDO A AZIZE / UNIVERSITE NAZI BONI 130

MINUS
A B

KINDO A AZIZE / UNIVERSITE NAZI BONI 131

MINUS
Affichez le nom,le numéro et le PROF de tous
les employés ayant quitté la société.

SQL> SELECT name, empid, title

2 FROM emp_history
3 MINUS
4 SELECT NOME, Matr, PROF
5 FROM emp;

NAME EMPID TITLE

---------- --------- ---------
BALFORD 6235 CLERK
BRIGGS 7225 PAY CLERK
JEWELL 7001 ANALYST
SPENCER 6087 OPERATOR
...
6 rows selected. KINDO A AZIZE / UNIVERSITE NAZI BONI 132
Règles des Opérateurs
Ensemblistes
• Les expressions citées dans la clause SELECT doivent être
égales en nombre et avoir des données du même type.
• Les doublons sont automatiquement éliminés, sauf avec
l'opérateur UNION ALL.
• Les noms de colonnes apparaissant dans le résultat sont
ceux de la première requête.
• Par défaut, le résultat est trié par ordre croissant, sauf
avec l’opérateur UNION ALL.
• Utilisez des parenthèses pour modifier la séquence
d'exécution.

KINDO A AZIZE / UNIVERSITE NAZI BONI 133

Résumé
• L'opérateur UNION ramène toutes les lignes distinctes.
• L'opérateur UNION ALL ramène toutes les lignes, y
compris les doublons.
• L'opérateur INTERSECT ramène toutes les lignes
partagées par deux requêtes.
• L'opérateur MINUS ramène toutes les lignes distinctes
sélectionnées par la première requête, et non par la
seconde.
• La clause ORDER BY doit être placée à la fin de l'ordre.

KINDO A AZIZE / UNIVERSITE NAZI BONI 134

Sous-Interrogations

KINDO A AZIZE / UNIVERSITE NAZI BONI 135

Objectifs
• A la fin de cette, vous saurez :
• Décrire les types de problèmes que les sous-
interrogations peuvent résoudre
• Définir des sous-interrogations
• Énumérer les types de sous-interrogations
• Écrire des sous-interrogations mono-ligne et multi-
ligne

KINDO A AZIZE / UNIVERSITE NAZI BONI 136

 Définition
• Une Sous-interrogation est un ordre SELECT imbriqué dans une clause
d’un autre ordre SELECT .
• Elles permettent de sélectionner des lignes d’une table lorsque la
condition dépend des données de la table elle-même .
• Peuvent être placées dans les clauses SQL suivantes :
WHERE, HAVING, FROM

KINDO A AZIZE / UNIVERSITE NAZI BONI 137

Utilisation d'une Sous-Interrogation
pour Résoudre un Problème
• "Qui a un salaire supérieur à celui de Ali?"

Requête principale

"Quel employé a un salaire supérieur à celui

? de Ali?"

sous-interrogation

?
"Quel est le salaire de Ali?"

KINDO A AZIZE / UNIVERSITE NAZI BONI 138

Sous-Interrogations
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);

• La sous-interrogation (requête interne) est exécutée une fois

avant la requête principale.
• Le résultat de la sous-interrogation est utilisé par la requête
principale (externe).

KINDO A AZIZE / UNIVERSITE NAZI BONI 139

Utilisation d'une Sous-Interrogation
SQL> SELECT NOME
2 FROM emp 2975
3 WHERE sal >
4 (SELECT sal
5 FROM emp
6 WHERE Matr=7566);

NOME
----------
KING
FORD
SCOTT

KINDO A AZIZE / UNIVERSITE NAZI BONI 140

Conventions d'Utilisation des
Sous-Interrogations
• Placez les sous-interrogations entre parenthèses.
• Placez les sous-interrogations à droite de l'opérateur
de comparaison.
• N'ajoutez jamais de clause ORDER BY à une sous-
interrogation.
• Utilisez les opérateurs mono-ligne avec les sous-
interrogations mono-ligne.
• Utilisez les opérateurs multi-ligne avec les sous-
interrogations multi-ligne.

KINDO A AZIZE / UNIVERSITE NAZI BONI 141

 Types de Sous-Interrogations
• Sous-interrogation mono-ligne
Requête principale

sous-interrogation

• Sous-interrogation multi-ligne
Requête principale

sous-interrogation

• Sous-interrogation multi-colonne
Requête principale

sous-interrogation

KINDO A AZIZE / UNIVERSITE NAZI BONI 142

Sous-Interrogations Mono-ligne
• Ne ramènent qu'une seule ligne
• Utilisent des opérateurs de comparaison mono-ligne

Opérateur Signification
= Egal à
> Supérieur à
>= Supérieur ou égal à
< Inférieur à
<= Inférieur ou égal à
<> Différent de

KINDO A AZIZE / UNIVERSITE NAZI BONI 143

Exécution de Sous-Interrogations
Mono-ligne
SQL> SELECT NOME, PROF
2 FROM emp
3 WHERE PROF = CLERK
4 (SELECT PROF
5 FROM emp
6 WHERE Matr = 7369)
7 AND sal > 1100
8 (SELECT sal
9 FROM emp
10 WHERE Matr = 7876);

NOME PROF
---------- ---------
MILLER CLERK

KINDO A AZIZE / UNIVERSITE NAZI BONI 144

Utilisation de Fonctions de Groupe
dans une Sous-Interrogation

SQL> SELECT NOME, PROF, sal

800
2 FROM emp
3 WHERE sal =
4 (SELECT MIN(sal)
5 FROM emp);

NOME PROF SAL

---------- --------- ---------
SMITH CLERK 800

KINDO A AZIZE / UNIVERSITE NAZI BONI 145

Clause HAVING avec Sous-
Interrogations
• Oracle Server exécute les sous-interrogations en
premier.
• Oracle Server ramène les résultats dans la clause
HAVING de la requête principale.

SQL> SELECT DEPT, MIN(sal)

2 FROM emp
3 GROUP BY DEPT 800
4 HAVING MIN(sal) >
5 (SELECT MIN(sal)
6 FROM emp
7 WHERE DEPT = 20);

KINDO A AZIZE / UNIVERSITE NAZI BONI 146

Exemple
• Trouver la profession ayant le salaire moyen le
moins élevé .

SELECT PROF, AVG(sal)

FROM emp
GROUP BY PROF
HAVING AVG(sal) =
( SELECT MIN(AVG(sal))
FROM emp
GROUP BY PROF )

KINDO A AZIZE / UNIVERSITE NAZI BONI 147

Qu'est-ce Qui ne Va pas dans cet
Ordre ?
SQL> SELECT Matr, NOME
2 FROM emp
3 WHERE sal =
4 (SELECT MIN(sal)
5 FROM emp
6 GROUP BY DEPT);

ERROR:
ORA-01427: single-row sub-query returns more than
one row

no rows selected

KINDO A AZIZE / UNIVERSITE NAZI BONI 148

Cet Ordre Va-t-il Fonctionner ?
SQL> SELECT NOME, PROF
2 FROM emp
3 WHERE PROF =
4 (SELECT PROF
5 FROM emp
6 WHERE NOME='SMYTHE');

no rows selected

KINDO A AZIZE / UNIVERSITE NAZI BONI 149

Sous-Interrogation Multi-ligne
• Ramène plusieurs lignes
• Utilise des opérateurs de comparaison multi-ligne

Opérateur Signification
IN Egal à un élément quelconque de la liste

ANY Compare la valeur à chaque valeur ramenée

par la sous-interrogation

ALL Compare la valeur à toutes les valeurs

ramenées par la sous-interrogation

KINDO A AZIZE / UNIVERSITE NAZI BONI 150

 Exemple

• Trouver les employés qui gagnent

l’équivalent d’un salaire minimum de
département .

KINDO A AZIZE / UNIVERSITE NAZI BONI 151

Utilisation de l'Opérateur ANY
dans les Sous-Interrogations Multi-ligne
SQL> SELECT Matr, NOME, PROF 1300
1100
2 FROM emp 800
3 WHERE sal < ANY 950
4 (SELECT sal
5 FROM emp
6 WHERE PROF = 'CLERK')
7 AND PROF <> 'CLERK';

Matr NOME PROF

--------- ---------- ---------
7654 Ky SALESMAN
7521 Kanazoe SALESMAN

KINDO A AZIZE / UNIVERSITE NAZI BONI 152

Utilisation de l'Opérateur ALL dans les
Sous-Interrogations Multi-ligne

SQL> SELECT Matr, NOME, PROF 1566.6667

2 FROM emp 2175
2916.6667
3 WHERE sal > ALL
4 (SELECT avg(sal)
5 FROM emp
6 GROUP BY DEPT)

Matr NOME PROF

--------- ---------- ---------
7839 KING PRESIDENT
7566 JONES MANAGER
7902 FORD ANALYST
7788 SCOTT ANALYST

KINDO A AZIZE / UNIVERSITE NAZI BONI 153

Prédicats :ALL, ANY, EXISTS
• ALL
• Donner le salaire et le nom des employés gagnant plus que tous les
ingénieurs. SQL :
SELECT NOME, SAL FROM EMP
WHERE SAL > ALL (SELECT SAL
FROM EMP
WHERE PROF='INGENIEUR');

KINDO A AZIZE / UNIVERSITE NAZI BONI 154

Prédicats (2)
• ANY :

• Donner le salaire et le nom des employés gagnant plus qu'un (au

moins un) ingénieur.
SELECT ENOM, SAL FROM EMP
WHERE SAL > ANY (SELECT SAL
FROM EMP
WHERE PROF='INGENIEUR');

KINDO A AZIZE / UNIVERSITE NAZI BONI 155

Prédicats (3)
• EXISTS :
• Nom des employés qui travaillent dans le department Research.
SELECT [Link]
FROM EMP E
WHERE EXISTS (SELECT *
FROM DEPT D
WHERE [Link] = [Link]
AND [Link] = 'Research')

KINDO A AZIZE / UNIVERSITE NAZI BONI 156

Résumé
• Les sous-interrogations sont utiles lorsqu'une
requête fait appel à des valeurs inconnues.

SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);

KINDO A AZIZE / UNIVERSITE NAZI BONI 157

 Recherche des données
Synthèse
6 SELECT <liste et/ou expressions Projection de l’ensemble obtenu en (5)
attributs Aj et/ou fonctions sur sur les Aj, calcul des expressions,
Attributs Ap> calcul des fonctions (appliquées aux
groupes s’il y en a) sur Ap

1 FROM <liste de relations Ri> Produit cartésien des relations Ri

2 WHERE <Conditions sur les tuples> : Sélection des tuples de (1) respectant
C1 la condition C1

3 GROUP BY <liste attributs Ak  Aj> Partitionnement de l’ensemble obtenu

en (2) suivant les valeurs Ak

4 HAVING <condition sur groupes - Sélection des groupes de (3) vérifiant

fonctions> : C2 C2

5 ORDER BY <liste d’attributs Al ou n° ordre Tri des tuples obtenus en (4) suivant
dans le SELECT> les valeurs Al

KINDO A AZIZE / UNIVERSITE NAZI BONI 158

 Recherche des données

Synthèse (2)
• Condition de recherche : • Condition élémentaire :
• WHERE (sélection de • Évaluée à Vrai ou Faux
tuples), HAVING (sélection • Prédicat :
de groupes) • Comparaison : =, <, <=, >,
>=, <>
• Compositions de conditions • Attribut/valeur
élémentaires (AND, OR, • Attribut/attribut
NOT) • Intervalle :BETWEEN
• Évaluée à Vrai ou Faux • Chaîne : LIKE
• Nullité : IS NULL
• Appartenance : IN
• Quantification : EXISTS,
ANY, ALL

KINDO A AZIZE / UNIVERSITE NAZI BONI 159

Manipulation des Données

KINDO A AZIZE / UNIVERSITE NAZI BONI 160

Objectifs
• A la fin de cette partie, vous saurez :
• Décrire chaque ordre du LMD
• Insérer des lignes dans une table
• Mettre à jour des lignes dans une table
• Supprimer des lignes d'une table
• Contrôler les transactions

KINDO A AZIZE / UNIVERSITE NAZI BONI 161

Langage de Manipulation des
Données
• Un ordre du LMD est exécuté lorsque :
• Vous ajoutez des lignes à une table
• Vous modifiez des lignes existantes dans une
table
• Vous supprimez des lignes d'une table
• Une transaction est un ensemble d'ordres du LMD
formant une unité de travail logique.

KINDO A AZIZE / UNIVERSITE NAZI BONI 162

 Ajout d'une Nouvelle Ligne dans
une Table
50 DEVELOPMENT DETROIT
Nouvelle ligne "…insérer une nouvelle
DEPT ligne dans la table
DEPT DNAME LIEU
DEPT …"
------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS DEPT
30 SALES CHICAGO DEPT DNAME LIEU
40 OPERATIONS BOSTON ------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DEVELOPMENT DETROIT

KINDO A AZIZE / UNIVERSITE NAZI BONI 163

L'Ordre INSERT
• L'ordre INSERT permet d'ajouter de nouvelles lignes
dans une table.
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);

• Cette syntaxe n'insère qu'une seule ligne à la fois.

KINDO A AZIZE / UNIVERSITE NAZI BONI 164

Insertion de Nouvelles Lignes
• Insérez une nouvelle ligne en précisant une valeur
pour chaque colonne.
• Eventuellement, énumérez les colonnes dans la
clause INSERT.
SQL> INSERT INTO dept (DEPT, dname, LIEU)
2 VALUES (50, 'DEVELOPMENT', 'DETROIT');
1 row created.
• Indiquez les valeurs dans l'ordre par défaut des
colonnes dans la table.
• Placez les valeurs de type caractère et date entre
simples quotes.

KINDO A AZIZE / UNIVERSITE NAZI BONI 165

Insertion de Lignes Contenant des
Valeurs NULL
• Méthode implicite : ne spécifiez pas la colonne dans
la liste.

SQL> INSERT INTO dept (DEPT, dname )

2 VALUES (60, 'MIS');
1 row created.

• Méthode explicite : spécifiez le mot-clé

NULL.
SQL> INSERT INTO dept
2 VALUES (70, 'FINANCE', NULL);
1 row created.

KINDO A AZIZE / UNIVERSITE NAZI BONI 166

Insertion de Valeurs Spéciales
• La fonction SYSDATE renvoie la date et l'heure
courantes.

SQL> INSERT INTO emp (Matr, NOME, PROF,

2 SUP, DATEEMB, sal, comm,
3 DEPT)
4 VALUES (7196, 'GREEN', 'SALESMAN',
5 7782, SYSDATE, 2000, NULL,
6 10);
1 row created.

KINDO A AZIZE / UNIVERSITE NAZI BONI 167

Insertion de Dates dans un Format
Spécifique
• Ajout d'un nouvel employé.
SQL> INSERT INTO emp
2 VALUES (2296,'AROMANO','SALESMAN',7782,
3 TO_DATE('FEB 3,97', 'MON DD,YY'),
4 1300, NULL, 10);
1 row created.

• Vérification de l'ajout.
Matr NOME PROF SUP DATEEMB SAL COMM DEPT
----- ------- -------- ---- --------- ---- ----- -----
2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10

KINDO A AZIZE / UNIVERSITE NAZI BONI 168

Copie de Lignes d'une Autre Table

• Ecrivez votre ordre INSERT en spécifiant une sous-

interrogation.

SQL> INSERT INTO managers(id, name, salary, DATEEMB)

2 SELECT Matr, NOME, sal, DATEEMB
3 FROM emp
4 WHERE PROF = 'MANAGER';
3 rows created.

• N'utilisez pas la clause VALUES.

• Le nombre de colonnes de la clause INSERT doit
correspondre à celui de la sous-interrogation.

KINDO A AZIZE / UNIVERSITE NAZI BONI 169

 Modification des Données
d'une Table
EMP
Matr NOME PROF ... DEPT "…modifier une
7839 KING PRESIDENT 10 ligne
7698 BLAKE MANAGER 30 de la table EMP…"
7782 CLARK MANAGER 10
7566 JONES MANAGER 20
...

EMP
Matr NOME PROF ... DEPT

7839 KING PRESIDENT 10

7698 BLAKE MANAGER 30
7782 CLARK MANAGER 20
10
7566 JONES MANAGER 20
...

KINDO A AZIZE / UNIVERSITE NAZI BONI 170

L'Ordre UPDATE
• Utilisez l'ordre UPDATE pour modifier des lignes
existantes.
•
UPDATE table
SET column = value [, column = value]
[WHERE condition];

• Si nécessaire, vous pouvez modifier plusieurs lignes

à la fois.

KINDO A AZIZE / UNIVERSITE NAZI BONI 171

Modification de Lignes
d'une Table
• La clause WHERE permet de modifier une ou
plusieurs lignes spécifiques.
SQL> UPDATE emp
2 SET DEPT = 20
3 WHERE Matr = 7782;
1 row updated.

• Si vous omettez la clause WHERE, toutes les lignes

sont modifiées.
SQL> UPDATE employee
2 SET DEPT = 20;
14 rows updated.

KINDO A AZIZE / UNIVERSITE NAZI BONI 172

Modification avec une
Sous-Interrogation Multi-colonne
• Modifier le PROF et le n° de département de
l'employé 7698 à l'identique de l'employé 7499.

SQL> UPDATE emp

2 SET (PROF, DEPT) =
3 (SELECT PROF, DEPT
4 FROM emp
5 WHERE Matr = 7499)
6 WHERE Matr = 7698;
1 row updated.

KINDO A AZIZE / UNIVERSITE NAZI BONI 173

Modification de Lignes en
Fonction d'une Autre Table
• Utilisez des sous-interrogations dans l'ordre
UPDATE pour modifier des lignes d'une table à
l'aide de valeurs d'une autre table.

SQL> UPDATE employee

2 SET DEPT = (SELECT DEPT
3 FROM emp
4 WHERE Matr = 7788)

KINDO A AZIZE / UNIVERSITE NAZI BONI 174

Ordre UPDATE Synchronisé
• Syntaxe

Utilisez une Sous-Interrogation Synchronisée pour

UPDATE table1
mettre à jouralias1
les lignes d'une table basée sur des
SET column = (SELECT expression
lignes d'une autre table.
FROM table2 alias2
WHERE [Link] = [Link]);

KINDO A AZIZE / UNIVERSITE NAZI BONI 175

Ordre UPDATE Synchronisé
Exemple
• Ajouter une nouvelle colonne à la table emp
pour stocker le nom du département, puis
remplissez la table à l’aide d’un UPDATE
Synchronisé .

UPDATE emp e
SET dname = (SELECT dname
FROM dept d
WHERE [Link] = [Link]);

KINDO A AZIZE / UNIVERSITE NAZI BONI 176

Modification de Lignes :
Erreur de Contrainte d'Intégrité

SQL> UPDATE emp

2 SET DEPT = 55
3 WHERE DEPT = 10;

UPDATE emp
*
ERROR at line 1:
ORA-02291: integrity constraint (USR.EMP_DEPT_FK)
violated - parent key not found

KINDO A AZIZE / UNIVERSITE NAZI BONI 177

Suppression d'une Ligne d'une Table
DEPT
DEPT DNAME LIEU
------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS "…supprime une ligne
30 SALES CHICAGO
40 OPERATIONS BOSTON de la table DEPT…"
50 DEVELOPMENT DETROIT
60 MIS DEPT
...
DEPT DNAME LIEU
------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 MIS
...

KINDO A AZIZE / UNIVERSITE NAZI BONI 178

L'Ordre DELETE
• Vous pouvez supprimer des lignes d'une table
au moyen de l'ordre DELETE.

DELETE [FROM] table

[WHERE condition];

KINDO A AZIZE / UNIVERSITE NAZI BONI 179

Suppression de Lignes d'une Table
• La clause WHERE permet de supprimer une ou
plusieurs lignes spécifiques.
SQL> DELETE FROM department
2 WHERE dname = 'DEVELOPMENT';
1 row deleted.

• Si vous omettez la clause WHERE, toutes les lignes

sont supprimées.

SQL> DELETE FROM department;

4 rows deleted.

KINDO A AZIZE / UNIVERSITE NAZI BONI 180

Suppression de Lignes en Faisant
Référence à une Autre Table
• Utilisez des sous-interrogations dans l'ordre
DELETE pour supprimer des lignes dont
certaines valeurs correspondent à celles d'une
autre table.

SQL> DELETE FROM employee

2 WHERE DEPT =
3 (SELECT DEPT
4 FROM dept
5 WHERE dname ='SALES');
6 rows deleted.

KINDO A AZIZE / UNIVERSITE NAZI BONI 181

Ordre DELETE Synchronisé

Syntaxe
SQL> DELETE FROM table1 alias1
2 WHERE column operator
3 (SELECT expression
3 FROM table2 alias2
4 WHERE [Link] = [Link]);

Utilisez une Sous-Interrogation Synchronisée

pour supprimer uniquement les lignes existant
également dans une autre table.

KINDO A AZIZE / UNIVERSITE NAZI BONI 182

Ordre DELETE Synchronisé
Exemple
• Supprimer les employés de la table emp_history
qui appartiennent aussi à la table emp .

SQL> DELETE FROM emp_history eh

WHERE empid = (SELECT Matr
FROM emp e
WHERE [Link] = [Link]);

KINDO A AZIZE / UNIVERSITE NAZI BONI 183

Suppression de Lignes :
Erreur de Contrainte d'Intégrité

SQL> DELETE FROM dept

2 WHERE DEPT = 10;

DELETE FROM dept

*
ERROR at line 1:
ORA-02292: integrity constraint (USR.EMP_DEPT_FK)
violated - child record found

KINDO A AZIZE / UNIVERSITE NAZI BONI 184

Transactions de Base de Données
• Une transaction se compose des éléments
suivants :
• Ensemble d'ordres du LMD effectuant une
modification cohérente des données
• Un ordre du LDD
• Un ordre du LCD

KINDO A AZIZE / UNIVERSITE NAZI BONI 185

 Transactions de Base de Données
Une transaction :
• Commence à l'exécution du premier ordre SQL
• Se termine par l'un des événements suivants :
• COMMIT ou ROLLBACK
• Exécution d'un ordre LDD ou LCD (validation
automatique)
• Fin de session utilisateur
• Panne du système

KINDO A AZIZE / UNIVERSITE NAZI BONI 186

 Avantages des Ordres
COMMIT et ROLLBACK
• Garantit la cohérence des données
• Possibilité d'afficher le résultat des modifications avant qu'elles ne
soient définitives
• Regroupement logique d'opérations

KINDO A AZIZE / UNIVERSITE NAZI BONI 187

Contrôle des Transactions
Transaction

INSERT UPDATE INSERT DELETE

COMMIT Savepoint A Savepoint B

ROLLBACK to Savepoint B

ROLLBACK to Savepoint A

ROLLBACK

KINDO A AZIZE / UNIVERSITE NAZI BONI 188

 Traitement Implicite des Transactions
• Une validation automatique a lieu dans les situations suivantes :
• Exécution d'un ordre du LDD
• Exécution d'un ordre du LCD
• Sortie normale de SQL*Plus, sans ordre COMMIT ou
ROLLBACK explicite
• Il se produit un rollback automatique en cas de sortie anormale
de SQL*Plus ou d'une panne du système

KINDO A AZIZE / UNIVERSITE NAZI BONI 189

 Etat des Données Avant
COMMIT ou ROLLBACK
• Il est possible de restaurer l'état précédent des données.
• L'utilisateur courant peut afficher le résultat des opérations du LMD au
moyen de l'ordre SELECT.
• Les résultats des ordres du LMD exécutés par l'utilisateur courant ne
peuvent pas être affichés par d'autres utilisateurs.
• Les lignes concernées sont verrouillées. Aucun autre utilisateur ne peut
les modifier.

KINDO A AZIZE / UNIVERSITE NAZI BONI 190

 Etat des Données Après COMMIT
• Les modifications des données dans la base sont définitives.
• L'état précédent des données est irrémédiablement perdu.
• Tous les utilisateurs peuvent voir le résultat des modifications.
• Les lignes verrouillées sont libérées et peuvent de nouveau être
manipulées par d'autres utilisateurs.
• Tous les savepoints sont effacés.

KINDO A AZIZE / UNIVERSITE NAZI BONI 191

Validation de Données
• Effectuez les modifications.
SQL> UPDATE emp
2 SET DEPT = 10
3 WHERE Matr = 7782;
1 row updated.

• Validez les modifications.

SQL> COMMIT;
Commit complete.

KINDO A AZIZE / UNIVERSITE NAZI BONI 192

Etat des Données Après ROLLBACK
• L'ordre ROLLBACK rejette toutes les
modifications de données en instance.
• Les modifications sont annulées.
• L'état précédent des données est restauré.
• Les lignes verrouillées sont libérées.

SQL> DELETE FROM employee;

14 rows deleted.
SQL> ROLLBACK;
Rollback complete.

KINDO A AZIZE / UNIVERSITE NAZI BONI 193

 Annulation des Modifications
Jusqu'à une Etiquette
• Posez une étiquette dans la transaction courante au moyen de
l'ordre SAVEPOINT.
• Annulez la transaction jusqu'à cette étiquette en utilisant l'ordre
ROLLBACK TO SAVEPOINT.

SQL> UPDATE...
SQL> SAVEPOINT update_done;
Savepoint created.
SQL> INSERT...
SQL> ROLLBACK TO update_done;
Rollback complete.

KINDO A AZIZE / UNIVERSITE NAZI BONI 194

Rollback au Niveau Ordre
• Si un seul ordre du LMD dans la transaction échoue, seul cet ordre
est annulé.
• Oracle8 met en œuvre un savepoint implicite.
• Toutes les autres modifications sont conservées.
• L'utilisateur doit terminer explicitement les transactions en
exécutant un ordre COMMIT ou ROLLBACK.

KINDO A AZIZE / UNIVERSITE NAZI BONI 195

Lecture Cohérente
• La lecture cohérente garantit à tout moment une vue homogène
des données.
• Les modifications effectuées par un utilisateur n'entrent pas en
conflit avec celles d'un autre utilisateur.
• Sur les mêmes données, garantit que :
• la lecture ignore les écritures en cours
• l'écriture ne perturbe pas la lecture

KINDO A AZIZE / UNIVERSITE NAZI BONI 196

 Verrouillage
• Les verrous :
• Evitent les risques de destruction des données en cas de
transactions simultanées
• N'exigent aucune intervention de l'utilisateur
• S'appliquent au niveau de restriction le plus bas
• Sont actifs durant toute la transaction
• Fonctionnent en deux modes de base :
• Exclusif
• Partagé

KINDO A AZIZE / UNIVERSITE NAZI BONI 197

Résumé
Ordre Description
INSERT Ajoute une nouvelle ligne dans une table
UPDATE Modifie des lignes dans une table
DELETE Supprime des lignes d'une table
COMMIT Valide toutes les modifications de données en
instance
SAVEPOINT Permet un rollback partiel
Annule toutes les modifications de données en
ROLLBACK instance

KINDO A AZIZE / UNIVERSITE NAZI BONI 198

Traitement d’une requête SQL
SELECT nom, prenom
FROM emp
WHERE prof= ‘Enseignant’

Analyse Syntaxique
Schémas
Droits
Vérification
DD Vues
Placement, index
Optimisation CI
Statistiques
Génération d'un plan d'exécution

Forme exécutable de la requête

BD
Exécution

KINDO A AZIZE / UNIVERSITE NAZI BONI 199