BNS

1atlbase Management Sstum

akabase Managem ent óystiro -

Cetle tuer é
data stone 0 Hebuit thus
data

SAL CoNMAN DS

pAL

DCL TCL
DDL DML
Explainplan
-Cemmut Seluck
Gtmant
Heatt Innt
LocK Tablu
RINGRe RelllaCk
drep upolat
Altun Saye
Olet Pount
-N eHgEe but Thaps ac Hn
THuntakL Call
LOmment
Rename kanguage
DDL Rata OinutSn data hsuld be
odatabase and desCuptiens
Ldeals with
dotalase.
new

us OjActs
obyaS
CHeat: rentt a kalti varol
wrhith 'u eutng
ht talrtL
)At Atus
tht datalase.
oluh th jcts 4merm
3 nop pa cs
umeyt thi COMd indudng °Fada
ATuur.cat

add e m m e n to the dota dic.

5 Ceomurnt
6Roname:Kunom objct
 w tt a
alb
tet d cbJect.
nuw
a

RE ATE usto to
D1eai

CMeatL falr
I ts
TABLE
TABLE
NAME

epid
employe
InNT
CREATE

dataYpe ename CHAR

Colnam

C o l n a m e 2 data"ype age TNT,

Employee

E p i d e ame
a9e

2 DROP entue dlatalas y cb

Hemove
L alleus to thi tal.
dillte
th olalalase, Jt

DROP Talt enmpleyee

DROP TA BLE tablurame
DROp database 2 databa5e
name
DROP Ookalesbe Eployee;
Remove indivi dual neconod use dut
3) ALTER
to alt modipy th stuuctune
Jt alleus
naddituen7 eun th datoyp,t
database add

LqLng 2aistur9 (elu.

VARCHAR-
incud eslnd,tr
(DALTER tar 4 tabluname>
ADD 4 coumnna me data ype?
Con)
HChange d Curonname >4new tol name>
(on)
Dre Lumn 2 Coumnname

FgAlt inlt mplayee dd ( addus VARCHARC20));

Aub kabte empee hangephon eno Cont no
At tale )
<mpoyee drep(elwmn
Employe e ename
Gpid ename LontDo
Phore neaddus
 TO vieu changus t u ciw the
OCCHed un thi
kalt
Epid Contno Adol
DESCRIBE tal employee;

4 REVAME
Tisustd to unamt
LJk an oyuot
and db talte

RENANE talli 2 0Ldname> to 4 vew name.

RENANE talt 2 employee> to <emp>
nerm thi taire

5 TRUNCATE ed dl all thi nous ET

same as dlutt. CK.
M0 ollbaC
Yumanetly.
elutt i ollbgtk.

TRUNCATE talr 2 taltename

EQ Truuncad tall Emp

dut1t
Manipulalen kanguage.. (Sld, unswt, pdatu)
ado
DM L>
PIstonms uad ely quwy e dota.

SELECT: db.
daieue thu data nem
usd o

CHAUSES ORDER By () AS
(1D
DWHERE () CROUP By (ii) HAVING
, <, > :,
4
z, Btw)%,»a).
to Het aievt C=, ! =,

min", Sun'Avge) ).
W H E R E > w h i t h
HOw
WHERE ounl) ma)
aMarge .(
data 10 Mup
C1HOupb Cslea)
Seluct amer 9pby s to duplay.
Houing in which t
speapis thl ondur
Cdbx by Alias on) Runame
as

Syntax tablurame >;

SELECT * fom
Ritun all h nows.
Eg SLluct 4Mem emf
 i) INSERT daltus Con
TAUtungdota
nto d 1on
th dalu at a
moHe olato unto
datati,
INSERT Into 2tablun
name> C colname | 4
L data tyt
pe,>
Col name2
pe
valus
val, valu2, val ue ..
insut into
emp empid
INT, ename
VARCHAR (26)
VARCHAR (20))
Vals 1,A
ouby ABC
2, DEF

enpid ename city

ABC Pune

8: CpDATE

in
mody tht ucoMo pHLserd
LUsung
toll
yntox
updad 2 tablu_name> Set 2 oumn_name = value

