Section 12

Test: Secon 12 Quiz Review your answers, feedback, and queson scores below. n asterisk !"# indicates a correct answer.

Secon 12 Quiz !nswer all quesons in t$is secon#

1.

 funcon%based inde& 'ay be 'ade usin( your own funcons, but only if

t$e funcon funcon is creat created ed usin( t$e t$e )*T*R+-S )*T*R+-ST T clause. clause. True True or /alse0 /alse0

+ark for for Review Review

!1# oints

True !"#

/alse

ncorrect

ncorrect. Refer to Secon 12 esson 2.

2.

3$at is t$e correct synta& synta& to use t$e R*T4R--5 6$rase at osion 0

)*R* T7* *'6Rec S R*8R) !last9na'e e'6loyees.last9na'eT7*, e'6loyees.last9na'eT7*, salary e'6loyees.salaryT7*#; e'69info *'6Rec; e'69id -4+<*R := 1>>;

<*54)T* 4)T* e'6loyees S*T salary = salary " 1.1 3?*R* e'6loyee9id = e'69id %% osion  db's9out6ut.6ut9line!@Aust db's9out6ut.6ut9line !@Aust (ave a raise to @ BB e'69info.last9na'e BB @, w$o now 'akes @ BB e'69info.salary#; *-); +ark for Review !1# oints

R*T4R--5 last9na'e, salary -T8 e'69info; !"#

R*T4R--5 last9na'e, salary T8 e'69info;

last9na'e, salary R*T4R--5 -T8 e'69info;

R*T4R--5 /R8+ e'69info;

ncorrect


C. ... <*5-

n t$e followin( e&a'6le, w$ere do you 6lace t$e 6$rase <4D 8*T0

<*54)T* 4)T* e'6loyees S*T salary = salary " 1.1 3?*R* e'6loyee9id = e'69id %% osion  db's9out6ut.6ut9line!@Aust db's9out6ut.6ut9line !@Aust (ave a raise to @ BB e'69info.last9na'e BB @, w$o now 'akes @ BB e'69info.salary#; *-); +ark for Review !1# oints




R*T4R--5 /R8+ e'69info;

ncorrect


C. ... <*5-


S**T %% %% osion osion  salary %% osion < -T8 v9saltab %% osion  /R8+ e'6loyees 3?*R* de6art'ent9id = 2> 8R)*R <7 salary %% osion ) ; ... +ark for Review !1# oints

osion 

osion < !"#

osion 

osion )

ncorrect


E.

