（153）View the Exhibits and examine the structures of the PRODUCTS and SALES tables. Which two SQL statements would give the same output? (Choose two.)

A.

SELECT prod_id FROM products

INTERSECT

SELECT prod_id FROM sales;

B.

SELECT prod_id FROM products

MINUS

SELECT prod_id FROM sales;

C.

SELECT DISTINCT p.prod_id

FROM products p JOIN sales s

ON p.prod_id=s.prod_id;

D.

SELECT DISTINCT p.prod_id

FROM products p JOIN sales s

ON p.prod_id <> s.prod_id;

答案：（A、C）

解析：

如果products和sales中的prod_id都对应相同，则B的结果为空，而D的结果不为空。

create table product

(

id number(2),

name varchar2(10)

);

insert into product values(1, 'ProductA');

insert into product values(2, 'ProductB');

insert into product values(3, 'ProductC');

create table sales

(

id number(2),

prod_id number(2),

quantity number(2)

);

insert into sales values(1, 1, 7);

insert into sales values(2, 1, 8);

insert into sales values(3, 2, 9);

insert into sales values(4, 2, 10);

insert into sales values(5, 3, 11);

另解：可以做2个集合｛1，2，3｝和{1,2}

ITPub博客

OCP 1Z0 051 153

最新文章