Semester 2 Mid Term Exam Pl Sql

Test:Semester2MidTermExam 1. Whe When na aus user ers ses essi sion onc cha hang nges est the hev val alue ueo of fa apa pack ckag age eva vari riab able le, ,th the ene new wva va luecanim luecanimmedia mediately telybese beseenby enbyothe otherses rsessions sions.Tru .Trueor eorFalse False? ? MarkforR MarkforReview eview (1)Points True False(*)

Correct 2. Pac Packa kage geC CUR URSP SPAC ACK Kde decl clar ares esa ag glo loba bal lcu curs rsor ori in nth the epa pack ckag ag especification.Thepackagecontainsthreepublicprocedures:OPENPROC especification.Thepackagecontainsthreepu blicprocedures:OPENPROCopensth opensth ecursor;FETCHPROCfetches5rowsfromthecu ecursor;FETCHPROCfe tches5rowsfromthecursor'sactiveset;CLOSEP rsor'sactiveset;CLOSEPROCclose ROCclose sthecursor. Whatwillhappenwhenausersessionexecutes Whatwillhappenwhen ausersessionexecutesthefollowingcommandsin thefollowingcommandsintheorde theorde rshown? curspack.openproc;--line1 curspack.fetchproc;--line2 curspack.fetchproc;--line3 curspack.openproc;--line4 curspack.fetchproc;--line5 curspack.closeproc;--line6 MarkforReview (1)Points Thefirst15rowswillbefetched. Thefirst10rowswillbefetched,thenthefi Thefirst10rowswill befetched,thenthefirst5rowswillbefetche rst5rowswillbefetched d again. Thefirst5rowswillbefetchedthreetimes. Anerrorwilloccuratline2. Anerrorwilloccuratline4.(*)

Correct 3. An AnOr Orac acle led dir irec ecto tory ryc cal alle led dFI FILE LESD SDIR IRh has asb bee een ncr crea eate ted dby by executing: CREATEORREPLACEDIRECTORYfilesdirAS'C:\NEWFILES'; Whichofthefollowingwillcreateanewtext Whichofthefollowing willcreateanewtextfilecalledC:\NEWFILES\E filecalledC:\NEWFILES\EMP_REPORT MP_REPORT .TXT? .TXT? MarkforR MarkforReview eview (1)Points

UTL_FILE.CREATE('FILESDIR','EMP_REPORT.TXT'); UTL_FILE.FOPEN('C:\NEWFILES\EMP_REPORT.TXT','w'); UTL_FILE.FOPEN('FILESDIR','EMP_REPORT.TXT','w');(*) UTL_FILE.OPEN('FILESDIR','EMP_REPORT.TXT','c');

Correct 4. eUT eUTL_ L_FI FILE LEp pac acka kage ge? ? (1)Points

Whi Which cho of fth the efo foll llow owin ing gbe best std des escr crib ibes est the hep pur urpo pose seo of fth th Mark Markf for orR Rev evie iew w

Itisusedtoloadbinaryfilessuchasemploy Itisusedtoloadbin aryfilessuchasemployees'photosintothedata ees'photosintothedatab b ase. Itisusedtoreadandwritetextfilesstored Itisusedtoreadand writetextfilesstoredoutsidethedatabase.(* outsidethedatabase.(*) ) Itisusedtofindouthowmuchfreespaceis Itisusedtofindout howmuchfreespaceisleftonanoperatingsyst leftonanoperatingsyste e mdisk. ItisusedtoqueryCHARandVARCHAR2columnsintables.

Correct 5. The TheU UTL TL_F _FIL ILE Epa pack ckag age eca can nbe beu use sed dto toc cre reat ate ebi bina nary ryf fil iles es suchasJ suchasJPEGs PEGsaswe aswellas llastext textfile files.Tr s.Trueor ueorFals False? e? MarkforR MarkforReview eview (1)Points True False(*)

Correct 6. The TheD DBM BMS_ S_OU OUTP TPUT UT.P .PUT UTp pro roce cedu dure rep pla lace ces ste text xti in na abu buff ffer erb b utdoesnotdisplaythecontentsofthebuffer utdoesnotdisplayth econtentsofthebuffer.TrueorFalse? .TrueorFalse? MarkforReview (1)Points

True(*) False

Correct