T$e followin( state'ent state'ent is a valid e&a'6le of usin( t$e R*T4R--5 clause.

True or /alse0 )*R* T7* *'6Rec S R*8R) !last9na'e e'6loyees.last9na'eT7*, e'6loyees.last9na'eT7*, salary e'6loyees.salaryT7*#; e'69info *'6Rec; e'69id -4+<*R := 1>>; <*54)T* 4)T* e'6loyees S*T salary = salary " 1.1 3?*R* e'6loyee9id = e'69id R*T4R--5 last9na'e, salary -T8 e'69info; db's9out6ut.6ut9line!@Austt (ave a raise to @ BB e'69info.last9na'e BB db's9out6ut.6ut9line!@Aus @, w$o now 'akes @ BB e'69info.salary#; *-); +ark for Review !1# oints

True !"#

/alse

orrect

orrect

F.

n t$e followin( e&a'6le, w$ere do you 6lace t$e 6$rase )*T*R+-ST0

R*T* R*T* 8R R** /4-T8- total9sal

!69de6t9id - %% osion  e'6loyees.de6art'ent9idT7*# R*T4R- -4+<*R %% osion < S v9total9sal -4+<*R; <*5S**T S4+!salary# -T8 v9total9sal /R8+ e'6loyees 3?*R* de6art'ent9id = 69de6t9in; R*T4R- v9total9sal %% osion ; *-) total9sal;

+ark for Review !1# oints

osion 

osion < !"#

osion 

ncorrect


a(e 1 of C

-e&t

Su''ary



G.

)eter'inisc 'eans t$e funcon will always return t$e sa'e out6ut return

value for any (iven set of in6ut ar(u'ent values. True or /alse0 +ark for Review !1# oints

True !"#

/alse

ncorrect


H.

7ou want to take 'ake a co6y of all t$e cies in t$e world listed in t$e cies

table, w$ic$ contains 'illions of rows. T$e followin( 6rocedure acco'6lis$es t$is eIciently. True or /alse0

R*T* 8R R** R8*)4R* co6y9cies S T7* t9cies S T<* 8/ ciesR83T7* -)*J <7 <-R79-T*5*R; v9ciestab t9e'6; <*5S**T " <4D 8*T -T8 v9ciestab /R8+ cies; /8R i - v9ciestab./RST..v9ciestab.ST -S*RT -T8 new9cies K4*S v9ciestab!i#; *-) co6y9cies; +ark for Review !1# oints

True !"#

/alse

ncorrect


L. two#

3$ic$ of t$e followin( are -8T beneMts of usin( t$e -887 $int0 !$oose

+ark for Review

!1# oints

!$oose all correct answers#

*li'inates e&tra 6rocessin(

4ses a lar(er block of server 'e'ory for faster access !"#

/aster because a sin(le co6y of t$e data is used

*Icient since it uses less 'e'ory

Safer because it uses 6assin( by value !"#

ncorrect


N. /alse0

T$e )<+S9SQ 6acka(e is easier to use t$an *J*4T* ++*)T*. True or

+ark for Review

!1# oints

True

/alse !"#

ncorrect


1>.

7ou want to create a funcon w$ic$ dro6s a table. 7ou write t$e followin(

code: R*T* 8R R** /4-T8- dro6tab !69tab9na'e - KR?R2# R*T4R- <88*- S <*5)R8 T<* 69tab9na'e; R*T4R- TR4*; *J*T83?*- 8T?*RS T?*- R*T4R- /S*; *-);

3$y will t$is 6rocedure not co'6ile successfully0


ncorrect

revious


a(e 2 of C

-e&t

Su''ary



11.

*&a'ine t$e followin( 6rocedure, w$ic$ dro6s a table w$ose na'e is 6assed

as an - 6ara'eter: R*T* 8R R** R8*)4R* dro69tab !69table9na'e - KR?R2# S

v9sql9state'ent KR?R2!1>>#; <*5... *-);

3$ic$ of t$e followin( will work correctly w$en coded in t$e 6rocedure@s e&ecutable secon0 !$oose two.#



v9sql9state'ent := @)R8 T<* @ BB 69table9na'e; *J*4T* ++*)T* v9sql9state'ent; !"#

v9sql9state'ent := @)R8 T<* @; *J*4T* ++*)T* v9sql9state'ent 69table9na'e;

*J*4T* ++*)T* @)R8 T<* 69table9na'e@;

*J*4T* ++*)T* @)R8 T<* @ BB 69table9na'e;

!"#

v9sql9state'ent := @)R8 T<* @ BB 69table9na'e; *J*4T* ++*)T* @v9sql9state'ent@;

ncorrect


12.

 6ublic 6acka(ed 6rocedure contains t$e followin( SQ state'ent:

4)T* e'6loyees S*T salary = salary " 1.1; 3$en is t$is SQ state'ent 6arsed0

+ark for Review

!1# oints

8nly t$e Mrst 'e t$e 6rocedure is e&ecuted

3$en t$e 6acka(e body is created !"#

3$en t$e 6acka(e $eader is loaded into 'e'ory

3$en t$e 6acka(e is loaded into 'e'ory

3$en t$e 6acka(e s6eciMcaon is created

ncorrect


1C.

/or w$ic$ of t$e followin( is it necessary to use )yna'ic SQ0 !$oose

t$ree.# +ark for Review !1# oints


5R-T !"#

T*R !"#

SK*8-T

4)T*

)R8 !"#

ncorrect


1E.

*&a'ine t$e followin( code:

R*T* 8R R** R8*)4R* 'y6roc S 4RS8R c9curs S S**T view9na'e /R8+ user9views; <*5/8R v9curs9rec - c9curs 88 *J*4T* ++*)T* @)R8 K*3 @ BB v9curs9rec.view9na'e; *-) 88; *-);

3$at will $a66en w$en t$is 6rocedure is invoked0


ll views in t$e user@s sc$e'a will be dro66ed. !"#

T$e 6rocedure will raise an e&ce6on because one of t$e views is a co'6le& view.

T$e 6rocedure will raise an e&ce6on because )yna'ic SQ can dro6 tables but cannot dro6 views.

T$e 6rocedure will not co'6ile successfully because t$e synta& of *J*4T* ++*)T* is incorrect.

orrect

orrect

1F.

T$e easiest way to include )) state'ents in a OSQ block is to use t$e

)<+S9SQ 6acka(e. True or /alse0

+ark for Review

!1# oints

True

/alse !"#

ncorrect


revious

a(e C of C

Su''ary



1.

To create a list 'ovies fro' a catalo( of 'illions of tles, you could use t$e

followin( code state'ent to create a collecon. True or /alse0 ... T7* na'etab S T<* 8/ 'ovies.tleT7*; Title9tab na'etab; ... S**T tle <4D 8*T -T8 tle9tab /R8+ 'ovies 8R)*R <7 rental9count )*S; ... +ark for Review !1# oints

True !"#

/alse

orrect

orrect

2.

3$at is t$e 'ain 6ur6ose for usin( t$e R*T4R--5 clause0

+ark for

Review !1# oints

'6rove 6erfor'ance by returnin( a sin(le value

Return 'ore readily any e&ce6ons t$at are raised by t$e state'ent

'6rove 6erfor'ance by 'ini'izin( t$e nu'ber of state'ents

'6rove 6erfor'ance by 'akin( one call to t$e SQ en(ine !"#

ncorrect


C. )*R*

3$at is t$e correct synta& to use t$e R*T4R--5 6$rase at osion 0

T7* *'6Rec S R*8R) !last9na'e e'6loyees.last9na'eT7*, salary e'6loyees.salaryT7*#; e'69info *'6Rec; e'69id -4+<*R := 1>>; <*54)T* e'6loyees S*T salary = salary " 1.1 3?*R* e'6loyee9id = e'69id %% osion  db's9out6ut.6ut9line!@Aust (ave a raise to @ BB e'69info.last9na'e BB @, w$o now 'akes @ BB e'69info.salary#; *-); +ark for Review !1# oints



R*T4R--5 /R8+ e'69info;


ncorrect


E.

T$e followin( state'ent is a valid e&a'6le of usin( t$e R*T4R--5 clause.

True or /alse0 )*R* T7* *'6Rec S R*8R) !last9na'e e'6loyees.last9na'eT7*, salary e'6loyees.salaryT7*#; e'69info *'6Rec; e'69id -4+<*R := 1>>; <*54)T* e'6loyees S*T salary = salary " 1.1 3?*R* e'6loyee9id = e'69id R*T4R--5 last9na'e, salary -T8 e'69info; db's9out6ut.6ut9line!@Aust (ave a raise to @ BB e'69info.last9na'e BB @, w$o now 'akes @ BB e'69info.salary#; *-); +ark for Review !1# oints

True !"#

/alse

orrect

orrect

F. ...


<*5S**T %% osion  salary %% osion < -T8 v9saltab %% osion  /R8+ e'6loyees 3?*R* de6art'ent9id = 2> 8R)*R <7 salary %% osion ) ; ... +ark for Review !1# oints

osion 

osion < !"#

osion 

osion )

ncorrect


a(e 1 of C

-e&t

Su''ary



G. two#

3$ic$ of t$e followin( are -8T beneMts of usin( t$e -887 $int0 !$oose

+ark for Review

!1# oints


4ses a lar(er block of server 'e'ory for faster access !"#

*li'inates e&tra 6rocessin(

/aster because a sin(le co6y of t$e data is used

Safer because it uses 6assin( by value !"#

*Icient since it uses less 'e'ory

ncorrect


H.

7ou want to take 'ake a co6y of all t$e cies in t$e world listed in t$e cies

table, w$ic$ contains 'illions of rows. T$e followin( 6rocedure acco'6lis$es t$is eIciently. True or /alse0 R*T* 8R R** R8*)4R* co6y9cies S T7* t9cies S T<* 8/ ciesR83T7* -)*J <7 <-R79-T*5*R; v9ciestab t9e'6; <*5S**T " <4D 8*T -T8 v9ciestab /R8+ cies; /8R i - v9ciestab./RST..v9ciestab.ST -S*RT -T8 new9cies K4*S v9ciestab!i#; *-) co6y9cies; +ark for Review !1# oints

True !"#

/alse

orrect

orrect

L.

 funcon%based inde& 'ay be 'ade usin( your own funcons, but only if

t$e funcon is created usin( t$e )*T*R+-ST clause. True or /alse0

+ark for Review

!1# oints

True !"#

/alse

orrect

orrect

N.

8nly one call to )<+S9SQ is needed in order to dro6 a table. True or /alse0


True

/alse !"#

orrect

orrect

1>.


R*T* 8R R** R8*)4R* 'y6roc S 4RS8R c9curs S S**T view9na'e /R8+ user9views; <*5/8R v9curs9rec - c9curs 88 *J*4T* ++*)T* @)R8 K*3 @ BB v9curs9rec.view9na'e; *-) 88; *-);







ncorrect

revious


a(e 2 of C

-e&t

Su''ary



11.

3$en SQ state'ents are included wit$in a 6rocedure, t$e state'ents are

6arsed w$en t$e 6rocedure is co'6iled. True or /alse0 !1# oints

+ark for Review

True !"#

/alse

orrect

orrect

12. Review !1# oints

*&ecute arse /etc$

3$ic$ is t$e correct order for t$e e&ecuon Pow of SQ0

+ark for

arse /etc$
arse
ncorrect


1C.

3$at will $a66en w$en t$e followin( 6rocedure is invoked0

R*T* 8R R** R8*)4R* do9so'e9work S 4RS8R c9curs S S**T obect9na'e /R8+ user9obects 3?*R* obect9ty6e = @/4-T8-@; <*5/8R v9curs9rec - c9curs 88 *J*4T* ++*)T* @T*R /4-T8- @ BB v9curs9rec.obect9na'e BB @ 8+*@; *JT 3?*- c9cursR8384-T  2; *-) 88; *-);


T$e Mrst two funcons in t$e user@s sc$e'a will be reco'6iled.

T$e Mrst t$ree funcons in t$e user@s sc$e'a will be reco'6iled. !"#

T$e 6rocedure will not co'6ile successfully because t$e synta& of t$e T*R /4-T8state'ent is incorrect.

ll funcons in t$e user@s sc$e'a will be reco'6iled.

T$e 6rocedure will not co'6ile successfully because you cannot T*R funcons usin( )yna'ic SQ.

orrect

orrect

1E. !1# oints

-a'e two reasons for usin( )yna'ic SQ.

+ark for Review


rovides t$e ability to $andle 'utan( rows w$en e&ecun( a state'ent involvin( t$e sa'e table.

rovides t$e ability to e&ecute SQ state'ents w$ose structure is unknown unl e&ecuon 'e. !"#

*nables session%control state'ents to be wrien and e&ecuted fro' OSQ. !"#

llows fetc$ of data for )+ state'ents.

ncorrect


1F.


+ark for Review

!1# oints


*nables syste' control state'ents to be wrien and e&ecuted fro' OSQ

voids errrors at co'6ile 'e of )+ state'ents

*nables data%deMnion state'ents to be wrien and e&ecuted fro' OSQ !"#

reates a SQ state'ent wit$ varyin( colu'n data, or dierent condions !"#

ncorrect




1. Review !1# oints

arse

3$ic$ is t$e correct order for t$e e&ecuon Pow of SQ0

+ark for

/etc$
*&ecute arse /etc$

arse
orrect

orrect

2.


+ark for Review

!1# oints


llows fetc$ of data for )+ state'ents.

rovides t$e ability to e&ecute SQ state'ents w$ose structure is unknown unl e&ecuon 'e. !"#

*nables session%control state'ents to be wrien and e&ecuted fro' OSQ. !"#

rovides t$e ability to $andle 'utan( rows w$en e&ecun( a state'ent involvin( t$e sa'e table.

orrect

orrect

C.

/or w$ic$ of t$e followin( is it necessary to use )yna'ic SQ0 !$oose

t$ree.# +ark for Review !1# oints


SK*8-T

4)T*

)R8 !"#

T*R !"#

5R-T !"#

orrect

orrect

E.


R*T* 8R R** R8*)4R* 'y6roc S 4RS8R c9curs S S**T view9na'e /R8+ user9views; <*5/8R v9curs9rec - c9curs 88

*J*4T* ++*)T* @)R8 K*3 @ BB v9curs9rec.view9na'e; *-) 88; *-);







