（169）View the Exhibit and examine the description for the PRODUCTS and SALES table.

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table.

You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years.

Which is the valid DELETE statement?

A. DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE time_id - 3*365 = SYSDATE );

B. DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3*365 );

C. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id);

D. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 );

答案：（C）

解析：

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),

time_id date

);

insert into sales values(1, 1, to_date('2014-03-01', 'YYYY-MM-DD'));

insert into sales values(2, 1, to_date('2014-03-02', 'YYYY-MM-DD'));

insert into sales values(3, 2, to_date('2014-03-03', 'YYYY-MM-DD'));

insert into sales values(4, 2, to_date('2014-03-12', 'YYYY-MM-DD'));

insert into sales values(5, 3, to_date('2014-03-11', 'YYYY-MM-DD'));

insert into sales values(6, 3, to_date('2014-03-12', 'YYYY-MM-DD'));

--C（X），逻辑有问题

scott@TESTDB11>select id, name from product where id in (select prod_id from sales where sysdate - 3 >= time_id);

ID NAME

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

1 ProductA

2 ProductB

--这才是正确答案

scott@TESTDB11>select id, name from product where id not in (select prod_id from sales where time_id >= sysdate - 3);

ID NAME

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

1 ProductA

ITPub博客

OCP 1Z0-051 169