Semester 1 Mid Term Exam - Part 2

Test:Semester1MidTermExam-PartII 1. APL APL/S /SQL QLb blo loc ckco kcont ntai ains nst the hef fol ollo lowi wing ngc cod ode: e: v_counter:=1; LOOP EXITWHENv_counter=5; v_counter:=v_counter+1; ENDLOOP; WhatisthevalueofV_COUNTERaftertheloopisfinished? MarkforReview (1)Points 5(*) 6 1 Thisisaninfiniteloop;theloopwillneverfinish.

Incorrect.RefertoSection4Lesson3. 2. Whi Which cho one neo of fth thes ese eta task sks sis isb bes est tdo done neu usi sing nga aL LOO OOP Pst stat at ement? ement? MarkforR MarkforReview eview (1)Points Assigningalettergradetoanumericalscore Calculatinganddisplayingthesumofallinte Calculatinganddispla yingthesumofallintegersfrom1to100(*) gersfrom1to100(*) Testingifaconditionistrue,falseornull Fetchinganddisplayinganemployee'slastnam Fetchinganddisplayin ganemployee'slastnamefromthedatabase efromthedatabase

Correct 3. The TheE EXI XIT Tst stat atem emen ent tca can nbe bel loc ocat ated eda any nywh wher ere ein insi side dea ab bas as icloop.TrueorFalse? icloop.TrueorFalse ? MarkforReview (1)Points True(*)

False

Correct 4. Wha What tki kind ndo of fst stat atem emen ent tis isb bes est tsu suit ited edf for ord dis ispl play ayin ing gth th emultiplicationtablefor"sixes":6x1=6,6x2 emultiplicationtable for"sixes":6x1=6,6x2=12...6x12=72? =12...6x12=72? MarkforReview (1)Points CASEexpression IFstatement CASEstatement LOOPstatement(*)

Correct 5.

Wh i c h o n e o f t h e s e i s N O T a ki n d o f l o o p ?

Review (1)Points ASCENDINGloop(*) FORloop Basicloop WHILEloop

Correct 6.

E x a m i n e t h e fo l l o w i n g c o d e :

DECLARE aVARCHAR2(6):=NULL; bVARCHAR2(6):=NULL; BEGIN IFa=bTHEN DBMS_OUTPUT.PUT_LINE('EQUAL'); ELSIFa!=bTHEN DBMS_OUTPUT.PUT_LINE('UNEQUAL'); ELSE DBMS_OUTPUT.PUT_LINE('OTHER');

M a rk f or

ENDIF; END; Whichwordwillbedisplayed? MarkforReview (1)Points UNEQUAL EQUAL Nothingwillbedisplayed OTHER(*)

Correct 7. MarkforReview (1)Points

Wha What tis ist the hec cor orre rect ctf for orm mof ofa as sim impl ple eIF IFs sta tate teme ment nt? ?

IFconditionTHENstatement; IFconditionTHENstatement; ENDIF;(*) IFcondition; THENstatement; ENDIF; IFcondition THENstatement ENDIF;

Correct 8. THEN-ELSE THEN-ELSEstruc structures tures? ? (1)Points

Wha What tis ist the hec cor orre rect ctn nam ame efo for rCA CASE SE, ,LO LOOP OP, ,WH WHIL ILE, E,a and ndI IFFMarkforR MarkforReview eview

Controlstructures(*) Arraystructures

Memorystructures Cursorstructures

Correct 9. MarkforReview (1)Points

Wha What tis ist the hec cor orre rect ctf for orm mof ofa ac com ompo poun und dIF IFs sta tate teme ment nt? ?

IFcondition THENstatement1 ELSEstatement2; IFcondition THENstatement1 ELSEstatement2; ENDIF; IFcondition; THENstatement1; ELSEstatement2; ENDIF; IFconditionTHENstatement1; ELSEstatement2; ENDIF; (*)

