ocp051題庫筆記講解1z0v902解析

1Z0-051V9.022017-6-1ExaminethedataintheLIST_PRICEandMIN_PRICEcolumnsofthePRODUCTStable: Whichtwoexpressionsgivethesameoutput?(ChooseNVL(NULLIF(list_price,min_price),NVL(COALESCE(list_price,min_price),NVL2(COALESCE(list_price,min_price),min_price,COALESCE(NVL2(list_price,list_price,min_price),PRODUCTSLIST_PRICEPRODUCTSLIST_PRICEMIN_PRICE兩列的數(shù)據(jù)：A.10000,20000,0DROPTABLET106;CREATETABLET106(LIST_PRICENUMBER,MIN_PRICENUMBER);INSERTINTOT106VALUES(1000,800);INSERTINTOT106VALUES(2000,NULL);INSERTINTOT106VALUES(3000,SELECT*FROMSELECTNVL(NULLIF(list_price,min_price),0)FROMSELECTNVL(COALESCE(list_price,min_price),0)FROMSELECTNVL2(COALESCE(list_price,min_price),min_price,0)FROMSELECTCOALESCE(NVL2(list_price,list_price,min_price),FROMFROMViewtheExhibitandexaminethestructureanddataintheWhichtwoSQLstatementswouldexecutesuccessfully?(ChooseSELECTAVG(inv_date) SELECTMAX(inv_date),MIN(cust_id) SELECTMAX(AVG(SYSDATE-inv_date)) SELECTAVG(inv_date-SYSDATE),AVG(inv_amt) Cgroupby;SELECT*SELECTAVG(INV_DATE) ;--SELECTAVG(INV_AMT) ALTER ADD(cust_idSETCUST_ID='C'WHERESETCUST_ID='B'WHERESELECTMAX(inv_date),MIN(cust_id) SELECTSYSDATE-inv_date SELECT(8.97328703703704+13.9740509259259)/2FROMDUAL;--SELECTAVG(SYSDATE-inv_date)FROM SELECTMAX(AVG(SYSDATE-inv_date))FROM SELECTAVG(inv_date-SYSDATE),AVG(inv_amt) WhichtwostatementsaretrueregardingtheCOUNTfunction?(Choosetwo.)TheCOUNTfunctioncanbeusedonlyforCHAR,VARCHAR2,andNUMBERdatatypes.COUNT(*)returnsthenumberofrowsincludingduplicaterowsandrowscontainingNULLvalueinanyofthecolumns.COUNT(cust_id)returnsthenumberofrowsincludingrowswithduplicatecustomerIDsandNULLvalueintheCUST_IDcolumn.COUNT(DISTINCTinv_amt)returnsthenumberofrowsrowscontainingduplicatesandNULLvaluesintheINV_AMTASELECTstatementusingtheCOUNTfunctionwithaDISTINCTkeywordcannothaveaWHEREclause.B.count(*)NULL的行。C.COUNT(cust_id)NULL的行。D.COUNT(DISTINCTinv_amt)NULL的行。SELECTSELECT*;INCTINV_AMT)FROM --3322selectCOUNT(DISTINCTINV_AMT) whereExaminethestructureoftheMARKStable: NOTNULL Whichtwostatementswouldexecutesuccessfully?(ChooseSELECTstudent_name,subject1FROMmarksWHEREsubject1>SELECTstudent_name,SUM(subject1)FROMmarksWHEREstudent_nameLIKESELECTSUM(subject1+subject2+subject3)FROMmarksWHEREstudent_nameISSELECTSUM(DISTINCTNVL(subject1,0)),FROMWHEREsubject1>哪兩個語句可以執(zhí)行成功？（選擇兩個哪兩個語句可以執(zhí)行成功？（選擇兩個B選項不正確，student_name是正常列顯示，sumgroupCREATETABLESTUDENT_IDVARCHAR2(4)NOTNULL,STUDENT_NAMEVARCHAR2(25),SUBJECT1NUMBER(3),SUBJECT2NUMBER(3),SUBJECT3NUMBER(3)select*fromSELECTstudent_name,subject1FROMmarksWHEREsubject1>SELECTstudent_name,subject1FROMmarksWHEREsubject1>(SELECTAVG(subject1)FROMSELECTstudent_name,SUM(subject1)FROMmarksWHEREstudent_nameLIKESELECTstudent_name,SUM(subject1)FROMmarksWHEREstudent_nameLIKE'R%'GROUPBY

SELECTSUM(subject1)FROMmarksWHEREstudent_nameLIKE'R%'GROUPBYINSERTINTOMARKSVALUES(1,NULL,1,2,3);INSERTINTOMARKSVALUES(2,'B',4,5,6);INSERTINTOMARKSVALUES(3,NULL,7,8,9);select*frommarksWHEREstudent_nameISselectsubject1+subject2+subject3frommarksWHEREstudent_nameISNULL;SELECTSUM(subject1+subject2+subject3)FROMmarksWHEREstudent_nameISINSERTINTOMARKSVALUES(4,'B',6,5,4);INSERTINSERTINTOMARKSVALUES(4,'B',6,5,4);INSERTINTOMARKSVALUES(5,NULL,9,8,7);select*frommarksWHEREsubject1>SELECTSUM(DISTINCTNVL(subject1,0)),MAX(subject1)from(select*frommarksWHEREsubject1>subject2)SELECTSUM(DISTINCTNVL(subject1,0)),FROMWHEREsubject1>ViewtheExhibitandexaminethestructureoftheCUSTOMERSUsingtheCUSTOMERStable,youneedtogenerateareportthatshowstheaveragecreditlimitforcustomersinWASHINGTONandNEWYORK.WhichSQLstatementwouldproducetherequiredSELECTcust_city,AVG(cust_credit_limit)FROMcustomersWHEREcust_cityIN('WASHINGTON','NEWGROUPBYcust_credit_limit,SELECTcust_city,AVG(cust_credit_limit)FROMcustomersWHEREcust_cityIN('WASHINGTON','NEWGROUPBYSELECTcust_city,AVG(cust_credit_limit)FROMcustomersWHEREcust_cityIN('WASHINGTON','NEWGROUPBYSELECTcust_city,AVG(NVL(cust_credit_limi