ocp 1Z0-051 71-105题解析
71. Which arithmeticoperations can be performed on a column by using a SQL function that is builtintoOracle database ?(Choose three .)A. a dditionB. s ubtractionC. r aising to a powerD. f inding the quotientE. f inding the lowestvalueAnswer: ACE解析：A选项 sum()B选项 无C选项 自乘 power(m,n)D选项 无E选项 min()72. Which tasks can beperformed using SQL functions built into Oracle Database ? (Choose three.)A. d isplaying a date ina nondefault formatB. f inding the numberof characters in an expressionC. s ubstituting acharacter string in a text expression with a specified stringD. c ombining more thantwo columns or expressions into a single column in the outputAnswer: ABC解析：
A选项，to_char可以转换多种日期格式，详解见：http://blog.iyunv.com/zbdba/article/details/17042195B选项，length(char)C选项，replace(char1,char2,char3)D选项，无
73. Which tasks can beperformed using SQL functions that are built into Oracle database ? (Choosethree .)A. f inding theremainder of a divisionB. a dding a number to adate for a resultant date valueC. c omparing twoexpressions to check whether they are equalD. c hecking whether aspecified character exists in a given stringE. r emoving trailing,leading, and embedded characters from a character stringAnswer: ACD解析：A选项，mod(m,n)B选项，无C选项 : NULLIF(表达式1，表达式2)当两个表达式相等时，返回空；否则返回表达式1。测试：
scott@ORCL>select nullif(2*3,3*4) from dual;
NULLIF(2*3,3*4)--------------- 6
scott@ORCL>selectnullif(2*3,3*2) from dual;
NULLIF(2*3,3*2)
D选项，instr(char1,char2,])E选项，无
74. Which statements aretrue regarding single row functions? (Choose all that apply.)A. MOD : returns thequotient of a divisionB. TRUNC : can be usedwith NUMBER and DATE valuesC. CONCAT : can be usedto combine any number of valuesD. SYSDATE : returns thedatabase server current date and timeE. INSTR : can be usedto find only the first occurrence of a character in a stringF. TRIM : can be used toremove all the occurrences of a character from a stringAnswer: BD解析：引用官方文档解释 Single-rowfunctions：Single-rowfunctions return a single resultrow for every row of a queried table orview. These functionscan appear in select lists, WHERE clauses, START WITH andCONNECT BY clauses, andHAVING clauses.
A选项，mod应该是返回余数，而不是商B选项，引用文档中的一段话：
This function takes as an argument any numeric data type or any nonnumeric datatype that canbe implicitly converted to a numeric data type. If you omit n2, then thefunction returns thesame data type as the numeric data type of the argument. If youinclude n2, then thefunction returns NUMBER.C选项，concat(char1,char2)用于连接字符串D选项，sysdate放回系统当前时间
scott@ORCL>selectsysdate from dual;
SYSDATE--------------02-12月-13
E选项，instr(char1,char2,])The INSTR functionssearch string for substring. The search operation is definedas comparing thesubstring argument with substrings of string of the same lengthfor equality until amatch is found or there are no more substrings left. Eachconsecutive comparedsubstring of string begins one character to the right (forforward searches) or onecharacter to the left (for backward searches) from the firstcharacter of theprevious compared substring. If a substring that is equal tosubstring is found, thenthe function returns an integer indicating the position ofthe first character ofthis substring. If no such substring is found, then the functionreturns zero.SELECT INSTR('CORPORATEFLOOR','OR', 3, 2) "Instring"FROM DUAL;Instring----------14
F选项，trim() 从字符串string的头，尾或两端截掉字符
75. The following dataexists in the PRODUCTS table:PROD_ID PROD_LIST_PRICE123456 152525.99You issue the followingquery:SQL> SELECT RPAD((ROUND(prod_list_price)), 10,'*')FROM productsWHERE prod_id = 123456;What would be theoutcome?A. 152526 ****B. **152525.99C. 152525** **D. an error messageAnswer: A解析：
round(m,n)不指定n的值，直接取整数，为152525Rpad(char1,n,char2)把字符串1右侧填充字符串char2使其长度达到n，如果字符串char1长度大于n，则返回字符串char1右侧n个字符在这里，oracle有隐式转换，将数字转换为字符串
76. You need to displaythe first names of all customers from the CUSTOMERS table that contain thecharacter 'e' and havethe character 'a' in the second last position.Which query would givethe required output?A. SELECTcust_first_nameFROM customersWHEREINSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name,-2, 1)='a';B. SELECTcust_first_nameFROM customersWHEREINSTR(cust_first_name, 'e')<>'' ANDSUBSTR(cust_first_name,-2, 1)='a';C. SELECTcust_first_nameFROM customersWHEREINSTR(cust_first_name, 'e')IS NOT NULL ANDSUBSTR(cust_first_name,1,-2)='a';D. SELECTcust_first_nameFROM customersWHEREINSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name,LENGTH(cust_first_name),-2)='a';Answer: A解析：
INSTR(cust_first_name, 'e')<>0 返回e在cust_first_name的位置，这里等于0，表示为第一个位置SUBSTR(cust_first_name,-2, 1)='a' 用于获取字串，-2表示位置，倒数第二个，1表示子串的长度
77. In the CUSTOMERStable, the CUST_CITY column contains the value 'Paris' for theCUST_FIRST_NAME'ABIGAIL'.Evaluate the followingquery:SQL> SELECT INITCAP(cust_first_name || ' ' ||UPPER(SUBSTR(cust_city,-LENGTH(cust_city),2)))FROM customersWHERE cust_first_name ='ABIGAIL';What would be theoutcome?A. Abigail PAB. Abigail PaC. Abigail ISD. an error messageAnswer: B解析：Initcap 返回首字母大写INITCAP (UPPER(SUBSTR(cust_city,-LENGTH(cust_city),2)))INITCAP (UPPER(SUBSTR(cust_city,-5,2)))INITCAP (UPPER(Pa))INITCAP(PA)Pa
78. Evaluate thefollowing query:SQL> SELECTTRUNC(ROUND(156.00,-1),-1)FROM DUAL;What would be theoutcome?A. 16B. 100C. 160D. 200E. 150Answer: C解析：Round(m,n)对m进行四舍五入，n为负数，表示将m四舍五入到小数点左边第n位Trunc(m,n)对m进行截取操作，n小于0时，表示截取到小数点左边第n位TRUNC(ROUND(156.00,-1),-1)TRUNC(160,-1)160
79. View the Exhibit andexamine the structure of the CUSTOMERS table.In the CUSTOMERS table,the CUST_LAST_NAME column contains the values 'Anderson' and 'Ausson'.You issue the followingquery:SQL> SELECTLOWER(REPLACE(TRIM('son' FROM cust_last_name),'An','O'))FROM CUSTOMERSWHERELOWER(cust_last_name) LIKE 'a%n';What would be theoutcome?A. 'Oder' and 'Aus'B. a n error because theTRIM function specified is not validC. a n error because theLOWER function specified is not validD. a n error because theREPLACE function specified is not validAnswer: B解析：scott@ORCL>selecttrim('son' from 'anderson') from dual;select trim('son' from'anderson') from dual *第 1 行出现错误:ORA-30001: 截取集仅能有一个字符Trim的用法：trim( trim_char from string) 从字符串String中删除指定的字符trim_char。leading:从字符串的头开始删除。trailing:从字符串的尾部开始删除。borth:从字符串的两边删除。
80. Which two statementsare true regarding working with dates? (Choose two.)A. The default internalstorage of dates is in the numeric format.B. The default internalstorage of dates is in the character format.C. The RR date formatautomatically calculates the century from the SYSDATE function and does notallow the user to enterthe century.D. The RR date formatautomatically calculates the century from the SYSDATE function but allows theuser to enter thecentury if required.Answer: AD解析：Oracle内部存储的时间为数值格式
The RR datetime formatelement is similar to the YY datetime format element, but itprovides additionalflexibility for storing date values in other centuries. The RRdatetime format elementlets you store 20th century dates in the 21st century byspecifying only the lasttwo digits of the year.
81. You are currentlylocated in Singapore and have connected to a remote database in Chicago.You issue the followingcommand:SQL> SELECTROUND(SYSDATE-promo_begin_date,0)FROM promotionsWHERE(SYSDATE-promo_begin_date)/365 > 2;PROMOTIONS is the publicsynonym for the public database link for the PROMOTIONS table.What is the outcome?A. a n error because theROUND function specified is invalidB. a n error because theWHERE condition specified is invalidC. n umber of days sincethe promo started based on the current Chicago date and timeD. number of days sincethe promo started based on the current Singapore date and timeAnswer: C解析：当然是基于chicago的时间，因为sysdate也是获取chicago当前的时间
82. Examine the data inthe CUST_NAME column of the CUSTOMERS table.CUST_NAMERenske LadwigJason MallinSamuel McCainAllan MCEwenIrene MikkilineniJulia NayerYou need to displaycustomers' second names where the second name starts with "Mc" or"MC."Which query gives therequired output?A. SELECTSUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHEREINITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';B. SELECTSUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHEREINITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';C. SELECTSUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE SUBSTR(cust_name,INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');D. SELECTSUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHEREINITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');Answer: B解析：INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))INSTR(cust_name,' ') 返回空字符串的位置SUBSTR(cust_name,INSTR(cust_name,' ')+1)截取空字符串后面的字符INITCAP将每个单词的第一个字母大写，其他字母小写
83. Examine the data inthe CUST_NAME column of the CUSTOMERS table.CUST_NAMELex De HaanRenske LadwigJose Manuel UrmanJason MallinYou want to extract onlythose customer names that have three names and display the * symbol in placeof the first name asfollows:CUST NAME*** De Haan**** Manuel UrmanWhich two queries givethe required output? (Choose two.)A. SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUSTNAME"FROM customersWHERE INSTR(cust_name, '',1,2)<>0;B. SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUSTNAME"FROM customersWHERE INSTR(cust_name, '',-1,2)<>0;C. SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,''),'*') "CUST NAME"FROM customersWHERE INSTR(cust_name, '',-1,-2)<>0;D. SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,''),'*') "CUST NAME"FROM customersWHERE INSTR(cust_name, '',1,2)<>0 ;Answer: AB解析： 以Lex De Haan为例：LPAD(SUBSTR(cust_name,INSTR(cust_name,'')),LENGTH(cust_name),'*'),INSTR(cust_name,' ')返回空字符串的位置 4SUBSTR(cust_name,4') 返回指定位置后的字符串 De HannLPAD(‘De Hann’,11 ,'*')****De Hann
INSTR(cust_name, '',1,2)<>0; 表示搜索空字符串，从第一个字符开始，到最后的得到空字符串在该字符串中出现的次数为2次，说明该字符串有三个Names
INSTR(cust_name, '',-1,2)<>0 同理，从倒数第一个位置搜索
84. View the Exhibit andexamine the structure of the EMPLOYEES table.Examine the data in theENAME and HIREDATE columns of the EMPLOYEES table:ENAME HIREDATESMITH 17-DEC-80ALLEN 20-FEB-81WARD 22-FEB-81You want to generate alist of user IDs as follows:USERIDSmi17DEC80All20FEB81War22FEB81You issue the followingquery:SQL>SELECTCONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-')) "USERID"FROM employees;What is the outcome?A. It executessuccessfully and gives the correct output.B. It executessuccessfully but does not give the correct output.C. It generates an errorbecause the REPLACE function is not valid.D. It generates an errorbecause the SUBSTR function cannot be nested in the CONCAT function.Answer: A以SMITH 17-DEC-80 为列：CONCAT(SUBSTR(INITCAP(ename),1,3),REPLACE(hiredate,'-'))INITCAP(ename) 返回首字母大写，其他字母小写 SmithSUBSTR(‘Smith’,1,3) 截取字串SmiREPLACE(hiredate,'-') 如果不指定代替的字符串，将直接删除要替代的字串scott@ORCL>selectreplace('17-DEC-80','-') from dual;
REPLACE-------17DEC80
CONCAT(‘Smi’,’ 17DEC80’)Smi17DEC80
85. View the E xhibitand examine the structure and data in the INVOICE table.Which statements aretrue regarding data type conversion in expressions used in queries? (Choose allthat apply.)A. inv_amt ='0255982': requires explicit conversionB. inv_date >'01-02-2008' : uses implicit conversionC.CONCAT(inv_amt,inv_date) : requires explicit conversionD. inv_date ='15-february-2008' : uses implicit conversionE. inv_no BETWEEN '101'AND '110' : uses implicit conversionAnswer: DEB选项不能隐式转换，需要sql函数转换
86. Examine thestructure and data of the CUST_TRANS table:CUST_TRANSName Null TypeCUSTNO NOT NULL CHAR(2)TRANSDATE DATETRANSAMT NUMBER(6,2)CUSTNO TRANSDATETRANSAMT11 01-JAN-07 100022 01-FEB-07 200033 01-MAR-07 3000Dates are stored in thedefault date format dd-mon-rr in the CUST_TRANS table.Which SQL statementswould execute successfully? (Choose three .)A. SELECT transdate +'10' FROM cust_trans;B. SELECT * FROMcust_trans WHERE transdate = '01-01-07';C. SELECT transamt FROMcust_trans WHERE custno > '11';D. SELECT * FROMcust_trans WHERE transdate='01-JANUARY-07';E. SELECT custno + 'A'FROM cust_trans WHERE transamt > 2000;Answer: ACD解析：
由上题可知A正确，B错误，D正确C选项，测试：
scott@ORCL>insert into zbcxy values('11');
已创建 1 行。scott@ORCL>insertinto zbcxy values('22');
已创建 1 行。scott@ORCL>select *from zbcxy where id>'11';
ID--22
E选项,无法将A转换为数字，测试：scott@ORCL>selectid+'s' from zbcxy;select id+'s' from zbcxy *第 1 行出现错误:ORA-01722: 无效数字
scott@ORCL>selectid+'3' from zbcxy;
ID+'3'---------- 14 25
87. You want to displaythe date for the first Mon day of the next month and issue the followingcommand:SQL>SELECTTO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'),'dd "is the firstMonday for" fmmonth rrrr')FROM DUAL;What is the outcome?A. It executessuccessfully and returns the correct result.B. It executessuccessfully but does not return the correct result.C. It generates an errorbecause TO_CHAR should be replaced with TO_DATE.D. It generates an errorbecause rrrr should be replaced by rr in the format string.E. It generates an errorbecause fm and double quotation marks should not be used in the format string.Answer: A解析：TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON')LAST_DAY(SYSDATE)返回当月最后一天NEXT_DAY(LAST_DAY(SYSDATE),'MON')返回LAST_DAY(SYSDATE)后由'MON'指定的第一个工作日对应的日期
88. You need tocalculate the number of days from 1st January 2007 till date.Dates are stored in thedefault format of dd-mon-rr.Which SQL statementswould give the required output? (Choose two .)A. SELECT SYSDATE -'01-JAN-2007' FROM DUAL;B. SELECT SYSDATE -TO_DATE('01/JANUARY/2007') FROM DUAL;C. SELECT SYSDATE -TO_DATE('01-JANUARY-2007') FROM DUAL;D. SELECTTO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL;E. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL;Answer: BC解析：A选项，'01-JAN-20’无法转换为sysdate格式To_date 将符合格式的字符串转换为日期，和sysdate格式一样B C正确D选项，即使转换为字符串，也无法相比Character values are compared on the basis of twomeasures:■ Binary or linguistic sorting■ Blank-padded or nonpadded comparison semanticssys@ORCL>select '01-FEB-2010'-'01-JAN-2007' from dual;select '01-FEB-2010'-'01-JAN-2007' from dual *第 1 行出现错误:ORA-01722: 无效数字
E选项转换后和A项相同
89. You need to displaythe date 11-oct-2007 in words as 'Eleventh of October, Two Thousand Seven'.Which SQL statementwould give the required result?A. SELECTTO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year')FROM DUAL;B. SELECTTO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year')FROM DUAL;C. SELECTTO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year')FROM DUAL;D. SELECTTO_DATE(TO_CHAR('11-oct-2007','fmDdspth ''of'' Month, Year'))FROM DUAL;Answer: C解析：
sys@ORCL>SELECT TO_CHAR(TO_DATE('11-10月-2007'), 'fmDdthsp "of" Month, Year')2from dual;
TO_CHAR(TO_DATE('11-10月-2007'),'FMD------------------------------------Eleventh of 10月, Two ThousandSeven
90. Examine thestructure and data in the PRICE_LIST table:name Null TypePROD_ID NOT NULLNUMBER(3)PROD_PRICE VARCHAR2(10)PROD_ID PROD_PRICE100 $234.55101 $6,509.75102 $1,234You plan to give adiscount of 25% on the product price and need to display the discount amount inthesame format as thePROD_PRICE.Which SQL statementwould give the required result?A. SELECTTO_CHAR(prod_price* .25,'$99,999.99')FROM PRICE_LIST;B. SELECTTO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00')FROM PRICE_LIST;C. SELECTTO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00')FROM PRICE_LIST;D. SELECTTO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00')FROM PRICE_LIST;Answer: C解析：需要先将价格转换为数字再进行运算TO_NUMBER(prod_price,'$99,999.99')* .25 将符合特定格式的字符串转换为数值当然运算完成，再转换为价格的格式TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')*.25,'$99,999.00')更多格式说明：
http://blog.iyunv.com/zbdba/article/details/17042195
91. View the Exhibit andexamine the structure of the PROMOTIONS table.Which two SQL statementswould execute successfully? (Choose two.)A. UPDATE promotionsSET promo_cost =promo_cost+ 100WHERETO_CHAR(promo_end_date, 'yyyy') > '2000';B. SELECTpromo_begin_dateFROM promotionsWHERETO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';C. UPDATE promotionsSET promo_cost =promo_cost+ 100WHERE promo_end_date> TO_DATE(SUBSTR('01-JAN-2000',8));D. SELECTTO_CHAR(promo_begin_date,'dd/month')FROM promotionsWHERE promo_begin_dateIN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));Answer: ABA选项，测试：
sys@ORCL>select TO_CHAR(sysdate,'yyyy') from dual;
TO_C----2013
B选项，测试：sys@ORCL>selectTO_CHAR(sysdate,'mon dd yy') from dual;
TO_CHAR(SYSDAT--------------12月 03 13
C选项，to_date()中的不符合日期格式D选项，to_date()中的不符合日期格式
92. View the E xhibitand examine the data in the PROMO_NAME and PROMO_END_DATE columns ofthe PROMOTIONS table,and the required output format.Which two queries givethe correct result? (Choose two.) A. SELECT promo_name,TO_CHAR(promo_end_date,'Day') ', 'TO_CHAR(promo_end_date,'Month')' 'TO_CHAR(promo_end_date,'DD,YYYY') AS last_dayFROM promotions;B.SELECTpromo_name,TO_CHAR (promo_end_date,'fxDay') ', 'TO_CHAR(promo_end_date,'fxMonth')' 'TO_CHAR(promo_end_date,'fxDD,YYYY') AS last_dayFROM promotions;C. SELECT promo_name,TRIM(TO_CHAR(promo_end_date,'Day')) ', 'TRIM(TO_CHAR(promo_end_date,'Month'))' 'TRIM(TO_CHAR(promo_end_date,'DD,YYYY')) AS last_dayFROM promotions;D.SELECTpromo_name,TO_CHAR(promo_end_date,'fmDay')','TO_CHAR(promo_end_date,'fmMonth')' 'TO_CHAR(promo_end_date,'fmDD,YYYY') AS last_dayFROM promotions;Answer: CD解析：TRIM(TO_CHAR(promo_end_date,'Day'))TO_CHAR(promo_end_date,'fmDay')TO_CHAR(promo_end_date,'fxDay')得到星期几TRIM(TO_CHAR(promo_end_date,'Month'))TO_CHAR(promo_end_date,'fmMonth')TO_CHAR(promo_end_date,'fxMonth')得到几月TRIM(TO_CHAR(promo_end_date,'DD,YYYY')) TO_CHAR(promo_end_date,'fmDD,YYYY')TO_CHAR(promo_end_date,'fxDD,YYYY')得到 几号，年份
93. View the Exhibit andexamine the structure of the CUSTOMERS table.Using the CUSTOMERStable, y ou need to generate a report that shows an increase in the creditlimitby 15% for allcustomers. Customers whose credit limit has not been entered should have themessage "Not Available"displayed.Which SQL statementwould produce the required result?A. SELECTNVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT"FROM customers;B. SELECTNVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT"FROM customers;C. SELECTTO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT"FROM customers;D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'NotAvailable') "NEW CREDIT"FROM customers;Answer: D解析：需要处理空值，Nvl 如果为空值，就返回后面的字符串TO_CHAR(cust_credit_limit*.15)先计算，如果cust_credit_limit为空，最终结果还是为空，所以返回 Not Availiable
94. Examine thestructure of the PROGRAMS table:name Null TypePROG_ID NOT NULLNUMBER(3)PROG_COST NUMBER(8,2)START_DATE NOT NULL DATEEND_DATE DATEWhich two SQL statementswould execute successfully? (Choose two.)A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE)FROM programs;B. SELECTTO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))FROM programs;C. SELECTNVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')FROM programs;D. SELECTNVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')FROM programs;Answer: AD解析：B选项，SYSDATE-END_DATE运算结果不能作为正确的日期格式C选项，无法返回 'Ongoing'引用官方文档：The arguments expr1 andexpr2 can have any data type. If their data types aredifferent, then OracleDatabase implicitly converts one to the other. If they cannot beconverted implicitly,then the database returns an error
95. The PRODUCTS tablehas the following structure:name Null TypePROD_ID NOT NULLNUMBER(4)PROD_NAME VARCHAR2(25)PROD_EXPIRY_DATE DATEEvaluate the followingtwo SQL statements:SQL>SELECT prod_id,NVL2(prod_expiry_date, prod_expiry_date + 15,'')FROM products;SQL>SELECT prod_id,NVL(prod_expiry_date, prod_expiry_date + 15)FROM products;Which statement is trueregarding the outcome?A. Both the statementsexecute and give different results.B. Both the statementsexecute and give the same result.C. Only the first SQLstatement executes successfully.D. Only the second SQLstatement executes successfully.Answer: A解析：NVL2(prod_expiry_date,prod_expiry_date + 15,'')如果prod_expiry_date为空，返回’’，否则返回 prod_expiry_date+15NVL(prod_expiry_date,prod_expiry_date + 15) 如果 prod_expiry_date为空，返回prod_expiry_date+15，否则返回prod_expiry_date
96. Examine thestructure of the INVOICE table.name Null TypeINV_NO NOT NULLNUMBER(3)INV_DATE DATEINV_AMT NUMBER(10,2)Which two SQL statementswould execute successfully? (Choose two.)A. SELECTinv_no,NVL2(inv_date,'Pending','Incomplete')FROM invoice;B. SELECTinv_no,NVL2(inv_amt,inv_date,'Not Available')FROM invoice;C. SELECTinv_no,NVL2(inv_date,sysdate-inv_date,sysdate)FROM invoice;D. SELECTinv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')FROM invoice;Answer: AC解析：官方文档：The argument expr1 canhave any data type. The arguments expr2 and expr3 canhave any data typesexcept LONG.If the datatypes of expr2 and expr3 are different, then Oracle Database implicitlyconverts oneto the other. If they cannot be converted implicitly, then the databasereturns anerror
97. View the Exhibit andevaluate the structure and data in the CUST_STATUS table.You issue the followingSQL statement:SQL> SELECT custno,NVL2(NULLIF(amt_spent, credit_limit), 0, 1000)"BONUS"FROM cust_status;Which statement is trueregarding the execution of the above query?A. It produces an errorbecause the AMT_SPENT column contains a null value.B. It displays a bonusof 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.C. It displays a bonusof 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, orAMT_SPENT is null .D. It produces an errorbecause the TO_NUMBER function must be used to convert the result of theNULLIF function beforeit can be used by the NVL2 function.Answer: C解析：Nullif 如果里面的两个表达式相等，就返回空，但是如果有其中一个表达式为空，也将返回为空
98. Which statement istrue regarding the COALESCE function?A. It can have a maximumof five expressions in a list.B. It returns thehighest NOT NULL value in the list for all rows.C. It requires that allexpressions in the list must be of the same data type.D. It requires that atleast one of the expressions in the list must have a NOT NULL value.Answer: C解析：引用官方文档：COALESCE returns the firstnon-null expr in the expression list. You must specify atleast two expressions.If all occurrences of expr evaluate to null, then the functionreturns null.
If all occurrences ofexpr are numeric data type or any nonnumeric data type that canbe implicitly convertedto a numeric data type, then Oracle Database determines theargument with thehighest numeric precedence, implicitly converts the remainingarguments to that datatype, and returns that data type.
99. View the Exhibit andexamine the structure of the PROMOTIONS table.Using the PROMOTIONStable, you need to find out the average cost for all promos in the ranges$0-2000 and $2000-5000in category AYou issue the followingSQL statement:SQL>SELECT AVG(CASEWHEN promo_cost BETWEEN0 AND 2000 AND promo_category='A'then promo_costELSE null END)"CAT_2000A",AVG(CASEWHEN promo_cost BETWEEN2001 AND 5000 AND promo_category='A'THEN promo_costELSE null END)"CAT_5000A"FROM promotions;What would be theoutcome?A. It executessuccessfully and gives the required result.B. It generates an errorbecause NULL cannot be specified as a return value.C. It generates an errorbecause CASE cannot be used with group functions.D. It generates an errorbecause multiple conditions cannot be specified for the WHEN clause.Answer: A解析：找到在平均 promo_cost在$-2000和$2000-5000范围类，并且属于A的AVG(CASEWHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='A'then promo_costELSE null END)"CAT_2000A",满足条件，输出 promo_cost否则输出空同理：AVG(CASEWHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='A'THEN promo_costELSE null END)"CAT_5000A"
100. View the Exhibitand examine the structure of the PROMOTIONS table.Which SQL statements arevalid? (Choose all that apply.)A. SELECT promo_id,DECODE(NVL(promo_cost,0), promo_cost,promo_cost * 0.25, 100)"Discount"FROM promotions;B. SELECT promo_id,DECODE(promo_cost, 10000,DECODE(promo_category,'G1', promo_cost *.25, NULL),NULL)"Catcost"FROM promotions;C. SELECT promo_id,DECODE(NULLIF(promo_cost, 10000),NULL, promo_cost*.25,'N/A') "Catcost"FROM promotions;D. SELECT promo_id,DECODE(promo_cost, >10000, 'High',<10000, 'Low')"Range"FROM promotions;Answer: AB解析：这里主要考察decode的用法，引用官方文档：DECODE compares expr toeach search value one by one. If expr is equal to asearch, then OracleDatabase returns the corresponding result. If no match isfound, then Oraclereturns default. If default is omitted, then Oracle returns null.
The arguments can be anyof the numeric types (NUMBER, BINARY_FLOAT, orBINARY_DOUBLE) orcharacter types.
For example:
SELECT product_id,DECODE (warehouse_id, 1,'Southlake',2, 'San Francisco',3, 'New Jersey',4, 'Seattle','Non domestic')"Location"FROM inventoriesWHERE product_id <1775ORDER BY product_id,"Location";
101. Examine the data inthe PROMO_BEGIN_DATE column of the PROMOTIONS table:PROMO_BEGIN _DATE04-jan-0010-jan-0015-dec-9918-oct-9822-aug-99You want to display thenumber of promotions started in 1999 and 2000.Which query gives thecorrect output?A. SELECTSUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000",SUM(DECODE(SUBSTR(promo_begin_date,8),'99',1,0))"1999"FROM promotions;B. SELECT SUM(CASETO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1ELSE 0 END)"1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1ELSE 0 END)"2000"FROM promotions;C. SELECT COUNT(CASETO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1ELSE 0 END)"1999",COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1ELSE 0 END)"2000"FROM promotions;D. SELECTCOUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1, 0))"1999",COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'),8),'2000', 1,0)) "2000"FROM promotions;Answer: A解析：题目意思要求分别统计1999年和2000的数量首先得区分1999和2000才能分别进行统计SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0))SUM(DECODE(SUBSTR(promo_begin_date,8),'99',1,0))截取后面两位数字，如果是00就是2000如果是99就是1999
102. Examine thestructure of the TRANSACTIONS table:name Null TypeTRANS_ID NOT NULLNUMBER(3)CUST_NAME VARCHAR2(30)TRANS_DATETIMESTAMPTRANS_AMT NUMBER(10,2)You want to display thedate, time, and transaction amount of transactions that where done before 12noon. The value zeroshould be displayed for transactions where the transaction amount has not beenentered.Which query gives therequired result?A. SELECTTO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),TO_CHAR(trans_amt,'$99999999D99')FROM transactionsWHERETO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 ANDCOALESCE(trans_amt,NULL)<>NULL;B. SELECTTO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),NVL(TO_CHAR(trans_amt,'$99999999D99'),0)FROM transactionsWHERETO_CHAR(trans_date,'hh24') < 12;C. SELECTTO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0)FROM transactionsWHERETO_DATE(trans_date,'hh24') < 12;D. SELECT TO_DATE(trans_date,'dd-mon-yyyy hh24:mi:ss'),NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'),0)FROM transactionsWHERETO_DATE(trans_date,'hh24') < 12;Answer: B解析：题目的意思找出12点之前的事务的时间以及rans_amt，如果为空就显示为012点之前，正确的表示：TO_CHAR(trans_date,'hh24')< 12
TO_DATE(trans_date,'hh24')中日期格式不正确 C D错误
如果为空就显示为0，A选项没有处理
103. Examine thestructure of the TRANSACTIONS table:name Null TypeTRANS_ID NOT NULLNUMBER(3)CUST_NAME VARCHAR2(30)TRANS_DATE DATETRANS_AMT NUMBER(10,2)You want to display thetransaction date and specify whether it is a weekday or weekend.Evaluate the followingtwo queries:SQL>SELECTTRANS_DATE,CASEWHENTRIM(TO_CHAR(trans_date,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend'ELSE 'weekday'END "Day Type"FROM transactions;SQL>SELECTTRANS_DATE, CASEWHENTO_CHAR(trans_date,'DAY') BETWEEN 'MONDAY' AND 'FRIDAY' THEN 'weekday'ELSE 'weekend'END "DayType"FROM transactions;Which statement is trueregarding the above queries?A. Both give wrongresults.B. Both give the correctresult.C. Only the first querygives the correct result.D. Only the second querygives the correct result.Answer: C解析：BETWEEN 'MONDAY' AND'FRIDAY' 这里错误，不会按星期的顺序去一一比较
104. Examine thestructure of the PROMOS table:name Null TypePROMO_ID NOT NULLNUMBER(3)PROMO_NAME VARCHAR2(30)PROMO_START_DATE NOTNULL DATEPROMO_END_DATE DATEYou want to generate areport showing promo names and their duration (number of days). If thePROMO_END_DATE has notbeen entered, the message 'ONGOING' should be displayed.Which queries give thecorrect output? (Choose all that apply.)A. SELECT promo_name,TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING'))FROM promos;B. SELECTpromo_name,COALESCE(TO_CHAR(promo_end_date - promo_start_date),'ONGOING')FROM promos;C. SELECT promo_name,NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING')FROM promos;D. SELECT promo_name,DECODE(promo_end_date-promo_start_date,NULL,'ONGOING',promo_end_date- promo_start_date)FROM promos;E. SELECT promo_name,decode(coalesce(promo_end_date,promo_start_date),null,'ONGOING',promo_end_date -promo_start_date)FROM promos;Answer: BCD解析：A选项，nvl中两个表达式数据类型不一样，也不能隐式转换E选项，coalesce(promo_end_date,promo_start_date) 返回第一个非空值，但是有可能其中一个是空值
105. Examine thestructure of the PROMOS table:name Null TypePROMO_ID NOT NULLNUMBER(3)PROMO_NAME VARCHAR2(30)PROMO_START_DATE NOTNULL DATEPROMO_END_DATE NOT NULLDATEYou want to display thelist of promo names with the message 'Same Day' for promos that started andended on the same day.Which query gives thecorrect output?A. SELECT promo_name,NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day')FROM promos;B. SELECT promo_name,NVL(TRUNC(promo_end_date - promo_start_date), 'Same Day')FROM promos;C. SELECT promo_name,NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL,'SameDay')FROM promos;D. SELECT promo_name,DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,'Same day')FROM promos;Answer: D解析：题目意思如果开始和结束为同一天，就输出’Same Day’NULLIF(promo_start_date,promo_end_date) 如果两个表达式相同，则返回null
我也曾幻想我们的未来＇可我﹑看到一片箜白＇！ 我会把你们的好放在心里，永远不忘记 帮你顶下哈！！ 我是心甘情愿的，所以没有为什么，只有自己知道那是骗人的 你都好意思和我分手，我有什么不好意思在你背后给你一刀呢 、谁都知道、那说不出的不安、永远没有答案……
页:
[1]