Langage de requête SQL

2-ème année IMA – S4

Année universitaire : 2024-2025

Pr. LAHMIDI Ayoub Pr. MERZOUK Soukaina lahmidiay@[Link]

L'Ordre SELECT
Élémentaire
Pr. MERZOUK Soukaina 2
Objectifs
A la fin de ce chapitre, vous saurez :
▪ Énumérer toutes les possibilités de l’ordre SQL SELECT
▪ Exécuter un ordre SELECT élémentaire
▪ Faire la différence entre les ordres SQL et les
commandes SQL*Plus

Pr. MERZOUK Soukaina 3

Les Possibilités de l'Ordre SQL SELECT
Sélection Projection

Table 1 Table 1

Jointure

Table 1 Table 2
Pr. MERZOUK Soukaina
Ordre SELECT Élémentaire

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

FROM table;

▪ SELECT indique quelles colonnes rapporter

▪ FROM indique dans quelle table rechercher

mot-clé = mot isolé, clause = bloc de commande, ordre = phrase complète.

Pr. MERZOUK Soukaina 5

É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.
Pr. MERZOUK Soukaina 6
Sélection de Toutes les Colonnes

SQL> SELECT *
2 FROM dept;

DEPTNO DNAME LOC

--------- -------------- ------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Pr. MERZOUK Soukaina 7

Sélection d’Une ou Plusieurs Colonnes Spécifiques

SQL> SELECT deptno, loc

2 FROM dept;
DEPTNO LOC
--------- ------------
10 NEW YORK
20 DALLAS
30 CHICAGO
40 BOSTON

Pr. MERZOUK Soukaina 8

Valeurs par Défaut des En-têtes de Colonne

➢Justification par défaut

▪ A gauche : date et données alphanumériques
▪ A droite : données numériques
➢Affichage par défaut : en majuscules

Pr. MERZOUK Soukaina 9

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. Opérateur Description
Exemple + Addition
➢SQL> SELECT ename, sal, sal+300 - Soustraction
➢ 2 FROM emp;
* Multiplication
/ Division

Pr. MERZOUK Soukaina 10

Priorité des Opérateurs * + / -
➢La multiplication et la division ont priorité sur l’addition et la soustraction.
➢A niveau de priorité identique, les opérateurs sont évalués de gauche à
droite.
➢Les parenthèses forcent la priorité d’évaluation et permettent de clarifier les
ordres.
Exemple
SQL> SELECT ename, sal, 12*sal+100
2 FROM emp;
À faire:
SQL> SELECT ename, sal, 12*(sal+100)
2 FROM emp;
Pr. MERZOUK Soukaina 11
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.
SQL> SELECT ename, job, comm
2 FROM emp;
ENAME JOB COMM
---------- --------- ---------
KING PRESIDENT
BLAKE MANAGER
...
TURNER SALESMAN 0
...
14 rows selected.
Pr. MERZOUK Soukaina 12
Valeurs NULL dans les Expressions Arithmétiques
➢Les expressions arithmétiques comportant une valeur NULL sont
évaluées à NULL

SQL> select ename , 12*sal+comm

2 from emp
3 WHERE ename='KING';

ENAME 12*SAL+COMM
---------- -----------
KING

Pr. MERZOUK Soukaina 13

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

Pr. MERZOUK Soukaina 14

Utilisation des Alias de Colonnes
SQL> SELECT ename AS name, sal salary
2 FROM emp;

NAME SALARY
------------- ---------
...
SQL> SELECT ename "Name",
2 sal*12 "Annual Salary"
3 FROM emp;
Name Annual Salary
------------- -------------
...
Pr. MERZOUK Soukaina 15
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
SQL> SELECT ename||job AS "Employees"
2 FROM emp;
Employees
-------------------
KINGPRESIDENT
BLAKEMANAGER
CLARKMANAGER
JONESMANAGER
MARTINSALESMAN
...
14 rows selected.
Pr. MERZOUK Soukaina 16
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.
Employee Details
---------------------
KING is a PRESIDENT
SQL> SELECT ename ||' '||'is a'||' '||job BLAKE is a MANAGER
2 AS "Employee Details" CLARK is a MANAGER
3 FROM emp; JONES is a MANAGER
MARTIN is a SALESMAN
...
14 rows selected.

Pr. MERZOUK Soukaina 17

Doublons
➢Par défaut, le résultat d’une requête affiche toutes les lignes, y compris
DEPTNO
les doublons. ---------
10
SQL> SELECT deptno 30
10
2 FROM emp; 20
...
14 rows selected.

➢Pour éliminer les doublons il faut ajouter le mot-clé DISTINCT à la

clause SELECT.
DEPTNO
---------
SQL> SELECT DISTINCT deptno 10
2 FROM emp; 20
30

Pr. MERZOUK Soukaina 18

Affichage de la Structure d’une Table
➢Utilisez la commande SQL*Plus DESCRIBE pour afficher la structure
d’une table.
DESC[RIBE] tablename

Exemple
SQL> DESCRIBE dept
Name Null? Type
----------------- -------- ----
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

Pr. MERZOUK Soukaina 19

Commandes de Fichiers SQL*Plus

➢SAVE filename
➢GET filename
➢START filename
➢@ filename
➢EDIT filename : fichier [Link]
➢SPOOL filename
➢EXIT

Pr. MERZOUK Soukaina 20

Résumé

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

FROM table;

L’environnement SQL*Plus permet :

▪ D’exécuter des ordres SQL
▪ D’éditer des ordres SQL

Pr. MERZOUK Soukaina 21

Sélection et Tri des
Lignes Retournées par
un SELECT
Pr. MERZOUK Soukaina 22
Objectifs
➢A la fin de ce chapitre, vous saurez :
▪ Limiter le nombre de lignes retournées par une requête
▪ Trier les lignes retournées par une requête

Pr. MERZOUK Soukaina 23

Sélectionner les Lignes
EMP
EMPNO ENAME JOB ... DEPTNO
“…rechercher tous
7839 KING PRESIDENT 10 les employés du
7698 BLAKE MANAGER 30 département 10”
7782 CLARK MANAGER 10
7566 JONES MANAGER 20
...

EMP
EMPNO ENAME JOB ... DEPTNO

7839 KING PRESIDENT 10

7782 CLARK MANAGER 10
7934 MILLER CLERK 10

Pr. MERZOUK Soukaina 24

Sélectionner les Lignes
➢Restreindre la sélection au moyen de la clause WHERE.
SELECT [DISTINCT] {*, column [alias], ...}
FROM table
[WHERE condition(s)];

➢La clause WHERE se place après la clause FROM.

ENAME JOB DEPTNO
SQL> SELECT ename, job, deptno ---------- --------- ---------
2 FROM emp JAMES CLERK 30
3 WHERE job='CLERK'; SMITH CLERK 20
ADAMS CLERK 20
MILLER CLERK 10

Pr. MERZOUK Soukaina 25

Chaînes de Caractères et Dates
➢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'.

SQL> SELECT ename, job, deptno

2 FROM emp
3 WHERE ename = 'JAMES';

Pr. MERZOUK Soukaina 26

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

SQL> SELECT ename, sal, comm ENAME SAL COMM

2 FROM emp ---------- --------- ---------
MARTIN 1250 1400
3 WHERE sal<=comm;

