ITPEC FE Subject A April 2025 Question 21

Topic: Databases

Which of the following is the clause that should be inserted into blank A of the SQL statement below in order to display the total sales data for each product that sold for at least $5,000 from the table OrderDetails?

OrderDetails (OrderID, ProductID, UnitPrice, Quantity)

[SQL statement]
SELECT ProductID, SUM(UnitPrice * Quantity)
FROM OrderDetails
A

Explanation

GROUP BY ProductID, then HAVING on the sum — the condition is on a per-product total, so it must filter groups, not rows.

"Total sales for each product" means one result row per product, so the rows must be grouped with GROUP BY ProductID. "Sold for at least $5,000" is a condition on each group's SUM(UnitPrice * Quantity), an aggregate, so it belongs in HAVING, which is evaluated after grouping.

The complete statement is SELECT ProductID, SUM(UnitPrice * Quantity) FROM OrderDetails GROUP BY ProductID HAVING SUM(UnitPrice * Quantity) >= 5000;

Answer (a)

Why not others:

  • •(b) — without GROUP BY the whole table is one group, so the sum is a single grand total, and ProductID in the select list is neither grouped nor aggregated, which is an error
  • •(c) — HAVING is written before GROUP BY, but SQL requires GROUP BY first (the column name is also misspelled ProductI)
  • •(d) — WHERE filters individual rows before grouping, so it cannot use an aggregate such as SUM, and without GROUP BY there are no per-product totals

Key rule: Clause order follows evaluation order: WHERE filters rows, GROUP BY forms groups, HAVING filters groups by their aggregates.

Databases · AI-written, may contain errors

Practice this exam

This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official FE past-paper collection or Report an issue.