Section10 (Answerallquestionsinthissection) 7. E x a m i n e th e f o l l o w i n g p a c k a g e c o d e : CREATEORREPLACEPACKAGEol_packIS PROCEDUREsubprog(p1INVARCHAR2,p2INNUMBER); PROCEDUREsubprog(param1INCHAR,param2INNUMBER); FUNCTIONsubprog(param1INVARCHAR2,para FUNCTIONsubprog( param1INVARCHAR2,param2INNUMBER)RETURNDATE m2INNUMBER)RETURNDATE; ; ENDol_pack; Whichofthefollowingcallswillbesuccessful?(Choosetwo.) MarkforReview (1)Points (Chooseallcorrectanswers) ol_pack.subprog('Jane',30); ol_pack.subprog(param1=>'Jane',param2=>30);(*) v_number:=ol_pack.subprog(p1=>'Jane'); v_date:=ol_pack.subprog('Jane',30);(*)

Incorrect.RefertoSection10Lesson3. 8. Fun Funct ctio ions nsc cal alle led dfr from oma aS SQL QLq que uery ryo or rDM DML Lst stat atem emen ent tmu must st notendthecurrenttransaction,orcreateor notendthecurrentt ransaction,orcreateorrollbacktoasavepoint rollbacktoasavepoint.Trueor .Trueor False? MarkforReview (1)Points True(*) False

Correct

9. Ap Apub ubli lic cfu func ncti tion oni in na apa pack ckag age eis isi inv nvok oked edf fro rom mwi with thin ina a SQLstatement.Thefunction'scodecaninclud SQLstatement.Thefu nction'scodecanincludeaCOMMITstatement.Tru eaCOMMITstatement.TrueorFals eorFals e? M a r k f o r R e v i ew (1)Points True False(*)

Correct 10. 10. Pac Packa kage geH HRP RPAC ACK Kco cont ntai ains nst the hef fol ollo lowi wing ngp pub ubli lic cfu func ncti tion on: : FUNCTIONempfunc(p_deptnoNUMBER)RETURNNUMBERIS BEGIN UPDATEemployeesSETsalary=salary*1.1 WHEREdepartment_id=p_deptno; RETURNSQL%ROWCOUNT; ENDempfunc; WhatwillhappenwhenthefollowingSQLstatementisexecuted? SELECTdepartment_name,hrpack.empfunc(department_id) FROMdepartments; MarkforReview (1)Points TheSELECTwillfailbecauseyoucannotreturnSQL%ROWCOUNTfromapack TheSELECTwillfailbecauseyoucannotreturn SQL%ROWCOUNTfromapacka a gedfunction. TheSELECTwillfailbecauseyoucannotcallp TheSELECTwillfailb ecauseyoucannotcallpackagedfunctionsfromwi ackagedfunctionsfromwit t hinaSQLstatement. TheSELECTwillfailbecauseyoucannotexecuteaDMLstatementfromwi TheSELECTwillfailbecauseyoucannotexecut eaDMLstatementfromwit t hinaquery. TheSELECTwillsucceedbecauseitisreferencingadifferenttablefro TheSELECTwillsucceedbecauseitisreferenc ingadifferenttablefrom m thefunction.(*)

Correct 11. Thef Thefoll ollowi owing ngexa exampl mples eshow howsa sava valid lidre recor cordd ddata ataty type peand andva varia riable ble.T .True rue orFalse? TYPEDeptRecTyp ISRECORD(deptidNUMBER(4)NOTNULL:=99, dnamedepartments.department_name%TYPE, locdepartments.location_id%TYPE,

regionregions%ROWTYPE); dept_recDeptRecTyp; MarkforReview (1)Points True(*) False

Correct 12. 12. In Ina apa pack ckag age, e,p pub ubli lic cco comp mpon onen ents tsa are red dec ecla lare red din int the hes spe pe cifica cificatio tionb nbut utpri privat vatec ecomp ompone onents ntsar aren enot. ot.Tr True ueor orFal False? se? Markf Markfor orRev Review iew (1)Points True(*) False

