OCP-1Z0-051 第64题 order by使用别名注意事项

一、原题

View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.

Evaluate the following two queries:

SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code"

          FROM promotions ORDER BY code;

SQL>SELECT DISTINCT promo_category promo_cost "code"

         FROM promotions ORDER BY 1;

Which statement is true regarding the execution of the above queries?

A. Only the first query executes successfully.

B. Only the second query executes successfully.

C. Both queries execute successfully but give different results.

D. Both queries execute successfully and give the same result.

答案：B

二、题目翻译

查看PROMOTIONS表的PROMO_CATEGORY 和PROMO_COST列的数据

看下面两个查询

下面关于执行上面的查询的描述，哪句话是正确的？

A.只有第一个执行成功。

B.只有第二个执行成功。

C.两个都执行成功，但是结果不同。

D.两个都执行成功，并且结果相同。

三、题目解析

第一句sql执行时报错，因为ORDER BY使用列别名时要完全匹配，如果别名加了双引号，必须也要加双引号。

第二句sql执行成功，1表示使用select后的第一列进行排序。

四、测试

       关于order by 后面别名的使用，测试如下：

SQL> select ename "name"

from emp

order by name;

select ename "name"

from emp

order by name

         *

ERROR at line 1:

ORA-00904: "NAME": invalid identifier

SQL> select ename "name"

from emp

order by "name";

name

--------------------

ADAMS

ALLEN

BLAKE

CLARK

FORD

JAMES

JONES

KING

MARTIN

MILLER

SCOTT

SMITH

TURNER

WARD

14 rows selected.