whwe ond

updati Emp set Salay :

2o000 whue ename ABc;

ernpid ename Cby selaay

ABC Pune 20000

(1 DELETE:
uud to dilt some d all HeLOnds em the
Aisihn9
DELETE rom 2taltt name> whuie 2
lendi tuen>
Qelut em emp whuie Empid
'T';
Empid Ename Age
00
ABC 3
002 PaR
003
xYz3
 C17nant).
3
pCL
DCL ata Contrel hangunge( Revoke,
Cortuel th s acuss in a databaße
Rulattd sewut 19Susu.
nnant gitung the pumicsi en
Reveke ?
Rlea S the
purmiSSim.
the db
to
RAn7: wes CCU pLNilages

ARANT pHeviuge lisE> On 2 Helation-name

oN
view nome
To 2uuAIMoL list >;
HRANT all ON
emp t ABC u
Ltable - rame
RE VOKE: yiSUsly on gnarti od on
T t is Jmd to ton l pHe
qnanted Ruliased
dinudl OmisS61en.
RilLased qnanted
REVOKE 2 pevi leqe list > ON viewoT ReLname>

FROM Z ULAname>.

E RuoKe
updat ON emP o ABC
/* anod */=> {on multi une
PA SL TRAAHAERS =407 3ing line
13n OPLHa H
on
ASS
ensTHe.
PL Pocedunal hanguae Not case
SL.
pMOudunal entensi en. Jonguage hon
SAL
LOnade ulatuenal db. /3ame db as

IBN b .
ud in Ondcu, TumeL Ten in -memDy olb,
Suppon
Suctwral pA09kammung thcough un.
Pouduus 0OP
Ldwelepmnt web appticatien q V " P
BlocKS+uctue Blbck (Pro, fune)
~>dYinu type
BLOCK HEADER
Used
AS vaniabll
any
cleclate
BE IN DECLARATION HEADERin blocK oku PLYS8 L
0J
or
E XEOUTION HEADER s e van
ise.
Ep ti on hanle any Prnot tAat
EX CEPTION HEAPER
 Ptoqram:
FOR LOo P
DECLARE
X Numben: l00 =

BEGIN
Then
Fo in . 1 o LooP IF moD(i,2) zo
INSERT INTO mp yaluus ( , X, ' i b ev e n ' )

ELSE
INSERT 1NTO tmp Valus , x, 'i is bolo').

END 1F
X= X + 100
END LOo P;
COMM IT; Sa ving the povg1om
END
SAL Suluck *
mo emp 0mdun by Coli,
Num cel Num-ol2 cha-co

iis odd
aoo i s eVen

TRIGHAERS

1niggets
00
L a pHouduHestavts autem atically
pui ed chounoys SCCw de tht BNS.
db tupget
LTwigg Pent
tuggun Leno optiona
TH29en atienCeanges happan On db
wtin a r event
OCcUMdb tuigpr i ined, an
pHe- dyeruot PL SA blk will pyomm mcL 0
ynex
enis OR Rpla| Trugger THiggename
talrame

BoHe/ AFUI ugger eeni ON

tun athon.

o n eoch Hou]
whun enoller

Qeclane
didanaden Stats

BAnAN
En emtalk btat;

Enuphen
Encpun hand;
tablu nome.
END
*
utemut
PrnognoumaL> Suli ct mom
addus
Salony
2000 .OO

Name
Delhi 6oo0 0 o
32 Cchennai

Hani 35

Pruuye 30
Prdy 3ooo O6

Banglore
Puvi 3 6ooo.o6

Dup Kunela

3+
pioky
o l d gNew.
btuw
b t
duuplay
thu salay du
THLggo
tuggu olisp olis-sal
updatrg on austomu.
Cneati b uplace
oM nAudung
Byone Duutirg
oM ach A
70)
whut CNewID

DECLARE

Sal-dih
Begu old. sal;
Sal -

dal -d u : heu "ll: old. salapny)

