3.4 accra db2 certification sample

8/13/2019 3.4 Accra DB2 Certification Sample

1/28

2010 IBM Corporation

Information Management

Information Management Ecosystem PartnershipsIBM Canada Lab

Summer/Fall 2010

DB2

Certification Sample Test


2/28

2 2010 IBM Corporation


Which of the following is the major difference betweenrelational data and XML data?

! "elational #ata is self$#escri%ing& 'M( #ata is not B! "elational #ata )as in)erent or#ering& 'M( #ata #oes not C! "elational #ata must %e ta%ular& 'M( #ata #oes not )a*e

to %e ta%ular

D! "elational #ata is comprise# of entities& 'M( #ata iscomprise# of num%ers+ c)aracters+ an# #ates


3/28

, 2010 IBM Corporation


A table named DEPARTMET has the following col!mns"

DEPT#ID

DEPT#AME

MAA$ER

A%$#&ALAR'

Which of the following is the best wa( to )re*ent most !sersfrom *iewing A%$#&ALAR' data?

! -ncr.pt t)e ta%les #ata

B! Create a *ie t)at #oes not contain t)e 3S("4column C! "e*o5e S-(-CT access for t)e 3S("4 column

from users )o s)oul# not see 3S("4 #ata D! Store 3S("4 #ata in a separate ta%le an# grant

S-(-CT pri*ilege for t)at ta%le to t)e appropriate users


4/28



Which of the following statements will catalog the database

M'D+ on the node M',DE and assign it the aliasM'EWD+?

! CT(7 DTBS- m.ne#% T 87D- m.no#e B! CT(7 DTBS- m.ne#% S m.#% T 87D-

m.no#e

C! CT(7 DTBS- m.#% T 87D- m.no#e D! CT(7 DTBS- m.#% S m.ne#% T 87D-

m.no#e


5/28



In which of the following scenarios wo!ld a stored )roced!re

be beneficial? ! n application running on a remote client nee#s to %e

a%le to con*ert #egrees Celsius to #egrees Fa)ren)eit an#*ice *ersa

B! n application running on a remote client nee#s to collect

t)ree input *alues+ perform a calculation using t)e *aluespro*i#e#+ an# store t)e input #ata+ along it) t)e results oft)e calculation in to #ifferent %ase ta%les

C! n application running on a remote client nee#s to trac5e*er. mo#ification ma#e to a ta%le t)at contains sensiti*e

#ata D! n application running on a remote client nee#s to

ensure t)at e*er. ne emplo.ee t)at :oins t)e compan. isassigne# a uni;ue+ se;uential emplo.ee num%er


6/28

< 2010 IBM Corporation


If the following &-L statements are e.ec!ted in the order shown"