Correct 10. 10. Wha What tty type peo of fco cont ntro rol lst stru ruct ctur ures esa are rer rep epet etit itio ion nst stat atem emen en tsthaten tsthatenable ableyout youtoexe oexecute cutestate statements mentsina inaPLSQ PLSQLblo Lblockre ckrepeate peatedly? dly? Markfor Markfor Review (1)Points IFstatements Loops(*) CASEexpressions

CASEstatements

Incorrect.RefertoSection4Lesson1. 11. Howm Howmany anyEL ELSIF SIFst state atemen ments tsare areyo youa uallo llowed wedto toha have vein inac acomp ompoun oundI dIFs Fstat tat ement? ement? MarkforR MarkforReview eview (1)Points Onlyone Asmanyasyouwant(*) Theymustmatchthesamenumberasthenumber Theymustmatchthesa menumberasthenumberofELSEstatements. ofELSEstatements. None;thecommandisELSEIF;

Correct 12. 12. You Youn nee eed dto toe exe xecu cute tea as set eto of fst stat atem emen ents ts1 10 0ti time mes, s,i inc ncre re asingacounterby1eachtime.Whichofthef asingacounterby1e achtime.WhichofthefollowingPL/SQLconstruct ollowingPL/SQLconstructscando scando this? this?(Ch (Choos ooset ethre hree) e) Markf Markfor orRev Review iew (1)Points (Chooseallcorrectanswers) IF...THEN...ELSE AWHILEloop(*) CASE...WHEN...THEN AFORloop(*) Abasicloop(*)

Incorrect.RefertoSection4Lesson1. 13. 13. Wha What twi will llb be eth the eva valu lue eof ofv v_s _sal al_d _des esc caf afte ter rth the efo foll llow owin in gcodeisexecuted? DECLARE v_salaryNUMBER(6,2):=NULL; v_sal_descVARCHAR2(10); BEGIN

CASE WHENv_salary<10000THENv_sal_desc:='LowPaid'; WHENv_salary>=10000THENv_sal_desc:='HighPaid'; ENDCASE; END; MarkforReview (1)Points HighPaid LowPaid Null Thecodewillfailandreturnanexception(*)

Correct 14. 14. Wha What twi will llb be eth the eva valu lue eof ofv v_r _res esul ult taf afte ter rth the efo foll llow owin ing g codeisexecuted? DECLARE v_gradeCHAR(1):=NULL; v_resultVARCHAR2(10); BEGIN CASEv_grade WHEN('A'OR'B')THENv_result:='VeryGood'; WHEN('E'OR'F')THENv_result:='Poor'; ELSEv_result:='InBetween'; ENDCASE; END; MarkforReview (1)Points Poor InBetween(*) Null VeryGood

Correct 15. 15. deisexecuted? DECLARE

Wha What tva valu lue ewi will llv v_a _ans nswe wer rco cont ntai ain naf afte ter rth the efo foll llow owin ing gco co

v_ageNUMBER:=18; v_answerVARCHAR2(10); BEGIN v_answer:= CASE WHENv_age<25THEN'Young' WHENv_age=18THEN'Exactly18' ELSE'Older' ENDCASE; END; MarkforReview (1)Points Exactly18 Young(*) Null Older

Correct 16. 16. Wha What twi will llb be eth the eva valu lue eof ofv var aria iabl ble ec caf afte ter rth the efo foll llow owin in gcodeisexecuted? DECLARE aBOOLEAN:=TRUE; bBOOLEAN:=NULL; cNUMBER; BEGIN IFaANDbTHENc:=2; ELSIFaORbTHENc:=0; ELSEc:=1; ENDIF; END; MarkforReview (1)Points 1 Null 0(*) 2

Correct

