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’));
on B option,
what would have happened if these two promotion names have more than one promo id,
-i think B is ambiguos.
I definitely agree with you, I think B is wrong.
B is ambiguous, it is not necessarily wrong. Hopefully in the exam the questions will be less so (ambiguous).
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
Sorry you’re right it’s not specified that the promotion name must be unique even if it’s usual in an anagraphical table
This is simply for check your knowl. Nowhere is mentioned that the values may be somewhat. I think B is correct.
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?
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.
THEN ONLY C AND D AH?
hi,
can someone help on the equality operator (=) vs IN operator question, thanks.
Can someone justify why ‘A’ is wrong?
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.
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
B,C,D Correct
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.
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.
C.
DELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’
OR promo_name = ‘everyday low price’);
Usually posts some incredibly interesting stuff like this. If youre new to this site.
we came across a cool site that you just could possibly enjoy. Take a look should you want
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
one of our visitors just lately suggested the following website
Every when in a while we opt for blogs that we read. Listed beneath would be the latest websites that we pick
Sites of interest we’ve a link to
below youll uncover the link to some websites that we assume you ought to visit
just beneath, are various completely not associated web pages to ours, nonetheless, they’re certainly worth going over
always a big fan of linking to bloggers that I really like but dont get a lot of link appreciate from
Sites of interest we have a link to
B,C,D are correct answers