OracleOCP11g考试题库.docx
《OracleOCP11g考试题库.docx》由会员分享,可在线阅读,更多相关《OracleOCP11g考试题库.docx(67页珍藏版)》请在冰豆网上搜索。
![OracleOCP11g考试题库.docx](https://file1.bdocx.com/fileroot1/2022-12/30/3c23b397-8e8c-400b-bd4f-cc1040c9c30b/3c23b397-8e8c-400b-bd4f-cc1040c9c30b1.gif)
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