Question 20

Explanation

Display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID.

This means for each ORDER_ID, we want the product with the maximum UNIT_PRICE.

Given SQL Query (Exhibit 1):

SELECT order_id, product_id, unit_price
FROM order_items
WHERE (unit_price, order_id) =
  (SELECT MAX(unit_price), order_id
   FROM order_items
   GROUP BY order_id);

What’s wrong with this query?

• The subquery returns multiple rows — one (MAX(unit_price), order_id) pair per ORDER_ID.

• The main query uses =, which only works with a subquery returning a single row.

• Using = with a multi-row subquery causes an error.

Correct Fix:

To compare a tuple against multiple values, use the IN operator instead of =:

SELECT order_id, product_id, unit_price
FROM order_items
WHERE (order_id, unit_price) IN
  (SELECT order_id, MAX(unit_price)
   FROM order_items
   GROUP BY order_id);

Note: You must match the column order in the WHERE clause and subquery.

Evaluate the options:

1. Remove the GROUP BY clause from the subquery and place it in the main query

   • Incorrect. You need the GROUP BY in the subquery to get MAX(unit_price) per order_id.

2. Replace = with the >ANY operator

   • Incorrect. That would give you rows with unit_price greater than any max — not what’s asked.

3. Replace = with the >ALL operator

   • Incorrect. That gives you rows greater than all max prices, also not what’s asked.

4. Replace = with the IN operator

   • Correct. This allows comparison with multiple rows, which is what the subquery returns.

Note: When evaluating the tables, the Primary and Foreign Keys are colour coded and the table with the 3 prong connector is the upper level. There may be more than one Primary Key per table and the Foreign Key to a given table may be the linked to a lower level Foreign Key.