Cours de SQL Oracle ETS

ORACLE
Cours de SQL
labor par
Mohamed Taleb, B.Sc., M.Sc.
Said Makhlouf, ING. en informatique
Alain April, Professeur lEcole de Technologie Suprieure
TABLE DES MATIRES
1. INTRODCUTION.................................... ERROR! BOOKMARK NOT DEFINED.
1.1. LES BASES DE DONNES ............................................................................................ 1
1.2. LE MODLE RELATIONNEL......................................................................................... 1
1.2.1. Composantes du modle relationnel................................................................. 1
1.3. LE MODLE DE DONNES ........................................................................................... 1
1.3.1. Le modle Entit/Relation................................................................................. 1
1.4. TERMINOLOGIE DES BASES DE DONNES RELATIONNELLES ....................................... 2
1.5. PROPRITS DUNE BASE DE DONNES RELATIONNELLE............................................ 2
1.6. SOLUTION COMPLTE DORACLE .............................................................................. 3
1.7. LE SYSTME DE GESTION DES BD RELATIONNELLES (RDBMS)................................ 3
2. SQL (STRUCTURED QUERY LANGUAGE).......................................................... 4
2.1. RCAPITULATIF DES COMMANDES SQL ......................................................... 4
2.2. LES OBJETS SQL ................................................................................................. 4
2.3. SQL*PLUS .......................................................................................................... 5
2.4. COMMENT ENTRER SOUS SQL*PLUS ............................................................ 5
2.5. COMMANDES SQL*PLUS................................................................................. 7
2.6. FICHIERS DE COMMANDES SQL*PLUS .......................................................... 8
2.7. COMMANDES SQL .............................................................................................. 8
13. QUIZ .......................................................................................................................... 37
14. RPONSE AUX QUESTIONS DU QUIZ.............................................................. 44
Cours de SQL dOracle
1. Introduction
1.1. Les bases de donnes
- Une base de donnes est une collection organise des informations
- Pour grer une base de donnes, on a besoin dun DBMS (Data Base
Management System) ou SGBD (Systme de gestion de base de donnes),
cest (DBMS) un programme qui permet de stocker, rcuprer et modifier les
donnes dune base de donnes.
Il existe 4 types de base de donnes :

- Hirarchique
- Rseau
- Relationnelle
- Objet relationnel (rcent)
La base de donnes Oracle, jusqu la version 7 est une base de donnes relationnelle
(RBDMS : Relational Data Base Management System)
La version 8 dOracle est un Object Relational DBMS (ORDBMS).
1.2. Le modle relationnel

- Les grandes lignes des principes du modle relationnel pour les systmes des bases
de donnes ont t faites par Dr. E F CODD en juin 1970. En ce moment l, les
modles les plus rpondus taient les modles hirarchiques et rseaux.
- Le RDBMS, par sa facilit et sa flexibilit, est devenu rapidement trs populaire.
- Oracle a implment le RDBMS avec succs
1.2.1. Composantes du modle relationnel

- collection dobjets qui stockent les donnes
- un ensemble doprateurs qui permettent de manipuler ces objets
- intgrit des donnes (prcisions, intervalles,)
1.3. Le modle de donnes

1.3.1. Le modle Entit/Relation
Le modle Entit/Relation se construit lors de la phase de lanalyse du cycle dun
systme de dveloppement.
Ses avantages :
- permet dorganiser les informations dans un format clair et prcis
- permet une comprhension facile du schma de la base de donnes
- offre un bon cadre de travail pour lintgration dapplication
Copyright Mohamed Taleb 1

