现在的位置: 首页 > 综合 > 正文

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

2014年09月05日 ⁄ 综合 ⁄ 共 2612字 ⁄ 字号 评论关闭

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.

抱歉!评论已关闭.