SGBDR1

(ORACLE)

Mr MOHAMED AIT HAMMOU

Le langage SQL (Structured Query Longage: Langage dinterrogation

structur) , li la structure relationnelle des BD, est un langage non
procdural, il comporte plusieurs commandes qui se repartissent en
trois familles fonctionnellement distinctes:

Langage de dfinition des donnes(LDD): crer des tables dans

une BDR, ainsi den modifier ou de supprimer leur structure.

Langage de manipulation des donnes (LMD): slectionner,

insrer, modifier, ou supprimer des donnes dans une table.

Langage de contrle de donnes(LCD): grer la scurit et les

permissions au niveau des utilisateurs dune

BDR.

Mr MOHAMED AIT HAMMOU

Langage de dfinition des Donnes

Un ordre du LDD permet de crer , de supprimer et de
modifier la structure dune base de donnes relationnelle;
les objets que le LDD peut grer sont:

Table
Vue
Squence
Index
Synonyme

1/21/15
Mr MOHAMED AIT HAMMOU

Table DUAL
Table DUAL
C'est une particularit d'Oracle. Cette pseudo-table ne contient qu'une
seule ligne et une seule colonne et ne peut tre utilise qu'avec une requte
select.
Elle permet de faire afficher une expression dont la valeur ne dpend
d'aucune table en particulier.
Exemple
Afficher la date d'aujourd'hui, le nom de l'utilisateur et le rsultat d'un
calcul, revient crire.
select sysdate, user, round(3676 / 7) from dual;

1/21/15
Mr MOHAMED AIT HAMMOU

Syntaxe :

Cration de Tables

CREATE TABLE nomtable

(champs typechamps [DEFAULT valeur], ...);
CREATE TABLE nomtable
[(champs1, champs2...)] as sousinterrogation;

Les identificateurs utiliss doivent respecter les rgles

suivantes:
Commencer par une lettre et contenir que les
caractres A Z, 0 9, _, $, et # (30 caractres
maximum).
tre diffrent des autres objets appartenant au mme
utilisateur et des mots rserv Oracle
1/21/15
Mr MOHAMED AIT HAMMOU

Types de donnes

Description

VARCHAR2(taille)
Donnes caractres de longueur variable
(2000 caractres maximum)
CHAR(taille)
caractres maximum)

Donnes caractres de longueur fixe (255

NUMBER(taille,dcimale) Numrique de longueur variable

DATE

Valeurs de date et d'heure

LONG

Donnes caractres de longueur variable,

jusqu' 2 giga-octets

LONG RAW

Binaire(image)

1/21/15
Mr MOHAMED AIT HAMMOU

Cration de Tables
CREATE
CREATE TABLE
TABLE dept
dept
(( deptno
deptno NUMBER(2),
NUMBER(2),

dname
dname VARCHAR2(15),
VARCHAR2(15),
loc
loc VARCHAR2(12));
VARCHAR2(12));

CREATE
CREATE
AS
AS

TABLE
TABLE Empclerk
Empclerk
SELECT
SELECT empno
empno mat,
mat, ename,
ename, nom,
nom, sal
sal ++ comm
comm as
as salaire,
salaire,
hiredate,
hiredate, deptno
deptno
FROM
FROM emp
emp
WHERE
WHERE job
job like
like 'clerk');
'clerk');

1/21/15
Mr MOHAMED AIT HAMMOU

L'ordre ALTER TABLE permet dajouter de nouveau

champs ou de modifier des champs existants.
Syntaxe :
ALTER TABLE Nomtable
ADD
(champs typechamps [DEFAULT valeur]
[, champs typechamps]...);
ALTER TABLE Nomtable
MODIFY
(champs typechamps [DEFAULT valeur]
[, champs typechamps]...);
ALTER TABLE Nomtable
DROP COLUMN champs ;
ALTER TABLE table
RENAME COLUMN ancien_nom TO nouveau_nom
1/21/15
Mr MOHAMED AIT HAMMOU

Gestion des tables

Supprimer une Table
DROP
DROP TABLE
TABLE Nomtable;
Nomtable;

Renommer une Table

RENAME
RENAME AncienNomTable
AncienNomTable TO
TO NouveauNomTable;
NouveauNomTable;

Vider une Table

TRUNCATE
TRUNCATE TABLE
TABLE NomTable;
NomTable;

1/21/15
Mr MOHAMED AIT HAMMOU