Pr. MERZOUK Soukaina 27

Autres Opérateurs de Comparaison

Opérateur Signification
BETWEEN Compris entre ... et ...
...AND... (bornes comprises)
IN (liste) Correspond à une valeur de la
liste
LIKE Ressemblance partielle de
chaînes de caractères
IS NULL Correspond à une valeur NULL

Pr. MERZOUK Soukaina 28

Utilisation de l’Opérateur BETWEEN
BETWEEN permet de tester l'appartenance à une fourchette de valeurs.
SQL> SELECT ename, sal
2 FROM emp
3 WHERE sal BETWEEN 1000 AND 1500;

ENAME SAL
---------- --------- Limite Limite
MARTIN 1250
TURNER 1500 inférieure supérieure
WARD 1250
ADAMS 1100
MILLER 1300

Pr. MERZOUK Soukaina 29

Utilisation de l’Opérateur IN

➢IN permet de comparer une expression avec une liste de valeurs.

SQL> SELECT empno, ename, sal, mgr
2 FROM emp
3 WHERE mgr IN (7902, 7566, 7788);
EMPNO ENAME SAL MGR
--------- ---------- --------- ---------
7902 FORD 3000 7566
7369 SMITH 800 7902
7788 SCOTT 3000 7566
7876 ADAMS 1100 7788

Pr. MERZOUK Soukaina 30

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.
SQL> SELECT ename
▪ (%) représente zéro ou plusieurs caractères 2 FROM emp
▪ ( _ ) représente un caractère 3 WHEREename LIKE 'S%';
▪ Vous pouvez combiner plusieurs caractères génériques de recherche.
▪ Vous pouvez utiliser l’identifiant ESCAPE "\" pour rechercher "%" ou "_".
SQL> SELECT ename ENAME
----------
2 FROM emp JAMES
3 WHEREename LIKE '_A%'; WARD
Pr. MERZOUK Soukaina 31
Utilisation de l’Opérateur IS NULL
➢Recherche de valeurs NULL avec l’opérateur IS NULL

SQL> SELECT ename, mgr

2 FROM emp
3 WHERE mgr IS NULL;
ENAME MGR
---------- ---------
KING

Pr. MERZOUK Soukaina 32

Opérateurs Logiques

Opérateur Signification
AND Retourne TRUE si les deux conditions sont
VRAIES
OR Retourne TRUE si l’une au moins des conditions
est VRAIE
NOT Ramène la valeur TRUE si la condition qui suit
l’opérateur est FAUSSE

Pr. MERZOUK Soukaina 33

Utilisation de l’Opérateur AND

Avec AND, les deux conditions doivent être VRAIES.

SQL> SELECT empno, ename, job, sal
2 FROM emp
3 WHERE sal>=1100
4 AND job='CLERK';

EMPNO ENAME JOB SAL

--------- ---------- --------- ---------
7876 ADAMS CLERK 1100
7934 MILLER CLERK 1300

Pr. MERZOUK Soukaina

34
Utilisation de l’Opérateur OR
Avec OR, l'une ou l'autre des deux conditions doit être VRAIE.
SQL> SELECT empno, ename, job, sal
2 FROM emp
3 WHERE sal>=1100
4 OR job='CLERK';
EMPNO ENAME JOB SAL
--------- ---------- --------- ---------

7839 KING PRESIDENT 5000

7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
...
14 rows selected.
Pr. MERZOUK Soukaina 35
Utilisation de l’Opérateur NOT
SQL> SELECT ename, job
2 FROM emp
3 WHERE job NOT IN ('CLERK','MANAGER','ANALYST');
ENAME JOB
---------- ---------
KING PRESIDENT
MARTIN SALESMAN
ALLEN SALESMAN
TURNER SALESMAN
WARD SALESMAN

... WHERE sal NOT BETWEEN 1000 AND 1500

... WHERE ename NOT LIKE ’%A%’
... WHERE comm IS NOT NULL
Pr. MERZOUK Soukaina
36
Règles de Priorité
Opérateur Signification
1 Tous les opérateurs de comparaison
2 NOT
3 AND
4 OR

➢Les parenthèses permettent de modifier les règles de priorité

Pr. MERZOUK Soukaina 37

Règles de Priorité
SQL> SELECT ename, job, sal
2 FROM emp
3 WHERE job='SALESMAN'
4 OR job='PRESIDENT'
5 AND sal>1500;

ENAME JOB SAL

---------- --------- ---------
KING PRESIDENT 5000
MARTIN SALESMAN 1250
ALLEN SALESMAN 1600
TURNER SALESMAN 1500
WARD SALESMAN 1250
Pr. MERZOUK Soukaina 38
Règles de Priorité
Utilisation de parenthèses pour forcer la priorité.

SQL> SELECT ename, job, sal

2 FROM emp
3 WHERE (job='SALESMAN'
4 OR job='PRESIDENT')
5 AND sal>1500;
ENAME JOB SAL
---------- --------- ---------
KING PRESIDENT 5000
ALLEN SALESMAN 1600
Pr. MERZOUK Soukaina 39
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 ename, job, deptno, hiredate
2 FROM emp
3 ORDER BY hiredate;
ENAME JOB DEPTNO HIREDATE
---------- --------- --------- ---------
SMITH CLERK 20 17-DEC-80
ALLEN SALESMAN 30 20-FEB-81
...
14 rows selected.

Pr. MERZOUK Soukaina 40

Tri par Ordre Décroissant
SQL> SELECT ename, job, deptno, hiredate
2 FROM emp
3 ORDER BY hiredate DESC;

ENAME JOB DEPTNO HIREDATE

---------- --------- --------- ---------
ADAMS CLERK 20 12-JAN-83
SCOTT ANALYST 20 09-DEC-82
MILLER CLERK 10 23-JAN-82
JAMES CLERK 30 03-DEC-81
FORD ANALYST 20 03-DEC-81
KING PRESIDENT 10 17-NOV-81
MARTIN SALESMAN 30 28-SEP-81
...
14 rows selected.
Pr. MERZOUK Soukaina
41
Tri sur l’Alias de Colonne
SQL> SELECT empno, ename, sal*12 annsal
2 FROM emp
3 ORDER BY annsal;

EMPNO ENAME ANNSAL

--------- ---------- ---------
7369 SMITH 9600
7900 JAMES 11400
7876 ADAMS 13200
7654 MARTIN 15000
7521 WARD 15000
7934 MILLER 15600
7844 TURNER 18000
...
14 rows selected.
Pr. MERZOUK Soukaina
42
Tri sur Plusieurs Colonnes
➢L’ordre des éléments de la liste ORDER BY donne l’ordre du tri.
SQL> SELECT ename, deptno, sal
2 FROM emp
3 ORDER BY deptno, sal DESC;

ENAME DEPTNO SAL

---------- --------- ---------
KING 10 5000
CLARK 10 2450
MILLER 10 1300
FORD 20 3000
...
14 rows selected.

➢Vous pouvez effectuer un tri sur une colonne ne figurant pas dans la liste SELECT.

Pr. MERZOUK Soukaina 43

Résumé

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

FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];

Pr. MERZOUK Soukaina

44
Fonctions Mono-
Ligne
Pr. MERZOUK Soukaina 45
Objectifs

➢A la fin de ce chapitre, 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

Pr. MERZOUK Soukaina 46

