OracleOCP認(rèn)證考試練習(xí)真題1Z0-062英文

試卷科目：OracleOCP認(rèn)證考試練習(xí)OracleOCP認(rèn)證考試練習(xí)真題1Z0-062英文PAGE"pagenumber"pagenumber/SECTIONPAGES"numberofpages"numberofpagesOracleOCP認(rèn)證考試練習(xí)真題1Z0-062英文第1部分：單項(xiàng)選擇題，共78題，每題只有一個(gè)正確答案,多選或少選均不得分。[單選題]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只能處理過濾掉一個(gè)字符，不能過濾多個(gè)字符，所以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表示保留到小數(shù)點(diǎn)左邊2位，也就是百位，那么，四舍五入后就是200；（ROUND(156.00,-1)結(jié)果就是160）TRUNC是直接截?cái)啵財(cái)嗪笠彩?00。[單選題]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會(huì)排除空值，所以不對(duì)，必須要先對(duì)空值進(jìn)行非空處理，再統(tǒng)計(jì)[單選題]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等需要分組的函數(shù)不能相互嵌套[單選題]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解析:當(dāng)存在主外鍵約束關(guān)系的時(shí)候，外鍵關(guān)系啟用的時(shí)候，不能對(duì)主表進(jìn)行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操作符用于將值與子查詢返回的值列表或結(jié)果集進(jìn)行比較。[單選題]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連接的語句中只會(huì)出現(xiàn)一個(gè)orderby位于結(jié)尾（不包含子查詢中的）否則會(huì)報(bào)[單選題]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都是可以執(zhí)行，按考點(diǎn)來，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轉(zhuǎn)換[單選題]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解析:[單選