Les Contraintes dintgrits

Les types de contraintes reconnues dans SQL sont:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

1/21/15
Mr MOHAMED AIT HAMMOU

Les Contraintes dintgrits

Syntaxe :
CREATE
CREATE TABLE
TABLE [Schema.]NomTable
[Schema.]NomTable
(Champs
(Champs TypeChamps
TypeChamps [DEFAULT
[DEFAULT Valeur]
Valeur]

[[CONSTRAINT
[[CONSTRAINT NomContrainte]
NomContrainte] TypeContrainte],
TypeContrainte],

,,

[[CONSTRAINT
[[CONSTRAINT NomContrainte]
NomContrainte] TypeContrainte(Champs,
TypeContrainte(Champs, ...),]
...),]

);
);
ALTER
NomTable
ALTER TABLE
TABLE
NomTable
ADD
ADD [CONSTRAINT
[CONSTRAINT NomContrainte]
NomContrainte] TypeContrainte(Champs);
TypeContrainte(Champs);

1/21/15
Mr MOHAMED AIT HAMMOU

CREATE
CREATE TABLE
TABLE Employes(
Employes(
empno
empno NUMBER(4)
NUMBER(4) PRIMARY
PRIMARY KEY,
KEY, ename
ename VARCHAR2(15)
VARCHAR2(15) NOT
NOT NULL,
NULL,
job
job VARCHAR2(10),
VARCHAR2(10), mgr
mgr NUMBER(4),
NUMBER(4), hiredate
hiredate DATE,
DATE, sal
sal NUMBER(8,2),
NUMBER(8,2),
comm
comm NUMBER(8,2),
NUMBER(8,2), deptno
deptno NUMBER(2)
NUMBER(2) NOT
NOT NULL
NULL ,,
CONSTRAINT
CONSTRAINT un_dept
un_dept UNIQUE
UNIQUE (ename)
(ename) ););
ALTER
ALTER TABLE
TABLE Employes
Employes
ADD
ADD CONSTRAINT
CONSTRAINT Emp_Dept_Fk
Emp_Dept_Fk FOREIGN
FOREIGN KEY
KEY (deptno)
(deptno)

REFERENCES
REFERENCES dept
dept (deptno)
(deptno) ON
ON DELETE
DELETE CASCADE;
CASCADE;

ALTER
ALTER TABLE
TABLE Employes
Employes
ADD
ADD CONSTRAINT
CONSTRAINT CONSTRAINT
CONSTRAINT CHECK
CHECK (Comm
(Comm >> 0);
0);
1/21/15
Mr MOHAMED AIT HAMMOU

Gestion des contraintes

Supprimer une contrainte
ALTER
ALTER TABLE
TABLE NomTable
NomTable
DROP
DROP CONSTRAINT
CONSTRAINT NomContrainte
NomContrainte [CASCADE];
[CASCADE];

Dsactiver une Contraintes

ALTER
NomTable
ALTER TABLE
TABLE
NomTable
DISABLE
DISABLE CONSTRAINT
CONSTRAINT NomContrainte
NomContrainte [CASCADE];
[CASCADE];

Activer une Contraintes

ALTER
NomTable
ALTER TABLE
TABLE
NomTable
ENABLE
NomContrainte
ENABLE CONSTRAINT
CONSTRAINT
NomContrainte [CASCADE];
[CASCADE];

1/21/15
Mr MOHAMED AIT HAMMOU

Notion

de vue

Une vue est une table virtuelle base sur une ou

plusieurs

table (ou sur dautres vues). Cest une

fentre par laquelle il est possible de visualiser ou de

modifier des donnes venant de ces tables (appels
tables de base).

Les utilisateurs consultent la base, ou modifier la base

(avec certaines restrictions)

travers les vues

