OracleOCP认证考试练习真题OCP071

试卷科目：OracleOCP认证考试练习OracleOCP认证考试练习真题OCP071PAGE"pagenumber"pagenumber/SECTIONPAGES"numberofpages"numberofpagesOracleOCP认证考试练习真题OCP071第1部分：单项选择题，共79题，每题只有一个正确答案,多选或少选均不得分。[单选题]1.5.ChoosethebestanswerTheCUSTOMERStablehasaCUST_LAST_NAMEcolumnofdatatypeVARCHAR2.ThetablehastworowswhoseCOST_LAST_MANEvaluesareAndersonandAusson.WhichqueryproducesoutputforCUST_LAST_SAMEcontainingOderforthefirstrowandAusforthesecond?A)SELECTREPLACE(REPLACE(cust_last_name,'son',''),'An','O')FROMcustomers;B)SELECTREPLACE(TRIM(TRALING'son'FROMcust_last_name),'An','O')FROMcustomers;C)SELECTINITCAP(REPLACE(TRIM('son'FROMcust_last_name),'An','O'))FROMcustomers;D)SELECTREPLACE(SUBSTR(cust_last_name,-3),'An','O')FROMcustomers;答案:A解析:[单选题]2.8.ChoosethebestanswerTheEMPLOYEEStablecontainscolumnsEMP_IDofdatatypeNUMBERandHIRE_DATEofdatatypeDATEYouwanttodisplaythedateofthefirstMondayafterthecompletionofsixmonthssincehiringTheNLS_TERRITORYparameterissettoAMERICAinthesessionand,therefore,SundayisthefirstdayoftheweekWhichquerycanbeused?A)SELECTemp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY')FROMemployees;B)SELECTemp_id,ADD_MONTHS(hire_date,6),NEXT_DAY('MONDAY')FROMemployees;C)SELECTemp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6)FROMemployees;D)SELECTemp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1)FROMemployees;答案:A解析:[单选题]3.37、ChoosethebestanswerExaminethedescriptionofthePRODUCT_INFORMATIONtable:NameNULL?TypePROD_IDNOTNULLNUMBER(2)PROD_NANEVARCRAR2(10)LIST_PRICENUMBER(6,2)Whichqueryretrievesthenumberofproductswithanulllistprice?A)SELECT(COUNT(list_price)FROMProduct_intormationWHERElist_price=NULL;B)SELECTcount(nvl(list_price,0))FROMproduct_informationWHERElist_priceisnull;C)SELECTCOUNT(DISTINCTlist_price)FROMproduct_informationWHERElist_priceisnull.D)SELECTCOUNT(list_price)FROMproduct_informationwherelist_priceisNULL;答案:B解析:[单选题]4.49、Whatistrueaboutnon-equijoinstatementperformance?A)Thebetweenconditionalwaysperformslesswellthanusingthe>=and<=conditionsB)TheOraclejoinsyntaxperformsbetterthantheSQL:1999compliantANSIjoinsyntax.C)Thejoinsyntaxusedmakesnodifferencetoperformance.D)Thebetweenconditionalwaysperformsbetterthanusingthe>=and<=conditions.E)Tablealiasescanimproveperformance.答案:E解析:[单选题]5.54、Choosethebestanswer.Examinethisquery:SELECT2FROMduald1CROSSJOINduald2CROSSJOINduald3;Whatisreturneduponexecution?A)0rowsB)anerrorC)8rowsD)6rowsE)1rowF)3rows答案:E解析:[单选题]6.56、ChoosethebestanswerExaminethedescriptionoftheCUSTOMERStable:NameNull?TypeCUST_IDNotNULLVARCHAR2(6)FIRST_NAMEVARCHAR2(50)LAST_NAMENotNULLVARCHAR2(50)ADDRRESSVARCHAR2(50)CITYVARCHAR2(25)YouwanttodisplaydetailsofallcustomerswhoresideincitiesstartingwiththeletterDfollowedbyatleasttwocharacterWhichquerycanbeused?A)SELECT*FROMcustomersWHEREcity='D_%';B)SELECT*FROMcustomersWHEREcity='%D_';C)SELECT*FROMcustomersWHEREcityLIKE'D%';D)SELECT*FROMcustomersWHEREcityLIKE'D_';答案:C解析:[单选题]7.57、ExaminethedescriptionortheCUSTOMERStable:NameNull?TypeCUST_IDNOTNULLNUMBERCUST_FIRST_NAMNOTNULLVARCHAR2(20)CUST_LAST_NAMENOTNULLVARCHAR2(30)CUST_INCOME_LEVELVARCHAR2(30)CUST_CREDIT_LIMITNUMBERForCustomerswhoseincomelevelhasavalue,youwanttodisplaythefirstnameanddueamountas5%oftheircreditlimit.Customerswhosedueamountisnullshouldnotbedisplayed.Whichqueryshouldbeused?A)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMOUNTFROMcustomersWHEREcust_income_level!=NULLANDcust_credit_level!=NULL;B)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMONTFROMcustomersWHEREcust_income_level<>NULLANDdue_amount<>NULL;C)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMONTFROMcustomersWHEREcust_income_levelISNOTNULLANDcust_credit_limitISNOTNULL;D)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMONTFROMcustomersWHEREcust_income_levelISNOTNULLANDdue_amountISNOTNULL;E)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMONTFROMcustomersWHEREcust_income_level!=NULLANDdue_amount!=NULL;答案:C解析:[单选题]8.59、YouwanttowriteaquerythatpromptsfortwocolumnnamesandtheWHEREconditioneachtimeItisexecutedinasessionbutonlypromptsforthetablenamethefirsttimeitisexecuted.Thevariablesusedinyourqueryareneverundefinedinyoursession.Whichquerycanbeused?A)SELECT&col1,&col2FROM&&tableWHERE&condition;B)SELECT&col1,&col2FROM?&table?WHERE&condition;C)SELECT&&col1,&&col2FROM&tableWHERE&&condition=&&cond;D)SELECT?&co11?,?&&co12?FROM&tableWHERE?&&condition?=?&cond?;E)SELECT&&col1,&&col2FROM&tableWHERE&&condition;答案:A解析:[单选题]9.66、ExaminethedescriptionoftheEMPLOYEEStable:NameNullTypeEMP_IDNOTNULLNUMBEREMP_NAMEVARCHAR2(10)DEPT_IDNUMBER(2)SALARYNUMBER(8,2)JOIN_DATEDATENLS_DATE_FORMATissettoDD-MON-YY.Whichqueryrequiresexplicitdatatypeconversion?A)SELECTsalary+120.50FROMemployees;B)SELECTSUBSTR(joindate,1,2)-10FROMemployees;C)SELECTjoindate11.?11salaryFROMemployees；D)SELECTjoindateFROMemployeeswherejoindate>*10-02-2018*;E)SELECTjoindate+20FROMemployees;答案:D解析:[单选题]10.88、ExaminethedescriptionoftheEMPLOYEEStable:NameNull?TypeEMP_IDNOTNULLNUMBEREMPNAMEVARCHAR2(40)DEPT_IDNUMBER(2)SALARYNUNGER(B,2)JOIN_DATEDATEWhichqueryisvalid?A)SELECTdept_id,joindate,SUM(salary)FROMemployeesGROUPBYdept_id,join_date;B)SELECTdept_id,MAX(AVG(salary))FROMemployeesGROUPBYdept_id;C)SELECTdept_id,AVG(NAX(salary))FROMemployeesGROUPBYdept_id;D)SELECTdept_id,join_date,SUM(salary)FROMemployeesGROUPBYdept_id;答案:A解析:[单选题]11.91、ExaminethedescriptionoftheSALES1table:NameNullTypeSALES_IDNOTNULLNUMBERSTORE_IDNOTNULLNUMBERITEMS_IDNUMBERQUANTITYNUMBERSALES_DATEDATESALES2isatablewiththesamedescriptionasSALES1,SomesalesdataisduplicatedInbothtables.YouwanttodisplaytherowsfromtheSALES1tablewhicharenotpresentintheSALIES2table.WhichsetoperatorgeneratestherequiredoutputA)SUBTRACTB)INTERSECTC)UNIONALLD)MINUSE)UNION答案:D解析:[单选题]12.117、Whichstatementistrueaboutaggregatefunctions?A)TheAVGfunctionimplicitlyconvertsNULLStozero.B)TheMAXandMINfunctionscanbeusedoncolumnswithcharacterdatatypesC)AggregatefunctionscanbeusedinanyclauseofaSELECTstatementD)Aggregatefunctionscanbenestedtoanynumberoflevels答案:B解析:[单选题]13.122、WhichstatementistrueaboutTRUNCATEandDELETE?A)ForlargetablesTRUNCATEisfasterthanDELETE.B)FortableswithmultipleindexesandtriggersisfasterthanTRUNCATE.C)YoucanneverTRUNCATEatableifforeignkeyconstraintswillbeviolated.D)Youcannevertowsfromatableifforeignkeyconstraintswillbeviolated.答案:A解析:[单选题]14.123、ExaminethedescriptionortheBOOKS_TRANSACTIONStable:NameNull?TypeCUST_IDNOTNullNUMBERCUST_FIRST_NAMENOTNullVARCHAR2(20)CUST_LAST_NAMEVARCHAR2(30)CUST_CREDIT_LIMITNUMBERFORcustomerswhoseincomelevelhasavalue,youwanttodisplaythefirstnameanddueamountas5%oftheircreditlimit.Customerswhosedueamountisnullshouldnotbedisplayed.Whichqueryshouldbeused?A)SELECTcust_first_name,cust_credit_limit*.05ASDUEAMOUNTFROMcustomersWHEREcustincome_level!=NULLANDcustcredit_level!=NULL;B)SELECTcust_first_name,cust_credit_limit*.05ASDUEAMOUNTFROMcustomersWHEREcustincome_levelISNOTNULLANDdue_amountISNOTNULL;C)SELECTcust_first_name,cust_credit_limit*.05ASDUEAMOUNTFROMcustomersWHEREcustincome_level<>NULLANDdue_amount<>NULL;D)SELECTcust_first_name,cust_credit_limit*.05ASDUEAMOUNTFROMcustomersWHEREcust_income_levelISNOTNULLANDcust_credit_limitISNOTNULL;E)SELECTcust_first_name,cust_credit_limit*.05ASDUEAMOUNTFROMcustomersWHEREcustincome_level!=NULLANDdue_amount!=NULL;答案:D解析:[单选题]15.124、ViewtheExhibitsandexaminethestructureoftheCOSTSandPROMOTIONStables.YouwanttodisplayPRODIDSwhosepromotioncostislessthanthehighestcostPRODIDinapromotiontimeinterval.ExaminethisSQLstatement:SELECTprodidFROMcostsWHEREpromoidIN(SELECTpromoidFROMpromotionsWHEREpromo_cost<ALL(SELECTMAX(promocost)FROMpromotionsGROUPBY(promo_enddate-promo_begin_date)));Whatwillbetheresult?A)Itexecutessuccessfullybutdoesnotgivetherequiredresult.B)ItgivesanerrorbecausetheALLkeywordisnotvalidC)ItgivesanerrorbecausetheGROUPBYclauseisnotvalidD)Itexecutessuccessfullyandgivestherequiredresult.答案:A解析:[单选题]16.139、WhichisthedefaultcolumnorcolumnsforsortingoutputfromcompoundqueriesusingSEToperatorssuchasINTERSECTinaSQLstatement?A)ThefirstcolumninthelastSELECTofthecompoundqueryB)ThefirstNUMBERcolumninthefirstSELECTofthecompoundqueryC)ThefirstVARCHAR2columninthefirstSELECTofthecompoundqueryD)ThefirstcolumninthefirstSELECTofthecompoundqueryE)ThefirstNUMBERorVARCHAR2columninthelastSELECTofthecompoundquery答案:D解析:[单选题]17.141、Youexecutethisquery:SELECTTOCHAR(NEXT_DAY(LAST_DAY(SYSDATE),?MON?),?dd?Mondayfor?fmMonthrrr?)FROMDUAL;Whatistheresult?A)Itexecutessuccessfullybutdoesnotreturnanyresult.B)ItreturnsthedateforthefirstMondayofthenextmonth.C)Itgeneratesanerror.D)ItreturnsthedateforthelastMondayofthecurrentmonth.答案:B解析:[单选题]18.142、WhichstatementistrueabouttheINTERSECToperatorusedincompoundqueries?A)ItprocessesNULLSintheselectedcolumns.B)INTERSECTisoflowerprecedencethanUNIONorUNIONALL.C)ItignoresNULLS.D)MultipleINTERSECToperatorsarenotpossibleinthesameSQLstatement答案:A解析:[单选题]19.145、WhichstatementistrueregardingtheSESSION_PRIVSdictionaryview?A)Itcontainstheobjectprivilegesgrantedtootherusersbythecurrentusersession.B)ItcontainsthesystemprivilegesgrantedtootherusersbythecurrentUsersession.C)Itcontainsthecurrentsystemprivilegesavailableintheusersession.D)Itcontainsthecurrentobjectprivilegesavailableintheusersession.答案:C解析:[单选题]20.146、ViewtheExhibitandexaminethestructureoftheSALES,CUSTOMERS,PRODUCTS,andTIMEStables.ThePROD_IDcolumnistheforeignkeyintheSALEStable.WhichreferencesthePRODUCTStable.Similarly,theCUST_IDandTIME_IDcolumnsareAlsoforeignkeysintheSALEStablereferencingtheCUSTOMERSandTIMEStables,respectively.EvaluatethefollowingCREATETABLEcommand:CREATETABLEnew_sales(prod_id,Icust_id,order_dateDEFAULTSYSDATE)ASSELECTIprod_id,cust_id,time_idFROMsales.Whichstatementistrueregardingtheabovecommand?A)TheNEW_SALEStablewouldnotgetcreatedbecausetheDEFAULTvaluecannotbespecifiedinthecolumndefinition.B)TheNEW_SALEStablewouldgetcreatedandalltheNOTNULLconstraintsdefinedonthespecifiedcolumnswouldbepassedtothenewtable.C)TheNEW_SALEStablewouldnotgetcreatedbecausethecolumnnamesintheCREATETABLEcommandandtheSELECTclauseIdonotmatch.D)TheNEW_SALEStablewouldgetcreatedandalltheFOREIGNKEYconstraintsdefinedonthespecifiedcolumnswouldbepassedtothenewtable答案:B解析:[单选题]21.148、Youexecutethesecommands:SQL>DEFINEhiredate=?01-APR-2011?;SQL>SELECTemployee_id,first_name,salaryFROMemployeesWHEREhiredate>&hiredateANDmanager_id>&mgr_id;Forwhichsubstitutionvariableswillyoubeprompted?A)noneB)&hiredateand&mgr_idC)only&hiredate.D)only&mgr_id.答案:D解析:[单选题]22.149、EvaluatethefollowingSQLstatementSQL>SELECTpromo_id,prom_categoryFROMpromotionsWHEREpromo_category=?Internet?ORDERBYpromo_idUNIONSELECTpromo_id,promo_categoryFROMPomotionsWHEREpromo_category=?TV?UNIONSELECTpromoid,promocategoryFROMpromotionsWHEREpromocategory=?Radio?Whichstatementistrueregardingtheoutcomeoftheabovequery?A)ItexecutessuccessfullyanddisplaysrowsinthedescendignoreofPROMOCATEGORYB)Itproducesanerrorbecausepositional,notationcannotbeusedintheORDERBYclausewithSBToperators.C)ItexecutessuccessfullybutignorestheORDERBYclausebecauseitisnotlocatedattheendofthecompoundstatement.D)ItproducesanerrorbecausetheORDERBYclauseshouldappearonlyattheendofacompoundquery-thatis,withthelastSELECTstatement.答案:D解析:[单选题]23.150、ExaminethecommandtocreatetheBOOKStable.SQL>createtablebooks(bookidCHAR(6)PRIMARYKEY,titleVARCHAR2(100)NOTNULL,publisher_idVARCHAR2(4)，author_idVARCHAR2(50));TheBOOKIDvalue101doesnotexistinthetable.ExaminetheSQLstatement.insertintobooks(bookidtitle,author_idvalues(?101?，?LEARNINGSQL?,?TimJones?)A)ItexecutessuccessfullyandtherowisinsertedwithanullPLBLISHER_IDB)ItexecutessuccessfullyonlyifNULLisexplicitlyspecifiedintheINSERTstatement.C)ItexecutessuccessfullyonlyNULLPUBLISHER_IDcolumnnameisaddedtothecolumnslistintheINSERTstatement.D)ItexecutessuccessfullyonlyifNULLPUBLISHERIDcolumnnameisaddedtothecolumnslistandNULLisexplicitlyspecifiedIntheINSERTstatement.答案:A解析:[单选题]24.153、TheSTOREStablehasacolumnSTART_DATEofdatatypeDATE,containingthedatetherowwasinserted.YouonlywanttodisplaydetailsofrowswhereSTART_DATEiswithinthelast25months.whichWHEREclausecanbeused?A)WHERETO_NUMBER(start_date-SYSDATE)<=25B)WHEREADD_MONTHS(startdate,25)<=SYSDATEC)WHEREMONTHS_BETWEEN(SYSDATE,start_date)<=25D)WHEREMONTHS_BETWEEN(start_date,SYSDATE)<=25答案:C解析:[单选题]25.160Examinethisstatement:SELECT1ASid,?John?ASfirst_name,NULLAScommissionFROMdualINTERSECTSELECT1,?John?nullFROMdualORDERBY3;Whatisreturneduponexecution?A)2rowsB)0rowsC)AnerrorD)1rows答案:D解析:[单选题]26.167Examinethisstatement,whichexecutessuccessfully:Inwhichorderaretherowsdisplayed?A)sortedbyDEPARTMENT_NAMEB)sortedbyDEPARTMENT_NAMEandAVGSALC)sortedbyDEPARTMENT_NAMEandMAXSALD)sortedbyAVGSALE)SortedbyMAXSAL答案:D解析:[单选题]27.168Examinethisstatementwhichexecutessuccessfully:WhichstatementwillviolatetheCHECKconstraint?A)UPDATEemp80SETdepartment_id=90WHEREdepartment_id=80;B)DELETEFROMemp80WHEREdepartment_id=90;C)SELECT*FROMemp80WHEREdepartment_id=80；D)SELECT*FROMemp80WHEREdepartment_id=90;答案:A解析:[单选题]28.169Whichstatementfallstoexecutesuccessfully?A)SELECT*FROMemployeeseJOINdepartmentdWHEREe.department_id=d.department_idANDd.department_id=90;B)SELECT*FROMemployeeseJOINdepartmentsdONe.department_id=d.department_idWHEREd.department_id=90;C)SELECT*FROMemployeeseJOINdepartmentsdONe.department_id=d.department_idANDd.department_id=90;D)SELECT*FROMemployeeseJOINdepartmentsdONd.departments_id=90WHEREe.department_id=d.department_id;答案:D解析:[单选题]29.185Youmustfindthenumberofemployeeswhosesalaryislowerthanemployee110.Whichstatementfailstodothis?A)SELECTCOUNT(*)FROMemployeesJOINemployeesaONe.salary<a.salaryWHEREa.employee_id=110;B)SELECTCOUNT(*)FROMemployeesWHEREsalary<(SELECTsalaryFROMemployeesWHEREemployee业id=110);C)SELECTCOUNT(*)FROMemployeeseJOIN(SELECTsalaryFROMemployeesWHEREemployee_id=110)aONe.salary<a.salary;D)SELECTCOUNT(*)FROMemployeeseWHEREe.salary<(SELECTa.salaryFROMemployeesaWHEREe.employee_id=110);答案:D解析:[单选题]30.187ExaminethedataintheEMPLOYEEStable:EMPLOYEEIDLASTNAMEMONTHLYSATARYMONTHLYCOMMISSIONPCT101Kochhar24000<NULL>102Ernst17000.5103Rajs21000.2104Lorentz25000<NULL>105Morris12000<NULL>Whichstatementwillcomputethetotalannualcompensationforeachemployee?A)SELECTlastname,(monthlysalary*12)+(monthly_commission_pct*12)ASannualcompFROMemployees；B)SELECTlast_name(monthly_salary+monthly_commission_pct)*12ASannual_FROMemployees;C)SELECTlastname,(monthly_salary*12)+(monthly_salary*12*NVL(monthlycommissionpct,0))Asannual_compFROMemployees;D)SELECTlast_name,monthly_salary*12)+(monthly_salary*12*Monthlycommission_Pct)ASannual_compFROMemployees;答案:C解析:[单选题]31.问题192WhichstatementwillreturnthelastsequencenumbergeneratedbytheEMP_SEQsequence?A)SELECTNEXTVALFROMemp_seq;B)SELECTCURRVALFROMemp_seq;C)SELECTemp_seq.CURRVALFROMDUAL;D)SELECTemp_seq.NEXTVALFROMDUAL;答案:D解析:[单选题]32.问题197ExaminethedatainthePRODUCTStable:PRODIDPRODNAMEPRODLISTCATEGORYID101Plate101102Cup201103Saucer201104Knife301105Fork301Examinethesequeries:SELECTprodname,prodlistFROMproductsWHEREprod1istNOTIN(10，20)ANDcategory_id=1;2.SELECTprodname,|prod_listFROMproductsWHEREprodlist<>ANY(10，20)ANDcategory_id=1;3.SELECTprodname,prod_listFROMproductsWHEREprod_list<>ALL(10，20)ANDcategory_id=1;Whichqueriesgeneratethesameoutput?A)1and3B)1,2,and3C)2and3D)1and2答案:A解析:[单选题]33.199ExaminethisdescriptionoftheEMPtable:NameNull?TypeEMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)SALNUMBER(7,2)DEPTNONUMBER(2)Youexecutethisquery:SELECTdeptnoAS"departments",SUM(sal)AS"salary"FROMempGROUP|BY1HAVINGSUM(sal)>3000;Whatistheresult?A)onlydepartmentswherethetotalsalaryisgreaterthan3000,returnedinnoparticularorderB)alldepartmentsandasumofthesalariesofemployeeswithasalarygreaterthan3000C)anerrorD)onlydepartmentswherethetotalsalaryisgreaterthan3000,orderedbydepartment答案:C解析:[单选题]34.208Youwanttoreturnthecurrentdateandtimefromtheusersession,withadatatypeofTIMESTAMPWITHTIMEZONEWhichfunctionwilldothis?A)CURRENTDATEB)CURRENT_TIMESTAMPC)SYSDATED)LOCALTIMESTAMP答案:B解析:[单选题]35.210Examinethisstatementwhichexecutessuccessfully:CREATEviewemp80ASSELECTFROMemployeesWHEREdepartment_id=80WITHCHECKOPTION;WhichstatementwillviolatetheCHECKconstraint?A)DELETEFROMemp80WHEREdepartment_id=90;B)SELECTFROMemp80WHEREdepartment_id=90;C)SELECTFROMemp80WHEREdepartment.id=80;D)UPDATEemp80SETdepartment.1d=80;WHEREdepartment_id=90;答案:D解析:[单选题]36.214ExaminetheBRICKStable:COLOURSHAPEWEIGHTRedcube5Redcylinder10Bluecube15Bluecylinder20Youwritethisquery:SELECTFROMbricksb1CROSSJOINbricksb2WHEREb1.Weight<b2.Weight:Howmanyrowswillthequeryreturn?A)1B)16C)10D)6E)4F)0答案:D解析:[单选题]37.222ExaminethedescriptionoftheENPLOYEStable:NameNull?TypeEMP_IDNOTNULLNUMBEREMP_NAMEVARCHAR2(10)DEPTIDNUMBER(2)SALARYNUMBER(8,2)JOINDATEDATENLS_DATE_FORMATissettoDD-MON-YY.Whichqueryrequiresexplicitdatatypeconversion?A)SELECTSUBSTR(joindate,1,2)-10FROMemployees;B)SELECTjoin_date+'20'EROMemployees;C)SELECTjoin_date丨丨??丨丨salaryFROMemployees;D)SELECTjoin_dateFROMemployeesWHEREjoindate>*10-02-2018';E)SELECTsalary+'120.50'FROMemployees;答案:D解析:[单选题]38.问题224ExaminethedescriptionoftheEMPLOYEEStable:NameNull?TypeEMPLOYEEIDNOTNULLNUMBER(4)LASTNAMENOTNULLVARCHAR2(100)SALARYNOTNOTNULLNUMBER(6,2)DEPARTMENT_IDNOTNULLNUMBER(4)Examinethisquery:1SETECTe.last_name,2e.salary,3a.avg_sal4FROMemployeese5WHEREe.salary>(SELECTAVG(a.salary)ASavg__sal6FROMemployeesa7WHEREa.department_1d=e.department.1d)8ORDERBYe.last_name;Whichlineproducesanerror?A)Line7B)Line8C)Line3D)Line5答案:C解析:[单选题]39.1、Choosethebestanswer.ExaminethedescriptionoftheEMPLOYEEStable:NameNullTypeEMP_IDNOTNULNUMBEREMP_NAMEVARCHAR2(40)DEPT_IDNUMBER(2)SALARYNUMBER(8,2)JOIN_DATEDATEWhichqueryisvalid?A)SELECTdept_id,join_date,SUM(salary)FROMemployeesGROUPBYdept_id,join_date;B)SELECTdepe_id,join_date,SUM(salary)FROMemployeesGROUPBYdept_id:C)SELECTdept_id,MAX(AVG(salary))FROMemployeesGROUPBYdept_id;D)SELECTdept_id,AVG(MAX(salary))FROMemployeesGROUPBYdapt_id;答案:A解析:[单选题]40.233ExaminethedataintheINVOICEStable:INVOICE_IDCURRENCY_CODERAISED_DATE1EUR01-JAN-20192USD01-FEB-20193JPY01-MAR-2019ExaminethedataintheCURRENCIEStable:CURRENCY_CODEJPYGPBCADEURUSDWhichqueryreturnsthecurrenciesinCURRENCIESthatarenotpresentinINVOICES?A)SELECTcurrency_codeFROMcurrenciesMINUSSELECTcurrency_codeFROMinvoices;B)SELECT*FROMcurrenciesWHERENOTEXISTS(SELECTNULLFROMinvoicesWHEREcurrency_code=currency_code);C)SELECTcurrency_codeFROMcurrenciesINTERSECTSELECTcurrency_codeFROMinvoices;D)SELECT*FROMcurrenciesMINUSSELECT*FROMinvoices;答案:A解析:[单选题]41.235Examinethesestatementswhichexecutesuccessfully:ALTERSESSIONSETNLS_DATE_FORMAT=?DD-MON-YYYYHH24MI:SS?ALTERSESSIONSETTIME_ZONE=?-5:00?;SELECTDBTIMEZONE,SYSDATEFROMDUALExaminetheresult:DBTIMEZONESYSDATE+00.0011-JUL-201911:00:00IfLOCALTIMESTAMPwasselectedatthesametimewhatwoulditreturn?A)11-JUL-20196,00,00,00000000AM-05:00B)11-JUL-201911,00,00,00000000AMC)11-JUL-20196,00,00,000000AMD)11-JUL-201911,00,00,000000AM-05:00答案:B解析:[单选题]42.236Examinethisquery:SELECTSUBSTR(SYSDATE,1,5)?Result?FROMDUALWhichstatementistrue?A)ItfailsunlesstheexpressionismodifiedtoTO-CHAR(SUNBSTR(SYSDATE,1,5)B)ItfailsunlesstheexpressionismodifiedtoSUBSTR(TO_CHAR(SYSDATE),1,5)C)ItfailsunlesstheexpressionismodifiedtoSUBSTR(TO_CHAR(TRUNC(SYSDATE)),1,5)D)Itexecutessuccessfullywithanimplicitdatatypeconversion答案:D解析:[单选题]43.问题237Inyoursession,theNLS._DAE_FORMATisDD-MM-YYYY.Thereare86400secondsinaday.Examinethisresult:DATE02-JAN-2020Whichstatementreturnsthis?A)SELECTTO_CHAR(TO_DATE(?29-10-2019?)+INTERVAL?2?;MONTH+INTERVAL?5?;DAY-INTERVAL?86410?SECOND,?DD-MON-YYYY?)AS"date"FROMDUAL;B)SELECTTO_CHAR(TO_DATE(?29-10-2019?)+INTERVAL?3?MONTH+INTERVAL?7?DAY-INTERVAL?360?SECOND,?DD-MON-YYYY?)AS"date"FROMDUAL;C)SELECTToCHAR(TO_DATE(?29-10-2019?)+INTERVAL?2?NONTH+INTERVAL?5?DAYINEERVAL?120?SECOND,?DD-MON-YYY)AS"date"FROMDUAL;D)SELECT-TO_CHAR(TO_DATE(?29-10-2019?+INTERVAL?2?MONTH+INTERVAL?6?DAY-INTERVAL?120?SECOND,?DD-MON-YY?)AS"daTe"FROMDUAL;E)SELECT-TO_CHAR(TO_DATE(?29-10-2019?+INTERVAL?2?MONTH+INTERVAL?4?DAY-INTERVAL?120?SECOND,?DD-MON-YY?)AS"daTe"FROMDUAL;答案:B解析:[单选题]44.240ExaminethisStatementwhichreturnsthenameofeachemployeeandtheirmanager,SELECTe.lastnameASemp,,m.last_nameASmgrFROMemployeeseJOINmanagersmONe.manager_id=m.employee_idORDERBYemp;Youwanttoextendthequerytoincludeemployeeswithnomanager.WhatmustyouaddbeforeJOINtodothis?A)CROSSB)FULLOUTERC)LEFTOUTERD)RIGHTOUTER答案:C解析:[单选题]45.241whichistrueabouttheround,truncateandmodfunctions>?A)ROUND(MOD(25,3),-1)ISINVALIDB)ROUND(MOD(25,3),-1)ANDTRUNC(MOD(25,3),-1)AREBOTHVALIDANDGIVETHESAMERESULT.C)ROUND(MOD(25,3),-1)ANDTRUNC(MOD(25,3),-1)AREBOTHVALIDANDGIVETHEDIFFERENTRESULTS.D)TRUNC(MOD(25,3),-1)ISINVALID.答案:B解析:[单选题]46.248Examinethisquery:SELECTINTERVAL'100'MONTHDURATIONFROMDUAL;Whatwillbetheoutput?A)DURATION+08-04B)DURATION+100C)DURATION+08D)anerror答案:A解析:[单选题]47.254Whichstatementwillreturnacomma-separatedlistofemployeenamesinalphabeticalorderforeachdepartmentintheEMPtable?A)SELECTdeptno,LISTAGG(ename,',')WITHINGROUPASemployee_listFROMempGROUPBYdeptno;B)SELECTdeptno,LISTAGG(ename,',')WITHINGROUPASemployee_listFROMempGROUPBYdeptnoORDERBYename;C)SELECTdeptno,LISTAGG(ename,',')WITHINGROUP(GROUPBYdeptno)ASemployee_listFROMempORDERBYename;D)SELECTdeptno,LISTAGG(ename,',')WITHINGROUP(ORDERBYename)ASemployee_listFROMempGROUPBYdeptno;答案:D解析:[单选题]48.258Examinethesestatements:CREATETABLEalter_test(c1VARCHAR2(10),c2NUMBER(10));INSERTINTOalter_testVALUES('123'，123);COMMIT;WhichistrueahoutmodifyIngthecolumnsinAITER_TEST?A)c1canbechangedtoNUMBER(10)andc2canbechangedtoVARCHAN2(10).B)c2canbechangedtoNUMBER(5)butc1cannotbechangedtoVARCHAN2(5).C)c2canbechangedtoVARCHAR2(10)butc1cannotbechangedtoNUMBER(10).D)c1canbechangedtoNUMBER(10)butc2cannotbechangedtoVARCHAN2(10).E)c1canbechangedtoVARCHAR2(5)andc2canbechangedtoNUMBER(12,2).答案:E解析:[单选题]49.问题259Youhavetheprivilegestocreateanytypeofsynonym.WhichstalementwillcreateasynonymcalledEMPfortheHCM.EMPLOYEE_RECORDStablethatisaccesibletoallusers?A)CREATEGLOBALSYNONYMempFORhcm.employee_records;B)CREATESYNONYMempFORhcm.employee_records;C)CREATESYNONYMPUBLIC.empFORhcm.employee_records;D)CREATESYNONYMSYS.empFORhcm.employee_records;E)CREATEPUBLICSYNONYMempFORhcm.employee_records;答案:E解析:[单选题]50.272Examinethispartialquery:SELECTch.channel_type,t.month,co.country_code,SUM(s.amount_sold)SALESFROMsaless,timest,channelsch,countriescoWHEREs.time_id=t.timeidANDs.country_id=co.countryidANDs.channelid=ch.channelidANDch.channeltypeIN(&apo