Oracle Database Design Final Exam

Section 11

(Answer all questions in this section) 1.

In an Oracle database, why would 1_TABLE not work as a table nae!

"ark #or $e%iew (1) &oints

The database does does not understand understand all ca'ital letters letters There is no 'roble here here ou ou can create create a table called 1_TABLE 1_TABLE Ob*ect naes ust not start with a nuber They ust be+in with a letter () TABLE T ABLE is a reser%ed reser%ed word -orrect 2. The

trans#oration #ro an E$ dia+ra to a 'hysical desi+n trans#oration desi+n in%ol%es chan+in+ terinolo+y .econdary /nique Identi0ers becoe


-oluns Tables T ables /nique -onstraints () &riary ey -onstraints -orrect 3.

In a 'hysical data odel, a relationshi' is re'resented re'resented as a cobination o#2 (-hoose Two)


(-hoose all correct answers) -olun &riary ey or /nique ey () -heck -onstraint or /nique ey 3orei+n ey () -orrect 4. T To o

resol%e a any any to any relationshi' relationshi' in a 'hysical odel odel you create create a4an ___________________! _____________ ______!


/nique key constraints Intersection entity Intersection table ()

Two T wo tables with 3orei+n 3orei+n key key constraints between between the -orrect 5.

In a conce'tual odel, any5to5any relationshi's relationshi's are resol%ed %ia a structure called a4an2 ________________


.u'erty'e Intersection Table Intersection Entity () .ubty'e -orrect 6.

An 6Arc I'leentation6 can be done *ust like any other $elationshi' 5 you si'ly add the required 3orei+n eys True or 3alse!


True T rue 3alse () ( ) -orrect 7. The

e7'lanation below is a /ser e7'lanation /ser 8e0ned inte+rity rule rule and ust, ust, there#ore, be anually coded9 the 8atabase cannot en#orce this rule autoatically True T rue or 3alse! 3alse! A 'riary key ust be unique, and no 'art o# the 'riary key can be null


True T rue 3alse () ( ) -orrect 8.

A #orei+n key cannot re#er to a 'riary key in the sae table True or 3alse!


True T rue 3alse () ( ) -orrect 9.

A table ust ha%e a 'riary key True or 3alse!


True T rue 3alse () ( ) -orrect 10. Entity

inte+rity re#ers to2


Tables T ables always containin+ containin+ te7t data Tables T ables always containin+ containin+ nueric data -oluns ha%in+ &riary eys, 3orei+n eys, /nique eys, and -heck constraints de0ned in the database Tables T ables ha%in+ &riary &riary eys, eys, 3orei+n 3orei+n eys, eys, /nique eys, eys, and -heck -heck constraints de0ned in the database () -orrect Section 11

(Answer all questions in this section) 11. A

table ust ha%e at least one candidate key, as well as its 'riary key True T rue or 3alse! 3alse!


True T rue 3alse () ( ) -orrect

Section 12

(Answer all questions in this section) 12. .yste

8ocuentation is de%elo'ed ri+ht at the end once the syste has +one li%e and users ha%e been usin+ it #or a little while ou are ore likely to +et it correct that way True or 3alse!


True T rue 3alse () ( ) -orrect 13. 8urin+

which 'hases o# the .yste 8e%elo'ent Li#e -ycle would you test the syste be#ore rollin+ it out to the users!


Build and Transition

.trate+y and Analysis 8esi+n and &roduction Transition T ransition and &roduction &roduction () -orrect 14. The

#_custoers table contains #_custoers contains the #ollowin+ data2 data2

"ark #or

ID

Name

Address

City

State

Zip

1

Cole Bee

123 Main Street

Orlando

FL

32838

2

Zoe Tee

1!!" Oli#er A#en$e

Boston

MA

!211%

3

Sandra Lee

22 Main Street

Tampa

FL

32&&&

$e%iew (1) &oints

I# you run the #ollowin+ stateent2 8ELETE 3$O" 3_-/.TO"E$. :;E$E I8 <= >9 ;ow any rows will be le#t in the table!

? @ 1 () > -orrect 15. Once