(Limitation d'accs )
1/21/15
Mr MOHAMED AIT HAMMOU

Syntaxe
CREATE
CREATE VIEW
VIEW NomVue
NomVue [(alias[,
[(alias[, alias]...)]
alias]...)]
AS
AS SousInterogation
SousInterogation
[WITH
[WITH CHECK
CHECK OPTION]
OPTION]
[WITH
[WITH READ
READ ONLY]
ONLY]

La SousInterogation(ordre select) ne doit pas

comporter la clause ORDER BY.
WITH CHECK OPTION permet de garantir que les
ordres LMD reste dans le domaine de la vue.
WITH READ ONLY assure qu'aucune opration
LMD ne sera excute dans la vue.

1/21/15
Mr MOHAMED AIT HAMMOU

Exemple de cration d'une Vue

CREATE
CREATE VIEW
VIEW manager
manager
(matricule,
(matricule, nom,
nom, fonction,
fonction, dateembouche,
dateembouche, salaire,
salaire, Departement
Departement ))
AS
AS SELECT
SELECT empno,
empno, ename,
ename, job,hiredate,
job,hiredate, sal
sal ++ nvl(comm,0),
nvl(comm,0), dname
dname
FROM
emp,
FROM
emp, dept
dept
WHERE
WHERE ((emp.deptno
((emp.deptno == dept.deptno)
dept.deptno) and
and
empno
empno in
in (select
(select mgr
mgr from
from emp
emp where
where mgr
mgr is
is not
not NULL))
NULL))
WITH
WITH READ
READ ONLY
ONLY
Select
Select **
from
from manager
manager ;;

1/21/15
Mr MOHAMED AIT HAMMOU

Suppression dune vue

L'ordre DROP VIEW permet supprimer une vue (les
tables de base correspondantes ne sont pas supprimes).
Syntaxe
DROP
DROP VIEW
VIEW

NomVue;
NomVue;

1/21/15
Mr MOHAMED AIT HAMMOU

Index

INDEX

Un index Oracle est un objet de schma qui permet d'acclrer l'extraction

de lignes par le biais d'un pointeur.

Si la colonne ne comporte pas d'index, la table entire est balaye.

Un index offre un accs direct et rapide aux lignes d'une table. Il a pour
but de rduire les E/S sur disque grce un chemin d'accs index qui

identifie rapidement l'emplacement des donnes.

Quand Faut-il Crer un Index

Lorsqu'une colonne est frquemment utilise dans la clause WHERE ou

dans une condition de jointure.

Lorsqu'une colonne contient un large ventail de valeurs.

Lorsqu'une colonne contient un grand nombre de valeurs NULL.
Lorsque deux ou plusieurs colonnes sont frquemment utilises
conjointement dans une clause WHERE ou une condition de jointure.

Lorsque la table est de grande taille et que la plupart des requtes

doivent extraire moins de 2 4 % de lignes.
1/21/15
Mr MOHAMED AIT HAMMOU

CREATE INDEX
Un index se cre par la commande CREATE INDEX :
CREATE [UNIQUE] INDEX [nom-schma.]nom-index ON <nom-table> ( { nomcolonne [ASC | DESC ] } )
On peut spcifier par l'option UNIQUE que chaque valeur d'index doit tre
unique dans la table.
On peut spcifier par l'option ASC et DESC un tri en ordre croissant ou
dcroissant (respectivement).
Exemple : index sur les numros de pice, sans double, en ordre croissant
CREATE UNIQUE INDEX emp_idx ON EMP (EMPNO ASC) ;

1/21/15
Mr MOHAMED AIT HAMMOU

DROP INDEX

Pour modifier un index, vous devez le supprimer puis le recrer.

Utiliser DROP INDEX Pour supprimer un index
Pour supprimer un index, vous devez en tre le propritaire ou avoir le
privilge DROP ANY INDEX.

Un index est automatiquement supprim ds qu'on supprime la table

laquelle il appartient.
Exemple
DROP INDEX emp_idx ;

1/21/15
Mr MOHAMED AIT HAMMOU

SEQUENCE
Une squence est un compteur programmable stock en mmoire par Oracle
et utilisable de manire partag.
Il est, en gnral, utilis pour fournir les n des cls d'enregistrements.
CREATE SEQUENCE nom_squence [INCREMENT BY entier1]
[START WITH entier2]
Exemple
create sequence seqdept
increment by 10
start with 10

1/21/15
Mr MOHAMED AIT HAMMOU

Utilisation SEQUENCE

Deux pseudo-colonnes permettent d'utiliser les squences :

CURRVAL retourne la valeur courante de la squence ;

NEXTVAL incrmente la squence et retourne la nouvelle valeur.

Exemple:
insert into dept(deptno, dname,loc)
values (seqdept.nextval, 'Finances', 'casablanca')
Pour voir la valeur d'une squence, on utilise currval avec la table dual
select seqdept.currval from dual.

1/21/15
Mr MOHAMED AIT HAMMOU

