View the Exhibit and examine the structure of the ORDER_ITEMS table.
Examine the following SQL statement:
SELECT order_id, product_id, unit_price
FROM order_items
WHERE unit_price = (SELECT MAX(unit_price) FROM order_items GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID.
What correction should be made in the above SQL statement to achieve this?
A.
Replace = with the IN operator.
B.
Replace = with the >ANY operator.
C.
Replace = with the >ALL operator.
D.
Remove the GROUP BY clause from the subquery and place it in the main query.
What is the rationale for answer A but not the other ones?
“Highest PRICE per ORDER_ID” <== Each ORDER_ID has many PRICE.
Because Each line_item can have only one price.
(SELECT MAX(unit_price) FROM order_items GROUP BY order_id)
—————————————————————
the highest price's item/product, for each order_id
And then, display the the product, that has highest UNIT_PRICE for each ORDER_ID.
So A is correct. Replace = with the IN operator.
The correct solution actually is not offered – a correlated subquery:
SELECT order_id, product_id, unit_price
FROM order_items oi
WHERE unit_price = (SELECT MAX(unit_price) FROM order_items WHERE order_id = oi.order_id);
A is data-dependent, relying on practical impossibility that highest unit price per each order must be unique.
As a result, it produces incorrect duplicates:
ORDER_ID PRODUCT_ID UNIT_PRICE
———- ———- ———-
2354 3182 61
2354 3176 113.3
2354 3170 145.2
2354 3167 51
2355 2359 226.6
2356 2264 199.1
2357 2276 236.5
2357 2252 788.7
etc.
Here have to display the “Product that has the Highest Unit_Price per Order ID”
Choices B and C Displays “Greaterthan highest Unit_price per Order Id…”
but Choice A Displays ” In Highest Unit_price per Order Id”
Correct Answer is A