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 );
Option D is the correct answer
I’m a little confused on this one. I got D for being the best answer, but it doesn’t check on whether there was a sale or not. The question specifically asks to delete rows where there was no sale, but the DML removes everything in the Product table within last 3 year period.
IN SUBQUERY WE ARE CHOOSING ONLY SALES RECORDS
IN MAIN QUERY WE USED DELETE SO I WILL REMOVE ALL THE SALES RECORDS WITH THE MATCHING CONDITION
I also got confused on this one. What does it mean of ‘which no sale was done’? If you sale something, they should add a sale record in the table SALES, right? ‘WHERE SYSDATE – 3*365 >= time_id’ means getting the last three years’ records.
So in my opinion, the collect statement should be
DELETE
FROM products
WHERE prod_id NOT IN (SELECT prod_id
FROM sales
WHERE SYSDATE – 3*365 >= time_id);
Notice that NOT in the statement.
Actually, I think that using ‘exists’ here is better than ‘in’.
It deletes products that have records in table sales which means that these products were sold.
Option D – there are deleted products which have been sold more then 3 years ago.
option d with not in is correct, but with this choice c is de best solution
about C:
SYSDATE – 3*365 >= time_id it means
time_id <= SYSDATE – 3*365
so it is incorrect.
DO you agree that option c is incorrect
Hi,
Im confused too…
for example, in the subquery, we can have the same product sales two times
the first time before sysdate-3*365 and the second time after sysdate-3*365.
then the subquery select the product and it will be deleted.
test:
sales1 : product A , time_id 01/01/2011
sales2 : product A , time_id 01/01/2012
sysdate = 18/02/2014
sysdate – 3*365 = 19/02/2011
Yes Mourad.. you are right..
Right query upto my knowledge is
DELETE
FROM products
WHERE prod_id IN
(SELECT prod_id FROM
(SELECT prod_id, max(time_id) as timeID FROM sales group by prod_id)
WHERE SYSDATE – 3*365 >= timeID);
I hope, all of the options are wrong.
Because you can not delete any row from PRODUCT table if any child record exists in the SALE table, due to FOREIGN KEY relation, irrespective of the TIME_ID.
Yes Mourad.. you are right..
Right query upto my knowledge is
DELETE
FROM products
WHERE prod_id IN
(SELECT prod_id FROM
(SELECT prod_id, max(time_id) as timeID FROM sales group by prod_id)
WHERE SYSDATE – 3*365 >= timeID);
WHERE SYSDATE – 3*365 >= time_id); filter returns the dates that are before 3 years. So, nothing wrong putting the option C is correct. Option D WHERE clause returns the dates that are after 3 years which is wrong.
ALL ARE WRONG. They ask to delete products that HAVE NOT BEEN sold in past three years. Therefore, you find those that HAVE BEEN sold in last three years…
Select Prod_id
from sales
where (sysdate – 3*365) <= time_id
Now, you do the following:
Delete from Products where prod_id NOT IN
(select Prod_id
from sales
where (sysdate – 3*365) <= time_id)
The answer is displayed as being C. My question is that wouldn’t it be wrong also since you would have to delete the records from both the Sales and products table since there is a foreign key constaint?
this can generate a error, because 2008 is a “leap year”
Got failed today from this dump because i trust on it.
jajaja, no hay opción correcta, debería ser un not in .
This is a poorly worded question with 4 incorrect answers. I understood it to mean ‘delete all the products that have not been sold for at least 3 years’. To ensure that we don’t remove products that have been sold both before AND after the 3 year cut off I think you’d need to do something like this:
DELETE
FROM products
WHERE prod_id NOT IN (SELECT prod_id
FROM sales
WHERE time_id >= SYSDATE – 3*365);
On the FOREIGN KEY issue. You can delete a item from the parent table as long as it was created with an ON DELETE {SET NULL | CASCADE}
C
i guess TIME_ID is something when the Product gets sold. Therefore the query sysdate-3*365 >= TIME_ID will in turn check TIME_ID <= (SYSDATE-3*365).
TIME_ID <= 3YEARSFRPMTODAY will be deleted.
So the products whose TIME ID is not updated for the past three years will be deleted.