OCP-1Z0-051 第17题 null参与运算后仍是null

一、原题

View the Exhibit and examine the data in the EMPLOYEES table.
You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:
SQL>  SELECT ename||' joined on '||hiredate||    
                         ', the total compensation paid is '||    
                         TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365) * sal + comm)    
                         "COMPENSATION UNTIL DATE"    
FROM employees;
What is the outcome?
 
A. It generates an error because the alias is not valid.
B. It executes successfully and gives the correct output.
C. It executes successfully but does not give the correct output.
D. It generates an error because the usage of the  ROUND function in the expression is not valid.
E. It generates an error because the concatenation operator can be used to combine only two items.

答案：C

二、题目翻译

下图是EMPLOYEES表的数据,现在要生成一个报表，显示每位员工获得的总薪酬。
执行下面的查询语句：
SQL>  SELECT ename||' joined on '||hiredate||    
', the total compensation paid is '||    
TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365) * sal + comm)    
"COMPENSATION UNTIL DATE"    
FROM employees;
会产生什么结果？
A. 它产生一个错误，因为别名是无效的。
B. 它执行成功，并且给出正确的输出。
C. 它执行成功，但是没有给出正确结果。
D. 它产生一个错误，因为在表达式中使用ROUND函数是无效的。
E. 它产生一个错误，因为连接操作符只能用来连接两项。

三、题目解析
        这条sql语句能执行成功，但因为COMM里有NULL值，参与运算后会得到空值，会使某些行的值不正确。
        使用scott用户中的emp表测试如下：

SQL>  SELECT ename||' joined on '||hiredate||      2  ', the total compensation paid is '||    
  3  TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365) * sal + comm)    
  4  "COMPENSATION UNTIL DATE"    
  5  FROM emp;

COMPENSATION UNTIL DATE
----------------------------------------------------------------------
SMITH joined on 17-DEC-80, the total compensation paid is
ALLEN joined on 20-FEB-81, the total compensation paid is 53100
WARD joined on 22-FEB-81, the total compensation paid is 41750
JONES joined on 02-APR-81, the total compensation paid is
MARTIN joined on 28-SEP-81, the total compensation paid is 42650
BLAKE joined on 01-MAY-81, the total compensation paid is
CLARK joined on 09-JUN-81, the total compensation paid is
SCOTT joined on 19-APR-87, the total compensation paid is
KING joined on 17-NOV-81, the total compensation paid is
TURNER joined on 08-SEP-81, the total compensation paid is 49500
ADAMS joined on 23-MAY-87, the total compensation paid is

COMPENSATION UNTIL DATE
----------------------------------------------------------------------
JAMES joined on 03-DEC-81, the total compensation paid is
FORD joined on 03-DEC-81, the total compensation paid is
MILLER joined on 23-JAN-82, the total compensation paid is

14 rows selected.

       发现上面很多的结果都是空值(null)，并没有得到我们想要的结果。