OracleOCP认证考试练习真题1Z0-062英文

试卷科目：OracleOCP认证考试练习OracleOCP认证考试练习真题1Z0-062英文PAGE"pagenumber"pagenumber/SECTIONPAGES"numberofpages"numberofpagesOracleOCP认证考试练习真题1Z0-062英文第1部分：单项选择题，共78题，每题只有一个正确答案,多选或少选均不得分。[单选题]1.class="fr-ficfr-dibcursor-hover">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解析:[单选题]2.问题5ChoosethebestanswerTheCUSTOMERStablehasaCUST_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解析:trim只能处理过滤掉一个字符，不能过滤多个字符，所以BC可以排除。[单选题]3.问题8ChoosethebestanswerTheEMPLOYEEStablecontainscolumnsEMP_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解析:[单选题]4.问题13Choosethebestanswer.Examinethisquery:SELECTTRUNC(ROUND(156.00,-2),-1)FROMDUAL;Whatistheresult?A)16B)160C)150D)200E)100答案:D解析:ROUND(156.00,-2)也就是说，156.00四舍五入，-2表示保留到小数点左边2位，也就是百位，那么，四舍五入后就是200；（ROUND(156.00,-1)结果就是160）TRUNC是直接截断，截断后也是200。[单选题]5.问题37ChoosethebestanswerExaminethedescriptionofthePRODUCT_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)BELECTCOUNT(list_price)FROMproduct_informationwherelist_priceisNULL;答案:B解析:直接用count会排除空值，所以不对，必须要先对空值进行非空处理，再统计[单选题]6.问题45ChoosethebestanswerYouhavebeenaskedtocreateatableforabankingapplication.Oneofthecolumnsmustmeetthreerequirements:1)Bestoredinaformatsupportingdatearithmeticwithoutusingconversionfunctions2)Storealoanperiodofupto10years3)BeusedforcalculatinginterestforthenumberofdaystheloanremainsunpaidWhichdatatypeshouldyouuse?A)TIMESTAMPWITHTIMEZONEB)TIMESTAMPC)TIMESTAMPWITHLOCALTIMEZONED)INTERVALYEARTOMONTHE)INTERVALDAYTOSECOND答案:B解析:[单选题]7.问题54Choosethebestanswer.Examinethisquery:SELECT2FROMduald1CROSSJOINduald2CROSSJOINduald3;Whatisreturneduponexecution?A)0rowsB)anerrorC)8rowsD)6rowsE)1rowF)3rows答案:E解析:[单选题]8.问题56ChoosethebestanswerExaminethedescriptionoftheCUSTOMERStable: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解析:[单选题]9.问题57ExaminethedescriptionortheCUSTOMERStable: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解析:[单选题]10.问题59YouwanttowriteaquerythatpromptsfortwocolumnnamesandtheWHEREconditioneachtimeItisexecutedinasessionbutonlypromptsforthetablenamethefirsttimeitisexecuted.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解析:[单选题]11.问题66ExaminethedescriptionoftheEMPLOYEEStable: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)SELECTjoin_date11.?11salaryFROMemployees;D)SELECTjoin_dateFROMemployeeswherejoindate>*10-02-2018*;E)SELECTjoin_date+20FROMemployees;答案:D解析:[单选题]12.问题88ExaminethedescriptionoftheEMPLOYEEStable: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解析:max,和avg,min,count,sum等需要分组的函数不能相互嵌套[单选题]13.问题91ExaminethedescriptionoftheSALES1table:NameNullTypeSALES_IDNOTNULLNUMBERSTORE_IDNOTNULLNUMBERITEMS_IDNUMBERQUANTITYNUMBERSALES_DATEDATESALES2isatablewiththesamedescriptionasSALES1,SomesalesdataisduplicatedInbothtables.YouwanttodisplaytherowsfromtheSALES1tablewhicharenotpresentintheSALIES2table.Whichsetoperatorgeneratestherequiredoutput?A)SUBTRACTB)INTERSECTC)UNIONALLD)MINUSE)UNION答案:D解析:[单选题]14.问题117Whichstatementistrueaboutaggregatefunctions?A)TheAVGfunctionimplicitlyconvertsNULLStozero.B)TheMAXandMINfunctionscanbeusedoncolumnswithcharacterdatatypesC)AggregatefunctionscanbeusedinanyclauseofaSELECTstatementD)Aggregatefunctionscanbenestedtoanynumberoflevels答案:B解析:[单选题]15.问题122WhichstatementistrueaboutTRUNCATEandDELETE?A)ForlargetablesTRUNCATEisfasterthanDELETE.B)FortableswithmultipleindexesandtriggersisfasterthanTRUNCATE.C)YoucanneverTRUNCATEatableifforeignkeyconstraintswillbeviolated.D)Youcannevertowsfromatableifforeignkeyconstraintswillbeviolated.答案:A解析:当存在主外键约束关系的时候，外键关系启用的时候，不能对主表进行drop和truncate操作。[单选题]16.问题123ExaminethedescriptionortheBOOKS_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解析:[单选题]17.问题124ViewtheExhibitsandexaminethestructureoftheCOSTSandPROMOTIONStables.YouwanttodisplayPRODIDSwhosepromotioncostislessthanthehighestcostPRODIDinapromotiontimeinterval.ExaminethisSQLstatement:SELECTprodidFROMcostsWHEREpromoidIN(SELECTpromoidFROMpromotionsWHEREpromo_cost<ALL(SELECTMAX(promocost)FROMpromotionsGROUPBY(promo_enddate-promo_begin_date)));Whatwillbetheresult?A)Itexecutessuccessfullybutdoesnotgivetherequiredresult.B)ItgivesanerrorbecausetheALLkeywordisnotvalid.C)ItgivesanerrorbecausetheGROUPBYclauseisnotvalidD)Itexecutessuccessfullyandgivestherequiredresult.答案:A解析:OracleALL操作符用于将值与子查询返回的值列表或结果集进行比较。[单选题]18.问题139WhichisthedefaultcolumnorcolumnsforsortingoutputfromcompoundqueriesusingSEToperatorssuchasINTERSECTinaSQLstatement?A)ThefirstcolumninthelastSELECTofthecompoundqueryB)ThefirstNUMBERcolumninthefirstSELECTofthecompoundqueryC)ThefirstVARCHAR2columninthefirstSELECTofthecompoundqueryD)ThefirstcolumninthefirstSELECTofthecompoundqueryE)ThefirstNUMBERorVARCHAR2columninthelastSELECTofthecompoundquery答案:D解析:[单选题]19.问题141Youexecutethisquery:SELECTTO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'),'dd"Mondayfor"fmMonthrrrr')FROMDUAL;Whatistheresult?A)Itexecutessuccessfullybutdoesnotreturnanyresult.B)ItreturnsthedateforthefirstMondayofthenextmonth.C)Itgeneratesanerror.D)ItreturnsthedateforthelastMondayofthecurrentmonth.答案:B解析:[单选题]20.问题142WhichstatementistrueabouttheINTERSECToperatorusedincompoundqueries?A)ItprocessesNULLSintheselectedcolumns.B)INTERSECTisoflowerprecedencethanUNIONorUNIONALL.C)ItignoresNULLS.D)MultipleINTERSECToperatorsarenotpossibleinthesameSQLstatement答案:A解析:[单选题]21.问题145WhichstatementistrueregardingtheSESSION_PRIVSdictionaryview?A)Itcontainstheobjectprivilegesgrantedtootherusersbythecurrentusersession.B)ItcontainsthesystemprivilegesgrantedtootherusersbythecurrentUsersession.C)Itcontainsthecurrentsystemprivilegesavailableintheusersession.D)Itcontainsthecurrentobjectprivilegesavailableintheusersession.答案:C解析:[单选题]22.问题146ViewtheExhibitandexaminethestructureoftheSALES,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解析:[单选题]23.问题148Youexecutethesecommands: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解析:hiredate已被提前定义不在提示[单选题]24.问题149EvaluatethefollowingSQLstatementSQL>SELECTpromo_id,prom_categoryFROMpromotionsWHEREpromo_category=?Internet?ORDERBYpromo_idUNIONSELECTpromo_id,promo_categoryFROMPomotionsWHEREpromo_category=?TV?UNIONSELECTpromoid,promo_categoryFROMpromotionsWHEREpromocategory=?Radio?Whichstatementistrueregardingtheoutcomeoftheabovequery?A)ItexecutessuccessfullyanddisplaysrowsinthedescendignoreofPROMOCATEGORYB)Itproducesanerrorbecausepositional,notationcannotbeusedintheORDERBYclausewithSBToperators.C)ItexecutessuccessfullybutignorestheORDERBYclausebecauseitisnotlocatedattheendofthecompoundstatement.D)ItproducesanerrorbecausetheORDERBYclauseshouldappearonlyattheendofacompoundquery-thatis,withthelastSELECTstatement.答案:D解析:union连接的语句中只会出现一个orderby位于结尾（不包含子查询中的）否则会报[单选题]25.问题150ExaminethecommandtocreatetheBOOKStable.SQL>createtablebooks(book_idCHAR(6)PRIMARYKEY,titleVARCHAR2(100)NOTNULL,publisher_idVARCHAR2(4)，author_idVARCHAR2(50));TheBOOKIDvalue101doesnotexistinthetable.ExaminetheSQLstatement.insertintobooks(book_id,title,author_id)values(?101?，?LEARNINGSQL?,?TimJones?)A)ItexecutessuccessfullyandtherowisinsertedwithanullPLBLISHER_ID.B)ItexecutessuccessfullyonlyifNULLisexplicitlyspecifiedintheINSERTstatement.C)ItexecutessuccessfullyonlyNULLPUBLISHER_IDcolumnnameisaddedtothecolumnslistintheINSERTstatement.D)ItexecutessuccessfullyonlyifNULLPUBLISHERIDcolumnnameisaddedtothecolumnslistandNULLisexplicitlyspecifiedIntheINSERTstatement.答案:A解析:[单选题]26.问题153TheSTOREStablehasacolumnSTART_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解析:[单选题]27.60Examinethisstatement:SELECT1ASid,?John?ASfirst_name,NULLAScommissionFROMdualINTERSECTSELECT1,?John?nullFROMdualORDERBY3;Whatisreturneduponexecution?A)2rowsB)0rowsC)AnerrorD)1ROW答案:D解析:[单选题]28.问题167Examinethisstatement,whichexecutessuccessfully:SELECTd.department_name,ROUND(AVG(NULLIF(e.salary,0)))ASavgsal,MAX(e.salary)ASmaxsalFROMemployeeseJOINdepartmentsdON(e.department_id=d.department_id)GROUPBYd.department_nameORDERBY2;Inwhichorderaretherowsdisplayed?A)sortedbyDEPARTMENT_NAMEB)sortedbyDEPARTMENT_NAMEandAVGSALC)sortedbyDEPARTMENT_NAMEandMAXSALD)sortedbyAVGSALE)SortedbyMAXSAL答案:D解析:[单选题]29.问题168Examinethisstatementwhichexecutessuccessfully:CREATEVIEWemр80ASSELECTE*FROMemployeesWHERRdepartment_id=80WITHCHECKOPTION;WhichstatementwillviolatetheCHECKconstraint?A)UPDATEemp80SETdepartment_id=90WHEREdepartment_id=80;B)DELETEFROMemp80WHEREdepartment_id=90;C)SELECT*FROMemp80WHEREdepartment_id=80；D)SELECT*FROMemp80WHEREdepartment_id=90;答案:A解析:[单选题]30.问题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解析:BCD都是可以执行，按考点来，on接condition，where接comparition[单选题]31.问题185Youmustfindthenumberofemployeeswhosesalaryislowerthanemployee110.Whichstatementfailstodothis?A)SELECTCOUNT(*)FROMemployeeseJOINemployeesaONe.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解析:[单选题]32.问题187ExaminethedataintheEMPLOYEEStable:EMPLOYEE_IDLAST_NAMEMONTHLY_SATARYMONTHLY_COMMISSION_PCT101Kochhar24000<NULL>102Ernst17000.5103Rajs21000.2104Lorentz25000<NULL>105Morris12000<NULL>Whichstatementwillcomputethetotalannualcompensationforeachemployee?A)SELECTlastname,(monthly_salary*12)+(monthly_commission_pct*12)ASannualcompFROMemployees;B)SELECTlast_name(monthly_salary+monthly_commission_pct)*12ASannual_compFROMemployees;C)SELECTlastname,(monthly_salary*12)+(monthly_salary*12*NVL(monthly_commission_pct,0))Asannual_compFROMemployees;D)SELECTlast_name,monthly_salary*12)+(monthly_salary*12*Monthlycommission_Pct)ASannual_compFROMemployees;答案:C解析:[单选题]33.问题190TheSYSDATEfunctiondisplaysthecurrentOracleServerdateas:21-MAY-19Youwishtodisplaythedateas:MONDAY,21MAY,2019Whichstatementwilldothis?A)SELECTTO_CHAR(SYSDATE,'FMDAY,DDMONTH,YYYY')FROMDUAL;B)SELECTTO_DATE(SYSDATE,'FMDAY,DDMONTH,YYYY')FROMDUAL;C)SELECTTO_CHAR(SYSDATE,'FMDD,DAYMONTH,YYYY')FROMDUAL;D)SELECTTO_CHAR(SYSDATE,'FMDAY,DDTHMONTH,YYYY')FROMDUAL;答案:A解析:[单选题]34.问题197ExaminethedatainthePRODUCTStable:PROD_IDPROD_NAMEPROD_LISTCATEGORY_ID101Plate101102Cup201103Saucer201104Knife301105Fork301Examinethesequeries:SELECTprodname,prodlistFROMproductsWHEREprod_listNOTIN(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解析:[单选题]35.问题199ExaminethisdescriptionoftheEMPtable:NameNull?TypeEMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)SALNUMBER(7,2)DEPTNONUMBER(2)Youexecutethisquery:SELECTdeptnoAS"departments",SUM(sal)AS"salary"FROMempGROUPBY1HAVINGSUM(sal)>3000;Whatistheresult?A)onlydepartmentswherethetotalsalaryisgreaterthan3000,returnedinnoparticularorderB)alldepartmentsandasumofthesalariesofemployeeswithasalarygreaterthan3000C)anerrorD)onlydepartmentswherethetotalsalaryisgreaterthan3000,orderedbydepartment答案:C解析:[单选题]36.问题203Examinethisquerywhichexecutessuccessfully:SELECTjob,deptnoFROMempUNIONALLSELECTjob,deptnoFROMjobs_history;Whatwillbetheresult?A)ItwillreturnrowscommontobothSELECTstatements.B)ItwillreturnrowsfrombothSELECTstatementsaftereliminatingduplicaterows.C)ItwillreturnrowsthatarenotcommontobothSELECTstatements.D)ItwillreturnrowsfrombothSELECTstatementsincludingduplicaterows.答案:D解析:[单选题]37.问题208Youwanttoreturnthecurrentdateandtimefromtheusersession,withadatatypeofTIMESTAMPWITHTIMEZONE.Whichfunctionwilldothis?A)CURRENTDATEB)CURRENT_TIMESTAMPC)SYSDATED)LOCALTIMESTAMP答案:B解析:[单选题]38.问题210Examinethisstatementwhichexecutessuccessfully:CREATEviewemp80ASSELECTFROMemployeesWHEREdepartment_id=80WITHCHECKOPTION;WhichstatementwillviolatetheCHECKconstraint?A)DELETEFROMemp80WHEREdepartment_id=90;B)SELECTFROMemp80WHEREdepartment_id=90;C)SELECTFROMemp80WHEREdepartment.id=80;D)UPDATEemp80SETdepartment._id=80WHEREdepartment_id=90;答案:D解析:[单选题]39.问题214ExaminetheBRICKStable:COLOURSHAPEWEIGHTRedcube5Redcylinder10Bluecube15Bluecylinder20Youwritethisquery:SELECTFROMbricksb1CROSSJOINbricksb2WHEREb1.Weight<b2.Weight:Howmanyrowswillthequeryreturn?A)1B)16C)10D)6E)4F)0答案:D解析:[单选题]40.问题216ExaminethedataintheEMPLOYEEStable:EMPLOYEE_IDLAST_NANEMONTHLY_SALARYMONTTHLY_COMEHISSIOM_PCT101Kochhar24000<NULL>102Ernst17000.5103Rajs21000.2104LORENTZ25000<NULL>105Morris12000<NULL>Whichstatementwillcomputethetotalannualcompensationforeachemployee?A)SELECTlast_NAME(monthly_salary+monthly_commission_pct)*12ASannual_compFROMemployees;B)selectlast_name,(monthly_salary*12)+(monthly_salary*12*monthly_commission_pct)ASannual_campFROMemployeesC)SELECTlast_name,(monthly_salary*12)+(monthly_salary*12*NVL(monthly_commission_pct,0))ASannual_compD)SELECTlast_name,(monthly_salary*12)+(monthly_commission_pct*12)ASFROMemployees:答案:C解析:[单选题]41.问题222ExaminethedescriptionoftheENPLOYEStable:NameNull?TypeEMP_IDNOTNULLNUMBEREMP_NAMEVARCHAR2(10)DEPTIDNUMBER(2)SALARYNUMBER(8,2)JOINDATEDATENLS_DATE_FORMATissettoDD-MON-YY.Whichqueryrequiresexplicitdatatypeconversion?A)SELECTSUBSTR(join_date,1,2)-10FROMemployees;B)SELECTjoin_date+'20'FROMemployees;C)SELECTjoin_date丨丨??丨丨salaryFROMemployees;D)SELECTjoin_dateFROMemployeesWHEREjoindate>*10-02-2018';E)SELECTsalary+'120.50'FROMemployees;答案:D解析:需要to_date转换[单选题]42.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_id=e.department.id)8ORDERBYe.last_name;Whichlineproducesanerror?A)Line7B)Line8C)Line3D)Line5答案:C解析:[单选题]43.问题225Examinethisquerywhichexecutessuccessfully;Selectjob,deptnofromempUnionallSelectjob,deptnofromjobs_history;Whatwillbetheresult?A)Itwillreturnrowsfrombothselectstatementsaftereliminatingduplicaterows.B)Itwillreturnrowscommontobothselectstatements.C)Itwillreturnrowsbothselectstatementsincludingduplicaterows.D)Itwillreturnrowsthatarenotcommontobothselectstatements.答案:C解析:[单选题]44.问题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解析:[单选题]45.问题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解析:[单选题]46.问题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解析:[单选