OCP-1Z0-051-V9.02-156题

156. Evaluate the following SQL statement:

SQL> SELECT cust_id, cust_last_name "Last Name"

FROM customers

WHERE country_id = 10

UNION

SELECT cust_id CUST_NO, cust_last_name

FROM customers

WHERE country_id = 30;

Which ORDER BY clauses are valid for the above query? (Choose all that apply.)

A. ORDER BY 2,1

B. ORDER BY CUST_NO

C. ORDER BY 2,cust_id

D. ORDER BY "CUST_NO"

E. ORDER BY "Last Name"

Answer: ACE

答案解析：

ORDER BY 后面可以跟数字，可以完全一摸一样的列名,经过union后，字段名根据第一个查询语句显示，变为cust_id和Last Name。

sh@TEST0924> SELECT cust_id, cust_last_name "Last Name" from customers where country_id=52770 and rownum<5

  2  union

  3  select cust_id CUST_NO,cust_last_name from customers where country_id=52775 and rownum<5;

 

   CUST_ID Last Name

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

      3228 Ruddy

      6783 Ruddy

     10338 Ruddy

     13894 Ruddy

     21005 Ruddy

     37004 Embrey

     40559 Embrey

     49671 Ruddy

 

8 rows selected.

A答案：

sh@TEST0924> SELECT cust_id, cust_last_name "Last Name" from customers where country_id=52770 and rownum<5

  2  union

  3  select cust_id CUST_NO,cust_last_name from customers where country_id=52775 and rownum<5

  4  ORDER BY 2,1;

 

   CUST_ID Last Name

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

     37004 Embrey

     40559 Embrey

      3228 Ruddy

      6783 Ruddy

     10338 Ruddy

     13894 Ruddy

     21005 Ruddy

     49671 Ruddy

 

8 rows selected.

 

B答案：报错，没有CUST_NO字段。

sh@TEST0924> SELECT cust_id, cust_last_name "Last Name" from customers where country_id=52770 and rownum<5

  2  union

  3   select cust_id CUST_NO,cust_last_name from customers where country_id=52775 and rownum<5

  4  ORDER BY CUST_NO;

ORDER BY CUST_NO

         *

ERROR at line 4:

ORA-00904: "CUST_NO": invalid identifier

 

C答案：

sh@TEST0924> SELECT cust_id, cust_last_name "Last Name" from customers where country_id=52770 and rownum<5

  2  union

  3  select cust_id CUST_NO,cust_last_name from customers where country_id=52775 and rownum<5

  4  ORDER BY 2,cust_id

  5  ;

 

   CUST_ID Last Name

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

     37004 Embrey

     40559 Embrey

      3228 Ruddy

      6783 Ruddy

     10338 Ruddy

     13894 Ruddy

     21005 Ruddy

     49671 Ruddy

 

8 rows selected.

 

D答案：报错

sh@TEST0924> SELECT cust_id, cust_last_name "Last Name" from customers where country_id=52770 and rownum<5

  2  union

  3  select cust_id CUST_NO,cust_last_name from customers where country_id=52775 and rownum<5

  4  ORDER BY "CUST_NO"

  5  ;

ORDER BY "CUST_NO"

         *

ERROR at line 4:

ORA-00904: "CUST_NO": invalid identifier

 

E答案：

sh@TEST0924> SELECT cust_id, cust_last_name "Last Name" from customers where country_id=52770 and rownum<5

  2  union

  3  select cust_id CUST_NO,cust_last_name from customers where country_id=52775 and rownum<5

  4  ORDER BY "Last Name"

  5  ;

 

   CUST_ID Last Name

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

     37004 Embrey

     40559 Embrey

      3228 Ruddy

      6783 Ruddy

     10338 Ruddy

     13894 Ruddy

     21005 Ruddy

     49671 Ruddy

 

8 rows selected.