17. 17. You Youw wan ant tto toa ass ssig ign na ava valu lue eto tov v_r _res esul ult twh whic ich hde depe pend nds son on thevalueofv_grade:ifv_grade='A'setv_ thevalueofv_grade: ifv_grade='A'setv_resultto'VeryGood'and resultto'VeryGood'andsoon. soon. DECLARE v_gradeCHAR(1); v_resultVARCHAR2(10); BEGIN v_result:= CASEv_grade Thenextlineshouldbe MarkforReview (1)Points WHENv_grade='A'THEN'VeryGood' WHEN'A'THEN'VeryGood'; WHEN'A'THENv_result:='VeryGood'; WHEN'A'THEN'VeryGood'(*)

Correct 18.

Wha What tki kind nds sof ofl loo oops psc can anb be ene nest sted ed? ?

Mark Markf fo orRe rRevi view ew

(1)Points BASICloops WHILEloops FORloops Alloftheabove(*)

Correct 19. 19. Whe When nco codi ding ngt two won nes este ted dlo loop ops, s,b bot oth hlo loop ops smu must stb be eof oft the he sametype.Forexample,youcannotcodeaFORloopinsideaWHILEloop sametype.Forexample,youcannotcodeaFOR loopinsideaWHILEloop.Trueor .Trueor False? MarkforReview (1)Points True

False(*)

Correct 20. 20. You Youwa want ntt to odi disp spla lay ymu mult ltip ipli lica cati tion ont tab able les sfo for rnu numb mber ers sup up to12.Thedisplayshouldlooklikethis: 1x1=1 1x2=2 ..... 1x12=12 2x1=2 2x2=4 ..... 2x12=24 3x1=3 ..... ..... 12x12=144 Whichofthefollowingisanefficientwayto Whichofthefollowing isanefficientwaytodothisinPL/SQL? dothisinPL/SQL? MarkforReview (1)Points UsetwonestedFORloops.(*) Storeallthenumbersfrom1to144inatable,thenfetchanddisplay Storeallthenumbersfrom1to144inatable ,thenfetchanddisplayt t hemusingacursor. CreateafunctionwhichacceptstwonumbersasINparametersandreturn Createafunctionwhichacceptstwonumbersas INparametersandreturns s theirproduct.Invokethefunction144times. Writeananonymousblockwhichcontains144callstoDBMS_OUTPUT,each Writeananonymousblockwhichcontains144ca llstoDBMS_OUTPUT,eachl l ookinglike:DBMS_OUTPUT.PUT_LINE('7x9=63');

Correct 21. Exam Examin ine eth the ef follo ollowi wing ngc cod ode: e: DECLARE v_outer_countNUMBER:=1; v_inner_countNUMBER:=1; BEGIN LOOP LOOP v_inner_count:=v_inner_count+1; EXITWHENv_inner_count>5;--LineA ENDLOOP; v_outer_count:=v_outer_count+1; EXITWHENv_outer_count>3; ENDLOOP;

END; WhathappensatLineAwhenthevalueofV_INN WhathappensatLineA whenthevalueofV_INNER_COUNTequals6? ER_COUNTequals6? MarkforReview (1)Points Bothloopsareexitedandtheblock'sexecutionisterminated. Theinnerloopisexitedbuttheouterloopco Theinnerloopisexit edbuttheouterloopcontinuesexecution.(*) ntinuesexecution.(*) Theouterloopisexitedbuttheinnerloopco Theouterloopisexit edbuttheinnerloopcontinuesexecution. ntinuesexecution. Anerrorconditionisreturned.

Correct 22. 22. In Ina aWH WHIL ILE Elo loop op, ,th the est stat atem emen ents tsi ins nsid ide eth the elo loop opm mus ust tex ex ecute ecuteat atlea least stonc once. e.Tru Trueo eorF rFals alse? e? Markf Markfor orRev Review iew (1)Points True False(*)

Correct 23. 23. Whi Which chs sta tate teme ment ntb bes est tde desc scri ribe bes swh when ena aF FOR ORl loo oop psh shou ould ldb b eused? MarkforReview (1)Points Whenthenumberofiterationsisknown(*) WhentestingthevalueinaBooleanvariable Whenthecontrollingconditionmustbeevaluat Whenthecontrollingc onditionmustbeevaluatedatthestartofeachi edatthestartofeachit t eration