Correct 13. 13. Whi Which cho of fth the efo foll llow owin ing gwi will lld dis ispl play ayt the hed det etai aile led dco code deo o fthesubprogramsinpackageDEPTPACKinyourschema? fthesubprogramsinpackageDEPTPACKinyour schema? MarkforReview (1)Points SELECTtextFROMUSER_SOURCE WHEREname='DEPTPACK' ANDtype='PACKAGE' ORDERBYline; SELECTtextFROMUSER_SOURCE WHEREname='DEPTPACK' ANDtype='PACKAGEBODY' ORDERBYline; (*)

SELECTtextFROMUSER_SOURCE WHEREobject_name='DEPTPACK' ANDobject_type='PACKAGEBODY' ORDERBYline; SELECTtextFROMUSER_SOURCE WHEREname='DEPTPACK' ANDtype='BODY' ORDERBYline;

Correct 14. 14. You Your rsc sche hema mac con onta tain ins sfo four urp pac acka kage ges, s,e eac ach hha havi ving nga as spe peci ci ficationandabody.Youhavealsobeengrantedprivilegestoaccessth ficationandabody.Youhavealsobeengrante dprivilegestoaccessthreepacka reepacka ges(andtheirbodies)inotherusers'schemas ges(andtheirbodies) inotherusers'schemas.Whatwillbedisplayed .Whatwillbedisplayedbythefo bythefo llowingquery? SELECTCOUNT(*)FROMALL_OBJECTS WHEREobject_typeLIKE'PACK%' ANDowner<>USER; MarkforReview (1)Points 14 7 3 6(*) 0

Correct 15. 15. Pac Packa kage geO OLD LDPA PACK CKi is sin iny you our rsc sche hema ma. .Wh What atw wil ill lha happ ppen enw whe he nthefollowingstatementisexecuted? DROPPACKAGEoldpack; MarkforReview (1)Points Thebodywillbedroppedbutthespecification Thebodywillbedropp edbutthespecificationwillberetained. willberetained. Thespecificationwillbedroppedbutthebody Thespecificationwill bedroppedbutthebodywillberetained. willberetained. Boththespecificationandthebodywillbedropped.(*) Thestatementwillfailbecauseyoumustdrop Thestatementwillfai lbecauseyoumustdropthebodybeforeyoucand thebodybeforeyoucandr r opthespecification.

Correct

16. 16. Exa Exami mine net the hef fol ollo lowi wing ngp pac acka kage ges spe peci cifi fica cati tion on: : CREATEORREPLACEPACKAGEtaxpackIS CURSORempcursISSELECT*FROMemployees; PROCEDUREtaxproc; ENDmypack; ThepackagebodyofTAXPACKalsoincludesafunctioncalledTAXFUNC.Wh ThepackagebodyofTAXPACKalsoincludesafu nctioncalledTAXFUNC.Whichoneo ichoneo fthefollowingstatementsisNOTtrue? MarkforReview (1)Points Theprocedurecanbeinvokedby: BEGIN taxpack.taxproc; END;

Thepackaagewillnotcompilebecauseyoucannotdeclareacursorinth Thepackaagewillnotcompilebecauseyoucann otdeclareacursorinthe e specification. (*)

TAXPROCisapublicprocedureandTAXFUNCisaprivatefunction TAXPROCcaninvokeTAXFUNCifTAXPROCiscodedbeforeTAXFUNC TAXPROCcanopenthecursor

Correct 17. 17. 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p pac acka kage ges sis isN NOT OT true? MarkforReview (1)Points Allproceduresandfunctionsmustbedeclared Allproceduresandfun ctionsmustbedeclaredinthespecification.(*) inthespecification.(*) Cursorscanbedeclaredinthespecification. Thebodycontainsthedetailedcodeofthesubprograms. Variablescanbedeclaredinthebody. Thespecificationmustbecreatedbeforethebody.

Correct 18. 18. Eve Every rys sub ubpr prog ogra ram mwh whic ich hha has sbe been end dec ecla lare red din ina ap pac acka kage ges s pecificationmustalsobeincludedinthepackagebody.TriueorFalse? pecificationmustalsobeincludedinthepack agebody.TriueorFalse? Markfor Review (1)Points True(*) False

Correct 19. 19. Pac Packa kage geM MYP YPAC ACK Kco cont ntai ains nsp pro roce cedu dure reM MYP YPRO ROC. C.Y You ouc can ans see eew w hichparametersMYPROCusesbyexecuting:DESCRIBEmypack.myproc.True hichparametersMYPROCusesbyexecuting:DESC RIBEmypack.myproc.TrueorFalse? orFalse? MarkforReview (1)Points True False(*)