Lordre Select
Lutilisation la plus frquente de SQL seffectue dans les
requtes afin de rechercher les donnes dans une base de
donnes.
Syntaxe
select [distinct] * | <liste des champs >
From <Liste des tables>
[where <critre de slection >]
[group by <critres de regroupement>]
[having <conditions de filtrage sur les groupes>]
[order by <liste des champs > asc | desc ];
1/21/15
Mr MOHAMED AIT HAMMOU

criture des Ordres SQL

Les ordres SQL peuvent tre crits indiffremment en

majuscules et/ou minuscules.

Les

ordres SQL peuvent tre crits sur plusieurs

lignes.

Les mots-cls ne doivent pas tre abrgs ni scinds

sur deux lignes diffrentes.

Les clauses sont gnralement places sur des lignes

distinctes.

1/21/15
Mr MOHAMED AIT HAMMOU

Exemple de SELECT
SELECT *
FROM
dept;
DEPTNO
--------10
20
30
40

DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS

LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON

SELECT deptno, loc

FROM
dept;
DEPTNO
--------10
20
30
40

LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON

1/21/15
Mr MOHAMED AIT HAMMOU

Exemple de SELECT
SELECT *
FROM
dept;
DEPTNO
--------10
20
30
40
SELECT
SELECT
FROM
FROM

DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS

deptno
deptno
emp;
emp;

DEPTNO
--------10
30
10
20
...
14 rows selected.

LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON

SELECT DISTINCT deptno

FROM
emp;
DEPTNO
--------10
20
30

1/21/15
Mr MOHAMED AIT HAMMOU

LALIAS DE COLONNE

Permet de renommer un en-tte de colonne (champs)

Suit immdiatement le nom de la colonne ; le mot-cl
AS peut tre plac entre le nom et lalias est optionnel

Doit