Qu’est ce qu’une fonction ?
➢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 La fonction
exécute une
arg 2 action Valeur
résultante

arg n

Pr. MERZOUK Soukaina 47

Deux Types de Fonctions SQL

Fonctions

Fonctions Fonctions
mono-ligne multi-ligne

Pr. MERZOUK Soukaina 48

Fonctions Mono-Ligne
➢Manipulent des éléments de données
➢Acceptent des arguments et ramènent une valeur
➢Agissent sur chacune des lignes rapportées
➢Ramènent un seul résultat par ligne
➢Peuvent modifier les types de données
➢Peuvent être imbriquées

function_name (column|expression, [arg1, arg2,...])

Pr. MERZOUK Soukaina 49

Fonctions Mono-Ligne

Caractère

Générale Numérique
Fonctions
mono-ligne

Conversion Date

Pr. MERZOUK Soukaina 50

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 ...

Pr. MERZOUK Soukaina 51

Fonctions de Conversion Majuscules/Minuscules

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

Pr. MERZOUK Soukaina 52

Utilisation des Fonctions de Conversion
Majuscules/Minuscules
Afficher le matricule, le nom et le numéro de département de l’employé Blake.
SQL> SELECT empno, ename, deptno
2 FROM emp
3 WHERE ename = 'blake’;
no rows selected

SQL> SELECT empno, ename, deptno

2 FROM emp
3 WHERE LOWER(ename) = 'blake';

EMPNO ENAME DEPTNO

--------- ---------- ---------
7698 BLAKE 30
Pr. MERZOUK Soukaina 53
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

Pr. MERZOUK Soukaina 54

Utilisation des Fonctions de Manipulation des Caractères

SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename),

2 INSTR(ename, 'A')
3 FROM emp
4 WHERE SUBSTR(job,1,5) = 'SALES';

ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')

---------- ------------------- ------------- ----------------
MARTIN MARTINSALESMAN 6 2
ALLEN ALLENSALESMAN 5 1
TURNER TURNERSALESMAN 6 0
WARD WARDSALESMAN 4 2

Pr. MERZOUK Soukaina 55

Fonctions Numériques
➢ROUND : Arrondit la valeur à la précision spécifiée
ROUND(45.926, 2) 45.93

➢TRUNC : Tronque la valeur à la précision spécifiée

TRUNC(45.926, 2) 45.92

➢MOD : Ramène le reste d’une division

MOD(1600,300) 100

Pr. MERZOUK Soukaina 56

Utilisation de la Fonction ROUND
➢Affichage de la valeur 45.923 arrondie au centième, à 0 décimale et à
la dizaine supérieure.

SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),

2 ROUND(45.923,-1)
3 FROM DUAL;

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)

--------------- -------------- -----------------
45.92 46 50

Pr. MERZOUK Soukaina 57

Utilisation de la Fonction TRUNC
➢Affichage de la valeur 45.923 tronquée au centième, à 0 décimale et
à la dizaine.

SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),

2 TRUNC(45.923,-1)
3 FROM DUAL;

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)

--------------- ------------- ---------------
45.92 45 40

Pr. MERZOUK Soukaina 58

Utilisation de la Fonction MOD

➢Calculer le reste de la division salaire par commission pour l’ensemble

des employés ayant un poste de vendeur.
SQL> SELECT ename, sal, comm, MOD(sal, comm)
2 FROM emp
3 WHERE job = 'SALESMAN';

ENAME SAL COMM MOD(SAL,COMM)

---------- --------- --------- -------------
MARTIN 1250 1400 1250
ALLEN 1600 300 100
TURNER 1500 0 1500
WARD 1250 500 250

Pr. MERZOUK Soukaina 59

Autres Fonctions Numériques

➢ABS(x) : Valeur absolue de x

➢CEIL(n) : Plus petit entier supérieur ou égal à n.

➢SIGN(n) : Si n<0, -1; si n=0, 0; si n>0, 1.

➢FLOOR(n) : Plus grand entier supérieur ou égal à n.

Pr. MERZOUK Soukaina 60

Utilisation des Dates

➢Oracle stocke les dates dans un format numérique interne : siècle,

année, mois, jour, heures, minutes, secondes.
➢Le format de date par défaut est DD-MON-YY.
➢La fonction SYSDATE ramène la date et l’heure courante.
➢DUAL est une table factice qu'on peut utiliser pour visualiser
SYSDATE.

Pr. MERZOUK Soukaina 61

Opérations Arithmétiques sur les Dates

➢Ajout ou soustraction d’un nombre à une date pour obtenir un

résultat de type date.
➢Soustraction de deux dates afin de déterminer le nombre de jours
entre ces deux dates.
➢Ajout d’un nombre d’heures à une date en divisant le nombre
d’heures par 24.

Pr. MERZOUK Soukaina 62

Utilisation d’Opérateurs Arithmétiques avec les Dates

SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS

2 FROM emp
3 WHERE deptno = 10;

ENAME WEEKS
---------- ---------
KING 830.93709
CLARK 853.93709
MILLER 821.36566

Pr. MERZOUK Soukaina 63

Fonctions Date
FONCTION DESCRIPTION
MONTHS_BETWEEN(d1,d2) Nombre de mois situés entre deux dates

ADD_MONTHS(date, n) Ajoute des mois calendaires à une date

NEXT_DAY(date,’char’) Jour qui suit la date spécifiée

LAST_DAY(date) Dernier jour du mois

ROUND(date [,’fmt’] ) Arrondit une date

TRUNC (date [,’fmt’] ) Tronque une date

Pr. MERZOUK Soukaina 64

Utilisation des Fonctions Date

