Page 1 of 81 Introduction to Oracle9i Questions and Answers Lesson 1: Basic SELECT Statement 1. The CUSTOMERS table has these columns: CUSTOMER_ID CUSTOMER_N#ME CUSTOMER_#DDRESS CUSTOMER_)%ONE
NUMBER!" NOT NULL $#RC%#R&1''" NOT NULL $#RC%#R&1('" $#RC%#R&&'"
*ou nee+ to ,-o+uce out,ut that states Dea- Custome- custome-_name/ . The custome-_name +ata 0alues come -om the CUSTOMER_N#ME column in the CUSTOMERS table. 2hich statement ,-o+uces this out,ut3 #. SELECT +ea- custome-/ custome-_name/ 4ROM custome-s5 B. SELECT Dea- Custome-/ custome-_name 66 7/7 4ROM custome-s5 C. SELECT 7Dea- Custome- 7 66 custome-_name 7/7 4ROM custome-s5 D. SELECT 7Dea- Custome- 7 66 custome-_name 66 7/7 4ROM custome-s5 E. SELECT Dea- Custome- 66 custome-_name 66 / 4ROM custome-s5 4. SELECT 7Dea- Custome- 7 66 custome-_name 66 7/7 66 4ROM custome-s5 #ns8e-: D
&. # SELECT statement can be use+ to ,e-o-m these th-ee unctions: 1. Choose -o8s -om a table. &. Choose columns -om a table 9. B-in toethe- +ata that is sto-e+ in +ie-ent tables b; c-eatin a lin< bet8een them. 2hich set o
9. 2hich statement co--ectl; +esc-ibes S>L an+ iS>L?)lus Comman+s3 Co mman+s3 #. Both S>L an+ iS>L?,lus allo8 mani,ulation o 0alues in the +atabase. B. iS>L?)lus -econi@es S>L statements an+ sen+s them to the se-0e-5 S>L is the O-acle ,-o,-ieta-; inte-ace o- eAecutin S>L statements. C. iS>L?)lus is a lanuae o- communicatin 8ith the O-acle se-0e- to access +ata5 S>L -econi@es S>L statements an+ sen+s them to the se-0e-. D. S>L mani,ulates +ata an+ table +einitions in the +atabase5 iS>L?)lus +oes not allo8 mani,ulation o 0alues in the +atabase.
Page 2 of 81 #ns8e-: D
!. E0aluate this S>L statement: SELECT e.em,lo;ee_i+/ .1(? e.sala-;" .( ? e.commission_,ct" s.sales amount ? .9( ? e.bonus"" #S C#LC_$#LUE 4ROM em,lo;ees e/ sales s 2%ERE e.em,lo;ee_i+ s.em,_i+5
2hat 8ill ha,,en i ;ou -emo0e all the ,a-entheses -om the calculation3 #. The 0alue +is,la;e+ in the C#LC_$#LUE column 8ill be lo8e-. B. The 0alue +is,la;e+ in the C#LC_$#LUE C #LC_$#LUE column 8ill be hihe-. C. The-e 8ill be no +ie-ence in the 0alue 0 alue +is,la;e+ in the C#LC_$#LUE column. D. #n e--o- 8ill be -e,o-te+. #ns8e-: C
(. 4-om S>L?)lus/ ;ou issue this th is SELECT statement: SELECT? 4-om o-+e-s5 *ou use this statement to -et-ie0e +ata -om a +ata table o- __________. Choose all that a,,l;" #. U,+atin B. $ie8in C. Deletin D. Inse-tin E. T-uncatin #ns8e-: B/ D . 2hich t8o a-e att-ibutes o iS>L?)lus3 Choose t8o" #. iS>L?)lus comman+s cannot be abb-e0iate+. B. iS>L?)lus comman+s a-e accesses -om a b-o8se-. C. iS>L?)lus comman+s a-e use+ to mani,ulate +ata in tables. D. iS>L?)lus comman+s mani,ulate table +einitions in the +atabase. E. iS>L?)lus is the O-acle ,-o,-ieta-; inte-ace o- eAecutin S>L statements. #ns8e-: B/ E
. 2hich S>L statement ene-ates the alias #nnual Sala-; o- the calculate+ column S#L#R*?1&3 #. SELECT ename/ sala-;?1& F#nnual Sala-;G 4ROM em,lo;ees5 B. SELECT ename/ sala-;?1& H#nnual Sala-; 4ROM em,lo;ees5
Page 2 of 81 #ns8e-: D
!. E0aluate this S>L statement: SELECT e.em,lo;ee_i+/ .1(? e.sala-;" .( ? e.commission_,ct" s.sales amount ? .9( ? e.bonus"" #S C#LC_$#LUE 4ROM em,lo;ees e/ sales s 2%ERE e.em,lo;ee_i+ s.em,_i+5
2hat 8ill ha,,en i ;ou -emo0e all the ,a-entheses -om the calculation3 #. The 0alue +is,la;e+ in the C#LC_$#LUE column 8ill be lo8e-. B. The 0alue +is,la;e+ in the C#LC_$#LUE C #LC_$#LUE column 8ill be hihe-. C. The-e 8ill be no +ie-ence in the 0alue 0 alue +is,la;e+ in the C#LC_$#LUE column. D. #n e--o- 8ill be -e,o-te+. #ns8e-: C
(. 4-om S>L?)lus/ ;ou issue this th is SELECT statement: SELECT? 4-om o-+e-s5 *ou use this statement to -et-ie0e +ata -om a +ata table o- __________. Choose all that a,,l;" #. U,+atin B. $ie8in C. Deletin D. Inse-tin E. T-uncatin #ns8e-: B/ D . 2hich t8o a-e att-ibutes o iS>L?)lus3 Choose t8o" #. iS>L?)lus comman+s cannot be abb-e0iate+. B. iS>L?)lus comman+s a-e accesses -om a b-o8se-. C. iS>L?)lus comman+s a-e use+ to mani,ulate +ata in tables. D. iS>L?)lus comman+s mani,ulate table +einitions in the +atabase. E. iS>L?)lus is the O-acle ,-o,-ieta-; inte-ace o- eAecutin S>L statements. #ns8e-: B/ E
. 2hich S>L statement ene-ates the alias #nnual Sala-; o- the calculate+ column S#L#R*?1&3 #. SELECT ename/ sala-;?1& F#nnual Sala-;G 4ROM em,lo;ees5 B. SELECT ename/ sala-;?1& H#nnual Sala-; 4ROM em,lo;ees5
Page 3 of 81 C. SELECT ename/ sala-;?1& #S #nnual Sala-; 4ROM em,lo;ees5 D. SELECT ename/ sala-;?1& #S INITC#)H#NNU#L S#L#R*" 4ROM em,lo;ees #ns8e-: B
J. E0aluate this S>L statement: SELECT e.EM)LO*EE_ID/e.L#S e.EM)LO*EE_ID/e.L#ST_N#ME/e.DE)#RTMENT_ID/ T_N#ME/e.DE)#RTMENT_ID/ +.DE)#RTMENT_N#ME 4ROM EM) e/ DE)#RTMENT + 2%ERE e.DE)#RTMENT_ID +.DE)#RTMENT_ID5
In the statement/ 8hich ca,abilities o a SELECT statement a-e ,e-o-me+3 #. Selection/ ,-o=ection/ =oin B. Die-ence/ ,-o=ection/ =oin C. Selection/ inte-section/ =oin D. Inte-section/ ,-o=ection/ =oin E. Die-ence/ ,-o=ection/ ,-o+uct #ns8e-: #
K. 2hich is an iS>L?)lus comman+3 #. INSERT B. U)D#TE C. SELECT D. DESCRIBE E. DELETE 4. REN#ME #ns8e-: D
1'. *ou nee+ to ,-o+uce a -e,o-t o- mailin labels o- all custome-s. The mailin label must ha0e onl; the custome- name an+ a++-ess. The CUSTOMERS table has these columns: CUST_ID CUST_N#ME CUST_#DDRESS CUST_)%ONE
NUMBER!" $#RC%#R&1''" $#RC%#R&1('" $#RC%#R&&'"
NOT NULL NOT NULL
2hich SELECT statement accom,lishes this tas<3
Page 4 of 81 #. SELECT ? 4ROM custome-s5 B. SELECT name/ a++-ess 4ROM custome-s5 C. SELECT i+/ name/ a++-ess/ ,hone 4ROM custome-s5 D. SELECT cust_name/ cust_a++-ess 4ROM custome-s5 E. SELECT cust_i+/ cust_name/ cust_a++-ess/ cust_,hone 4ROM custome-s5 #ns8e-: D
11. E0aluate this S>L statement: SELECT ename/ sal/ 1&?sal1'' 4ROM em,5 The S#L column sto-es the monthl; sala-; o the em,lo;ee. 2hich chane must be ma+e to the abo0e s;ntaA to calculate the annual com,ensation as monthl; sala-; ,lus a monthl; bonus o 1''/ multi,lie+ b; 1&3 #. No chane is -eui-e+ to achie0e the +esi-e+ -esults. B. SELECT ename/ sal/ 1&?sal1''" 4ROM em,5 C. SELECT ename/ sal/ 1&?sal"1'' 4ROM em,5 D. SELECT ename/ sal1''/?1& 4ROM em,5 #ns8e-: B
19. The STUDENT_R#DES table has these columns STUDENT_ID SEMESTER_END )#
NUMBER1&" D#TE NUMBER!/9"
2hich statement in+s stu+ents 8ho ha0e a -a+e ,oint a0e-ae )#" -eate- than 9.' o- the calen+a- ;ea- &''13 #. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es 2%ERE semeste-_en+ BET2EEN G'1P#N&''1G #ND G91DEC&''1G OR ,a Q 9.5 B. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es 2%ERE semeste-_en+ BET2EEN G'1P#N&''1G #ND G91DEC&''1G #ND ,a t 9.'5 C. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es 2%ERE semeste-_en+ BET2EEN G'1P#N&''1G #ND G91DEC&''1G #ND ,a Q 9.'5 D. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es 2%ERE semeste-_en+ BET2EEN G'1P#N&''1G #ND G91DEC&''1G #ND ,a Q 9.'5 E. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es 2%ERE semeste-_en+ Q G'1P#N&''1G OR semeste-_en+ G91DEC&''1G #ND ,a Q 9.'5
Page 5 of 81 #ns8e-: C
Lesson &: Rest-ictin an+ So-tin Data 1. The EM) table contains these columns: L#ST N#ME $#RC%#R&&(" S#L#R* NUMBER/&" DE)#RTMENT_ID NUMBER" *ou nee+ to +is,la; the em,lo;ees 8ho ha0e not been assine+ to an; +e,a-tment. *ou 8-ite the SELECT statement: SELECT L#ST_N#ME/ S#L#R*/ DE)#RTMENT_ID 4ROM EM) 2%ERE DE)#RTMENT_ID NULL5 2hat is t-ue about this S>L statement3 #. The S>L statement +is,la;s the +esi-e+ -esults. B. The column in the 2%ERE clause shoul+ be chane+ to +is,la; the +esi-e+ -esults. C. The o,e-ato- in the 2%ERE clause shoul+ be chane+ to +is,la; the +esi-e+ -esults. D. The 2%ERE clause shoul+ be chane+ to use an oute- =oin to +is,la; the +esi-e+ -esults. #ns8e-: C
&. The CUSTOMERS table has these columns: CUSTOMER_ID CUSTOMER_N#ME STREET_#DDRESS CIT*_#DDRESS ST#TE_#DDRESS )RO$INCE_#DDRESS COUNTR*_#DDRESS )OST#L_CODE CUSTOMER_)%ONE
NUMBER!" NOT NULL $#RC%#R&1''" NOT NULL $#RC%#R&1('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&1&" $#RC%#R&&'"
2hich statement in+s the -o8s in the CUSTOMERS table that +o not ha0e a ,ostal co+e3
#. SELECT custome-_i+/ custome-_name 4ROM custome-s 2%ERE ,ostal_co+e CONT#INS NULL5 B. SELECT custome-_i+/ custome-_name 4ROM custome-s
Page 6 of 81 2%ERE ,ostal_co+e 7________75 C. SELECT custome-_i+/ custome-_name 4ROM custome-s 2%ERE ,ostal_co+e IS NULL5 D. SELECT custome-_i+/ custome-_name 4ROM custome-s 2%ERE ,ostal co+e IS N$L5 E. SELECT custome-_i+/ custome-_name 4ROM custome-s 2%ERE ,ostal_co+e NULL5 #ns8e-: C 9. The STUDENT_R#DES table has these columns: STUDENT_ID SEMESTER_END )#
NUMBER1&" D#TE NUMBER!/9"
The -eist-a- -eueste+ a -e,o-t listin the stu+ents7 -a+e ,oint a0e-aes )#" so-te+ -om hihest -a+e ,oint a0e-ae to lo8est. 2hich statement ,-o+uces a -e,o-t that +is,la;s the stu+ent ID an+ )# in the so-te+ o-+e- -eueste+ b; the -eist-a-3 #. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es ORDER B* ,a #SC5 B. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es SORT ORDER B* ,a #SC5 C. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es SORT ORDER B* ,a5 D. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es ORDER B* ,a5 E. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es SORT ORDER B* ,a DESC5 4. SELECT stu+ent_i+/ ,a 4ROM stu+ent_-a+es ORDER B* ,a DESC5 #ns8e-: 4
!. The EM)LO*EES table contains these columns: EM)LO*EE_ID L#ST_N#ME POB_ID
NUMBER!" $#RC%#R& &(" $#RC%#R&1'"
*ou 8ant to sea-ch o- st-ins that contain 7S#_7 in the POB_ID column. 2hich S>L statement +o ;ou use3 #. SELECT em,lo;ee_i+/ last_name/ =ob_i+ 4ROM em,lo;ees 2%ERE =ob_i+ LIE 7S#_7 ESC#)E 775 B. SELECT em,lo;ee_i+/ last_name/ =ob_i+ 4ROM em,lo;ees 2%ERE =ob_i+ LIE 7S#_75 C. SELECT em,lo;ee_i+/ last_name/ =ob_i+ 4ROM em,lo;ees 2%ERE =ob_i+ LIE 7S#_7 ESC#)E 5 D. SELECT em,lo;ee_i+/ last_name/ =ob_i+ 4ROM em,lo;ees 2%ERE =ob_i+ 7S#_75
Page 7 of 81 #ns8e- : #
(. The CUSTOMERS table has these columns: CUSTOMER_ID CUSTOMER_N#ME STREET_#DDRESS CIT*_#DDRESS ST#TE_#DDRESS )RO$INCE_#DDRESS COUNTR*_#DDRESS )OST#L_CODE CUSTOMER_)%ONE
NUMBER!" NOT NULL $#RC%#R&1''" NOT NULL $#RC%#R&1('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&1&" $#RC%#R&&'"
# ,-omotional sale is bein a+0e-tise+ to the custome-s in 4-ance. 2hich 2%ERE clause i+entiies custome-s that a-e locate+ in 4-ance3
#. 2%ERE lo8e-count-;_a++-ess" -ance B. 2%ERE lo8e-count-;_a++-ess" 7-ance7 C. 2%ERE lo8e-count-;_a++-ess" IS 7-ance7 D. 2%ERE lo8e-count-;_a++-ess" 7-ance7 E. 2%ERE lo8e-count-;_a++-ess" LIE -ance #ns8e-: B . The )RODUCTS table has these columns: )RODUCT_ID )RODUCT_N#ME )RICE
NUMBER!" $#RC%#R&!(" NUMBERJ/&"
E0aluate this S>L statement: SELECT ? 4ROM )RODUCTS ORDER B* ,-ice/ ,-o+uct_name5 2hat is t-ue about the S>L statement3 #. The -esults a-e not so-te+. B. The -esults a-e so-te+ nume-icall;. C. The -esults a-e so-te+ al,habeticall;. D. The -esults a-e so-te+ nume-icall; an+ then al,habeticall;. #ns8e-: D . E0aluate these t8o S>L statements: SELECT last_name/ sala-;/ hi-e_+ate4ROM EM)LO*EES ORDER B* sala -; DESC5 SELECT last_name/ sala-;/ hi-e_+ate4ROM EM)LO*EES ORDER B* & DESC5 2hat is t-ue about them3
Page 8 of 81 #. The t8o statements ,-o+uce i+entical -esults. B. The secon+ statement -etu-ns a s;ntaA e--o-. C. The-e is no nee+ to s,eci; DESC because the -esults a-e so-te+ in +escen+in o-+e- b; +eault. D. The t8o statements can be ma+e to ,-o+uce i+entical -esults b; a++in a column alias othe sala-; column in the secon+ S>L statement. #ns8e-: # J. EAamine the st-uctu-e o the STUDENTS table: STUDENT_ID STUDENT_N#ME COURSE_ID M#RS ST#RT_D#TE 4INIS%_D#TE
NUMBER $#RC%#R&9'" $#RC%#R&1'" NUMBER D#TE D#TE
NOT NULL/ )-ima-; e; NOT NULL
*ou nee+ to c-eate a -e,o-t o the 1' stu+ents 8ho achie0e+ the hihest -anL an+ 8ho com,lete+ the cou-se in the ;ea- 1KKK. 2hich S>L statement accom,lishes this tas<3 #. SELECT stu+ent_ i+/ ma-L7 ORDER B* ma-L7 ORDER B* ma-L7 ORDER B* ma-
D. SELECT stu+ent_i+/ ma-LG ORDER B* ma-
Page 9 of 81 2%ERE RO2NUM 1' 5 E. SELECT stu+ent i+/ ma-LG5 #ns8e-: D K. E0aluate the S>L statement: SELECT ROUNDTRUNCMOD1''/1'"/1"/&" 4ROM +ual5 2hat 8ill be +is,la;e+3 #. ' B. 1 C. '.'' D. #n e--o- statement #ns8e-: #
1'. *ou 8ant to +is,la; the titles o boo
11. In a SELECT statement that inclu+es a 2%ERE clause/ 8he-e is the ROU) B* clause ,lace+ in the SELECT statement3 #. Imme+iatel; ate- the SELECT clause B. Beo-e the 2%ERE clause
Page 10 of 81 C. Beo-e the 4ROM clause D. #te- the ORDER B* clause E. #te- the 2%ERE clause #ns8e-: E
1&. The STUDENT_R#DES table has these columns: STUDENT_ID SEMESTER_END )#
NUMBER1&" D#TE NUMBER!/9"
The -eiste- has -eueste+ a -e,o-t listin the stu+ents7 -a+e ,oint a0e-aes )#"/ so-te+ -om hihest -a+e ,oint a0e-ae to lo8est 8ithin each semeste-/ sta-tin -om the ea-liest +ate. 2hich statement accom,lishes this3 #. SELECT stu+ent_i+/ semeste-_en+/ ,a 4ROM stu+ent_-a+es ORDER B* semeste-_en+ DESC/ ,a DESC5 B. SELECT stu+ent_i+/ semeste-_en+/ ,a 4ROM stu+ent_-a+es ORDER B* semeste-_en+ #SC/ ,a #SC5 C. SELECT stu+ent_i+/ semeste-_en+/ ,a 4ROM stu+ent_-a+es ORDER B* semeste-_en+/ ,a DESC5 D. SELECT stu+ent_i+/ semeste-_en+/ ,a 4ROM stu+ent_-a+es ORDER B* ,a DESC/ semeste-_en+ DESC5 E. SELECT stu+ent_i+/ semeste-_en+/ ,a 4ROM stu+ent_-a+es ORDER B* ,a DESC/ semeste-_en+ #SC5 #ns8e-: C
19. The ORDERS table has these columns: ORDER_ID CUSTOMER_ID ORDER_TOT#L
NUMBER!" NUMBER1&" NUMBER1'/&"
NOT NULL NOT NULL
The ORDERS table t-ac
Page 11 of 81 R#NE ON o-+e-_total 1'' #ND &'''" INCLUSI$E5 B. SELECT custome-_i+/ o-+e-_i+/ o-+e-_total 4ROM o-+e-s %#$IN o-+e-_total BET2EEN 1'' an+ &'''5 C. SELECT custome-_i+/ o-+e-_i+/ o-+e-_total 4ROM o-+e-s 2%ERE o-+e-_total BET2EEN 1'' an+ &'''5 D. SELECT custome-_i+/ o-+e-_i+/ o-+e-_total 4ROM o-+e-s 2%ERE o-+e-_total Q 1'' an+ &'''5 E. SELECT custome-_i+/ o-+e-_i+/ o-+e-_total 4ROM o-+e-s 2%ERE o-+e-_total Q 1'' an+ o-+e-_total &'''5 #ns8e-: C/ E
1!. The EM) table has these columns: EN#ME $#RC%#R&9(" S#L#R* NUMBERJ/&" %IRE_D#TE D#TE Manaement 8ants a list o names o em,lo;ees 8ho ha0e been 8ith the com,an; omo-e than i0e ;ea-s. 2hich S>L statement +is,la;s the -eui-e+ -esults3 #. SELECT EN#ME 4ROM EM) 2%ERE S*SD#TE%IRE_D#TE Q (5 B. SELECT EN#ME 4ROM EM) 2%ERE %IRE_D#TES*SD#TE Q (5 C. SELECT EN#ME 4ROM EM) 2%ERE S*SD#TE%IRE_D#TE"V9( Q (5 D. SELECT EN#ME 4ROM EM) 2%ERE S*SD#TE%IRE_D#TE"? 9( Q (5 #ns8e-: C
1(. E0aluate these t8o S>L statements: SELECT last_name/ sala-; / hi-e_+ate 4ROM EM)LO*EES ORDER B* sala-; DESC5
Page 12 of 81 SELECT last_name/ sala-;/ hi-e_+ate 4ROM EM)LO*EES ORDER B* & DESC5 2hat is t-ue about them3 #. The t8o statements ,-o+uce i+entical -esults. B. The secon+ statement -etu-ns a s;ntaA e--o-. C. The-e is no nee+ to s,eci; DESC because the -esults a-e so-te+ in +escen+in o-+eb; +eault. D. The t8o statements can be ma+e to ,-o+uce i+entical -esults b; a++in a column alias o- the sala-; column in the secon+ S>L statement. #ns8e-: #
1. 2hich t8o statements a-e t-ue -ea-+in the ORDER B* clause3 Choose t8o" #. The so-t is in ascen+in b; o-+e- b; +eault. B. The so-t is in +escen+in o-+e- b; +eault. C. The ORDER B* clause must ,-ece+e the 2%ERE clause. D. The ORDER B* clause is eAecute+ on the client si+e. E. The ORDER B* clause comes last in the SELECT statement. 4. The ORDER B* clause is eAecute+ i-st in the ue-; eAecution. #ns8e-: #/ E
1. *ou nee+ to +is,la; the last names o those em,lo;ees 8ho ha0e the lette- H# as the secon+ cha-acte- in thei- names. 2hich S>L statement +is,la;s the -eui-e+ -esults3 #. SELECT last_name 4ROM EM) 2%ERE last_name LIE F_#G5 B. SELECT last_name 4ROM EM) 2%ERE last name G?#G C. SELECT last_name 4ROM EM) 2%ERE last name G_#G5 D. SELECT last_name 4ROM EM) 2%ERE last name LIE F?#G #ns8e-: #
Lesson 9: SinleRo8 4unctions 1. Manaement has as
Page 13 of 81 L#ST N#ME $#RCN#R&9(" S#L#R* NUMBERK/&" COMMISION_)CT NUMBER!/&"
NOT NULL NOT NULL
2hich statement ensu-es that a 0alue is +is,la;e+ in the calculate+ columns o- all em,lo;ees3 #. SELECT last_name/ 1&?sala-;? commission_,ct 4ROM em,5 B. SELECT last_name/ 1&?sala-;? commission_,ct/'" 4ROM em,5 C. SELECT last_name/ 1&?sala-;?n0lcommission_,ct/'"" 4ROM em,5 D. SELECT last_name/ 1&?sala-;?+eco+ecommission_,ct/'"" 4ROM em,5 #ns8e-: C
&. The EM)LO*EE tables has these columns: L#ST_N#ME $#RC%#R&9(" S#L#R* NUMBERJ/&" COMMISSION_)CT NUMBER(/&" *ou 8ant to +is,la; the name an+ annual sala-; multi,lie+ b; the commission_,ct oall em,lo;ees. 4o- -eco-+s that ha0e a NULL commission_,ct/ a @e-o must be +is,la;e+ aainst the calculate+ column. 2hich S>L statement +is,la;s the +esi-e+ -esults3 #. SELECT last_name/ sala-; ? 1&" ? commission_,ct 4ROM EM)LO*EES5 B. SELECT last_name/ sala-; ? 1&" ? I4NULLcommission_,ct/ '" 4ROM EM)LO*EES5 C. SELECT last_name/ sala-; ? 1&" ? N$L&commission_,ct/ '" 4ROM EM)LO*EES5 D. SELECT last_name/ sala-; ? 1&" ? N$Lcommission_,ct/ '" 4ROM EM)LO*EES5 #ns8e-: D 9. The EM)LO*EES table contains these columns: L#ST_N#ME $#RC%#R& &(" S#L#R* NUMBER /&" COMMISSION_)CT NUMBER " *ou nee+ to 8-ite a ue-; that 8ill ,-o+uce these -esults: 1. Dis,la; the sala-; multi,lie+ b; the commission_,ct. &. EAclu+e em,lo;ees 8ith a @e-o commission_,ct. 9. Dis,la; a @e-o o- em,lo;ees 8ith a null commission 0alue. E0aluate the S>L statement: SELECT L#ST_N#ME/ S#L#R*?COMMISSION_)CT 4ROM EM)LO*EES 2%ERE COMMISSION_)CT IS NOT NULL5 2hat +oes the statement ,-o0i+e3
Page 14 of 81 #. #ll o the +esi-e+ -esults B. T8o o the +esi-e+ -esults C. One o the +esi-e+ -esults D. #n e--o- statement #ns8e-: C
Page 15 of 81
!. E0aluate the S>L statement: SELECT ROUND!(.K(9/ 1"/ TRUNC!(.K9/ &" 4ROM +ual5 2hich 0alues a-e +is,la;e+3 #. ! an+ !( B. ! an+ !(.K9 C. (' an+ !(.K9 D. (' an+ !(.K E. !( an+ !(.K9 4. !(.K( an+ !(.K9 #ns8e-: C
(. The EM)LO*EES table contains these columns: EM)LO*EE_ID EN#ME POB_ID
NUMBER!" $#RC%#R& &(" $#RC%#R&1'"
2hich S>L statement 8ill -etu-n the EN#ME/ lenth o the EN#ME/ an+ the nume-ic ,osition o the lette- a in the EN#ME column/ o- those em,lo;ees 8hose EN#ME en+s 8ith a the lette- n3 #. SELECT EN#ME/ LENT%EN#ME"/ INSTREN#ME/ 7a7" 4ROM EM)LO*EES 2%ERE SUBSTREN#ME/ 1/ 1" 7n75 B. SELECT EN#ME/ LENT%EN#ME"/ INSTREN#ME/ /1/1" 4ROM EM)LO*EES 2%ERE SUBSTREN#ME/ 1/ 1" 7n75 C. SELECT EN#ME/ LENT%EN#ME"/ SUBSTREN#ME/ 1/1" 4ROM EM)LO*EES 2%ERE INSTREN#ME/ 1/ 1" 7n75 D. SELECT EN#ME/ LENT%EN#ME"/ SUBSTREN#ME/ 1/1" 4ROM EM)LO*EES 2%ERE INSTREN#ME/ 1/ 1" 7n75 #ns8e-: #
Page 16 of 81 . 2hich ou- a-e att-ibutes o sinle -o8 unctions3 Choose ou-." #. cannot be neste+ B. mani,ulate +ata items C. act on each -o8 -etu-ne+ D. -etu-n one -esult ,e- -o8 E. acce,t onl; one a-ument an+ -etu-n onl; one 0alue 4. acce,t a-uments 8hich can be a column o- an eA,-ession #ns8e-: B/ C/ D/ 4
. 2hich S>L statement -etu-ns a nume-ic 0alue3 #. SELECT #DD_MONT%SM#Whi-e_Date"/ " 4ROM EM)5 B. SELECT ROUNDhi-e_+ate" 4ROM EM)5 C. SELECT s;s+atehi-e_+ate 4ROM EM)5 D. SELECT TO_NUMBERhi-e_+ate " 4ROM EM)5 #ns8e-: C
J. 2hich t8o tasL3 Choose !" #. st-in B. cha-acteC. inteeD. calen+aE. nume-ic 4. t-anslation . +ate %. con0e-sion #ns8e-: B/ E/ / %
1'. 2hich S>L statement +is,la;s the +ate Ma-ch 1K/ &''1 in a o-mat that a,,ea-s as HNineteenth o Ma-ch &''1 1&:'':'' #M3 #. SELECT TO_C%#RTO_D#TE71KMa-&''1G/ FDDMon****G"/ FmD+s,th Ho Month **** m%%:MI:SS #MG" NE2_D#TE 4ROM +ual5
Page 17 of 81 B. SELECT TO_C%#RTO_D#TEG1KMa-&''1G/ FDDMon****G"/ FD+s,th Ho Month **** m%%:MI:SS #MG" NE2_D#TE 4ROM +ual5 C. SELECT TO_C%#RTO_D#TEG1KMa-&''1G/ FDDMon****G"/ FmD+s,th Ho Month **** %%:MI:SS #MG" NE2_D#TE 4ROM +ual5 D. SELECT TO_C%#RTO_D#TEG1KMa-&''1G/ FDDMon****"/ FmD+s,th Ho Month ****mt%%:%I:SS #M7" NE2_D#TE 4ROM +ual5 #ns8e-: # 11. *ou 8oul+ li
1&. 2hich SELECT statement 8ill the -esult Fello 8o-l+G -om the st-in F%ello 2o-l+G3 #. SELECT SUBSTR F%ello 2o-l+G/1" 4ROM +ual5 B. SELECT INITC#)TRIM F%ello 2o-l+G/ 1/1"" 4ROM +ual5 C. SELECT LO2ERSUBSTRF%ello 2o-l+G/ 1/ 1" 4ROM +ual5 D. SELECT LO2ERSUBSTRF%ello 2o-l+G/ &/ 1" 4ROM +ual5 E. SELECT LO2ERTRIM F%G 4ROM F%ello 2o-l+G"" 4ROM +ual5 #ns8e-: E 19. 2hich ou- statements co--ectl; +esc-ibe unctions that a-e a0ailable in S>L3 Choose ou-" #. INSTR -etu-ns the nume-ic ,osition o a name+ cha-acte-. B. N$L& -etu-ns the i-st nonnull eA,-ession in the eA,-ession list. C. TRUNC#TE -oun+s the column/ eA,-ession/ o- 0alue to n +ecimal ,laces. D. DECODE t-anslates an eA,-ession ate- com,a-in it to each sea-ch 0alue. E. TRIM t-ims the hea+in o t-ailin cha-acte-s o- both" -om a cha-acte- st-in. 4. N$L com,a-es t8o eA,-essions an+ -etu-ns null i the; a-e eual/ o- the i-st eA,-ession o the; a-e not eual. . NULLI4 com,a-es t8, eA,-essions an+ -etu-ns null i the; a-e eual/ o- the i-st eA,-ession i the; a-e not eual. #ns8e-: #/ D/ E/
1!. 2hich t8o tas
Page 18 of 81 #. Con0e-t 1' to FTENG B. Con0e-t F1'G to 1' C. Con0e-t 1' to F1'G D. Con0e-t FTENG to 1' E. Con0e-t a +ate to a cha-acte- eA,-ession 4. Con0e-t a cha-acte- eA,-ession to a +ate #ns8e-: C/ E
1(. 2hich t8o a-e cha-acte- mani,ulation unctions3 Choose t8o." #. TRIM B. RE)L#CE C. TRUNC D. TO_D#TE E. MOD 4. C#SE #ns8e-: #/ B
1. 2hich SELECT statement shoul+ ;ou use to eAt-act the ;ea- -om the s;stem +ate an+ +is,la; it in the o-mat 1KKJ3 #. SELECT TO_C%#RS*SD#TE/7;;;;7" 4ROM +ual5 B. SELECT TO_D#TES*SD#TE/7;;;;7" 4ROM +ual5 C. SELECT DECODESUBSTRS*SD#TE/ J"/ 7****7" 4ROM +ual5 D. SELECT DECODESUBSTRS*SD#TE/ J"/ 7;ea-7" 4ROM +ual5 E. SELECT TO_C%#RSUBSTRS*SD#TE/ J/&"/7;;;;7" 4ROM +ual5 #ns8e-: #
2hich th-ee SELECT statements +is,la;s &''' in the o-mat H&/'''.''3 Choose th-ee" #. SELECT TO_C%#R&'''/ FX/XXX.XXG" 4ROM +ual5 B. SELECT TO_C%#R&'''/ F'/'''.''G" 4ROM +ual5 C. SELECT TO_C%#R&'''/ FK/KKK.''G" 4ROM +ual5 D. SELECT TO_C%#R&'''/ FK/KKK.KKG" 4ROM +ual5 E. SELECT TO_C%#R&'''/ F&/'''.''G" 4ROM +ual5 4. SELECT TO_C%#R&'''/ FN/NNN.NNG" 4ROM +ual5 #ns8e-: B/ C/ D
Page 19 of 81 Lesson !: Poinin Tables 1. EAamine the st-uctu-e o the EM)LO*EES an+ DE)#RTMENTS tables: EM)LO*EES EM)LO*EE_ID DE)#RTMENT_ID M#N#ER_ID L#ST_N#ME
NUMBER NUMBER NUMBER $#RC%#R&&("
DE)#RTMENTS DE)#RTMENT_ID M#N#ER_ID DE)#RTMENT_N#ME LOC#TION_ID
NUMBER NUMBER $#RC%#R&9(" NUMBER
*ou 8ant to c-eate a -e,o-t +is,la;in em,lo;ee last names/ +e,a-tment names/ an+ locations. 2hich ue-; shoul+ ;ou use to c-eate an eui=oin3 #. SELECT last_name/ +e,a-tment_name/ location_i+ 4ROM em,lo;ees / +e,a-tments 5 B. SELECT em,lo;ees.last_name/ +e,a-tments.+e,a-tment_name/ +e,a-tments.location_i+ 4ROM em,lo;ees e/ +e,a-tments D 2%ERE e.+e,a-tment_i+ +.+e,a-tment_i+5 C. SELECT e.last_name/ +.+e,a-tment_name/ +.location_i+ 4ROM em,lo;ees e/ +e,a-tments D 2%ERE manae-_i+ manae-_i+5 D. SELECT e.last_name/ +.+e,a-tment_name/ +.location_i+ 4ROM em,lo;ees e/ +e,a-tments D 2%ERE e.+e,a-tment_i+ +.+e,a-tment_i+5 #ns8e-: D &. In 8hich t8o cases 8oul+ ;ou use an oute- =oin3 Choose t8o." #. The tables bein =oine+ ha0e NOT NULL columns. B. The tables bein =oine+ ha0e onl; matche+ +ata. C. The columns bein =oine+ ha0e NULL 0alues. D. The tables bein =oine+ ha0e onl; unmatche+ +ata. E. The tables bein =oine+ ha0e both matche+ an+ unmatche+ +ata. 4. Onl; 8hen the tables ha0e a ,-ima-;
9. *ou 8ant to -et-ie0e all em,lo;eesG last names/ alon 8ith thei- manae-Gs last names an+ thei- +e,a-tment names. 2hich ue-; 8oul+ ;ou use3 #. SELECT last_name/ manae-_i+/ +e,a-tment_name 4ROM em,lo;ees e 4ULL OUTER POIN +e,a-tments + ON e.+e,a-tment_i+ +.+e,a-tment_i+"5
Page 20 of 81 B. SELECT e.last_name/ m.last_name/ +e,a-tment_name 4ROM em,lo;ees e LE4T OUTER POIN em,lo;ees m on e.manaae-_i+ m.em,lo;ee_i+" LE4T OUTER POIN +e,a-tments + ON e.+e,a-tment_i+ +.+e,a-tment_i+"5 C. SELECT e.last_name/ m.last_name/ +e,a-tment_name 4ROM em,lo;ees e RIT OUTER POIN em,lo;ees m on e.manae-_i+ m.em,lo;ee_i+" LE4T OUTER POIN +e,a-tments + ON e.+e,a-tment_i+ +.+e,a-tment_i+"5 D. SELECT e.last_name/ m.last_name/ +e,a-tment_name 4ROM em,lo;ees e LE4T OUTER POIN em,lo;ees m on e.manae-_i+ m.em,lo;ee_i+" RIT OUTER POIN +e,a-tments + ON e.+e,a-tment_i+ +.+e,a-tment_i+"5 E. SELECT e.last_name/ m.last_name/ +e,a-tment_name 4ROM em,lo;ees e RI%T OUTER POIN em,lo;ees m on e.manae-_i+ m.em,lo;ee_i+" RI%T OUTER POIN +e,a-tments + ON e.+e,a-tment_i+ +.+e,a-tment_i+"5 4. SELECT last_name/ manae-_i+/ +e,a-tment_name 4ROM em,lo;ees e POIN +e,a-tments + ON e.+e,a-tment_i+ +.+e,a-tment_i+" 5 #ns8e-: B !.
The COMMISSION column sho8s the monthl; commission ea-ne+ b; the em,lo;ee. 2hich t8o tas
Page 21 of 81 EM)LO*EES L#ST_N#ME et@ Da0is in Da0is ochha-
DE)#RTMENT_ID 1' &' &' 9'
S#L#R* 9''' 1('' &&'' (''' ('''
DE)#RTMENTS DE)#RTMENT_ID DE)#RTMENT_N#ME 1' Sales &' Ma-
. EAamine the st-uctu-e o the EM)LO*EES an+ DE)#RTMENTS tables: EM)LO*EES Column name EM)LO*EE_ID EM)_N#ME POB_ID S#L#R* MR_ID DE)#RTMENT ID
Data t;,e NUMBER $#RC%#R& 9'" $#RC%#R& &'" NUMBER NUMBER NUMBER
DE)#RTMENTS Column name Data t;,e DE)#RTMENT_ID NUMBER DE)#RTMENT_N#ME $#RC%#R&9'" MR_ID NUMBER
Rema-
Ree-ences EM)LO*EE_ID COLUMN 4o-ein
Rema-
E0aluate this S>L statement: SELECT em,lo;ee_i+/ e.+e,a-tment_i+/ +e,a-tment_name/ Sala-; 4ROM em,lo;ees e/ +e,a-tments + 2%ERE e.+e,a-tment_i+ +.+e,a-tment_i+5
Page 22 of 81 2hich S>L statement is eui0alent to the abo0e S>L statement3 #. SELECT em,lo;ee_i+/ +e,a-tment_i+/ +e,a-tment_name/ Sala-; 4ROM em,lo;ees 2%ERE +e,a-tment_i+ IN SELECT +e,a-tment_i+ 4ROM +e,a-tments"5 B. SELECT em,lo;ee_i+/ +e,a-tment_i+/ +e,a-tment_name/ Sala-; 4ROM em,lo;ees N#TUR#L POIN +e,a-tments5 C. SELECT em,lo;ee_i+/ +.+e,a-tment_i+/ +e,a-tment_name/ Sala-; 4ROM em,lo;ees e POIN +e,a-tments + ON e.+e,a-tment_i+ +.+e,a-tment_i+5 D. SELECT em,lo;ee_i+/ +e,a-tment_i+/ +e,a-tment_name/ Sala-; 4ROM em,lo;ees POIN +e,a-tments USIN e.+e,a-tment_i+/ +.+e,a-tment_i+"5 #ns8e-: C
. EAamine the +esc-i,tion o the M#RS table: STD_ID STUDENT_N#ME SUBP1 SUBP&
NUMBER!" $#RC%#R&9'" NUMBER9" NUMBER9"
SUBP1 an+ SUBP& in+icate the ma-
J. EAamine the st-uctu-e o the EM)LO*EES/ DE)#RTMENTS/ an+ LOC#TIONS tables. EM)LO*EES EM)LO*EE_ID EM)_N#ME POB_ID
NUMBER $#RC%#R& 9'" $#RC%#R& &'"
NOT NULL/ )-ima-; e;
Page 23 of 81 S#L#R* NUMBER MR_ID NUMBER DE)#RTMENT_ID NUMBER
Ree-ences EM)LO*EE_ID column 4o-ein
DE)#RTMENTS DE)#RTMENT_ID DE)#RTMENT_N#ME MR_ID
NUMBER $#RC%#R&9'" NUMBER
NOT NULL/ )-ima-; e;
LOC#TION_ID
NUMBER
4o-ein
LOC#TIONS LOC#TION_ID CIT*
NUMBER $#RC%#R& 9'"
NOT NULL/ )-ima-; e;
Ree-ences NR_ID column o the EM)LO*EES table
2hich t8o S>L statements ,-o+uce the name/ +e,a-tment name/ an+ the cit; o all the em,lo;ees 8ho ea-n mo-e then 1''''3 Choose t8o" #. SELECT em,_name/ +e,a-tment_name/ cit; 4ROM em,lo;ees e POIN +e,a-tments + USIN +e,a-tment_i+" POIN locations 1 USIN location_i+" 2%ERE sala-; Q 1''''5 B. SELECT em,_name/ +e,a-tment_name/ cit; 4ROM em,lo;ees e/ +e,a-tments +/ locations 1 POIN ON e.+e,a-tment_i+ +.+e,a-tment i+" #ND +.location_i+ 1.location_i+" #ND sala-; Q 1''''5 C. SELECT em,_name/ +e,a-tment_name/ cit; 4ROM em,lo;ees e/ +e,a-tments +/ locations 1 2%ERE sala-; Q 1''''5 D. SELECT em,_name/ +e,a-tment_name/ cit; 4ROM em,lo;ees e/ +e,a-tments +/ locations 1 2%ERE e.+e,a-tment_i+ +.+e,a-tment_i+ #ND +.location_i+ 1.location_i+ #ND sala-; Q 1''''5 E. SELECT em,_name/ +e,a-tment_name/ cit; 4ROM em,lo;ees e N#TUR#L POIN +e,a-tments/ locations 2%ERE sala-; Q 1''''5 #ns8e-: #/ D K. The STUDENT_R#DES table has these columns:
Page 24 of 81 STUDENT_ID SEMESTER_END )#
NUMBER1&" D#TE NUMBER!/9"
The -eist-a- has as
1'. EAamine the +ata o the EM)LO*EES table. EM)LO*EES EM)LO*EE_ID is the ,-ima-;
EM)LO*EE_ID 1'1 1'& 1'9 1'! 1'( 1' 1'J 11' 1&'
EM)_N#ME DE)T_ID Smith &' Ma-tin 1' Ch-is &' Pohn 9' Diana 9' B-;an !' Pennie9' Bob !' Ra0i &'
MR_ID 1&' 1'( 1&' 1'J 1'J 11' 11' 11'
POB_ID S#_RE) CLER IT_#DMIN %R_CLER %R_MR #D_#SST %R_DIR EW_DIR S#_DIR
S#L#R* !''' &('' !&'' &('' (''' 9''' ('' J''' (''
Page 25 of 81 2hich statement lists the ID/ name/ an+ sala-; o the em,lo;ee/ an+ the ID an+ name o the em,lo;ee7s manae-/ o- all the em,lo;ees 8ho ha0e a manae- an+ ea-n mo-e than !'''3 #. SELECT em,lo;ee_i+ Em,_i+/ em,_name Em,lo;ee/ sala-;/ em,lo;ee_i+ M-_i+/ em,_name Manae- 4ROM em,lo;ees 2%ERE sala-; Q !'''5 B. SELECT e.em,lo;ee_i+ Em,_i+/ e.em,_name Em,lo;ee/ e.sala-;/ m.em,lo;ee_i+ M-_i+/ m.em,_name Manae- 4ROM em,lo;ees e/ em,lo;ees m 2%ERE e.m-_i+ m.m-_i+ #ND e.sala-; Q !'''5 C. SELECT e.em,lo;ee_i+ Em,_i+/ e.em,_name Em,lo;ee/ e.sala-;/ m.em,lo;ee_i+ M-_i+/ m.em,_name Manae- 4ROM em,lo;ees e/ em,lo;ees m 2%ERE e.m-_i+ m.em,lo;ee_i+ #ND e.sala-; Q !'''5 D. SELECT e.em,lo;ee_i+ Em,_i+/ e.em,_name Em,lo;ee/ e.sala-;/ m.m-_i+ M-_i+/ m.em,_name manae- 4ROM em,lo;ees e/ em,lo;ees m 2%ERE e.m-_i+ m.em,lo;ee_i+ #ND e.sala-; Q !'''5 E. SELECT e.em,lo;ee_i+ Em,_i+/ e.em,_name Em,lo;ee/ e.sala-;/ m.m-_i+ M-_i+/ m.em,_name Manae- 4ROM em,lo;ees e/ em,lo;ees m 2%ERE e.em,lo;ee_i+ m.em,lo;ee_i+ #ND e.sala-; Q !'''5 #ns8e-: C 11. EAamine the +ata o the EM)LO*EES table. EM)LO*EES EM)LO*EE_ID is the ,-ima-;
EM)LO*EE_ID 1'1 1'& 1'9 1'! 1'( 1' 1'J 11' 1&'
POB_ID Smith Ma-tin Ch-is Pohn Diana B-;an PennieBob Ra0i
E0aluate this S>L statement:
EM)_N#ME S#L#R* &' 1' &' 9' 9' !' 9' !' &'
1&' 1'( 1&' 1'J 1'J 11' 11' 11'
DE)T_ID
MR_ID
S#_RE) CLER IT_#DMIN %R_CLER %R_MR #D_#SST %R_DIR EW_DIR S#_DIR
!''' &('' !&'' &('' (''' 9''' ('' J''' (''
Page 26 of 81 SELECT e.em,lo;ee_i+ Em,_i+/ e.em,_name Em,lo;ee/ e.sala-;/ m.em,lo;ee_i+ M-_i+/ m.em,_name Manae- 4ROM em,lo;ees e/ em,lo;ees m 2%ERE e.m-_i+ m.em,lo;ee_i+ #ND e.sala-; Q !'''5 2hat is its out,ut3 #. EM)_i+ 11' 1&' 1'J 1'9 1'(
EM)LO*EE Bob Ra0i PennieCh-is Diana
S#L#R* J''' ('' ('' !&'' ('''
M-_i+ 11' 11' 1&' 1'J
ManaeBob Ra0i PennieCh-is Diana
B. EM)_i+ 1&' 1'J 1'9 1'(
EM)LO*EE Ra0i PennieCh-is Diana
S#L#R* ('' ('' !&'' ('''
M-_i+ 11' 11' 1&' 1'J
ManaeBob Bob Ra0i Pennie-
C. EM)_i+ 11' 1&' 1'J 1'9 1'(
EM)LO*EE Bob Ra0i PennieCh-is Diana
S#L#R* J''' ('' ('' !&'' ('''
M-_i+
Manae-
11' 11' 1&' 1'J
Bob Bob Ra0i Pennie-
D EM)_i+ 11' 1&' 1'J 1'9 1'(
EM)LO*EE Bob Ra0i PennieCh-is Diana
S#L#R* J''' ('' ('' !&'' ('''
M-_i+ 11' 1&' 1'J 1'9 1'(
ManaeBob Ra0i PennieCh-is Dina
E. The S>L statement ,-o+uces an e--o-. #ns8e-: B 1&. In 8hich case 8oul+ ;ou use a 4ULL OUTER POIN3 #. Both tables ha0e NULL 0alues. B. *ou 8ant all unmatche+ +ata -om one table. C. *ou 8ant all matche+ +ata -om both tables. D. *ou 8ant all unmatche+ +ata -om both tables. E. One o the tables has mo-e +ata than the othe-. 4. *ou 8ant all matche+ an+ unmatche+ +ata -om onl; one table.
Page 27 of 81 #ns8e-: D 19. EAamine the st-uctu-es o the EM)LO*EES an+ T#W tables. EM)LO*EES EM)LO*EE_ID EM)_N#ME POB_ID S#L#R* MR_ID DE)#RTMENT_ID
NUMBER $#RC%#R& 9'" $#RC%#R& &'" NUMBER NUMBER NUMBER
T#W MIN_S#L#R* M#W_S#L#R* T#W_)ERCENT
NUMBER NUMBER NUMBER
NOT NULL/ )-ima-; e;
Ree-ences EM)LO*EE_ID column 4o-ein
)e-centae taA o- i0en sala-; -ane
*ou nee+ to in+ the ,e-centae taA a,,licable o- each em,lo;ee. 2hich S>L statement 8oul+ ;ou use3 #. SELECT em,lo;ee_i+/ sala-;/ taA_,e-cent 4ROM em,lo;ees e/ taA t 2%ERE e.sala-; BET2EEN t.min_sala-; #ND t.maA_sala-;5
B. SELECT em,lo;ee_i+/ sala-;/ taA_,e-cent 4ROM em,lo;ees e/ taA t 2%ERE e.sala-; Q t.min_sala-;/ taA_,e-cent5 C. SELECT em,lo;ee_i+/ sala-;/ taA_,e-cent 4ROM em,lo;ees e/ taA t 2%ERE MINe.sala-;" t.min_sala-; #ND M#We.sala-;" t.maA_sala-; D. *ou cannot in+ the ino-mation because the-e is no common column bet8een the t8o tables. #ns8e-: #
1!. EAamine the st-uctu-e o the EM)LO*EES/ DE)#RTMENTS/ an+ T#W tables. EM)LO*EES EM)LO*EE_ID EM)_N#ME POB_ID S#L#R* MR_ID DE)#RTMENT_ID
NUMBER $#RC%#R& 9'" $#RC%#R& &'" NUMBER NUMBER NUMBER
NOT NULL/ )-ima-; e;
Ree-ences EM)LO*EE_ID column 4o-ein
Page 28 of 81 DE)#RTMENTS DE)#RTMENT_ID DE)#RTMENT_N#ME MR_ID
NUMBER $#RC%#R&9'" NUMBER
NOT NULL/ )-ima-; e; Ree-ences MR_ID column o the EM)LO*EES table
T#W MIN_S#L#R* NUMBER M#W_S#L#R* NUMBER T#W_)ERCENT NUMBER 4o- 8hich situation 8oul+ ;ou use a noneui=oin ue-;3 #. To in+ the taA ,e-centae o- each o the em,lo;ees. B. To list the name/ =ob i+/ an+ manae- name o- all the em,lo;ees. C. To in+ the name/ sala-;/ an+ +e,a-tment name o em,lo;ees 8ho a-e not 8o-L statement b; s,eci;in a minimum o n1 =oin con+itions. #ns8e-: E 1. 2hich th-ee a-e t-ue -ea-+in the use o oute- =oins3 Choose th-ee." #. *ou cannot use IN o,e-ato- in a con+ition that in0ol0es an oute-=oin. B. *ou use " on both si+es o the 2%ERE con+ition to ,e-o-m an oute-=oin. C. *ou use ?" on both si+es o the 2%ERE con+ition to ,e-o-m an oute-=oin. D. *ou use an oute-=oin to see the -o8s that +o not meet the =oin con+ition. E. In the 2%ERE con+ition/ ;ou use " ollo8in the name o the column in the table 8ithout matchin -o8s/ to ,e-o-m an oute-=oin. 4. *ou cannot lin< a con+ition that is in0ol0e+ in an oute-=oin to anothe- con+ition b; usin the OR o,e-ato-. #ns8e-: D/ E/ 4
Page 29 of 81 Lesson (: -ou, 4unctions 1. EAamine the +esc-i,tion o the STUDENTS table: STD_ID NUMBER!" COURSE_ID $#RC%#RD&1'" ST#RT_D#TE D#TE END_D#TE D#TE 2hich t8o a-eate unctions a-e 0ali+ on the ST#RT_D#TE column3 Choose t8o" #. SUMsta-t_+ate" B. #$sta-t_+ate" C. COUNTsta-t_+ate" D. #$sta-t_+ate/ en+_+ate" E. MINsta-t_+ate" 4. M#WIMUMsta-t_+ate" #ns8e-: C/ E &. EAamine the +esc-i,tion o the EM)LO*EES table: EM)_ID NUMBER!" NOT NULL L#ST_N#ME $#RC%#R&9'" NOT NULL 4IRST_N#ME $#RC%#R&9'" DE)T_ID NUMBER&" POB_C#T $#RC%#R&9'" S#L#R* NUMBERJ/&" 2hich statement sho8s the +e,a-tment ID/ minimum sala-;/ an+ maAimum sala-; ,ai+ in that +e,a-tment/ onl; o the minimum sala-; is less then (''' an+ the maAimum sala-; is mo-e than 1('''3 #. SELECT +e,t_i+/ MINsala-;/ M#Wsala-;" 4ROM em,lo;ees 2%ERE MINsala-;" (''' #ND M#Wsala-;" Q 1('''5 B. SELECT +e,t_i+/ MINsala-;"/ M#Wsala-;" 4ROM em,lo;ees 2%ERE MINsala-;" (''' #ND M#Wsala-;" Q 1(''' ROU) B* +e,t_i+5 C. SELECT +e,t_i+/ MINsala-;"/ M#Wsala-;" 4ROM em,lo;ees %#$IN MINsala-;" (''' #ND M#Wsala-;" Q 1('''5 D. SELECT +e,t_i+/ MINsala-;"/ M#Wsala-;" 4ROM em,lo;ees ROU) B* +e,t_i+ %#$IN MINsala-;" (''' #ND M#Wsala-;" E. SELECT +e,t_i+/ MINsala-;"/ M#Wsala-;" 4ROM em,lo;ees
Q 1('''5
Page 30 of 81 ROU) B* +e,t_i+/ sala-; %#$IN MINsala-;" (''' #ND M#Wsala-;" Q 1('''5 #ns8e-: D 9. 2hich t8o a-e t-ue about a-eate unctions3 Choose t8o." #. *ou can use a-eate unctions in an; clause o a SELECT statement. B. *ou can use a-eate unctions onl; in the column list o the SELECT clause an+ in the 2%ERE clause o a SELECT statement. C. *ou can miA sinle -o8 columns 8ith a-eate unctions in the column list o a SELECT statement b; -ou,in on the sinle -o8 columns. D. *ou can ,ass column names/ eA,-essions/ constants/ o- unctions as ,a-amete-s to an a-eate unction. E. *ou can use a-eate unctions on a table/ onl; b; -ou,in the 8hole table as one sinle -ou,. 4. *ou cannot -ou, the -o8s o a table b; mo-e than one column 8hile usin a-eate unctions. #ns8e-: C/ D !. The CUSTOMERS table has these columns: CUSTOMER_ID CUSTOMER_N#ME STREET_#DDRESS CIT*_#DDRESS ST#TE_#DDRESS )RO$INCE_#DDRESS COUNTR*_#DDRESS )OST#L_CODE CUSTOMER_)%ONE
NUMBER!" NOT NULL $#RC%#R&1''" NOT NULL $#RC%#R&1('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&1&" $#RC%#R&&'"
The CUSTOMER_ID column is the ,-ima-;
Page 31 of 81 4. L#REST #ns8e-: C . 2hich clause shoul+ ;ou use to eAclu+e -ou, -esults3 #. 2%ERE B. %#$IN C. RESTRICT D. ROU) B* E. ORDER B* #ns8e-: B . 2hich t8o statements a-e t-ue about 2%ERE an+ %#$IN clauses3 Choose t8o" #. # 2%ERE clause can be use+ to -est-ict both -o8s an+ -ou,s. B. # 2%ERE clause can be use+ to -est-ict -o8s onl;. C. # %#$IN clause can be use+ to -est-ict both -o8s an+ -ou,s. D. # %#$IN clause can be use+ to -est-ict -ou,s onl;. E. # 2%ERE clause C#NNOT be use+ in a ue-; o the ue-; uses a %#$IN clause. 4. # %#$IN clause C#NNOT be use+ in subue-ies. #ns8e-: B/ C
J. EAamine the +esc-i,tion o the EM)LO*EES table: EM)_ID L#ST_N#ME 4IRST_N#ME DE)T_ID
NUMBER!" NOT NULL $#RC%#R&9'" NOT NULL $#RC%#R&9'" NUMBER&"
2hich statement ,-o+uces the numbe- o +ie-ent +e,a-tments that ha0e em,lo;ees 8ith last name Smith3 #. SELECT COUNT?" 4ROM em,lo;ees 2%ERE last_name7Smith75 B. SELECT COUNT +e,t_i+" 4ROM em,lo;ees 2%ERE last_name7Smith75 C. SELECT DISTINCTCOUNT+e,t_i+"" 4ROM em,lo;ees 2%ERE last_name7Smith75 D. SELECT COUNTDISTINCT +e,t_i+" 4ROM em,lo;ees 2%ERE last_name7Smith75 E. SELECT UNI>UE+e,t_i+" 4ROM em,lo;ees 2%ERE last_name7Smith75 #ns8e-: D K. 2hat is t-ue o usin -ou, unctions on columns that contain NULL 0alues3 #. -ou, unctions on columns ino-e NULL 0alues. B. -ou, unctions on columns -etu-nin +ates inclu+e NULL 0alues. C. -ou, unctions on columns -etu-nin numbe-s inclu+e NULL 0alues. D. -ou, unctions on columns cannot be accu-atel; use+ on columns that contain NULL 0alues. E. -ou, unctions on columns inclu+e NULL 0alues in calculations i ;ou use the
Page 32 of 81 #ns8e-: # 1'. The STUDENT_R#DES table has these columns: STUDENT_ID SEMESTER_END )#
NUMBER1&" D#TE NUMBER!/9"
2hich statement in+s the hihest -a+e ,oint a0e-ae )#" ,e- semeste-3 #. SELECT M#W,a" 4ROM stu+ent_-a+es 2%ERE ,a IS NOT NULL5 B. SELECT ,a" 4ROM stu+ent_-a+es ROU) B* semeste-_en+ 2%ERE ,a IS NOT NULL5 C. SELECT M#W,a" 4ROM stu+ent_-a+es 2%ERE ,a IS NOT NULL ROU) B* semeste-_en+5 D. SELECT M#W,a" ROU) B* semeste-_en+ 2%ERE ,a IS NOT NULL 4ROM stu+ent_-a+es5 E. SELECT M#W,a" 4ROM stu+ent_-a+es ROU) B* semeste-_en+ 2%ERE ,a IS NOT NULL5 #ns8e-: C 11. *ou nee+ to 8-ite a S>L statement that -etu-ns em,lo;ee name/ sala-;/ +e,a-tment ID/ an+ maAimum sala-; ea-ne+ in the +e,a-tment o the em,lo;ee o- all em,lo;ees 8ho ea-n less than the maAimum sala-; in thei- +e,a-tment. 2hich statement accom,lishes this tas<3 #. SELECT a.em,_name/ a.sal/ b.+e,t_i+/ M#Wsal" 4ROM em,lo;ees a/ +e,a-tments b 2%ERE a.+e,t_i+ b.+e,t_i+ #ND a.sal M#Wsal" ROU) B* b.+e,t_i+5 B. SELECT a.em,_name/ a.sal/ a.+e,t_i+/ b.maAsal 4ROM em,lo;ees a/ SELECT +e,t_i+/ M#Wsal" maAsal 4ROM em,lo;ees ROU) B* +e,t_i+" b 2%ERE a.+e,t_i+ b.+e,t_i+ #ND a.sal b.maAsal5 C. SELECT a.em,_name/ a.sal/ a.+e,t_i+/ b.maAsal 4ROM em,lo;ees a 2%ERE a.sal SELECT M#Wsal" maAsal 4ROM em,lo;ees b ROU) B* +e,t_i+"5 D. SELECT em,_name/ sal/ +e,t_i+/ maAsal 4ROM em,lo;ees/ SELECT +e,t_i+/ M#Wsal" maAsal 4ROM em,lo;ees ROU) B* +e,t_i+" 2%ERE a.sal maAsal5 #ns8e-: B 19. 2hich clause 8oul+ ;ou use in a SELECT statement to limit the +is,la; to those em,lo;ees 8hose sala-; is -eate- then ('''3 #. ORDER B* S#L#R* Q (''' B. ROU) B* S#L#R* Q (''' C. %#$IN S#L#R* Q (''' D. 2%ERE S#L#R* Q (''' #ns8e-: D
Page 33 of 81 1!. The CUSTOMERS table has these columns: CUSTOMER_ID CUSTOMER_N#ME STREET_#DDRESS CIT*_#DDRESS ST#TE_#DDRESS )RO$INCE_#DDRESS COUNTR*_#DDRESS )OST#L_CODE CUSTOMER_)%ONE
NUMBER !" NOT NULL $#RC%#R& 1''" NOT NULL $#RC%#R& 1('" $#R%C#R& ('" $#RC%#R& ('" $#RC%#R& ('" $#RC%#R& ('" $#RC%#R& 1&" $#RC%#R& &'"
The CUSTOMER_ID column is the ,-ima-; UEU))ERcount-;_a++-ess""" D. COUNT DISTINCT U))ERcount-;_a++-ess" E. COUNTDISTINCT U))ERcount-;_a++-ess""" #ns8e-: E 1(. EAamine the +esc-i,tion o the CUSTOMERS table: CUSTOMER_ID CUSTOMER_N#ME STREET_#DDRESS CIT*_#DDRESS ST#TE_#DDRESS )RO$INCE_#DDRESS COUNTR*_#DDRESS )OST#L_CODE CUSTOMER_)%ONE
NUMBER!" NOT NULL $#RC%#R&1''" NOT NULL $#RC%#R&1('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&('" $#RC%#R&1&" $#RC%#R&&'"
The CUSTOMER_ID column is the ,-ima-;
Page 34 of 81 1. EAamine the +ata in the EM)LO*EES table: L#ST_N#ME et@ Da0is in Da0is
DE)#RTMENT_ID 1' &' &' 9'
S#L#R* 9''' 1('' &&'' ('''
2hich th-ee subue-ies 8o-<3 Choose th-ee" #. SELECT ? 4ROM em,lo;ees 8he-e sala-; Q SELECT MINsala-;" 4ROM em,lo;ees ROU) B* +e,a-tment.i+"5 B. SELECT ? 4ROM em,lo;ees 2%ERE sala-; SELECT #$sala-;" 4ROM em,lo;ees ROU) B* +e,a-tment_i+"5 C. SELECT +istinct +e,a-tment_i+ 4ROM em,lo;ees 2he-e sala-; Q #N* SELECT #$sala-;" 4ROM em,lo;ees ROU) B* +e,a-tment_i+"5 D. SELECT +e,a-tment_i+ 4ROM em,lo;ees 2%ERE S#L#R* Q #LL SELECT #$sala-;" 4ROM em,lo;ees ROU) B* +e,a-tment_i+"5 E. SELECT last_name 4ROM em,lo;ees 2he-e sala-; Q #N* SELECT M#Wsala-;" 4ROM em,lo;ees ROU) B* +e,a-tment_i+"5 4. SELECT +e,a-tment_i+ 4ROM em,lo;ees 2%ERE sala-; Q #LL SELECT #$sala-;" 4ROM em,lo;ees ROU) B* #$S#L#R*""5 #ns8e-: C/ D/ E &. EAamine the +ata -om the ORDERS an+ CUSTOMERS table. ORDERS ORD_ID 1'' 1'1 1'& 1'9 1'! 1'( 1' 1' 1'J
ORD_D#TE 1&P#N&''' 1( 'KM#R&''' 'KM#R&''' 1(M#R&''' &(PUN&''' 1JPUL&''' 1JPUL&''' &1PUL&''' '!#U&''' 1'
CUSTOMERS CUST_ID
CUST_N#ME
CUST_ID
ORD_TOT#L 1''''
!' 9( 1( 1( &' 9( &'
J''' 1&('' 1&''' ''' (''' ''' ('' J'''
CIT*
Page 35 of 81 1' 1( &' &( 9' 9( !'
Smith Bob Ma-tin Ma-; Rina Smith Lin+a
Los #neles San 4-ancisco Chicao Ne8 *o-< Chicao Ne8 *o-< Ne8 *o-<
2hich S>L statement -et-ie0es the o-+e- ID/ custome- ID/ an+ o-+e- total o- the o-+e-s that a-e ,lace+ on the same +a; that Ma-tin ,laces his o-+e-s3 #. SELECT o-+_i+/ cust_i+/ o-+_total 4ROM o-+e-s/ custome-s 2%ERE cust_nameGMatinG #ND o-+_+ate IN G1JPUL&'''G/G&1PUL&'''G"5 B. SELECT o-+_i+/ cust_i+/ o-+_total 4ROM o-+e-s 2he-e o-+_+ate IN SELECT o-+_+ate 4ROM o-+e-s 2%ERE cust_i+ SELECT cust_i+ 4ROM custome-s 2%ERE cust_name FMa-tinG""5 C. SELECT o-+_i+/ cust_i+/ o-+_total 4ROM o-+e-s 2he-e o-+_+ate IN SELECT o-+_+ate 4ROM o-+e-s/ custome-s 2he-e cust_name FMa-tinG"5 D. SELECT o-+_i+/ cust_i+/ o-+_total 4ROM o-+e-s 2%ERE cust_i+ IN SELECT cust_i+ 4ROM custome-s 2%ERE cust name FMa-tinG"5 #ns8e-: B
9. E0aluate the S>L statement: 1 SELECT a.em,_name/ a.sal/ a.+e,t_i+/ b.maAsal & 4ROM em,lo;ees a/ 9 SELECT +e,t_i+/ M#Wsal" maAsal !. 4ROM em,lo;ees ( ROU) B* +e,t_i+" b 2%ERE a.+e,t_i+ b.+e,t_i+ #ND a.sal b.maAsal5 2hat is the -esult o the statement3 #. The statement ,-o+uces an e--o- at line 1. B. The statement ,-o+uces an e--o- at line 9. C. The statement ,-o+uces an e--o- at line . D. The statement -etu-ns the em,lo;ee name/ sala-;/ +e,a-tment ID/ an+ maAimum sala-; ea-ne+ in the +e,a-tment o the em,lo;ee o- all +e,a-tments that ,a; less sala-; then the maAimum sala-; ,ai+ in the com,an;. E. The statement -etu-ns the em,lo;ee name/ sala-;/ +e,a-tment ID/ an+ maAimum sala-; ea-ne+ in the +e,a-tment o the em,lo;ee o- all em,lo;ees 8ho ea-n less than the maAimum sala-; in thei- +e,a-tment.
Page 36 of 81 #ns8e-: E !. EAamine the st-uctu-e o the EM)LO*EES an+ NE2_EM)LO*EES tables: EM)LO*EES EM)LO*EE_ID 4IRST_N#ME L#ST_N#ME %IRE_D#TE
NUMBER )-ima-; e; $#RC%#RD&&(" $#RC%#RD&&(" D#TE
NE2 EM)LO*EES EM)LO*EE_ID NUMBER )-ima-; e; N#ME $#RC%#R&'" 2hich U)D#TE statement is 0ali+3 #. U)D#TE ne8_em,lo;ees SET name Select last_name66 i-st_name 4ROM em,lo;ees 2he-e em,lo;ee_i+ 1J'" 2%ERE em,lo;ee_i+ 1J'5 B. U)D#TE ne8_em,lo;ees SET name SELECT last_name66i-st_name 4ROM em,lo;ees" 2%ERE em,lo;ee_i+ 1J'5 C. U)D#TE ne8_em,lo;ees SET name SELECT last_name66 i-st_name 4ROM em,lo;ees 2%ERE em,lo;ee_i+ 1J'" 2%ERE em,lo;ee_i+ SELECT em,lo;ee_i+ 4ROM ne8 em,lo;ees"5 D. U)D#TE ne8_em,lo;ees SET name SELECT last name66 i-st_name 4ROM em,lo;ees 2%ERE em,lo;ee_i+ SELECT em,lo;ee_i+ 4ROM ne8_em,lo;ees"" 2%ERE em,lo;ee_i+ 1J'5 #ns8e-: # (. 2hich t8o statements about subue-ies a-e t-ue3 Choose t8o." #. # sinle -o8 subue-; can -et-ie0e +ata -om onl; one table. B. # S>L ue-; statement cannot +is,la; +ata -om table B that is -ee--e+ to in its subue-;/ unless table B is inclu+e+ in the main ue-;7s 4ROM clause. C. # S>L ue-; statement can +is,la; +ata -om table B that is -ee--e+ to in its subue-;/ 8ithout inclu+in table B in its o8n 4ROM clause. D. # sinle -o8 subue-; can -et-ie0e +ata -om mo-e than one table. E. # sinle -o8 subue-; cannot be use+ in a con+ition 8he-e the LIE o,e-ato- is use+ o- com,a-ison. 4. # multi,le-o8 subue-; cannot be use+ in a con+ition 8he-e the LIE o,e-ato- is use+ o- com,a-ison. #ns8e-: B/ D . EAamine the st-uctu-e o the STUDENTS table: STUDENT_ID STUDENT_N#ME COURSE_ID
NUMBER $#RC%#R&9'" $#RC%#R&1'"
NOT NULL/ )-ima-; e; NOT NULL
Page 37 of 81 M#RS ST#RT_D#TE 4INIS%_D#TE
NUMBER D#TE D#TE
*ou nee+ to c-eate a -e,o-t o the 1' stu+ents 8ho achie0e+ the hihest -anL an+ 8ho com,lete+ the cou-se in the ;ea- 1KKK. 2hich S>L statement accom,lishes this tas<3 #. SELECT stu+ent_ i+/ ma-L7 ORDER B* ma-L7 ORDER B* ma-L7 ORDER B* ma-L75 #ns8e-: D . EAamine the +ata -om the EM) table: EM)_ID 1 & 9 ! ( J
DE)T_ID 1' &' 1' 1' 9' 9' 1' &'
COMMISSION ('' 1''' '' J'' &'' 9''
Page 38 of 81 The COMMISSION column sho8s the monthl; commission ea-ne+ b; the em,lo;ee. 2hich th-ee tas
NUMBER )-ima-; e; $#RC%#R&&(" $#RC%#R&&(" NUMBER NUMBER
2hat is the co--ect s;ntaA o- an inline 0ie83 #. SELECT a.last_name/ a.sala-;/ a.+e,a-tment_i+/ b.maAsal 4ROM em,lo;ees a/ SELECT +e,a-tment_i+/ maAsala-;"maAsal 4ROM em,lo;ees ROU) B* +e,a-tment_i+" b 2%ERE a.+e,a-tment_i+ b.+e,a-tment_i+ #ND a.sala-; b.maAsal5 B. SELECT a.last name/ a.sala-;/ a.+e,a-tment_i+ 4ROM em,lo;ees a 2%ERE a.+e,a-tment_i+ IN SELECT +e,a-tment_i+ 4ROM em,lo;ees b ROU) B* +e,a-tment_i+ ha0in sala-; SELECT maAsala-;" -om em,lo;ees"" C. SELECT a.last_name/ a.sala-;/ a.+e,a-tment_i+ 4ROM em,lo;ees a 2%ERE a.sala-; SELECT maAsala-;" 4ROM em,lo;ees b 2%ERE a.+e,a-tment_i+ b.+e,a-tment_i+"5
Page 39 of 81 D. SELECT a.last_name/ a.sala-;/ a.+e,a-tment_i+ 4ROM em,lo;ees a 2%ERE a.+e,a-tment_i+/ a.sala-;" IN SELECT +e,a-tment_i+/ a.sala-;" IN SELECT +e,a-tment_i+ maAsala-;" 4ROM em,lo;ees b ROU) B* +e,a-tment_i+ ORDER B* +e,a-tment_i+"5 #ns8e-: # K. 2hich th-ee statements about subue-ies a-e t-ue3 Choose th-ee" #. # sinle -o8 subue-; can -et-ie0e onl; one column an+ one -o8. B. # sinle -o8 subue-; can -et-ie0e onl; one -o8 but man; columns. C. # multi,le -o8 subue-; can -et-ie0e multi,le -o8s an+ multi,le columns. D. # multi,le -o8 subue-; can be com,a-e+ b; usin the HQ o,e-ato-. E. # sinle -o8 subue-; can use the IN o,e-ato-. 4. # multi,le -o8 subue-; can use the H o,e-ato-. #ns8e-: B/ C/ D 1'. 2hich o,e-ato- can be use+ 8ith a multi,le-o8 subue-;3 #. B. LIE C. BET2EEN D. NOT IN E. IS 4. Q #ns8e-: D 11. EAamine the +ata -om the ORDERS an+ CUSTOMERS tables. ORDERS
ORD_ID 1'' 1'1 1'& 1'9 1'! 1'( 1' 1' 1'K
ORD_D#TE 1&P#N&''' 'KM#R&''' 'KM#R&''' 1(M#R&''' &(PUN&''' 1JPUL&''' 1JPUL&''' &1PUL&''' '!#U&'''
CUST_ID 1( !' 9( 1( 1( &' 9( &' 1'
ORD_TOT#L 1'''' J''' 1&('' 1&''' ''' (''' ''' ('' J'''
CUSTOMERS
CUST_ID 1' 1( &'
CUST_N#ME Smith Bob Ma-tin
CIT* Los #neles San 4-ancisco Chicao
Page 40 of 81 &( 9' 9( !'
Ma-; Rina Smith Lin+
Ne8 *o-< Chicao Ne8 *o-< Ne8 *o-<
E0aluate the S>L statement: SELECT ? 4ROM o-+e-s 2%ERE cust_i+ SELECT cust_i+ 4ROM custome-s 2%ERE cust_name 7Smith7"5 2hat is the -esult 8hen the ue-; is eAecute+3
#. ORD_ID 1'& 1' 1'J
ORD_D#TE 'KM#R&'' 1JPUL&''' '!#U&'''
CUST_ID 9( 9( 1'
ORD_TOT#L 1&('' ''' J'''
B. ORD_ID 1'& 1'
ORD_D#TE 'KM#R&'' 1JPUL&'''
CUST_ID 9( 9(
ORD_TOT#L 1&('' '''
C. ORD_ID 1'J
ORD_D#TE '!#U&'''
CUST_ID 1'
ORD_TOT#L J'''
D. The ue-; ails because the subue-; -etu-ns mo-e than one -o8. E. The ue-; ails because the oute- ue-; an+ the inne- ue-; a-e usin +ie-ent tables. #ns8e-: D 1&. *ou +eine a multi,le-o8 subue-; in the 2%ERE clause o an S>L ue-; 8ith a com,a-ison o,e-ato- . 2hat ha,,ens 8hen the main ue-; is eAecute+3 #. The main ue-; eAecutes 8ith the i-st 0alue -etu-ne+ b; the subue-;. B. The main ue-; eAecutes 8ith the last 0alue -etu-ne+ b; the subue-;. C. The main ue-; eAecutes 8ith all the 0alues -etu-ne+ b; the subue-;. D. The main ue-; ails because the multi,le-o8 subue-; cannot be use+ 8ith the com,a-ison o,e-ato-. E. *ou cannot +eine a multi,le-o8 subue-; in the 2%ERE clause o a S>L ue-;. #ns8e-: D 19. # subue-; can be use+ to _________. #. C-eate -ou,s o +ata B. So-t +ata in a s,eciic o-+e-
Page 41 of 81 C. Con0e-t +ata to a +ie-ent o-mat D. Ret-ie0e +ata base+ on an un
1!. In 8hich scena-io 8oul+ TO) N anal;sis be the best solution3 #. *ou 8ant to i+enti; the most senio- em,lo;ee in the com,an;. B. *ou 8ant to in+ the manae- su,e-0isin the la-est numbe- o em,lo;ees. C. *ou 8ant to i+enti; the ,e-son 8ho ma
EM)LO*EE_ID EM)_N#ME DE)T_ID 1'1 Smith &' 1'& Ma-tin 1' 1'9 Ch-is &' 1'! Pohn 9' 1'( Diana 9' 1' Smith !' 1'J Pennie9' 11' Bob !' 1&' Ra0i &' DE)#RTMENTS DE)#RTMENT_ID 1' &' 9' !'
MR_ID 1&' 1'( 1&' 1'J 1'J 11' 11' 11'
POB_ID S#_RE) CLER IT_#DMIN %R_CLER IT_#DMIN #D_#SST %R_DIR EW_DIR S#_DIR
S#L#R* !''' &('' !&'' &('' (''' 9''' ('' J''' (''
DE)#RTMENT_N#ME #+min E+ucation IT %uman Resou-ces
On the EM)LO*EES table/ EM)LO*EE_ID is the ,-ima-;
Page 42 of 81 2hat ha,,ens 8hen the statement is eAecute+3 #. The statement eAecutes successull;/ lea0es the manae- ID as the eAistin 0alue/ an+ chanes the sala-; to !''' o- the em,lo;ees 8ith ID 1'9 an+ 1'(. B. The statement eAecutes successull;/ chanes the manae- ID to NULL/ an+ chanes the sala-; to !''' o- the em,lo;ees 8ith ID 1'9 an+ 1'(. C. The statement eAecutes successull;/ chanes the manae- ID to NULL/ an+ chanes the sala-; to 9''' o- the em,lo;ees 8ith ID 1'9 an+ 1'(. D. The statement ails because the-e is mo-e than one -o8 matchin the em,lo;ee name Smith. E. The statement ails because the-e is mo-e than one -o8 matchin the IT_#DMIN =ob ID in the EM)LO*EES table. 4. The statement ails because the-e is no 7#+minist-ation7 +e,a-tment in the DE)#RTMENTS table. #ns8e-: D 1. EAamine the st-uctu-e o the EM)LO*EES an+ NE2_EM)LO*EES tables: EM)LO*EES EM)LO*EE_ID 4IRST_N#ME L#ST_N#ME %IRE_D#TE
NUMBER )-ima-; e; $#RC%#R& &(" $#RC%#R& &(" D#TE
NE2 EM)LO*EES EM)LO*EE_ID N#ME
NUMBER $#RC%#R& '"
)-ima-; e;
2hich DELETE statement is 0ali+3 #. DELETE 4ROM em,lo;ees 2%ERE em,lo;ee_i+ SELECT em,lo;ee_i+ 4ROM em,lo;ees"5 B. DELETE ? 4ROM em,lo;ees 2%ERE em,lo;ee_i+ SELECT em,lo;ee_i+ 4ROM ne8_em,lo;ees"5 C. DELETE 4ROM em,lo;ees 2%ERE em,lo;ee_i+ IN SELECT em,lo;ee_i+ 4ROM ne8_em,lo;ees 2%ERE name 7Ca--e;7"5 D. DELETE ? 4ROM em,lo;ees 2%ERE em,lo;ee_i+ IN SELECT em,lo;ee_i+ 4ROM ne8_em,lo;ees 2%ERE last_name 7Ca--e;7"5 #ns8e-: C
1. In 8hich ou- clauses can a subue-; be use+3 Choose ou-."
Page 43 of 81 #. in the INTO clause o an INSERT statement B. in the 4ROM clause o a SELECT statement C. in the ROU) B* clause o a SELECT statement D. in the 2%ERE clause o a SELECT statement E. in the SET clause o an U)D#TE statement 4. in the $#LUES clause o an INSERT statement #ns8e-: B/ D/ E/ 4 1J. EAamine the +ata in the EM)LO*EES table. L#ST_N#ME et@ Da0is in Da0is Y
DE)#RTMENT_ID 1' &' &' 9'
S#L#R* 9''' 1('' &&'' '''
EAamine the subue-;: SELECT last_name 4ROM em,lo;ees 2%ERE sala-; IN SELECT M#Wsala-;" 4ROM em,lo;ees ROU) B* +e,a-tment_i+"5 2hich statement is t-ue3 #. The SELECT statement is s;ntacticall; accu-ate. B. The SELECT statement +oes not 8o-< because the-e is no %#$IN clause. C. The SELECT statement +oes not 8o-< because the column s,eciie+ in the ROU) B* clause is not in the SELECT list. D. The SELECT statement +oes not 8o-< because the ROU) B* clause shoul+ be in the main ue-; an+ not in the subue-;. #ns8e-: # 1K. 2hich t8o statements about subue-ies a-e t-ue3 Choose t8o." #. # subue-; shoul+ -et-ie0e onl; one -o8. B. # subue-; can -et-ie0e @e-o o- mo-e -o8s. C. # subue-; can be use+ onl; in S>L ue-; statements. D. Subue-ies C#NNOT be neste+ b; mo-e than t8o le0els. E. # subue-; C#NNOT be use+ in an S>L ue-; statement that uses -ou, unctions. 4. 2hen a subue-; is use+ 8ith an ineualit; com,a-ison o,e-ato- in the oute- S>L statement/ the column list in the SELECT clause o the subue-; shoul+ contain onl; one column. #ns8e-: B/ 4 &' ORDERS ORD_ID
ORD_D#TE
CUST_ID
ORD_TOT#L
Page 44 of 81 1'' 1'1 1'& 1'9 1'! 1'( 1' 1'J
1&P#B&''' 'KM#R&''' 'KM#R&''' 1(M#R&''' &(PUN&''' 1JPUL&''' 1JPUL&''' '!#U&'''
CUSTOMERS CUST_ID 1' 1( &' &( 9' 9( !'
CUST_N#ME Smith Bob Ma-tin Ma-; Rina Smith Lin+a
1( !' 9( 1( 1( &' 9( 1'
J'''
CIT* Los #neles San 4-ancisco Chicao Ne8 *o-< Chicao Ne8 *o-< Ne8 *o-<
E0aluate this S>L statement: SELECT cust_i+/ o-+_total 4ROM o-+e-s 2%ERE o-+_total Q #N* SELECT o-+_total 4ROM o-+e-s 2%ERE cust_i+ IN SELECT cust_i+ 4ROM custome-s 2%ERE cit; LIE FNe8 *o-
ORD_TOT#L 1'''' 1&('' 1&'''
o< C. CUST_ID 1( !' 1( 1( &' 9(
ORD_TOT#L 1'''' J''' 1&''' ''' (''' '''
1'''' J''' 1&('' 1&('' ''' (''' '''
Page 45 of 81 &' 1'
('' J'''
D. CUST_ID 1( &' &'
ORD_TOT#L ''' (''' (''
E. The ue-; -etu-ns no -o8s. 4. The ue-; ails because #N* is not a 0ali+ o,e-ato- 8ith a subue-;. #ns8e-: #
Page 46 of 81 Lesson : Data Mani,ulation 1. *ou a++e+ a )%ONE_NUMBER column o NUMBER +ata t;,e to an eAistin EM)LO*EES table. The EM)LO*EES table al-ea+; contains -eco-+s o 1'' em,lo;ees. No8/ ;ou 8ant to ente- the ,hone numbe-s o each o the 1'' em,lo;ees into the table. Some o the em,lo;ees ma; not ha0e a ,hone numbe- a0ailable. 2hich +ata mani,ulation o,e-ation +o ;ou ,e-o-m3 #. MERE B. INSERT C. U)D#TE D. #DD E. ENTER 4. *ou cannot ente- the ,hone numbe-s o- the eAistin em,lo;ee -eco-+s. #ns8e-: C
&. 2hich a-e DML statements3 Choose all that a,,l;" #. COMMITY B. MEREY C. U)D#TEY D. DELETEY E. CRE#TEY 4. DRO)Y #ns8e-: B/ C/ D 9. EAamine the st-uctu-e o the EM)LO*EES table: EM)LO*EE_ID 4IRST_N#ME L#ST_N#ME
NUMBER )-ima-; e; $#RC%#R&&(" $#RC%#R&&("
2hich th-ee statements inse-ts a -o8 into the table3 Choose th-ee" #. INSERT INTO em,lo;ees $#LUES NULL/ FPohnG/FSmithG"5 B. INSERT INTO em,lo;ees i-st_name/ last_name" $#LUESFPohnG/FSmithG"5 C. INSERT INTO em,lo;ees $#LUES F1'''G/FPohnG/NULL"5 D. INSERT INTO em,lo;eesi-st_name/last_name/ em,lo;ee_i+" $#LUES 1'''/ FPohnG/FSmithG"5 E. INSERT INTO em,lo;ees em,lo;ee_i+" $#LUES 1'''"5 4. INSERT INTO em,lo;ees em,lo;ee_i+/ i-st_name/ last_name" $#LUES 1'''/ FPohnG/FG"5 #ns8e-: C/ E/ 4 !. E0aluate the set o S>L statements: CRE#TE T#BLE +e,t +e,tno NUMBER&"/ +name $#RCN#R&1!"/ loc $#RCN#R&19""5 ROLLB#C5
Page 47 of 81 DESCRIBE DE)T 2hat is t-ue about the set3 #. The DESCRIBE DE)T statement +is,la;s the st-uctu-e o the DE)T table. B. The ROLLB#C statement -ees the sto-ae s,ace occu,ies b; the DE)T table. C. The DESCRIBE DE)T statement -etu-ns an e--o- OR#'!'!9: ob=ect DE)T +oes not eAist. D. The DESCRIBE DE)T statement +is,la;s the st-uctu-e o the DE)T table onl; i the-e is a COMMIT statement int-o+uce+ beo-e the ROLLB#C statement. #ns8e-: # (. EAamine the st-uctu-e i the EM)LO*EES an+ NE2 EM)LO*EES tables: EM)LO*EES EM)LO*EE_ID 4IRST_N#ME L#ST_N#ME %IRE_D#TE
NUMBER )-ima-; e; $#RC%#R&&(" $#RC%#R&&(" D#TE
NE2 EM)LO*EES EM)LO*EE_ID N#ME
NUMBER $#RC%#R&'"
2hich MERE statement is 0ali+3 #. MERE INTO ne8_em,lo;ees c USIN em,lo;ees e ON c.em,lo;ee_i+ e.em,lo;ee_i+" 2%EN M#TC%ED T%EN U)D#TE SET c.name e.i-st_name 66G/G66 e.last_name 2%EN NOT M#TC%ED T%EN INSERT $#LUESe.em,lo;ee_i+/ e.i-st_name 66G/ F66e.last_name"5 B. MERE ne8_em,lo;ees c USIN em,lo;ees e ON c.em,lo;ee_i+ e.em,lo;ee_i+" 2%EN EWIST T%EN U)D#TE SET c.name e.i-st_name 66G/G66 e.last_name 2%EN NOT M#TC%ED T%EN INSERT $#LUESe.em,lo;ee_i+/ e.i-st_name 66G/ F66e.last_name"5 C. MERE INTO ne8 em,lo;ees c USIN em,lo;ees e ON c.em,lo;ee_i+ e.em,lo;ee_i+" 2%EN EWISTS T%EN U)D#TE SET c.name e.i-st_name 66G/G66 e.last_name 2%EN NOT M#TC%ED T%EN
)-ima-; e;
Page 48 of 81 INSERT $#LUESe.em,lo;ee_i+/ e.i-st_name 66G/ F66e.last_name"5 D. MERE ne8_em,lo;ees c 4ROM em,lo;ees e ON c.em,lo;ee_i+ e.em,lo;ee_i+" 2%EN M#TC%ED T%EN U)D#TE SET c.name e.i-st_name 66G/G66 e.last_name 2%EN NOT M#TC%ED T%EN INSERT INTO ne8_em,lo;ees $#LUESe.em,lo;ee_i+/ e.i-st_name 66G.G66e.last_name"5 #ns8e-: #
. EAamine the +ata in the EM)LO*EES an+ EM)_%IST tables: EM)LO*EES
EM)LO*EE_ID 1'1 1'& 1'9 1'! 1'( 1' 1'J 11' 1&' EM)_%IST EM)LO*EE_ID 1'1 1'9 1'! 1' 1'J
POB_ID Smith Ma-tin Ch-is Pohn Diana B-;an PennieBob Ra0i
N#ME Smith Ch-is Pohn Smith Pennie-
EM)_N#ME S#L#R* &' 1' &' 9' 9' !' 9' !' &'
POB_ID S#_CLER IT_CLER %R_CLER #D_#SST %R_MR
1&' 1'( 1&' 1'J 1'J 11' 11' 11'
DE)T_ID
MR_ID
S#_RE) CLER IT_#DMIN %R_CLER %R_MR #D_#SST %R_DIR EW_DIR S#_DIR
!''' &('' !&'' &('' (''' 9''' ('' J''' (''
S#L#R* &''' &&'' &''' 9''' !(''
The EM)_%IST table is u,+ate+ at the en+ o e0e-; ;ea-. The em,lo;ee ID/ name/ =ob ID/ an+ sala-; o each eAistin em,lo;ee a-e mo+iie+ 8ith the latest +ata. Ne8 em,lo;ee +etails a-e a++e+ to the table. 2hich statement accom,lishes this tas<3 #. U)D#TE em,_hist SET em,lo;ee_i+/ name/ =ob_i+/ sala-; SELECT em,lo;ee_i+/ name/ =ob_i+/ sala-; 4ROM em,lo;ees" 2%ERE em,lo;ee_i+ IN SELECT em,lo;ee_i+ 4ROM em,lo;ees"5 B. MERE INTO em,_hist eh
Page 49 of 81 USIN em,lo;ees e ON eh.em,lo;ee_i+ e.em,lo;ee_i+" 2%EN M#TC%ED T%EN U)D#TE SET eh.name e.name/ eh.=ob_i+ e.=ob_i+/ eh.sala-; e.sala-; 2%EN NOT M#TC%ED T%EN INSERT $#LUES e.em,lo;ee i+/ e.name/ e.=ob i+/ e.sala-;"5 C. MERE INTO em,_hist eh USIN em,lo;ees e ON eh.em,lo;ee_i+ e.em,lo;ee_i+" 2%EN M#TC%ED T%EN U)D#TE em, hist SET eh.name e.name/ eh.=ob_i+ e.=ob_i+/ eh.sala-; e.sala-; 2%EN NOT M#TC%ED T%EN INSERT INTO em,_hist $#LUES e.em,lo;ee_i+/ e.name/ e.=ob_i+/ e.sala-;"5 D. MERE INTO em,_hist eh USIN em,lo;ees e 2%EN M#TC%ED T%EN U)D#TE em,_hist SET eh.name e.name/ eh.=ob_i+ e.=ob_i+/ eh.sala-; e.sala-; 2%EN NOT M#TC%ED T%EN INSERT INTO em,_hist $#LUES e.em,lo;ee_i+/ e.name/ e.=ob_i+/ e.sala-;"5 #ns8e-: B
. EAamine the +ata in the EM)LO*EES table. EM)LO*EES
EM)LO*EE_ID 1'1 1'& 1'9 1'! 1'( 1' 1'J 11' 1&'
N#ME Smith Ma-tin Ch-is Pohn Diana B-;an PennieBob Ra0i
DE)T_ID &' 1' &' 9' 9' !' 9' !' &'
MR_ID 1&' 1'( 1&' 1'J 1'J 11' 11' 11'
POB_ID S#_RE) CLER IT_#DMIN %R_CLER IT_#DMIN #D_#SST %R_DIR EW_DIR S#_DIR
S#L#R* !''' &('' !&'' &('' (''' 9''' ('' J''' (''
On the EM)LO*EES table/ EM)LO*EE_ID is the ,-ima-;
Page 50 of 81 E0aluate this DELETE statement: DELETE em,lo;ee_i+/ sala-;/ =ob_i+ 4ROM em,lo;ees 2%ERE +e,t_i+ K'5 2h; +oes the DELETE statement ail 8hen ;ou eAecute it3 #. The-e is no -o8 8ith +e,t_i+ K' in the EM)LO*EES table. B. *ou cannot +elete the POB_ID column because it is a NOT NULL column. C. *ou cannot s,eci; column names in the DELETE clause o the DELETE statement. D. *ou cannot +elete the EM)LO*EE_ID column because it is the ,-ima-;
NUMBER )-ima-; e; $#RC%#R&&(" $#RC%#R&&(" D#TE
*ou issue these statements: CRE#TE table ne8_em, em,lo;ee_i+ NUMBER/ name $#RC%#R&9'""5 INSERT INTO ne8_em, SELECT em,lo;ee_i+ / last_name -om em,lo;ees5 Sa0e,oint s15 U)D#TE ne8_em, set name U))ERname"5 Sa0e,oint s&5 Delete -om ne8_em,5 Rollbac< to s&5 Delete -om ne8_em, 8he-e em,lo;ee_i+ 1J'5 U)D#TE ne8_em, set name 7Pames75 Rollbac< to s&5 U)D#TE ne8_em, set name 7Pames7 2%ERE em,lo;ee_i+ 1J'5 Rollbac<5 #t the en+ o this t-ansaction/ 8hat is t-ue3 #. *ou ha0e no -o8s in the table. B. *ou ha0e an em,lo;ee 8ith the name o Pames. C. *ou cannot -oll bac< to the same sa0e,oint mo-e than once. D. *ou- last u,+ate ails to u,+ate an; -o8s because em,lo;ee ID 1J' 8as al-ea+; +elete+. #ns8e-: # K. EAamine the st-uctu-e o the EM)LO*EES table: EM)LO*EE_ID EM)_N#ME POB_ID S#L
NUMBER $#RC%#R&9'" $#RC%#R&&'" NUMBER
NOT NULL DE4#ULT 7S#_RE)7
Page 51 of 81 COMM_)CT NUMBER MR_ID NUMBER DE)#RTMENT_ID NUMBER *ou nee+ to u,+ate the -eco-+s o em,lo;ees 1'9 an+ 11(. The U)D#TE statement ;ou s,eci; shoul+ u,+ate the -o8s 8ith the 0alues s,eciie+ belo8: POB_ID: Deault 0alue s,eciie+ o- this column +einition. S#L: MaAimum sala-; ea-ne+ o- the =ob ID S#_RE). COMM_)CT: Deault 0alue s,eciie+ o- this commission ,e-centae column/ i an;. I no +eault 0alue is s,eciie+ o- the column/ the 0alue shoul+ be NULL. DE)#RTMENT_ID: Su,,lie+ b; the use- +u-in -un time th-ouh substitution 0a-iable. 2hich U)D#TE statement meets the -eui-ements3 #. U)D#TE em,lo;ees SET =ob_i+ DE4#ULT #ND Sal SELECT M#Wsal" 4ROM em,lo;ees 2%ERE =ob_i+ 7S#_RE)7" #ND comm_,ct DE4#ULT #ND +e,a-tment_i+ Z+i+ 2%ERE em,lo;ee_i+ IN 1'9/11("5 B. U)D#TE em,lo;ees SET =ob_i+ DE4#ULT #ND Sal M#Wsal" #ND comm_,ct DE4#ULT OR NULL #ND +e,a-tment_i+ Z+i+ 2%ERE em,lo;ee_i+ IN 1'9/11(" #ND =ob_i+ 7S#_RE)75 C. U)D#TE em,lo;ees SET =ob_i+ DE4#ULT/ Sal SELECT M#Wsal" 4ROM em,lo;ees 2%ERE =ob_i+ 7S#_RE)7"/ comm_,ct DE4#ULT/ +e,a-tment_i+ Z+i+ 2%ERE em,lo;ee_i+ IN 1'9/11("5 D. U)D#TE em,lo;ees SET =ob_i+ DE4#ULT/ Sal M#Wsal"/ comm_,ct DE4#ULT/ +e,a-tment_i+ Z+i+ 2%ERE em,lo;ee_i+ IN 1'9/11(" #ND =ob_i+ 7S#_RE)75 E. U)D#TE em,lo;ees SET =ob_i+ DE4#ULT/ Sal SELECT M#Wsal" 4ROM em,lo;ees 2%ERE =ob_i+ 7S#_RE)7" comm_,ct DE4#ULT OR NULL/
Page 52 of 81 +e,a-tment_i+ Z+i+ 2%ERE em,lo;ee_i+ IN 1'9/11("5 #ns8e-: C
1'. 2hich t8o statements com,lete a t-ansaction3 Choose t8o" #. DELETE em,lo;ees5 B. DESCRIBE em,lo;ees5 C. ROLLB#C TO S#$E)OINT C5 D. R#NT SELECT ON em,lo;ees TO SCOTT5 E. #LTER T#BLE em,lo;ees SET UNUSED COLUMN sal5 4. Select M#Wsal" 4ROM em,lo;ees 2%ERE +e,a-tment_i+ &'5 #ns8e-: D/E
11. # +ata mani,ulation lanuae statement _____. #. com,letes a t-ansaction on a table B. mo+iies the st-uctu-e an+ +ata in a table C. mo+iies the +ata but not the st-uctu-e o a table D. mo+iies the st-uctu-e but not the +ata o a table #ns8e-: C 1&. *ou o8n a table calle+ EM)LO*EES 8ith this table st-uctu-e: EM)LO*EE_ID 4IRST_N#ME L#ST_N#ME %IRE_D#TE
NUMBER )-ima-; e; $#RC%#R&&(" $#RC%#R&&(" D#TE
2hat ha,,ens 8hen ;ou eAecute this DELETE statement3 DELETE em,lo;ees5 #. *ou et an e--o- because o a ,-ima-;
NUMBER )-ima-; e; $#RC%#R&&(" $#RC%#R&&(" D#TE
Page 53 of 81 NE2_EM)LO*EES EM)LO*EE_ID N#ME
NUMBER $#RC%#R&'"
)-ima-; e;
2hich MERE statement is 0ali+3 #. MERE INTO ne8_em,lo;ees c USIN em,lo;ees e ON c.em,lo;ee_i+ e.em,lo;ee_i+" 2%EN M#TC%ED T%EN U)D#TE SET c.name e.i-st_name 667/766 e.last_name 2%EN NOT M#TC%ED T%EN INSERT $#LUES e.em,lo;ee_i+/ e.i-st_name 667/ 766e.last_name"5 B. MERE ne8_em,lo;ees c USIN em,lo;ees e ON c.em,lo;ee_i+ e.em,lo;ee_i+" 2%EN EWISTS T%EN U)D#TE SET c.name e.i-st_name 667/766 e.last_name 2%EN NOT M#TC%ED T%EN INSERT $#LUES e.em,lo;ee_i+/ e.i-st_name 667/ 766e.last_name"5 C. MERE INTO ne8_em,lo;ees c USIN em,lo;ees e ON c.em,lo;ee_i+ e.em,lo;ee_i+" 2%EN EWISTS T%EN U)D#TE SET c.name e.i-st_name 667/766 e.last_name 2%EN NOT M#TC%ED T%EN INSERT $#LUESe.em,lo;ee_i+/ e.i-st_name 667/ 766e.last_name"5 D. MERE ne8_em,lo;ees c 4ROM em,lo;ees e ON c.em,lo;ee_i+ e.em,lo;ee_i+" 2%EN M#TC%ED T%EN U)D#TE SET c.name e.i-st_name 667/766 e.last_name 2%EN NOT M#TC%ED T%EN INSERT INTO ne8_em,lo;ees $#LUES e.em,lo;ee_i+/ e.i-st_name 667/ 766e.last_name"5 #ns8e-: # 1!. 2hich th-ee a-e t-ue3 Choose th-ee." #. # MERE statement is use+ to me-e the +ata o one table 8ith +ata -om anothe-. B. # MERE statement -e,laces the +ata o one table 8ith that o anothe-. C. # MERE statement can be use+ to inse-t ne8 -o8s into a table. D. # MERE statement can be use+ to u,+ate eAistin -o8s in a table. #ns8e-: #/ C/ D
Page 54 of 81 1(. EAamine the st-uctu-e o the EM)LO*EES table: EM)LO*EE_ID 4IRST_N#ME L#ST_N#ME %IRE_D#TE
NUMBER )-ima-; e; $#RC%#R&&(" $#RC%#R&&(" D#TE
2hich INSERT statement is 0ali+3 #. INSERT INTO em,lo;ees em,lo;ee_i+/ i-st_name/ last_name/ hi-e_+ate" $#LUES 1'''/ FPohnG/ FSmithG/ F'1V'1V'1G"5 B. INSERT INTO em,lo;eesem,lo;ee_i+/ i-st_name/ last_name/ hi-e_+ate" $#LUES 1'''/ FPohnG/ FSmithG/ G'1 Panua-; '1G"5 C. INSERT INTO em,lo;eesem,lo;ee_i+/ i-st_name/ last_name/ %i-e_+ate" $#LUES 1'''/ FPohnG/ FSmithG/ To_+ateF'1V'1V'1G""5 D. INSERT INTO em,lo;eesem,lo;ee_i+/ i-st_name/ last_name/ hi-e_+ate" $#LUES 1'''/ FPohnG/ FSmithG/ '1Pan'1"5 #ns8e-: B
1. EAamine the st-uctu-e o the EM)LO*EES table: EM)LO*EE_ID 4IRST_N#ME L#ST_N#ME %IRE_D#TE
NUMBER )-ima-; e; $#RC%#R& &(" $#RC%#R& &(" D#TE
2hich U)D#TE statement is 0ali+3 #. U)D#TE em,lo;ees SET i-st_name FPohnG SET last_name FSmithG 2%ERE em,lo;ee_i+ 1J'5 B. U)D#TE em,lo;ees SET i-st_name FPohnG/ SET last_name FSmothG 2%ERE em,lo;ee_i+ 1J'5 C. U)D#TE em,lo;ee SET i-st_name FPohnG #ND last_name FSmithG 2%ERE em,lo;ee_i+ 1J'5 D. U)D#TE em,lo;ee SET i-st_name FPohnG/ last_name FSmithG 2%ERE em,lo;ee_i+ 1J'5 #ns8e-: D
Page 55 of 81 Lesson J: 2o-
Page 56 of 81 D. CRE#TE T#BLE o-+e-s o-+e-_i+ NUMBER 1'"/ custome-_i+ NUMBER J" o-+e-_status NUMBER 1'"/ +ate_o-+e-e+ D#TE DE4#ULT S*SD#TE"5 #ns8e-: B
9. 2hich is a 0ali+ CRE#TE T#BLE statement3 #. CRE#TE T#BLE EM)KX em,i+ numbe-&""5 B. CRE#TE T#BLE EM)?1&9 em,i+ numbe-&""5 C. CRE#TE T#BLE )#C#E ,ac
!. 2hich th-ee a-e D#TETIME +ata t;,es that can be use+ 8hen s,eci;in column +einitions3 Choose th-ee." #. TIMEST#M) B. INTER$#L MONT% TO D#* C. INTER$#L D#* TO SECOND D. INTER$#L *E#R TO MONT% E. TIMEST#M) 2IT% D#T#B#SE TIME[ONE #ns8e-: #/ C/ D (. 2hich ou- a-e co--ect ui+elines o- namin +atabase tables3 Choose ou-" #. Must bein 8ith eithe- a numbe- o- a lette-. B. Must be 19' cha-acte-s lon. C. Shoul+ not be an O-acle Se-0e- -ese-0e+ 8o-+. D. Must contain onl; #[/ a@/ '/ _/ ?/ an+ X. E. Must contain onl; #[/ a@/ 'K/ _/ / an+ X. 4. Must bein 8ith a lette-. #ns8e-: B/ C/ E/ 4
. 2hat +oes the TRUNC#TE statement +o3 #. Remo0es the table B. Remo0es all -o8s -om a table C. Sho-tens the table to 1' -o8s D. Remo0es all columns -om a table E. Remo0es o-ein
Page 57 of 81 . *ou nee+ to chane the +einition o an eAistin table. The COMMERCI#LS table nee+s its DESCRI)TION column chane+ to hol+ 0a-;in lenth cha-acte-s u, to &''' b;tes. The column can cu--entl; hol+ 1''' b;tes ,e- 0alue. The table contains &'''' -o8s. 2hich statement is 0ali+3 #. #LTER T#BLE comme-cials MODI4* +esc-i,tion C%#R&&'''""5 B. #LTER T#BLE comme-cials C%#NE +esc-i,tion C%#R&&'''""5 C. #LTER T#BLE comme-cials C%#NE +esc-i,tion $#RC%#R&&'''""5 D. #LTER T#BLE comme-cials MODI4* +esc-i,tion $#RC%#R&&'''""5 E. *ou cannot inc-ease the si@e o a column i the table has -o8s. #ns8e-: D
J. The EM)LO*EES table has these columns: L#ST N#ME S#L#R* %IRE_D#TE
$#RC%#R&9(" NUMBERJ/&" D#TE
Manaement 8ants to a++ a +eault 0alue to the S#L#R* column. *ou ,lan to alte- the table b; usin this S>L statement: #LTER T#BLE EM)LO*EES MODI4* S#L#R* DE4#ULT ('''"5 2hat is t-ue about ;ou- #LTER statement3 #. Column +einitions cannot be alte-e+ to a++ DE4#ULT 0alues. B. # chane to the DE4#ULT 0alue aects onl; subseuent inse-tions to the table. C. Column +einitions cannot be alte-e+ at a++ DE4#ULT 0alues o- columns 8ith a NUMBER +ata t;,e. D. #ll the -o8s that ha0e a NULL 0alue o- the S#L#R* column 8ill be u,+ate+ 8ith the 0alue ('''. #ns8e-: B K. 2hich statement +esc-ibes the RO2ID +ata t;,e3 #. Bina-; +ata u, to ! iab;tes. B. Cha-acte- +ata u, to ! iab;tes. C. Ra8 bina-; +ata o 0a-iable lenth u, to & iab;tes. D. Bina-; +ata sto-e+ in an eAte-nal ile/ u, to ! iab;tes. E. # heAa+ecimal st-in -e,-esentin the uniue a++-ess o a -o8 in its table. #ns8e-: E
1'. E0aluate the S>L statement DRO) T#BLE DE)T:
Page 58 of 81 2hich ou- statements a-e t-ue o the S>L statement3 Choose ou-" #. *ou cannot -oll bac< this statement. B. #ll ,en+in t-ansactions a-e committe+. C. #ll 0ie8s base+ on the DE)T table a-e +elete+. D. #ll in+eAes base+ on the DE)T table a-e +-o,,e+. E. #ll +ata in the table is +elete+/ an+ the table st-uctu-e is also +elete+. 4. #ll +ata in the table is +elete+/ but the st-uctu-e o the table is -etaine+. . #ll s;non;ms base+ on the DE)T table a-e +elete+. #ns8e-: #/ B/ D/ E 11. E0aluate the S>L statement: TRUNC#TE T#BLE DE)T5 2hich th-ee a-e t-ue about the S>L statement3 Choose th-ee." #. It -eleases the sto-ae s,ace use+ b; the table. B. It +oes not -elease the sto-ae s,ace use+ b; the table. C. *ou can -oll bac< the +eletion o -o8s ate- the statement eAecutes. D. *ou can NOT -oll bac< the +eletion o -o8s ate- the statement eAecutes. E. #n attem,t to use DESCRIBE on the DE)T table ate- the TRUNC#TE statement eAecutes 8ill +is,la; an e--o-. 4. *ou must be the o8ne- o the table o- ha0e DELETE #N* T#BLE s;stem ,-i0ilees to t-uncate the DE)T table #ns8e-: #/ D/ 4
Page 59 of 81 Lesson K: Const-aints 1. 2hich ou- a-e 0ali+ O-acle const-aint t;,es3 Choose ou-." #. C#SC#DE B. UNI>UE C. NONUNI>UE D. C%EC E. )RIM#R* E* 4. CONST#NT . NOT NULL #ns8e-: B/ D/ E/ &. 2hich statement a++s a const-aint that ensu-es the CUSTOMER_N#ME column o the CUSTOMERS table hol+s a 0alue3 #. #LTER T#BLE custome-s #DD CONSTR#INT cust_name_nn C%EC custome-_name IS NOT NULL5 B. #LTER T#BLE custome-s MODI4* CONSTR#INT cust_name_nn C%EC custome-_name IS NOT NULL5 C. #LTER T#BLE custome-s MODI4* custome-_name CONSTR#INT cust_name_nn NOT NULL5 D. #LTER T#BLE custome-s MODI4* custome-_name CONSTR#INT cust_name_nn IS NOT NULL5 E. #LTER T#BLE custome-s MODI4* name CONSTR#INT cust_name_n n NOT NULL5 4. #LTER T#BLE custome-s #DD CONSTR#INT cust_name_nn C%EC custome-_name NOT NULL5 #ns8e-: C
9. *ou nee+ to mo+i; the STUDENTS table to a++ a ,-ima-;
NUMBER )-ima-; e; $#RC%#R&&(" $#RC%#R&&("
2hich th-ee statements inse-t a -o8 into the table3 Choose th-ee."
Page 60 of 81 #. INSERT INTO em,lo;ees $#LUES NULL/ 7Pohn7/ 7Smith7"5 B. INSERT INTO em,lo;ees i-st_name/ last_name" $#LUES 7Pohn7/ 7Smith7"5 C. INSERT INTO em,lo;ees $#LUES 71'''7/ 7Pohn7/ NULL"5 D. INSERT INTO em,lo;ees i-st_name/ last_name/ em,lo;ee_i+" $#LUES 1'''/ 7Pohn7/ 7Smith7"5 E. INSERT INTO em,lo;ees em,lo;ee_i+" $#LUES 1'''"5 4. INSERT INTO em,lo;ees em,lo;ee_i+/ i-st_name/ last_name" $#LUES 1'''/ 7Pohn7/ 77"5 #ns8e-: C/ E/ 4
(. EAamine the S>L statement that c-eates ORDERS table: CRE#TE T#BLE o-+e-s SER_NO NUMBER UNI>UE/ ORDER_ID NUMBER/ ORDER_D#TE D#TE NOT NULL/ ST#TUS $#RC%#R&1'" C%EC status IN 7CREDIT7/ 7C#S%7""/ )ROD_ID NUMBER RE4ERENCES )RODUCTS)RODUCT_ID"/ ORD_TOT#L NUMBER/ )RIM#R* E* o-+e-_i+/ o-+e-_+ate""5 4o- 8hich columns 8oul+ an in+eA be automaticall; c-eate+ 8hen ;ou eAecute the abo0e S>L statement3 Choose t8o."
#. SER_NO B. ORDER_ID C. ST#TUS D. )ROD_ID E. ORD_TOT#L 4. com,osite in+eA on ORDER_ID an+ ORDER_D#TE #ns8e-: #/ 4 . 2hich const-aint can be +eine+ onl; at the column le0el3 #. UNI>UE B. NOT NULL C. C%EC D. )RIM#R* E* E. 4OREIN E* #ns8e-: B . 2hich 0ie8 shoul+ a use- ue-; to +is,la; the columns associate+ 8ith the const-aints on a table o8ne+ b; the use-3 #. USER_CONSTR#INTS B. USER_OBPECTS C. #LL_CONSTR#INTS D. USER_CONS_COLUMNS E. USER_COLUMNS #ns8e- : D
Page 61 of 81
J. *ou nee+ to +esin a stu+ent -eist-ation +atabase that contains se0e-al tables sto-in aca+emic ino-mation. The STUDENTS table sto-es ino-mation about a stu+ent. The STUDENT_R#DES table sto-es ino-mation about the stu+ent7s -a+es. Both o the tables ha0e a column name+ STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a ,-ima-; UE const-aint +oes not ,e-mit a null 0alue o- the column. B. # UNI>UE in+eA ets c-eate+ o- columns 8ith )RIM#R* E* an+ UNI>UE const-aints. C. The )RIM#R* E* an+ 4OREIN E* const-aints c-eate a UNI>UE in+eA. D. The NOT NULL const-aint ensu-es that null 0alues a-e not ,e-mitte+ o- the column. #ns8e-: B/ D 1'. 4o- 8hich t8o const-aints +oes the O-acle Se-0e- im,licitl; c-eate a uniue in+eA3 Choose t8o." #. NOT NULL B. )RIM#R* E* C. 4OREIN E* D. C%EC E. UNI>UE #ns8e-: B/ E
11. 2hich s;ntaA tu-ns an eAistin const-aint on3 #. #LTER T#BLE table_name EN#BLE const-aint_name5
Page 62 of 81 B. #LTER T#BLE table_name ST#TUS EN#BLE CONSTR#INT const-aint_name5 C. #LTER T#BLE table_name EN#BLE CONSTR#INT const-aint_name5 D. #LTER T#BLE table_name ST#TUS EN#BLE CONSTR#INT const-aint_name5 E. #LTER T#BLE table_name TURN ON CONSTR#INT const-aint_name5 4. #LTER T#BLE table_name TURN ON CONSTR#INT const-aint_name5 #ns8e-: C 1&. *ou nee+ to mo+i; the STUDENTS table to a++ a ,-ima-;
EM)_N#ME Smith Ma-tin Ch-is Pohn Diana Smith PennieBob Ra0i
DE)T_ID &' 1' &' 9' 9' !' 9' !' &'
MR_ID 1&' 1'( 1&' 1'J 1'J 11' 11' 11'
POB_ID S#_RE) CLER IT_#DMIN %R_CLER IT_#DMIN #D_#SST %R_DIR EW_DIR S#_DIR
S#L#R* !''' &('' !&'' &('' (''' 9''' ('' J''' (''
DE)#RTMENT_N#ME #+min E+ucation IT %uman Resou-ces
#lso eAamine the S>L statements that c-eate the EM)LO*EES an+ DE)#RTMENTS tables: CRE#TE T#BLE +e,a-tments +e,a-tment_i+ NUMBER )RIM#R* E*/ +e,a-tment_name $#RC%#R&9'""5 CRE#TE T#BLE em,lo;ees EM)LO*EE_ID NUMBER )RIM#R* E*/ EM)_N#ME $#RC%#R&&'"/ DE)T_ID NUMBER RE4ERENCES +e,a-tments+e,a-tment_i+"/ MR_ID NUMBER RE4ERENCES em,lo;eesem,lo;ee i+"/ MR_ID NUMBER RE4ERENCES em,lo;eesem,lo;ee i+"/ POB_ID $#RC%#R&1(". S#L#R* NUMBER"5 On the EM)LO*EES table/ EM)LO*EE_ID is the ,-ima-;
Page 63 of 81 MR_ID is the ID o manae-s an+ -ee-s to the EM)LO*EE_ID. DE)T_ID is o-ein
1!. 2hich S>L statement +eines the 4OREIN E* const-aint on the DE)TNO column o the EM) table3 #. CRE#TE T#BLE EM) em,no NUMBER!"/ ename $#RCN#R&9("/ +e,tno NUMBER/&" NOT NULL CONSTR#INT em,_+e,tno_< 4OREIN E* +e,tno RE4ERENCES +e,t +e,tno"5 B. CRE#TE T#BLE EM) em,no NUMBER!"/ ename $#RCN#R&9("/ +e,tno NUMBER/&" CONSTR#INT em,_+e,tno_< RE4ERENCES +e,t +e,tno""5 C. CRE#TE T#BLE EM) em,no NUMBER!" ename $#RC%#R&9("/ +e,tno NUMBER/&" NOT NULL/ CONSTR#INT em,_+e,tno_< RE4ERENCES +e,t +e,tno" 4OREIN E* +e,tno""5 D. CRE#TE T#BLE EM) em,no NUMBER!"/ ename $#RCN#R&9("/ +e,tno NUMBER/&" 4OREIN E* CONSTR#INT em, +e,tno < RE4ERENCES +e,t +e,tno""5 #ns8e-: B
1(. 2hich statement eA,licitl; names a const-aint3 #. #LTER T#BLE stu+ent_-a+es #DD 4OREIN E* stu+ent_i+" RE4ERENCES stu+entsstu+ent_i+"5
Page 64 of 81 B. #LTER T#BLE stu+ent_-a+es #DD CONSTR#INT N#ME stu+ent_i+_< 4OREIN E* stu+ent_i+" RE4ERENCES stu+entsstu+ent_i+"5 C. #LTER T#BLE stu+ent_-a+es #DD CONSTR#INT stu+ent_i+_< 4OREIN E* stu+ent_i+" RE4ERENCES stu+entsstu+ent_i+"5 D. #LTER T#BLE stu+ent -a+es #DD N#MED CONSTR#INT stu+ent_i+_< 4OREIN E* stu+ent_i+" RE4ERENCES stu+entsstu+ent_i+"5 E. #LTER T#BLE stu+ent -a+es #DD N#ME stu+ent_i+_< 4OREIN E* stu+ent_i+" RE4ERENCES stu+entsstu+ent_i+"5 #ns8e-: C 1. 2hich th-ee statements co--ectl; +esc-ibe the unctions an+ use o const-aints3 Choose th-ee." #. Const-aints ,-o0i+e +ata in+e,en+ence. B. Const-aints ma
1. 2hich const-aint can be +eines onl; at the column le0el3 #. UNI>UE B. NOT NULL C. C%EC D. )RIM#R* E* E. 4OREIN E* #ns8e-: B 1J. 2hich t8o statements about c-eatin const-aints a-e t-ue3 Choose t8o" #. Const-aint names must sta-t 8ith S*S_C. B. #ll const-aints must be +eines at the column le0el. C. Const-aints can be c-eate+ ate- the table is c-eate+. D. Const-aints can be c-eate+ at the same time the table is c-eate+. E. Ino-mation about const-aints is oun+ in the $IE2_CONSTR#INTS +ictiona-; 0ie8. #ns8e-: C/ D Lesson 1': $ie8s 1. To, N anal;sis -eui-es _____ an+ _____. Choose t8o." #. the use o -o8i+ B. a ROU) B* clause
Page 65 of 81 C. an ORDER B* clause D. onl; an inline 0ie8 E. an inline 0ie8 an+ an oute- ue-; #ns8e-: C/ E
&. 2hich ob=ect ,-i0ilees can be -ante+ on a 0ie83 #. none B. DELETE/ INSERT/SELECT C. #LTER/ DELETE/ INSERT/ SELECT D. DELETE/ INSERT/ SELECT/ U)D#TE #ns8e-: D 9. 2hat is t-ue about u,+ates th-ouh a 0ie83 #. *ou cannot u,+ate a 0ie8 8ith -ou, unctions. B. 2hen ;ou u,+ate a 0ie8 -ou, unctions a-e automaticall; com,ute+. C. 2hen ;ou u,+ate a 0ie8 onl; the const-aints on the un+e-l;in table 8ill be in eect. D. 2hen ;ou u,+ate a 0ie8 the const-aints on the 0ie8s al8a;s o0e--i+e the const-aints on the un+e-l;in tables. #ns8e-: # !. 2hat +oes the 4ORCE o,tion o- c-eatin a 0ie8 +o3 #. c-eates a 0ie8 8ith const-aints B. c-eates a 0ie8 e0en i the un+e-l;in ,a-ent table has const-aints C. c-eates a 0ie8 in anothe- schema e0en i ;ou +on7t ha0e ,-i0ilees D. c-eates a 0ie8 -ea-+less o 8hethe- o- not the base tables eAist #ns8e-: D
(. 2hich best +esc-ibes an inline 0ie83 #. a schema ob=ect B. a subue-; that can contain an ORDER B* clause C. anothe- name o- a 0ie8 that contains -ou, unctions D. a subue-; that is ,a-t o the 4ROM clause o anothe- ue-; #ns8e-: D . 2hich t8o statements about 0ie8s a-e t-ue3 Choose t8o." #. # 0ie8 can be c-eate+ as -ea+ onl;. B. # 0ie8 can be c-eate+ as a =oin on t8o o- mo-e tables. C. # 0ie8 cannot ha0e an ORDER B* clause in the SELECT statement. D. # 0ie8 cannot be c-eate+ 8ith a ROU) B* clause in the SELECT statement. E. # 0ie8 must ha0e aliases +eine+ o- the column names in the SELECT statement.
Page 66 of 81 #ns8e-: #/ B
. *ou c-eate+ a 0ie8 calle+ EM)_DE)T_$U that contains th-ee columns -om the EM)LO*EES an+ DE)#RTMENTS tables: EM)LO*EE_ID/ EM)LO*EE_N#ME #ND DE)#RTMENT_N#ME. The DE)#RTMENT_ID column o the EM)LO*EES table is the o-ein
J. EAamine the st-uctu-e i the EM)LO*EES table: Column name EM)LO*EE_ID EM)_N#ME POB_ID S#L MR_ID DE)#RTMENT_ID
Data T;,e NUMBER $#RC%#R&9'" $#RC%#R&&'" NUMBER NUMBER NUMBER
Rema-
*ou nee+ to c-eate a 0ie8 calle+ EM)_$U that allo8s the use- to inse-t -o8s th-ouh the 0ie8. 2hich S>L statement/ 8hen use+ to c-eate the EM)_$U 0ie8/ allo8s the useto inse-t -o8s3 #. CRE#TE $IE2 em,_$u #S
Page 67 of 81 SELECT em,lo;ee_i+/ em,_name/ +e,a-tment_i+ 4ROM em,lo;ees 2%ERE m-_i+ IN 1'&/ 1&'"5 B. CRE#TE $IE2 em,_$u #S SELECT em,lo;ee_i+/ em,_name/ =ob_i+ +e,a-tment_i+ 4ROM em,lo;ees 2%ERE m-_i+ IN 1'&/ 1&'"5 C. CRE#TE $IE2 em,_$u #S SELECT +e,a-tment_i+/ SUMsal" TOT#LS#L 4ROM em,lo;ees 2%ERE m-_i+ IN 1'&/ 1&'" ROU) B* +e,a-tment_i+5 D. CRE#TE $IE2 em,_$u #S SELECT em,lo;ee_i+/ em,_name/ =ob_i+/ DISTINCT +e,a-tment_i+ 4ROM em,lo;ees5 #ns8e-: B
Page 68 of 81 K. *ou nee+ to c-eate a 0ie8 EM)_$U. The 0ie8 shoul+ allo8 the use-s to mani,ulate the -eco-+s o onl; the em,lo;ees that a-e 8o-L statement 8oul+ ;ou use to c-eate the 0ie8 EM)_$U3 #. CRE#TE $IE2 em,_0u #S SELECT ? 4ROM em,lo;ees 2%ERE +e,a-tment_i+ IN 1'/&'"5 B. CRE#TE $IE2 em,_0u #S SELECT ? 4ROM em,lo;ees 2%ERE +e,a-tment_i+ IN 1'/&'" 2IT% RE#D ONL*5 C. CRE#TE $IE2 em,_0u #S SELECT ? 4ROM em,lo;ees 2%ERE +e,a-tment_i+ IN 1'/&'" 2IT% C%EC O)TION5 D. CRE#TE 4ORCE $IE2 em,_0u #S SELECT ? 4ROM em,lo;ees 2%ERE +e,a-tment_i+ IN 1'/&'"5 E. CRE#TE 4ORCE $IE2 em,_0u #S SELECT ? 4ROM em,lo;ees 2%ERE +e,a-tment_i+ IN 1'/&'" NO U)D#TE5 #ns8e-: C 1'. Ma-; has a 0ie8 calle+ EM)_DE)T_LOC_$U that 8as c-eate+ base+ on the EM)LO*EES/ DE)#RTMENTS/ an+ LOC#TIONS tables. She -ante+ SELECT ,-i0ilee to Scott on this 0ie8. 2hich o,tion enables Scott to eliminate the nee+ to uali; the 0ie8 8ith the name M#R* .EM)_DE)_LOC_$U each time the 0ie8 is -ee-ence+3 #. Scott can c-eate a s;non;m o- the EM)_DE)T_LOC_$U bus usin the comman+: CRE#TE )RI$#TE S*NON*M EDL_$U 4OR ma-;.EM) DE)T_LOC_$U5 then he can ,-eiA the columns 8ith this s;non;mn. B. Scott can c-eate a s;non;m o- the EM)_DE)T_LOC_$U b; usin the comman+: CRE#TE S*NON*M EDL_$U 4OR ma-;.EM)_DE)T_LOC_$U5 then he can ,-eiA the columns 8ith this s;non;m. C. Scott can c-eate a s;non;m o- the EM)_DE)T_LOC_$U b; usin the comman+: CRE#TE LOC#L S*NON*M EDL_$U 4OR ma-;.EM) DE)T_LOC_$U5 then he can ,-eiA the columns 8ith this s;non;m. D. Scott can c-eate a s;non;m o- the EM)_DE)T_LOC_$U b; usin the comman+:
Page 69 of 81 CRE#TE S*NON*M EDL_$U ON ma-;EM)_DE)T_LOC_$U"5 then he can ,-eiA the columns 8ith this s;non;m. E. Scott cannot c-eate a s;non;m because s;non;ms can be c-eate+ onl; o- tables. 4. Scott cannot c-eate an; s;non;m o- Ma-;Gs 0ie8. Ma-; shoul+ c-eate a ,-i0ate s;non;m o- the 0ie8 an+ -ant SELECT ,-i0ilee on that s;non;m to Scott. #ns8e-: B
11. *ou nee+ to ,e-o-m ce-tain +ata mani,ulation o,e-ations th-ouh a 0ie8 calle+ EM)_DE)T_$U/ 8hich ;ou ,-e0iousl; c-eate+. *ou 8ant to loo< at the +einition o the 0ie8 the SELECT statement on 8hich the 0ie8 8as c-eate." %o8 +o ;ou obtain the +einition o the 0ie83 #. Use the DESCRIBE comman+ in the EM)_DE)T $U 0ie8. B. Use the DE4INE $IE2 comman+ on the EM)_DE)T $U 0ie8. C. Use the DESCRIBE $IE2 comman+ on the EM)_DE)T $U 0ie8. D. >ue-; the USER_$IE2S +ata +ictiona-; 0ie8 to sea-ch o- the EM)_DE)T_$U 0ie8. E. >ue-; the USER_SOURCE +ata +ictiona-; 0ie8 to sea-ch o- the EM)_DE)T_$U 0ie8. 4. >ue-; the USER_OBPECTS +ata +ictiona-; 0ie8 to sea-ch o- the EM)_DE)T_$U 0ie8. #ns8e-: D
1&. *ou a-e -ante+ the CRE#TE $IE2 ,-i0ilee. 2hat +oes this allo8 ;ou to +o3 #. C-eate a table 0ie8. B. C-eate a 0ie8 in an; schema. C. C-eate a 0ie8 in ;ou- schema. D. C-eate a seuence 0ie8 in an; schema. E. C-eate a 0ie8 that is accessible b; e0e-;one. 4. C-eate a 0ie8 onl; o it is base+ on tables that ;ou c-eate+. #ns8e-: C
19. EAamine the st-uctu-e o the EM)_DE)T_$U 0ie8: Column Name EM)LO*EE_ID EM)_N#ME POB_ID S#L#R* DE)#RTMENT_ID DE)T_N#ME
T;,e Rema-
Page 70 of 81 2hich S>L statement ,-o+uces an e--o-3 #. SELECT ? 4ROM em,_+e,t_0u5 B. SELECT +e,a-tment_i+/ SUMsala-;" 4ROM em,_+e,t_0u ROU) B* +e,a-tment_i+5 C. SELECT +e,a-tment_i+/ =ob_i+/ #$sala-;" 4ROM em,_+e,t_0u ROU) B* +e,a-tment_i+/ =ob_i+5 D. SELECT =ob_i+/ SUMsala-;" 4ROM em,_+e,t_0u 2%ERE +e,a-tment_i+ IN 1'/&'" ROU) B* =ob_i+ %#$IN SUMsala-;" Q &''''5 E. None o the statements ,-o+uce an e--o-5 all a-e 0ali+. #ns8e-: E 1!. 2hich S>L statement 8oul+ ;ou use to -emo0e a 0ie8 calle+ EM)_DE)T_$U -om ;ou- schema3 #. DRO) em,_+e,t_0u5 B. DELETE em,_+e,t_0u5 C. REMO$E em,_+e,t_0u5 D. DRO) $IE2 em,_+e,t_0u5 E. DELETE $IE2 em,_+e,t_0u5 4. REMO$E $IE2 em,_+e,t_0u5 #ns8e-: D Lesson 11: Othe- Ob=ects 1. 2hich t8o statements about seuences a-e t-ue3 Choose t8o" #. *ou use a NEWT$#L ,seu+o column to loo< at the neAt ,ossible 0alue that 8oul+ be ene-ate+ -om a seuence/ 8ithout actuall; -et-ie0in the 0alue. B. *ou use a CURR$#L ,seu+o column to loo< at the cu--ent 0alue =ust ene-ate+ -om a seuence/ 8ithout aectin the u-the- 0alues to be ene-ate+ -om the seuence. C. *ou use a NEWT$#L ,seu+o column to obtain the neAt ,ossible 0alue -om a seuence b; actuall; -et-ie0in the 0alue -om the seuence. D. *ou use a CURR$#L ,seu+o column to ene-ate a 0alue -om a seuence that 8oul+ be use+ o- a s,eciie+ +atabase column. E. I a seuence sta-tin -om a 0alue 1'' an+ inc-emente+ b; 1 is use+ b; mo-e then one a,,lication/ then all o these a,,lications coul+ ha0e a 0alue o 1'( assine+ to theicolumn 8hose 0alue is bein ene-ate+ b; the seuence. 4. *ou use REUSE clause 8hen c-eatin a seuence to -esta-t the seuence once it ene-ates the maAimum 0alue +eine+ o- the seuence. #ns8e-: B/ C &. The +atabase a+minist-ato- o ;ou- com,an; c-eate+ a ,ublic s;non;m calle+ %R othe %UM#N_RESOURCES table o the ENER#L schema/ because man; use-s -euentl; use this table. #s a use- o the +atabase/ ;ou c-eate+ a table calle+ %R in ;ou- schema. 2hat ha,,ens 8hen ;ou eAecute this ue-;3
Page 71 of 81 SELECT ? 4ROM %R5 #. *ou obtain the -esults -et-ie0e+ -om the ,ublic s;non;m %R c-eate+ b; the +atabase a+minist-ato-. B. *ou obtain the -esults -et-ie0e+ -om the %R table that belons to ;ou- schema. C. *ou et an e--o- messae because ;ou cannot -et-ie0e -om a table that has the same name as a ,ublic s;non;m. D. *ou obtain the -esults -et-ie0e+ -om both the ,ublic s;non;m %R an+ the %R table that belons to ;ou- schema/ as a Ca-tesian ,-o+uct. E. *ou obtain the -esults -et-ie0e+ -om both the ,ublic s;non;m %R an+ the %R table that belons to ;ou- schema/ as a 4ULL POIN. #ns8e-: B 9. In 8hich scena-io 8oul+ in+eA be most useul3 #. The in+eAe+ column is +ecla-e+ as NOT NULL. B. The in+eAe+ columns a-e use+ in the 4ROM clause. C. The in+eAe+ columns a-e ,a-t o an eA,-ession. D. The in+eAe+ column contains a 8i+e -ane o 0alues. #ns8e-: D !. EAamine the st-uctu-e o the EM)LO*EES table: Column name EM)LO*EE_ID L#ST_N#ME 4IRST_N#ME POB_ID S#L MR_ID DE)#RTMENT_ID
Data t;,e NUMBER $#RCN#R&9'" $#RCN#R&9'" NUMBER NUMBER NUMBER NUMBER
Rema-
Ree-ences EM)LO*EE_ID column
*ou nee+ to c-eate an in+eA calle+ N#ME_IDW on the i-st name an+ last name iel+s o the EM)LO*EES table. 2hich S>L statement 8oul+ ;ou use to ,e-o-m this tas<3 #. CRE#TE INDEW N#ME_IDW i-st_name/ last_name"5 B. CRE#TE INDEW N#ME_IDW i-st_name #ND last_name"5 C. CRE#TE INDEW N#ME_IDW ON i-st_name/ last_name"5 D. CRE#TE INDEW N#ME_IDW ON em,lo;ees i-st_name #ND last_name"5 E. CRE#TE INDEW N#ME_IDW ON em,lo;eesi-st_name/ last_name"5 4. CRE#TE INDEW N#ME_IDW 4OR em,lo;eesi-st_name/ last_name"5 #ns8e-: E (. 2hat a-e t8o -easons to c-eate s;non;ms3 Choose t8o." #. *ou ha0e too man; tables. B. *ou- tables a-e too lon. C. *ou- tables ha0e +iicult names. D. *ou 8ant to 8o-< on ;ou- o8n tables. E. *ou 8ant to use anothe- schema7s tables. 4. *ou ha0e too man; columns in ;ou- tables.
Page 72 of 81 #ns8e-: B/ E . EAamine the st-uctu-e o the EM)LO*EES table: EM)LO*EE_ID EM)_N#ME POB_ID S#L MR_ID DE)#RTMENT_ID
NUMBER $#RC%#R&9'" NUMBER NUMBER NUMBER NUMBER
NOT NULL/ )-ima-; e;
Ree-ences EM)LO*EE_ID column 4o-ein
*ou c-eate+ a seuence calle+ EM)_ID_SE> in o-+e- to ,o,ulate seuential 0alues othe EM)LO*EE_ID column o the EM)LO*EES table. 2hich t8o statements -ea-+in the EM)_ID_SE> seuence a-e t-ue3 Choose t8o." #. *ou cannot use the EM)_ID_SE> seuence to ,o,ulate the POB_ID column. B. The EM)_ID_SE> seuence is in0ali+ate+ 8hen ;ou mo+i; the EM)LO*EE_ID column. C. The EM)_ID_SE> seuence is not aecte+ b; mo+iications to the EM)LO*EES table. D. #n; othe- column o NUMBER +ata t;,e in ;ou- schema can use the EM)_ID_SE> seuence. E. The EM)_ID_SE> seuence is +-o,,e+ automaticall; au tomaticall; 8hen ;ou +-o, the EM)LO*EES table. 4. The EM)_ID_SE> seuence is +-o,,e+ automaticall; 8hen ;ou +-o, the EM)LO*EE_ID column. #ns8e-: C/ D . Use- Ma-; has a 0ie8 calle+ EM)_DE)T_LOC_$U that 8as c-eate+ base+ on the EM)LO*EES/ DE)#RTMENTS/ an+ LOC#TIONS tables. She has the ,-i0ilee to c-eate a ,ublic s;non;m/ an+ 8oul+ liL statement can Ma-; use to accom,lish that tas<3 #. CRE#TE )UBLIC S*NON*M EDL_$U ON em,_+e,t_loc_0u5 B. CRE#TE )UBLIC S*NON*M EDL:$U 4OR ma-; em,_+e,t_loc_0u"5 C. CRE#TE )UBLIC S*NON*M EDL_$U 4OR em,_+e,t_loc_0u5 D. CRE#TE S*NON*M EDL_$U ON em,_+e,t_loc_0u 4OR E#C% USER5 E. CRE#TE S*NON*M EDL_$U 4OR E#C% USER ON em,_+e,t_loc_0u5 4. CRE#TE )UBLIC S*NON*M EDL_$U ON em,_+e,t_loc_0u 4OR #LL USERS5 #ns8e-: # J. EAamine the statement: Create synonym emp for hr.employees;
2hat ha,,ens 8hen ;ou issue the statement3 #. #n e--o- is ene-ate+. B. *ou 8ill ha0e t8o i+entical tables in the %R schema 8ith +ie-ent names.
Page 73 of 81 C. *ou c-eate a table calle+ em,lo;ees in the %R schema base+ on ;ou EM) table. D. *ou c-eate an alte-nati0e name o- the em,lo;ees table in the %R schema in ;ou- o8n schema. #ns8e-: D
K. 2hat is t-ue about seuences3 #. The sta-t 0alue o the seuence is al8a;s 1. B. # seuence al8a;s inc-ements b; 1. C. The minimum 0alue o an ascen+in seuence +eaults to 1. D. The maAimum 0alue o +escen+in seuence +eaults to 1. #ns8e-: # 1'. EAamine the S>L statements that c-eates ORDERS table: CRE#TE T#BLE o-+e-s SER_NO NUMBER UNI>UE/ ORDER_ID NUMBER/ ORDER_D#TE D#TE NOT NULL ST#TUS $#RC%#RD&1'" C%EC status IN FCREDITG/GC#S%G""/ )ROD_ID_NUMBER RE4ERENCES )RODUCTS)RODUCT_ID"/ ORD_TOT#L NUMBER/ )RIM#R* E* o-+e- i+/ o-+e- +ate""5 4o- 8hich columns 8oul+ an in+eA be automaticall; c-eate+ 8hen ;ou eAecute the abo0e S>L statement3 Choose t8o" #. SER_NO B. ORDER_ID C. ST#TUS D. )ROD_ID E. ORD_TOT#L 4. Com,osite in+eA on ORDER_ID an+ ORDER_D#TE #ns8e-: #/ 4
11. 2hat is t-ue about seuences3 #. Once c-eate+/ a seuence belons to a s,eciic schema. B. Once c-eate+/ a seuence is lin
NUMBER $#RC%#R&9'" $#RC%#R&&'" NUMBER NUMBER
NOT NULL
Page 74 of 81 DE)#RTMENT_ID
NUMBER
*ou 8ant to c-eate a S>L sc-i,t ile that contains an INSERT statement. 2hen the sc-i,t is -un/ the INSERT statement shoul+ inse-t a -o8 8ith the s,eciie+ 0alues into the EM)LO*EES table. The INSERT statement shoul+ ,ass 0alues to the table columns as s,eciie+ belo8: EM)LO*EE_ID: NeAt 0alue -om the seuence EM)_ID_SE>EM)_N#ME an+ POB_ID: #s s,eciie+ b; the use- +u-in -un time/ th-ouh substitution 0a-iables S#L: &''' MR_ID: No 0alue DE)#RTMENT_ID: Su,,lie+ b; the use- +u-in -un time th-ouh substitution 0a-iable. The INSERT statement shoul+ ail i the use- su,,lies a 0alue othe- than &' o- ('. 2hich INSERT statement meets the abo0e -eui-ements3 #. INSERT INTO em,lo;ees $#LUES em,_i+_se.NEWT$#L/ 7Zename7/ 7Z=obi+7/ &'''/ NULL/ B. INSERT INTO em,lo;ees $#LUES em,_i+_se.NEWT$#L/ 7Zename7/ 7Z=obi+7/ &'''/ NULL/ Z+i+ IN &'/('""5 C. INSERT INTO SELECT ? 4ROM em,lo;ees 2%ERE +e,a-tment_i+ IN &'/('"" $#LUES em,_i+_se.NEWT$#L/ 7Zename7/ 7Z=obi+7/ &'''/ NULL/ D. INSERT INTO SELECT ? 4ROM em,lo;ees 2%ERE +e,a-tment_i+ IN &'/('" 2IT% C%EC O)TION" $#LUES em,_i+_se.NEWT$#L/ 7Zename7/ 7Z=obi+7/ &'''/ NULL/ E. INSERT INTO SELECT ? 4ROM em,lo;ees 2%ERE +e,a-tment_i+ &' #ND +e,a-tment_i+ ('" 2IT% C%EC O)TION " $#LUES em,_i+_se.NEWT$#L/ 7Zename7/ 7Z=obi+7/ &'''/ NULL/
Z+i+"5
Z+i+"5
Z+i+"5
Z+i+"5
#ns8e-: D
Lesson 1&: Use- #ccess 1. *ou nee+ to i0e the M#N#ER -ole the abilit; to select -om/ inse-t into/ an+ mo+i; eAistin -o8s in the STUDENT_R#DES table. #n;one i0en this M#N#ER -ole shoul+ be able to ,ass those ,-i0ilees on to othe-s. 2hich statement accom,lishes this3 #. R#NT select/ inse-t/ u,+ate ON stu+ent_-a+es TO manae-5 B. R#NT select/ inse-t/ u,+ate ON stu+ent_-a+es TO ROLE manae-5 C. R#NT select/ inse-t/ mo+i; ON stu+ent_-a+es TO manae- 2IT% R#NT O)TION5 D. R#NT select/ inse-t/ u,+ate ON stu+ent_-a+es TO manae- 2IT% R#NT O)TION5 E. R#NT select/ inse-t/ u,+ate ON stu+ent_-a+es TO ROLE manae- 2IT% R#NT O)TION5 4. 4.R#NT select/ inse-t/ mo+i; ON stu+ent_-a+es stu +ent_-a+es TO ROLE manae- 2IT% R#NT O)TION5
Page 75 of 81 #ns8e-: D &. EAamine the +esc-i,tion o the EM)LO*EES table: EM)_ID L#ST_N#ME 4IRST_N#ME DE)T_ID POB_C#T S#L#R*
NUMBER!" NOT NULL $#RC%#R&9'" NOT NULL $#RC%#R&9'" NUMBER&" $#RC%#RD&9'" NUMBERJ/&"
2hich statement sho8s the maAimum sala-; ,ai+ in each =ob cateo-; o each +e,a-tment3 #. SELECT +e,t_i+/ =ob_cat/ M#Wsala-;" 4ROM em,lo;ees 2%ERE sala-; Q M#Wsala-;"5 B. SELECT +e,t_i+/ =ob_cat/ M#Wsala-;" 4ROM em,lo;ees ROU) B* +e,t_i+/ =ob_cat5 C. SELECT +e,t_i+/ =ob_cat/ M#Wsala-;" 4ROM em,lo;ees5 D. SELECT +e,t_i+/ =ob_cat/ M#Wsala-;" 4ROM em,lo;ees ROU) B* +e,t_i+5 E. SELECT +e,t_i+/ =ob_cat/ M#Wsala-;" 4ROM em,lo;ees ROU) B* +e,t_i+/ =ob_cat/ sala-;5 #ns8e-: B
Page 76 of 81 9. 2hich +ata +ictiona-; table shoul+ ;ou ue-; to 0ie8 the ob=ect ,-i0ilees -ante+ to the use- on s,eciic columns3 #. USER_T#B_)RI$S_M#DE B. USER_T#B_)RI$S C. USER_COL_)RI$S_M#DE D. USER_COL_)RI$S #ns8e-: D !. 2hich t8o statements accu-atel; +esc-ibe a -ole3 Choose t8o." #. # -ole can be i0en to a maAimum o 1''' use-s. B. # use- can ha0e access to a maAimum o 1' -oles. C. # -ole can ha0e a maAimum o 1'' ,-i0ilees containe+ in it. D. )-i0ilees a-e i0en to a -ole b; usin the CRE#TE ROLE statement. E. # -ole is a name+ -ou, o -elate+ ,-i0ilees that can be -ante+ to the use-. 4. # use- can ha0e access to se0e-al -oles/ an+ se0e-al use-s can be assine+ the same -ole. #ns8e-: D/ 4 (. 2hat is necessa-; o- ;ou- ue-; on an eAistin 0ie8 to eAecute successull;3 #. The un+e-l;in tables must ha0e +ata. B. *ou nee+ SELECT ,-i0ilees on the 0ie8. C. The un+e-l;in tables must be in the same schema. D. *ou nee+ SELECT ,-i0ilees onl; on the un+e-l;in tables. #ns8e-: B . Scott issues the S>L statements: CRE#TE T#BLE +e,t +e,tno NUMBER&"/ +name $#RC%#R&1!"/ loc $#RC%#R&19"\5 R#NT SELECT ON DE)T TO SUE5 I Sue nee+s to select -om Scott7s DE)T table/ 8hich comman+ shoul+ she use3 #. SELECT ? 4ROM DE)T5 B. SELECT ? 4ROM SCOTT.DE)T5 C. SELECT ? 4ROM DB#.SCOTT.DE)T5 D. SELECT ? 4ROM #LL_USERS 2%ERE USER_N#ME 7SCOTT7 #ND T#BLE N#ME 7DE)T75 #ns8e-: B . 2hich statement c-eates a ne8 use-3 #. CRE#TE USER susan5 B. CRE#TE OR RE)L#CE USER susan5 C. CRE#TE NE2 USER susan DE4#ULT5 D. CRE#TE USER susan IDENTI4IED B* blue5 E. CRE#TE NE2 USER susan IDENTI4IED b; blue5
Page 77 of 81 4. CRE#TE OR RE)L#CE USER susan IDENTI4IED B* blue5 #ns8e-: D J. 2hat is t-ue about the 2IT% R#NT O)TION clause3 #. It allo8s a -antee DB# ,-i0ilees. B. It is -eui-e+ s;ntaA o- ob=ect ,-i0ilees. C. It allo8s ,-i0ilees on s,eciie+ columns o tables. D. It is use+ to -ant an ob=ect ,-i0ilee on a o-ein L comman+: CRE#TE USER scott IDENTI4IES b; tie-5 2hat ,-i0ilees +oes the use- Scott ha0e at this ,oint3 #. No ,-i0ilees. B. Onl; the SELECT ,-i0ilee. C. Onl; the CONNECT ,-i0ilee. D. #ll the ,-i0ilees o a +eault use-. #ns8e-: # 1'. *ou a-e the DB# o- an aca+emic +atabase. *ou nee+ to c-eate a -ole that allo8s a -ou, o use-s to mo+i; eAistin -o8s in the STUDENT_R#DES table. 2hich set o statements accom,lishes this3 #. CRE#TE ROLE -eist-a-5 R#NT MODI4* ON stu+ent_-a+es TO -eist-a-5 R#NT -eist-a- to use-1/ use-&/ use-9 B. CRE#TE NE2 ROLE -eist-a-5 R#NT #LL ON stu+ent_-a+es TO -eist-a-5 R#NT -eist-a- to use-1/ use-&/ use-9 C. CRE#TE ROLE -eist-a-5 R#NT U)D#TE ON stu+ent_-a+es TO -eist-a-5 R#NT ROLE -eist-a- to use-1/ use-&/ use-9 D. CRE#TE ROLE -eist-a-5 R#NT U)D#TE ON stu+ent_-a+es TO -eist-a-5 R#NT -eist-a- to use-1/ use-&/ use-95 E. CRE#TE -eist-a-5 R#NT C%#NE ON stu+ent_-a+es TO -eist-a-5 R#NT -eist-a-5 #ns8e-: D 11. The use- Sue issues this S>L statement: R#NT SELECT ON sue.EM) TO alice 2IT% R#NT O)TION5 The use- #lice issues this S>L statement: R#NT SELECT ON sue.EM) TO -eena 2IT% R#NT O)TION5 The use- Reena issues this S>L statement: R#NT SELECT ON sue.EM) TO timbe-5 The use- Sue issues this S>L statement: RE$OE select on sue.EM) 4ROM alice5
Page 78 of 81 4o- 8hich use-s +oes the -e0oL comman+: CRE#TE USER scott IDENTI4IED b; tie-5 2hat ,-i0ilees +oes the use- Scott ha0e at this ,oint3 #. no ,-i0ilees B. onl; the SELECT ,-i0ilee C. onl; the CONNECT ,-i0ilee D. all the ,-i0ilees o a +eault use#ns8e-: # 19. The DB# issues this S>L comman+: CRE#TE USER scott IDENTI4IED b; tie-5 2hat ,-i0ilees +oes the use- Scott ha0e at this ,oint3 #. no ,-i0ilees B. onl; the SELECT ,-i0ilee C. onl; the CONNECT ,-i0ilee D. all the ,-i0ilees o a +eault use#ns8e-: # 1!. EAamine the statement: R#NT select/ inse-t/ u,+ate ON stu+ent_-a+es TO manae2IT% R#NT O)TION5 2hich t8o a-e t-ue3 Choose t8o." #. M#N#ER must be a -ole. B. It allo8s the M#N#ER to ,ass the s,eciie+ ,-i0ilees on to othe- use-s. C. It allo8s the M#N#ER to c-eate tables that -ee- to the STUDENT_R#DES table. D. It allo8s the M#N#ER to a,,l; all DML statements on the STUDENT_R#DES table. E. It allo8s the M#N#ER the abilit; to select -om/ inse-t into/ an+ u,+ate the STUDENT_R#DES table. 4. It allo8s the M#N#ER the abilit; to select -om/ +elete -om/ an+ u,+ate the STUDENT_R#DES table.
Page 79 of 81 #ns8e-: B/ E 1(. The use- #lice 8ants to -ant all use-s ue-; ,-i0ilees on he- DE)T table. 2hich S>L statement accom,lishes this3 #. R#NT select ON +e,t TO #LL_USERS5 B. R#NT select ON +e,t TO #LL5 C. R#NT >UER* ON +e,t TO #LL_USERS D. R#NT select ON +e,t TO )UBLIC5 #ns8e-: D
1. 2hich ob=ect ,-i0ilees can be -ante+ on a 0ie83 #. none B. DELETE/ INSERT/SELECT C. #LTER/ DELETE/ INSERT/ SELECT D. DELETE/ INSERT/ SELECT/ U)D#TE #ns8e-: D 1. 2hich one is a s;stem ,-i0ilee3 #. SELECT B. DELETE C. EWECUTE D. #LTER T#BLE E. CRE#TE T#BLE #ns8e-: E 1J. EAamine these statements: CRE#TE ROLE -eist-a-5 R#NT U)D#TE ON stu+ent_-a+es TO -eist-a-5 R#NT -eist-a- to use-1/ use-&/ use-95 2hat +oes this set o S>L statements +o3 #. The set o statements contains an e--o- an+ +oes not 8o-<. B. It c-eates a -ole calle+ REISTR#R/ a++s the MODI4* ,-i0ilee on the STUDENT_R#DES ob=ect to the -ole/ an+ i0es the REISTR#R -ole to th-ee use-s. C. It c-eates a -ole calle+ REISTR#R/ a++s the U)D#TE ,-i0ilee on the STUDENT_R#DES ob=ect to the -ole/ an+ i0es the REISTR#R -ole to th-ee use-s. D. It c-eates a -ole calle+ REISTR#R/ a++s the U)D#TE ,-i0ilee on the STUDENT_R#DES ob=ect to the -ole/ an+ c-eates th-ee use-s 8ith the -ole. E. It c-eates a -ole calle+ REISTR#R/ a++s the U)D#TE ,-i0ilee on th-ee use-s/ an+ i0es
Page 80 of 81 the REISTR#R -ole to the STUDENT_R#DES ob=ect. 4. It c-eates a -ole calle+ STUDENT_R#DES/ a++s the U)D#TE ,-i0ilee on th-ee use-s/ an+ i0es the U)D#TE -ole to the -eist-a-. #ns8e-: C
Lesson 19: )-o+ucin Rea+able Out,ut 8ith iS>L?)lus 1. 2hich iS>L?)lus eatu-e can be use+ to -e,lace 0alues in the 2%ERE clause3 #. Substitution 0a-iables B. Re,lacement 0a-iables C. )-om,t 0a-iables D. Instea+o 0a-iables E. This eatu-e cannot be im,lemente+ th-ouh iS>L?)lus. #ns8e-: # &. 2hich substitution 0a-iable 8oul+ ;ou use i ;ou 8ant to -euse the 0a-iable 8ithout ,-om,tin the use- each time3 #. Z B. #CCE)T C. )ROM)T D. ZZ #ns8e-: D 9. 2hich S>L statement acce,ts use- in,ut o- the columns to be +is,la;e+/ the table name/ an+ 2%ERE con+ition3 #. SELECT Z1/ Z& 4ROM Z9 2%ERE last_name 7Z!75 B. SELECT Z1/ 7Z&7 4ROM Z9 2%ERE 7Zlast_name 7Z!7 75 C. SELECT Z1/ Z& 4ROM Z9 2%ERE last_name 7Z!75 D. SELECT Z1/ 7Z&7 4ROM EM) 2%ERE last_name 7Z!75 #ns8e-: C