obligatoirement tre inclus entre guillemets (")

sil contient des espaces, des caractres spciaux ou si

les majuscules/minuscules doivent tre diffrencies

SELECT ename AS name, sal salary

FROM
emp;
Mr MOHAMED AIT HAMMOU

LE
LITTRAL

Un

littral est un caractre, une expression, ou un

nombre inclus dans la liste SELECT.

Les valeurs littrales de type date et caractre doivent

tre places entre simples quottes (').
SELECT ename, job, deptno
FROM
emp
WHERE job='CLERK';
ENAME
---------JAMES
SMITH
ADAMS
MILLER

JOB
DEPTNO
--------- --------CLERK
30
CLERK
20
CLERK
20
CLERK
10
Mr MOHAMED AIT HAMMOU

LES OPRATEURS

Mr MOHAMED AIT HAMMOU

UTILISATION DES
OPRATEURS
SELECT
FROM
WHERE

ename, sal
emp
sal BETWEEN 1000 AND 1500;

ENAME
SAL
---------- --------MARTIN
1250
TURNER
1500
WARD
1250
ADAMS
1100
MILLER
1300
SELECT
FROM
WHERE

Limite

Limite

infrieure

suprieure

empno, ename, sal, mgr

emp
mgr IN (7902, 7566, 7788);

EMPNO
--------7902
7369
7788
7876

ENAME
SAL
MGR
---------- --------- --------FORD
3000
7566
SMITH
800
7902
SCOTT
3000
7566
ADAMS
1100
7788
Mr MOHAMED AIT HAMMOU

UTILISATION DES OPRATEURS DE COMPARAISON

SELECT
FROM
WHERE

ename
emp
ename LIKE 'S%';

SELECT
FROM
WHERE

ename
emp
ename LIKE '_A%';

ENAME
---------JAMES
WARD

Mr MOHAMED AIT HAMMOU

UTILISATION DES OPRATEURS LOGIQUES

...
...
...
...

WHERE
WHERE
WHERE
WHERE

...
...
...
...

NOT BETWEEN ... AND

IS NOT IN ...
NOT LIKE ...
IS NOT NULL

...

SELECT ename, job

FROM
emp
WHERE job NOT IN ('CLERK','MANAGER','ANALYST');
ENAME
---------KING
MARTIN
ALLEN
TURNER
WARD

JOB
--------PRESIDENT
SALESMAN
SALESMAN
SALESMAN
SALESMAN
Mr MOHAMED AIT HAMMOU

UTILISATION DE LOPRATEURS DE CONCATNATION

SELECT ename
FROM

||' is a '||job AS "Employee Details"

emp;

Employee
Employee Details
Details
------------------------------------------------KING
KING is
is aa PRESIDENT
PRESIDENT
BLAKE
BLAKE is
is aa MANAGER
MANAGER
CLARK
CLARK is
is aa MANAGER
MANAGER
JONES
JONES is
is aa MANAGER
MANAGER
MARTIN
MARTIN is
is aa SALESMAN
SALESMAN
...
...
14
14 rows
rows selected.
selected.

Mr MOHAMED AIT HAMMOU

EXEMPLE DE TRI DES ENREGISTREMENTS

SELECT
ename, deptno, sal
FROM
emp
ORDER BY deptno, sal DESC;
ENAME
DEPTNO
SAL
---------- --------- --------KING
10
5000
CLARK
10
2450
MILLER
10
1300
FORD
20
3000
...
14 rows selected.

Mr MOHAMED AIT HAMMOU

TYPES DE JOINTURES EN SQL

Equijointure
Equijointure

Non-quijointure
Non-quijointure

Jointure
Jointure externe
externe

Autojointure
Autojointure

Mr MOHAMED AIT HAMMOU

EXEMPLE
DQUIJOINTURE
SELECT
FROM
WHERE

empno, ename, emp.deptno, dept.deptno , dept.loc

emp, dept
emp.deptno=dept.deptno;

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.

Mr MOHAMED AIT HAMMOU

EXEMPLE DE NON-QUIJOINTURE
SELECT
e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal
BETWEEN
s.losal AND s.hisal;

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

Mr MOHAMED AIT HAMMOU

EXEMPLE DE JOINTURES EXTERNES

SELECT e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
ORDER BY
e.deptno;

ENAME
DEPTNO DNAME
---------- --------- ------------KING
10 ACCOUNTING
CLARK
10 ACCOUNTING
...
40 OPERATIONS
15 rows selected.

Mr MOHAMED AIT HAMMOU

EXEMPLE DE LAUTOJOINTURES
SELECT worker.ename||' works for '||manager.ename
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno;

WORKER.ENAME||'WORKSFOR'||MANAG
WORKER.ENAME||'WORKSFOR'||MANAG
------------------------------------------------------------BLAKE
BLAKE works
works for
for KING
KING
CLARK
CLARK works
works for
for KING
KING
JONES
JONES works
works for
for KING
KING
MARTIN
MARTIN works
works for
for BLAKE
BLAKE
...
...
13
13 rows
rows selected.
selected.

Mr MOHAMED AIT HAMMOU

FONCTION DE REGROUPEMENT DE DONNES

Les fonctions de groupe agissent sur des groupes de lignes
et donnent un rsultat par groupe

AVG ([DISTINCT|ALL]n)
COUNT ({ *|[DISTINCT|ALL]expr})
MAX ([DISTINCT|ALL]expr)
MIN ([DISTINCT|ALL]expr)
SUM ([DISTINCT|ALL]n)

Mr MOHAMED AIT HAMMOU

UTILISATION DES FONCTIONS DE REGROUPEMENT

SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)
FROM emp
WHERE
job LIKE 'SALES%';
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)
-------- --------- --------- --------1400
1600
1250
5600
SELECT
FROM emp
WHERE

COUNT(*)
deptno = 30;

COUNT(*)
--------6
Mr MOHAMED AIT HAMMOU

UTILISATION DES FONCTIONS DE REGROUPEMENT

SELECT AVG(comm)
FROM
emp;
AVG(COMM)
--------550

SELECT AVG(NVL(comm,0))
FROM
emp;
AVG(NVL(COMM,0))
---------------157.14286

Mr MOHAMED AIT HAMMOU

CRATION DE GROUPES DE DONNES

DEPTNO
SAL
--------- --------10
2450
10
5000
10
1300
20
800
20
1100
20
3000
20
3000
20
2975
30
1600
30
2850
30
1250
30
950
30
1500
30
1250

2916.6667

DEPTNO AVG(SAL)
"salaire
moyen pour ------- --------2175
chaque
10 2916.6667
dpartement
20
2175
de la table
30 1566.6667
EMP"
1566.6667

SELECT
deptno, AVG(sal)
FROM
emp
GROUP BY deptno;
Mr MOHAMED AIT HAMMOU

EXEMPLE DE CRATION DE GROUPES DE DONNES

SELECT
deptno, job, sum(sal)
FROM
emp
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.

Mr MOHAMED AIT HAMMOU

IMBRICATION DES FONCTIONS DE GROUPE

