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 );
None of these are correct as they attempt to delete from PRODUCTS those products that had a sale.
One way of doing it could be
DELETE
FROM products
WHERE prod_id NOT IN (SELECT prod_id
FROM sales
WHERE time_id >= SYSDATE – 3*365);
However it would not work if any prod_id in SALES were NULL. This could be better…
DELETE
FROM products p
WHERE NOT EXISTS (SELECT ‘X’ FROM sales s
WHERE s.prod_id = p.prod_id
AND time_id >= SYSDATE – 3*365);
alternatively you could use a MINUS.
I agree with what was said by Eamon
Wow, I sure hope this isn’t on an actual exam.
Also Eamon, PROD_ID in SALES is NOT NULL, so your first query will always work.
I think another alternative would be:
DELETE
FROM products
WHERE prod_id NOT IN (SELECT prod_id
FROM sales
GROUP BY prod_id
HAVING MAX(time_id) >= (SYSDATE – 3*365));
Actually, I just noticed… time_id is a foreign key to yet another table (TIMES), so a join or another subquery are needed.
Or not.
I believe the answer is correct because this
(SELECT prod_id
FROM sales
WHERE SYSDATE – 3*365 >= time_id) would give all sales of 3 years or older. So any prod_id in products IN that set of sales would be ok. Eanon, notice that you have time_id >= and that’s why you have to use NOT IN.