The products table has product_id, category and price. Which query returns only the products priced above the average price of their own category?
Select an answer to reveal the explanation.
Short Explanation
"Their own category" is the giveaway: the average has to change for each row you're checking. That's a correlated subquery, where the inner query peeks at the outer row's category. Without that link, you're comparing everyone to one storewide average.
Full Explanation
A correlated subquery references a column from the outer query, so it is logically evaluated for each outer row. The condition x.category = p.category makes the inner AVG compute the average price of whichever category the current product belongs to, and the outer WHERE keeps the product only if its own price exceeds that figure. The same result can be written with a common table expression or a window AVG partitioned by category. Removing the correlation leaves a subquery that returns one storewide average, so a cheap category's products are judged against expensive categories and the wrong set is returned. Placing AVG directly in the WHERE clause is invalid because WHERE filters individual rows before any grouping happens; aggregate functions belong in the SELECT list or HAVING clause, and the engine rejects this statement. Grouping by category with a HAVING comparison of MAX to AVG returns category names, not products, and only answers whether a category has any product above its average, which is nearly always true. Exam caveat: optimizers often rewrite correlated subqueries as joins, so the per-row description is logical, but an index on category still matters for performance. Operational check: compute each category's average separately, pick a product just above and one just below its average, and confirm only the first appears in the result.