ocp051題庫(kù)筆記講解-1z0-v9.02解析

1Z0-051V9.022017-5-25YouarecurrentlylocatedinSingaporeandhaveconnectedtoaremotedatabaseinChicago.YouissuethefollowingSQL>SELECTROUND(SYSDATE-promo_begin_date,0)FROMpromotionsWHERE(SYSDATE-promo_begin_date)/365>PROMOTIONSisthepublicsynonymforthepublicdatabaselinkforthePROMOTIONStable.Whatis anerrorbecausetheROUNDfunctionspecifiedisanerrorbecausetheWHEREconditionspecifiedisdateandtime.numberofdayssincethepromostartedbasedonthecurrentSingaporedateandtime.執(zhí)行下執(zhí)行下 令PROMOTIONSpublicsynonympublicdatabaselink執(zhí)行下 數(shù)據(jù)庫(kù) SELECTSYSDATE-TO_DATE('2017-5-1','YYYY-MM-DD')FROMSELECTROUND(SYSDATE-TO_DATE('2017-5-1','YYYY-MM-DD'))FROMDUAL;017-5-1','YYYY-MM-DD'),5)FROMDUAL;selectselectsysdatefromdual;--ExaminethedataintheCUST_NAMEcolumnoftheCUSTOMERStable.RenskeLadwigJasonMallinSamuelMcCainAllanMCEwenIreneMikkilineniJuliaNayerYouneedtodisplaycustomers'secondnameswherethesecondnamestartswith"Mc"or"MC."WhichquerygivestherequiredSELECTSUBSTR(cust_name,INSTR(cust_name,'')+1)FROMcustomersWHEREINITCAP(SUBSTR(cust_name,INSTR(cust_name,'SELECTSUBSTR(cust_name,INSTR(cust_name,'')+1)FROMcustomersWHEREINITCAP(SUBSTR(cust_name,INSTR(cust_name,'')+1))LIKESELECTSUBSTR(cust_name,INSTR(cust_name,'')+1)FROMcustomers SELECTSUBSTR(cust_name,INSTR(cust_name,'')+1)FROMcustomersWHEREINITCAP(SUBSTR(cust_name,INSTR(cust_name,'')+1))=要顯示第二個(gè)名字以"Mcor"MC"開頭的員工第二個(gè)名B選項(xiàng)正確，INSTRSUBSTR從空格后開始找，把找出的結(jié)果首like模糊查詢。MC。 createviewv_customersasselectcust_first_name||''||cust_last_nameascust_namefromcustomers;selectcount(*)fromv_customersSELECTcount(*)FROMv_customerstWHEREcust_nameLIKE'%Mc%'--SELECTSUBSTR(cust_name,INSTR(cust_name,'')+1)FROMv_customersSELECTSUBSTR(cust_name,INSTR(cust_name,'')+1)FROMv_customersWHEREINITCAP(SUBSTR(cust_name,INSTR(cust_name,'')+1))LIKESELECTSUBSTR(cust_name,INSTR(cust_name,'')+1)FROMv_customersWHERESUBSTR(cust_name,INSTR(cust_name,'')+1)LIKEorSUBSTR(cust_name,INSTR(cust_name,'')+1)LIKE;SELECTSUBSTR(cust_name,INSTR(cust_name,'')+1)FROMv_customersWHEREINITCAP(SUBSTR(cust_name,INSTR(cust_name,'')+1))likeSELECT*FROMV_CUSTOMERSWHEREINSTR(CUST_NAME,'SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,'')),LENGTH(cust_name),'*')"CUSTNAME"FROMWHEREWHEREINSTR(cust_name,'SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,'')),LENGTH(cust_name),'*')"CUSTNAME"FROMWHEREINSTR(cust_name,'',-ExaminethedataintheCUST_NAMEcolumnoftheCUSTOMERStable.LexDeHaanRenskeLadwigJoseManuelUrmanJasonMallinYouwanttoextractonlythosecustomernamesthathavethreenamesanddisplaythe*symbolinplaceofthefirstnameasfollows:CUSTNAME***De****ManuelWhichtwoqueriesgivetherequiredoutput?(Choose ')),LENGTH(cust_name),'*')"CUSTNAME"FROMWHEREINSTR(cust_name,' ')),LENGTH(cust_name),'*')"CUSTNAME"FROMWHEREINSTR(cust_name,'',- INSTR(cust_name,''),'*')"CUSTFROMWHEREINSTR(cust_name,'',-1,- ')),LENGTH(cust_name)-INSTR(cust_name,''),'*')"CUSTNAME"FROMWHEREINSTR(cust_name,'LexDeHaanRenskeLadwigJoseManuelUrmanJasonMallinCUST***De****ManuelINSTR(cust_name然后，SUBSTR(cust_name,INSTR(cust_name從第一個(gè)空格開始往后')),LENGTH(cust_name),'*')LPADcust_name原來的長(zhǎng)度，不足的部AINSTR(cust_name',1,2)從第一個(gè)位置，從左往右，查找第二次出0值，則說明有第二個(gè)空格，則有第三個(gè)名字，BINSTR(cust_name1,2)從最后一個(gè)位置，從右往左，查找第二0值，則說明有第二個(gè)空格，則有第三個(gè)名字。C選項(xiàng)不正確，INSTR(cust_name1,-2)第四個(gè)參數(shù)，是第幾次出現(xiàn)，這里不DLENGTH(cust_nameINSTR(cust_name,，是總長(zhǎng)度，SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,'')),LENGTH(cust_name)-INSTR(cust_name,''),'*')"CUSTNAME"FROMWHEREINSTR(cust_name,'selectLPAD(SUBSTR('WEIHAIBIN',4),LENGTH('WEIHAIBIN'),'*')FROMSELECTSELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,'')),LENGTH(cust_name),'*')"CUSTNAME"FROMWHEREINSTR(cust_name,'ExaminethedataintheENAMEandHIREDATEcolumnsoftheEMPLOYEES YouwanttogeneratealistofuserIDsasfollows:YouissuethefollowingSQL>SELECTCONCAT(SUBSTR(INITCAP(ename),1,3),REPLACE(hiredate,'-'))FROMWhatis ItexecutessuccessfullyandgivesthecorrectItexecutessuccessfullybutdoesnotgivethecorrectItgeneratesanerrorbecausetheREPLACEfunctionisnotItgeneratesanerrorbecausetheSUBSTRfunctioncannotbenestedintheCONCATfunction.ENAMEandHIREDATE SELECTCONCAT(SUBSTR(INITCAP(ename),1,3),REPLACE(hiredate,'-'))FROMSELECTLAST_NAME,hire_dateFROM SELECTCONCAT(SUBSTR(INITCAP(LAST_name),1,3),REPLACE(hire_date,'-'))"USERID"FROMSELECThire_dateFROMSELECTREPLACE(HIRE_DATE,'/')FROMSELECTREPLACE(HIRE_DATE,'-')FROMEMPLOYEES;--date隱式轉(zhuǎn)換為字符串認(rèn)顯示格式是SELECTSELECTTO_CHAR(HIRE_DATE)FROMSELECTTO_CHAR(HIRE_DATE,'dd-MON-rr')FROMWhichstatementsaretrueregardingdatatypeconversioninexpressionsusedinqueries?(Chooseallthatapply.)inv_amt ':requiresexplicitinv_date>'01-02-2008':usesimplicitCONCAT(inv_amt,inv_date):requiresexplicitinv_date='15-february- :usesimplicitinv_noBETWEEN'101'AND'110':usesimplicitA.inv_amt inv_date01-02-2008')inv_date15-february-2008inv_noBETWEEN101AND110'表的結(jié)構(gòu)fromfromdual;fromfromdual;--fromselectcasewhen'2'-1>0then'YES'ELSE'NO'ENDFROMDUAL;selectcasewhen'01-MAY-17'