Correct 20. 20. Wha What tis isw wro rong ngw wit ith hth the efo foll llow owin ing gsy synt ntax axf for orc cre reat atin ing ga a packagespecification? CREATEORREPLACEPACKAGEmypackIS g_constant1NUMBER(6):=100; FUNCTIONfunc1(p_param1INVARCHAR2); FUNCTIONfunc2; ENDmypack; MarkforReview (1)Points Youcannotdeclareconstantsinthespecification. Apackagemustcontainatleastoneprocedure. TheRETURNdatatypeofthefunctionsmustbespecified. (*)

Thefirstlineshouldbe: CREATEORREPLACEPACKAGESPECIFICATIONmypackIS

Nothingiswrong,thiscodecontainsnoerrors.

Correct 21. Thef Thefoll ollowi owing ngpro proced cedure uread adds dsac acolu olumn mnof ofdat dataty atype peDAT DATEt Etot othe heEMP EMPLOY LOYEES EES table.Thenameofthenewcolumnispassedt table.Thenameofth enewcolumnispassedtotheprocedureasapara otheprocedureasaparameter. meter. CREATEORREPLACEPROCEDUREaddcol (p_col_nameINVARCHAR2)IS v_first_stringVARCHAR2(100):='ALTERTAB v_first_stringVAR CHAR2(100):='ALTERTABLEEMPLOYEESADD('; LEEMPLOYEESADD('; v_second_stringVARCHAR2(6):='DATE)'; BEGIN ...LineA END; Whichofthefollowingwillworkcorrectlywhe Whichofthefollowing willworkcorrectlywhencodedatlineA?(Choos ncodedatlineA?(Choosetwo.) etwo.) MarkforReview (1)Points (Chooseallcorrectanswers) v_first_string||p_col_name||v_second_string; EXECUTEIMMEDIATEv_first_string||p_col_name EXECUTEIMMEDIATEv_fi rst_string||p_col_name||v_second_string; ||v_second_string; (*)

EXECUTEIMMEDIATE'v_first_string'||p_col_na EXECUTEIMMEDIATE'v_f irst_string'||p_col_name||'v_second_string'; me||'v_second_string'; v_first_string:=v_first_string||p_col_name; EXECUTEIMMEDIATEv_first_string||v_second_string; (*)

EXECUTEv_first_string||p_col_name||v_second_string;

Correct 22. 22. You Youw wan ant tto toc cre reat ate ea afu func ncti tion onw whi hich chd dro rops psa at tab able le. .Yo You u writethefollowingcode: CREATEORREPLACEFUNCTIONdroptab (p_tab_nameINVARCHAR2) RETURNBOOLEANIS BEGIN DROPTABLEp_tab_name; RETURNTRUE; EXCEPTION WHENOTHERSTHENRETURNFALSE;

END; Whywillthisprocedurenotcompilesuccessfully? MarkforReview (1)Points Becauseyoucanneverdropatablefrominsideafunction BecausethePL/SQLcompilercannotcheckiftheargumentofp_tab_name BecausethePL/SQLcompilercannotcheckifth eargumentofp_tab_namei i savalidtable-name(*) Becauseyoudonothavetheprivilegeneededtodropatable BecauseyoucannotuseRETURNintheexceptionsection

Correct 23. 23. Whi Which cho of fth the efo foll llow owin ing gSQ SQL Lst stat atem emen ents tsc can anb be ein incl clud uded edi i naPL naPL/SQ /SQLb Lbloc locko konly nlyby byus using ingDy Dynam namic icSQL SQL?( ?(Cho Choose osetw two.) o.) Markf Markfor orRev Review iew (1)Points (Chooseallcorrectanswers) DELETE SAVEPOINT ALTER(*) SELECT.....FORUPDATENOWAIT GRANT(*)

Correct 24 .

Na m e t w o r e a s o n s f o r u s i n g D yn a m i c S QL .

M a rk f or

Review (1)Points (Chooseallcorrectanswers) ProvidetheabilitytoexecuteSQLstatementswhosestructureisunknow ProvidetheabilitytoexecuteSQLstatements whosestructureisunknown n untilexecutiontime.(*)

Providetheabilitytohandlemutatingrowswhenexecutingastatement Providetheabilitytohandlemutatingrowswh enexecutingastatementi i nvolvingthesametable. AllowfetchofdataforDMLstatements. Enablessession-controlstatementstobewritt Enablessession-contro lstatementstobewrittenandexecutedfromPL/S enandexecutedfromPL/SQ Q L.(*)

Correct 25. 25. Wha What tdo does est the heR RET ETUR URNI NING NGc cla laus use edo doi in nth the eex exam ampl ple ebe belo low? w? CREATEORREPLACEPROCEDUREnew_dept (p_dept_nameINdepartments.name%TYPE)IS v_new_dept_iddepartments.dept_id%TYPE; BEGIN INSERTINTOdepartments(dept_id,name) VALUESdept_seq.NEXTVAL,p_dept_name RETURNINGdept_seq.CURRVALINTOv_new_dept_id; DBMS_OUTPUT.PUT_LINE(p_dept_name||'isdepa DBMS_OUTPUT.PUT_LINE (p_dept_name||'isdepartmentnumber'||v_new_ rtmentnumber'||v_new_dept_id); dept_id); ENDnew_dept; MarkforReview (1)Points Insertsthenewdepartmentidinthedepartmenttable. PerformstheSELECTstatementtodeterminethedepartmentidofthenew PerformstheSELECTstatementtodeterminethe departmentidofthenew department.(*) Usesthenewdepartmentnumberinacursor.