REATE TABLE orders(order_num INTEGER NOT NULL,buyer_name VAR HAR(35,amoun! NU"ERI (5,#$REATE UNI%UE IN&E' d)_orderno ON orders(order_num$ Which of the following describes the res!lting beha*ior?

! -*er. 7"D-"38=M *alue entere# must %e uni;ue& )ene*ert)e 7"D-"S ta%le is ;uerie# ros s)oul# %e #ispla.e# in or#er ofincreasing 7"D-"38=M *alues

B! -*er. 7"D-"38=M *alue entere# must %e uni;ue& )ene*ert)e 7"D-"S ta%le is ;uerie# ros ill %e #ispla.e# in no

particular or#er C! Duplicate 7"D-"38=M *alues are alloe#& no ot)er in#e>

can %e create# for t)e 7"D-"S ta%le t)at reference t)e7"D-"38=M column

D! -*er. 7"D-"38=M *alue entere# must %e uni;ue& no ot)er

in#e> can %e create# for t)e 7"D-"S ta%le t)at reference t)e7"D-"38=M column


7/28? 2010 IBM Corporation


An alias can be an alternate name for which two of the

following D+/ objects? ! Se;uence B! Trigger C! ie D! Sc)ema

-! Ta%le


8/28@ 2010 IBM Corporation


Which of the following e*ents will ,T ca!se a trigger to be

acti*ated? ! select operation B! n insert operation C! n up#ate operation D! #elete operation


9/28


10/2810 2010 IBM Corporation


A se0!ence was created with the DDL statement shown below"

CREATE *E%UENCE my_se+ *TART ITH -. INCRE"ENT B/ -. CACHE -.

1ser 1&ER2 s!ccessf!ll( e.ec!tes the following statements in the order shown"

VALUE* NE'T VALUE 0OR my_se+ INTO 12ar$

VALUE* NE'T VALUE 0OR my_se+ INTO 12ar$

1ser 1&ER/ s!ccessf!ll( e.ec!tes the following statements in the order shown"

ALTER *E%UENCE my_se+ RE*TART ITH 5 INCRE"ENT B/ 5 CACHE 5$VALUE* NE'T VALUE 0OR my_se+ INTO 12ar$

After !sers 1&ER2 and 1&ER/ are finished3 !ser 1&ER4 e.ec!tes the following 0!er("

*ELECT NE'T VALUE 0OR my_se+ 0RO" sysbm4sysdummy-

What *al!e will be ret!rned b( the 0!er(?! 9B! 10C! 20D! ,0


11/28



$i*en the following two tables"

5ow man( rows wo!ld be ret!rned !sing the followingstatement?

&ELE6T name 7R,M names3 )oints ! 0 B! 9 C! 10 D! 29


12/28




Ass!ming the following res!lts are desired"

Which of the following joins will )rod!ce the desired res!lts? A4 *ELECT 0RO" !ab- INNER 6OIN !ab# ON 7o8_- 9 7o8_a B4 *ELECT 0RO" !ab- LE0T OUTER 6OIN !ab# ON 7o8_- 9 7o8_a C4 *ELECT 0RO" !ab- RIGHT OUTER 6OIN !ab# ON 7o8_- 9 7o8_a &4 *ELECT 0RO" !ab- 0ULL OUTER 6OIN !ab# ON 7o8_- 9 7o8_a


13/28

1, 2010 IBM Corporation


If the following &-L statements are e.ec!ted in the ordershown"

CREATE TABLE !ab8e- (7- INTEGER, 7# INTEGER$IN*ERT INTO !ab8e- VALUE* (-#3, :5;$

U


14/28



$i*en the following table"

Ass!ming the following res!lts are desired"

Which of the following statements will )rod!ce the desiredres!lts?

! *ELECT UNI%UE 0RO" !ab- B!*ELECT &I*TINCT 0RO" !ab- C! *ELECT UNI%UE( 0RO" !ab- D! *ELECT &I*TINCT( 0RO" !ab-


15/28



Ass!ming table TA+2 contains 288 rows3 which of the

following 0!eries will ret!rn onl( half of the rows a*ailable?

! S-(-CT F"7M ta%1 FI8D FI"ST 90 "7S B! S-(-CT F"7M ta%1 F-TC FI"ST 90 "7S 78(4

C! S-(-CT F"7M ta%1 I(- "738=M E 90 D! S-(-CT F"7M ta%1 M'"7S 90


16/28

1< 2010 IBM Corporation



Which two of the following 0!eries will dis)la( the em)lo(eename and de)artment name for all em)lo(ees that are in&ales?

A4

B4

C4

&4

E4


17/28

1? 2010 IBM Corporation


$i*en the following table definition"

Which of the following 0!eries will ret!rn &ALE&information3 sorted b( &ALEPER&,3 from A to 93 and&ALEDATE3 from most recent to earliest?

A4 *ELECT no7e_no, sa8es_=erson, sa8es_da!e, sa8es_am!

0RO" sa8es *ORT B/ sa8es_=erson,sa8es_da!e &E*C B4 *ELECT no7e_no, sa8es_=erson, sa8es_da!e, sa8es_am!0RO" sa8es *ORT B/ sa8es_=erson &E*C, sa8es_da!e

C4 *ELECT no7e_no, sa8es_=erson, sa8es_da!e, sa8es_am!0RO" sa8es OR&ER B/ sa8es_=erson, sa8es_da!e &E*C

&4 *ELECT no7e_no, sa8es_=erson, sa8es_da!e, sa8es_am!0RO" sa8es OR&ER B/ sa8es_=erson &E*C, sa8es_da!e


18/28

1@ 2010 IBM Corporation



Which of the following 0!eries will ret!rn &ALE&information3 sorted b( &ALEPER&,3 from A to 93 and&ALEDATE3 from most recent to earliest?

A4 *ELECT no7e_no, sa8es_=erson, sa8es_da!e, sa8es_am!

0RO" sa8es *ORT B/ sa8es_=erson,sa8es_da!e &E*C B4 *ELECT no7e_no, sa8es_=erson, sa8es_da!e, sa8es_am!0RO" sa8es *ORT B/ sa8es_=erson &E*C, sa8es_da!e

C4 *ELECT no7e_no, sa8es_=erson, sa8es_da!e, sa8es_am!0RO" sa8es OR&ER B/ sa8es_=erson, sa8es_da!e &E*C

&4 *ELECT no7e_no, sa8es_=erson, sa8es_da!e, sa8es_am!0RO" sa8es OR&ER B/ sa8es_=erson &E*C, sa8es_da!e


19/28

1A 2010 IBM Corporation


Which two of the following statements are tr!e abo!t the

5A%I$ cla!se?

! T)e I8 clause is use# in place of t)e -"-clause!

B! T)e I8 clause uses t)e same s.nta> as t)e-"- clause! C! T)e I8 clause can onl. %e use# it) t)e "7=

B4 clause! D! T)e I8 clause accepts il#car#s!

-! T)e I8 clause uses t)e same s.nta> as t)e I8clause!


20/28




Which of the following &-L statements will remo*e all rowsthat had a &ALEDATE in the (ear 2::;?

! D-(-T- F"7M sales -"- 4-"Gsales3#ateH 1AA9 B! D-(-T- F"7M sales -"- 4-"Gsales3#ateH 1AA9

C! D"7 F"7M sales -"- 4-"Gsales3#ateH 1AA9 D! D"7 F"7M sales -"- 4-"Gsales3#ateH 1AA9


21/28



$i*en the following 6REATE TA+LE statement"

CREATE TABLE !ab8e# LI>E !ab8e-

Which two of the following will ,T occ!r when thestatement is e.ec!ted?

! TB(-2 ill )a*e t)e same column names an# column

#ata t.pes as TB(-1 B! TB(-2 ill )a*e t)e same column #efaults as TB(-1 C! TB(-2 ill )a*e t)e same nulla%ilit. c)aracteristics as

TB(-1 D! TB(-2 ill )a*e t)e same in#e>es as TB(-1!

-! TB(-2 ill )a*e t)e same referential constraints asTB(-1


22/28



Data Concurrenc.

Which of the following isolation le*els will loc< all rows

scanned to b!ild a res!lt data set? ! =ncommitte# "ea# B! Cursor Sta%ilit. C! "ea# Sta%ilit. D! "epeata%le "ea#


23/28



Data%ase Transactions

Create ta%le t%1 G

c1 integer primar. 5e.+

c2 *arc)arG10HH

Consi#ering t)e folloing statements are successfull. e>ecute# inC( it) auto$commit #isa%le#+ )o man. ros are I8S-"T-D

in t)e ta%le t%1JInsert into t%1 *alues G@+ paulH

Insert into t%1 *alues G@+ paulH

commit

Insert into t%1 *alues G@+ paulH

roll%ac5

! 0 B! 1 C! 2 D! ,


24/28

2< 2010 IBM Corporation


Data%ase "eco*er.

db2 backup database sample to /home/db2inst1/backups

Backup successful. The timestamp for this backup image

is:

)ic) of t)e %elo ill restore t)e #ata%aseJ

