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 andeva luate 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 eva luate 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