OCP-1Z0-051 第169题 使用子查询DELETE数据

一、原题

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

二、题目翻译

看下面PRODUCTS and SALES表的结构:
PROD_ID是PRODUCTS表的主键，SALES表的外键.你想从PRODUCTS表移除所有过去三年没有销售过的产品行。
哪一个DELETE语句有效？

三、题目解析

子查询SELECT prod_id FROM sales WHERE SYSDATE - 3 * 365 >= time_id判断哪些产品是三年前的产品。
但是这样的判断也不够精确，比如，有可能有一年有366天,
而且这里PROD_ID是sales表的外键，如果直接这样删除，会报外键约束的错, 除非先禁用约束，或连SALES表中的数据一起删除。
不过，在这些选项中，只有C选项最接近，那就选C吧。