Which DELETE statements are valid?

View the Exhibit and examine the description of SALES and PROMOTIONS tables.
You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS
table has either blowout sale or everyday low price as values.
Which DELETE statements are valid? (Choose all that apply.)

View the Exhibit and examine the description of SALES and PROMOTIONS tables.

You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS
table has either blowout sale or everyday low price as values.

Which DELETE statements are valid? (Choose all that apply.)

A.
DELETE
FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’)
AND promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘everyday low price’);

B.
DELETE
FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’)
OR promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘everyday low price’);

C.
DELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’
OR promo_name = ‘everyday low price’);

D.
DELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name IN (‘blowout sale’,’everyday low price’));



Leave a Reply 28

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


Oscar Copado Silva

Oscar Copado Silva

on B option,

what would have happened if these two promotion names have more than one promo id,

-i think B is ambiguos.

steven

steven

I definitely agree with you, I think B is wrong.

Eamon

Eamon

B is ambiguous, it is not necessarily wrong. Hopefully in the exam the questions will be less so (ambiguous).

Alvin2201

Alvin2201

In the exhibit the PROMO_ID attribute is referenced by PROMO_ID in table SALES and COSTS in a ONE-to-many relation: so it MUST BE UNIQUE and B. is not ambiguos

Alvin2201

Alvin2201

Sorry you’re right it’s not specified that the promotion name must be unique even if it’s usual in an anagraphical table

OldBoyOdeSu

OldBoyOdeSu

This is simply for check your knowl. Nowhere is mentioned that the values may be somewhat. I think B is correct.

user

user

why is B correct?

What if either ‘blowout sale’ or ‘everyday low price’ has more than one promo_id?

I thought the equality operator (=) could be used only if the subquery returns one single result.

So on the exam, should we assume the subquery will ALWAYS return ONE result? or should we assume the otherwise?

Ben

Ben

I don’t think we need to make any assumption here. We need to focus more what is the question asking about. A and B are asking you AND, OR logic, therefore you choose B rather than A, then C and D also apply.

kar

kar

THEN ONLY C AND D AH?

user

user

hi,

can someone help on the equality operator (=) vs IN operator question, thanks.

Lib Guest

Lib Guest

Can someone justify why ‘A’ is wrong?

samantha

samantha

Because when you use the AND operator ALL conditions required must be met. In this case if you use AND no results will be retrieved.

Jav

Jav

A is wrong because the and operator will select the rows that have ‘blowout sale’ and ‘every day low price’ at the same time
B is wrong because the = operator is used when the subquery gives you only one record
C is correct and D is correct.

the difference between = and IN is that the = is used to compare just one value and IN is used to compare to a set of values

Justyna

Justyna

B is something like:
delete from emp
where deptno = ( select deptno from dept where dname =’SALES’)
or deptno = ( select deptno from dept where dname =’OPERATIONS’)

There is only one deptno with name SALES, and there is only one deptno with name OPERATIONS. In such case above statement works well and operator = can be used.

donald

donald

B is WRONG. PERIOD. promo_name is NOT defined with a condition of PRIMARY KEY or UNIQUE. Clearly many promo_id’s can have the name of ‘everyday low price’, or ‘blowout sale’. That’s what generates multiple records being returned to the EQUALS (=) operator. And any time multiple records are returned to an =’s operator, Oracle throws an ERROR.
One of the poor facts of these examinations is that the questions are not articulated properly. And writing SQL REQUIRES questioning the requirements.

Hossam

Hossam

C.
DELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’
OR promo_name = ‘everyday low price’);

Google

Google

Usually posts some incredibly interesting stuff like this. If youre new to this site.

spray on chrome

spray on chrome

we came across a cool site that you just could possibly enjoy. Take a look should you want

Turen

Turen

although web sites we backlink to beneath are considerably not associated to ours, we feel they’re in fact worth a go by, so possess a look

app creator

app creator

one of our visitors just lately suggested the following website

how to make an app for free

how to make an app for free

Every when in a while we opt for blogs that we read. Listed beneath would be the latest websites that we pick

kala jadu

kala jadu

Sites of interest we’ve a link to

social apps

social apps

below youll uncover the link to some websites that we assume you ought to visit

manager

manager

just beneath, are various completely not associated web pages to ours, nonetheless, they’re certainly worth going over

a knockout post

a knockout post

always a big fan of linking to bloggers that I really like but dont get a lot of link appreciate from

it services omaha

it services omaha

Sites of interest we have a link to

Hrishikesh

Hrishikesh

B,C,D are correct answers