|
| ree 8 suite
a loins , se Opacetore
o| peplay ail “the erployere and the depth
tt nc
“genpiementing a IF quder join
Quays seed #from erp
gol> select emp: empno, emp: enarne dep alp
| deptedname from emp jet outer jen
dept © emp-depino -deptedep toe 5
Ow uct
=
EMeMO enomMe pepo DNAME
G03 MILER 10 ALC OONTING
PCCOON TH IG
4362 CLORKE 1° (TING
oO PCCOON TING
3824 KING : NG
3676 “EDA Ms 20 RES ARCH
(3369 smMrte 20 ReEsepREH
20 RESEARCH
ecoTt 20 Ries FPRcn
3566 yones 20 reser
34.00% TAME S gO SPLES
PLE.
a TORNER Me nee
4654 MATTIN B0 SALES
1 ASPy
4 weed 30 SALES
44 - ‘
144K oe 30 SPLES
30 SPLES I
“Al
RlLOKE Querys Right Qulet Join
S@L> kelect © mp: empno, emp Cnayne ,
cept sdeptno, depl-lname from , «mp
Tigh) outey join alept on em-depino=
dept- depino 5
Ouetput~
EMPNIO
234
a648
Fan
3566
aay
402
3364
4444
S21
Ae5y
aca
3938
EN PME DF
KING ia
BLOKE 1439 4
CLARK 10
“Jones _
SCOTT 20
FORO 20
SMITH 20
POLLEN BO
wARD ee
MARTIN,
“TORALER
A 4 20
| ee ee
|
1.400 JAMES 30
MILLER to
40
DEPTNSD §=ONAME
—_—_—~
», BCCOONITING
SPLES
ae
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
REsef Rew
SALES
SPLES
SALES
SALES
REseA RH
SALES
AccounTind
opr nto the employee Name canch
. clay
v psPat
depacimne| name, 10 which “they one
poor King ampteme ming full oof jo
ues’
£QL> eclect amp ename , daph dneme
from empe full outer JOw" dept of
emp depine = dept: deptne j
output
Eneme ONPME
mailer pccoun TING
cLeRK AccoON TING
KING pccOONTING
ppems ReseeRcH
SMa TH Res EARCH
FORO RpEseOR
&coTT ARCH
JONES RESEARCH
Es SALES
TORNER SALES
Marta SALES
wi
oe
Selet
4 emp rename, mar-ename ag
“MGR”, mar Sol ay *MAR SAL” fron emp,
emp m4 wohede emp M]P= meqempnos
Or 2
oop
ENAME MAR ARSPL
-_——_— ---- ot
“JONES LINC) 5000
CLARK (IN G 5000
5000
BLOKE KING
| Temes — BLAKE 2650
TurneR BLAKE 2850
Mertin BLAKE 2x50
WRRP BLAKE 2850
2850
Puen — BLOKE
MileR CLARK 9460
FORD TONES 2
SCOTT ones 294s pea
ADAMS ecorr | gcco
€MTITH FORD 3000 | pete a Query lo
Oust
| joe, employee nod. pub the nam,
2 ®€04 Name
| gos cach depodment
e
j
, Lee ection
| Even «
| ees. 4 there ary
quer
ai > select emp-e
; Prempna, emp. enarme
,
emp- jobydeet> deptno, depl-dnorne leg!
me cdegl-loc
from emp lett ree
, Pp Outer join dept on
emp. deptno = dept-clepino;
Ouetpeh
EMPNO ENeme 3eB Oeer Dneame LOC
Oo
A434 Maree CLERK 10 ACCOUNTING ALEH!
YOR
Inaen CrerK. Mannose 10 PXCUNTING New
YORK
‘PRESIOENT 10 PCCOUNTING NEA
3 YORK
29 RESERREX Daas
‘sac POAMS CLERK
: no ReseArce OPLAS
33eq. «SMITH | CLERE
no RESERRU RUA
|
A402 FORO FALUST
ae pnevusr 2° peserct On»
MeNeGFe 2° pesrnrn ons
Cie
go SPURS Meo | | J yep T VOM
tewene neme 0B C er F log
yey y “TORNFP epLles ao SALES Chen
MeN Gp
465" martin SPLES 20. Shles CHT CO,
MAN 6
501 WPRD SPLES 20 «SPIES Curry,
man
20 SAEs CNTCHG,
0 SALES CHICAS,
wWya4 ALLEN SALE Ss
Mam
aeqs BLPKE MANAGER 3
2 duplay He details ax those whe chour
se same salady.
Que 4
emp-job,
5 gelect emp-empno , EMmP-eNAame,
gar
emp: gal From emp, emp ck where
| ehPP° eal = de Sal and emp- empnoz aren:
| Suet
| “pwpno enpme oR PL
| 7902 FORD prpuet 3000"
_ ae? | SCOTT ANAL aie
1 465 an ps0
52h IBRD SPLESMON josd
A -
BARS oe ee a eee. |
Set Operators
4 unten
8 uses PTT
A gn terRseel
A Minos
= ONTONE
quer? ZQL> Select epino, dame from
aept union select depino, ename trom
emPs :
Quist
DEPTNO DNPME
_ 7 AccOONTING
10 CLRPRE
10 KING
{o MILEFR
20 ADAM ¢
29 FORD
20 TONES
20 _ RESEARCH
2D — scOTT
Ve
sMmitH
20
30 ALLEN
80 BLAKE
30 ce
ei Bo
MORTIN »
PePTNO
yo
ONAME
SOLES
JORNFR
ALNRD
OPE PATON S
CNTON ALLE
Query gab >select depino, diname
| fom dept Union all select depino,
ename hom emp5
Output
DEPTNO
10
20
30
40
10
30
1o
20
20
20
20
30
ONONE
“ACCOUNTING
RESEARCH
SALES
OPERATIONS
KING
BLAKE
cCLleAREK
JONES
SMFTH
PLLEN hh yo
——
DNAME
WHPRD
MpRTIN
20 TORNEE
2°
PORMS
20
JAMES
nsensechor
——
Quen Salr
gelect depino, dname 4x00
der enteuect gelect depine, ename
from EMP §
Oustput
ne OWE
Selected
MINLS+
-
Quetyi- SQL > gelect depine, dname fen
ne trom
dept | minus select depine, mm"
emp s
TNLO ONAME
Nh — -——
\z ed ACCOUNTING
ee Reser RCH
Bo SRPLES
OCkeaT TERN: were ae
TH
chm inands 0% Vie ue
MO tite a
AY erate a wer Ahat displays athe ern Jorge u
ets who be long
| Mame and Salary ot employ
40 dept 10°
Quen)
gelect empno,
empview as
SQL> create View
emp Where depino=10;
ename, gal from
Quip view created «
eelect ¥ from empviews
Ename SPL
Evieno
| #34 KING 5000 1
| Ae? CrARK auso beac enly we
| 4434 mriier ‘30° paatatle *
) Create a view ith read only option
thet digplays the employee ame and
|
| thei department Name -
| Query, ceeacte view emprieatl of sect
| e-ename, drdrame | trot! emp re: join et 4
© e-deptho=ddepine with read only
Conshraint efi Teadonly »
dept. to |e
i. onAme
} 7 eM ae
ene : ee
! x COUNTING
a Spres
y
at ot : ACCOUNTING
puPrt Rese ARCH
pon €S oo
ow e cco
=e E ARC
for? aa: :
gmat# idee
pire sore
mierO 8
moetan! 3
| TORN er =
poems oo
es SPLES a)
AccoUNTING
MILLER
deploy | the yew qendacted:
| ud
| ear>celect
views name xo ger eur
Outpt
| WEVAL NEME
‘EmMPV Ie)
FmevigiwiL |
_
4 Ewcceete abe OMe command 60 -the vie,n
| emeated and drop them
Quenyi-
ear ddelete fyor) empvier where empno=93
it.
Moy tnsett frfo WMPVICW values Fg8q
,
WKN
Cary Uplate View ged
\ sooo) 5
Zal=|0000 Whey.
empno = 48
cary Axop view empviews
gard dwp View empvieos 5
output
4 ow deleted
| EMeNO ENAME SAL
Fao Cloak 2450
nasy Miller ‘1300
A vow cxeatedl
Empno ENAME SAL
49¢2 Clank 2ysd
1434 Milles 1300
i 1834 Kina, 1s000
5 A wow updected ENAME 4A
“a8 9 Clank q000
4a3" Ni\er 1300
483% King 5000
| je Awopped
en Avopped
jewoname -rom gered