➢MONTHS_BETWEEN ('01-SEP-95','11-JAN-94’) 19.6774194

➢ADD_MONTHS ('11-JAN-94’,6) '11-JUL-94'
➢NEXT_DAY ('01-SEP-95','FRIDAY’) '08-SEP-95'
➢LAST_DAY('01-SEP-95’) '30-SEP-95'

Pr. MERZOUK Soukaina 65

Fonctions de Conversion

Conversion
de types
de données

Conversion Conversion
de types de types
de données de données
implicite explicite

Pr. MERZOUK Soukaina 66

Conversion de Types de Données Implicite

➢Pour les affectations, Oracle effectue automatiquement les

conversions suivantes
De Vers
VARCHAR2 ou CHAR NUMBER
VARCHAR2 ou CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2

Pr. MERZOUK Soukaina 67

 Conversion de Types de Données Implicite
➢Pour l’évaluation d’expressions, Oracle effectue automatiquement les
conversions suivantes
De Vers
VARCHAR2 ou CHAR NUMBER
VARCHAR2 ou CHAR DATE

TO_NUMBER TO_DATE

NUMBER CHARACTER DATE

TO_CHAR TO_CHAR
Pr. MERZOUK Soukaina 68
Utilisation de la Fonction TO_CHAR avec les Dates
TO_CHAR(date, 'fmt')

➢Le modèle de format :

▪ Doit être placé entre simples quotes et différencie les majuscules et
minuscules.
▪ Peut inclure tout élément valide de format date
▪ Comporte un élément fm qui supprime les espaces de remplissage ou les
zéros de tête
▪ Est séparé de la valeur date par une virgule
Pr. MERZOUK Soukaina 69
Modèles de Format Date

YYYY Année exprimée avec 4 chiffres

YEAR Année exprimée en toutes lettres

MM Mois exprimé avec 2 chiffres

MONTH Mois exprimé en toutes lettres

3 premières lettres du nom du jour
DY

DAY Jour exprimé en toutes lettres

Pr. MERZOUK Soukaina 70

Modèles de Format pour les Dates
➢Les éléments horaires formatent la partie horaire de la date.

HH24:MI:SS AM 15:45:32 PM

➢Pour ajouter des chaînes de caractères, les placer entre guillemets.

DD "of" MONTH 12 of OCTOBER

➢Différents suffixes existent pour les nombres.

ddspth fourteenth

Pr. MERZOUK Soukaina 71

Utilisation de la Fonction TO_CHAR avec les Dates

SQL> SELECT ename,

2 TO_CHAR(hiredate,'fmDD Month YYYY') HIREDATE
3 FROM emp;

ENAME HIREDATE
---------- -----------------
KING 17 November 1981
BLAKE 1 May 1981
CLARK 9 June 1981
JONES 2 April 1981
MARTIN 28 September 1981
ALLEN 20 February 1981
...
14 rows selected.

Pr. MERZOUK Soukaina

Utilisation de la Fonction TO_CHAR avec les Nombres

TO_CHAR(number, 'fmt')

➢Utilisez les formats suivants avec TO_CHAR pour afficher un nombre sous la
forme d'une chaîne de caractère.

9 Représente un chiffre
0 Force l’affichage du zéro
$ Place un signe dollar flottant
L Utilise le symbole monétaire local flottant
. Imprime un point décimal
, Imprime un séparateur de milliers

Pr. MERZOUK Soukaina 73

Utilisation de la Fonction TO_CHAR avec les Nombres

SQL> SELECT TO_CHAR(sal,'$99,999') SALARY

2 FROM emp
3 WHERE ename = 'SCOTT';

SALARY
--------
$3,000

Pr. MERZOUK Soukaina 74

 Fonctions TO_NUMBER et TO_DATE
➢Conversion d’une chaîne de caractères en format numérique avec la
fonction TO_NUMBER

TO_NUMBER(char)

• Conversion d’une chaîne de caractères en format date avec la fonction

TO_DATE

TO_DATE(char[, 'fmt'])

Pr. MERZOUK Soukaina 75

Fonction NVL
➢Convertit une valeur NULL en une valeur réelle
▪ Fonctionne avec les données de type date, caractère et
numérique.
▪ Les types de données doivent correspondre
• NVL(comm,0)
• NVL(hiredate,'01-JAN-97')
• NVL(job,'No Job Yet')

Pr. MERZOUK Soukaina 76

Utilisation de la Fonction NVL

SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0)

2 FROM emp;

ENAME SAL COMM (SAL*12)+NVL(COMM,0)

---------- --------- --------- --------------------
KING 5000 60000
BLAKE 2850 34200
CLARK 2450 29400
JONES 2975 35700
MARTIN 1250 1400 16400
ALLEN 1600 300 19500
...
14 rows selected.

Pr. MERZOUK Soukaina 77

Fonction DECODE

➢Facilite les recherches conditionnelles en jouant le rôle de CASE ou

IF-THEN-ELSE

DECODE(col/expression, search1, result1

[, search2, result2,...,]
[, default])

Pr. MERZOUK Soukaina 78

Utilisation de la Fonction DECODE
SQL> SELECT job, sal,
2 DECODE(job, 'ANALYST', SAL*1.1,
3 'CLERK', SAL*1.15,
4 'MANAGER', SAL*1.20,
5 SAL)
6 REVISED_SALARY
7 FROM emp;

JOB SAL REVISED_SALARY

--------- --------- --------------
PRESIDENT 5000 5000
MANAGER 2850 3420
MANAGER 2450 2940
...
14 rows selected.
Pr. MERZOUK Soukaina 79
Imbrication des Fonctions
➢Le niveau d’imbrication des fonctions mono-ligne est illimité
➢Les fonctions imbriquées sont évaluées de l'intérieur vers l'extérieur

F3(F2(F1(col,arg1),arg2),arg3)

Etape 1 = Résultat 1
Etape 2 = Résultat 2
Etape 3 = Résultat 3

Pr. MERZOUK Soukaina 80

Imbrication des Fonctions

SQL> SELECT ename,

2 NVL(TO_CHAR(mgr),'No Manager')
3 FROM emp
4 WHERE mgr IS NULL;

ENAME NVL(TO_CHAR(MGR),'NOMANAGER')
---------- -----------------------------
KING No Manager

Pr. MERZOUK Soukaina 81

Résumé

➢Utilisez des fonctions mono-ligne pour :

➢Transformer des données
➢Formater des dates et des nombres pour l'affichage
➢Convertir des types de données de colonnes

Pr. MERZOUK Soukaina 82

Afficher des Données
Issues de Plusieurs
Tables
Pr. MERZOUK Soukaina 83
Objectifs

➢A la fin de ce chapitre, vous saurez :

▪ Ecrire des ordres SELECT pour accéder aux données de plusieurs
tables en utilisant des équijointures et des non-équijointures
▪ Visualiser des données ne répondant pas aux conditions de jointure,
en utilisant les jointures externes
▪ Relier une table à elle-même

Pr. MERZOUK Soukaina 84

Afficher des Données Issues de Plusieurs Tables
EMP DEPT
EMPNO ENAME ... DEPTNO DEPTNO DNAME LOC
------ ----- ... ------ ------ ---------- --------
7839 KING ... 10 10 ACCOUNTING NEW YORK
7698 BLAKE ... 30 20 RESEARCH DALLAS
... 30 SALES CHICAGO
7934 MILLER ... 10 40 OPERATIONS BOSTON

EMPNO DEPTNO LOC

----- ------- --------
7839 10 NEW YORK
7698 30 CHICAGO
7782 10 NEW YORK
7566 20 DALLAS
7654 30 CHICAGO
7499 30 CHICAGO
...
14 rows selected.

Pr. MERZOUK Soukaina 85

Qu'est-ce qu'une Jointure ?
➢Une jointure sert à extraire des données de plusieurs tables.

SELECT [Link], [Link]

FROM table1, table2
WHERE table1.column1 = table2.column2;

❑Ecrivez la condition de jointure dans la clause WHERE.

❑Placez le nom de la table avant le nom de la colonne lorsque celui-ci
figure dans plusieurs tables.

Pr. MERZOUK Soukaina 86

Produit Cartésien
➢On obtient un produit cartésien lorsque :
▪ Une condition de jointure est omise
▪ Une condition de jointure est incorrecte
➢Toutes les lignes de la première table sont jointes à toutes les lignes
de la seconde
➢Pour éviter un produit cartésien, toujours insérer une condition de
jointure correcte dans la clause WHERE.

Pr. MERZOUK Soukaina 87

Génération d'un Produit Cartésien
EMP (14 lignes) DEPT (4 lignes)
EMPNO ENAME ... DEPTNO DEPTNO DNAME LOC
------ ----- ... ------ ------ ---------- --------
7839 KING ... 10 10 ACCOUNTING NEW YORK
7698 BLAKE ... 30 20 RESEARCH DALLAS
... 30 SALES CHICAGO
7934 MILLER ... 10 40 OPERATIONS BOSTON

ENAME DNAME
------ ----------
KING ACCOUNTING
"Produit cartésien : BLAKE ACCOUNTING
14*4=56 lignes" ...
KING RESEARCH
BLAKE RESEARCH
...
56 rows selected.
Pr. MERZOUK Soukaina 88
Types de Jointures

Equijointure Non-équijointure

Jointure externe Autojointure

Pr. MERZOUK Soukaina

Qu'est-ce qu'une Equijointure ?
EMP DEPT
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
------ ------- ------- ------- ---------- --------
7839 KING 10 10 ACCOUNTING NEW YORK
7698 BLAKE 30 30 SALES CHICAGO
7782 CLARK 10 10 ACCOUNTING NEW YORK
7566 JONES 20 20 RESEARCH DALLAS
7654 MARTIN 30 30 SALES CHICAGO
7499 ALLEN 30 30 SALES CHICAGO
7844 TURNER 30 30 SALES CHICAGO
7900 JAMES 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7902 FORD 20 20 RESEARCH DALLAS
7369 SMITH 20 20 RESEARCH DALLAS
... ...
14 rows selected. 14 rows selected.

Clé étrangère Clé primaire

Pr. MERZOUK Soukaina 90
Extraction d'Enregistrements avec les Equijointures
SQL> SELECT [Link], [Link], [Link],
2 [Link], [Link]
3 FROM emp, dept
4 WHERE [Link]=[Link];

EMPNO ENAME DEPTNO DEPTNO LOC

----- ------ ------ ------ ---------
7839 KING 10 10 NEW YORK
7698 BLAKE 30 30 CHICAGO
7782 CLARK 10 10 NEW YORK
7566 JONES 20 20 DALLAS
...
14 rows selected.

Pr. MERZOUK Soukaina 91

Différencier les Noms de Colonne Ambigus

➢Préfixer avec le nom de la table pour différencier les noms de

colonnes appartenant à plusieurs tables.
➢Ces préfixes de table améliorent les performances.
➢Différencier des colonnes de même nom appartenant à plusieurs
tables en utilisant des alias de colonne.

Pr. MERZOUK Soukaina 92

Ajout de Conditions de Recherche avec l'Opérateur AND
EMP DEPT
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
------ ------- ------- ------ --------- --------
7839 KING 10 10 ACCOUNTING NEW YORK
7698 BLAKE 30 30 SALES CHICAGO
7782 CLARK 10 10 ACCOUNTING NEW YORK
7566 JONES 20 20 RESEARCH DALLAS
7654 MARTIN 30 30 SALES CHICAGO
7499 ALLEN 30 30 SALES CHICAGO
7844 TURNER 30 30 SALES CHICAGO
7900 JAMES 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7902 FORD 20 20 RESEARCH DALLAS
7369 SMITH 20 20 RESEARCH DALLAS
... ...
14 rows selected. 14 rows selected.
Pr. MERZOUK Soukaina
93
Utilisation d'Alias de Table
➢Simplifiez les requêtes avec les alias de table.

SQL> SELECT [Link], [Link], [Link],

2 [Link], [Link]
3 FROM emp, dept
4 WHERE [Link]=[Link];

SQL> SELECT [Link], [Link], [Link],

2 [Link], [Link]
3 FROM emp e, dept d
4 WHERE [Link]=[Link];

Pr. MERZOUK Soukaina 94

Non-Equijointures
EMP SALGRADE
EMPNO ENAME SAL GRADE LOSAL HISAL
------ ------- ------ ----- ----- ------
7839 KING 5000 1 700 1200
7698 BLAKE 2850 2 1201 1400
7782 CLARK 2450 3 1401 2000
7566 JONES 2975 4 2001 3000
7654 MARTIN 1250 5 3001 9999
7499 ALLEN 1600
7844 TURNER 1500
7900 JAMES 950
... "Les salaires (SAL) de la table
14 rows selected. EMP sont compris entre le
salaire minimum (LOSAL) et le
salaire maximum (HISAL) de la
table SALGRADE"
Pr. MERZOUK Soukaina 95
Extraction d'Enregistrements avec les Non-Equijointures

SQL> SELECT [Link], [Link], [Link]

2FROM emp e, salgrade s
3WHERE [Link]
4BETWEEN [Link] AND [Link];

ENAME SAL GRADE

---------- --------- ---------
JAMES 950 1
SMITH 800 1
ADAMS 1100 1
...
14 rows selected.

Pr. MERZOUK Soukaina 96

Jointures Externes
EMP DEPT
ENAME DEPTNO DEPTNO DNAME
----- ------ ------ ----------
KING 10 10 ACCOUNTING
BLAKE 30 30 SALES
CLARK 10 10 ACCOUNTING
JONES 20 20 RESEARCH
... ...
40 OPERATIONS

Pas d'employés dans le

département OPERATIONS

Pr. MERZOUK Soukaina 97

Jointures Externes
➢Les jointures externes permettent de visualiser des lignes qui ne
répondent pas à la condition de jointure.
➢L'opérateur de jointure externe est le signe (+).
SELECT [Link], [Link]
FROM table1, table2
WHERE [Link](+) = [Link];

SELECT [Link], [Link]

FROM table1, table2
WHERE [Link] = [Link](+);
Pr. MERZOUK Soukaina 98
Utilisation des Jointures Externes
SQL> SELECT [Link], [Link], [Link]
2 FROM emp e, dept d
3 WHERE [Link](+) = [Link]
4 ORDER BY [Link];

ENAME DEPTNO DNAME

---------- --------- -------------
KING 10 ACCOUNTING
CLARK 10 ACCOUNTING
...
40 OPERATIONS
15 rows selected.

Pr. MERZOUK Soukaina 99

Autojointures
EMP (WORKER) EMP (MANAGER)
EMPNO ENAME MGR EMPNO ENAME
----- ------ ---- ----- --------
7839 KING
7698 BLAKE 7839 7839 KING
7782 CLARK 7839 7839 KING
7566 JONES 7839 7839 KING
7654 MARTIN 7698 7698 BLAKE
7499 ALLEN 7698 7698 BLAKE

"Dans la table WORKER, MGR équivaut à

EMPNO dans la table MANAGER"

Pr. MERZOUK Soukaina 100

Liaison d'une Table à Elle-même
SQL> SELECT [Link]||' works for '||[Link]
2 FROM emp worker, emp manager
3 WHERE [Link] = [Link];

[Link]||'WORKSFOR'||MANAG
-------------------------------
BLAKE works for KING
CLARK works for KING
JONES works for KING
MARTIN works for BLAKE
...
13 rows selected.

Pr. MERZOUK Soukaina 101

INNER JOIN
SELECT *
FROM table1
INNER JOIN table2
ON [Link] = table2.fk_id

La syntaxe ci-dessus stipule qu’il faut sélectionner les enregistrements

des tables table1 et table2 lorsque les données de la colonne “id” de
table1 est égal aux données de la colonne fk_id de table2.

Pr. MERZOUK Soukaina

102
INNER JOIN
SELECT ename,sal, Dname, Loc
FROM emp
INNER JOIN dept
ON [Link] = [Link]

Pr. MERZOUK Soukaina

103
Résumé
SELECT [Link], [Link]
FROM table1, table2
WHERE table1.column1 = table2.column2;
Equijointure Non-équijointure

Jointure externe Autojointure

Pr. MERZOUK Soukaina

104
Regrouper les Données
avec les Fonctions de
Groupe
Pr. MERZOUK Soukaina 10
5
Objectifs

➢A la fin de ce chapitre, 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

Pr. MERZOUK Soukaina 106

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

Pr. MERZOUK Soukaina 107

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)

Pr. MERZOUK Soukaina 108

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 job LIKE 'SALES%';
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)
-------- --------- --------- ---------
1400 1600 1250 5600

➢MIN et MAX s'utilisent avec tous types de données.

Pr. MERZOUK Soukaina 109

Utilisation de la Fonction COUNT
➢COUNT(*) ramène le nombre de lignes d'une table.

SQL> SELECT COUNT(*) COUNT(*)

2 FROM emp ---------
3 WHERE deptno = 30; 6

➢COUNT(expr) ramène le nombre de lignes non NULL.

SQL> SELECT COUNT(comm) COUNT(COMM)

2 FROM emp -----------
3 WHERE deptno = 30; 4

Pr. MERZOUK Soukaina 110

Fonctions de Groupe et Valeurs NULL
➢Les fonctions de groupe, à l'exception de COUNT (*), ignorent les
valeurs NULL des colonnes.
SQL> SELECT AVG(comm)
2 FROM emp;

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

Pr. MERZOUK Soukaina 111

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

Pr. MERZOUK Soukaina 112

Création de Groupes de Données
EMP DEPTNO SAL
--------- ---------
10 2450
10 5000 2916.6667
10 1300 DEPTNO AVG(SAL)
20 800
2175 ------- ---------
20 1100 "salaire
20 3000 moyen pour chaque 10 2916.6667
20 3000
département 20 2175
20 2975
30 1600 de la table EMP" 30 1566.6667
30 2850
30 1250
30 950 1566.6667
30 1500
30 1250
Pr. MERZOUK Soukaina 113
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.

Pr. MERZOUK Soukaina 114

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 deptno, AVG(sal)
2 FROM emp
3 GROUP BY deptno;
DEPTNO AVG(SAL)
--------- ---------
10 2916.6667
20 2175
30 1566.6667
Pr. MERZOUK Soukaina 115
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 deptno;

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

Pr. MERZOUK Soukaina 116

Regroupement sur Plusieurs Colonnes
EMP
DEPTNO JOB SAL
--------- --------- ---------
10 MANAGER 2450
DEPTNO JOB 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 poste,
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

Pr. MERZOUK Soukaina 117

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

DEPTNO JOB SUM(SAL)

--------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
...
9 rows selected.
Pr. MERZOUK Soukaina 118
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 deptno, COUNT(ename)

2 FROM emp;

SELECT deptno, COUNT(ename)

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

Pr. MERZOUK Soukaina 119

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 deptno, AVG(sal)
2 FROM emp
3 WHERE AVG(sal) > 2000
4 GROUP BY deptno;

WHERE AVG(sal) > 2000

*
ERROR at line 3:
ORA-00934: group function
Pr. MERZOUK Soukaina is not allowed here 120
Exclusion de Groupes
EMP
DEPTNO SAL
--------- ---------
10 2450
10 5000 5000
10 1300
20 800
20 1100 "salaire maximum DEPTNO 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

Pr. MERZOUK Soukaina 121

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

Pr. MERZOUK Soukaina 122

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

DEPTNO MAX(SAL)
--------- ---------
10 5000
20 3000

Pr. MERZOUK Soukaina 123

Utilisation de la Clause HAVING
SQL> SELECT job, SUM(sal) PAYROLL
2 FROM emp
3 WHERE job NOT LIKE 'SALES%'
3 GROUP BY job
4 HAVING SUM(sal)>5000
5 ORDER BY SUM(sal);

JOB PAYROLL
--------- ---------
ANALYST 6000
MANAGER 8275
Pr. MERZOUK Soukaina
Imbrication des Fonctions de Groupe
➢Afficher le salaire moyen maximum.

SQL> SELECT max(avg(sal))

2 FROM emp
3 GROUP BY deptno;

MAX(AVG(SAL))
-------------
2916.6667

Pr. MERZOUK Soukaina 125

Résumé

SELECT column, group_function

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

Pr. MERZOUK Soukaina 126

Sous-Interrogations
Pr. MERZOUK Soukaina 12
7
Objectifs

➢A la fin de ce chapitre, vous saurez :

➢Décrire les types de problèmes que les sous-interrogations
peuvent résoudre
➢Définir des sous-interrogations
➢Enumérer les types de sous-interrogations
➢Ecrire des sous-interrogations mono-ligne et multi-ligne

Pr. MERZOUK Soukaina 128

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
Pr. MERZOUK Soukaina 129
Utilisation d'une Sous-Interrogation pour Résoudre un
Problème
"Qui a un salaire supérieur à celui de Jones ?"
Requête principale
"Quel employé a un salaire supérieur à
?
celui de Jones ?"

sous-interrogation
?
"Quel est le salaire de Jones ?"

Pr. MERZOUK Soukaina 130

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).
Pr. MERZOUK Soukaina 131
Utilisation d'une Sous-Interrogation
SQL> SELECT ename
2 FROM emp
3 WHERE sal > 2975
4 (SELECT sal
5 FROM emp
6 WHERE empno=7566);

