OCP-1Z0-051 补充题库 第38题 nvl用法

一、原题

See 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%的值,如果用户的信用额度没有被输入(即为null)，则显示为"Not Available".
下面哪个SQL语句能得出上面要求的结果。

三、题目解析

用户信用额度的15%，就是cust_credit_limit*.15,
如果信用额度为null的，显示为"Not Available"，就需要使用nvl函数，来判断cust_credit_limit是否为空。
nvl的两个参数，需要类型一致，因为’Not Available’是字符类型，所以，前面的第一个参数，也需要用to_char转成字符类型。

nvl的用法，详见:
          http://blog.csdn.net/holly2008/article/details/25251513