OCP 1Z0 051 155

155. Evaluate the following SQL statement: 
SQL> SELECT promo_id, promo_category 
FROM promotions 
WHERE promo_category = 'Internet' ORDER BY 2 DESC 
UNION 
SELECT promo_id, promo_category 
FROM promotions 
WHERE promo_category = 'TV' 
UNION 
SELECT promo_id, promo_category 
FROM promotions 
WHERE promo_category ='Radio'; 
Which statement is true regarding the outcome of the above query? 
A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY. 
B. It produces an error because positional notation cannot be used in the   ORDER BY clause with SET 
operators. 
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the 
compound statement. 
D.  It produces an error because  the ORDER BY clause should appear only at  the end of a compound 
query-that is, with the last SELECT statement. 

普通语句 order by 可以用别名，列名，列位置

SQL> select dname,loc from dept order by deptno;DNAME          LOC-------------- -------------ACCOUNTING     NEW YORKRESEARCH       DALLASSALES          CHICAGOOPERATIONS     BOSTON4 rows selectedSQL> select dname,loc from dept order by 1;DNAME          LOC-------------- -------------ACCOUNTING     NEW YORKOPERATIONS     BOSTONRESEARCH       DALLASSALES          CHICAGO4 rows selectedSQL> select dname as 部门,loc from dept order by 部门;部门           LOC-------------- -------------ACCOUNTING     NEW YORKOPERATIONS     BOSTONRESEARCH       DALLASSALES          CHICAGO4 rows selected

但当有union minus这些集合操作时就只能用第一个集合的别名，或列位置

SQL> select id as col1 from t1 union select no from t2 order by id;select id as col1 from t1 union select no from t2 order by idORA-00904: "ID": invalid identifierSQL> select id as col1 from t1 union select no from t2 order by col1;COL1----   1   2   34 rows selectedSQL> select id as col1 from t1 union select no from t2 order by 1;COL1----   1   2   34 rows selected

且order by 只能放在最后，如果放面前面的集合会报错

SQL> select id as col1 from t1 order by 1 union select no from t2;select id as col1 from t1 order by 1 union select no from t2ORA-00933: SQL command not properly ended

Answer: D