ocp1Z0-05171-105题解析
71. Which arithmeticoperations can be performed on a column by using a SQL function that is builtinto Oracle database ?(Choose three .) A. a ddition B. s ubtraction C. r aising to a power D. f inding the quotient E. f inding the lowestvalu
71. Which arithmeticoperations can be performed on a column by using a SQL function that is builtinto
Oracle database ?(Choose three .)
A. a ddition
B. s ubtraction
C. r aising to a power
D. f inding the quotient
E. f inding the lowestvalue
Answer: 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 format
B. f inding the numberof characters in an expression
C. s ubstituting acharacter string in a text expression with a specified string
D. c ombining more thantwo columns or expressions into a single column in the output
Answer: ABC
解析：
A选项，to_char可以转换多种日期格式，详解见：
http://blog.csdn.net/zbdba/article/details/17042195
B选项，length(char)
C选项，replace(char1,char2,char3)
D选项，无
73. Which tasks can beperformed using SQL functions that are built into Oracle database ? (Choose
three .)
A. f inding theremainder of a division
B. a dding a number to adate for a resultant date value
C. c omparing twoexpressions to check whether they are equal
D. c hecking whether aspecified character exists in a given string
E. r emoving trailing,leading, and embedded characters from a character string
Answer: 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,[m[n]])
E选项，无
74. Which statements aretrue regarding single row functions? (Choose all that apply.)
A. MOD : returns thequotient of a division
B. TRUNC : can be usedwith NUMBER and DATE values
C. CONCAT : can be usedto combine any number of values
D. SYSDATE : returns thedatabase server current date and time
E. INSTR : can be usedto find only the first occurrence of a character in a string
F. TRIM : can be used toremove all the occurrences of a character from a string
Answer: BD
解析：
引用官方文档解释 Single-rowfunctions：
Single-rowfunctions return a single resultrow for every row of a queried table or
view. These functionscan appear in select lists, WHERE clauses, START WITH and
CONNECT BY clauses, andHAVING clauses.
A选项，mod应该是返回余数，而不是商
B选项，引用文档中的一段话：
This function takes as an argument any numeric data type or any nonnumeric data
type that canbe implicitly converted to a numeric data type. If you omit n2, then the
function returns thesame data type as the numeric data type of the argument. If you
include n2, then thefunction returns NUMBER.
C选项，concat(char1,char2)用于连接字符串
D选项，sysdate放回系统当前时间
scott@ORCL>selectsysdate from dual;
SYSDATE
--------------
02-12月-13
E选项，instr(char1,char2,[m[,n]])
The INSTR functionssearch string for substring. The search operation is defined
as comparing thesubstring argument with substrings of string of the same length
for equality until amatch is found or there are no more substrings left. Each
consecutive comparedsubstring of string begins one character to the right (for
forward searches) or onecharacter to the left (for backward searches) from the first
character of theprevious compared substring. If a substring that is equal to
substring is found, thenthe function returns an integer indicating the position of
the first character ofthis substring. If no such substring is found, then the function
returns 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_PRICE
123456 152525.99
You issue the followingquery:
SQL> SELECT RPAD((ROUND(prod_list_price)), 10,'*')
FROM products
WHERE prod_id = 123456;
What would be theoutcome?
A. 152526 ****
B. **152525.99
C. 152525** **
D. an error message
Answer: A
解析：
round(m,n)不指定n的值，直接取整数，为152525
Rpad(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 the
character 'e' and havethe character 'a' in the second last position.
Which query would givethe required output?
A. SELECTcust_first_name
FROM customers
WHEREINSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name,-2, 1)='a';
B. SELECTcust_first_name
FROM customers
WHEREINSTR(cust_first_name, 'e')<>'' AND
SUBSTR(cust_first_name,-2, 1)='a';
C. SELECTcust_first_name
FROM customers
WHEREINSTR(cust_first_name, 'e')IS NOT NULL AND
SUBSTR(cust_first_name,1,-2)='a';
D. SELECTcust_first_name
FROM customers
WHEREINSTR(cust_first_name, 'e')<>0 AND
SUBSTR(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 the
CUST_FIRST_NAME'ABIGAIL'.
Evaluate the followingquery:
SQL> SELECT INITCAP(cust_first_name || ' ' ||
UPPER(SUBSTR(cust_city,-LENGTH(cust_city),2)))
FROM customers
WHERE cust_first_name ='ABIGAIL';
What would be theoutcome?
A. Abigail PA
B. Abigail Pa
C. Abigail IS
D. an error message
Answer: 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. 16
B. 100
C. 160
D. 200
E. 150
Answer: 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 CUSTOMERS
WHERELOWER(cust_last_name) LIKE 'a%n';
What would be theoutcome?
A. 'Oder' and 'Aus'
B. a n error because theTRIM function specified is not valid
C. a n error because theLOWER function specified is not valid
D. a n error because theREPLACE function specified is not valid
Answer: B
解析：
scott@ORCL>selecttrim('son' from 'anderson') from dual;
select trim('son' from'anderson') from dual
*
第 1 行出现错误:
ORA-30001: 截取集仅能有一个字符
Trim的用法：
trim([leading | trailing| both] 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 not
allow the user to enterthe century.
D. The RR date formatautomatically calculates the century from the SYSDATE function but allows the
user to enter thecentury if required.
Answer: AD
解析：
Oracle内部存储的时间为数值格式
The RR datetime formatelement is similar to the YY datetime format element, but it
provides additionalflexibility for storing date values in other centuries. The RR
datetime format elementlets you store 20th century dates in the 21st century by
specifying 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 promotions
WHERE(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 invalid
B. a n error because theWHERE condition specified is invalid
C. n umber of days sincethe promo started based on the current Chicago date and time
D. number of days sincethe promo started based on the current Singapore date and time
Answer: C
解析：
当然是基于chicago的时间，因为sysdate也是获取chicago当前的时间
82. Examine the data inthe CUST_NAME column of the CUSTOMERS table.
CUST_NAME
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You 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 customers
WHEREINITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
B. SELECTSUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHEREINITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
C. SELECTSUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE SUBSTR(cust_name,INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
D. SELECTSUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHEREINITCAP(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_NAME
Lex De Haan
Renske Ladwig
Jose Manuel Urman
Jason Mallin
You want to extract onlythose customer names that have three names and display the * symbol in place
of the first name asfollows:
CUST NAME
*** De Haan
**** Manuel Urman
Which two queries givethe required output? (Choose two.)
A. SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUSTNAME"
FROM customers
WHERE INSTR(cust_name, '',1,2)<>0;
B. SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUSTNAME"
FROM customers
WHERE INSTR(cust_name, '',-1,2)<>0;
C. SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-
INSTR(cust_name,''),'*') "CUST NAME"
FROM customers
WHERE INSTR(cust_name, '',-1,-2)<>0;
D. SELECTLPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-
INSTR(cust_name,''),'*') "CUST NAME"
FROM customers
WHERE INSTR(cust_name, '',1,2)<>0 ;
Answer: AB
解析：
以Lex De Haan为例：
LPAD(SUBSTR(cust_name,INSTR(cust_name,'')),LENGTH(cust_name),'*')
,INSTR(cust_name,' ')返回空字符串的位置 4
SUBSTR(cust_name,4') 返回指定位置后的字符串 De Hann
LPAD(‘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 HIREDATE
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
You want to generate alist of user IDs as follows:
USERID
Smi17DEC80
All20FEB81
War22FEB81
You 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) 返回首字母大写，其他字母小写 Smith
SUBSTR(‘Smith’,1,3) 截取字串 Smi
REPLACE(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 all
that apply.)
A. inv_amt ='0255982': requires explicit conversion
B. inv_date >'01-02-2008' : uses implicit conversion
C.CONCAT(inv_amt,inv_date) : requires explicit conversion
D. inv_date ='15-february-2008' : uses implicit conversion
E. inv_no BETWEEN '101'AND '110' : uses implicit conversion
Answer: DE
B选项不能隐式转换，需要sql函数转换
86. Examine thestructure and data of the CUST_TRANS table:
CUST_TRANS
Name Null Type
CUSTNO NOT NULL CHAR(2)
TRANSDATE DATE
TRANSAMT NUMBER(6,2)
CUSTNO TRANSDATETRANSAMT
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000
Dates 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 following
command:
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 semantics
sys@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')
2 from 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 Type
PROD_ID NOT NULLNUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD_PRICE
100 $234.55
101 $6,509.75
102 $1,234
You plan to give adiscount of 25% on the product price and need to display the discount amount inthe
same 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.csdn.net/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 promotions
SET promo_cost =promo_cost+ 100
WHERETO_CHAR(promo_end_date, 'yyyy') > '2000';
B. SELECTpromo_begin_date
FROM promotions
WHERETO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';
C. UPDATE promotions
SET promo_cost =promo_cost+ 100
WHERE promo_end_date> TO_DATE(SUBSTR('01-JAN-2000',8));
D. SELECTTO_CHAR(promo_begin_date,'dd/month')
FROM promotions
WHERE promo_begin_dateIN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));
Answer: AB
A选项，测试：
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 of
the 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_day
FROM 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_day
FROM 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_day
FROM 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_day
FROM 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 creditlimit
by 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 Type
PROG_ID NOT NULLNUMBER(3)
PROG_COST NUMBER(8,2)
START_DATE NOT NULL DATE
END_DATE DATE
Which 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 are
different, then OracleDatabase implicitly converts one to the other. If they cannot be
converted implicitly,then the database returns an error
95. The PRODUCTS tablehas the following structure:
name Null Type
PROD_ID NOT NULLNUMBER(4)
PROD_NAME VARCHAR2(25)
PROD_EXPIRY_DATE DATE
Evaluate 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+15
NVL(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 Type
INV_NO NOT NULLNUMBER(3)
INV_DATE DATE
INV_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 can
have any data typesexcept LONG.
If the datatypes of expr2 and expr3 are different, then Oracle Database implicitly
converts oneto the other. If they cannot be converted implicitly, then the database
returns 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, or
AMT_SPENT is null .
D. It produces an errorbecause the TO_NUMBER function must be used to convert the result of the
NULLIF 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 at
least two expressions.If all occurrences of expr evaluate to null, then the function
returns null.
If all occurrences ofexpr are numeric data type or any nonnumeric data type that can
be implicitly convertedto a numeric data type, then Oracle Database determines the
argument with thehighest numeric precedence, implicitly converts the remaining
arguments 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 A
You issue the followingSQL statement:
SQL>SELECT AVG(CASE
WHEN promo_cost BETWEEN0 AND 2000 AND promo_category='A'
then promo_cost
ELSE null END)"CAT_2000A",
AVG(CASE
WHEN promo_cost BETWEEN2001 AND 5000 AND promo_category='A'
THEN promo_cost
ELSE 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(CASE
WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='A'
then promo_cost
ELSE null END)"CAT_2000A",
满足条件，输出 promo_cost
否则输出空
同理：
AVG(CASE
WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='A'
THEN promo_cost
ELSE 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 a
search, then OracleDatabase returns the corresponding result. If no match is
found, then Oraclereturns default. If default is omitted, then Oracle returns null.
The arguments can be anyof the numeric types (NUMBER, BINARY_FLOAT, or
BINARY_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 inventories
WHERE product_id <1775
ORDER BY product_id,"Location";
101. Examine the data inthe PROMO_BEGIN_DATE column of the PROMOTIONS table:
PROMO_BEGIN _DATE
04-jan-00
10-jan-00
15-dec-99
18-oct-98
22-aug-99
You 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 1
ELSE 0 END)"1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1
ELSE 0 END)"2000"
FROM promotions;
C. SELECT COUNT(CASETO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1
ELSE 0 END)"1999",COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1
ELSE 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 Type
TRANS_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 12
noon. The value zeroshould be displayed for transactions where the transaction amount has not been
entered.
Which query gives therequired result?
A. SELECTTO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),
TO_CHAR(trans_amt,'$99999999D99')
FROM transactions
WHERETO_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 transactions
WHERETO_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 transactions
WHERETO_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 transactions
WHERETO_DATE(trans_date,'hh24') < 12;
Answer: B
解析：题目的意思找出12点之前的事务的时间以及rans_amt，如果为空就显示为0
12点之前，正确的表示：
TO_CHAR(trans_date,'hh24')< 12
TO_DATE(trans_date,'hh24')中日期格式不正确 C D错误
如果为空就显示为0，A选项没有处理
103. Examine thestructure of the TRANSACTIONS table:
name Null Type
TRANS_ID NOT NULLNUMBER(3)
CUST_NAME VARCHAR2(30)
TRANS_DATE DATE
TRANS_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,CASE
WHENTRIM(TO_CHAR(trans_date,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend'
ELSE 'weekday'
END "Day Type"
FROM transactions;
SQL>SELECTTRANS_DATE, CASE
WHENTO_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 Type
PROMO_ID NOT NULLNUMBER(3)
PROMO_NAME VARCHAR2(30)
PROMO_START_DATE NOTNULL DATE
PROMO_END_DATE DATE
You want to generate areport showing promo names and their duration (number of days). If the
PROMO_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 Type
PROMO_ID NOT NULLNUMBER(3)
PROMO_NAME VARCHAR2(30)
PROMO_START_DATE NOTNULL DATE
PROMO_END_DATE NOT NULLDATE
You want to display thelist of promo names with the message 'Same Day' for promos that started and
ended 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,'Same
Day')
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