Correct 24. 24. BEGIN

Wha What twi will llh hap appe pen nwh when ent the hef fol ollo lowi wing ngc cod ode eis ise exe xecu cute ted? d?

FORiin1..3LOOP DBMS_OUTPUT.PUT_LINE(i); i:=i+1; ENDLOOP; END; MarkforReview (1)Points Itwilldisplay1,2,3. Itwilldisplay2,3,4. ItwillresultinanerrorbecauseyoucannotmodifythecounterinaF Itwillresultinanerrorbecauseyoucannot modifythecounterinaFO O Rloop.(*) Itwillresultinanerrorbecausethecounter Itwillresultinane rrorbecausethecounterwasnotexplicitlydecla wasnotexplicitlydeclar r ed.

Correct 25 .

E x a m i n e t he f o l l o w i n g c o d e:

DECLARE v_boolBOOLEAN:=FALSE; v_counterNUMBER(4):=0; BEGIN ...LineA  END; WhichofthefollowingisNOTvalidatlineA? MarkforReview (1)Points WHILENOTv_booleanLOOP WHILEv_booleanANDv_counter<6LOOP WHILEv_counter>8LOOP WHILEv_counterIN1..5LOOP(*)

Correct 26. 26. efollowin efollowingis gistrue true? ?

Whe When nus usin ing ga aco coun unte ter rto toc con ontr trol ola aF FOR ORl loo oop, p,w whi hich cho of fth th MarkforR MarkforReview eview

