SQL
ORACLE
EXERCICES
SOMMAIRE
CHAPITRE 1 MODLE PHYSIQUE DE DONNES (MPD) DE LA BASE UTILISE..............3
CHAPITRE 2 : SQL ET SQL*PLUS....................................................................................................4
EXERCICES 1 10 : SQL ET SQL*PLUS :..............................................................................................................................4
EXERCICES 11 14 : SQL ET SQL*PLUS,............................................................................................................................5
CORRIGES DES EXERCICES......................................................6
CHAPITRE 2 : SQL ET SQL*PLUS...................................................................................................7
EXERCICES 1 10 : SQL ET SQL*PLUS :..............................................................................................................................7
EX. 1...........................................................................................................................................................................................7
EX.2............................................................................................................................................................................................7
EX.3............................................................................................................................................................................................8
EX.4............................................................................................................................................................................................9
EX.5............................................................................................................................................................................................9
EX.6..........................................................................................................................................................................................10
EX.7..........................................................................................................................................................................................10
EX.8..........................................................................................................................................................................................11
EX.9..........................................................................................................................................................................................11
EX.10........................................................................................................................................................................................12
EXERCICES 11 14 : SQL ET SQL*PLUS............................................................................................................................12
EX.11........................................................................................................................................................................................12
EX.12........................................................................................................................................................................................13
EX.13........................................................................................................................................................................................13
EX.14........................................................................................................................................................................................14
EXERCICES 15 : LES VUES...................................................................................................................................................14
DEMONSTRATIONS...........................................................................................................................16
CHAPITRE 3 : LES TRAITEMENTS STOCKES........................................................................... 16
2
/
Chapitre 1 Modle Physique de Donnes (MPD) de la base utilise
Modle Physique de Donnes
Proj et : Formation
Modle : Exos
Auteur : Or@Tech Version 1.0 10.11.99
e_image
no
format
validite
nom_fichier
image
<pk> NUMBER(7)
VARCHAR2(25)
VARCHAR2(1)
VARCHAR2(255)
LONG RAW
not null
null
null
null
null
<pk>
NUMBER(7)
VARCHAR2(1)
VARCHAR2(255)
VARCHAR2(2000)
not null
null
null
null
no
nom
e_continent
<pk> NUMBER(7)
<ak> VARCHAR2(50)
no = image_no
e_service
no
nom
continent_no
<pk>
<ak>
<fk,ak>
NUMBER(7)
VARCHAR2(25)
NUMBER(7)
e_produit
<pk> NUMBER(7)
<ak> VARCHAR2(50)
VARCHAR2(255)
<fk> NUMBER(7)
<fk> NUMBER(7)
NUMBER(11,2)
no
nom
description
texte_no
image_no
prix_conseille
not null
not null
null
null
null
null
e_client
<pk> NUMBER(7)
VARCHAR2(50)
VARCHAR2(25)
VARCHAR2(400)
VARCHAR2(30)
VARCHAR2(20)
VARCHAR2(30)
VARCHAR2(5)
VARCHAR2(9)
<fk> NUMBER(7)
<fk> NUMBER(7)
VARCHAR2(255)
not null
not null
null
no
nom
telephone
adresse
ville
departement
pays
cp_postal
solvabilite
emp_no
continent_no
commentaires
commande_no
ligne_no
produit_no
prix
quantite
qte_livree
not null
not null
not null
null
null
null
not null
not null
null
null
no = superieur_no
null
null
null
null
null
null
null
null
no = service_no
no = emp_no
no
nom
prenom
utilisateur
dt_entree
commentaires
no = client_no
superieur_no
titre
service_no
salaire
no = produit_no pct_commission
e_emp
<pk> NUMBER(7)
VARCHAR2(25)
VARCHAR2(25)
<ak> VARCHAR2(8)
DATE
VARCHAR2(255)
<fk> NUMBER(7)
<fk> VARCHAR2(25)
<fk> NUMBER(7)
NUMBER(11,2)
NUMBER(4,2)
e_titre
titre <pk> VARCHAR2(25)
3
/
no
client_no
dt_commande
dt_livraison
emp_no
total
nt_paiement
validite
not null
not null
null
null
null
null
null
null
null
null
null
titre = titre
no = commande_no
e_commande
<pk> NUMBER(7)
<fk> NUMBER(7)
DATE
DATE
<fk> NUMBER(7)
NUMBER(11,2)
VARCHAR2(8)
VARCHAR2(1)
no = entrepot_no
no = superieur_no
no = produit_no
e_ligne
<pk,fk,ak> NUMBER(7)
<pk>
NUMBER(7)
<fk,ak>
NUMBER(7)
NUMBER(11,2)
NUMBER(9)
NUMBER(9)
not null
not null
no = continent_no
no = continent_no
no = texte_no
not null
not null
null
null
null
null
null
null
null
no = continent_no
e_texte
no
validite
nom_fichier
texte
e_entrepot
<pk> NUMBER(7)
<fk> NUMBER(7)
LONG
VARCHAR2(30)
VARCHAR2(20)
VARCHAR2(30)
VARCHAR2(5)
VARCHAR2(25)
<fk> NUMBER(7)
no
continent_no
adresse
ville
departement
pays
cp_postal
telephone
superieur_no
not null
no = emp_no
not null
not null
null
null
null
null
null
null
produit_no
entrepot_no
qte_stockee
stock_securite
max_stocke
detail_sortie
dt_stock
e_stock
<pk,fk> NUMBER(7)
<pk,fk> NUMBER(7)
NUMBER(9)
NUMBER(9)
NUMBER(9)
VARCHAR2(255)
DATE
not null
not null
null
null
null
null
null
Chapitre 2 : SQL et SQL*Plus
EXERCICES 1 10 : SQL ET SQL*PLUS :
Prparation : charger la base DELUXE.SQL
Enregistrez la requte que vous construirez
Enregistrez galement les rsultats dans un fichier spool
Exercice 1
Liste des services dont le numro est suprieur 45.
Exercice 2
Liste des employs dont le nom commence par "M"
Rcrire la requte en utilisant une variable.
Exercice 3
Liste des employs nayant pas de commission.
Exercice 4
Liste des employs qui ont les fonctions suivantes : Magasinier, Chef dentrept.
Exercice 5
Liste des employs gagnant entre 1400 et 2000 et ayant intgr la socit aprs le 20 janvier
1992.
Exercice 6
Liste des employs dont la commission est suprieure 200, classs dans lordre croissant des
commissions.
Exercice 7
Liste des employs tris par titre et pour chaque titre tris par salaire dcroissant.
Exercice 8
Liste des employs et des services dans lesquels ils exercent.
Exercice 9
Liste des numros de produits qui nont jamais t commands.
Exercice 10
Liste des employs travaillant en Europe et ayant le mme titre que SMITH.
4
/
EXERCICES 11 14 : SQL ET SQL*PLUS,
Exercice 11
Liste des employs (no, nom, salaire) tris par nom. Le salaire de VELASQUEZ doit tre cach
par des "*".
Exercice 12
Liste des employs (nom, prnom, date dentre, salaire et numro de service) travaillant dans un
service qui contient au moins un Reprsentant Commercial.
Exercice 13
Liste des employs dont le prnom comprend au moins un A ou un i et dont lensemble (nom,
prnom) comporte au plus 6 voyelles.
Exercice 14
Afficher les informations du service ayant le plus demploys : son numro, son nom, le nom du
continent o il se trouve et le nombre demploys y travaillant.
Exercice 15 : Les vues
Crer une vue V_EMP qui rassemblera :
Le numro, le nom, le titre, le salaire et le nom du service de lemploy
Regarder le contenu de la vue EMP_T
Supprimer la table E_EMP (avec loption CASCADE CONSTRAINTS)
Afficher le contenu de la vue V_EMP Que se passe-t-il ?
5
/
CORRIGES DES EXERCICES
6
/
Chapitre 1 : SQL et SQL*Plus
EXERCICES 1 10 : SQL ET SQL*PLUS :
Ex. 1.
SELECTnom,no
FROMe_service
WHEREno>45
/
NOMNO
Administration50
1 ligne(s) retourne(s)
Ex.2.
Premire requte
SELECTnom
FROMe_emp
WHEREnomLIKE'M%'
/
NOM
MENCHU
MAGEE
MADURO
MARKARIAN
4 ligne(s) retourne(s)
7
/
2me requte
SQL> spool var
SQL> get var2.sql
1 SELECT nom
2 FROM e_emp
3* WHERE nom LIKE '&1%'
SQL> r
1 SELECT nom
2 FROM e_emp
3* WHERE nom LIKE '&1%'
Entrez une valeur pour 1 : M
ancien 3 : WHERE nom LIKE '&1%'
nouveau 3 : WHERE nom LIKE 'M%'
NOM
------------------------MENCHU
MAGEE
MADURO
MARKARIAN
4 ligne(s) slectionne(s).
Ex.3.
SELECTnom,salaire,pct_commission
FROMe_emp
WHEREpct_commissionISNULL
/
NOMSALAIREPCT_COMMISSION
VELASQUEZ2500.00
NGAO1450.00
NAGAYAMA1400.00
QUICKTOSEE1450.00
ROPBURN1550.00
URGUHART1200.00
MENCHU1250.00
BIRI1100.00
CATCHPOLE1300.00
HAVEL1307.00
MADURO1400.00
SMITH940.00
NOZAKI1200.00
PATEL795.00
NEWMAN750.00
MARKARIAN850.00
CHANG800.00
PATEL795.00
DANCS860.00
SCHWARTZ1100.00
20 ligne(s) retourne(s)
8
/
Ex.4.
SELECTnom,titre
FROMe_emp
WHEREtitreIN('Magasinier','Chefd''entrept')
/
NOMTITRE
URGUHARTChefd'entrept
MENCHUChefd'entrept
BIRIChefd'entrept
CATCHPOLEChefd'entrept
HAVELChefd'entrept
MADUROMagasinier
SMITHMagasinier
NOZAKIMagasinier
PATELMagasinier
NEWMANMagasinier
MARKARIANMagasinier
CHANGMagasinier
PATELMagasinier
DANCSMagasinier
SCHWARTZMagasinier
15 ligne(s) retourne(s)
Ex.5.
SELECTnom,salaire,dt_entree
FROMe_emp
WHEREsalaireBETWEEN1400AND2000
ANDdt_entree>'20JAN92'
/
NOMSALAIREDT_ENTREE
NGUYEN1525.0022JAN92
MADURO1400.0007FEB92
2 ligne(s) retourne(s)
9
/
Ex.6.
SELECT nom, salaire, pct_commission,
pct_commission * salaire/100 "COMMISSION"
FROM e_emp
WHERE (pct_commission * salaire/100) >200
ORDER BY commission
/
NOM
SALAIRE PCT_COMMISSION
COMMISSION
------- --------------- --------------- --------------NGUYEN
1525.00
15.00
228
DUMAS
1450.00
17.50
253
2 ligne(s) retourne(s)
Ex.7.
SELECT nom, titre, salaire
FROM e_emp
ORDER BY titre, salaire DESC
/
NOM
TITRE
-------------- -------------------------
SALAIRE
-------
HAVEL
Chef d'entrept
1307.00
CATCHPOLE
Chef d'entrept
1300.00
MENCHU
Chef d'entrept
1250.00
URGUHART
Chef d'entrept
1200.00
BIRI
Chef d'entrept
1100.00
ROPBURN
DR, Administration
1550.00
QUICK-TO-SEE
DR, Finance
1450.00
NGAO
DR, Oprations
1450.00
NAGAYAMA
DR, Ventes
1400.00
MADURO
Magasinier
1400.00
NOZAKI
Magasinier
1200.00
SCHWARTZ
Magasinier
1100.00
SMITH
Magasinier
940.00
DANCS
Magasinier
860.00
MARKARIAN
Magasinier
850.00
CHANG
Magasinier
800.00
PATEL
Magasinier
795.00
PATEL
Magasinier
795.00
NEWMAN
Magasinier
750.00
VELASQUEZ
Prsident
2500.00
NGUYEN
Reprsentant Commercial
1525.00
SEDEGHI
Reprsentant Commercial
1515.00
GILJUM
Reprsentant Commercial
1490.00
DUMAS
Reprsentant Commercial
1450.00
MAGEE
Reprsentant Commercial
1400.00
10
/
Ex.8.
SELECTe_emp.nomEMPLOYE,e_service.nomSERVICE
FROMe_emp,e_service
WHEREe_emp.service_no=e_service.no
/
EMPLOYESERVICE
VELASQUEZAdministration
NGAOOprations
NAGAYAMAVentes
QUICKTOSEEFinance
ROPBURNAdministration
URGUHARTOprations
MENCHUOprations
BIRIOprations
CATCHPOLEOprations
HAVELOprations
MAGEEVentes
GILJUMVentes
SEDEGHIVentes
NGUYENVentes
DUMASVentes
MADUROOprations
SMITHOprations
NOZAKIOprations
PATELOprations
NEWMANOprations
MARKARIANOprations
CHANGOprations
PATELVentes
DANCSOprations
SCHWARTZOprations
25 ligne(s) retourne(s)
Ex.9.
SELECTnoFROMe_produit
MINUS
SELECTproduit_noFROMe_ligne
/
NO
41020
41050
2 ligne(s) retourne(s)
11
/
Ex.10.
SELECTe.nom,e.titre
FROMe_empe,e_services
WHEREe.service_no=s.no
ANDs.continent_no=(SELECTnoFROMe_continent
WHEREnom='Europe')
ANDtitre=(SELECTtitreFROMe_empWHEREnom='SMITH')
/
NOMTITRE
DANCSMagasinier
SCHWARTZMagasinier
2 ligne(s) retourne(s)
EXERCICES 11 14 : SQL ET SQL*PLUS
Ex.11.
SQL> SELECT no, nom,
2> DECODE(nom, 'VELASQUEZ', '****', salaire) SALAIRE
3> FROM e_emp
4> ORDER BY nom
/
NO
--------8
9
22
24
15
12
10
16
11
21
7
3
20
2
14
18
19
23
4
5
25
13
17
6
1
NOM
--------------BIRI
CATCHPOLE
CHANG
DANCS
DUMAS
GILJUM
HAVEL
MADURO
MAGEE
MARKARIAN
MENCHU
NAGAYAMA
NEWMAN
NGAO
NGUYEN
NOZAKI
PATEL
PATEL
QUICK-TO-SEE
ROPBURN
SCHWARTZ
SEDEGHI
SMITH
URGUHART
VELASQUEZ
SALAIRE
-----------------------------1100
1300
800
860
1450
1490
1307
1400
1400
850
1250
1400
750
1450
1525
1200
795
795
1450
1550
1100
1515
940
1200
****
25 ligne(s) retourne(s)
12
/
Ex.12.
SELECTnom,prenom,dt_entree,salaire,service_no
FROMe_empe
WHEREEXISTS(SELECT*FROMe_emp
WHEREtitre='ReprsentantCommercial'
ANDservice_no=e.service_no)
/
NOM
--------NAGAYAMA
MAGEE
GILJUM
SEDEGHI
NGUYEN
DUMAS
PATEL
PRENOM
-----Midori
Colin
Henry
Yasmin
Mai
Andr
Radha
DT_ENTREE
--------17-JUN-91
14-MAY-90
18-JAN-92
18-FEB-91
22-JAN-92
09-OCT-91
17-OCT-90
SALAIRE
------1400.00
1400.00
1490.00
1515.00
1525.00
1450.00
795.00
SERVICE_NO
---------31
31
32
33
34
35
34
7 ligne(s) retourne(s)
Ex.13
SELECTnom,prenom
FROMe_emp
WHERE(prenomLIKE'%A%'ORprenomLIKE'%i%')
ANDLENGTH(
TRANSLATE(
UPPER(nom||prenom),
'*BCDFGHJKLMNPQRSTVWXZ','*'))<=6
/
NOMPRENOM
NGAOLaDoris
ROPBURNAudry
MAGEEColin
SEDEGHIYasmin
NGUYENMai
DUMASAndr
NOZAKIAkira
PATELVikram
CHANGEddie
SCHWARTZSylvie
10ligne(s)retourne(s)
13
/
Ex.14.
SELECTe.service_no,s.nomSERVICE,c.nomCONTINENT,COUNT(*)NOMBREEMPLOYES
FROMe_empe,e_services,e_continentc
WHEREe.service_no=s.no
ANDs.continent_no=c.no
GROUPBYe.service_no,s.nom,c.nom
HAVINGCOUNT(*)=(SELECTMAX(COUNT(*))
FROMe_emp
GROUPBYservice_no)
/
SERVICE_NOSERVICECONTINENTNOMBREEMPLOYES
41OprationsAmriqueduNord4
1ligne(s)retourne(s)
EXERCICES 15 : LES VUES
Crer une vue V_EMP qui rassemblera :
Le numro, le nom, le titre, le salaire et le nom du service de lemploy
Regarder le contenu de la vue EMP_T
Supprimer la table E_EMP (avec loption CASCADE CONSTRAINTS)
Afficher le contenu de la vue V_EMP Que se passe-t-il ?
Supprimer la vue V_EMP
SQL> create or replace view v_emp
2 as
3 select e.no, e.nom, e.prenom, e.titre, e.salaire, d.nom "SERVICE"
4 from e_emp e, e_service d
5* where e.service_no = d.no
Vue cre.
SQL> select nom, service, titre, salaire from v_emp;
NOM
SERVICE
TITRE
SALAIRE
------------ ------------------ ---------------------- ---------VELASQUEZ
Administration
Prsident
2500
NGAO
Oprations
DR, Oprations
1450
NAGAYAMA
Ventes
DR, Ventes
1400
QUICK-TO-SEE Finance
DR, Finance
1450
ROPBURN
Administration
DR, Administration
1550
URGUHART
Oprations
Chef d'entrept
1200
MENCHU
Oprations
Chef d'entrept
1250
BIRI
Oprations
Chef d'entrept
1100
CATCHPOLE
Oprations
Chef d'entrept
1300
HAVEL
Oprations
Chef d'entrept
1307
MAGEE
Ventes
Reprsentant Commercial 1400
GILJUM
Ventes
Reprsentant Commercial 1490
SEDEGHI
Ventes
Reprsentant Commercial 1515
NGUYEN
Ventes
Reprsentant Commercial 1525
DUMAS
Ventes
Reprsentant Commercial 1450
MADURO
Oprations
Magasinier
1400
SMITH
Oprations
Magasinier
940
14
/
NOZAKI
NEWMAN
MARKARIAN
CHANG
PATEL
DANCS
SCHWARTZ
Oprations
Oprations
Oprations
Oprations
Ventes
Oprations
Oprations
Magasinier
Magasinier
Magasinier
Magasinier
Magasinier
Magasinier
Magasinier
1200
750
850
800
795
860
1100
25 ligne(s) slectionne(s).
SUPPRESSION DE LA TABLE CIBLE
SQL> drop table e_emp;
drop table e_emp
*
ERREUR la ligne 1 :
ORA-02449: cls uniques/primaires de la table rfrences par des cls
trangres
SQL> drop table e_emp cascade;
drop table e_emp cascade
*
ERREUR la ligne 1 :
ORA-00905: Mot-cl absent
SQL>
SQL> drop table e_emp cascade constraints;
Table supprime.
LA VUE EXISTE MAIS ELLE EST INVALIDE !
SQL> select * from v_emp;
select * from v_emp
*
ERREUR la ligne 1 :
ORA-04063: view "SCOTT.V_EMP" a des erreurs
15
/
DEMONSTRATIONS
Chapitre 1 : LES TRAITEMENTS STOCKES
DEMONSTRATION : LE PACKAGE
(base sur le schma SCOTT)
create or replace package pack_emp
as
procedure pp_emp (no
in number,
name
in varchar,
salr
in number,
dept
in number)
;
procedure pp_sup (no
in number,
dept
in number)
;
function f_cpt (no_dept
nm_job
in number,
in varchar2)
return number
;
end;
/
create or replace package body pack_emp
as procedure pp_emp (no
in number,
name
in varchar,
salr
in number,
dept
in number)
as
begin
insert into emp (empno, ename, sal, deptno)
values (no,name, salr, dept);
commit;
end;
procedure pp_sup (no
16
/
in number,
dept in number)
as
begin
delete from emp
where empno = no;
commit;
end;
function f_cpt (no_dept
nm_job
in number,
in varchar2)
return number
as
cpt number :=0;
begin
select count(deptno)
into cpt
from emp
where deptno
= no_dept
and upper(job)= upper(nm_job)
;
return(cpt);
end;
end;
/
DEMONSTRATION : LA FONCTION
REM EXECUTION D UNE FONCTION
REM Exemple pour executer la procedure EXE_UIWORK :
REM execute exe_uwork (10,'MANAGER')
REM puis pour vrifier : select * from t_cpt
REM
REM CREATION D UNE TABLE DE RECEPTION DES RESULTATS DE LA FONCTION
DROP TABLE t_cpt;
CREATE TABLE t_cpt
(dt_jr
date,
nb_salarie number)
17
/
/
REM CREATION DE LA PROCEDURE QUI EXECUTE LA FONCTION DU PACKAGE
CREATE OR REPLACE PROCEDURE exe_uwork (no_dept
nm_job
number,
varchar2)
IS
cpt_dept emp.deptno%type := 0;
BEGIN
cpt_dept := pack_emp.f_cpt(no_dept,nm_job);
insert into t_cpt values (sysdate, cpt_dept);
commit;
END;
/
DEMONSTRATION : LE TRIGGER
REM
REM Creation du trigger qui recherche la sequence sur emp2
REM
REM CREATION TABLE EMP2
REM
CREATE TABLE EMP2
(empno number(3) not null,
ename varchar2(15) not null)
TABLESPACE user_data
STORAGE (INITIAL 50K)
/
REM CREATION DE LA SEQUENCE SUR EMP2
REM
CREATE SEQUENCE seq_emp2
increment by 1
start with 1
/
18
/
REM CREATION DU TRIGGER
REM
create or replace trigger trg_seq
before insert
on emp2
for each row
begin
/* select de la sequence dans la variable new. */
select seq_emp2.nextval
into :new.empno
from dual;
end;
/
REM
REM
REM INSERTION
dANS EMP2
INSERT INTO emp2 (ename)
VALUES ('EXO TRIGGER')
/
COMMIT;
REM
SELECT * FROM EMP2
/
19
/