SELECT
max(avg(sal))
FROM
emp
GROUP BY deptno;
MAX(AVG(SAL))
------------2916.6667

Mr MOHAMED AIT HAMMOU

UTILISATION DE LA CLAUSE HAVING

La clause HAVING permet restreindre les groupes

Les lignes sont regroupes.

La fonction de groupe est applique.
Les groupes qui correspondent la clause HAVING
sont affichs.

Mr MOHAMED AIT HAMMOU

EXEMPLE DE LUTILISATION DE HAVING

SELECT
FROM
GROUP BY
HAVING

deptno, max(sal)
emp
deptno
max(sal)>2900;

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

Mr MOHAMED AIT HAMMOU

EXEMPLE DE LUTILISATION DE HAVING

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

job, SUM(sal) PAYROLL

emp
job NOT LIKE 'SALES%'
job
SUM(sal)>5000
SUM(sal);

JOB
PAYROLL
--------- --------ANALYST
6000
MANAGER
8275

Mr MOHAMED AIT HAMMOU

INTERROGATIONS
Syntaxe
SELECT select_list
FROM tables
WHERE expr operator (SELECT select_list FROM
tables )
;

La sous-interrogation (requte interne) est excute une fois

avant la requte principale (une sous-interrogation ne doit pas
contenir la clause ORDER BY).

Le rsultat de la sous-interrogation est utilis par la requte

principale (externe).

Mr MOHAMED AIT HAMMOU

TYPES DE SOUS-INTERROGATIONS
Sous-interrogation
Sous-interrogation
mono-ligne
mono-ligne
Oprateurs mono-ligne

Sous-interrogation
Sous-interrogation
multi-ligne
multi-ligne
Oprateurs multi-ligne

Mr MOHAMED AIT HAMMOU

EXEMPLE DE SOUS-INTERROGATIONS MONO-LIGNE

SELECT
FROM
WHERE

AND

ename, job
emp
job =
(SELECT
FROM
WHERE
sal >
(SELECT
FROM
WHERE

ENAME
ENAME
------------------MILLER
MILLER

CLERK

job
emp
ename = 'ADAMS')

1100

sal
emp
ename = 'ADAMS');

JOB
JOB
----------------CLERK
CLERK

Mr MOHAMED AIT HAMMOU

EXEMPLE DE SOUS-INTERROGATIONS MONO-LIGNE

SELECT
FROM
WHERE

ename, job, sal

emp
sal =
(SELECT MIN(sal)
FROM
emp);

ENAME
ENAME
------------------SMITH
SMITH
SELECT
FROM
GROUP BY
HAVING

800

JOB
SAL
JOB
SAL
----------------- ----------------CLERK
800
CLERK
800
deptno, MIN(sal)
emp
deptno
800
MIN(sal) >
(SELECT MIN(sal)
FROM
emp
WHERE
deptno = 20);
Mr MOHAMED AIT HAMMOU

EXEMPLE DE SOUS-INTERROGATIONS MULTI-LIGNE

SELECT
FROM
WHERE

AND

