047-159

159. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS

tables.Evaluate the following SQL statement:

SELECT oi.order_id, product_id, order_date

FROM order_items oi JOIN orders o USING(order_id);

Which statement is true regarding the execution of this SQL statement?

A. The statement would not execute because table aliases are not allowed in

the JOIN clause.

B. The statement would not execute because the table alias prefix is not used

in the USING clause.

C. The statement would not execute because all the columns in the SELECT

clause are not prefixed with table aliases.

D. The statement would not execute because the column part of the USING clause

cannot have a qualifier in the SELECT list.

Answer: D

Eg:

1:create table order_items(order_id number(12),line_item_id number(3),product_id

number(6));

create table orders(order_id number(12),order_date timestamp(6) with local time zone);

2:insert into order_items values(1,100,1000);

insert into orders values(1,to_timestamp ('2010/01/09 14:51:21','yyyy/mm/dd

hh24:mi:ss'));

3: USING 子句的列部分不能有限定词

select o.order_id,product_id,order_date

from order_items oi join orders o

using(order_id);

ORA-25154: USING 子句的列部分不能有限定词

4:using子句里也不能有限定词

select order_id,product_id,order_date

from order_items oi left join orders o

using(oi.order_id);

ORA-01748: 此处只允许简单的列名

5: ok (不在using里可以用)

select order_id,oi.product_id,order_date

from order_items oi join orders o

using(order_id);

6: ok

select order_id,product_id,order_date

from order_items oi join orders o

using(order_id);

7:ok 类似natural join

select order_id,oi.product_id,order_date

from order_items oi natural join orders o;

The keyword USING is similar to the natural join, in the sense that its

use depends on the presence of identically named columns in the JOIN.

No table name prefix is allowed before the column name,not here, and not

elsewhere in the statement,The USING keyword does basically the same thing

as the natural join in the sense that the connection between the joined tables

is performed automatically. The difference is that USING lets us perform. an

outer join as well as an inner join.

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

转载于:http://blog.itpub.net/11312660/viewspace-718967/