! #%2 restore #ata%ase sample offline from/)ome/#%2inst1/%ac5ups ta5en at it)out rolling forar#

B! #%2 restore #ata%ase sample offline from/)ome/#%2inst1/%ac5ups ta5en at it)out rolling forar#

C! #%2 restore #ata%ase sample online from

/)ome/#%2inst1/%ac5ups ta5en at it)out rolling forar# D! #%2 restore #ata%ase sample from /)ome/#%2inst1/%ac5upsta5en at it)out rolling forar#


25/28

2? 2010 IBM Corporation



Which of the following will retrie*e the rows that ha*e amissing *al!e in the &ALEPER&,?

A4 *ELECT 0RO" sa8es HERE mdn! 9 ? ? B4 *ELECT 0RO" sa8es HERE mdn! 9 NULL

C4 *ELECT 0RO" sa8es HERE mdn! 9 @ &4 *ELECT 0RO" sa8es HERE mdn! I* NULL


26/28

2@ 2010 IBM Corporation


$i*en the following store )roced!re e.ists"

Proced!re )roc2 =I *ar2 *archar=28>3 ,1T rc ITE$ER>

@@

Which is the correct wa( to in*o


27/28

2A 2010 IBM Corporation


Which is tr!e abo!t an I+M D+/ Instance

@@

? A4 Can on8y 2ae one =er serer

B4 one a8s, a88 ns!ean7es on !2e serer a8 C4 T2ere on8y be one &a!abase =er ns!an7e &4

2a! s a dr!y read 2a! s deC oC A I& !erms D durab8!y444e!7


28/28

2010 IBM Corporation


Information Management Ecosystem PartnershipsIBM Canada Lab

Summer/Fall 2010Questions?

E-mail: [email protected]: DB2 Academic Wo!sho"#

3.4 accra db2 certification sample

Documents