1300
empno, ename, job
1100
emp
800
950
sal < ANY
(SELECT sal
FROM
emp
WHERE job = 'CLERK)
job <> ''CLERK';

EMPNO
EMPNO
----------------7654
7654
7521
7521

ENAME
ENAME
------------------MARTIN
MARTIN
WARD
WARD

JOB
JOB
----------------SALESMAN
SALESMAN
SALESMAN
SALESMAN

Mr MOHAMED AIT HAMMOU

EXEMPLE DE SOUS-INTERROGATIONS MULTI-LIGNE

SELECT
FROM
WHERE

empno, ename, job

1566.6667
2175
emp
2916.6667
sal > ALL
(SELECT avg(sal)
FROM
emp
GROUP BY deptno)

EMPNO
EMPNO
----------------7839
7839
7566
7566
7902
7902
7788
7788

ENAME
ENAME
------------------KING
KING
JONES
JONES
FORD
FORD
SCOTT
SCOTT

JOB
JOB
----------------PRESIDENT
PRESIDENT
MANAGER
MANAGER
ANALYST
ANALYST
ANALYST
ANALYST

Mr MOHAMED AIT HAMMOU

LES SOUS-INTERROGATIONS SYNCHRONISES

Syntaxe: SELECT outer1, outer2, ...
FROM table1 alias1
WHERE outer1 operator (SELECT inner1
FROM table2 alias2
WHERE alias1.outer2 =
alias2.inner1);
La sous-interrogation est excute pour chaque
enregistrement de la requte principale.
Nouvelle ligne candidate (requte externe)
Excuter la requte interne utilisant une valeur de la ligne
candidate
Utiliser la valeur(s) de la requte interne qualifiant la ligne
candidate
Mr MOHAMED AIT HAMMOU

EXEMPLE DE SOUS-INTERROGATIONS
SYNCHRONISES
Recherchez tous les employs dont le salaire est
suprieur au salaire moyen de leur dpartement
SELECT empno, sal, deptno
Chaque fois que la requte
externe est traite,
FROM
emp outer
la requte interne
WHERE sal > (SELECT AVG(sal)
est excute.
FROM emp inner
WHERE outer.deptno= inner.deptno);
EMPNO
SAL
DEPTNO
EMPNO
SAL
DEPTNO
--------------- ----------------- ----------------7839
5000
10
7839
5000
10
7698
2850
30
7698
2850
30
7566
2975
20
7566
2975
20
...
...
66 rows
rows selected.
selected.

Oprateur mono.ligne
ou muti-ligne

Mr MOHAMED AIT HAMMOU

LES SOUS-INTERROGATIONS SYNCHRONISES ET

L'OPRATEUR EXISTS
Pour chaque enregistrement de la requte principale, une
recherche denregistrement est effectue dans la sousinterrogation.

La recherche dans la sous-interrogation est interrompue

ds une ligne a t trouve, la condition de la requte
principale est vraie.

La condition de la requte principale est fausse si aucune

ligne na t trouve dans la sous-interrogation.
Mr MOHAMED AIT HAMMOU

EXEMPLE DE LUTILISATION DE LOPRATEUR EXISTS

Recherchez les employs ayant au moins une personne
sous leur responsabilit.
SELECT empno, ename, job, deptno
FROM
emp outer
WHERE EXISTS (SELECT empno
FROM
emp inner
WHERE inner.mgr = outer.empno);
EMPNO ENAME
--------- ---------7839 KING
7698 BLAKE
7782 CLARK
7566 JONES
...
6 rows selected.

JOB
DEPTNO
--------- --------PRESIDENT
10
MANAGER
30
MANAGER
10
MANAGER
20

Mr MOHAMED AIT HAMMOU

LES OPRATEURS ENSEMBLISTES

Intersect
Union
Minus
Table Emp

Table EMPHISTORY

Mr MOHAMED AIT HAMMOU

EXEMPLE DE L'OPRATEUR UNION

Affichez le nom, le poste et le dpartement de tous
les employs
SELECT ename, job, deptno
FROM
emp
UNION
SELECT name, title, deptid
FROM emp_history;
ENAME
JOB
DEPTNO
---------- --------- --------ADAMS
CLERK
30
ALLEN
SALESMAN
30
ALLEN
SALESMAN
20
BALFORD
CLERK
20
BLAKE
MANAGER
30
...
20 rows selected.
Mr MOHAMED AIT HAMMOU

EXEMPLE DE L'OPRATEUR INTERSECT

Affichez les diffrents noms, numros et postes des
employs prsents dans les tables EMP et
EMPHISTORY.
SELECT ename, empno, job
FROM
emp
INTERSECT
SELECT name, empid, title
FROM
emp_history;
ENAME
EMPNO
ENAME
EMPNO JOB
JOB
------------------- ----------------- ----------------ALLEN
7499
ALLEN
7499 SALESMAN
SALESMAN
CLARK
7782
CLARK
7782 MANAGER
MANAGER
SCOTT
7788
SCOTT
7788 ANALYST
ANALYST
Mr MOHAMED AIT HAMMOU

EXEMPLE DE L'OPRATEUR MINUS

Affichez le nom,le numro et le poste de tous les
employs ayant quitt la socit.
SELECT
FROM
MINUS
SELECT
FROM

name, empid, title

emp_history
ename, empno, job
emp;

NAME
EMPID
NAME
EMPID
--------------------------- --------BALFORD
6235
BALFORD
6235
BRIGGS
7225
BRIGGS
7225
JEWELL
7001
JEWELL
7001
SPENCER
6087
SPENCER
6087
...
...
66 rows
rows selected.
selected.

TITLE
TITLE
----------------CLERK
CLERK
PAY
PAY CLERK
CLERK
ANALYST
ANALYST
OPERATOR
OPERATOR

Mr MOHAMED AIT HAMMOU

Ajout denregistrement dans une Table

L'ordre INSERT permet d'ajouter un nouveau
enregistrement dans une table.
Syntaxe 1:
INSERT
INSERT INTO
INTO Nomtable
Nomtable [(champs
[(champs [,
[, champs
champs ...])]
...])]
VALUES
VALUES (valeur
(valeur [,[, valeur...]
valeur...] ););

Indiquez

les valeurs dans l'ordre par dfaut des

colonnes dans la table.

Placez

les valeurs de type caractre et date entre

simples quotes.
Mr MOHAMED AIT HAMMOU

Ajout denregistrement dans une Table

INSERT INTO dept (deptno, dname, loc)
VALUES

(50, 'FINANCES', 'PARIS');

INSERT
INSERT INTO
INTO emp
emp (empno,
(empno, ename,
ename, job,
job,
mgr,
mgr, hiredate,
hiredate, sal,
sal, deptno)
deptno)
VALUES
VALUES

(8000,
(8000, SAAD',
SAAD', 'ANALYST',
'ANALYST',
7839,
7839, SYSDATE,
SYSDATE, 4000,
4000, 20);
20);

Mr MOHAMED AIT HAMMOU

Ajout denregistrement dans une Table

Syntaxe 2:
INSERT
INSERT INTO
INTO Nomtable
Nomtable [(champs
[(champs [,
[, champs
champs ...])]
...])]
(select
(select );
);

Les champs de la clause INSERT doit correspondre

ceux de la sous-interrogation
Exemple:
INSERT
INSERT INTO
INTO SALSES
SALSES (mat,
(mat, nom,
nom, sal,
sal, hiredate,
hiredate, deptno)
deptno)
SELECT
SELECT empno,
empno, ename,
ename, hiredate,
hiredate, sal+
sal+ comm,
comm, deptno
deptno
FROM
FROM emp
emp
WHERE
WHERE job
job like
like 'SALSES%';
'SALSES%';

Mr MOHAMED AIT HAMMOU

Modification des donnes dune Table

L'ordre UPDATE permet de modifier les
enregistrements dune table.
Syntaxe :
UPDATE
UPDATE
SET
SET
[WHERE
[WHERE

Nomtable
Nomtable
Champs
Champs == valeur
valeur [,
[, champs
champs == valeur]
valeur]
condition];
condition];

La

clause WHERE permet de modifier une ou

plusieurs lignes spcifiques; en cas dabsence de cette
clause toutes les lignes seront modifies.
Chaque opration de modification doit respecter les
contraintes dintgrit rfrentielle de la base de
donnes.

Mr MOHAMED AIT HAMMOU

Modification des donnes dune Table

UPDATE
UPDATE emp
emp

SET
com
SET
com =
=00
WHERE
WHERE com
com is
is NULL;
NULL;
UPDATE
UPDATE emp
emp
SET
SET mgr
mgr == (SELECT
(SELECT empno
empno

FROM
FROM emp
emp
WHERE
WHERE (deptno
(deptno == 30)
30) AND
AND (job
(job like
like 'MANAGER')
'MANAGER') )) ,,

job
job ='SALSESMEN'
='SALSESMEN'
WHERE
WHERE deptno
deptno == 30;
30;

Mr MOHAMED AIT HAMMOU

Suppression des enregistrements dune Table

L'ordre DELETE
permet de supprimer
enregistrements a partir dune table.

des

Syntaxe :
DELETE
DELETE [FROM]
[FROM] Nomtable
Nomtable
[WHERE
[WHERE condition];
condition];

La

clause WHERE permet de supprimer une ou

plusieurs lignes spcifiques en labsence de cette
clause, toutes les lignes sont supprimes.
Chaque opration de modification doit respecter les
contraintes dintgrit rfrentielle de la base de
donnes.
Mr MOHAMED AIT HAMMOU

Suppression des enregistrements dune Table

DELETE
DELETE
WHERE
WHERE

dept
dept
dname
dname == 'FINANCES';
'FINANCES';

DELETE
DELETE FROM
FROM emp
emp
WHERE
deptno
WHERE
deptno ==
(SELECT
(SELECT deptno
deptno
FROM
FROM dept
dept

WHERE
WHERE dname
dname ='SALES');
='SALES');

Mr MOHAMED AIT HAMMOU