OCP-1Z0-051-题目解析-第17题

17. View the E xhibit 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: 

<span style="font-family:Verdana;">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; </span>

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. 

Answer: C 

题目解析：

这道题给的sql问题比较多，可能是在复制的过程中丢掉了部分符号，根据题意猜测，这道题目给的sql应该是

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

可以在scott用户下执行下。

我们看下上面的employees表格，可以看到comm有很多值是NULL，而任何值加上NULL的结果都是NULL，所以这个sql的输出结果肯定不是我们想要的

可以通过nvl 或者decode函数判读NULL值，正确的sql语句应该是

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

ENAME||'JOINEDON'||HIREDATE||',THETOTALCOMPENSATIONPAIDIS'         COMPENSATION UNTIL DATE
------------------------------------------------------------------ ----------------------------------------
SMITH joined on 17-DEC-80, the total compensation paid is          27200
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          98175
MARTIN joined on 28-SEP-81, the total compensation paid is         42650
BLAKE joined on 01-MAY-81, the total compensation paid is          94050
CLARK joined on 09-JUN-81, the total compensation paid is          80850
SCOTT joined on 19-APR-87, the total compensation paid is          81000
KING joined on 17-NOV-81, the total compensation paid is           165000
TURNER joined on 08-SEP-81, the total compensation paid is         49500
ADAMS joined on 23-MAY-87, the total compensation paid is          29700
JAMES joined on 03-DEC-81, the total compensation paid is          31350
FORD joined on 03-DEC-81, the total compensation paid is           99000
MILLER joined on 23-JAN-82, the total compensation paid is         41600

14 rows selected.

看下答案：

A：会产生一个错误，因为别名无效（错误，别名"COMPENSATION UNTIL DATE" 没有任何问题）

B: 语句能正确执行，并给出正确的结果（错误，给出的结果是错误的）

C: 语句能正确执行，但不能给出正确的结果（正确）

D: 会产生一个错误，因为在表的式中使用round函数是无效的（错误，表达式中可以使用round函数）

E:会产生一个错误，因为连接操作只能连接两个项目（错误，可以连接多个）