OracleOCP認證考試練習(xí)真題題庫十

試卷科目：OracleOCP認證考試練習(xí)OracleOCP認證考試練習(xí)真題題庫十PAGE"pagenumber"pagenumber/SECTIONPAGES"numberofpages"numberofpagesOracleOCP認證考試練習(xí)真題題庫十第1部分：單項選擇題，共77題，每題只有一個正確答案,多選或少選均不得分。[單選題]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解析:[單選題]2.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解析:trim只能處理過濾掉一個字符,不能過濾多個字符,所以BC可以排除。[單選題]3.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解析:[單選題]4.Choosethebestanswer.Examinethisquery:SELECTTRUNC(ROUND(156.00,-2),-1)FROMDUAL;Whatistheresult?A)16B)160C)150D)200E)100答案:D解析:ROUND函數(shù)依據(jù)制定的小數(shù)精度對數(shù)值進行攝入運算。其函數(shù)有兩個參數(shù)。decimalpercision制定攝入的精度。負的小數(shù)精度值位于小數(shù)點左邊,正值在小數(shù)點右邊。TRUNC函數(shù)依據(jù)指定的小數(shù)精度對數(shù)值截取運算。不進行四舍五入。[單選題]5.ChoosethebestanswerYouhavebeenaskedtocreateatableforabankingapplication.Oneofthecolumnsmustmeetthreerequirements:1)Bestoredinaformatsupportingdatearithmeticwithoutusingconversionfunctions2)Storealoanperiodofupto10years3)BeusedforcalculatinginterestforthenumberofdaystheloanremainsunpaidWhichdatatypeshouldyouuse?A)TIMESTAMPWITHTIMEZONEB)TIMESTAMPC)TIMESTAMPWITHLOCALTIMEZONED)INTERVALYEARTOMONTHE)INTERVALDAYTOSECOND答案:E解析:官檔SQLLanguagereference>BasicElementsofOracleSQL[單選題]6.Choosethebestanswer.Examinethisquery:SELECT2FROMduald1CROSSJOINduald2CROSSJOINduald3;Whatisreturneduponexecution?A)0rowsB)anerrorC)8rowsD)6rowsE)1rowF)3rows答案:E解析:實驗驗證:[單選題]7.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解析:考察like模糊匹配[單選題]8.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解析:[單選題]9.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解析:[單選題]10.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解析:[單選題]11.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解析:[單選題]12.ExaminethedescriptionoftheSALES1table:NameNullTypeSALES_IDNOTNULLNUMBERSTORE_IDNOTNULLNUMBERITEMS_IDNUMBERQUANTITYNUMBERSALES_DATEDATESALES2isatablewiththesamedescriptionasSALES1,SomesalesdataisduplicatedInbothtables.YouwanttodisplaytherowsfromtheSALES1tablewhicharenotpresentintheSALIES2table.Whichsetoperatorgeneratestherequiredoutput?A)SUBTRACTB)INTERSECTC)UNIONALLD)MINUSE)UNION答案:D解析:[單選題]13.Whichstatementistrueaboutaggregatefunctions?A)TheAVGfunctionimplicitlyconvertsNULLStozero.B)TheMAXandMINfunctionscanbeusedoncolumnswithcharacterdatatypesC)AggregatefunctionscanbeusedinanyclauseofaSELECTstatementD)Aggregatefunctionscanbenestedtoanynumberoflevels答案:B解析:(none)Explanation說明/參考:Avgfunction通過實驗證明NULL行被忽略。[單選題]14.WhichstatementistrueaboutTRUNCATEandDELETE?A)ForlargetablesTRUNCATEisfasterthanDELETE.B)FortableswithmultipleindexesandtriggersisfasterthanTRUNCATE.C)YoucanneverTRUNCATEatableifforeignkeyconstraintswillbeviolated.D)Youcannevertowsfromatableifforeignkeyconstraintswillbeviolated.答案:A解析:(none)Explanation說明/參考:[單選題]15.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解析:(none)Explanation說明/參考:[單選題]16.ViewtheExhibitsandexaminethestructureoftheCOSTSandPROMOTIONStables.YouwanttodisplayPRODIDSwhosepromotioncostislessthanthehighestcostPRODIDinapromotiontimeinterval.ExaminethisSQLstatement:SELECTprodidFROMcostsWHEREpromoidIN(SELECTpromoidFROMpromotionsWHEREpromo_cost<ALL(SELECTMAX(promocost)FROMpromotionsGROUPBY(promo_enddate-promo_begin_date)));Whatwillbetheresult?A)Itexecutessuccessfullybutdoesnotgivetherequiredresult.B)ItgivesanerrorbecausetheALLkeywordisnotvalid.C)ItgivesanerrorbecausetheGROUPBYclauseisnotvalidD)Itexecutessuccessfullyandgivestherequiredresult.答案:A解析:(none)Explanation說明/參考:[單選題]17.WhichisthedefaultcolumnorcolumnsforsortingoutputfromcompoundqueriesusingSEToperatorssuchasINTERSECTinaSQLstatement?A)ThefirstcolumninthelastSELECTofthecompoundqueryB)ThefirstNUMBERcolumninthefirstSELECTofthecompoundqueryC)ThefirstVARCHAR2columninthefirstSELECTofthecompoundqueryD)ThefirstcolumninthefirstSELECTofthecompoundqueryE)ThefirstNUMBERorVARCHAR2columninthelastSELECTofthecompoundquery答案:D解析:(none)Explanation說明/參考:[單選題]18.Youexecutethisquery:SELECTTOCHAR(NEXT_DAY(LAST_DAY(SYSDATE),?MON?),?dd?Mondayfor?fmMonthrrr?)FROMDUAL;Whatistheresult?A)Itexecutessuccessfullybutdoesnotreturnanyresult.B)ItreturnsthedateforthefirstMondayofthenextmonth.C)Itgeneratesanerror.D)ItreturnsthedateforthelastMondayofthecurrentmonth.答案:B解析:(none)Explanation說明/參考:[單選題]19.WhichstatementistrueabouttheINTERSECToperatorusedincompoundqueries?A)ItprocessesNULLSintheselectedcolumns.B)INTERSECTisoflowerprecedencethanUNIONorUNIONALL.C)ItignoresNULLS.D)MultipleINTERSECToperatorsarenotpossibleinthesameSQLstatement答案:A解析:(none)Explanation說明/參考:[單選題]20.WhichstatementistrueregardingtheSESSION_PRIVSdictionaryview?A)Itcontainstheobjectprivilegesgrantedtootherusersbythecurrentusersession.B)ItcontainsthesystemprivilegesgrantedtootherusersbythecurrentUsersession.C)Itcontainsthecurrentsystemprivilegesavailableintheusersession.D)Itcontainsthecurrentobjectprivilegesavailableintheusersession.答案:C解析:(none)Explanation說明/參考:[單選題]21.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解析:(none)Explanation說明/參考:[單選題]22.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解析:(none)Explanation說明/參考:[單選題]23.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解析:(none)Explanation說明/參考:[單選題]24.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_ID.B)ItexecutessuccessfullyonlyifNULLisexplicitlyspecifiedintheINSERTstatement.C)ItexecutessuccessfullyonlyNULLPUBLISHER_IDcolumnnameisaddedtothecolumnslistintheINSERTstatement.D)ItexecutessuccessfullyonlyifNULLPUBLISHERIDcolumnnameisaddedtothecolumnslistandNULLisexplicitlyspecifiedIntheINSERTstatement.答案:A解析:(none)Explanation說明/參考:[單選題]25.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解析:(none)Explanation說明/參考:[單選題]26.Examinethisstatement:SELECT1ASid,?John?ASfirst_name,NULLAScommissionFROMdualINTERSECTSELECT1,?John?nullFROMdualORDERBY3;Whatisreturneduponexecution?[A)2rowsB)0rowsC)AnerrorD)1ROW答案:D解析:(none)Explanation說明/參考:[單選題]27.Examinethisstatement,whichexecutessuccessfully:Inwhichorderaretherowsdisplayed?A)sortedbyDEPARTMENT_NAMEB)sortedbyDEPARTMENT_NAMEandAVGSALC)sortedbyDEPARTMENT_NAMEandMAXSALD)sortedbyAVGSALE)SortedbyMAXSAL答案:D解析:(none)Explanation說明/參考:[單選題]28.Examinethisstatementwhichexecutessuccessfully:WhichstatementwillviolatetheCHECKconstraint?A)UPDATEemp80SETdepartment_id=90WHEREdepartment_id=80;B)DELETEFROMemp80WHEREdepartment_id=90;C)SELECT*FROMemp80WHEREdepartment_id=80;D)SELECT*FROMemp80WHEREdepartment_id=90;答案:A解析:(none)Explanation說明/參考:[單選題]29.Whichstatementfailstoexecutesuccessfully?A)SELECT*FROMemployeeseJOINdepartmentdWHEREedepartment_id=d.department_idANDddepartment_id=90;B)SELECT*FROMemployeeseJOINdepartmentsdONedepartment_id=d.department_idWHEREddepartment_id=90;C)SELECT*FROMemployeeseJOINdepartmentsdONedepartment_id=d.department_idANDddepartment_id=90;D)SELECT*FROMemployeeseJOINdepartmentsdONddepartment_id=90WHEREedepartment_id=ddepartment_id;答案:A解析:A選項缺失關(guān)鍵字joinon/using[單選題]30.Youmustfindthenumberofemployeeswhosesalaryislowerthanemployee110.Whichstatementfailstodothis?A)SELECTCOUNT(*)FROMemployeeseJOINemployeesaONe。salary<a。salaryWHEREa。employee_id=110;B)SELECTCOUNT(*)FROMemployeesWHEREsalary<(SELECTsalaryFROMemployeesWHEREemployee_id=10);C)SELECTCOUNT(*)FROMemployeeseJOIN(SELECTsalaryFROMemployeesWHEREemployee_id=110)aONe。salary<a。salary;D)SELECTCOUNT(*)FROMemployeeseWHEREe。salary<(SELECTa。salaryFROMemployeesaWHEREe。employee_id=110);答案:D解析:(none)Explanation說明/參考:[單選題]31.ExaminethedataintheEMPLOYEEStable: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解析:(none)Explanation說明/參考:[單選題]32.TheSYSDATEfunctiondisplaysthecurrentOracleServerdateas: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解析:(none)Explanation說明/參考:[單選題]33.WhichstatementwillreturnthelastsequencenumbergeneratedbytheEMP_SEQsequence?A)SELECTNEXTVALFROMemp_seq;B)SELECTCURRVALFROMemp_seq;C)SELECTemp_seq.CURRVALFROMDUAL;D)SELECTemp_seq.NEXTVALFROMDUAL;答案:D解析:(none)Explanation說明/參考:[單選題]34.ExaminethedatainthePRODUCTStable:PRODIDPRODNAMEPRODLISTCATEGORYID101Plate101102Cup201103Saucer201104Knife301105Fork301Examinethesequeries:1SELECTprodname,prodlistFROMproductsWHEREprod1istNOTIN(10,20)ANDcategory_id=1;2SELECTprodname,|prod_listFROMproductsWHEREprodlist<>ANY(10,20)ANDcategory_id=1;3SELECTprodname,prod_listFROMproductsWHEREprod_list<>ALL(10,20)ANDcategory_id=1;Whichqueriesgeneratethesameoutput?A)1and3B)1,2,and3C)2and3D)1and2答案:A解析:(none)Explanation說明/參考:[單選題]35.ExaminethisdescriptionoftheEMPtable: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解析:(none)Explanation說明/參考:[單選題]36.Examinethisquerywhichexecutessuccessfully:SELECTjob,deptnoFROMempUNIONALLSELECTjob,deptnoFROMjobs_history;Whatwillbetheresult?A)ItwillreturnrowscommontobothSELECTstatements.B)ItwillreturnrowsfrombothSELECTstatementsaftereliminatingduplicaterows.C)ItwillreturnrowsthatarenotcommontobothSELECTstatements.D)ItwillreturnrowsfrombothSELECTstatementsincludingduplicaterows.答案:D解析:(none)Explanation說明/參考:[單選題]37.Youwanttoreturnthecurrentdateandtimefromtheusersession,withadatatypeofTIMESTAMPWITHTIMEZONEWhichfunctionwilldothis?A)CURRENTDATEB)CURRENT_TIMESTAMPC)SYSDATED)LOCALTIMESTAMP答案:B解析:(none)Explanation說明/參考:[單選題]38.Examinethisstatementwhichexecutessuccessfully:CREATEviewemp80ASSELECTFROMemployeesWHEREdepartment_id=80WITHCHECKOPTION;WhichstatementwillviolatetheCHECKconstraint?A)DELETEFROMemp80WHEREdepartment_id=90;B)SELECTFROMemp80WHEREdepartment_id=90;C)SELECTFROMemp80WHEREdepartment.id=80;D)UPDATEemp80SETdepartment.1d=80;WHEREdepartment_id=90;答案:D解析:(none)Explanation說明/參考:[單選題]39.ExaminetheBRICKStable:COLOURSHAPEWEIGHTRedcube5Redcylinder10Bluecube15Bluecylinder20Youwritethisquery:SELECTFROMbricksb1CROSSJOINbricksb2WHEREb1.Weight<b2.Weight:Howmanyrowswillthequeryreturn?A)1B)16C)10D)6E)4F)0答案:D解析:(none)Explanation說明/參考:[單選題]40.ExaminethedataintheEMPLOYEEStable:EMPLOYEE_IDLAUTNANEMONTHLYSALARYMONTTHLYCOMEHISSIOMPCT101Kochhar24000<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解析:(none)Explanation說明/參考:[單選題]41.ExaminethedescriptionoftheENPLOYEStable: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解析:(none)Explanation說明/參考:[單選題]42.ExaminethedescriptionoftheEMPLOYEEStable: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解析:(none)Explanation說明/參考:[單選題]43.Examinethisquerywhichexecutessuccessfully;Selectjob,deptnofromempUnionallSelectjob,deptnofromjobs_history;Whatwillbetheresult?A)Itwillreturnrowsfrombothselectstatementsaftereliminatingduplicaterows.B)Itwillreturnrowscommontobothselectstatements.C)Itwillreturnrowsbothselectstatementsincludingduplicaterows.D)Itwillreturnrowsthatarenotcommontobothselectstatements.答案:C解析:(none)Explanation說明/參考:[單選題]44.ExaminethedataintheINVOICEStable:INVOICE_IDCURRENCY_CODERAISED_DATE1EUR01-JAN-20192USD01-FEB-20193JPY01-MAR-2019ExaminethedataintheCURRENCIEStable:CURRENCY_CODEJPYGPBCADEURUSDWhichqueryreturnsthecurrenciesinCURRENCIESthatarenotpresentinINVOICES?A)SELECTcurrency_codeFROMcurrenciesMINUSSELECTcurrency_codeFROMinvoices;B)SELECT*FROMcurrenciesWHERENOTEXISTS(ELECTNULLFROMinvoicesWHEREcurrency_code=currency_code);C)SELECTcurrency_codeFROMcurrenciesINTERSECTSELECTcurrency_codeFROMinvoices;D)SELECT*FROMcurrenciesMINUSSELECT*FROMinvoices;答案:A解析:(none)Explanation說明/參考:[單選題]45.Examinethesestatementswhichexecutesuccessfully: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解析:(none)Explanation說明/參考:[單選題]46.Examinethisquery: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解析:(none)Explanation說明/參考:[單選題]47.Inyoursession,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;答案:C解析:(none)Explanation說明/參考:[單選題]48.ExaminethisStatementwhichreturnsthenameofeachemployeeandtheirmanager,SELECTe。lastnameASemp,,m。last_nameASmgrFROMemployeeseJOINmanagersmONe。manager_id=m.employee_idORDERBYemp;Youwanttoextendthequerytoincludeemployeeswithnomanager.WhatmustyouaddbeforeJOINtodothis?A)CROSSB)FULLOUTERC)LEFTOUTERD)RIGHTOUTER答案:C解析:(none)Explanation說明/參考:[單選題]49.Whichistrueabouttheround,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解析:(none)Explanation說明/參考:[單選題]50.Examinethisquery:SELECTINTERVAL'100'MONTHDURATIONFROMDUAL;Whatwillbetheoutput?A)DURATION+08-04B)DUFATION+100C)DURATION+08D)anerror答案:A解析:(none)Explanation說明/參考:將返回8年零四個月年轉(zhuǎn)月[單選題]51.Whichstatementwillreturnacomma-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解析:(none)Explanation說明/參考:SELECTdeptno,LISTAGG(ename,',')WITHINGROUP(ORDERBYename)ASemployee_listFROMempGROUPBYdeptno;10CLARK,KING,MILLER20FORD,JONES,SMITH30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD[單選題]52.Examinethesestatements:CREATETABLEalter_test(c1