OracleOCP11g考试题库.docx
《OracleOCP11g考试题库.docx》由会员分享，可在线阅读，更多相关《OracleOCP11g考试题库.docx（67页珍藏版）》请在冰豆网上搜索。
OracleOCP11g考试题库
1.
WhichstatementistrueregardingtheCOALESCEfunction?
A.Itcanhaveamaximumoffiveexpressionsinalist
B.ItreturnsthehighestNOTNULLvalueinthelistforallrows
C.Itrequiresthatallexpressionsinthelistmustbeofthesamedatatype
D.ItrequiresthatatleastoneoftheexpressionsinthelistmusthaveaNOTNULLvalue
Answer:
C
2.
ViewtheExhibitandexaminethestructureofthePROMOTIONStable.
WhichSQLstatementsarevalid?
（Chooseallthatapply.）
A.SELECTpromo_id,DECODE（NVL（promo_cost,0）,promo_cost,
promo_cost*0.25,100）"Discount"
FROMpromotions;
B.SELECTpromo_id,DECODE（promo_cost,10000,
DECODE（promo_category,'G1',promo_cost*.25,NULL）,NULL）"Catcost"
FROMpromotions;
CSELECTpromo_id,DECODE（NULLIF（promo_cost,10000）,NULL,promo_cost*.25,'N/A'）"Catcost"
FROMpromotions;
D.SELECTpromo_id,DECODE（promo_cost,>10000,'High',<10000,'Low'）"Range"
FROMpromotions;
Answer:
A,B
3.
ViewtheExhibitandexaminethestructureofORDERSandCUSTOMERStables.
Thereisonlyonecustomerwiththecust_last_namecolumnhavingvalueRoberts.WhichINSERTstatementshouldbeusedtoaddarowintotheORDERStableforthecustomerwhoseCUSTLASTNAMEisRobertsandCREDITLIMITis600?
A.INSERTINTOordersVALUES（1,'10-mar-2007','direct',
（SELECTcustomer_id
FROMcustomers
WHEREcustlastname='Roberts'AND
credit_limit=600）,1000）;
B.INSERTINTOorders（order_id,order_date,order_mode,
（SELECTcustomer_id
FROMcustomers
WHEREcustlastname='Roberts'AND
credit_limit=600）,order_total）
VALUES（1,'10-mar-2007','direct',&&customer_id,1000）;
C.INSERTINTO（SELECTo.order_id,o.order_date,o.order_mode,c.customer_id,o.order_total
FROMorderso,customersc
WHEREo.customer_id=c.customer_id
ANDc.cust_last_name='Roberts'ANDc.credit_limit=600|
VALUES（1,'10-mar-2007','direct',（SELECTcustomer_id
FROMcustomers
WHEREcust_last_name='Roberts'ANDcredit_limit=600）,1000）;
D.INSERTINTOorders（order_id,order_date,order_mode,
（SELECTcustomer_id
FROMcustomers
WHEREcust_last_name='Roberts'AND
credit_limit=600）,order_total）
VALUES（1,'10-mar-2007','direct',&customer_id,1000）;
Answer:
A
4.
ViewtheExhibitandexaminethestructureoftheCUSTOMERStable.
EvaluatethefollowingSQLstatement
SQL>SELECTcust_city,COUNT（cust_last_name）
FROMcustomers
WHEREcust_credit_limit>1000
GROUPBYcust_city
HAVINGAVG（cust_credit_limit）BETWEEN5000AND6000;
Whichstatementistrueregardingtheoutcomeoftheabovequery?
A.Itexecutessuccessfully
B.ItreturnsanerrorbecausetheBETWEENoperatorcannotbeusedintheHAVINGclause
C.ItreturnsanerrorbecauseWHEREandHAVINGclausescannotbeusedinthesameSELECTstatement
D.ItreturnsanerrorbecauseWHEREandHAVINGclausescannotbeusedtoapplyconditionsonthesamecolumn
Answer:
A
5.
ViewtheExhibitandexaminethestructureofthePROMOTIONStable.
ExaminethefollowingtwoSQLstatements:
WhichstatementistrueregardingtheabovetwoSQLstatements?
A.statement1givesanerror,statement2executessuccessfully
B.statement2givesanerror,statement1executessuccessfully
C.statement1andstatement2executesuccessfullyandgivethesameoutput
D.statement1andstatement2executesuccessfullyandgiveadifferentoutput
Answer:
D
6.
YoucreatedanORDERStablewiththefollowingdescription:
Youinsertedsomerowsinthetable.Aftersometime,youwanttoalterthetablebycreatingthePRIMARYKEYconstraintontheORD_IDcolumn.Whichstatementistrueinthisscenario?
A.Youcannothavetwoconstraintsononecolumn
B.Youcannotaddaprimarykeyconstraintifdataexistsinthecolumn
C.Theprimarykeyconstraintcanbecreatedonlyatthetimeoftablecreation
D.Youcanaddtheprimarykeyconstraintevenifdataexists,providedthattherearenoduplicatevalues
Answer:
D
7.
Whendoesatransactioncomplete?
（Chooseallthatapply.）
A.whenaDELETEstatementisexecuted
B.whenaROLLBACKcommandisexecuted
C.whenaPL/SQLanonymousblockisexecuted
D.whenadatadefinitionlanguage（DDL）statementisexecuted
E.whenaTRUNCATEstatementisexecutedafterthependingtransaction
Answer:
B,D,E
8.
YouneedtodisplaythefirstnamesofallcustomersfromtheCUSTOMERStablethatcontainthecharacter'e'andhavethecharacter'a'inthesecondlastposition
Whichquerywouldgivetherequiredoutput?
A.SELECTcust_first_name
FROMcustomers
WHEREINSTR（cust_first_name,'e'）<>0AND
SUBSTR（cust_first_name,-2,l）='a';
B.SELECTcustfirstname
FROMcustomers
WHEREINSTR（cust_first_name,'e'）<>"AND
SUBSTR（cust_first_name,-2,l）='a';
C.SELECTcust_first_name
FROMcustomers
WHEREINSTR（cust_first_name,'e'）ISNOTNULLAND
SUBSTR（cust_first_name,l,-2）='a';
D.SELECTcust_first_name
FROMcustomers
WHEREINSTR（cust_first_name,'e'）<>0AND
SUBSTR（cust_first_name,LENGTH（cust_first_name）,-2）='a';
Answer:
A
9.
TheORDERStablebelongstotheuserOE.OEhasgrantedtheSELECTprivilegeontheORDERStabletotheuserHR.
WhichstatementwouldcreateasynonymORDsothatHRcanexecutethefollowingquerysuccessfully?
SELECT*FROMord;
A.CREATESYNONYMordFORorders;ThiscommandisissuedbyOE
B.CREATEPUBLICSYNONYMordFORorders;ThiscommandisissuedbyOE
C.CREATESYNONYMordFORoe.orders;Thiscommandisissuedbythedatabaseadministrator
D.CREATEPUBLICSYNONYMordFORoe.orders;Thiscommandisissuedbythedatabaseadministrator
Answer:
D
10.
ViewtheExhibitandexaminethestructureofthePRODUCTStable
Youneedtogenerateareportinthefollowingformat:
Whichtwoquerieswouldgivetherequiredoutput?
（Choosetwo.）
A.SELECTprod_name||q"'scategoryis'||prod_categoryCATEGORIES
FROMproducts;
B.SELECTprod_name||q'['s]'categoryis'||prod_categoryCATEGORIES
FROMproducts;
C.SELECTprod_name||q'\'s\'categoryis'||prod_categoryCATEGORIES
FROMproducts;
D.SELECTprod_name||q'<'s>'||'categoryis'||prod_categoryCATEGORIES
FROMproducts;
Answer:
C,D
11.
WhichstatementistrueregardingtheINTERSECToperator?
A.ItignoresNULLvalues
B.Reversingtheorderoftheintersectedtablesalterstheresult
C.ThenamesofcolumnsinallSELECTstatementsmustbeidentical
D.ThenumberofcolumnsanddatatypesmustbeidenticalforallSELECTstatementsinthequery
Answer:
D
12.
WhichtwostatementsaretrueregardingtheUSINGandONclausesintablejoins?
（Choosetwo.）
A.BothUSINGandONclausescanbeusedforequijoinsandnonequijoins
B.AmaximumofonepairofcolumnscanbejoinedbetweentwotablesusingtheONclause
C.TheONclausecanbeusedtojointablesoncolumnsthathavedifferentnamesbutcompatibledatatypes
D.TheWHEREclausecanbeusedtoapplyadditionalconditionsinSELECTstatementscontainingtheONortheUSINGclause
Answer:
C,D
13.
ExaminethestructureofthePROGRAMStable
WhichtwoSQLstatementswouldexecutesuccessfully?
（Choosetwo.）
A.SELECTNVL（ADD_MONTHS（END_DATE,l）,SYSDATE）
FROMprograms;
B.SELECTTO_DATE（NVL（SYSDATE-END_DATE,SYSDATE））
FROMprograms;
C.SELECTNVL（MONTHS_BETWEEN（start_date,end_date）,'Ongoing'）
FROMprograms;
D.SELECTNVL（TO_CHAR（MONTHS_BETWEEN（start_date,end_date））,'Ongoing'）
FROMprograms;
Answer:
A,D
14.
Wherecansubqueriesbeused?
（Chooseallthatapply.）
A.fieldnamesintheSELECTstatement
B.theFROMclauseintheSELECTstatement
C.theHAVINGclauseintheSELECTstatement
D.theGROUPBYclauseintheSELECTstatement
E.theWHEREclauseinonlytheSELECTstatement
F.theWHEREclauseinSELECTaswellasallDMLstatements
Answer:
A,B,C,F
15.
ViewtheExhibitsandexaminethestructuresofthePRODUCTS,SALES,andCUSTOMERStables
Youneedtogenerateareportthatgivesdetailsofthecustomer'slastname,nameoftheproduct,andthequantitysoldforallcustomersin'Tokyo'
Whichtwoqueriesgivetherequiredresult?
（Choosetwo.）
A.SELECTc.cust_last_name,p.prod_name,s.quantity_sold
FROMsalessJOINproductsp
USING（prod_id）
JOINcustomersc
B.SELECTc.cust_last_name,p.prod_name,s.quantity_sold
FROMproductspJOINsalessJOINcustomersc
ON（p.prod_id=s.prod_id）
ON（s.cust_id=c.cust_id）
WHEREc.cust_city='Tokyo';
C.SELECTc.cust_last_name,p.prod_name,s.quantity_sold
FROMproductspJOINsaless
ON（p.prod_id=s.prod_id）
JOINcustomersc
ON（s.cust_id=c.cust_id）
ANDc.cust_city='Tokyo';
D.SELECTc.cust_id,c.cust_last_name,p.prod_id,p.prod_name,s.quantity_soldFROMproductspJOINsaless
USING（prod_id）
JOINcustomersc
USING（cust_id）
WHEREc.cust_city='Tokyo';
Answer:
A,C
16.
ViewtheExhibitandevaluatethestructureanddataintheCUST_STATUStable
YouissuethefollowingSQLstatement
SQL>SELECTcustno,NVL2（NULLIF（amt_spent,credit_limit）,0,1000）"BONUS"
FROMcust_status;
Whichstatementistrueregardingtheexecutionoftheabovequery?
A.ItproducesanerrorbecausetheAMT_SPENTcolumncontainsanullvalue
B.Itdisplaysabonusof1000forallcustomerswhoseAMT_SPENTislessthanCREDIT_LIMIT
C.Itdisplaysabonusof1000forallcustomerswhoseAMT_SPENTequalsCREDIT_LIMIT,orAMT_SPENTisnull
D.ItproducesanerrorbecausetheTO_NUMBERfunctionmustbeusedtoconverttheresultoftheNULLIFfunctionbeforeitcanbeusedbytheNVL2function
Answer:
C
17.
ExaminethestructureanddatainthePRICELISTtable
Youplantogiveadiscountof25%ontheproductpriceandneedtodisplaythediscountamountinthesameformatasthePROD_PRICE.
WhichSQLstatementwouldgivetherequiredresult?
A.SELECTTO_CHAR（prod_price*.2
升级会员 