OCP认证082考试题库及答案.pdf

《OCP认证082考试题库及答案.pdf》由会员分享，可在线阅读，更多相关《OCP认证082考试题库及答案.pdf（63页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、（新）OCP认证08 2考试题库及答案一、单选题1.Viewt heExhibit s andexaminet hes t ru ct u reoft hecos t s andPROMOTIONSt abIesYou want t od i s p IayPRODIDSwhos ep romot i oncos t i s Ies s t hant heh i ghes t cos t PRODID i nap romot i ont i me i nt ervaI13 CO SI8NameTIMEJDPROMOJD二UMT_COSTUNIT PRICENuW?NOT NULLNOT NUL

2、LNOT HULLNOT NULLNOT NULLTypeDAYENUMBER 一NUMBER NUMBER0O万kU I/BER HoilTMtFROMOTXXJSN EIPROMOJOPROMO NAVENO1 KUU.|vARCHAH2(30)|KM fAm 阳;Hm 阳m 1PROMO SUEiCAIfcGORYJDNOT NUU.NUVBtRPROMO.CATtGORYKOI SULL VARCHAR2(30j阳6 V。一 匕 ATEGORY。SOT NULL NUMBERPR0U6 c d irPROMO BEGIN DAUNOT HULLlDATEPROMO DATt2 T SU

3、LL|DATtExami net h i s SQLs t at ementSELECT prod_idFROM costsWHERE proao_id IN(SELECT pr8O_idFROM promotionsWHERE promo-cost ALTERSYSTEMSETDB_CACHE_SIZ E=1024M;What i s t heou t e?Av ThevaIu e i s changedonIy i nt hePFILEandt ak es effect at t henext i ns t ances t art u pB、ThevaIu e i s changedfor

4、t hecu rrent i ns t anceand i nt hePFILEC、It fa i Is becau s et heSCOPEcIau s e i s m i s s i ngD、Change i s ap p I i edt ot hecu rrent i ns t ance,bu t does not p ers i s t aft er i ns t anceres t art答案：D4.Wh i chs t at ement i s t ru eabou t dat abas eI i nk s?A、Adat abas eIi nk creat edi nadat ab

5、as eaI Iows aconnect ionfromt hat dat abas esi ns t ancet ot heTarget dat abas es ins t ance,bu t not vicevers aB、Pr i vat edat abas eIi nk creat i onreq u i res t hes ameu s ert oex i s t i nbot ht heI ocaIandt heremot edat abas esCx Ap u bI i cdat abas eIi nk canbeu s edbyau s erconnect edt ot heI

6、 oca I dat abas e i ns tancet oconnect t oanys chema i nt heremot edat abas e i ns t anceDx Ap u bI icdat abas eIink canbecreat edonIybys ysE、Adat abas eIi nk canbecreat edonIybet weent woOracI edat abas es答案：c5.Wh i chs t at ement i s t ru eabou t t heINTERSECTop erat oru s ed i np ou ndq u er i es

7、?Ax Mu lt ip le!NTERSECTop erat ors arenot p os s ibIeint hes ameSQLs t at ementB、It p roces s es NULLS i nt hes eIect edcoIu mnsC、INTERSECT is oflowerp recedencet hanUNIONorUNIONALLD、It ignores NULLS答案：B6.You rdat abas e i ns t ance i s s t art edwi t hanSPFILEAPFILEis als oava i I ableYou execu t

8、et h i s mandALTERSYSTEMSETDBCACHESIZ E=100KWhere i s t hevaIu echanged?A、int heSPFILEandPFTIEBx i nt heSPFILE,PFILE,andmemoryC、onlyint heSPFILED、i nt heSPFILEand i nmemoryEx onlyinmemory答案：D7.Exami net hedes cr i p t i onoft hePRODUCTINEORMATTONt abIeNaa Null?TypPROD_ID NOT NULL NUMBER(2)PROD二NAMt

9、VARCHAK2(10)LIST二PRX3 NUMBER 2)Wh i chq u eryret r i eves t henu mberofp rodu ct s wi t hanu Illi s t p r i ce?A、SELECTCOUNT(Ii s t p r i ce)FROMp rodu ct i nformat i onWHEREIi s t p r i ceNULLB、SELECTCOUNT(Iis t p rice)FROMp rodu ct informat ionWHEREIis t p riceIs NULL,C、SELECTCOUNT(DI ST INCTIis t

10、 p rice)PROMp rodu ct informat ionWHEREIis t p riceIs NULL:D、SELECTCOUNT(NVL(Iis t p rice,0)FROMp rodu ct informat ionWHEREIis t p riceIs NULL答案：D8.Exami net hedes cr i p t i onoft heEMPLOYEESt abIeNISDATEFORMAT i s s et t oDD-MON-YYWh i chq u eryreq u i res exp I i c i t dat at yp econvers i on?A、S

11、ELECTj oi ndat eFROMemp Ioyees WHEREj oi ndat e10-02-2018;B、SELECTj oindat e|f|Is alaryFROMemp loyeesG SELECTs aIary+120.50FROMemp IoyeesD、SELECTSUBSTR(j oi n_dat e,1,2)-1OFROMemp IoyeesEx SELECTj oindat e+20FROMemp Ioyees答案：A9.What i s t ru eabou t non-eq u i Joi ns t at ement p erformance?A、Thej o

12、ins ynt axu s edmak es nod ifferencet op erformance.B、TheBETWEENcond i t ionu s edwi t hannon-eq u i j oins omet imes p erforms bet t ert hanu s i ngt he=and=andUcond i t i onsD、TheOracIej o i ns ynt axp erforms bet t ert hant hes oI:19 9 9 p Iiant ANSI j oins yntaxEx TheOracIej oi ns ynt axp erform

13、s Ies s weI It hant hes oI:19 9 9 p I iant ANSI j oins ynt ax答案：A10.Adat abas e i s conf i gu redt ou s eau t omat i cu ndomanagement wi t ht emp oraryu ndoenab IedAnUPDATE i s execu t edonat emp oraryt abIeWhere i s t heUNDOs t ored?Ax i nt heu ndot abI es p aceB、i nt heSYSAUXt abI es p aceCx int h

14、eSG AD、i nt hePG AE、i nt het emp oraryt abI es p ace答案：E11.Exami net hedes cr i p t ionoft heCUSTOMERSt abIe:NameNull?TypeCUST-IDNOT NULLNUMBERCUST_FIRST_NAMENOT NULLVARCHAR2(20)CV3T_IAST_NAMENOT NULLVARCKAR2(30)CU3T_INCXE_LEVELVARCHAR2(30)CUST CREDIT LIMITNUMBERForcu s t omers whos e i ne I eve Iha

15、s avaIu e,you want t od i s p Iayt hef i rs t nameanddu eamou nt as 5%Oft hei rcredit I imit.Cu s t omers whos edu eamou nt i s nu I Is hou Idnot bedi s p Iayed.Wh i chq u erys hou Idbeu s ed?A、SELECTcu s t _fi rs t _name,cu s t _credit _l imit*.05ASDUE_AM0UNTFROMcu s t omers WHEREcu s t _ins _level

16、ISNOTNULLANDdu e_amou nt I SNOTNULL;B、SELECTcu s t _fi rs t _name,cu s t _credit _l imit*.05ASDUE_AM0UNTFROMcu s t omers WHEREcu s t _i ne_leve I!=NULLANDcu s t _cred i t _leve I!=NULL;Cx SELECTcu s t _fi rs t _name,cu s t _credit _l imit*.05ASDUE_AM0UNTFROMcu s t omers WHEREcu s t _i ne_leve INULLA

17、NDdu e_amou nt ONULL;D、SELECTcu s t _fi rs t _name,cu s t _credit _l imit*.05ASDUE_AM0UNTFROMcu s t omers WHEREcu s t _i ne_leve I!=NULLANDdu e_amou nt!=NULL;Ex SELECTcu s t _fi rs t _name,cu s t _credit _limit*.05ASDUE_AM0UNTFR0Mcu s t omer s WHEREcu s t _ i ne_I eve I ISNOTNULLANDcu s t _cred i t

18、_I imit I SNOTNULL;答案：E12.You want t owr i t eaq u eryt hat p romp t s fort wocoIu mnnames andt heWHEREcond i t ioneacht imeit IsExecu t ed i nas es s i onbu t onIyp romp t s fort het abIenamet hef i rs t t i me i t i s execu tedThevar i abIes u s ed i nyou rq u eryareneveru ndef i ned i nyou rs es

19、s i onWh i chq u erycanbeu s ed?A、SELECT&co11&co2FR0M&t abIeWHERE&cond i t ion=&condB、SELECT&coI1&C012FR0M&t abIeWHERE&condit ionC、SELECT&coI1&C012FR0M&t ableWHERE&condit ionD、ELECT&co11&coFR0M&t abIeWHERE&cond i t ion=&condE、SELECT&co11&C0L2FR0M&t ableWHERE&condit ion答案：B13.Wh i chp res s i onmet h

20、od i s remendedforD i rect-Pat hIns ert op erat i ons?A、COLUMNSTOREPRESSADVANCEDB、ROWSTOREPRESSADVANCEDC、COLUMNSTOREPRESSBASICD、ROWSTOREPRESSBASIC答案：D14.You want t ou s et abI ep res s i ons u i t ab IeforOLTPt hat wiI I:1-p res s rows foraI IDMLs t at ement s ont hat t abIe2-Mi nimi zet heoverheads

21、 as s oci at edwit hp res s ionWh ichp res s ionop t ioni s bes ts u i t edfort h i s?A、COLUMNSTOREPRESSFORQUERYLOWB、ROWSTOREPRESSBASICC、COLUMNSTOREPRESSFORARCHIVELOWD、COLUMNSTOREPRESSFORARCHI VEH IG HE、ROWSTOREPRESSADVANCED答案：E15.Exami net hedes cr i p t ionoft heCUSTOMERSt abIeNameI WTypeCUSTJDNOT

22、 NULLVARCHAR2(6)nRST_NAMEVARCHAR2(50)LAST-NAMENOT NULLVARCHAR2(50)ADDRESSVARCHAR2(50)CITYVARCHAR2(25)You want t od i s p Iaydet a i I s ofa I Icu s t omers whores ideincit ies s t art i ngwi t ht helet t erDfollowedbyatLeas t t wocharact ers Wh i chq u erycanbeu s ed?A、SELECTFROMcu s t omers WHEREci

23、t yLIKEDB、SELECT*FROMcu s t omers WHEREC、SELECTFROMcu s t omers WHEREcit yLIKEDD、SELECTFROMcu s t omers WHEREcit y=D答案：A16.Whichi s t hedefau I t oo Iu mnorcoIu mns fors ort i ngou t p u t fromp ou ndq u er i es u si ngSETop erat ors s u chas INTERSECT i naSQLs t at ement?Ax t hef i rs t NUMBERorVAR

24、CHAR2coIu mn i nt heIas t s eIect oft hep ou ndq u eryBx t hef i rs t NUMBERcoIu mn i nt hef i rs t s eIect oft hep ou ndq u eryC、t hef i rs t vARCHAR2coIu mn i nt hef i rs t s eIect oft hep ou ndq u eryD、t hef i rs t colu mnint hefi rs t s eIect oft hep ou ndq u eryE、t hef i rs t coIu mn i nt heIas

25、 t s eIect s oft hep ou ndq u ery答案：D17.You havebeent as k edt ocreat eat abIeforabank i ngap p Ii cat i onOneoft hecoIu mns mu s t meet t hreereq u i rement s:Bes t ored i naformat s u p p ort i ngdat ear i t hmet i cwi t hou t u s i ngconvers i onfu nct ions St oreaIoanp er i odofu p t olOyearsBeu

26、 s edforcaIcu lat ingint eres t fort henu mberofdays t heIoanrema i ns u np a i dWh ichdat at yp es hou Idyou u s e?A、INTERVALYEARTOMONTHB、INTERVALDAYTOSECONDG TIMESTAMPWITHLOCALTIMEZ ONED、TIMESTAMPE、TIMESTAMPWITHTIMEZ ONE答案：B18.Int hes p f i Ieofas ingleins t ancedat abas e,LOCALLISTENERis s et t o

27、LISTENER1.TheTNSNAMESORAf i leinSORACLEHOME/net work/adminint hedat abas ehomecont ains:LISTENER仁(ADDRESS二(PROTOCOL=TCP)(H0ST=hos t 1abc.)(P0RT=1521)Wh i chs t at ement i s t ru e?Ax Therearet woIi s t eners namedLISTENERandLISTENER1ru nn i ngs imu It aneou s Iyu s i ngp ort l521ont hes amehos t as

28、t hedat abas e i ns t ancesB、Thedef init ionforLISTENER1req u i res aCONNECTDATAs ect iont oenabIedynamics erv i ceregi s t rat i onG LISTENER1mu s t aIs obedef inedint heLISTENER.ORAf i Iet oenabIedynamics erviceregis t rat ionDx TheLREG p roces s regis t ers s ervices dynamicallywit ht heLISTENER_

29、1Ii s t enerEx Dynami cs erv i ceregi s t rat i oncannot beu s edfort h i s dat abas e i ns t ance答案：A19.Int hes p f i Ieofas ingleins t ancedat abas e,LOCALLISTENERi s s et t oLISTENER.1TheTNSNAMES.ORAf i Ie i nSORACLEHOME/net work/adm i n i nt hedat abas ehomecont a insLISTENER=(ADDRESS(PROTOCOL T

30、CP)(HOST=hoMl abc com)(PORT 1521)Wh i chs t at ement i s t ru e?Ax Dynamics erviceregis t rat ioncannot beu s edfort his dat abas eins t anceB、TheLREG p roces s regis t ers s ervices dynamicallywit ht heLISTENERII i s t enerC、LISTENER_1mu s t aIs obedef inedint heLISTENER.ORAf i Iet oenabIedynamics

31、erviceregis t rat ionD、Therearet woIi s t eners namedLISTENERandLISTENER1ru nn i ngs imu It aneou s Iyu s i ngp ort 1521ont hes amehos t as t hedat abas e i ns t ancesThedef init ionforLISTENER1req u i res aCONNECTDATAs ect iont oenabIedynamics ervice答案：D20.TheCUSTOMERSt ablehas aCUST_LASTNAMEcoIu m

32、nofdat at yp eVARCHAR2Thet abIehas t worows whos eCUST_LASTNAMEvaIu es areAnders onandAu s s onWh i chq u eryp rodu ces ou t p u t forCUST_LASTNAMEcont a i n i ngoderfort hef i rs t rowandAu s fort heSecond?A、SELECTREPLACE(SUBSTR(cu s t _Ias t _name,-3),AN,0)FROMcu s t omers;B、SELECT INITCAPREPLACE(

33、TRIM(S0NFR0Mcu s t _Ias t _namE.,1 AN,0)FROMCu s t omers;C、SELECTREPLACE(TRIM(TRAILINGHS0NFR0Mcu s t Ias t namE.,*AN,O)FROMCu s t omersDxSELECTREPLACE(REPLACE(ou s t las t name,s on,),AN,O)FROMcu s t omers;答案：D21.Exami net hedes cr i p t i onoft hep rodu ct s t abIeName Null?PROD NAME VARCHAR2(40)CO

34、ST NUMBER(H Z)A、SELECTp rodid,AVG(MAX(cos t)FROMp rodu ct s G ROUPBYp rod.Bv SELECTp rodid,MAX(AVG(cos t)FROMp rodu ct s G ROUPBYp rodid:G SeIect p rod id,re I eas edat e,SUM(cos t)FROMp rodu ct s G ROUPBYp rod i dD、SELECTp rod id,re I eas edat e,SUM(cos t)FROMp rodu ct s G ROUPBYp rod id,re Ieaseda

35、t e答案：D多选题1.Whicht hrees t at ement s aret ru eabou t s eq u ences inas ingleins t anceOracIedatabas e?Av As eq u encenu mbert hat was aI Iocat edcanberoI Iedback ifat rans act ionfai IsBv As eq u encecanonIybedrop p edbyaDBAC、Seq u ences canaI ways havegap sDv As eq u encecan i s s u edu p I i cat

36、evaIu es.E、As eq u ences u naI Iocat edcachedvaIu es areIos t i ft he i ns t ances hu t s downF、Twoormoret abIes cannot havek eys generat edfromt hes ames eq u ence答案：CDE2.Wh i cht hrees t at ement s aret ru eabou t u s i ngSQL*PI u s?A、It canru ns cr i p t s p as s edt o i t byas heI Is cr i p t.B、

37、It canru ns cr i p t s ent eredat t heSQLp romp tC、It has bot hmand-l i neandgrap h i caI u s er i nt erfaces (G u n)D、It has i t s ownmands t hat ares ep arat efromanySQLs t at ement sE、It canru nRecoveryManager(RMAN)mandsF、It mu s t bedownIoadedfromt heOracI eTechno IogyNet work (OTN)答案：BCD3.Exam

38、i net hes emandsoracleghostOl sqlplus ul/oracleSQL SELECT*FROM emp;ENOENAMEDN1Alan223en2SQL exitoracleghostOl cat emp.dat.t23,Curl,44,Bob,4oracleShostOl*S sqlldr ul/oracleWh i cht wos t at ement s aret ru eabou t t hes q IIdrexecu t i on?A、It overwr it es t hedat aforAIanandadds dat aforCu rIandBobB

39、、It generat es as q Is cr i p t t hat i t u s es t oIoaddat afromEMP.DATt oEMPC It overwr i t es aI I dat a i nEMPw i t hdat afromEMP.DAT.D、It generat es aIogt hat cont a i ns cont roIf iIeent r i es,wh i chcanbeu s edwit hnormaISQL*Loaderop erat i onsEx It ap p ends dat afromEMP.DATt oEMP.答案：DE4.Wh

40、 i cht wos t at ement s aret ru eabou t u ndos egment s andt heu s eofu ndobyt rans actions inanOracleDat abas e i ns t anceAx As i ng I et rans act i onmayu s emu It ip leu ndos egment s s imu It aneou s Iy.Bx Undos egment s canbes t ored i nt heSYSAUXt abI es p aceC、Undos egment s canext endwhenat

41、 rans act i onf i I Is t heIas t ext ent oft heu ndos egmentDx Undos egment s canwrap arou ndt ot hef i rs t ext ent whenat rans act i onf i I Is t heIas t ext endoft heu ndos egmentE、Undos egment s canbes t ored i nt heSYSTEMt abI es p ace答案：DE5.Wh i cht wos t at ement s aret ru eabou t s u bs t i

42、t u t i onvar i abIes?A、As u bs t i t u t i onvar i abIeu s edt op romp t foracoIu mnnamemu s t beencIos ed i ndoubIeq u ot at i onmark s.B、As u bs t i t u t i onvar i abI ep ref i xedwi t hs aIways p romp t s onIyonceforavaIu e i nas es s i onC、As u bs t i t u t i onvar i abIeu s edt op romp t fora

43、coIu mnnamemu s t beencIos ed ins ingIeq u ot at i onmark sD、As u bs t i t u t i onvar i abI ep ref i xedwi t h&p romp t s onIyonceforavaIu e i nas es si onu nI es s i t i s s et t ou ndef i ned i nt hes es s i onE、As u bs t i t u t i onvar i abIecanbeu s edonIy i naSELECRs t at ementF、As u bs t i t

44、 u t i onvar i abIecanbeu s edwi t hanycIau s e i nas eI ect s t at ement答案：DF6.TheORDERSt abIehas acoIu mnORDERDATEofdat at yp eDATE.Thedefau It d i s p Iayformat foradat e i s DD-MON-RRWh i cht woWHEREcond i t i ons demons t rat et hecorrect u s ageofconvers i onfu nct i ons?A、WHEREorderdat eTODAT

45、E(ADDMONTHS(SYSDATE,6),MONDDYYYY)B、WHERETOCHAR(orderdat e,MONDDYYYY)=JAN202019,C、WHEREorderdat eTODATE(JUL102018,NONDDYYYY)D、WHEREorderdat eTOCHAR(ADDMONTHS(SYSDATE,6),MONDDYYYY)E、WHEREorderdat eIN(ToDATE(oct 212018,monDDYYYY),roCHAR(Nov212018MonDDYYYY)答案：BC7.Inoneofyou rdat abas es,you creat eau s

46、er,HR,andt henexecu t et h i s mandG RANTCREATESESSIONTOhrWITHADMI NOPT I ON:Wh i cht hreeact i ons canHRp erform?Ax G rant t heCREATESESSIONp r i v i Iegewi t hADMTNOPTIONt oot heru s ersB、Logi nt ot hedat abas e i ns t ance.Cx Revok et heCREATEs es s ionp r ivi Iegefromot heru s ersD、Execu t eDDLs

47、 t at ement s i nt heHRs chemaE、Execu t eDMLs t at ement s i nt heHRs chemaF、Revok et heCREATESESSIONp rivi legefromu s erHR答案：ACF8.Wh i cht hrees t at ement s aret ru eabou t s i ng Ie-rowfu nct i ons?(Choos et hree.)Ax Theycanbeu s edonIyint heWHEREcIau s eofaSELECTs t at ementB、Theargu ment canbe

48、acoIu mnname,var i abIe,Ii t era Ioranexp res s i onCx Thedat at yp eret u rnedcanbedifferent fromt hedat at yp eoft heargu mentD、Theycanbenes t edt oanyI eve IE、Theycanaccep t onIyoneargu mentTheyret u rnas i ngleres u It rowp ert abIe答案：BCD9.Wh i cht hrees t at ement s aret ru eabou t t heAu t oma

49、t i cD i agnos t i cRep os i t ory(ADR)?Ax 11 i s af i Ie-bas edrep os i t oryheIdou t s i deanydat abas eB、TheADRbas ei s s p ecifiedint heDIAG NOSTICDESTdat abas ep aramet eC、11 i s on Iyu s edforOracIeDat abas ed i agnos t i c i nformat i onD、11 i s he Id i ns i deanOracI edat abas es chemaE、It c

50、anbeu s edforp robIemd i agnos i s ofadat abas ewhent hat dat abas es i ns t ance i s dow答案：ABE10.Wh i cht hrees t at ement s aret ru eabou t as eIfj oin?Ax It canbeanou t erj oinBx TheONcIau s ecanbeu s edCx Theq u erymu s t u s et wodifferent aI ias es fort het abIeDx It mu s t beaneq u i j oi nE、