ENAME
----------
KING
FORD
SCOTT
Pr. MERZOUK Soukaina 132
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 multiligne avec les sous-interrogations
multiligne.

Pr. MERZOUK Soukaina 133

Types de Sous-Interrogations
➢Sous-interrogation mono-ligne
Requête principale
ramène
sous-interrogation CLERK

➢Sous-interrogation multi-ligne
Requête principale
ramène CLERK
sous-interrogation MANAGER
➢Sous-interrogation multi-colonne
Requête principale
ramène CLERK 7900
sous-interrogation MANAGER 7698
Pr. MERZOUK Soukaina 134
Sous-Interrogations Mono-ligne
➢Ne ramènent qu'une seule ligne
➢Utilisent des opérateurs de comparaison mono-ligne
➢Peuvent faire appel à une fonction de groupe
Opérateur Signification
= Egal à
> Supérieur à
>= Supérieur ou égal à
< Inférieur à
<= Inférieur ou égal à
<> Différent de
Pr. MERZOUK Soukaina 135
Exécution de Sous-Interrogations Mono-ligne
SQL> SELECT ename, job
2 FROM emp
3 WHERE job = CLERK
4 (SELECT job
5 FROM emp
6 WHERE empno = 7369)
7 AND sal > 1100
8 (SELECT sal
9 FROM emp
10 WHERE empno = 7876);
ENAME JOB
---------- ---------
MILLER CLERK
Pr. MERZOUK Soukaina 136
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 deptno, MIN(sal)
2 FROM emp
3 GROUP BY deptno
800
4 HAVING MIN(sal) >
5 (SELECT MIN(sal)
6 FROM emp
7 WHERE deptno = 20);
Pr. MERZOUK Soukaina 137
Exemple
➢Trouver le poste ayant le salaire moyen le moins élevé .