Entits
Employe Departement
#* No employe Assign #* No dept
* Nom employe * Nom dept
o Poste o Loc
(#) No dept Compos de
# : cl primaire Relation :
(#) : cl trangre Nom: ex : assign , compos de,
* : obligatoire renseign par,
o : optionnel Option: doit tre ou peut tre
Degr : soit : 1 et un seul
Soit : 1 ou plusieurs
1.4. Terminologie des bases de donnes relationnelles

- Chaque ligne dune table doit tre identifie par une cl primaire qui ne permet
pas de duplications et qui ne doit pas tre nulle.
- Une cl trangre ou secondaire est une colonne qui dfinit quelles sont tables
qui sont relies entre elles.
- Lordre des lignes et des colonnes dans une table importe peu (pas important)
- Chaque ligne dune table est identifie dune manire unique par la cl primaire.
1.5. Proprits dune base de donnes relationnelle

Contient une collection de tables.
- Elle peut tre accde et modifie en excutant des requtes SQL.

1.6. Solution complte dOracle
Oracle 7/8 Dictionnaire de donnes
BD
Tables,Vues, Index,
(Objets)
PL/SQL SQL SQL*PLUS
Application Oracle Developer
Financier Discovery
Manufacture

Oracle Designer
1.7. Le systme de gestion des BD relationnelles (RDBMS)

- Le RDBMS dOracle est le noyau du produit Oracle..
- Il inclut le serveur dOracle et plusieurs outils qui assistent les utilisateurs dans
la maintenance, la gestion et la mise jour des donnes.
- Le RDBMS permet dexcuter les tables suivantes :
o Grer le stockage et la dfinition des donnes
o Le contrle de laccs aux donnes
o Linterprtation des commandes (SQL, PL/SQL)
o Les backup et les recouvrements
- Le dictionnaire de donnes dOracle est lun des plus importants composants
du serveur. Il est compos dun ensemble de tables et de vues (en lecture
seulement) qui font rfrence la base de donnes.

2. SQL (Structured Query Language)

- Cest un langage utilise pour accder aux informations dune base de donnes.
- Cest un langage ensembliste
- Cest un langage qui peut tre utilis par les dveloppeurs et les administrateurs.
2.1. Rcapitulatif des commandes SQL

SELECT Permet de rcuprer des donnes de la B.D.
INSERT DML(Langage de manipulation de donnes)

UPDATE Cest respectivement insertion, la mise jour et la suppression
DELETE de lignes dune table de la BD.
CREATE DDL(Langage de dfinition de donnes)

AFTER Cest la cration, la mise jour et la suppression des structures de
donnes des tables dune base de donnes.
DROP
RENAME
TRUNCATE
COMMIT Cest des commandes qui servent grer les changements

ROLLBACK faits par le DML
SAVEPOINT
GRANT DCL(Langage de contrle de donnes)

REVOKE Donnent et enlvent le droit daccs la BD et aux structures de
la BD.
2.2. Les Objets SQL

- Les tables
- Les vues
- Les index
- Les synonymes
- Les squences

2.3. SQL*PLUS
- Cest un outil dOracle qui reconnat et soumet les ordres SQL au serveur
dOracle pour les excuter.
- SQL*PLUS contient son propre langage de commande.
- SQL*PLUS peut crer des scripts pour stocker des requtes pour excuter
ultrieurement.
2.4. Comment entrer sous SQL*PLUS

- Sous environnement Windows (NT, 98,98, )
1. Start (ou Dmarrer) Program (ou Programme) Oracle for
Windows NT, 95, 98, 2000 SQL*Plus 8.0 ou 9i
2. Entrer :
o Username (nom dutilisateur) : SCOTT
o Password (Mot de passe) : tiger
o Host String database (nom de la base de donnes)
- Sous un autre environnement :
[username[/Password[Database]]]
Contrle de laccs aux utilisateurs :

Le DBA a le plus haut niveau des privilges
Cration dutilisateurs :
Le DBA cre un usager (user) avec la commande suivante :
CREATE USER nom_utilisateur IDENTIFIED BY mot de passe;
Ex: Create Scott Identified by Tiger;
Cration de privilges :
Il y a 2 types de privilges :
1. Privilge systme (System privileges) (No CASCADE) : Create, Alter,
Drop
2. Privilges objet (Object privileges) (avec CASCADE) : Select, Insert,
Update, Delete, Alter(Table, Sequence seulement), Index, reference,
execute,
GRANT objet_p [{colonne}] ON objet TO {utilisateur|Role|Public}
Objet_p: Select, Update, Insert, Index, References, Execute, Delete, Alter
Colonnes : ce sont des colonnes de la table ou vue
Objet : table, vue,
Public : tous les utilisateurs
Ex: GRANT select ON EMP TO Blake, Smith;
Ex: GRANT update (dname, loc) ON DEPT TO Scott, Manager;

Le DBA peut donner des privilges un utilisateur grce la commande

GRANT.
GRANT liste_privilge [, priv.] TO nom_utilisateur[, ]
Ex : Grant create table, create view to Scott;
Les privilges pour un dveloppeur : Create table, create session(se

connecter la base de donnes), Create View, Create procedure( function,
procedure, package), Create Sequence.
Cration de rles :
Cre par le DBA pour un groupe dusagers avec des privilges. Cela,
permet plus facilement de donner ou de supprimer des privilges. Le DBA
cre un rle puis assigne des privilges ce rle puis assigne ces
privilges des utilisateurs.
Syntaxe :
1. CREATE ROLE nom_rle
2. GRANT CREATE TABLE, CREATE VIEW TO nom_role;
3. GRANT nom_role TO utilisateur1, Blacke, Clarke;
Changement de mot de passe :

ALTER USER nom_utilisateur IDENTIFIED BY nouveau_mot_de_passe;
Ex : ALTER USER Scott IDENTIFIED BY Lion;

2.5. Commandes SQL*PLUS

DESC[RIBE] nom_table Dcrit la structure dune table
A[PPEND] texte Ajoute du texte la fin de la ligne courante (L1 a[ppend]

texte
C[HANGE] / old/new Change lancien texte

C[HANGE] /texte Supprime le texte de la ligne courante
CL[EAR] BUFF[ER] Supprime les lignes du buffer SQL
DEL Supprime la ligne courante
I[NPUT] Insrer un nombre indfini de lignes

I[NPUT] texte Insrer une ligne [texte]
L[ist] Liste toutes les lignes du buffer

L[ist] n Liste la nime ligne
L[ist] n m Liste de la ligne no n m
R[UN] Affiche et excute la requte SQL se trouvant dans le buffer
n Affiche la ligne no n
n texte Remplace la nime ligne par texte
O texte Insrer une ligne avant la ligne no 1.

2.6. Fichiers de commandes SQL*PLUS

SAVE nom_fichier [.ext] [REPLACE] [APPEND] : Sauvegarde le contenu du buffer
SQL dans nom_fichier
[REPLACE] : remplace le nom dj existant
[APPEND] : ajoute au fichier existant
GET fichier[.ext] : obtenir le fichier
START fichier[.ext] : excuter le fichier, quivalent @
EDIT fichier.ext : ramne lditeur le contenu du fichier .ext
EDIT : invoke lditeur de SQL*PLUS et sauvegarde le contenu du buffer dans un

fichier par dfaut (afiedt.buf).
SPOOL nom fichier.ext |OFF|OUT : stocke le rsultat dune requte dans un fichier.
OFF : ferme le fichier SPOOL
OUT : ferme le fichier SPOOL et envoie le fichier rsultat limprimante.
EXIT : quitter SQL*PLUS
DEFINE nom_variable [= chane de caractre seulement]: dfinir une variable

utilisateur et lui affecte une valeur. Cest lquivalent de ACCEPT.
DEFINE employe
DEFINE employe = SMITH
UNDEFINE nom_variable : supprime la variable utilisateur.
2.7. Commandes SQL

1. Lordre SELECT
SELECT [Distinct] {* | colonnes [alias], }
FROM nomde tables;
Exemples:
SELECT * FROM Dept;
SELECT ename, job, deptno FROM EMP;
SELECT ename NOM, job Poste_Travail, deptno Num dept FROM EMP;
Description des tables :
DESC DEPT
DESC EMP
2. Oprateurs arithmtiques
Ordre des oprateurs : * / + -
Ex1 : select ename, sal, sal + 300 from EMP;
Ex2 : select ename, sal, sal * 12 + 100 from EMP;
Ex3: select ename, sal, 12*(sal + 100) from EMP;

3. Les alias
Ex1: select ename as Nom, sal Salaire mensuel from EMP;
Ex2 : select ename Nom, sal*12 Salaire_annuelle from EMP;
4. La concatnation
Ex1 : select ename || job as Employes from EMP;
Ex2 : select ename || || est le|| ||job from EMP;
5. Lignes dupliques
select [Distinct] {*, col1, col2, } from nom de table;
select distinct deptno from EMP;
6. Slection de lignes (clause Where)

select [distinct] {* | colonne alias, }
from nom de table
where condition(s);
Ex : select ename, job, dept
From EMP
Where job = CLERK
7. Ordre de tri
select [Distinct] {* | col alis, }
from nom de table
where condition(s)
order by col1, col2, {ASC | DESC}
Ex: Select ename, job, deptno
From EMP
Where job = CLERK
Order by ename;
8. Oprateurs de comparaison
= , >, >=, <, <=, <>, !=
Ex : Select ename, sal, comm
From EMP
Where sal <= 1500;
9. Autres oprateurs de comparaison

between and
IN (list)
IS NULL
LIKE
Ex1: Select ename, sal, job From EMP Where sal between 800 and 2500;
Ex2: Select * From DEPT Where deptnoIN( 10, 20, 30);
Ex3: Select * From EMP Where comm IS NULL;

9. Loprateurs LIKE
Caractre jocker de loprateur LIKE :
% : remplace 0 n caractre (n = 1, 2, )
_ (soulign) : remplace un et un seul caractre
Ex1 : tous les employs (noms) qui commencent par M
Select * from EMP where ename LIKE M%;
Ex2 : tous les noms qui contiennent un E la 3ime position
Select * from EMP where ename LIKE _ _E%;
Ex3 : tous les noms qui se terminent par M
Select * from EMP where ename LIKE %M;
Ex4 : tous les noms dont le 1er caractre est S, le 3ime est O et le
dernier est T
Select * from EMP where ename LIKE S_O%T;
Ex5 : tous les noms qui contiennent un O
Select * from EMP where ename LIKE %O%;
10. La clause ESCAPE

Elle permet dspcialiser les caractres jockers _ et %. Le caractre
prcis derrire la clause ESCAPE permet la recherche des caractres _
et % dans une chane de caractres.
Ex : Select * from EMP where ename like %*_% ESCAPE *;
Recherche tous les employs dont le nom contient le caractre _
11. Les oprateurs logiques

AND : retourne vrai ou faux
OR : retourne vrai ou faux
NOT
Ex1 : select * from EMP where deptno NOT in (10, 30) AND sal < 4000;
Ex2 : select * from EMP where deptno NOT in (10, 30) OR sal < 4000;
12. Loprateur SOUNDEX

- SOUNDEX(chane) est une fonction qui permet une comparaison
phontique
- Pour faire une comparaison phontique entre 2 chanes :
SOUNDEX(chane1) = SOUNDEX(chane2)
Ex : slectionner les employs dont le nom ressemble ALIN, ALLAIN,
MALLER, FARD
Select * from EMP where SOUNDEX(ename) = SOUNDEX(ALIN);

13. Les fonctions caractres

LENGTH (chane) Longueur de la chane
UPPER (chane) Toutes les lettres de la chane en
majuscule
LOWER (chane) Toutes les lettres de la chane en
minuscule
INITCAP (chane) La 1re lettre de chaque mot de la
chane en majuscule, les autres en
minuscule
LPAD (chane, longueur, [chane]) Complter gauche par une chane de
caractres sur une longueur donne.
Ex : LPAD(Dupond,10,*#) =
*#*#Dupond
RPAD (chane, longueur, [chane]) Complter droite par une chane de
caractres sur une longueur donne.
Ex : RPAD(Dupond,10,*#) =
Dupond*#*#
LTRIM (chane[, caractres]) Supprime gauche des caractres
dans la chane.
Ex : LTRIM(Dupond, Du) =
pond
RTRIM (chane[, caractres]) Supprime droite des caractres dans
la chane.
Ex : RTRIM(Dupond, pond) =
Du
SUBSTR(chane, position [,longueur]) Extraction dune chane partir dune
position donne et sur une longueur
donne.
Ex : SUBSTR(Dupond,2,3) = upo
REPLACE(chane, car[, chane]) Remplace un ensemble de caractres.
Ex1:REPLACE(TUTU,U,OU) =
TOUTOU
Ex2:REPLACE(TATA,T) = AA

14. Les commandes NVL et DECODE

NVL(expression, valeur) :
Si expression IS NULL (non renseign) alors
Valeur;
Sinon expression;
Finsi;
Ex1 : slectionner le nom et la commission des employs, pour ceux dont la
commission nest pas renseigne, mettre 0.
Select ename, nvl(comm, 0) from EMP;
Ex2: slectionner le nom des employs, pour ceux qui ne sont pas
renseigns, remplacer par AAA.
Select nvl(ename, AAA) from EMP; Que se passe-t-il?
DECODE(expression, val1, result1[, val2, result2], [, default]) :

Si expression = val1 alors
Result1;
Sinon si expression = val2 alors
Result2;
..
sinon default;
finsi;
finsi;
Ex1: Afficher les employs, leur job et leur salaire, pour ceux gagnant 1500,
remplacer le salaire par ***.
Select ename, job, decode(sal,1500, ***, sal) from EMP;
Ex2: Afficher tous les employs de la table DEPT, pour ceux dont le deptno
=10 alors afficher 88 et deptno=20 alors afficher 99.
Select dname, loc, decode(deptno,10, 88, 20, 99, deptno) from DEPT;
15. Les fonctions de conversion de types

1. TO_CHAR(chane[, format])
Conversion dune expression (date ou numrique) en chane de caractres
selon un format de prsentation.
Formats numriques :
9 : Affichage de cette valeur si elles est diffrente de zro
0 : Affichage de zro gauche pour une valeur zro
$ : Affichage de la valeur prfixe par le signe $
, : Affichage de , lendroit indiqu
. : Affichage du point dcimal lendroit indiqu
Ex1 : select empno, to_char(empno, 9999) from EMP;
select empno, to_char(empno, 9999.99) from EMP;
select empno, to_char(empno, 009999.99) from EMP;
Ex2 : select sal, to_char(sal, $99,999) from EMP;

Formats date :
yyyy, year, yy (anne)
mm, non, month (mois)
dd, dy, day (jour)
hh: (heure)
mi: (minutes)
ss: (secondes)
Ex1 : select hiredate, to_char(hiredate, yyyy-mm-dd) from EMP;
Ex2 : select ename, to_char(hiredate, dd/month/yyyy) from EMP;
Ex3 : select to_char(hiredate, yyyy mm dd hh24:mi) from EMP;
2. TO_DATE(chane[, format])
Conversion d,une chane en date selon un format.
Ex1 : select ename, to_date(19-FEB-1998, dd-mon-yyyy) from EMP;
mme dimension
Ex2 : select ename, hiredate
from EMP
where hiredate = to_date(FEBRUARY 22 1981, month dd yyyy);
3. TO_NUMBER(chane) ou TO_DATE(chane[, format])

Conversion dune chane de caractres en nombre
Ex1 : select ename, to_number(1250) from EMP;
Ex2 : select ename, to_number(565, 999) from EMP;
Ex3 : select ename, to_number(1250.56, 9999.99) from EMP;
16. Utilisation des pseudocolonnes ROWID, USER et SYSDATE

1. ROWID : cest ladresse dune ligne (tuple), compos de trois
champs :
- numro de bloc dans le fichier
- numro de la ligne (dune table) dans le bloc
- numro de fichier
2. USER : lutilisation courant dOracle
3. SYSDATE : la date systme
Ex : select rowid, user, sysdate from sys.dual;
N.B. : la table dual appartient au systme, elle possde une seule colonne
(DUMMY).

17. Les fonctions Date

Last_day(date) Dernier jour du mois dune date donne.
Ex : Last_day(17-DEC-80) = 31-DEC-80
Next_day(date, chane) Date du prochain jour partir dune date
donne.
Ex : select next_day(sysdate, Monday)
from dual;
Add_months(date,n) Ajoute n mois une date donne.
Ex : select ename, hiredate,
add_months(hiredate,2) from EMP where
ename = SMITH;
Months_between(date1, date2) Nombre de mois entre deux dates.
Ex1 : select months_between(sysdate,
to_date(25-APR-2000)) from dual;
Ex2 : select months_between(to_date (
25-APR-2000)) from dual;
18. Les calculs verticaux

Les calculs verticaux font intervenir les valeurs dune colonne sur
lensemble ou un sous-ensemble des lignes ramenes par une requte.
Les fonctions de groupe :
COUNT : Nombre de lignes
MIN : Maximum
SUM : Somme
AVG : Moyenne
STDDEV : cart type, cest lcart par rapport la moyenne des valeurs
dun ensemble de valeurs(x2 ((x)2 / n) / (n-1)))
VARIANCE : variance, cest le carr de lcart type
Syntaxe :
Select [colonne,] fonction_groupe(colonne)
from nom_table
[where condition]
[group by colonne]
[order by colonne]
Ex1: select avg(sal), max(sal), min(sal), sum(sal) from EMP;
Ex2: select min(hiredate), max(hiredate) from EMP;
Ex3: select min(ename), max(ename) from EMP;
Ex4: select count(*) from EMP;
Ex5: select count(*) from EMP where deptno = 30;

Ex6 : Afficher le nombre de dpartement distinct dans la table EMP.

Select count (distinct (deptno)) from EMP;
Ex7 : select avg(comm) from EMP;
N.B. : les functions de groupe ignorant les colonnes de valeurs nulles

Ex8: select avg(nvl(comm, 0) from EMP;
La fonction NVL force les fonctions de groupe dinclure les valeurs nulles.
N.B. : toutes les colonnes dans le select, qui ne sont pas des fonctions de
groupe, doivent tre dans la clause GROUP BY.
Ex1: select deptno, avg(sal) from EMP group by deptno;
Ex2: select ename, deptno, avg(sal) from EMP group by deptno;
N.B. : La colonne du group by peut ne pas tre dans le select.

Ex: select avg(sal) from EMP group by deptno;
19. Utilisation du group by avec plusieurs colonnes

Ex: somme des colonnes par dpartement et par job
Select deptno, job, sum(sal) from EMP group by deptno, job;
N.B.: on ne peut pas utiliser les functions de groupe dans la clause WHERE.
Ex faux : select deptno, avg(sal)
from EMP
where avg(sal) > 2000 /* ERREUR : impossible*/
group by deptno;
Pour cela, on doit utiliser la clause HAVING pour les fonctions de groupe.
Ex vrai : select deptno, avg(sal)
from EMP
group by deptno;
HAVING avg(sal) > 2000 ; /* OK */
Syntaxe gnrale de la clause SELECT :

Select colonne, groupe_fonction
From noms_table
[Where condition]
[Group by expression de groupe]
[Having condition de groupe]
[Order by colonne]
Ex: La job, le total des salaries mensuel, pour chaque job avec la somme des
salaires > 5000, tri par ordre croissant sur la somme des salaires.
Select job, sum(sal)
From EMP
Group by job
Having sum(sal) > 5000
Order by sum(sal);

20. Requtes avec les oprateurs ensemblistes

Lobjectif est de manipuler les ensembles ramens par plusieurs select
laide des oprateurs ensemblistes.
UNION : lunion de deux requtes ou ensembles
INTERSECT : lintersection de requtes ou ensembles
MINUS : diffrence de deux requtes ou ensembles
Select from where oprateurs ensemblistes;
Select from where oprateurs ensemblistes;
Select from where [order by ];
Rgles :
- mme nombre de variables en projection
- correspondance de types
Rsultats :
- les titres des colonnes sont ceux du 1er select
- opration distinct est implicite
N.B. : UNION ALL : mme chose que UNION sauf que quelle ne fait pas
de tri pour liminer les doublons.
Ex1 : select deptno, empno, ename, sal from EMP where deptno = 10
UNION
Select deptno, empno, ename, sal from EMP30 where deptno=20;
N.B. :
- EMP et EMP30 ont la mme structure et le mme contenu.
- UNION ALL donne le mme rsultat pour Ex1 car il ny a pas de
doublons.
Ex2 : select empno, ename, sal from EMP where deptno = 10

UNION
Select empno, ename, sal from EMP30 where deptno = 10;
N.B. :
- Pour Ex2, UNION et UNION ALL donnent des rsultats diffrents car
il y a de doublons.
Ex3 : intersection entre les tables EMP et EMP30

select * from EMP
INTERSECT
Select * from EMP30;
Ex4 : difference entre EMP et EMP30 (dont deptno = 10)
select * from EMP

MINUS
Select * from EMP30 where deptno = 10;

21. Les commandes Commit, Rollback et Save point

COMMIT : confirmation des changements faites sur la BD
Rollback : infirmation des changements faites sur la BD
Save point :
Ex : 1. update
2. insert
3. Save point tiquette
4. delete .
5. update
6. Rollback to etiquette /* les chagements faits partir de ltape
3 sont infirms */
N.B. : Si on cre un second Save point avec le mme nom (tiquette) que le
prcdent, alors le 1r Save point est supprim
22. Les commandes de mise jour des donnes

1. Modification de lignes dune table
update <nom_table | nom_vue>
Set nom_colonne = <expression | ordre select>
[, nom_col2 = <expression | ordre select>]

where <critres de qualification>;
Ex1: Augmenter le salaire de chaque employ de 10%
Update EMP
Set sal = sal * 1.10;
Ex2: Mettre jour job et deptno de lemploy 7698, les nouveaux job et
deptno sont gaux au job et deptno de lemploy no 7499.
Update EMP
Set (job, deptno) = (select job, deptno from EMP where empno = 7499)
Where empno 7698;
Ex3: Mettre jour job et deptno de lemploy 7698
- le nouveau job est gal celui de lemploy 7499
- le nouveau deptno est gal celui de SALES de la table DEPT
(SALES est le nom du dpartement
Update EMP
Set job = (select job from EMP where empno = 7499),
deptno = (select deptno from DEPT where dname = SALES)
Where empno = 7698;
Ex4: Mettre jour la table EMP(mettre jour le no dept deptno=10 55
Update EMP
Set deptno = 55
Where deptno = 10;

N.B.: le deptno = 55 nexiste pas dans la table DEPT (la table DEPT est la
table matre, deptno est une cl primaire dans DEPT et est une cl
secondaire dans la table EMP, donc il y a violation des contraintes
dintgrit (voir aprs).
2. Suppression de lignes dune table

Delete from <nom_table | nom_vue>
Where <critres de qualification>;
N.B. : si pas de clause WHERE alors toutes les lignes de la table ou vue
sont supprimes (la table est vide mais elle existe toujours).
Ex1: supprimer lemploy dont ename = SCOTT

Delete from EMP where ename = SCOTT;
Rollback; (infirmer)
Ex2: supprimer les employs dont Hiredate est suprieure au 1er janvier
1987
Delete from EMP where hiredate > to_date(01-JAN-1987, dd-
mon-yyyy); /* contrainte dintgrit */
Ex3: supprimer les employs de la table EMP dont le nom de dept
dname=SALES
Delete from EMP where deptno = (select deptno from DEPT
where dname = SALES);
/* contrainte dintgrit */
Ex4: supprimer de la table DEPT le dpartement no 10.
Delete from DEPT where deptno = 10; /* contrainte dintgrit */
N.B. : Comme les tables EMP et DEPT sont relies par la cl deptno, donc
on ne peut pas supprimer nimporte quelle ligne des deux tables
(contrainte dintgrit).
N.B. : Si on fait les mmes exercices avec les tables EMP30 et DEPT30
qui ont la mme structure et le mme contenu que les tables EMP et
DEPT, mais nont pas de contraintes dintgrit (elles ne sont pas relies),
alors ont peu supprimer nimporte quelle ligne.
3. Insertion de lignes dans une table

3.1. INSERT INTO table[colonne1[, col2]] VALUE(val1[, val2]);
N.B. : - une seule ligne est insre avec cette syntaxe.
- Si les colonnes ne sont pas prcises alors insertion de toutes les
colonnes de la ligne.
Ex1: Insrer dans la table DEPT30 les valeurs 50, Dveloppement,
Montral correspondants aux colonnes deptno, dname, loc.
Insert into DEPT30 values(50, Dveloppement, Montral);

N.B. : ce nest pas la peine de prciser le nom des colonnes, car DEPT30
ne contient que ces 3 colonnes.
Ex2 : Insrer les valeurs empno=2296, ename=Bernard, sal = 2500,

deptno=20 dans la table EMP30.
Insert into EMP30(empno, ename, sal, deptno)
values(2296, Bernard, 2500, 20);
N.B. : l, on a precise les colonnes (empno, ename, sal, deptno) car il
existe dautres colonnes non concernes par linsertion.
3.2. Insertion de valeurs en utilisant des variables de substitution

Ex : Sous SQL*PLUS, on fait entrer des valeurs (ACCEPT) quon affecte
des variables quon va utiliser lors de linsertion.
ACCEPT dept_no PROMPT Entrer le no dept : 55
ACCEPT delpt_nom PROMPT Entrer le nom dept : Finance
ACCEPT location PROMPT Entrer location : Montral
Insert into DEPT30 values(&dept_no, &dept_nom, &location);
Select ename, sal, &&col_nom 1 seul prompt : lexcution :
From EMP Entrer la val de col_nom : deptno
Order by &col_nom
3.3. Insertion de lignes partir dune autre table

INSERT INTO nom_table [colonne1 [, col2 ]] ordre select;
Ex1: insrer dans EMP30 les employs de EMP dont le no dept=10.
Insert into EMP30 select * from EMP where deptno = 10;
Ex2: insrer dans DEPT30 toutes les lignes de la table DEPT
Insert into DEPT30 select * from DEPT;
23. Les fonctions numriques

ABS(n) Valeur absolue de n.
Ex: abs(-4) = 4 abs(4) = 4.
SIGN(n) Signe de n.
Ex: sign(-1) = -, sign(1) = +, sign(0) = 0.
CEIL(n) Plus petit entier >= n ou alors le plafond de n. Ex :
ceil(3.2) = 4, ceil(3) = 3.
FLOOR(n) Plus grand entier <=n ou alors le plancher de n. Ex. :
floor(3.5) = 3, floor(3) = 3.
MOD(m,n) Modulo de m, n, le reste de la division de m par n.
Ex: mod(4,2) = 0, mod(3,2)=1
POWER(m,n) m lev la puissance de n. (m**n ou mn)
Ex: power(4,2)=8, power(9,3)=27.
SQRT(n) Racine carre de n. (Erreur si n < 0)

ROUND(col | expr,n) Arrondi de colonne ou dexpression n dcimales.

Ex: Round(45.926, 2)=45.93,
Round(125.2)=125
Round(345.343, 2) = 345.34
Round(345.347, 2) = 345.35
Round(1600, -3) = 2000
Round(1100, -3) = 1000
xdate = 12-juillet-96
Round(xdate,mm) = 01-JUI-96
Round(xdate+4, mm) = 01-AUG-96
Round(xdate, yy) = 01-JAN-97
TRUNC(col | expr, n) Tronque la colonne ou lexpression n dcimales (si n
est ngatif, les nombres qui sont gauche du point
dcimal sont tronqus zro.
Ex: Trunc(2500,-3)=2000
Trunc(2300,-3)=2000
Trunc(345.343,2)=345.34
Trunc(345.347,2)=345.34
Trunc(xdate,mm) =01-juillet-96,
Trunc(xdate+4, mm) =01-07-96
Trunc(xdate, yy) = 01-01-97
24. Le langage de dfinition de donnes

- Les types de donnes Oracle:
CHAR(taille) Chane de caractres de longueur fixe (1 255 octets)
VARCHAR2(taille) Chane de caractres de longueur variable (1 2000
octets)
DATE Format par dfaut : DD-MON-YY
NUMBER(n1[,n2]) Type numrique
n1 : nombre de digits du dcimal (1 38)
n2 : nombre de digits aprs la vigule
LONG Type texte (taille jusqu 2G octets)
RAW Type binaire (1 255 octets)
LONG RAW Type binaire long (jusqu 2G octets)
N.B. : une seule colonne de type LONG ou LONG RAW par table.

25. Cration, suppression, vidange et renommage de tables

1. Cration dune table :
CREATE TABLE <user>.<nom_table>
(
<def_colonne1>,
<def_colonne2>,
..
<def_colonnen>
[contraintes_table]
);
avec :
< def_colonne> = nom_colonne Type_colonne [Default expr]
[contrainte_col]
o :
[contrainte_col] = CONSTRAINT <nom_contrainte>
[NOT] NULL | UNIQUE | PRIMARY KEY |
REFERENCES[<user>].<table> (col1, col2,)
[ON DELETE CASCADE] | CHECK (condition);
[contraintes_table] = CONSTRAINT <nom_contrainte>

UNIQUE(col1,) | PRIMARY KEY(col1,)
FOREIGN KEY(col1,)
REFERENCES table(col1,)
[ON DELETE CASCADE] | CHECK(condition);
2. Suppression dune table
DROP TABLE nom_table;
- toutes les donnes ainsi que la structure de la table sont supprimes
- tous les index sont supprims
- on ne peut pas utiliser de ROLLBACK
3. Vidange dune table
TRUNCATE TABLE nom_table;
- supprime toutes les lignes de la table
- libre lespace utilis par la table
- on ne peut pas utiliser de ROLLBACK contrairement delete from
4. Renommage dune table
RENAME ancien_nom_table TO nouveau_nom_table;
Pour changer le nom dune table, une vue, une squence ou un
synonyme.
Ex : RENAME DEPT TO departement
N.B. : vous devez tre le propritaire de la table

5. Verrouillage dune table

LOCK TABLE [user.]nom_table IN LOCKMODE MODE [nowait]
user: propritaire de la table

LOCKMODE : Row Share, Row Exclusive, Share Update, Share
Exclusive
nowait : dans le cas o la table aurait t verrouille par un autre
utilisateur, Oracle va retourner le contrle cet utilisateur.
Exclusive : permet linterrogation sur la table mais pas dautres activits
Share : permet des interrogations partages mais pas update.
Row Share : quivalent Share Update (inclus pour compatibilit)
Permet aux autres usagers laccs la table. Interdit aux
utilisateurs de verrouiller toute la table pour un accs
exclusif
Row Exclusive : mme chose que Row Share, mais aussi interdit le
verrouillage en mode partag. Row Exclusive Lock est
obtenu automatiquement lors de Update, Insert ou
Delete.
Share Row Exclusive :
Ex: verrouiller la table EMP en mode exclusif mais ne pas attendre
si un autre utilisateur a dj verrouill la table.
LOCK TABLE EMP IN Exclusive MODE nowait

26. Les contraintes dintgrit

- elles font respecter les rgles dune table
- elles prviennent les suppressions et les mises jour dune table
- il existe cinq (5) types de contraintes
o NOT NULL :
- dfinie au niveau colonne seulement
- ne doit contenir de valeurs nulles
Ex: deptno number(2) CONSTRAINT dept_num NOT NULL;
Nom de la contrainte(facultatif)
N.B. : si on ne donne pas de nom une contrainte, cest le

systme qui va donner un nom alatoire cette contrainte.
o UNIQUE :
- pas de duplication de colonne (valeur unique)
- peut tre dfinie soit au niveau colonne, soit au niveau
table
Ex: Plnom char(12) NOT NULL UNIQUE; /*niveau colonne*/
o PRIMARY KEY :
- identifiant unique pour chaque ligne de la table
- une seule cl primaire par table
table
Au niveau colonne
PL number(4) CONSTRAINT pilote_num PRIMARY KEY,
Ou
PL number(4) PRIMARY KEY,
Au niveau table :
PL number(4), CONSTRAINT pilote_num PRIMARY
KEY(PL),
o FOREIGN KEY :
- doit tre relie une table parent
- dfinie soit au niveau table ou colonne
Au niveau colonne :
Ex : deptno number(4) CONSTRAINT num_dept
REFERENCES DEPT(deptno) /* DEPT : table parent */
Au niveau table :
Ex : deptno number(4),
.
CONSTRAINT num_dept FOREIGN KEY
REFERENCES DEPT(deptno)
ON DELETE CASCADE

N.B.:
REFERENCES identifie la table et la colonne parent.
ON DELETE CASCADE : indique que lorsquune ligne
de la table parent est supprime alors les lignes
correspondantes dans la table (fille) sont aussi
supprimes.
Remarque : Sans loption ON DELETE CASCADE, la ligne

dans la table parent ne peut pas rfrence dans
une table(fille).
Ex : on ne peut pas supprimer le dpartement no. 10 de la table
DEPT car il y a des employs dans la table EMP qui travaillent
dans le dpartement no. 10.
o CHECK :
table
- dfinit les conditions que doit satisfaire une colonne
- une colonne peut avoir plusieurs contraintes CHECK
Ex : deptno number(2) CONSTRAINT c_k_deptno
CHECK (deptno between 10 and 99),
N.B. : on peut visualiser les contraintes au niveau du
dictionnaire de donnes. (tables systme
USER_CONSTRAINTS et USER_CONS_COLUMNS)
Ex :
Select substr(constraint_name, 1, 10), constraint_type,
search_condition /* le contenu de la contrainte*/
From USER_CONSTRAINTS
Where table_name = DEPT;
[default expr] : permet de donner une valeur par dfaut une

colonne.
Ex: adr char(20) default Montral,
N.B. : Default nest pas une contrainte.
Ex1: crer la table DEPT dont la structure est :

deptno numrique sur 2 positions cl primaire
dname caracrtre sur 14 positions
loc caractre sur 13 positions
CREATE TABLE DEPT
(deptno numer(2) primary key,
dname varcha2(14),
loc varchar2(13));

Ex2: crer la table EMP dont la structure est :

empno numrique sur 4 positions cl primaire
ename caractre sur 10 positions non nul
job caractre sur 9 positions
mgr numrique sur 4 positions
hiredate date valeur par dfaut SYSDATE
sal numrique(7,2) non nul est sal > 500
comm numrique(7,2)
deptno numrique sur 2 positions cl trangre
CREATE TABLE EMP
(empno number(4) primary key,
ename varchar2(10) NOT NULL,
job varchar2(9),
mgr number(4),
hiredate date default sysdate,
sal number(7,2) not null check(sal > 500),
comm number(7,2),
deptno number(2),
foreign key(deptno) references EMP(deptno)
);
27. Modification la structure dune table

(ne pas confondre avec update). Elle se fait avec la syntaxe ALTER TABLE
qui permet de :
3. Ajouter une colonne une table (dj existante)
4. Modifier une colonne existante
5. Dfinir une valeur par dfaut une nouvelle colonne
6. Ajouter ou supprimer une contrainte (mais on ne peut modifier une
contrainte)
7. Activer ou dsactiver une contrainte
N.B. : on ne peut pas supprimer une colonne
1. Ajouter une colonne une table (dj existante)

ALTER TABLE nom_table ADD(colonne type [valeur par dfaut]);
Ex: ajouter une colonne AGE la table EMP. La valeur par dfaut est
30.
Alter table EMP add(AGE number(2) default 30;
2. Modifier une colonne dune table (dj existante)

ALTER TABLE nom_table MODIFY(colonne type [valeur par dfaut]);
Ex: modifier la colonne ename de la table EMP varchar2(20).
Alter table EMP modify(ename varchar2(20));
3. Dfinir une valeur par dfaut une nouvelle colonne

Voir les exemples du 1) et 2) ci-dessus.

4. Ajouter ou supprimer une contrainte

4.1. Ajouter une contrainte
ALTER TABLE nom_table ADD [CONSTRAINT nom_contrainte]
type (colonne);
Ex: ajouter la contrainte 10 <= deptno <= 99 pour la table DEPT.
Alter table DEPT add CONSTRAINT c_k_dept CHECK(deptno
between 10 and 99));
4.2. Supprimer une contrainte
ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte;
Ex1: supprimer la contrainte c_k_dept de la table DEPT
Alter table DEPT drop constraint c_k_dept;
Ex2: Alter table DEPT drop primary key cascade; /* supprime la
contrainte primary key aussi que la contrainte de la cl trangre
dans la table DEPT.
5. Activer ou dsactiver une contrainte

5.1. Activer une contrainte
ALTER TABLE nom_table ENABLE CONSTRAINT nom_contrainte;
5.2. Dsactiver une contrainte
ALTER TABLE nom_table DISABLE CONSTRAINT nom_contrainte;
28. Visionner une contrainte

On peut visionner les contraintes grce aux tables du dictionnaire de
donnes. Ces tables sont USER_CONSTRAINTS et
USER_CONS_COLUMNS.
Ex1 : Select substr(constraint_name, 1, 10), constraint_type,
search_condition
From USER_CONSTRAINTS
Where table_name = EMP;
Avec:
SEARCH_CONDITION : nous donne le contenu de la contrainte
et CONSTRAINT_TYPE : C : check
P : cl primaire
R : cl trangre
U : unique
Ex2 : Select constraint_name, column_name
From USER_CONS_COLUMNS
Where table_name =DEPT;

30. Cration dindex

- Un document est un objet de la base de donnes
- Il est utilis par le serveur dOracle pour accder plus rapidement aux
lignes dune table en utilisant des pointeurs.
- Il est utilis automatiquement par le serveur
- Les index sont crs soit
Automatiquement : un index est cre automatiquement
quand on dfinit une cl primaire ou une colonne unique
lors de la cration et la dfinition dune table
Manuellement : on peut crer manuellement des index sur
des colonnes pour augmenter la vitesse daccs aux lignes
dune table
Syntaxe :
CREATE INDEX nom_index ON nom_table(colonne1 [, col2] );
Ex: create index emp_ename_tdx on EMP(ename);
Quand doit-on crer un index :

- Une colonne est utilise frquemment dans une clause WHERE ou
dans une condition de jointure
- Une colonne contient beaucoup de valeurs
- Une colonne contient un grand nombre de valeurs nulles
- Quand plus de deux colonnes sont frquemment utilises ensemble
dans une clause WHERE ou une condition de jointure
- Quand une table est trs large (beaucoup de lignes) et les requtes
rcuprent moins de 2 4% des lignes
Quand doit-on ne pas crer un index :

- Quand la table est petite
- Quand les colonnes ne sont pas utilises frquemment comme tant
une condition dans une requte
- Quand les requtes qui accdent cette table rcuprent plus de 4%
des lignes
- Quand la table est mise jour frquemment
N.B. :
- Pour supprimer un index : DROP INDEX nom_index
- Les tables systme (dictionnaire de donnes) qui contiennent les
informations sur les index sont :
USER_IND_COLUMNS compose du nom de lindex,
nom de la table correspondante et du nom de la colonne
dindex
USER_INDEXES compos du nom de lindex et est-ce
quil est unique ou non (uniqueness)

Ex :
Select IC.index_name, IC.column_name,
IC.colum_position col_pos, IX.uniqueness
From USER_INDEXES IX, USER_IND_COLUMNS IC
Where IC.index_name = IX.index_name
And IC.table_name = EMP;
Rsultat:
Index_name Column_name Col_pos Uniqueness
EMP_ename_PK empno 1 Unique
EMP_ename_IDX Ename 1 Nonunique
31. Cration des synonymes

CREATE [PUBLIC] SYNONYM [<USER.>]<nom_synonyme>
FOR [<USER.>]<objet>; /* Objet : est gnralement une table */
PUBLIC : cre un synonyme accessible tous les utilisateurs
Objet : identifie lobjet et pour lequel le synonyme est cre.
N.B.:
- Le nom dun synonyme priv doit tre distinct de tous les objets
appartenant au mme utilisateur
- Un synonyme est utilis pour la scurit et la facilit de manipulation
- Son utilisation abusive augmente le temps dexcution des requtes
- Sert rfrencer un objet sans indiquer son propritaire
- Fournit un autre nom un objet
- On peut galement crer des synonymes pour des vues, des squences,
des procdures, et mme pour des synonymes
- Lobjet rfrenc ne peut tre contenu dans un package
- Toutes les modifications effectues sur les synonymes se rpercutent sur
la table correspondante et vis versa
- Pour supprimer un synonyme : DROP SYNONYM nom_synonyme;
Ex1: crer un synonyme pour chacune des tables EMP et DEPT
CREATE SYSNONYM syn_emp FOR EMP;
CREATE SYNONYM syn_dept FOR DEPT;
Ex2: supprimer le synonyme syn_dept
DROP SYNONYM syn_dept;

32. Cration des squences

- gnrent automatiquement des nombres uniques
- objet partageable
- sont utilises pour crer des valeurs pour les cls primaires
- remplace le code (insert into )
- augmente la vitesse daccs aux valeurs de squence (cache)
- les valeurs sont mises en mmoire ce qui implique un accs plus rapide ces
valeurs
Syntaxe :
CREATE SEQUENCE nom_squence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
o :
INCREMENT BY n : Pas = n, si nest pas spcifi, Pas=1(par dfaut)
START WITH n : cest le 1er nombre de la squence(valeur par dfaut=1)
MAXVALUE n : cest la valeur max que la squence peut gnrer
NOMAXVALUE : (par dfaut) un max de 1027.
MINVALUE n : cest la valeur min de la squence
NOMINVALUE : (par dfaut) un min de (1027).
CYCLE : la squence peut gnrer des valeurs aprs avoir atteint soit le
max soit le min
NOCYCLE : (par dfaut) ne peut gnrer des valeurs si > max et des
valeurs < min.
CACHE n : spcifie le nombre (n) de valeurs que le serveur Oracle garde
en mmoire
NOCACHE : (par dfaut) le serveur Oracle garde 20 valeurs
Crer une squence

Ne pas utiliser de cycle(ne pas gnrer des valeurs>MAXVAL et <MINVAL)
CREATE SEQUENCE dept_deptno
INCREMENT BY 1
START WITH 91
MAXVALUE 100
NOCACHE
NOCYCLE;
Select dept_deptno.currval from dual;

Select dept_deptno.nextval from dual;

Cette commande cre une squence Dept_deptno qui sera utilise pour la
colonne deptno de la table DEPT.
La squence commence par 91
La valeur max = 100
Nocycle : pas de valeur > 100
Nocache : 20 valeurs en mmoire
Table systme : USER_SEQUENCES

Ex:
select sequence_name, min_value, max_value, increment_by, last_number
From USER_SEQUENCES
Les pseudocolonnes : NEXTVAL, CURRVAL

Une fois que la squence cre, on peut lutiliser pour crer des nombres
squentiels pour une table, et ce grce a NEXTVAL et CURRVAL.
NEXTVAL : est utilise pour des extractions successives de nombres de la
squence spcifie nom_seq.nextval.
Une fois que seque.nextval est rfrence (excute) un nouveau nombre est
gnr et le no de squence courant est plac dans CURRVAL.
NEXTVAL : retourne la valeur suivante de la squence
CURRVAL : obtient la valeur courante de la squence
On peut utiliser CURRVAL, NEXTVAL : select, update (set), insert
On ne peut pas utiliser CURRVAL, NEXTVAL :
- select dans une vue
- un select avec distinct
- un select avec group by, having, ordre by
- une sous-requte dans une select, delete ou update
- valeur par dfaut (DEFAULT) dans Create Table, Alter Table
Modifier une squence : ALTER SEQUENCE nom_squence; (avoir le
privilge)
Supprimer une squence : DROP SEQUENCE nom_squence;

33. Commentaires sur les tables et les colonnes

COMMENT ON TABLE nom_table | COLUMN table.colonne
IS chane de caractre;
N.B. : les commentaires sont insrs dans le dictionnaire de donnes. Leur
consulatation se fait travers les tables suivantes :
- USER_COL_COMMENTS (pour les colonnes)
- USER_TAB_COMMENTS (pour les tables)
Ex: Comment on column EMP.empno is Numro identifiant un employ;
Pour supprimer un commentaire : COMMENT ON COLUMN EMP.empno
IS ;
34. Cration des vues

- Une vue est une table logique qui permet laccs aux donnes dune ou
plusieurs tables de faon transparente
- Une vue ne contient aucune lignes, les donnes sont stockes dans les
tables
- Les vues sont utilises pour
Fournir un niveau supplmentaire de scurit sur les tables,
ainsi on peut restreindre pour un utilisateur donn, laccs
quelques lignes ou colonnes dune table
Masquer la complexit : une table peut tre la jointure de N
tables
Syntaxe :
CEARTE [OR REPLACE] [FORCE | NOFORCE] VIEW nom_vue
[(alias_col1, alias_col2, )]
AS requte SQL
WITH CHECK OPTION [CONSTRAINT nom_contrainte]
[WITH READ ONLY]
O :
OR REPLACE : permet de supprimer puis recrer la vue si
elle existe dj
FORCE : ignore les erreurs et cre la vue (mme si la
ou les table(s) nexiste(nt) pas (rarement
utilise)
NO FORCE : par dfaut (cre la vue uniquement si la ou
les table(s) existe(nt))
ALIAS : spcifie les noms des colonnes ou
expressions slectionnes par le requte SQL
(alias)
WITH CHECK OPTION : spcifie que seulement les lignes accessibles
par la vue peuvent tre modifies ou insres
nom_contrainte : cest le nom affect la contrainte With
Check Option
WITH READ ONLY : spcifie que les oprations DML ne peuvent
tre utilises dans la vue (en lecture
seulement)

Remarques :
- La modification dune table affecte la vue
- Le corps dune table ne peut contenir la clause ORDER BY ou FOR
UPDATE
- On ne peut pas effectuer des insertions, des mise jour et des
suppressions sur une vue contenant une jointure, des oprations
ensemblistes, des fonctions de groupe, la clause GROUP BY et
lopration DISTINCT
- Pour supprimer une vue : DROP VIEW nom_vue;
- Les tables systme : USER_VIEWS et USER_CATALOG (CAT)
Ex1: crer une vue sur la table EMP(empvu10) qui contient 3 colonnes :
empno, ename, job (slectionner uniquement les employs du
dpartement no 10. Donner les alias aux colonnes.
CREATE VIEW empvu10(num, nom, emploi)
AS select empno, ename, job from EMP where deptno = 10;
Verification:
Select * from empvu10; /* on a 3 lignes (dept 10)*/
Si on insre des lignes dans la vue (INSERT INTO empvu10.) les
lignes sont ajoutes dans la table EMP.
Ex2 : crer une vue (dept_vue) sur la table EMP contenant le no dept, le
salaire max, min et moyen par dpartement
CREATE OR REPLACE VIEW dept_vue(num_dept, sal_min,
sal_max, sal_moy)
AS select deptno, min(sal), max(sal, avg(sal) from EMP group by
deptno;
N.B.: on ne peut pas faire de mise jour, suppression, insertion car
existence de fonctions de groupe et group by.
Ex3 : crer une vue (empvue20) sur la table EMP qui slectionne tous les
employs qui travaillent dans le dept mo 20. (ajouter la contrainte
With Check Option)
CREATE OR REPLACE VIEW empvue20
AS select * from EMP where deptno = 20 with check option;
Verification:
- si on veut insrer un employ dont le no dept = 30 alors
impossible (with check option)
- si on veut mettre jour le no dept = 10 pour un employ de la
vue ceci est impossible
Ex4: crer une vue (en lecture seulement) empvue10 sur la table EMP
(deptno=10)
CREATE OR REPLACE VIEW empnvue10 AS select * from EMP
where deptno = 10 with read only
Cette vue est en lecture seulement, donc on ne peut pas faire des
insertions, des mises jour ou des suppressions

35. Les jointures

Quest-ce quune jointure?
- Une jointure permet de slectionner des donnes de plusieurs tables
- Les lignes dune table peuvent tre jointes aux lignes des autres tables en
utilisant des colonnes communes que sont les cls primaires et
secondaires
- Les conditions de jointure sont crites dans la clause WHERE
- Prfixer les noms des colonnes avec le nom de table quand le mme nom
de la colonne apparat dans lus dune table
Select table1.colonne, table2.colonne
From table1, table2
Where table1.colonne1 = table2.colonne2;
Ou :
Select t1.colonne, t2.colonne
From table1 t1, table2 t2
Where t1.colonne1 = t2.colonne2;
Les types de jointure

1. Le produit cartsien
2. quijointure (ou jointure simple)
3. Non-quijointure
4. Jointure externe
5. Autojointure (self join)
1. Le produit cartsien : un produit cartsien est gnr si la condition de

jointure est omise
Ex:
EMP (14 lignes DEPT (4lignes)
Empno Ename .. deptno deptno dname loc
Select ename, dname from EMP, DEPT; /* 14*4=56 lignes */

ename dname


2. quijointure (jointure simple): un produit cartsien est gnr si la

condition de jointure est omise
EMP (14 lignes) DEPT (4lignes)
Empno Ename .. deptno deptno dname loc
7839 King 10 10 Accounting NY
7698 Blake 30 30 Sales Chicago
7782 Clarke 10 10 Accounting NY

Cl Cl
trangre primaire
Pour dterminer le nom de dpartement dun employ, on compare le no
dept de la table EMP avec le no dept de la table DEPT. La relation entre
les tables EMP et DEPT est une quijointure ou jointure simple de la
colonne deptno des tables EMP et DEPT
Ce type de jointure implique les 2 cls primaire et secondaire
N.B. : lutilisation des tables prfixes augmente la performance
Rcupration de lignes avec une quijointure :

- spcifier le nom des colonnes dans lordre SELECT
- spcifier les tables utiliser
- joindre les tables dans la clause WHERE
Exp1: Afficher le no, le nom et le sal des employs ainsi que le no dept,
le nom de dept correspondant et sa localisation
Select E.empno, E.ename, E.sal, D.deptno, D.dname, D.loc
From EMP E, DEPT D
Where E.deptno = D.deptno;
Exp2: slectionner empno, ename, deptno et loc(DEPT) pour lemploy
KING.
Select E.empno, E.ename, E.deptno, D.loc
From EMP E, DEPT D
Where E.deptno = D.deptno
And E.ename = KING;
N.B. : Lutilisation des alias permet une criture plus courte et prend
moins despace mmoire.

Jointure de plus de deux tables
Customer Order Item

name custid custid ordid ordid itemid
Ex: Afficher name, ordid, total, item, itemtot,o name=TKB sport shop
Select C.name, O.ordid, I.itemid, I.itemtot, O.total

From Customer C, Ordre O, Item I
Where C.custid = O.custid
And O.ordid = I.itemid
And C.name = TKB sport shop;
3. Non-quijointure :
EMP SALGRADE
Empno Ename sal grade losal hisal
7839 King 5000 1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
- le salaire dans EMP est entre losal et hisal de SALGRADE, cest un lien
indirect
- la relation est obtenue en utilisant un oprateur autre que lgalit(=)
(between, <=, >=, <, >)
Ex : select e.ename, e.sal, s.grade

From EMP e, SALGRADE s
Where e.sal between s.losal and s.hisal;

4. Jointure externe :
EMP DEPT
Ename deptno deptno dname
King 10 10 Accounting
Blake 30 30 Sales
Clarke 10 10 Accounting
40 Operation
Le dpartement 40 nexiste pas dans la table

EMP (il ny a pas demploys qui travaillent
dans le dpartement 40 (opration)
- on utilise la jointure externe pour rcuprer les lignes qui ne remplissent
pas la condition de jointure
- loprateur de la jointure externe est le signe (+)
- le signe (+) doit tre du ct de la cl trangre
Ex: slectionner ename de EMP, deptno, dname de DEPT pour les
deptno qui nexistent pas dans EMP les afficher quand mme.
Select E.ename, D.deptno, D.dname
From EMP E, DEPT D
Where D.deptno = E.deptno(+);
5. LAutojointure :
EMP(employ) EMP(Manager)
Empno Ename mgr empno ename
7839 King - 7839 King
7698 Blake 7839 7839 King
7782 Clarke 7839 7839 King
7566 Jones 7839 7698 Blake
7654 Martin 7698 7698 Blake
7499 Allen 7698
- On utilise une autojointure lorsquon a besoin de joindre une table
elle mme.
- Pour trouver le nom du manager de chaque employ, on a besoin (ou
doit) joindre la table EMP avec elle-mme.
Ex: pour trouver le nom du manager de lemploy Blake on doit :
- Trouver lemploy Blake dans EMP (ename)
- Trouver le no manager (mgr) de Blake (7839)
- Puis trouver le nom du manager dont le no employ est gal
7839 qui est King
Ex : Trouver le manager de chaque employ de la table EMP
Select e1.ename, e2.ename
From EMP e1, EMP e2
Where e1.mgr = e2.mgr;

13. QUIZ
1. Which of the following statement contains an error?
A. select * from EMP where empid = 493945;
B. select empid from EMP where empid = 493945;
C. select empid from EMP;
D. select empid where empid = 56949 and lastname = SMITH;
2. Which of the following correctly describes how to specify a column alias?
A. Place the alias at the beginning of the statement to describe the table
B. Place the alias after each column, separated by a space, to describe the column
C. Place the alias after each column, separated by a comma, to describe the column
D. Place the alias at the end of the statement to describe the table
3. The nvl() function?
A. Assists in the distribution of output across multiple column
B. Allows you to specify alternate output for non-NULL column values
C. Allows you to specify alternate output for NULL column values
D. Nullifies the value of the column output
4. Output from table called PLAYS with two columns, Play_Name and Author,
is shown next. Which of the following SQL statements produced it?
PLAY_TABLE
----------------------------------------------------------------
Midsummer Nights Dream, SHAKESSPEARE
Waiting for Godot, BECKETT
The glass menagerie, WILLIAMS
A. select Play_name|| Author from PLAYS;
B. select Play_name, Author from PLAYS;
C. select Play_name||, || Author from PLAYS;
D. select Play_name||, || Author Play_table from PLAYS;
5. Issuing the command define _editor=emacs will produce which outcome?
A. The EMACS editor will become the SQL*Plus default text editor
B. The EMACS editor will start running immediately
C. The EMACS editor will no longer be used by SQL*Plus as the default text editor
D. The EMACS editor will be deleted from the system
6. Which function can best be categorized as similar in function to an if-then-else
statement?
A. sqrt()
B. decode()
C. new_time()
D. rowidtochar()
7. Which of the following are number functions?(choose three of the four)
A. sinh()
B. to_number()
C. sqrt()
D. round

8. You issue the following statement. What will be displayed if the empid selected
is 604994?
Select DECODE(empid, 38475, Terminated, 60494, LOA, ACTIVE)
A. 60494
B. LOA
C. Terminated
D. ACTIVE
9. Which of the following is a valid SQL statement?
A. select to_char(nvl(sqrt(59483), 0)) from dual;
B. select to_char(nvl(sqrt(59483), INVALID)) from dual;
C. select (to_char(nvl(sqrt(59483), 0)) from dual;
D. select to_char(nvl(sqrt(59483), TRUE)) from dual;
10. The appropriate table to use when performing arithmetic calculations on
values defined within the select statement (not pulled from a table column) is
A. EMP
B. The table containing the column values
C. DUAL
D. An Oracle-defined table
11. Which of the following keywords are used in order by clauses?(choose two)
A. abs
B. asc
C. desc
D. disc
12. Which of the following statements are not true about order by clauses?
A. Ascending or descending order can be defined with the asc or desc keywords
B. Only one column can be used to define the sort order in an order by clause
C. Multiple columns can be used to define sort order in an order by clause
D. Columns can be represented by numbers indicating their listed order in the
select clause within order by
13. Which lines in the following select statements contain errors?
Select decode(empid, 58385, INACTIVE, ACTIVE) empid
from EMP
Where substr(lastname,1,1) > to_number(S)
and empid > 02000
order by empid desc, lastname asc;
A. select decode(empid,58385, INACTIVE, ACTIVE) empid
B. from EMP
C. where substr(lastname,1,1) > to_number(S)
D. and empid > 02000
E. order by empid desc, lastname asc
F. There are no errors in this statement

14. Which of the following is not a group function?

A. avg()
B. sqrt()
C. sum()
D. max()
15. In order to perform an inner join, which criteria must be true?
A. The common columns in the join do not need to have shared values
B. The tables in the join need to have common columns
C. The common columns in the join may or may not have shared values
D. The common columns in the join must have shared values
16. Once defined, how long will a variable remain defined in SQL*Plus?
A. Until the database is shut down
B. Until the instance is shut down
C. Until the statement completes
D. Until the session completes
17. You want to change the prompt Oracle uses to obtain input from a user.
Which two of the following choices are used for this purpose?(choose two)
A. Change the prompt in the config.ora file
B. Alter the prompt clause of the accept command
C. Enter a new prompt in the login.sql file
D. There is no way to change a prompt in Oracle
18. No search criteria for the EMP table are known. Which of the following
options are appropriate for use when search criteria is unknown for
comparison operations in a select statement?(choose two)
A. select * from EMP where empid = &empid;
B. select * from EMP where empid = 69494;
C. select * from EMP where empid = (select empid from invoice where
invoice_no = 4399485);
D. select * from EMP;
19. The default character for specifying substitution variables in select statement is
A. Ampersand
B. Ellipse
C. Quotation marks
D. Asterisk
20. A user is setting up a join operation between tables EMP and DEPT. There
are some employees in the EMP table that the user wants returned by the
query, but the employees are not assigned to department heads yet. Which
select statement is most appropriate for this user?
A. select e.empid, d.head from EMP e, DEPT d;
B. select e.empid, d.head from EMP e, DEPT d where e.deptno = d.deptno;
C. select e.empid, d.head from EMP e, DEPT d where e.deptno = d.deptno (+);
D. select e.empid, d.head from EMP e, DEPT d where e.deptno (+) = d.deptno;

21. Which of the following uses of the having clause are appropriate?(choose three)
A. To put returned data into sorted order
B. To exclude certain data groups based on known criteria
C. To include certain data groups based on unknown criteria
D. To include certain data groups based on known criteria
22. A Cartesian product is
A. A group function
B. Produced as a result of join select statement with no where clause
C. The result of fuzzy logic
D. A special feature of Oracle server
23. The default character that identifies runtime variables is changed by
A. Modifying the initsid.ora file
B. Modifying the login.sql file
C. Issuing the define variablename command
D. Issuing the set define command
24. Which line of the following select statement will produce an error?
A. select dept, avg(salary)
B. from EMP
C. group by empid;
D. There are no errors in this statement
25. Which of the following integrity constraint automatically create an index
when defined?(choose two)
A. Foreign keys
B. Unique constraints
C. NOT NULL constraints
D. Primary keys
26. Which of the following dictionary views gives information about the position
of a column in a primary key?
A. ALL_PRIMARY_KEYS
B. USER_CONSTRAINTS
C. ALL_IND_CONSTRAINTS
D. ALL_TABLES
27. Developer ANJU executes the following statement :
Create table ANIMALS as select * from MASTER.ANIMALS;
What is the effect of this statement?
A. A table named ANIMALS will be created in the MASTER schema with the
same data as the ANIMALS table owned by ANJU
B. A table named ANJU will be created in the ANIMALS schema with the same
data as the ANIMALS table owned by MASTER
C. A table named ANIMALS will be created in the ANJU schema with the same
data as the ANIMALS table owned by MASTER
D. A table named MASTER will be created in the ANIMALS schema with the
same data as the ANJU table owned by ANIMALS

28. User JANKO would like to insert a row into the EMPLOYEE table that has
three columns : empid, lastname, and salary. The user would like to enter
data for empid 59694, lastname harris, but no salary. Which statement
would work best?
A. insert into EMPLOYEE values(59694, Harris, NULL);
B. insert into EMPLOYEE values(59694, Harris);
C. insert into EMPLOYEE(empid, lastname, salary) values(59694, Harris);
D. insert into EMPLOYEE (select 59694 from Harris);
29. Which components are parts of an entity-relationship diagram?(choose two)
A. Referential integrity constraints
B. Entities
C. Relationships
D. Triggers
30. Which of the following choices is the strongest indicator of a parent/child
relationship?
A. Two tables in the database are named VOUCHER and VOUCHER_ITEM,
respectively
B. Two tables in the database are named EMPLOYEE and PRODUCTS,
respectively
C. Two tables in the database were created on the same day
D. Two tables in the database contain none of the same day
31. Which of the following are valid database datatypes in Oracle?(choose three)
A. CHAR
B. VARCHAR2
C. BOOLEAN
D. NUMBER
32. Omitting the where clause from a delete statement has which of the following
effects?
A. The delete statement will fail because there are no records to delete
B. The delete statement will prompt the user to enter criteria for the deletion
C. The delete statement will fail because of syntax error
D. The delete statement will remove all records from the table
33. Which line of the following statement will produce an error?
A. Create table GOODS
B. (goodno number,
C. Good_name varchar2(20) check(good_bame in(select name from AVAIL_GOODS));
D. Constraint pk_goods_01
E. Primary key (goodsno));
F. There are no errors in this statement

34. The transaction control that prevents more than one user from updating
data in a table is which of the following?
A. Locks
B. Commits
C. Rollbacks
D. Savepoints
35. Dropping a table has which of the following effects on a nonumerique index
created for the table?
A. No effects
B. The index will be dropped
C. The index will be rendered invalid
D. The index will contain NULL values
36. Which of the following statements about indexes is true?
A. Columns with low cardinality are handled well by B-tree indexes
B. Columns with low cardinality are handled poorly by bitmap indexes
C. Columns with high cardinality are handled well by B-tree indexes
37. To increase the number of nullable columns for a table:
A. Use the alter table statement
B. Ensure that all column values are NULL for all rows
C. First, increase the size of adjacent column datatypes, and then add the column
D. Add the column, populate the column, and then add the NOT NULL constraint
38. To add the number of columns selected by a view:
A. Add more columns to the underlying table
B. Issue the alter view statement
C. Use a correlated subquery in conjunction with the view
D. Drop and re-create the view with references to select more columns
39. A user issues the statement select count(*) from EMPLOYEE. The query
takes an inordinately long time and returns count of zero. The most cost-
effective solution is :
A. Upgrade the hardware
B. Truncate the table
C. Upgrade the version of Oracle
D. Delete the highwatermark
40. Which of the following choices are valid parameters for sequence creation?
A. Identified by
B. Using temporary talespace
C. Maxvalue
D. On delete cascade

41. The following statement is issued against the Oracle database. Which line
will produce an error?
A. Create view EMP_VIEW_01
B. As select E.empid, E.lastname, E.firstname, A.address
C. From EMPLOYEE E, EMPL_ADDRESS A
D. Where E.empid = A.empid
E. With check option;
F. This statement contains no errors
42. The following statement is issued on the database: comment on table EMPL
is Do not use this table. How can this data be viewed?
A. Using the describe command
B. Issuing a select * from EMPL statement
C. Selecting from ALL_COMMENTS
D. Selecting from ALL_TAB_COMMENTS
43. Which system privilege allows the user to connect to a database in restricted
session mode?
A. Create table
B. Create user
C. Restricted session
D. Create session
44. Which of the following statement is true about roles?(choose three)
A. Roles can be granted to other roles
B. Privileges can be granted to roles
C. Roles can be granted to users
D. Roles can be granted to synonyms
45. User MANN has granted the create any view with admin option privilege to
user SNOW. User SNOW granted the same privilege with admin option to
user REED. User MANN revokes the privilege from user SNOW. Which
statement is true about privileges granted to users REED,MANN and SNOW?
A. REED and MANN have the privilege, but SNOW does not
B. REED and SNOW have the privilege, but MANN does not
C. MANN and SNOW have the privilege, but REED does not
D. MANN has the privilege, but SNOW and REED does not
46. After referencing NEXTVAL, the value in CURRVAL
A. is incremented by one
B. is now in PREVVAL
C. is equal to NEXTVAL
D. is unchanged

14. Rponse aux questions du QUIZ

1. D. select empid where empid = 56949 and lastname = SMITH;
2. B. Place the alias after each column, separated by a space, to describe the column
3.C. Allows you to specify alternate output for NULL column values
4.D. select Play_name||, || Author Play_table from PLAYS;
5.A. The EMACS editor will become the SQL*Plus default text editor
6. B. decode()
7.A, C and D. sinh(), sqrt(), and round()
8.B. LOA
9.A. select to_char(nvl(sqrt(59483), 0)) from dual;
10. C. DUAL
11. B and C. asc and desc
12. B. Only one column can be used to define the sort order in an order by clause
13. C. where substr(lastname,1,1) > to_number(S)
14. B. sqrt()
15. B. The table in the join need to have common columns
16. D. Until the session completes
17. B and C. Alter the prompt clause of the accept command and Enter a new
prompt in the login.sql file
18. A and C. select * from EMP where empid = &empid;
select * from EMP where empid = (select empid from invoice where
invoice_no = 4399485);
19. A. Ampersand
20. C. select e.empid, d.head from EMP e, DEPT d where e.deptno=d.deptno (+);
21. B,C and D. To exclude certain data groups based on known criteria
To include certain data groups based on unknown criteria
To include certain data groups based on known criteria
22. B. Produced as a result of join select statement with no where clause
23. D. Issuing the set define command
24. C. group by empid;

25. B and D. Unique constraints and primary key

26. C. ALL_IND_COLUMNS
27. C. A table named ANIMALS will be created in the ANJU schema with the
same data as the ANIMALS table owned by MASTER
28. A. insert into EMPLOYEE values(59694, Harris, NULL);
29. B and C. Entities and Relationships
30. A. Two tables in the database are named VOUCHER and VOUCHER_ITEM,
respectively
31. A. B and D. CHAR, VARCHAR2 and NUMBER
32. D. The delete statement will remove all records from the table
33. C. Good_name varchar2(20) check(good_bame in(select name from
AVAIL_GOODS));
34. A. Locks
35. B. The index will be dropped
36. C. Columns with high cardinality are handled well by B-tree indexes
37. A. Use the alter table statement
38. D. Drop and re-create the view with references to select more columns
39. B. Truncate the table
40. C. Maxvalue
41. F. This statement contains no errors
42. D. Selecting from ALL_TAB_COMMENTS
43. C. Restricted session
44. A, B, and C. Roles can be granted to other roles
Privileges can be granted to roles
Roles can be granted to users
45. A. REED and MANN have the privilege, but SNOW does not
46. C. Is equal to NEXTVAL

Cours de SQL Oracle ETS

Transféré par

Droits d'auteur :

Formats disponibles

Cours de SQL Oracle ETS

Transféré par

Informations du document

Copyright

Formats disponibles

Partager ce document

Partager ou intégrer le document

Options de partage

Avez-vous trouvé ce document utile ?

Ce contenu est-il inapproprié ?

Droits d'auteur :

Formats disponibles

Cours de SQL Oracle ETS

Transféré par

Droits d'auteur :

Formats disponibles

ORACLE

Il existe 4 types de base de donnes :

1.2. Le modle relationnel

1.2.1. Composantes du modle relationnel

1.3. Le modle de donnes

Copyright Mohamed Taleb 1

1.4. Terminologie des bases de donnes relationnelles

1.5. Proprits dune base de donnes relationnelle

Copyright Mohamed Taleb 2

1.6. Solution complte dOracle

Oracle 7/8 Dictionnaire de donnes

PL/SQL SQL SQL*PLUS

Application Oracle Developer

1.7. Le systme de gestion des BD relationnelles (RDBMS)

Copyright Mohamed Taleb 3

2. SQL (Structured Query Language)

2.1. Rcapitulatif des commandes SQL

INSERT DML(Langage de manipulation de donnes)

CREATE DDL(Langage de dfinition de donnes)

COMMIT Cest des commandes qui servent grer les changements

GRANT DCL(Langage de contrle de donnes)

2.2. Les Objets SQL

Copyright Mohamed Taleb 4

2.4. Comment entrer sous SQL*PLUS

Contrle de laccs aux utilisateurs :

Copyright Mohamed Taleb 5

Le DBA peut donner des privilges un utilisateur grce la commande

Les privilges pour un dveloppeur : Create table, create session(se

Changement de mot de passe :

Copyright Mohamed Taleb 6

2.5. Commandes SQL*PLUS

A[PPEND] texte Ajoute du texte la fin de la ligne courante (L1 a[ppend]

C[HANGE] / old/new Change lancien texte

CL[EAR] BUFF[ER] Supprime les lignes du buffer SQL

DEL Supprime la ligne courante

I[NPUT] Insrer un nombre indfini de lignes

L[ist] Liste toutes les lignes du buffer

R[UN] Affiche et excute la requte SQL se trouvant dans le buffer

O texte Insrer une ligne avant la ligne no 1.

Copyright Mohamed Taleb 7

2.6. Fichiers de commandes SQL*PLUS

GET fichier[.ext] : obtenir le fichier

START fichier[.ext] : excuter le fichier, quivalent @

EDIT fichier.ext : ramne lditeur le contenu du fichier .ext

EDIT : invoke lditeur de SQL*PLUS et sauvegarde le contenu du buffer dans un

EXIT : quitter SQL*PLUS

DEFINE nom_variable [= chane de caractre seulement]: dfinir une variable

UNDEFINE nom_variable : supprime la variable utilisateur.

2.7. Commandes SQL

Copyright Mohamed Taleb 8

6. Slection de lignes (clause Where)

9. Autres oprateurs de comparaison

Copyright Mohamed Taleb 9

10. La clause ESCAPE

11. Les oprateurs logiques

12. Loprateur SOUNDEX

Copyright Mohamed Taleb 10

13. Les fonctions caractres

Copyright Mohamed Taleb 11

14. Les commandes NVL et DECODE