une ( '0ld balamy:
dbms.output pul new.solay);
("newsalay: "I|:
dbms.0utput. put-une
dbms. utput put_une ("sal.d: "1l: 5al-diff);
END
 Aagu
4) TCL Thansa duen conbuel un d
d6s.
gung On.
L Stat
in
changs
d to mad Logical
Tensa cion

Hoyp toqethu o

1 CON I7
Changis
made t o
tHi tably bli pum
U e d to 6aurt thu
tre
Usten whn oge
COHM IT DELETE
CONI7

RoLLBACK:-
=
LCtt+ pdo nde
tat
Enet þack to th pHevipu commiLd

nbel kmponay whun whMe ag :a5;

ROLLCACK
C u s t e m w s

Rolllack
t ) SAVE Po!INT:
Roack
alerg with
ud

Uud mark chans

Bookma -
Savepounk Saueponk-nami
JOINS

Imnui 6un Out foin

Cunion
:(ntuseet Rigkk ful

INNER JOIN:
B eth tht Yaluus Tablu)
SELECT
SELECT Col name emtoble-namel îNNERJO IN tab-name2
ON tablu I. cAl-name tahli 2. Colname;
 tal tw empid vARCHAR(20), ename cha 1n
tablu
emp2(empid VAR CHAR (2o) ename cha age int)
NSERT
NSERT into emp raluus (t01,'puv?, 40), (oo2, 'Pruya, 3o) (oos, 'aihu,
TmeJERT 1 t onf aaus (00o, 40), C2ooo, 30), (3000,Bo)

mpl. enamd, emp. salay Hern empl INNER JOIN

Sulucd
empl. a9e empo, age.

LEFT JOIN: mabthis

he upt
L durn all the Balus hLE
wlh hl Hiqht

SELEC T Lenam e
FROM tabl tabli 2
.oname

t a b l u I. ol_name
-

SFT JO!N tabl 2 ON

KEYS
Ke
uuaue EOtegin Key, Pruna
( bupet Ky: BLpNUE ardidat kuy
6up Ky k a uniaue,
stdid
Pho,
5tdid NamePhoneage

A Khil 842 Supu Ky

Khil 81
18
Bhand 9818
978 19
PAya

landidati Key:
-

CNetu Null
lempty).
1 Hedudanay
withut
unique).
should unique).

A
A Supix Kuy
Supux Kuy Cvalu
should

L d u c i b l u
wrth
atthibudu
used to uniqu
at).
Hipimum Aut or
comb )
HeCOnd
the tobl (5ingt
i table 2
Sid.Srame table Mank
id 5name Mark 6id 6Name

S 2P
S S
S b 2
783B S B 50 A 2C
7 S aB
S3
30
50
B
C a n o l i o al te

anolidate Key
Key
CD Paimaxy Kuy be oL
uniQue valtes aro
u n i u e

emp(emp
INT Himay Key, ha,
p r a m e ch

(entu tal

IV Fonegin Key:
yumneeto the etnun tabls
d

Lunks bihun ta bll

Simply dhay
empame
NT numany Ky,
Lmp emp
Ky );
ptdrID fOregin
 View VUtual table no memD1y).
y p l u c a t s Tabtu:

mom oldnomne
S c t 1lati tabu new tab ase &ilect

neal Yiew viewpame asl uuny)

stect* nom vi ew name a s oop.

)Aagnegation:.
V0 Anithmetue opratue

AV Sultd AVnq ( 5al) nom employee

COunt
Coun()
COunt Ccolrmh rame)
COunt Cdis olLoame)
man
marlsal)
man Cbal)

Sum suntsal)
Stdolev> Stddev bal)
Van iance (bal)
Vaniarce

in
eaual to. (_ oulpul > u a l
.
5ingle
> in
multplu
oukpuk
in multplu ralues.

uke starts
with
a.

>
/ 7: anyg .
anynung
a7:
nols uwitha, Hnewn,
un Hnewn,
with a , - n
Staxts
/>
-

Stt duKeHeNct stud

sltcd sh-name nom
nom tud2

munus
stud-nam

ntnsuetven'.

sel
Sn Ho Stud
Sel
Stdz
unuer
Sn
fmom
Suuct