ocp051題庫筆記講解1z0-v9.02解析

1Z0-051V9.022017-5-24YouneedtodisythefirstnamesofallcustomersfromtheCUSTOMERStablethatcontainthecharacter'e'andhavethecharacter'a'inthesecondlastposition.WhichquerywouldgivetherequiredSELECTcust_first_nameFROMcustomersWHEREINSTR(cust_first_name,ANDSUBSTR(cust_first_name,-2,SELECTcust_first_nameFROMcustomersWHEREINSTR(cust_first_name,ANDSUBSTR(cust_first_name,-2,SELECTcust_first_nameFROMcustomersWHEREINSTR(cust_first_name,'e')ISNOTNULLANDSUBSTR(cust_first_name,1,-SELECTcust_first_nameFROMcustomersWHEREINSTR(cust_first_name, CUSTOMERSCUSTOMERScustomersfirstnamese，并a SELECTC.CUST_ID,cust_first_nameFROMcustomersCWHEREINSTR(cust_first_name,ANDSUBSTR(cust_first_name,-2,SELECTC.CUST_ID,cust_first_nameFROMcustomersCWHEREINSTR(cust_first_name,ANDSUBSTR(cust_first_name,length(cust_first_name)-SELECTC.CUST_ID,cust_first_nameFROMcustomersC'E'或ANDSUBSTR(cust_first_name,length(cust_first_name)-SELECTC.CUST_ID,cust_first_nameFROMcustomersC統(tǒng)計'e'，不統(tǒng)計ANDSUBSTR(cust_first_name,length(cust_first_name)-SELECTC.CUST_ID,cust_first_nameFROMcustomersCWHEREregexp_count(cust_first_name,'e')>0--默認從第一個位置始，區(qū)分大小,只統(tǒng)計ANDSUBSTR(cust_first_name,length(cust_first_name)-selectLENGTH('weibin'),SUBSTR('weibin',-2,1)fromdual;selectLENGTH('weibin'),SUBSTR('weibin',length('weibin')-1,1)fromdual;IntheCUSTOMERStable,theCUST_CITYcolumncontainsthevalue'Paris'fortheCUST_FIRST_NAME'ABIGAIL'.EvaluatethefollowingSQL>SELECTINITCAP(cust_first_name||''||FROMcustomersWHEREcust_first_name='ABIGAIL';Whatwouldbethe AbigailAbigailAbigailanerror將所以,SUBSTRPa，UPPERPA，INITCAP是將首字符大寫，后AbigailPaSELECTINITCAP('ABIGAIL'||''||UPPER(SUBSTR('Paris',-FROMselect-LENGTH('Paris')fromSELECTSUBSTR('Paris',-5,2)FROMdual;--PaSELECTSUBSTR('Paris',1,2)FROMdual;--selectupper('Pa')fromdual;--select'ABIGAIL'||''||'PA'FROMdual;--ABIGAILselectINITCAP('ABIGAILPA')FROMdual;--AbigailEvaluatethefollowingSQL>SELECTTRUNC(ROUND(156.00,-1),-1)FROMDUAL;Whatwouldbe e?（ROUND(156.00,-1)ROUND(156.00,-1)也就是說，156.00四舍五入，-1表示保留到小數(shù)點左邊一位，160TRUNC是直接截斷，1600160。SELECTTRUNC(ROUND(156.00,-1),-1)FROMDUALSELECTTRUNC(ROUND(156.00,-1),0)FROMDUALSELECTTRUNC(ROUND(156.00,-1),-1)FROMDUALSELECTTRUNC(ROUND(156.00,-1),-2)FROMViewtheExhibitandexaminethestructureoftheCUSTOMERSIntheCUSTOMERStable,theCUST_LAST_NAMEcolumncontainsthevalues'Anderson'and'Ausson'.Youissuethefollowing FROMCUSTOMERSWHERELOWER(cust_last_name)LIKE'a%n';Whatwouldbethe 'Oder'andanerrorbecausetheTRIMfunctionspecifiedisnotanerrorbecausetheLOWERfunctionspecifiedisnotanerrorbecausetheRECEfunctionspecifiedisnotCUST_LAST_NAME列包含'Andersonand'Ausson'值'Oder'and報錯，因為 TRIM('sonFROMcust_last_name)第一個參數(shù)不能是多個字符，只能是一SQL>selectTRIM('son'FROM'Anderson')fromdual;selectTRIM('son'FROM'Anderson')fromdual*ERRORatlineORA-30001:trimsetshouldhaveonlyoneWhichtwostatementsaretrueregardingworkingwithdates?(Choosetwo.)ThedefaultinternalstorageofdatesisinthenumericThedefaultinternalstorageofdatesisinthecharacterTheRRdateformatautomaticallycalculatesthecenturyfromtheSYSDATEfunctionanddoesnotallowtheusertoenterthecentury.TheRRdateformatautomaticallycalculatesthecenturyfromtheSYSDATEfunctionbutallowstheusertoenterthecenturyifA.默認內(nèi) .D.RRSYSDATE函數(shù)中計算出世紀，但是如果需要還允許用戶輸入世Adump函數(shù)返回一個包含數(shù)據(jù)類型代碼，所占字節(jié)長度以及內(nèi) DATE長度為零(如果列為空)7個字節(jié).DATE數(shù)據(jù)都包含世紀、年、月、日、時、分和秒.47121199991231日。selectsysdate,dump(sysdate)from--12017/5/2412:54:52Typ=13Len=8:selectto_date('2017/5/2411:09:28','yyyy/mm/ddhh24:mi:ss'),dump(to_date('2017/5/2411:09:28','yyyy/mm/ddhh24:mi:ss'))fromdual;--12017/5/2411:09:28Typ=13Len=8:TheformatofthedatedatatypeisByte1-Base256yearmodifier:2252-Base256year:256*=1792+225=3-Month:4-Day:5-Hours:6-Minutes:7-Seconds:8-DROPTABLECREATETABLETESTDATE(D1INSERTINTOTESTDATEVALUES(SYSDATE);SELECTD1,DUMP(D1)FROMTESTDATE;--12017/5/2421:39:11Typ=12Len=7:selectto_char(D1,'ccyyyy/mm/ddhh24:mi:ss'),dump(D1)fromDATE表示的日期范圍可以是公元 datedateselectto_char(sysdate,'CCRR-MM-DD')fromselectTO_CHAR(to_date('17-05-24','RR-MM-DD'),'CCYYYY-MM-DD')FROMDUAL;--212017-05-selectTO_CHAR(to_date('49-05-24','RR-MM-DD'),'CCYYYY-MM-DD')FROMDUAL;--212049-05-selectTO_CHAR(to_date('94-05-24','RR-MM-DD'),'CCYYYY-MM-DD')FROMDUAL;--201994-05-selectTO_