ocp11g lz0-047 question 111

QUESTION 111

View the Exhibit and examine the data in the EMPLOYEES tables.
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 10;

What would be the outcome of the above SQL statement?
A. The statement would execute successfully and display all the rows in the ascending order of
DEPARTMENT_ID.
B. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows
in random order.
C. The statement would not execute because the positional notation instead of the column name should be
used with the ORDER BY clause.
D. The statement would not execute because the ORDER BY clause should appear only at the end of the
SQL statement, that is, in the last SELECT statement.

Answer: D

验证过程：在此我使用scott用户下的emp表来进行结果验证。

SQL> conn scott/tiger
Connected.

SQL> select empno,deptno from emp where deptno=10 order by deptno
  2  union
  3  select empno,deptno from emp where deptno=20
  4  union
  5  select empno,deptno from emp where deptno=30;
union
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

sql语句执行失败A,B选项错误。

SQL> l
  1  select empno,deptno from emp where deptno=10 order by deptno
  2  union
  3  select empno,deptno from emp where deptno=20
  4  union
  5* select empno,deptno from emp where deptno=30

--修改第一行将order by 子句中的列名换成列的所在位置即列数

SQL> 1                   
  1* select empno,deptno from emp where deptno=10 order by deptno
SQL> c/order by deptno/

order by 2

 1* select empno,deptno from emp where deptno=10  order by 2

--修改order by 子句之后的sql语句
SQL> l
  1  select empno,deptno from emp where deptno=10  order by 2
  2  union
  3  select empno,deptno from emp where deptno=20
  4  union
  5* select empno,deptno from emp where deptno=30

--执行sql

SQL> /
union
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

sql语句执行失败，C选项错误。

--去掉order by 子句
SQL> 1
  1* select empno,deptno from emp where deptno=10 order by 2
SQL> c/order by 2/
  1* select empno,deptno from emp where deptno=10

--去掉order by 子句后的sql
SQL> l
  1  select empno,deptno from emp where deptno=10
  2  union
  3  select empno,deptno from emp where deptno=20
  4  union
  5* select empno,deptno from emp where deptno=30
SQL> /

     EMPNO     DEPTNO
---------- ----------
      7369         20
      7499         30
      7521         30
      7566         20
      7654         30
      7698         30
      7782         10
      7788         20
      7839         10
      7844         30
      7876         20
      7900         30
      7902         20
      7934         10
14 rows selected.

sql语句执行成功，并且按empno进行排序输出。

--在sql语句末尾加上order by 子句
SQL> l
  1  select empno,deptno from emp where deptno=10
  2  union
  3  select empno,deptno from emp where deptno=20
  4  union
  5* select empno,deptno from emp where deptno=30

SQL> 5
  5* select empno,deptno from emp where deptno=30
SQL> a   order by deptno
  5* select empno,deptno from emp where deptno=30  order by deptno

--末尾加上order by 子句之后的sql
SQL> l
  1  select empno,deptno from emp where deptno=10
  2  union
  3  select empno,deptno from emp where deptno=20
  4  union
  5* select empno,deptno from emp where deptno=30 order by deptno
SQL> /
     EMPNO     DEPTNO
---------- ----------
      7782         10
      7839         10
      7934         10
      7369         20
      7566         20
      7788         20
      7876         20
      7902         20
      7499         30
      7521         30
      7654         30
      7698         30
      7844         30
      7900         30
14 rows selected.

sql语句执行成功，并且按deptno排序输出。

所以D选项正确，order by 子句应该放在sql语句的最后。

补充：
此题主要考查的是union，和union all的相关知识，要知道这两者的区别
union：取并集，但会去掉集间合重复的部分，会排序；
union all：取并集，不会去掉集合间重复的部分，不会排序。
还要注意union 和 order by 连用时，order by 放在语句末尾。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29457434/viewspace-1079419/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29457434/viewspace-1079419/