Which is the valid DELETE statement?

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?

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



Leave a Reply 22

Your email address will not be published. Required fields are marked *


Surendar

Surendar

Option D is the correct answer

Alex

Alex

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.

kar

kar

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

Luke

Luke

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.

Luke

Luke

Actually, I think that using ‘exists’ here is better than ‘in’.

Justyna

Justyna

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.

francesco

francesco

option d with not in is correct, but with this choice c is de best solution

Justyna

Justyna

about C:
SYSDATE – 3*365 >= time_id it means
time_id <= SYSDATE – 3*365

so it is incorrect.

farooq

farooq

DO you agree that option c is incorrect

mourad

mourad

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

Gautam

Gautam

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

Palash

Palash

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.

Gautam

Gautam

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

Sayed

Sayed

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.

donald

donald

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)

Ruth Kobus A is correct

Ruth Kobus A is correct

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?

Bruno Iamada

Bruno Iamada

this can generate a error, because 2008 is a “leap year”

Romiyo

Romiyo

Got failed today from this dump because i trust on it.

juliohhhh

juliohhhh

jajaja, no hay opción correcta, debería ser un not in .

Bryn

Bryn

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}

Preethi Thiagarajan

Preethi Thiagarajan

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.