Schema and sample data
| product_id | name | price |
|---|---|---|
| 1 | HDMI Cable | 20.00 |
| 2 | Wireless Mouse | 40.00 |
| 3 | USB-C Hub | 60.00 |
| 4 | Mechanical Keyboard | 100.00 |
| 5 | Noise-Cancelling Headphones | 120.00 |
| 6 | 4K Webcam | 160.00 |
| 7 | Ultrawide Monitor | 200.00 |
Show setup SQL (copy to run)
CREATE TABLE Products (
product_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
INSERT INTO Products (product_id, name, price) VALUES
(1, 'HDMI Cable', 20.00),
(2, 'Wireless Mouse', 40.00),
(3, 'USB-C Hub', 60.00),
(4, 'Mechanical Keyboard', 100.00),
(5, 'Noise-Cancelling Headphones', 120.00),
(6, '4K Webcam', 160.00),
(7, 'Ultrawide Monitor', 200.00);From the Products table, return the name and price of every product that costs more than the overall average price of all products. Compare each price against a scalar subquery of the form (SELECT AVG(price) FROM Products). Sort the result by price from highest to lowest.
Expected result
| name | price |
|---|---|
| Ultrawide Monitor | 200.00 |
| 4K Webcam | 160.00 |
| Noise-Cancelling Headphones | 120.00 |
Show hint
You cannot use an aggregate like AVG(price) directly in a WHERE clause. Instead, wrap it in parentheses as its own SELECT so it returns a single value, then compare each row against that value with a strict greater-than.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT name, price
FROM Products
WHERE price > (SELECT AVG(price) FROM Products)
ORDER BY price DESC;The inner query (SELECT AVG(price) FROM Products) is a scalar subquery: it returns exactly one value. Across the seven sample rows the prices sum to 700.00 and there are 7 products, so the average is 700.00 / 7 = 100.00. Because the subquery does not depend on the outer row (it is uncorrelated), SQL Server evaluates it once, caches the single result, and then reuses that 100.00 for every comparison rather than recomputing the average for each row.
The outer WHERE price > 100.00 then keeps only the products priced strictly above the average: the Noise-Cancelling Headphones (120.00), the 4K Webcam (160.00) and the Ultrawide Monitor (200.00). The Mechanical Keyboard sits exactly on the average at 100.00, and since the test is a strict greater-than rather than >=, it is excluded. See AVG for how the aggregate treats the column.
A common mistake is to write WHERE price > AVG(price) directly. That fails because an aggregate function cannot appear in a WHERE clause; the aggregate has to live inside its own subquery so it is computed as a separate step. Finally, ORDER BY price DESC lists the surviving rows from most to least expensive.