orrect

orrect

F.

 6ro(ra''er wants to code a 6rocedure w$ic$ will create a table wit$ a

sin(le colu'n. T$e dataty6e of t$e colu'n will be c$osen by t$e user w$o invokes t$e 6rocedure. T$e 6ro(ra''er writes t$e followin( code: R*T* 8R R** R8*)4R* create9tab !69col9dataty6e - KR?R2# S <*5R*T* T<* newtab !only9col 69col9dataty6e#; *-);

3$y will t$is 6rocedure not co'6ile successfully0


-one of t$e above; t$e 6rocedure will co'6ile successfully.

ncorrect

a(e 1 of C


-e&t

Su''ary



G.

3$ic$ of t$e followin( SQ state'ents can be included in a OSQ block

only by usin( )yna'ic SQ0 !$oose two.#

+ark for Review

!1# oints


SK*8-T

)**T*

T*R !"#

5R-T !"#

S**T ..... /8R 4)T* -83T

orrect

orrect

H.

 SQ state'ent can 6ass t$rou($ several sta(es. 3$ic$ of t$e followin( is

-8T one of t$ese sta(es0 !1# oints

RS*

/*T?

<-)

+ark for Review

R*T4R- !"#

*J*4T*

orrect

orrect

L.

3$ere would you 6lace t$e <4D 8*T state'ent in t$e followin(

e&a'6le0 )*R* T7* )e6tRecTab S T<* 8/ de6art'entsR83T7*; de6t9recs )e6tRecTab; 4RS8R c1 S S**T de6art'ent9id, de6art'ent9na'e, 'ana(er9id, locaon9id %% osion  /R8+ de6art'ents 3?*R* de6art'ent9id  H>; <*58*- c1 %% osion <; /*T? c1 %% osion  -T8 de6t9recs; *-);


osion 

osion <

osion  !"#

orrect

orrect

N. Review !1# oints

True !"#

/alse

/8R can be used wit$ any )+ state'ent. True or /alse0

+ark for

orrect

orrect

1>.

3$at is t$e correct synta& to use t$e R*T4R--5 6$rase at osion 0

)*R* T7* *'6Rec S R*8R) !last9na'e e'6loyees.last9na'eT7*, salary e'6loyees.salaryT7*#; e'69info *'6Rec; e'69id -4+<*R := 1>>; <*54)T* e'6loyees S*T salary = salary " 1.1 3?*R* e'6loyee9id = e'69id %% osion  db's9out6ut.6ut9line!@Aust (ave a raise to @ BB e'69info.last9na'e BB @, w$o now 'akes @ BB e'69info.salary#; *-); +ark for Review !1# oints




R*T4R--5 /R8+ e'69info;

orrect

orrect

revious

a(e 2 of C

-e&t

Su''ary



11.

3$at are beneMts of usin( t$e -887 $int0 !$oose two#

Review !1# oints


4ses a lar(er block of server 'e'ory for faster access

Safer because it uses 6assin( by value

+ark for

*Icient since it uses less 'e'ory !"#

/aster because a sin(le co6y of t$e data is used !"#

orrect

orrect

12.

T$e followin( e&a'6le code will co'6ile successfully. True or /alse0

R*T* 8R R** R8*)4R* de6t96roc S T7* t9de6t S T<* 8/ de6art'entsR83T7* -)*J <7 <-R79-T*5*R; <*5!69s'all9ar( - -4+<*R, 69bi(9ar( 84T -887 t9de6t#; %% re'ainin( code *-) de6t96roc; +ark for Review !1# oints

True !"#

/alse

ncorrect


1C.

/8R can only be used wit$ t$e -S*RT state'ent. True or /alse0


True

/alse !"#

ncorrect


1E.

3$at is wron( wit$ t$is code e&a'6le0

R*T* 8R R** R8*)4R* insert9e'6s S T7* t9e'6 S T<* 8/ e'6loyeesR83T7* -)*J <7 <-R79-T*5*R; v9e'6tab t9e'6; <*5/8R i - v9e'6tab./RST..v9e'6tab.ST -S*RT -T8 e'6loyees K4*S v9e'6tab!i#;

*-) 88; *-) insert9e'6s; +ark for Review !1# oints

v9e'6tab is incorrectly ty6ed.

-ot$in( is wron(; it will co'6ile successfully.

T$e 6$rase s$ould be /8R .

/8R does not require *-) 88. !"#

ncorrect


1F.

T$e followin( state'ent is a valid e&a'6le of usin( t$e R*T4R--5 clause.

True or /alse0 )*R* T7* *'6Rec S R*8R) !last9na'e e'6loyees.last9na'eT7*, salary e'6loyees.salaryT7*#; e'69info *'6Rec; e'69id -4+<*R := 1>>;

Section 12

Recommend Documents