SELECT job, AVG(sal)

FROM emp
GROUP BY job
HAVING AVG(sal) =
( SELECT MIN(AVG(sal))
FROM emp
GROUP By job );

Pr. MERZOUK Soukaina 138

Qu'est-ce Qui ne Va pas dans cet Ordre ?
SQL> SELECT empno, ename
2 FROM emp
3 WHERE sal =
4 (SELECT MIN(sal)
5 FROM emp
6 GROUP BY deptno);
ERROR:
ORA-01427: single-row sub-query returns more than
one row

no rows selected
Pr. MERZOUK Soukaina 139
Cet Ordre Va-t-il Fonctionner ?

SQL> SELECT ename, job

2 FROM emp
3 WHERE job =
4 (SELECT job
5 FROM emp
6 WHERE ename='SMYTHE');

no rows selected

Pr. MERZOUK Soukaina 140

Sous-Interrogation Multiligne
➢Ramène plusieurs lignes
➢Utilise des opérateurs de comparaison multiligne

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

Pr. MERZOUK Soukaina 141

Utilisation de l'Opérateur ANY dans les Sous-
Interrogations Multiligne
SQL> SELECT empno, ename, job
2 FROM emp 1300
1100
800
3 WHERE sal < ANY 950
4 (SELECT sal
5 FROM emp
6 WHERE job = 'CLERK')
7 AND job <> 'CLERK';
▪ <ANY signifie inférieur à au moins une des
EMPNO ENAME JOB valeurs, donc inférieur au maximum.
------- -------- ---------
7654 MARTIN SALESMAN
▪ >ANY signifie supérieur à au moins une des
7521 WARD SALESMAN valeurs, donc supérieur au minimum.
▪ =ANY équivaut à IN.
Pr. MERZOUK Soukaina 142
Utilisation de l'Opérateur ALL dans les Sous-
Interrogations Multi-ligne
SQL> SELECT empno, ename, job
2 FROM emp 2175
1566.6667

