Exam 1z0-082 topic 1 question 50 discussion

C- correct answer display PROD_IDS whose promotion cost is less than the highest cost PROD_ID WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date - promo_begin_date))); “< ALL …” here is the mistake, if the next selection returns not only one max value, it returns the wrong, maybe even an empty result

the correct answer is C but so far nobody explained the correct reason! the issue here is not the ALL operator but the last line of the query: (promo_end_date - promo_begin_date) is returning the number of days which the promotion was running, but no interval, which is requested by the question. so logically the query is not generating desired output.

C must be correct. D would be correct with WHERE promo_cost < ANY

A - false; keyword ALL is valid, it is used in Group Comparison Conditions, see Oracle doc: Oracle Database, Release 19, SQL Language Reference, 6 Conditions, Comparison Conditions B - false; GROUP BY clause is valid, it is used with Aggregate Functions, see Oracle doc: Oracle Database, Release 19, SQL Language Reference, 7 Functions, Aggregate Functions: C - false; The query gives required results. The most inner query returns maximum promotional costs within groups formed with all promotions with same duration in days. The inner query which starts SELECT promo_id returns those ID of promotions, which costs is smaller than any maximum costs returned with the most inner query. The outer query returns those product IDs, which have the promo_id returned with the previous query. There must be some promo_cost which is smaller than any maximum promo_cost within groups. Only in that case the query returns some rows. D - true; see C

I think both C and D can be right, and it depends on the data: 1. Get the result: Two promotion data that have the same period but with different cost, and one cost has the minimum cost of all promotions. >> if set the data like this: 1-$5000-period 10 days/ 2- $3000-period 15 days/ 3- $2500- period 30 days/ 4-$3500-period 30 days >> We can get the ALL list of ($5000, $3000, $3500), and $2500 will be left to the group but this cost is meet the < ALL situation and the related product ID will show up in the end. 2. No result in the end: If the example above change the third data to $3000, then we can still get the same ALL list ($5000, $3000, $3500) but there are no other cost that is less than the cost in the list. Therefore, we get no results.

D D D IS CORRECT

CORRECT C , executing success but not giving expected answer

I see no errors here

who give the suggested anwer? why are all of it are wrong?

I have recreated the tables and inserted some random data on them and tested the query. The correct answer is C: When you run select prod_id from costs where prod_id in (select prod_id from promotions where promo_cost < all (select max(promo_cost) from promotions group by (promo_end-promo_begin))); The query does go through but returns no results. When you run the same query without the group by part you get the expected results (prod_id 2 had the highest promo_cost) PROD_ID 1 3 4

C is correct

D is correct

<ALL: less than the lowest value returned by subquery. <ANY: less than the highest value returned by subquery.

so what is the correct answer ???

The correct answer is D. <ALL means that The value must be smaller than the smallest value in the list to evaluate to TRUE

c - correct

I'd suggest answer A, but I am not completely sure!