you ha%e created a table, it is not 'ossible to alter the de0nition o# it I# you need to add a new colun you ust delete the table de0nition and create a new, correct table True or 3alse!


True T rue 3alse () ( ) -orrect 16. :hat

coand can be used to create a new row in a table in the database!


-$EATE E: A88 I.E$T () -orrect 17. The

8E.-$IBE coand returns returns all rows rows #ro a table table True True or 3alse! 3alse!

"ark #or

$e%iew (1) &oints True T rue 3alse () ( ) -orrect 18. :hat

coand will return data #ro the database to you!


3ET-; ET .ELE-T () $ET/$ -orrect

Section 15

(Answer all questions in this section) 19.  ou ou

query the the database with this this .CL stateent2 stateent2 .ELE-T  3$O" students9 :hy would you use this stateent!


To T o insert data To T o %iew data () To T o dis'lay the table table structure structure To T o delete data -orrect 20. E%aluate

this .ELE-T stateent2

.ELE-T (salary  raise_'ercent) raise 3$O" e'loyees9 I# the $AI.E_&E$-ET colun only contains null %alues, what will the stateent return!

Only Deroes Only null %alues () A null %alue or a Dero de'endin+ on the %alue o# the .ALA$ colun A null %alue or a nueric %alue de'endin+ on the %alue o# the .ALA$ colun


-orrect Section 15

(Answer all questions in this section) 21. In

a .CL stateent, which clause s'eci0es one or ore coluns to be returned by the query!


.ELE-T () 3$O" :;E$E Any o# the abo%e o'tions9 you can list coluns where%er you want to in a .ELE-T stateent -orrect 22. The

E"&LOEE. E"&LOEE . table contains contains these coluns2 coluns2

.ALA$ /"BE$(,>) BO/. /"BE$(,>) -O""I..IO_&-T -O""I..IO_& -T /"BE$(>,>)


All three coluns contain %alues +reater than Dero There is one row o# data in the table and the the %alues are as #ollows2 .alary = F??, Bonus = F?, -oission_'ct = F E%aluate these two .CL stateents2 1 .ELE-T salary G bonus G coission_'ct  salary 5 bonus A. incoe 3$O" e'loyees9 > .ELE-T (salary G bonus ) G coission_'ct  (salary 5 bonus) incoe 3$O" e'loyees9 :hat will be the result!

.tateent 1 will return a hi+her %alue than stateent > .tateent > will return a hi+her %alue than stateent 1 () .tateent 1 will dis'lay a diHerent colun headin+ One o# the stateents will OT e7ecute -orrect 23. In

which clause o# a .ELE-T stateent would you s'eci#y the nae o# the table or tables bein+ queried!


The 3$O" clause clause () The .ELE-T clause

The :;E$E clause Any o# the abo%e o'tions9 you can list tables where%er you want in a .ELE-T stateent -orrect 24.  ou ou

query the the database with this this .CL stateent2 stateent2

.ELE-T  3$O" transaction :;E$E 'roduct_id = FJK9


:hich .CL .ELE-T stateent ca'abilities are achie%ed when this stateent is e7ecuted!

.election only &ro*ection only .election and 'ro*ection only () &ro*ection, &ro*ectio n, selection and *oinin+ Incorrect .ee .ection 1F Lesson 1

Section 16

(Answer all questions in this section) 25. E%aluate


.ELE-T  3$O" e'loyees :;E$E de'artent_id de'artent_id I(1?, >?, @?) A8 salary  >????9


:hich %alues would cause the lo+ical condition to return T$/E!

8E&A$T"ET_I8 = 1? and .ALA$ = >???? 8E&A$T"ET_I8 = >? and .ALA$ = >???? 8E&A$T"ET_I8 = null and .ALA$ = >???1 8E&A$T"ET_I8 = 1? and .ALA$ = >???1 () -orrect 26. :hich

o# the #ollowin+ coands will dis'lay the last nae concatenated with the *ob I8 #ro the e'loyees table, se'arated by a coa and s'ace, and label the resultin+ colun 6E'loyee and Title6!


.ELE-T 6 last nae6 MMN, NMM 6*ob_id6 G 6E'loyee and Title6 3$O" e'loyees9 .ELE-T last_naeMMN, NMM *ob_id 6E'loyee and Title6 3$O" e'loyees9 () .ELE-T 6 last nae6 MMN, NMM 6*ob_id6 G 6E'loyee and Title6 3$O" e'9

.ELE-T last_naeMM6,6MM *ob_id 6E'loyee and Title6 3$O" e'loyees9 -orrect 27.  ou ou

need to dis'lay dis'lay all the e'loyees e'loyees whose last last naes (o# any len+th) len+th) start with the letters N.N  :hich :;E$E clause should you use!


:;E$E last_nae LIE N.N () :;E$E last_nae LIE N.N :;E$E last_nae LIE N_.N :;E$E last_nae LIE N._N -orrect 28.  ou ou

want to retrie%e retrie%e a list o# custoers custoers whose last last naes be+in with with the letters N3rN  :hich sybol should you include in the :;E$E clause o# your .ELE-T stateent to achie%e the desired result!


 () P Q  -orrect 29. The


LA.T_A"E RA$-;A$>(>F) 3I$.T_A"E RA$-;A$>(>F) E"AIL RA$-;A$>(F?) ou are writin+ ou writin+ a .ELE-T stateent stateent to retrie%e retrie%e the naes o# e'loyees e'loyees that ha%e an eail address .ELE-T last_naeMMN, NMM0rst_nae 6E'loyee ae6 3$O" e'loyees9 :hich :;E$E clause should you use to co'lete this stateent!

:;E$E eail = /LL9 :;E$E eail S= /LL9 :;E$E eail I. /LL9 :;E$E eail I. OT /LL9 () -orrect


30. I#

you write queries usin+ the BET:EE o'erator, it does not atter in what order you enter the %alues, ie BET:EE low %alue A8 hi+h %alue will +i%e the sae result as BET:EE hi+h %alue and low %alue True or 3alse!


True T rue 3alse () ( ) -orrect Section 16

(Answer all questions in this section) 31. :hich

co'arison o'erator searches #or a s'eci0ed character 'attern!


I LIE () BET:EEA8 I. /LL -orrect 32.  ou ou

need to dis'lay dis'lay e'loyees with salaries that are are at least @???? @???? or hi+her :hich co'arison o'erator should you use!


 6=6 = () S= -orrect 33.  ou ou

need to dis'lay dis'lay only unique unique cobinations cobinations o# the LA.T_A"E LA.T_A"E and "AAE$_I8 coluns in the E"&LOEE. table :hich keyword should you include in the .ELE-T clause!


OL /IC/EOE 8I.TI-T () 8I.TI-T$O: -orrect 34. :hen

usin+ the LIE condition to search #or _ sybols, which character can you use as the de#ault E.-A&E o'tion!

"ark #or

$e%iew (1) &oints   U V () -orrect ou ou 35. 

need to dis'lay dis'lay e'loyees whose salary is in the ran+e o# 1???? 1???? throu+h >F??? #or e'loyees in de'artent F?  :hat does the :;E$E clause look like!


:;E$E de'artent_id < F? F??? :;E$E de'artent_id  F? A8 salary BET:EE 1???? A8 >F??? :;E$E de'artent_id = F? A8 salary BET:EE >F??1 A8 1???1 :;E$E de'artent_id = F? A8 salary BET:EE 1???? A8 >F??? () -orrect 36. :hen

usin+ the LIE condition, which sybol re'resents any sequence o# characters o# any len+th55Dero, one, or ore characters!


_  () Q U -orrect

Section 17

(Answer all questions in this section) 37. E%aluate

this .CL stateent2

.ELE-T ee'loyee_id, elast_nae, e0rst_nae, ana+er_id 3$O" e'loyees e, e'loyees  O$8E$ B elast_nae, e0rst_nae :;E$E ee'loyee_id = ana+er_id9 This stateent #ails when e7ecuted e7ecuted :hich chan+e will correct correct the 'roble! 'roble!

$eorder the clauses in the query ()


$eo%e the table aliases in the :;E$E clause $eo%e the table aliases in the O$8E$ B clause Include a ;ARI clause -orrect 38. The


E"&LOEE_I8 /"BE$(K) & LA.T_A"E RA$-;A$>(>F) 3I$.T_A"E RA$-;A$>(>F) 8E&A$T"ET_I8 /"BE$(K) -o'are these two .CL stateents2


1 .ELE-T 8I.TI-T de'artent_id 8E&T, last_nae, 0rst_nae 3$O" e'loyees O$8E$ B de'artent_id9 > .ELE-T de'artent_id 8E&T, last_nae, 0rst_nae 3$O" e'loyees O$8E$ B 8E&T9 ;ow will the results diHer!

One o# the stateents will return a synta7 error One o# the stateents will eliinate all du'licate 8E&A$T"ET_I8 %alues There is no diHerence in the result between between the two stateents stateents The stateents will will sort on diHerent diHerent colun %alues () IncorrectS .ee .ection 1 Lesson @ 39. The

&LAE$. table contains &LAE$. contains these coluns2 coluns2

&LAE$. TABLE2 LA.T_A"E RA$-;A$> (>?) 3I$.T_A"E RA$-;A$> (>?) .ALA$ /"BE$(W,>) TEA"_I8 /"BE$() /"BE$() "AAE$_I8 /"BE$(K) &O.ITIO_I8 /"BE$() ou ust dis'lay ou dis'lay the 'layer nae, nae, tea id, and and salary #or 'layers 'layers whose salary is in the ran+e #ro >F??? throu+h 1????? and whose tea id is in the ran+e o# 1>?? throu+h 1F?? The results ust be sorted by tea id #ro lowest to hi+hest and then #urther sorted by salary #ro hi+hest to lowest :hich stateent should you use to dis'lay the desired result!

.ELE-T last_nae, 0rst_nae, tea_id, salary 3$O" 'layers :;E$E (salary  >F??? O$ salary < 1?????) A8 tea_id BET:EE 1>?? A8 1F?? O$8E$ B tea_id, salary9 .ELE-T last_nae, 0rst_nae, tea_id, salary 3$O" 'layers :;E$E salary BET:EE >F??? A8 1?????


A8 tea_id BET:EE 1>?? A8 1F?? O$8E$ B tea_id, salary 8E.-9 () .ELE-T last_nae, 0rst_nae, tea_id, salary 3$O" 'layers :;E$E salary  >KKKKK A8 salary < 1????? A8 tea_id BET:EE 1>?? A8 1F?? O$8E$ B tea_id A.-, salary 8E.-9 .ELE-T last_nae, 0rst_nae, tea_id, salary 3$O" 'layers :;E$E salary BET:EE >KKKKK A8 1??????1 A8 tea_id BET:EE 1>?? A8 1F?? O$8E$ B tea_id 8E.-, salary 8E.-9 -orrect 40. :hich

stateent about the lo+ical o'erators is true!


The order o# o'erator o'erator 'recedence 'recedence is A8, O$, and OT OT The order o# o'erator o'erator 'recedence 'recedence is A8, OT, OT, and O$ O$ The order o# o'erator o'erator 'recedence 'recedence is OT, O$, and A8 The order o# o'erator o'erator 'recedence 'recedence is OT, A8, A8, and O$ O$ () -orrect Section 17

(Answer all questions in this section) 41. 3ro

le#t to ri+ht, what is the correct order o# &recedence!


Arithetic, -oncatenation, -o'arison, O$ () OT, A8, O$, Arithetic Arithetic, OT, Lo+ical, -o'arison Arithetic, OT, -oncatenation, Lo+ical -orrect 42. :hich

o# the #ollowin+ best describes the eanin+ o# the LIE o'erator!


8is'lay rows based on a ran+e o# %alues To T o test #or %alues in a list "atch a character 'attern () To T o 0nd ull %alues %alues

-orrect 43. :hich

lo+ical o'erator returns T$/E i# either condition is true!


O$ () A8 OT BOT; -orrect 44. :hich

o# the #ollowin+ are T$/E re+ardin+ the lo+ical A8 o'erator!


T$/E A8 T$/E T$/E return 3AL.E T$/E A8 3AL.E 3AL.E return return T$/E 3AL.E A8 T$/E return /LL T$/E A8 3AL.E 3AL.E return return 3AL.E 3AL.E () -orrect 45. :hich

clause would you include in a .ELE-T stateent to sort the rows returned by the LA.T_A"E colun!


O$8E$ B () :;E$E 3$O" ;ARI -orrect 46. E%aluate


.ELE-T last_nae, 0rst_nae, eail 3$O" e'loyees O$8E$ B eail9 I# the E"AIL colun contains null %alues, which stateent is true!

ull eail %alues will be dis'layed 0rst in the result ull eail %alues will be dis'layed last in the result () ull eail %alues will not be dis'layed in the result


The result will not be sorted sorted -orrect 47. E%aluate


.ELE-T last_nae, 0rst_nae, de'artent_id, ana+er_id 3$O" e'loyees9


ou need to sort ou sort data by ana+er ana+er id %alues and and then al'habetically al'habetically by e'loyee last nae and 0rst nae %alues :hich O$8E$ B clause could you use!

O$8E$ B de'artent_id, last_nae O$8E$ B ana+er_id, last_nae, 0rst_nae () O$8E$ B last_nae, 0rst_nae, ana+er_id O$8E$ B ana+er_id, 0rst_nae, last_nae -orrect 48. E%aluate


.ELE-T 0rst_nae, last_nae, eail 3$O" e'loyees O$8E$ B last_nae9


:hich stateent is true!

The rows will not be sorted The rows will be sorted al'habetically al'habetically by the LA.T_A"E LA.T_A"E %alues %alues () The rows will be sorted in re%erse re%erse al'habetical al'habetical order by the LA.T_A"E %alues The rows will be sorted al'habetically al'habetically by the 3I$.T_A"E 3I$.T_A"E and and then the LA.T_A"E %alues -orrect 49. E%aluate


.ELE-T  3$O" e'loyees :;E$E salary  @???? A8 de'artent_id = 1? O$ eail I. OT /LL9 :hich stateent is true!

The O$ condition condition will be e%aluated e%aluated be#ore the A8 A8 condition condition The A8 condition condition will be e%aluated e%aluated be#ore the the O$ condition condition () The O$ and A8 A8 conditions conditions ha%e the sae 'recedence 'recedence an and d will be e%aluated #ro le#t to ri+ht The O$ and A8 A8 conditions conditions ha%e the sae ' 'recedence recedence and and will be e%aluated #ro ri+ht to le#t


-orrect 50.  ou ou

need to create create a re'ort to dis'lay all e'loyees e'loyees that were were hired on or or be#ore Xanuary 1, 1KKJ The data should dis'lay in this # orat2 'mployee Start Date and Salary 1&83( ) Smit* 1!)MA+)1""2 , -!!! :hich .ELE-T stateent could you use!

.ELE-T e'loyee_id MM 5 MM last_nae 6E'loyee6, hire_date MM 4 MM salary 6.tart 8ate and .alary 3$O" e'loyees :;E$E hire_date <= N?15XA51KKJN9 .ELE-T e'loyee_id MMN NMM last_nae 6E'loyee6, hire_date MMN NMM salary 6.tart 8ate and .alary6 3$O" e'loyees :;E$E hire_date <= ?15XA51KK ?15 XA51KKJN9 JN9 .ELE-T e'loyee_id MMN65 6MM last_nae 6E'loyee6, hire_date MM6 4 6MM salary .tart 8ate and .alary6 3$O" e'loyees :;E$E hire_date <= N?15XA51KKJN9 .ELE-T e'loyee_id MMN 5 NMM last_nae NE'loyeeN, hire_date MMN 4 NMM salary N.tart 8ate and .alary6 3$O" e'loyees :;E$E hire_date <= N?15XA51KKJN9 .ELE-T e'loyee_id MMN 5 NMM last_nae 6E'loyee6, hire_date MMN 4 NMM salary 6.tart 8ate and .alary6 3$O" e'loyees :;E$E hire_date <= N?15XA51KKJN9 ()

-orrect


Oracle Database Design Final Exam

Recommend Documents