Incorrect.RefertoSection12Lesson2. 26. 26.

Wha What tis ist the hec cor orre rect cts syn ynta tax xto tou use set the heR RET ETUR URNI NING NGp phr hras ase e

atPositionA? DECLARE TYPEEmpRecISRECORD(last_nameemployees.las TYPEEmpRecISRECORD (last_nameemployees.last_name%TYPE,salaryemplo t_name%TYPE,salaryemployees.sala yees.sala ry%TYPE); emp_infoEmpRec; emp_idNUMBER:=100; BEGIN UPDATEemployeesSETsalary=salary*1.1W UPDATEemployeesSET salary=salary*1.1WHEREemployee_id=emp_id HEREemployee_id=emp_id--Posit --Posit ionA dbms_output.put_line('Justgavearaiseto' dbms_output.put_line ('Justgavearaiseto'||emp_info.last_name|| ||emp_info.last_name||',whon ',whon owmakes'||emp_info.salary); END; MarkforReview

(1)Points RETURNINGFROMemp_info; last_name,salaryRETURNINGINTOemp_info; RETURNINGlast_name,salaryINTOemp_info;(*) RETURNINGlast_name,salaryTOemp_info;

Incorrect.RefertoSection12Lesson2. 27. 27. Det Deter ermi mini nist stic icm mea eans nst the hef fun unct ctio ion nwi will lla alw lway ays sre retu turn rnt the he sameoutputreturnvalueforanygivensetof sameoutputreturnva lueforanygivensetofinputargumentvalues.T inputargumentvalues.TrueorFa rueorFa lse? lse? Mark Markf for orR Rev evie iew w (1)Points True(*) False

Incorrect.RefertoSection12Lesson2. 28. 28. You Youw wan ant tto tot tak ake ema make kea ac cop opy yof ofa all llt the hec cit itie ies sin int the hew w orldlistedinthecitiestable,whichcontain orldlistedinthecit iestable,whichcontainsmillionsofrows.Thef smillionsofrows.Thefollowing ollowing procedureaccomplishthisefficiently.TrueorFalse? CREATEORREPLACEPROCEDUREcopy_citiesIS TYPEt_citiesISTABLEOFcities%ROWTYPEIND TYPEt_citiesISTAB LEOFcities%ROWTYPEINDEXBYBINARY_INTEGER; EXBYBINARY_INTEGER; v_citiestabt_emp; BEGIN SELECT*BULKCOLLECTINTOv_citiestabFROMcities; FORALLiINv_citiestab.FIRST..v_citiestab.LAST INSERTINTOnew_citiesVALUESv_citiestab(i); ENDcopy_cities; MarkforReview (1)Points True(*) False

Correct

Section13 (Answerallquestionsinthissection) 29. 29. You Youc can anu use sea at tri rigg gger ert to opr prev even ent tro rows wsf fro rom mbe bein ing gde dele lete te dfromthe dfromtheEMPL EMPLOYEES OYEEStabl tableon eonMonda Mondays.T ys.Trueo rueorFal rFalse? se? MarkforR MarkforReview eview (1)Points True(*) False

Correct 30. 30. Whi Which cho of fth the efo foll llow owin ing gbe best std des escr crib ibes esa ad dat atab abas ase etr trig igge ge r? M a r k f o r R e v i ew (1)Points Asubprogramthatcheckswhetherauserhastypedthecorrectpassword Asubprogramthatcheckswhetherauserhasty pedthecorrectpasswordt t ologontothedatabase. APL/SQLsubprogramthatexecutesautomaticallywheneveranassociated APL/SQLsubprogramthatexecutesautomaticall ywheneveranassociatedd d atabaseeventoccurs.(*) APL/SQLsubprogramthatalwaysreturnsexactlyonevalue. Asubprogramthatisinvokedexplicitlybythe Asubprogramthatisi nvokedexplicitlybythecallingapplication. callingapplication. APL/SQLsubprogramthatinsertsrowsintoaloggingtable.

Correct 31. What Whattyp typeo eofd fdata atabas baseo eobje bject ctwou would ldyou youcr creat eatet etow owrit ritea eana naudi uditin tingr greco eco rdautomat rdautomaticall icallyeve yeveryti rytimea meauser userconne connectst ctstothe othedata database? base? MarkforR MarkforReview eview (1)Points Aprocedure Acomplexview Atrigger(*) Afunction

Apackage

Correct 32. 32. At Atri rigg gger erc can anb be ecr crea eate ted din int the hed dat atab abas ase eor orw wit ithi hin nan ana a ppli pplica cati tion on. .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 33. 33. Whi Which cho of fth the efo foll llow owin ing gar are ego good odg gui uide deli line nes sto tof fol ollo low wwh wh encreatin encreatingad gadataba atabasetr setrigger igger?(Ch ?(Choose oosetwo.) two.) MarkforR MarkforReview eview (1)Points (Chooseallcorrectanswers) Wherepossible,useatriggertoenforceafor Wherepossible,usea triggertoenforceaforeignkeyconstraint. eignkeyconstraint. Usetriggerstooverrideprivilegecheckingan Usetriggerstooverri deprivilegecheckingandviewotherusers'priva dviewotherusers'privat t etables. DonotuseatriggertoreplaceorduplicatesomethingwhichtheOracle Donotuseatriggertoreplaceorduplicates omethingwhichtheOracle Serverdoesautomatically.(*) Usetriggerstopreventunauthorizedusersfro Usetriggerstopreven tunauthorizedusersfromSELECTingconfidential mSELECTingconfidentiald d ata. Donotcreateatriggerthatautomaticallyfir Donotcreateatrigge rthatautomaticallyfiresanothertrigger.(*) esanothertrigger.(*)

Correct 34. 34. rbody.Tr rbody.Trueor ueorFals False? e? (1)Points True

You Youc can anc cod ode eCO COMM MMIT ITa and ndR ROL OLLB LBAC ACK Kst stat atem emen ents tsi in na atr trig igge ge MarkforR MarkforReview eview

False(*)

Correct

ge r b o d y ? (1)Points

35. 35. Whi Which chd dic icti tion onar ary yvi view ews sho hows wst the hed det etai aile led dco code deo of fa atr trig ig Ma r k fo r Re v i ew

USER_SOURCE USER_TRIGGERS(*) USER_OBJECTS USER_DML_TRIGGERS USER_SUBPROGRAMS

Correct 36. 36.

Use User rAY AYSE SEGU GUL Lsu succ cces essf sful ully lyc cre reat ates est the hef fol ollo lowi wing ngt tri rigg gger er

: CREATETRIGGERloc_trigg BEFOREUPDATEONaysegul.locations BEGIN.... AYSEGULnowtriestodroptheLOCATIONStable.Whathappens? MarkforReview (1)Points Anerrormessageisdisplayedbecauseyoucannotdropatablethatisa Anerrormessageisdisplayedbecauseyoucann otdropatablethatisas s sociatedwithatrigger. Thetableisdroppedandthetriggerisdisabled. Thetriggerisdroppedbutthetableisnotdropped. Boththetableandthetriggeraredropped.(*) Noneoftheabove.

Correct 37. 37. Whi Which cho of fth the efo foll llow owin ing gwi will llr rem emov ove ea atr trig igge ger rin iny you our rsc sc hemanamedEMP_TRIGGfromthedatabase? MarkforReview hemanamedEMP_TRIGGfromthedatabase? (1)Points DROPemp_triggTRIGGER; ALTERTRIGGERemp_triggDISABLE; DROPTRIGGERemp_trigg;(*) REMOVETRIGGERemp_trigg; Noneoftheabove

Correct 38. 38. Atr Atrig igge ger rau auto toma mati tica call lly yin inse sert rts sa aro row win into toa al log oggi ging ngt tab ab leeverytimeauser'ssessionreceivesthiserrormessage: ORA-00942:tableorviewdoesnotexist Whatkindoftriggeristhis? MarkforReview (1)Points Arowtrigger Astatementtrigger Adatabaseeventtrigger(*) ADDLtrigger AnAFTERtrigger

Correct 39 . E x a m i n e th i s c o d e : CREATETRIGGERde_trigg --LineA

BEGIN... WhichofthefollowingareNOTvalidatLineA?(Choosetwo.) MarkforReview (1)Points (Chooseallcorrectanswers) AFTERLOGOFFONSCHEMA(*) AFTERLOGONONSCHEMA BEFORELOGOFFONSCHEMA BEFOREDISCONNECTONSCHEMA(*) AFTERSERVERERRORONSCHEMA

Correct 40 . E x a m i n e th i s c o d e : CREATETRIGGERnew_trigg AFTERCREATEONreserved_word BEGIN... Whichofthefollowingcanbeusedinplaceof Whichofthefollowing canbeusedinplaceofreserved_word?(Chooset reserved_word?(Choosetwo.) wo.) MarkforReview (1)Points (Chooseallcorrectanswers) TABLE SCHEMA(*) USER DATABASE(*) TABLEemployees

Correct

41. Exam Examin ine eth the ef follo ollowi wing ngc cod ode: e: CREATETRIGGERemp_trigg AFTERUPDATEOFsalaryONemployees FOREACHROW DECLARE v_countNUMBER; BEGIN --LineA END; WhichofthefollowingstatementsisNOTallowedatLineA? MarkforReview (1)Points SELECTcount(*)INTOv_countFROMdepartments; UPDATEemployeesSETjob_id='IT_PROG'WHERE UPDATEemployeesSETj ob_id='IT_PROG'WHEREemployee_id=:OLD.employ employee_id=:OLD.employe e e_id; SELECTcount(*)INTOv_countFROMemployees;(*) DBMS_OUTPUT.PUT_LINE('Asalarywasupdated'); None.Alloftheaboveareallowed.

Correct 42. 42. 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? ? CREATETRIGGERdept_trigg BEFOREUPDATEOFdepartment_nameONdepartments BEGIN DBMS_OUTPUT.PUT_LINE(:NEW.department_name); END; MarkforReview (1)Points Youcannotuse:NEWinaBEFOREtrigger,only Youcannotuse:NEWin aBEFOREtrigger,onlyinanAFTERtrigger. inanAFTERtrigger. Youcannotuse:NEWor:OLDinastatementtrigger.(*) YoucannotuseDBMS_OUTPUT.PUT_LINEinsideatrigger. Thesecondlineshouldbe: BEFOREUPDATEONdepartments.department_name

Correct 43. 43. You Youn nee eed dto toc cre reat ate ea atr trig igge ger rth that atw wil ill lfi fire rew whe hene neve ver ran an employee'ssalaryorjob_idisupdated,butn employee'ssalaryor job_idisupdated,butnotwhenanyothercolumn otwhenanyothercolumnoftheEM oftheEM PLOYEEStableisupdated.Whichofthefollowi PLOYEEStableisupdat ed.Whichofthefollowingisthecorrectsyntax ngisthecorrectsyntaxtodothi todothi s? Ma r k f o r R e v i ew (1)Points CREATETRIGGERemp_upd_trigg AFTERUPDATEONemployees(salary,job_id) BEGIN... CREATETRIGGERemp_upd_trigg AFTERUPDATEOFsalary,job_idONemployees BEGIN... (*)

CREATETRIGGERemp_upd_trigg AFTERUPDATEOF(salary,job_id)ONemployees BEGIN... CREATETRIGGERemp_upd_trigg AFTERUPDATEOFsalaryORjob_idONemployees BEGIN...

Correct 44. 44. The There rea are ref fiv ive eem empl ploy oyee ees sin ind dep epar artm tmen ent t50 50. .Th The efo foll llow owin in gtriggeriscreated: CREATETRIGGERupd_emp AFTERUPDATEONemployees BEGIN INSERTINTOaudit_tableVALUES(USER,SYSDATE); END; Ausernowexecutes: UPDATEemployeesSETsalary=salary*1.1 WHEREdepartment_id=50; Howmanyrowswillbeinsertedintoaudit_table? MarkforReview (1)Points One(*) Two

Five Six Noneoftheabove

Correct 45. 45. AD ADML MLs sta tate teme ment ntt tri rigg gger erf fir ires eso onl nly yon once cef for ore eac ach htr trig igge ge ringDMLstatement,whilearowtriggerfiresonceforeachrowprocess ringDMLstatement,whilearowtriggerfires onceforeachrowprocessedbythe edbythe triggerin triggeringsta gstatemen tement.Tr t.Trueor ueorFals False? e? MarkforR MarkforReview eview (1)Points True(*) False

Correct 46. 46. Exa Exami mine net the hef fol ollo lowi wing ngc cod ode. e.T To ocr crea eate tea ar row owt tri rigg gger er, ,wh wh atcodeshouldbeincludedatLineA? CREATETRIGGERdept_trigg AFTERUPDATEORDELETEONdepartments --LineA BEGIN... MarkforReview (1)Points AFTEREACHROW FOREVERYROW FOREACHROW(*) ONEACHROW ONEVERYROW

Correct 47. 47.

Exa Exami mine net the hef fol ollo lowi wing ngt tri rigg gger er. .It Its sho houl uld dra rais ise ean ana app ppli li

cationerrorifausertriestoupdateanemployee'slastname.Itshou cationerrorifausertriestoupdateanempl oyee'slastname.Itshouldallow ldallow updatestoallothercolumnsoftheEMPLOYEES updatestoallotherc olumnsoftheEMPLOYEEStable.Whatshouldbecod table.Whatshouldbecodedatlin edatlin eA? CREATETRIGGERstop_ln_trigg BEFOREUPDATEONemployees BEGIN --LineA RAISE_APPLICATION_ERROR(-20201,'Updatingl RAISE_APPLICATION_ ERROR(-20201,'Updatinglastnamenotallowed'); astnamenotallowed'); ENDIF; END; MarkforReview (1)Points IFUPDATINGLAST_NAMETHEN IFUPDATING('LAST_NAME')THEN(*) IFUPDATE('LAST_NAME')THEN IFUPDATINGTHEN

Correct 48. 48.

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e exa xamp mple lef for ora ac com om

poundtrigger? CREATEORREPLACETRIGGERlog_emps FORUPDATEOFsalaryONemployees COMPOUNDTRIGGER TYPEt_log_empISTABLEOFlog_table%ROWTYPE INDEXBYBINARY_INTEGER; log_emp_tabt_log_emp; AFTEREACHROWIS BEGIN --someaction ENDAFTEREACHROW; AFTERSTATEMENTIS BEGIN --someaction ENDAFTERSTATEMENT; ENDlog_emps; MarkforReview (1)Points Theorderofthetimingstatementsisreversed.(*) ThedeclarationsectionismissingtheDECLAREkeyword. ThetriggeringeventFORUPDATEisnotallowed.

TheCOMPOUNDTRIGGERstatementismissingIS. Thereisnothingwrongwiththisexample.

Incorrect.RefertoSection13Lesson3. 49. 49. INS INSTE TEAD ADO OF Ftr trig igge gers rsa are rea alw lway ays sro row wtr trig igge gers rs, ,ev even eni if fFO FO REACHROW REACHROWiso isomitte mitted.Tr d.Trueor ueorFals False? e? MarkforR MarkforReview eview (1)Points True(*) False

Correct 50 . I n t he f ol l o wi n g c o d e: CREATETRIGGERmytrigg INSTEADOFINSERTORUPDATEONmy_object_name FOREACHROW BEGIN... my_object_namecanbethenameofatable.TrueorFalse? MarkforReview (1)Points True False(*)

C o r r ec t