OracleOCP认证考试练习真题题库三

试卷科目：OracleOCP认证考试练习OracleOCP认证考试练习真题题库三PAGE"pagenumber"pagenumber/SECTIONPAGES"numberofpages"numberofpagesOracleOCP认证考试练习真题题库三第1部分：单项选择题，共20题，每题只有一个正确答案,多选或少选均不得分。[单选题]1.ExaminethedescriptionoftheproductstableNamenull?TypePROD_IDnotnullNUMBERPROD_NAMEVARCHAR2(40)COSTNUMBER(8,2)RELEASE_DATEDATEWhichqueryisvalid?A)SELECTprodid,AVG(MAX(cost))FROMproductsGROUPBYprod_idB)SELECTprodid,MAX(AVG(cost))FROMproductsGROUPBYprod_idC)Selectprodid,releasedate,SUM(cost)FROMproductsGROUPBYprod_idD)SELECTprodid,releasedate,SUM(cost)FROMproductsGROUPBYprodid,release_date答案:D解析:[单选题]2.ExaminethedescriptionoftheSATES1tableNAMENULLTYPESALES_IDNOTNULLNUMBERSTORE_IDNOTNULLNUMBERITEMS_IDNUMBERQUANTITYNUMBERSALES_DATEDATESATES2isatablewiththesamedescriptionasSATES1，Somesalesdataiscontainederroneouslyinbothtables，YoumustdisplayrowsfromSATES1andSATES2andwishtoseetheduplicatestoo,Whichsetoperatorgeneratestherequiredoutput?A)MINUSB)UNIONALLC)SUBTRACTD)UNIONE)INTERSECT答案:E解析:[单选题]3.ExaminethedescriptionoftheEMPLOYEEStableNIS_DATEFORMATissettoDD-MON-YYWhichqueryrequiresexplicitdatatypeconversion?A)SELECTjoindateFROMemployeesWHEREjoindate>'10-02-2018';B)SELECTjoindate||''||salaryFROMemployeesC)SELECTsalary+'120.50'FROMemployeesD)SELECTSUBSTR(join_date,1,2)-10FROMemployeesE)SELECTjoindate+'20'FROMemployees答案:A解析:[单选题]4.YourdatabaseinstanceisstartedwithanSPFILEAPFILEisalsoavailableYouexecutethiscommandALTERSYSTEMSETDBCACHESIZE=100KWhereisthevaluechanged?A)intheSPFILEandPFTIEB)intheSPFILE,PFILE,andmemoryC)onlyintheSPFILED)intheSPFILEandinmemoryE)onlyinmemory答案:D解析:[单选题]5.ViewtheExhibitsandexaminethestructureofthecostsandPROMOTIONStablesYouwanttodisplayPRODIDSwhosepromotioncostislessthanthehighestcostPRODIDinapromotiontimeintervalExaminethisSQLstatement:Selectprod_idfromcostswherepromo_idin(selectpromo_idfrompromotionswherepromo_costA)Itexecutessuccessfullyandgivestherequiredresult.B)ItgivesanerrorbecausetheGROUPBYclauseisnotvalidC)ItexecutessuccessfullybutdoesnotgivetherequiredresultD)ItgivesanerrorbecausetheALLkeywordisnotvalid答案:C解析:[单选题]6.Inthespfileofasingleinstancedatabase,LOCALLISTENERissettoLISTENER1.TheTNSNAMESORAfileinSORACLEHOME/network/admininthedatabasehomecontains:LISTENER1=(ADDRESS=(PROTOCOL=TCP)(HOST=host1abc.com)(PORT=1521))Whichstatementistrue?A)TherearetwolistenersnamedLISTENERandLISTENER1runningsimultaneouslyusingport1521onthesamehostasthedatabaseinstancesB)ThedefinitionforLISTENER1requiresaCONNECTDATAsectiontoenabledynamicserviceregistrationC)LISTENER1mustalsobedefinedintheLISTENER.ORAfiletoenabledynamicserviceRegistrationD)TheLREGprocessregistersservicesdynamicallywiththeLISTENER_1listenerE)Dynamicserviceregistrationcannotbeusedforthisdatabaseinstance答案:A解析:[单选题]7.Whichstatementistrueaboutdatabaselinks?A)Adatabaselinkcreatedinadatabaseallowsaconnectionfromthatdatabase'sinstancetothetargetdatabase'sinstance,butnotviceversaB)PrivatedatabaselinkcreationrequiresthesameusertoexistinboththelocalandtheremotedatabasesC)ApublicdatabaselinkcanbeusedbyauserconnectedtothelocaldatabaseinstancetoconnecttoanyschemaintheremotedatabaseinstanceD)ApublicdatabaselinkcanbecreatedonlybysysE)AdatabaselinkcanbecreatedonlybetweentwoOracledatabases答案:A解析:（该题有争议，很多人选E，A是笔者的选择）[单选题]8.TheCUSTOMERStablehasaCUST_LASTNAMEcolumnofdatatypeVARCHAR2ThetablehastworowswhoseCUST_LASTNAMEvaluesareAndersonandAussonWhichqueryproducesoutputforCUST_LASTNAMEcontainingderforthefirstrowandAusforthesecond?A)SELECTREPLACE(SUBSTR(cust_last_name,-3),'AN',"O')FROMcustomers;B)SELECTINITCAPREPLACE(TRIM('SONFROMcust_last_namE.,'AN,'O'))FROMcustomers;C)SELECTREPLACE(TRIM(TRAILING"SON'FROMcustlastnamE.,'AN,'O')FROMcustomersD)SELECTREPLACE(REPLACE(custlastname,'son',''),'An','O')FROMcustomers;答案:D解析:[单选题]9.Whatistrueaboutnon-equijoinstatementperformance?A)Thejoinsyntaxusedmakesnodifferencetoperformance.B)TheBETWEENconditionusedwithannon-equijoinsometimesperformsbetterthanusingThe>=and<=conditionsC)TheBETWEENconditionusedwithannon-equijoinalwaysperformsbetterthanwhenusingThe>=and<=conditionsD)TheOraclejoinsyntaxperformsbetterthanthesol:1999compliantANSIjoinsyntaxE)TheOraclejoinsyntaxperformslesswellthanthesol:1999compliantANSIjoinsyntax答案:A解析:(两种join此法笔者认为并无性能上差距)[单选题]10.ExaminethedescriptionofthePRODUCTINEORMATTONtableNameNull7TypePROD_IDNOTNULLNUMBER(2)PROD_NANEVARCHAR2(10)LIST_PRICENUMBER(6,2Whichqueryretrievesthenumberofproductswithanulllistprice?A)SELECTCOUNT(listprice)FROMproductinformationWHERElistpriceNULLB)SELECTCOUNT(listprice)FROMproductinformationWHERElistpriceIsNULL.C)SELECTCOUNT(DISTINCTlistprice)PROMproductinformationWHERElistpriceIsNULLD)SELECTCOUNT(NVL(listprice,0))FROMproductinformationWHERElistpriceIsNULL答案:D解析:[单选题]11.YouexecutethiscommandCREATEBIGFILETABLESPACEbig_tbsDATAFILE'/u01/oracle/data/big_f1.dbf'SIZE20G;Sufficientstorageisavailableinfilesystem/u01Whichtwostatementsaretrueaboutthebig_tbsTablespace?(ChoosetwoA)AUTOEXTENDispossibleforthedatafileB)ItmustbebiggerthanthelargestSMALLFILEtablespaceC)AdditionaldatafilesmaynotbeaddedD)Itwillbeadictionary-managedtablespacebydefaultE)Itwillalwayshavea32Kblocksize答案:C解析:[单选题]12.WhichstatementistrueabouttheINTERSECToperatorusedincompoundQueries?A)MultipleINTERSECToperatorsarenotpossibleinthesameSQLstatementB)ItprocessesNULLSintheselectedcolumnsC)INTERSECTisoflowerprecedencethanUNIONorUNIONALLD)ItignoresNULLS答案:B解析:[单选题]13.AdatabaseisconfiguredtouseautomaticundomanagementwithtemporaryundoenabledAnUPDATEisexecutedonatemporarytable.WhereistheUNDOstored?A)intheundotablespaceB)intheSYSAUXtablespaceC)intheSGAD)inthePGAE)inthetemporarytablespace答案:E解析:[单选题]14.Youhavebeentaskedtocreateatableforabankingapplication.Oneofthecolumnsmustmeetthreerequirements:BestoredinaformatsupportingdatearithmeticwithoutusingconversionfunctionsStorealoanperiodofupto10yearsBeusedforcalculatinginterestforthenumberofdaystheloanremainsunpaidWhichDatatypeshouldyouuse?A)INTERVALYEARTOMONTHB)INTERVALDAYTOSECONDC)TIMESTAMPWITHLOCALTIMEZONED)TIMESTAMPE)TIMESTAMPWITHTIMEZONE答案:B解析:[单选题]15.Inthespfileofasingleinstancedatabase,LOCALLISTENERissettoLISTENER.1TheTNSNAMES.ORAfileinSORACLEHOME/network/admininthedatabasehomecontainsLISTENER_1=(ADDRESS=(PROTOCOL=TCP)(HOST=)(POPT=1521))Whichstatementistrue?A)DynamicserviceregistrationcannotbeusedforthisdatabaseinstanceB)TheLREGprocessregistersservicesdynamicallywiththeLISTENER1listenerC)LISTENER_1mustalsobedefinedintheLISTENER.ORAfiletoenabledynamicserviceregistrationD)TherearetwolistenersnamedLISTENERandLISTENER1runningsimultaneouslyusingport1521onthesamehostasthedatabaseinstancesE)ThedefinitionforLISTENER1requiresaCONNECTDATAsectiontoenabledynamicservice答案:D解析:[单选题]16.YouwanttowriteaquerythatpromptsfortwocolumnnamesandtheWHEREconditioneachtimeitIsexecutedinasessionbutonlypromptsforthetablenamethefirsttimeitisexecuted.ThevariablesusedinyourqueryareneverundefinedinyoursessionWhichquerycanbeused?A)SELECT&&col1&&co2FROM&tableWHERE&&condition=&&condB)SELECT&col1&COI2FROM&&tableWHERE&conditionC)SELECT&col1&CO12FROM&tableWHERE&conditionD)ELECT&&col1&&coFROM&tableWHERE&&condition=&condE)SELECT&&col1&&COL2FROM&tableWHERE&&condition答案:B解析:[单选题]17.ExaminethedescriptionoftheCUSTOMERStableNameNull?TypeCUST_IDNOTNULLVARCHAR2(6)FIRST_NAMEVARCHAR2(50)LAST_NAMENOTNULLVARCHAR2(50)ADDRESSVARCHAR2(50)CITYVARCHAR2(25)YouwanttodisplaydetailsofallcustomerswhoresideincitiesstartingwiththeletterDfollowedbyatleasttwocharactersWhichquerycanbeused?A)SELECT*FROMcustomersWHEREcityLIKE?D_%?;B)SELECT*FROMcustomersWHEREcity=?%D_?;C)SELECT*FROMcustomersWHEREcityLIKE?D_?;D)SELECT*FROMcustomersWHEREcity=?D_%?;答案:A解析:[单选题]18.YouwanttousetablecompressionsuitableforOLTPthatwill:1）CompressrowsforallDMLstatementsonthattable2）MinimizetheoverheadsassociatedwithcompressionWhichcompressionoptionisbestsuitedforthis?A)COLUMNSTORECOMPRESSFORQUERYLOWB)ROWSTORECOMPRESSBASICC)COLUMNSTORECOMPRESSFORARCHIVELOWD)COLUMNSTORECOMPRESSFORARCHIVEHIGHE)ROWSTORECOMPRESSADVANCED答案:E解析:[单选题]19.YourdatabaseinstanceisstartedwithaPFILE.Examinetheseparameters:NAMETYPEVALUEMemory_max_targetbiginteger0Memory_targetbiginteger0Sga_max_sizebiginteger2GSga_targetbiginteger2GYouwanttoincreasethesizeofthebuffercache.FreememoryisavailabletoincreasetheSizeofthebuffercache.Youexecutethecommand:SQL>ALTERSYSTEMSETDB_CACHE_SIZE=1024M;Whatistheoutcome?A)ThevalueischangedonlyinthePFILEandtakeseffectatthenextinstancestartupB)ThevalueischangedforthecurrentinstanceandinthePFILEC)ItfailsbecausetheSCOPEclauseismissingD)Changeisappliedtothecurrentinstance,butdoesnotpersistafterinstancerestart答案:D解析:[单选题]20.ExaminethedescriptionoftheCUSTOMERStable:NameNullTypeCUST_IDNOTNULLNUMBERCUST_FIRST_NAMENOTNULLVARCHAR2(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_incoms_levelISNOTNULLANDdue_amountISNOTNULL;B)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMOUNTFROMcustomersWHEREcust_income_level!=NULLANDcust_credit_level!=NULL;C)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMOUNTFROMcustomersWHEREcust_income_level<>NULLANDdue_amount<>NULL;D)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMOUNTFROMcustomersWHEREcust_income_level!=NULLANDdue_amount!=NULL;E)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMOUNTFROMcustomersWHEREcust_income_levelISNOTNULLANDcust_credit_limitISNOTNULL;答案:E解析:第2部分：多项选择题，共73题，每题至少两个正确答案,多选或少选均不得分。[多选题]21.Whichtwostatementsaretrueaboutspace-savingfeaturesinanOracleDatabase?A)AnindexcreatedwiththeUNUSABLEattributehasnosegment.B)PrivateTemporaryTables(PTTs)storemetadatainmemoryonly.C)PrivateTemporaryTables(PTTs)whenused,arealwaysdroppedatthenextcommitstatement.D)AnindexthatisalteredtobeUNUSABLEwillretainitssegmentE)Atablethatistruncatedwillalwayshaveallofitsextentsremoved答案:AB解析:[多选题]22.YoucurrentlyhaveanactivetransactioninyoursessionandhavebeengrantedselectaccesstoV$TRANSACTIONExecuting:selectxid,statusfromv$transaction;Inyoursessionreturns:XIDSTATUS0A0007000A070000ACTIVEInwhichthreesituationswillre-executingthisquerystillreturnarowbutwithadifferentXIDIndicatinganewtransactionhasstarted?A)aftersuccessfullyexecutingaTRUNCATEstatementfollowedbyaDMLstatementB)aftersuccessfullyexecutingaCREATETABLEASSelectstatementfollowedbyaSELECTFORUPDATEstatementC)aftersuccessfullyexecutingaCREATETABLEstatementfollowedbyaCREATEINDEXstatementD)aftersuccessfullyexecutingacommitorROLLBACKfollowedbyaDMLstatementE)aftersuccessfullyexecutingaDMLstatementfollowingafailedDMLstatementF)aftersuccessfullyexecutingacommitorROLLBACKfollowedbyaSelectstatement答案:ABD解析:[多选题]23.ExaminethedescriptionoftheBOOKSTRANSACTIONStable:NAMENULL?TYPETRANSACTION_IDNOTNULLVARCHAR2(6)TRANSACTION_TYPEVARCHAR2(3)BORROWED_DATEDATEBOOK_IDVARCHAR2(6)NUMBER_IDVARCHAR2(6)ExaminethispartialSQLstatement:Select*frombooks_transactions;WhichtwoWHEREconditionsgivethesameresult?A)WHEREborroweddate=SYSDATEAND(transaction_type='RM'AND(member_id'A101'ORmemberid='A102'))B)WHEREborroweddate=SYSDATEAND(transactiontype='RM'ORmemberidIN('A101','A102'))C)WHEREborroweddate=SYSDATEAND(transactiontype='RM'ANDmember_id'A101'ORmemberid='A102')D)WHEREborroweddate=SYSDATEANDtransactiontype='RM'ORmemberidIN('A101','A102')E)WHERE(borroweddate=SYSDATEANDtransactiontype='RM')ORmemberidIN?A101','A1021)答案:DE解析:[多选题]24.Whichthreestatementsaretrue?NAMEnull?TypeTRANSACTION_IDNOTNULLVARCHAR2(6)TRANSACTION_DATEDATEAMOUNTNUMBER(10,2)CUSTOMER_IDVARCHAR2(6)Nowexaminethissequenceofstatementissuedinanewsession:INSERTINTObooksVALUES('ADV112','AdventuresofTomSawyer',null,null);SAVEPOINTa;DELETEFROMbooks;ROLLBACKTOSAVEPOINTa;ROLLBACK;A)ThesecondROLLBACKcommandrestorestherowthatwasinsertedB)ThefirstROLLBACKcommandleavestheinsertedrowlockedC)ThesecondROLLBACKcommandrestoresthe100rowsthatwereinthetableoriginallyD)ThesecondROLLBACKcommandrollsbacktheROLLBACKTOSAVEPOINTacommandE)ThefirstROLLBACKcommandleavesthetable's100originalrowslockedF)ThefirstROLLBACKcommandrestoresthe100rowsthatwereinthetableoriginally答案:BDF解析:[多选题]25.ExaminethedescriptionoftheMEMBERstable:NAMENULL?TYPEMEMBER_IDNOTNULLVARCHAR2(6)FIRST_NAMEVARCHAR2(50)LAST_NAMENOTNULLVARCHAR2(50)ADDRESSVARCHAR2(50)CITYVARCHAR2(25)Examinethepartialquery:Selectcity,last_nameLNAMEfrommembers...;YouwanttodisplayallcitiesthatcontainthestringAN.Thecitiesmustbereturnedinascendingorderwiththelastnamesfurthersortedindescendingorder.Whichtwoclausesmustyouaddtothequery?A)ORDERBY1,LNAMEDESCB)ORDERBY1,2C)WHEREcityIN(?%AN%?)D)WHEREcity=?%AN%?E)WHEREcityLIKE?%AN%?F)ORDERBYlastnameDESC,cityASC答案:AE解析:[多选题]26.YouwanttoapplytheprincipleofLeastPrivilegeinallyourlivedatabases.OneofyourrequirementsistorevokeunnecessaryprivilegesfromalluserswhohavethemusingPrivilegeAnalysis.WhichtwoaretypesofanalysesthatcanbedoneusingtheDBMSPRIVILEGECAPTUREPackage?A)analysisofprivilegesthatauserhasonotherschema'sobjectsB)analysisofprivilegesthatauserhasontheirownschemaobjectsC)analysisofprivilegesgrantedindirectlytoarolethatarethenusedbyauserwhohasbeenGrantedthatroleD)analysisofprivilegesgranteddirectlytoarolethatarethenusedbyauserwhohasbeenGrantedthatroleE)analysisofallprivilegesusedbythesysuser答案:CD解析:[多选题]27.WhichtwostatementsaretrueaboutundosegmentsandtheuseofundobytransactionsinanOracledatabaseinstance?A)Asingletransactionmayusemultipleundosegmentssimultaneously.B)UndosegmentscanbestoredintheSYSAUXtablespaceC)UndosegmentscanextendwhenatransactionfillsthelastextentoftheundosegmentD)UndosegmentscanwraparoundtothefirstextentwhenatransactionfillsthelastextendoftheUndosegmentE)UndosegmentscanbestoredintheSYSTEMtablespace答案:CE解析:[多选题]28.InthePROMOTIONStable,thePROMO_BEGIN_DATEcolumnisofdatatypeDATEandthedefaultdateformatisDD-MON-RR。WhichtwostatementsaretrueaboutexpressionsusingPROMO_BEGIN_DATEcontainedinaquery?A)PROMO_BEGIN_DATE-SYSDATEwillreturnanumberB)TO_DATE(PROMO_BEGIN_DATE*5)willreturnadateC)TO_NUMBER(PROMO_BEGIN_DATE-5)willreturnanumberD)PROMO_BEGIN_DATE-SYSDATEwillreturnanerrorE)PROMO_BEGIN_DATE-5willreturnadate答案:AE解析:[多选题]29.WhichthreestatementsaretrueaboutthenamingmethodsandtheirfeaturessupportedbyOracledatabaseusedtoresolveconnectioninformation?A)DirectoryNamingcanbeusedifConnect-TimeFailoverisrequiredB)LocalNamingrequiressettingtheTNSADMINenvironmentvariableontheclientsideC)LocalnamingcanbeusedifConnect-TimeFailoverisrequiredD)DirectoryNamingrequiressettingtheTNSADMINenvironmentvariableontheclientsideE)AclientcanconnecttoanOracledatabaseinstanceevenifnoclientsidenetworkadminhasbeenconfigureF)EasyConnectsupportsTCP/IPandSSL答案:ACE解析:[多选题]30.ExaminethedescriptionofthePRODUCTDETAILStable:NAMENULLTYPEPRODUCT_IDNOTNULLNUMBER(2)PRODUCT_NAMENOTNULLVARCHAR2(25)PRDUCT_PRICENUMBER(8,2)EXPIRY_DATEDATEWhichtwostatementsaretrue?A)PRODUCT_NAMEcannotcontainduplicatevaluesB)EXPIRY_DATEcontainstheSYSDATEbydefaultifnodateisassignedtoitC)EXPIRY_DATEcannotbeusedinarithmeticexpressionsD)PRODUCT_PRICEcanbeusedinanarithmeticexpressionevenifithasnovaluestoredinit.E)PRODUCT_PRICEcontainsthevaluezerobydefaultifnovalueisassignedtoitF)PRODUCT_IDcanbeassignedthePRIMARYKEYconstraint答案:DF解析:[多选题]31.WhichthreestatementsaretrueaboutconnectionstringsandservicenamesusedtoconnecttoanOracledatabaseinstance?A)AconnectionstringmustincludetheSIDofadatabaseinstance.B)AsingleconnectionstringcanrefertomultipledatabaseinstancesC)Aconnectionstringincludingaservicenamemustbedefinedinthetnsnames.orafileD)AservicenameiscreatedbyalistenerE)Differentconnectionstringsinthesametnsnames.orafilecancontainthesameservicenameHostandportparametersF)Asingledatabaseinstancecansupportconnectionsformultipleservicenames答案:BEF解析:[多选题]32.WhichthreestatementsaretrueaboutDeferredSegmentCreationinOracledatabases?A)Itisthedefaultbehaviorfortablesandindexes.B)Itissupportedforsys-ownedtablescontainedinlocallymanagedtablespacesC)SessionsmaydynamicallyswitchbackandforthfromDEFERREDtoIMMEDIATEsegmentCreation.D)IndexesinherittheDEFERREDorIMMEDIATEsegmentcreationattributefromtheirparenttableE)ItissupportedforIndexOrganizedTables(IOTs)containedinlocallymanagedtablespaces.答案:CDE解析:[多选题]33.WhichtwoOracledatabasespacemanagementfeaturesrequiretheuseoflocallymanagedTablespaces?A)OracleManagedFiles(OMF)B)Server-generatedtablespacespacealertsC)OnlinesegmentshrinkD)FreespacemanagementwithbitmapsE)Automaticdatafileextension(AUTOEXTEND)答案:CD解析:[多选题]34.WhichtwoaretrueaboutaSQLstatementusingSEToperatorssuchasUNION?A)Thedatatypeofeachcolumnreturnedbythesecondquerymustexactlymatchthedatatypeofthecorrespondingcolumnreturnedbythefirstquery.B)Thenumber,butnotnames,ofcolumnsmustbeidenticalforallSELECTstatementsinthequery.C)ThenamesandnumberofcolumnsmustbeidenticalforallSELECTstatementsinthequery.D)Thedatatypeofeachcolumnreturnedbythesecondqueryisautomaticallyconvertedtothedatatypeofthecorrespondingcolumnreturnedbythefirstquery.E)Thedatatypegroupofeachcolumnreturnedbythesecondquerymustmatchthedatatypegroupofthecorrespondingcolumnreturnedbythefirstquery.答案:BE解析:[多选题]35.Whichtwoarebenefitsofexternaltables?A)TheysupportUPDATESwhichtransparentlyupdatesrecordsinthefilesystemasiftheyweretableB)TheycanbequeriedwhilethedatabaseisintheMOUNTstatelikedynamicperformanceviewsC)TheysupportDELETEswhichtransparentlydeletesrecordsinthefilesystemasiftheyweretablerowsD)TheresultsofacomplexjoinoraggregatingfunctionorbothcanbeunloadedtoafilefortransportationtoanotherdatabaseE)Theycanbequeried,transformed,andjoinedwithothertableswithouthavingtoloadthedatafirst答案:DE解析:[多选题]36.WhichthreestatementsaretrueregardingtheUNIONandUNIONALLoperators?A)ThenumberofcolumnsselectedbythefirstSELECTstatementcanbegreaterthanthenumberselectedinsubsequentSELECTstatementsB)DuplicatesareeliminatedautomaticallybytheUNIONALLoperatorC)ThenumberofcolumnsselectedineachSELECTstatementmustbeidenticalD)NULISarenotignoredduringduplicatecheckingE)ThenamesofcolumnsselectedineachSELECTstatementmustbeidenticalF)ThenamesofcolumnsselectedineachSELECTstatementcanbeidenticalG)DuplicatescanoptionallybeeliminatedbytheUNIONoperator答案:CDF解析:[多选题]37.TheSALEStablehascolumnsPROD_IDandQUANTITY_SOLDofdatatypeNUMBER.Whichtwoqueriesexecutesuccessfully?A)SELECTprod_idFROMsalesWHEREquantity_sold>55000ANDCOUNT(*)>10GROUPBYprod_idHAVINGCOUNT(*)>10;B)SELECTCOUNT(prod_iD)FROMsalesGROUPBYprodidWHEREquantity_sold>55000C)SELECTprodidFROMsalesWHEREquantitysold>55000ANDCOUNT(*)>10GROUPBYCOUNT(*)>10:D)SELECTprodidFROMsalesWHEREquantitysold>55000GROUPBYprodidHAVINGCOUNT(*)>10;E)SELECTCOUNT(prod_iD)FROMsalesWHEREquantity_sold>55000GROUPBYprodid;答案:DE解析:[多选题]38.WhichtwostatementsaretrueaboutINTERVALdatatypes?A)INTERVALYEARTOMONTHcolumnssupportyearlyintervalsB)TheYEARfieldinanINTERVALYEARTOMONTHcolumnmustbeapositivevalueC)ThevalueinanINTERVALDAYTOSECONDcolumncanbecopiedintoanINTERVALYEARTOMONTHcolumnD)INTERVALYEARTOMONTHcolumnsonlysupportmonthlyintervalswithinasingleyearE)INTERVALDAYTOSECONDcolumnssupportfractionsofsecondsF)INTERVALYEARTOMONTHcolumnsonlysupportmonthlyintervalswithinarangeofYears答案:AE解析:[多选题]39.WhichtwostatementsaretrueabouttheOraclejoinandANSIjoinsyntax?A)TheOraclejoinsyntaxperformsbetterthantheSQL:1999compliantANSIjoinsyntax.B)TheOraclejoinsyntaxlackstheabilitytodoouterjoinsC)TheSQL:1999compliantANSIjoinsyntaxsupportscreationofaCartesianproductoftwotablesD)TheOraclejoinsyntaxsupportscreationofaCartesianproductoftwotablesE)TheOraclejoinsyntaxperformslesswellthantheSQL:1999compliantANSIjoinsyntax答案:CD解析:[多选题]40.ExaminethiscommandSQL>ALTERTABLEORDERSSHRINKSPACECOMPACTWhichtwostatementsaretrue?A)QueriesandDMLstatementsareallowedonORDERswhiletheSHRINKisexecutingB)DependentindexesbecomeUNUSABLEC)TheSHRINKoperationcausesrowstobemovedtoemptyspacestartingtowardtheendoftheORDERssegmentD)TheSHRINKoperationcausesrowstobemovedtoemptyspacestartingfromthebeginningoftheORDERssegment.E)OnlyqueriesareallowedonORDERswhiletheSHRINKisexecutingF)Thehigh-watermark(HWM)ofORDERsisadjusted答案:AC解析:[多选题]41.WhichtwostatementsaretrueabouttheSETVERIFYONcommand?A)Itdisplaysvaluesforvariablesprefixedwith&&B)ItdisplaysvaluesforvariablescreatedbytheDEFINEcommandC)ItcanbeusedinSQLDeveloperandSQL*PlusD)ItcanbeusedonlyinSQL*PlusE)ItdisplaysvaluesforvariablesusedonlyintheWHEREclauseofaquery答案:BC解析:[多选题]42.WhicharetwooftheaccountmanagementcapabilitiesthatcanbeconfiguredusingOracleprofiles?A)thenumberofdaysforwhichanaccountmaybeloggedintooneormoresessionsbeforeitislockedB)thenumberofdaysforwhichanaccountmaybeinactivebeforeitislockedC)themaximumnumberofsessionspermittedforauserbeforethepasswordmustbechangedD)theabilitytopreventapasswordfromeverbeingreusedE)themaximumamountofcputimeallowedforauserssessionsbeforetheiraccountislocked答案:BD解析:[多选题]43.Whichthreestatementsaretrueregardingsinglerowsubqueries?A)TheymustbeplacedontheleftsideofthecomparisonoperatororconditionB)TheymustreturnarowtopreventerrorsintheSQLstatementC)TheycanbeusedintheHAVINGclause.D)ASQLstatementmayhavemultiplesinglerowsubqueryblocksE)TheycanbeusedintheWHEREclauseF)Theymustbeplacedontherightsideofthecomparisonoperatororcondition答案:CDE解析:[多选题]44.Youexecutethiscommand:Duringtheexportoperation,youdetachfromthejobbyusingcrtl+candthenexecutethisCommandExport>STOPJOB-immediateAreyousureyouwishtostopthejob([yes]/no):yesWhichtwostatementsaretrueaboutthejob?A)YoucanreattachtoitandmonitoritB)YoucanreattachtoitbutnotmonitoritC)ItterminatesD)ItcontinuestoruninthebackaroundE)Itispausedandcanberesumed答案:AE解析:[多选题]45.WhichthreestatementsaretrueabouttheAutomaticDiagnosticRepository(ADR)?A)Itisafile-basedrepositoryheldoutsideanydatabaseB)TheADRbaseisspecifiedintheDIAGNOSTICDESTdatabaseparameteC)ItisonlyusedforOracleDatabasediagnosticinformationD)ItisheldinsideanOracledatabaseschemaE)Itcanbeusedforproblemdiagnosisofadatabasewhenthatdatabase'sinstanceisdown答案:ABE解析:[多选题]46.ExaminethiscommandALTERDATABASEMOVEDATAFILE'/U01/SALES01.DBF'TO'/U02/SALES02.DBF';Whichtwostatementsaretrue?A)DML,maybeperformedontableswithoneormoreextentsinthisdatafileduringtheexecutionofthiscommandB)ItoverwritesanyexistingfilewiththenameSALES02.DBFin/u02bydefault.C)TableswithoneormoreextentsinthisdatafilemaybequeriedduringtheexecutionofthiscommandD)Compressedobjectsinsales01.DBFwillbeuncompressedinSALES02.DBFafterthemoveE)The"To"clausecontainingthenewfilenamemustbespecifiedevenifOracleManagedFiles(OMF)isused答案:AC解析:[多选题]47.Whichtwostatementsaretrueaboutsubstitutionvariables?A)Asubstitutionvariableusedtopromptforacolumnnamemustbeenclosedindoublequotationmarks.B)AsubstitutionvariableprefixedwithsalwayspromptsonlyonceforavalueinasessionC)AsubstitutionvariableusedtopromptforacolumnnamemustbeenclosedinsinglequotationmarksD)Asubstitutionvariableprefixedwith&&promptsonlyonceforavalueinasessionunlessitissettoundefinedinthesessionE)AsubstitutionvariablecanbeusedonlyinaSELECTstatementF)Asubstitutionvariablecanbeusedwithanyclauseinaselectstatement答案:DF解析:[多选题]48.ExaminethisdescriptionoftheTRANSACTIONStableWhichtwoSQLstatementsexecutesuccessfully?A)SELECTcustomer_idCUSTID,transactiondateTRANS_DATE,amount+100DUESFROMTransactionsB)SELECTcustomer_idAS"CUSTOMER-ID",transaction_dateASDATE,amount+100?DUES?FROMtransactionsC)SELECTcustomer_idAS?CUSTOMER-ID?,transaction_dateAsDATE,amount+100?DUES?FROMtransactionsD)SELECTcustomer_idAS"CUSTOMER-ID",transaction_dateAs"DATE",amount+100DUESFROMtransactionsE)SELECTcustomer_idASCUSTOMER-ID,transaction_dateAsTRANSDATE,amount100"DUESAMOUNT"FROMtransactions答案:AD解析:[多选题]49.WhichtwostatementsaretrueabouttheconfigurationanduseofUNDORETENTIONWithnoGUARANTEEDRETE