ocp051题库笔记讲解-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.执行下执行下 令PROMOTIONSpublicsynonympublicdatabaselink执行下 数据库 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))=要显示第二个名字以"Mcor"MC"开头的员工第二个名B选项正确，INSTRSUBSTR从空格后开始找，把找出的结果首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从第一个空格开始往后')),LENGTH(cust_name),'*')LPADcust_name原来的长度，不足的部AINSTR(cust_name',1,2)从第一个位置，从左往右，查找第二次出0值，则说明有第二个空格，则有第三个名字，BINSTR(cust_name1,2)从最后一个位置，从右往左，查找第二0值，则说明有第二个空格，则有第三个名字。C选项不正确，INSTR(cust_name1,-2)第四个参数，是第几次出现，这里不DLENGTH(cust_nameINSTR(cust_name,，是总长度，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隐式转换为字符串认显示格式是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'表的结构fromfromdual;fromfromdual;--fromselectcasewhen'2'-1>0then'YES'ELSE'NO'ENDFROMDUAL;selectcasewhen'01-MAY-17'