OCP 071 View the Exhibit and exa

一、原题

View the Exhibit and examine the structure of the CUSTOMERS table.

Using the CUSTOMERS table,you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.

Which SQL statement would produce the required result?

A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT"

        FROM customers;

B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT"

        FROM customers;

C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT"

        FROM customers;

D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT"

        FROM customers;

答案：D

二、题目翻译

下面是CUSTOMERS表的结构：

根据CUSTOMERS表生成一个报表，显示所有客户增长15%的credit limit.如果客户没有credit limit则显示Not Available.

哪个SQL语句给出所需结果？

三、题目解析

A、B、C选项不正确，原因类似，NVL函数第一个参数是number类型，第二个参数是字符类型，无法隐式转换。

D选项正确，先将cust_credit_limit * 0.15，这是数值类型，用to_char转成字符类型，nvl的两个参数类型一致，就正确了。

四、测试

这里，使用oracle已经有的测试表emp，其中comm列有null值,测试如下：

SQL> SELECT NVL(comm,'Not Available')*.15 "NEW CREDIT"

  2  FROM emp;

SELECT NVL(comm,'Not Available')*.15 "NEW CREDIT"

                *

ERROR at line 1:

ORA-01722: invalid number

SQL> SELECT NVL(comm*.15,'Not Available') "NEW CREDIT"

  2  FROM emp;

SELECT NVL(comm*.15,'Not Available') "NEW CREDIT"

                    *

ERROR at line 1:

ORA-01722: invalid number

SQL> SELECT TO_CHAR(NVL(comm*.15,'Not Available')) "NEW CREDIT"

  2  FROM emp;

SELECT TO_CHAR(NVL(comm*.15,'Not Available')) "NEW CREDIT"

                            *

ERROR at line 1:

ORA-01722: invalid number

SQL> SELECT NVL(TO_CHAR(comm*.15),'Not Available') "NEW CREDIT"

  2  FROM emp;

NEW CREDIT

----------------------------------------------------------------

Not Available

45

75

Not Available

210

Not Available

Not Available

Not Available

Not Available

0

Not Available

Not Available

Not Available

Not Available

14 rows selected.