(1)Points Youmusthaveexactlyonecounterbutitisim Youmusthaveexactly onecounterbutitisimplicitlydeclared.(*) plicitlydeclared.(*) Youmusthaveexactlyonecounterandyoumust Youmusthaveexactly onecounterandyoumustexplicitlydeclareit. explicitlydeclareit. Youcanhavemultiplecounters,butyouneedatleastone. Youdon'tneedacounter;youcantestforany Youdon'tneedacount er;youcantestforanything(forexample,wheth thing(forexample,whethe e raBOOLEANisTRUEorFALSE).

Correct

Section5 (Answerallquestionsinthissection) 27. 27. The Thef fol ollo lowi wing ngc cur urso sor rha has sbe been end dec ecla lare red: d: CURSORemp_cursIS SELECTfirst_name,last_name,job_id,salary FROMemployees; Whichofthefollowingcorrectlydeclaresacompositerecordwiththes Whichofthefollowingcorrectlydeclaresaco mpositerecordwiththesamestruc amestruc tureasthecursor? MarkforReview (1)Points emp_recemp_rec%ROWTYPE; emp_recemp_curs%TYPE; emp_recemp_curs%ROWTYPE;(*) emp_reccursor%ROWTYPE;

Correct 28. 28. sorattrib sorattributei uteistru strue? e? (1)Points

Whi Which cho of fth the efo foll llow owin ing gst stat atem emen ents tsa abo bout utt the he% %IS ISOP OPEN ENc cur ur MarkforR MarkforReview eview

Youcanissuethe%ISOPENcursorattributeonl Youcanissuethe%ISO PENcursorattributeonlywhenacursorisopen. ywhenacursorisopen.

Youcanissuethe%ISOPENcursorattributeonl Youcanissuethe%ISO PENcursorattributeonlywhenmorethanonereco ywhenmorethanonerecor r disreturned. Youcanissuethe%ISOPENcursorattributewhe Youcanissuethe%ISO PENcursorattributewhenacursorisopenorclo nacursorisopenorclos s ed.(*) Ifacursorisopen,thenthevalueof%ISOPENisfalse.

Correct 29. 29. The TheD DEP EPAR ARTM TMEN ENTS TSt tab able lec con onta tain ins sfo four urc col olum umns ns. .Ex Exam amin ine eth th efollowingcode: DECLARE CURSORdept_cursIS SELECT*FROMdepartments; v_dept_recdept_curs%ROWTYPE; BEGIN OPENdept_curs; FETCHdept_cursINTOv_dept_rec; ... Whichoneofthefollowingstatementsistrue? MarkforReview (1)Points v_dept_reccontainsthefirstfourrowsofthe v_dept_reccontainsth efirstfourrowsofthedepartmentstable. departmentstable. TheFETCHwillfailbecausethestructureofv_dept_recdoesnotmatch TheFETCHwillfailbecausethestructureofv _dept_recdoesnotmatcht t hestructureofthecursor. v_dept_reccontainsthefirstrowofthedepartmentstable.(*) Theblockwillfailbecausethedeclarationof Theblockwillfailbe causethedeclarationofv_dept_recisinvalid. v_dept_recisinvalid.

Correct 30. 30. The Thee emp mplo loye yees est tab able lec con onta tain ins s11 11c col olum umns ns. .Th The efo foll llow owin ing g blockdeclaresacursorandarecordbasedonthecursor: DECLARE CURSORemp_cursIS SELECT*FROMemployees; v_emp_recemp_curs%ROWTYPE; Atwelfthcolumnisnowaddedtotheemployeestable.Whichofthefoll Atwelfthcolumnisnowaddedtotheemployees table.Whichofthefollowingsta owingsta tementsistrue?

MarkforReview (1)Points Thedeclarationofemp_recmustbechangedto Thedeclarationofemp _recmustbechangedtoaddanextrafield. addanextrafield. Theblockwillstillworkcorrectlywithoutan Theblockwillstillw orkcorrectlywithoutanychangestothePL/SQLc ychangestothePL/SQLco o de.(*) TheblockwillfailandanINVALID_CURSORexce Theblockwillfailan danINVALID_CURSORexceptionwillberaised. ptionwillberaised. Anextrascalarvariablemustbedeclaredtoc Anextrascalarvariab lemustbedeclaredtocorrespondtothetwelfth orrespondtothetwelftht t ablecolumn.

Correct 31. Anim Animpli plicit citcu curso rsorc rcan anbe beuse usedf dfor oram amult ultipl iple-r e-row owSEL SELECT ECTst state atemen ment. t.Tru Tru eor eorF Fal alse se? ? Mark Markf for orR Rev evie iew w (1)Points True False(*)

Correct 32. 32.

Pla Place cet the hef fol ollo lowi wing ngs sta tate teme ment nts sin int the hec cor orre rect cts seq eque uenc nce: e:

OPENmy_curs; CLOSEmy_curs; CURSORmy_cursISSELECTmy_columnFROMmy_table; FETCHmy_cursINTOmy_variable; MarkforReview (1)Points C,D,A,B C,A,D,B(*) A,C,D,B C,A,B,D

Correct

33. 33. An Anex expl plic icit itc cur urso sor rmu must sta alw lway ays sbe bed dec ecla lare red, d,o ope pene ned dan and d closedbythePL/SQLprogrammer.TrueorFalse? closedbythePL/SQLprogrammer.TrueorFalse ? MarkforReview (1)Points True False(*)

Correct 34. 34. Whi Which cho of fth thes ese eco cons nstr truc ucts tsc can anb be eus used edt to ofe fetc tch hmu mult ltip iple le row rows sfr from oma ac cur urso sor' r's sac acti tive ves set et? ? Mark Markf for orR Rev evie iew w (1)Points ACASEstatement AnIF....ELSEstatement AbasicloopwhichincludesFETCHandEXITWHENstatements(*) AbasicloopwhichincludesOPEN,FETCHandCLOSEstatements

Correct 35. 35. MarkforReview (1)Points

Whi Which cho of fth thes ese eis isN NOT OTa av val alid idc cur urso sor rde decl clar arat atio ion? n?

CURSORemp_cursIS SELECTsalary FROMemployees ORDERBYsalaryDESC; CURSORemp_cursIS SELECTsalary FROMemployees WHERElast_nameLIKE'S%'; CURSORemp_dept_cursIS SELECTe.salary,d.department_name FROMemployeese,departmentsd WHEREe.department_id=d.department_id;

CURSORemp_cursIS SELECTsalaryINTOv_salary FROMemployees; (*)

Correct 36. 36. Aft After era ac cur urso sor rha has sbe been enc clo lose sed, d,i it tca can nbe beo ope pene ned dag agai ain n int inthe hes sam ame ePL PL/S /SQL QLb blo lock ck. .Tr True ueo or rFa Fals lse? e? Mark Markf for orR Rev evie iew w (1)Points True(*) False

Correct 37. 37. Whi Which cho of fth thes ese est stat atem emen ents tsa abo bout uti imp mpli lici cit tcu curs rsor ors sis isN NOT OT true? true? MarkforR MarkforReview eview (1)Points TheyaredeclaredautomaticallybyOraclefor Theyaredeclaredauto maticallybyOracleforsingle-rowSELECTstateme single-rowSELECTstatemen n ts. TheyaredeclaredautomaticallybyOraclefor Theyaredeclaredauto maticallybyOracleforallDMLstatements. allDMLstatements. TheyaredeclaredbythePL/SQLprogrammer.(*) TheyareopenedandclosedautomaticallybyOracle.

Correct 38. 38. Wha What twi will llh hap appe pen nwh when ent the hef fol ollo lowi wing ngc cod ode eis ise exe xecu cute ted? d? DECLARECURSORemp_cursIS SELECTsalaryFROMemployees; v_salaryemployees.salary%TYPE; BEGIN OPENemp_curs; FETCHemp_cursINTOv_salary; CLOSEemp_curs; FETCHemp_cursINTOv_salary; END; MarkforReview (1)Points

TheblockwillfailandanINVALID_CURSORexce Theblockwillfailan danINVALID_CURSORexceptionwillberaised.(*) ptionwillberaised.(*) Thefirstemployeerowwillbefetchedtwice. Thefirsttwoemployeerowswillbefetched. TheblockwillfailandaTOO_MANY_ROWSexcept Theblockwillfailan daTOO_MANY_ROWSexceptionwillberaised. ionwillberaised.

Correct 39. 39.

Wha What twi will llh hap appe pen nwh when ent the hef fol ollo lowi wing ngc cod ode eis ise exe xecu cute ted? d?

DECLARE CURSORemp_cursIS SELECTsalaryFROMemployees; v_salaryemployees.salary%TYPE; BEGIN FETCHemp_cursINTOv_salary; DBMS_OUTPUT.PUT_LINE(v_salary); CLOSEemp_curs; END; MarkforReview (1)Points Thefirstemployee'ssalarywillbefetchedanddisplayed. Allemployees'salarieswillbefetchedanddisplayed. Theexecutionwillfailandanerrormessagew Theexecutionwillfai landanerrormessagewillbedisplayed.(*) illbedisplayed.(*) Thelowestsalaryvaluewillbefetchedanddisplayed.

Correct 40. 40. You Youw wan ant tto tod dis ispl play aya all lll loc ocat atio ions ns, ,an and dth the ede depa part rtme ment nts s ineachlocation.Examinethefollowingcode: DECLARE CURSORloc_cursISSELECT*FROMlocations; CURSORdept_curs(p_loc_idNUMBER)IS SELECT*FROMdepartmentsWHERElocation_id=p_loc_id; BEGIN FORloc_recINloc_cursLOOP DBMS_OUTPUT.PUT_LINE(loc_rec.city); FORdept_recINdept_curs(--PointA--)LOOP DBMS_OUTPUT.PUT_LINE(dept_rec.department_name); ENDLOOP;

ENDLOOP; END; WhatshouldyoucodeatPointA? MarkforReview (1)Points p_loc_id location_id null LOOP...ENDLOOP; loc_rec.location_id(*)

Correct 41. When Whenusi using ngmul multip tiple lenes nested tedcu curso rsors, rs,wh what atkin kinds dsof ofloo loops pscan canyo youu uuse? se? MarkforReview (1)Points CursorFORloopsonly. Basicloopsonly. WHILEloopsonly. Noneoftheabove. Alloftheabove.(*)

Correct 42. 42. singlePL singlePL/SQL /SQLblock block? ? (1)Points Oneortwo. Onlyone.

How Howm man any yex expl plic icit itc cur urso sors rsc can anb be ede decl clar ared eda and ndu use sed din ina a MarkforR MarkforReview eview

Asmanyasneeded.(*) Uptoeightcursors. Noneoftheabove.

Correct 43.

Exa Exami mine net the hef fol ollo lowi wing ngc cod ode: e:

DECLARE CURSORcISSELECT*FROMemployeesFORUPDATE; c_recc%ROWTYPE; BEGIN OPENc; FORiIN1..20LOOP FETCHcINTOc_rec; IFi=6THEN UPDATEemployeesSETfirst_name='Joe' WHERECURRENTOFc; ENDIF; ENDLOOP; CLOSEc; END; Whichemployeeroworrowswillbeupdatedwhe Whichemployeerowor rowswillbeupdatedwhenthisblockisexecuted? nthisblockisexecuted? MarkforReview (1)Points Thefirst6fetchedrowswillbeupdated. Norowswillbeupdatedbecauseyoulockedthe Norowswillbeupdate dbecauseyoulockedtherowswhenthecursorwas rowswhenthecursorwas opened. The6thfetchedrowwillbeupdated.(*) Theblockwillnotcompilebecausethecursorshouldhavebeendeclared Theblockwillnotcompilebecausethecursor shouldhavebeendeclared ....FORUPDATEWAIT5; Noneoftheabove.

Correct 44. 44. Use User rTO TOM Mha has slo lock cked eda ar row owi in nth the eWO WORK RKER ERS Sta tabl ble. e.N Now ow, ,us us erDICKwantstoopenthefollowingcursor:

CURSORcIS SELECT*FROMworkersFORUPDATENOWAIT; WhatwillhappenwhenDICKopensthecursoran Whatwillhappenwhen DICKopensthecursorandtriestofetchrows? dtriestofetchrows? MarkforReview (1)Points TOM'ssessionisrolledback.DICK'ssessions TOM'ssessionisrolle dback.DICK'ssessionsuccessfullyfetchesrows uccessfullyfetchesrowsf f romthecursor. DICK'ssessionwaitsindefinitely. Bothsessionswaitforafewseconds;thenthesystembreaksalllocks Bothsessionswaitforafewseconds;thenthe systembreaksalllocksa a ndbothsessionsraiseanexception. DICK'ssessionimmediatelyraisesanexception.(*) Thec%NOWAITattributeissettoTRUE.

Correct 45. 45. You Youw wan ant tto tod dec ecla lare rea ac cur urso sor rwh whic ich hlo lock cks sea each chr row owf fet etch ch edbythecursor.Examinethefollowingcode: DECLARE CURSORemp_cursIS SELECT*FROMemployees FOR--PointA WhichofthefollowingcanNOTbecodedatPointA? MarkforReview (1)Points UPDATE; UPDATEOFsalary; UPDATEOFemployees;(*) UPDATENOWAIT;

Correct

46. 46. Whe When nus usin ing ga acu curs rsor orF FOR ORl loo oop, p,O OPE PEN, N,C CLO LOSE SEa and ndF FET ETCH CHs sta ta tementsshouldnotbeexplicitlycoded.Trueo tementsshouldnotbe explicitlycoded.TrueorFalse? rFalse? MarkforReview (1)Points True(*) False

Correct 47. 47.

Wha What tis isw wro rong ngw wit ith hth the efo foll llow owin ing gco code de? ?

DECLARE CURSORdept_cursISSELECT*FROMdepartments; BEGIN FORdept_recINdept_cursLOOP DBMS_OUTPUT.PUT_LINE(dept_curs%ROWCOUNT DBMS_OUTPUT.PUT _LINE(dept_curs%ROWCOUNT||dept_rec.department_n ||dept_rec.department_name): ame): ENDLOOP; DBMS_OUTPUT.PUT_LINE(dept_rec.department_id); END; MarkforReview (1)Points ThecursorDEPT_CURShasnotbeenopened. TheimplicitlydeclaredrecordDEPT_RECcannot Theimplicitlydeclare drecordDEPT_RECcannotbereferencedoutsideth bereferencedoutsidethe e cursorFORloop.(*) Youcannotuse%ROWCOUNTwithacursorFORloop. ThecursorDEPT_CURShasnotbeenclosed. Nothingiswrong,thiscodewillexecutesuccessfully.

Incorrect.RefertoSection5Lesson3. 48. 48. Exa Exami mine net the hef fol ollo lowi wing ngc cod ode. e.T To odi disp spla lay yth the esa sala lary ryo of fan an employee,whatmustbecodedatPointA? DECLARE CURSORemp_cursISSELECT*FROMemployees; BEGIN FORemp_recINemp_cursLOOP DBMS_OUTPUT.PUT_LINE(--whatgoeshere?); ENDLOOP; END; MarkforReview (1)Points

salary emp_curs.salary emp_rec.salary(*) employees.salary emp_rec.salaryINemp_curs

Incorrect.RefertoSection5Lesson3. 49. 49. The There rea are re1 12 2di dist stin inct ctJ JOB OB_I _IDs Dsi in nth the eEM EMPL PLOY OYEE EES Sta tabl ble. e.Y Y ouneedtowritesomePL.SQLcodetofetchanddisplayalltheemployee ouneedtowritesomePL.SQLcodetofetchand displayalltheemployeeswitha switha specificJOB_ID.ThechosenJOB_IDcanbediff specificJOB_ID.Thec hosenJOB_IDcanbedifferenteachtimethecode erenteachtimethecodeisexecut isexecut ed. What Whati is sth the ebe best stw way ayt to odo dot thi his? s? Mark Markf for orR Rev evie iew w (1)Points Write12separatePL/SQLblocks,eachdeclarin Write12separatePL/S QLblocks,eachdeclaringacursorwithadiffere gacursorwithadifferen n tJOB_IDintheWHEREclause. WriteasinglePL/SQLblockwhichdeclares12cursors,oneforeachdis WriteasinglePL/SQLblockwhichdeclares12 cursors,oneforeachdist t inctvalueofJOB_ID. WriteasinglePL/SQLblockwhichdeclaresonecursorusingaparameter WriteasinglePL/SQLblockwhichdeclaresone cursorusingaparameter fortheJOB_ID.(*) WriteasinglePL/SQLblockwhichusesacursortofetchalltheemploy WriteasinglePL/SQLblockwhichusesacurso rtofetchalltheemploye e erows,withanIFstatementtodecidewhicho erows,withanIFsta tementtodecidewhichofthefetchedrowstodis fthefetchedrowstodisplay. play.

Correct

a c u rs o r ? (1)Points

50. 50. Wha What tis iso one neo of fth the ead adva vant ntag ages eso of fus usin ing gpa para rame mete ters rsw wit ith h Ma r k fo r Re v i ew

YoucanuseacursorFORloop. YoucandeclarethecursorFORUPDATE. YoudonotneedtoDECLAREthecursoratall.

Youcanuseasinglecursortofetchadifferentsetofrowseachtime Youcanuseasinglecursortofetchadiffere ntsetofrowseachtimet t hecursorisopened.(*) Itwillexecutemuchfasterthanacursorwithoutparameters.

Correct