3 WHERE sal > ALL 2916.6667

4 (SELECT avg(sal)
5 FROM emp
6 GROUP BY deptno)

EMPNO ENAME JOB

--------- ---------- ---------
7839 KING PRESIDENT ▪ >ALL signifie supérieur au maximum
7566 JONES MANAGER ▪ <ALL signifie inférieur au minimum.
7902 FORD ANALYST
7788 SCOTT ANALYST

Pr. MERZOUK Soukaina

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

Pr. MERZOUK Soukaina 144

Manipulation des
Données
Pr. MERZOUK Soukaina 14
5
Objectifs

➢A la fin de ce chapitre, 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

Pr. MERZOUK Soukaina 146

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.

Pr. MERZOUK Soukaina 147

Ajout d'une Nouvelle Ligne dans une Table
50 DEVELOPMENT DETROIT
Nouvelle ligne "…insérer une nouvelle ligne
dans la table DEPT …"
DEPTNO DNAME LOC
------ ---------- -------- DEPT
10 ACCOUNTING NEW YORK DEPTNO DNAME LOC
20 RESEARCH DALLAS ------ ---------- --------
30 SALES CHICAGO 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON 20 RESEARCH DALLAS
30 SALES CHICAGO
DEPT
40 OPERATIONS BOSTON
50 DEVELOPMENT DETROIT
Pr. MERZOUK Soukaina 148
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.

Pr. MERZOUK Soukaina 149

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 (deptno, dname, loc)
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.
Pr. MERZOUK Soukaina 150
Insertion de Lignes Contenant des Valeurs NULL
➢Méthode implicite : ne spécifiez pas la colonne dans la liste.
SQL> INSERT INTO dept (deptno, 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.

Pr. MERZOUK Soukaina 151

Insertion de Valeurs Spéciales

➢La fonction SYSDATE renvoie la date et l'heure courantes.

SQL> INSERT INTO emp (empno, ename, job,

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

Pr. MERZOUK Soukaina 152

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.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- -------- ---- --------- ---- ----- -----
2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10

Pr. MERZOUK Soukaina 153

Copie de Lignes d'une Autre Table
➢Ecrivez votre ordre INSERT en spécifiant une sous-interrogation.

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

2 SELECT empno, ename, sal, hiredate
3 FROM emp
4 WHEREjob = '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.

Pr. MERZOUK Soukaina 154

Modification des Données d'une Table
EMP
EMPNO ENAME JOB ... DEPTNO
"…modifier une
ligne
7839 KING PRESIDENT 10
7698 BLAKE MANAGER 30 de la table EMP…"
7782 CLARK MANAGER 10
7566 JONES MANAGER 20
...

EMP
EMPNO ENAME JOB ... DEPTNO

7839 KING PRESIDENT 10

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

Pr. MERZOUK Soukaina 155

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.

Pr. MERZOUK Soukaina 156

Modification de Lignes d'une Table

➢La clause WHERE permet de modifier une ou plusieurs lignes

spécifiques. SQL> UPDATE emp
2 SET deptno = 20
3 WHERE empno = 7782;
1 row updated.

➢Si vous omettez la clause WHERE, toutes les lignes sont modifiées.
SQL> UPDATE employee
2 SET deptno = 20;
14 rows updated.
Pr. MERZOUK Soukaina 157
Modification avec une Sous-Interrogation Multi-colonne

➢Modifier le poste et le n° de département de l'employé 7698 à

l'identique de l'employé 7499.

SQL> UPDATE emp

2 SET (job, deptno) =
3 (SELECT job, deptno
4 FROM emp
5 WHERE empno = 7499)
6 WHERE empno = 7698;
1 row updated.

Pr. MERZOUK Soukaina 158

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 deptno = (SELECT deptno
3 FROM emp
4 WHERE empno = 7788)
5 WHERE job = (SELECT job
6 FROM emp
7 WHERE empno = 7788);
2 rows updated.
Pr. MERZOUK Soukaina 159
Modification de Lignes : Erreur de Contrainte d'Intégrité

SQL> UPDATE emp

2 SET deptno = 55
3 WHERE deptno = 10;

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

Pr. MERZOUK Soukaina 160

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

Pr. MERZOUK Soukaina 161

L'Ordre DELETE

➢Vous pouvez supprimer des lignes d'une table au moyen de l'ordre

DELETE.

DELETE [FROM] table

[WHERE condition];

Pr. MERZOUK Soukaina 162

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.

Pr. MERZOUK Soukaina 163

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 deptno =
3 (SELECT deptno
4 FROM dept
5 WHERE dname ='SALES');
6 rows deleted.

Pr. MERZOUK Soukaina 164

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

SQL> DELETE FROM dept

2 WHERE deptno = 10;

DELETE FROM dept

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

Pr. MERZOUK Soukaina 165

Transactions de Base de Données
➢Une transaction est une séquence d’opérations de lecture ou
d’écriture, se terminant par commit ou rollback.

❖Le commit est une instruction qui valide toutes les mises à jour.

❖Le rollback est une instruction qui annule toutes les mises à jour.

Pr. MERZOUK Soukaina 166

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

Pr. MERZOUK Soukaina 167

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

Pr. MERZOUK Soukaina 168

Contrôle des Transactions
Transaction

INSERT
INSERT UPDATE
UPDATE INSERT
INSERT DELETE
DELETE

COMMIT Savepoint A Savepoint B

ROLLBACK to Savepoint B

ROLLBACK to Savepoint A

ROLLBACK
Pr. MERZOUK Soukaina 169
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

Pr. MERZOUK Soukaina 170

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.

Pr. MERZOUK Soukaina 171

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.

Pr. MERZOUK Soukaina 172

Validation de Données

➢Effectuez les modifications.

SQL> UPDATE emp
2 SET deptno = 10
3 WHERE empno = 7782;
1 row updated.

➢ Validez les modifications.

SQL> COMMIT;
Commit complete.

Pr. MERZOUK Soukaina 173

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.

Pr. MERZOUK Soukaina 174

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.
Pr. MERZOUK Soukaina 175
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.

Pr. MERZOUK Soukaina 176

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

Pr. MERZOUK Soukaina 177

Implémentation de la Lecture Cohérente
update emp
set sal = 2000 Blocs de
where ename données
= 'SCOTT' Rollback
segments
Utilisateur A
données modifiées
select * Lit une et non modifiées
from emp image
cohérente 'anciennes’
données avant
Utilisateur B
modif.

Pr. MERZOUK Soukaina 178

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
ROLLBACK Annule toutes les modifications de données en
instance
Pr. MERZOUK Soukaina 179
Création des tables
avec contraintes
Pr. MERZOUK Soukaina 180
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

Pr. MERZOUK Soukaina 181

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é Oracle8 Server

Pr. MERZOUK Soukaina 182

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 dans Oracle :
▪ NOT NULL
▪ UNIQUE
▪ PRIMARY KEY
▪ FOREIGN KEY
▪ CHECK
Pr. MERZOUK Soukaina 183
Conventions Applicables aux Contraintes
➢Si vous ne nommez pas une contrainte, Oracle8 Server créera un nom
au format SYS_Cn.
➢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
➢Définissez une contrainte peut être définie au niveau table ou
colonne.
➢Consulter le dictionnaire de données pour retrouver une contrainte.

Pr. MERZOUK Soukaina 184

 L'Ordre CREATE TABLE
➢Vous devez posséder :
▪ Un privilège CREATE TABLE
▪ Un espace de stockage
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
…
[table_constraint]);
➢Spécifiez :
▪ Un nom de table
▪ Le nom, le type de données et la taille des colonnes.
Pr. MERZOUK Soukaina 185
Création de Tables
➢Créer la table. SQL> CREATE TABLE dept
2 (deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13));
Table created.
➢ Vérifier la création de la table.
SQL> DESCRIBE dept
Name NULL? Type
--------------------------- -------- ---------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Pr. MERZOUK Soukaina 186
Création de Tables avec contraintes

CREATE TABLE emp(

empno NUMBER(4),
ename VARCHAR2(10),
…
deptno NUMBER(2) NOT NULL,
CONSTRAINT emp_empno_pk
PRIMARY KEY (EMPNO));

Pr. MERZOUK Soukaina 187

Création d'une Table au Moyen d'une Sous-Interrogation

➢Créez une table et insérez des lignes en associant l'ordre CREATE

TABLE et l'option AS subquery.
CREATE TABLE table
[column(, column...)]
AS subquery;

➢Le nombre de colonnes spécifiées doit correspondre au nombre de

colonnes de la sous-interrogation.
➢Définissez des colonnes avec des noms de colonne et des valeurs par
défaut.
Pr. MERZOUK Soukaina 188
Création d'une Table au Moyen d'une Sous-Interrogation
SQL> CREATE TABLE dept30
2 AS
3 SELECT empno, ename, sal*12 ANNSAL, hiredate
4 FROM emp
5 WHERE deptno = 30;
Table created.
SQL> DESCRIBE dept30
Name NULL? Type
---------------------------- -------- -----
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
ANNSAL NUMBER
HIREDATE DATE

Pr. MERZOUK Soukaina 189

Les Contraintes

➢Contrainte au niveau colonne

column [CONSTRAINT constraint_name] constraint_type,

➢Contrainte au niveau table

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

Pr. MERZOUK Soukaina 190

La Contrainte NOT NULL

➢Se définit au niveau colonne

SQL> CREATE TABLE emp(

2 empno NUMBER(4),
3 ename VARCHAR2(10) NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(2) NOT NULL);

Pr. MERZOUK Soukaina 191

La Contrainte de Clé UNIQUE

➢Se définit au niveau table ou colonne

SQL> CREATE TABLE dept(

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

Pr. MERZOUK Soukaina 192

La Contrainte PRIMARY KEY

➢Se définit au niveau table ou colonne

SQL> CREATE TABLE dept(

2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE (dname),
6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));

Pr. MERZOUK Soukaina 193

La Contrainte FOREIGN KEY
➢Se définit au niveau table ou colonne
SQL> CREATE TABLE emp(
2 empno NUMBER(4),
3 ename VARCHAR2(10) NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(2) NOT NULL,
10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11 REFERENCES dept (deptno));
Pr. MERZOUK Soukaina 194
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

Pr. MERZOUK Soukaina 195

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
..., deptno NUMBER(2),
CONSTRAINT emp_deptno_ck
CHECK (DEPTNO BETWEEN 10 AND 99),...
Pr. MERZOUK Soukaina 196
Modification de la structure ALTER TABLE

➢Ajout de colonnes
ALTER TABLE nom_table
ADD (colonne1 type1, colonne2 type2);

➢Modification de colonnes
ALTER TABLE nom_table
MODIFY (colonne1 type1, colonne2 type2);

➢Suppression de colonnes
ALTER TABLE nom_table
DROP COLUMN (colonne1, colonne2);
197
Pr. MERZOUK Soukaina
Ajout de Colonnes
➢Utilisez la clause ADD pour ajouter des colonnes.
SQL> ALTER TABLE dept30
2 ADD (job VARCHAR2(9));
Table altered.
▪ La nouvelle colonne est placée à la fin.
EMPNO ENAME ANNSAL HIREDATE JOB
--------- ---------- --------- --------- ----
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.
Pr. MERZOUK Soukaina 198
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 (ename VARCHAR2(15));
Table altered.
➢ la modification d’une valeur par défaut ne s’applique qu’aux
insertions ultérieures dans la table.

Pr. MERZOUK Soukaina 199

Modification des contraintes : Ajout et Suppression

Ajout de contraintes
ALTER TABLE nom_table
ADD CONSTRAINT nom_contrainte
type_contrainte;

Comme à la création d’une table

Suppression de contraintes

ALTER TABLE nom_table

DROP CONSTRAINT nom_contrainte;

Pr. MERZOUK Soukaina

200
Modification des contraintes : Ajout et Suppression
SQL> ALTER TABLE emp
2 ADD CONSTRAINT emp_mgr_fk
3 FOREIGN KEY(mgr) REFERENCES emp(empno);
Table altered.
SQL> ALTER TABLE emp
2 DROP CONSTRAINT emp_mgr_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.
Pr. MERZOUK Soukaina 201
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_empno_pk CASCADE;
Table altered.

Pr. MERZOUK Soukaina 202

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_empno_pk;
Table altered.
➢Si vous activez une contrainte UNIQUE ou PRIMARY KEY, un index
correspondant est automatiquement créé.

Pr. MERZOUK Soukaina 203

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 EMPNO IS NOT NULL
SYS_C00675 C DEPTNO IS NOT NULL
EMP_EMPNO_PK P
...
Pr. MERZOUK Soukaina 204
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.

Pr. MERZOUK Soukaina 205

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.

Pr. MERZOUK Soukaina 206

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

Pr. MERZOUK Soukaina 207