← QCE Digital Solutions Unit 2

Sub-query returning a single value

A sub-query in brackets returns one value, like the average. The outer WHERE compares each row's field to it. MIN or MAX work too.

Part of: QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 46

Understanding Sub-query returning a single value in QCE Digital Solutions Unit 2

What it does: MIN inside the brackets returns the single smallest points value, and the outer WHERE finds the student who has that value. The sub-query must be wrapped in brackets. Without them the query is a syntax error. Because the sub-query gives a single value, comparison operators like > , < and = work. GROUP BY is not needed. The inner query in brackets is worked out first. It gives one average price, then the outer query compares each product's price to it.

Common mistakes: There is a trailing comma after points, so SQL expects another field before FROM. Remove it: SELECT firstName, points. The WHERE clause has no comparison operator, so it cannot compare price with the single value from the sub-query. It should be WHERE price <.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Sub-query returning a single value to a class →

This module's key terms are also in a printable crossword (Modules 305–336) and word search (Modules 234–409).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which query finds results with a mark above the average mark?
SELECT * FROM results WHERE mark > (SELECT AVG(mark) FROM results);SELECT * FROM results WHERE mark > AVG(mark);
Matches
SELECT * FROM results WHERE mark > SELECT AVG(mark) FROM results;
↓
The sub-query is not wrapped in brackets, so SQL cannot tell where the inner query starts and ends.
Applying
Meanings
Term
WHERE mark > (SELECT AVG(mark) FROM results)
Meaning
A sub-query in brackets returns one value, like the average. The outer WHERE compares each row's field to it. MIN or MAX work too.
Practice
SELECT studentId, subjectId, mark
Testing
Gaps
WHERE mark > (SELECT (mark)
Check Answer
Spots
1SELECT firstName, points,
2FROM students

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Seal' badge — Smooth Formatter, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which query finds results with a mark above the average mark?
A. SELECT * FROM results WHERE mark > (SELECT AVG(mark) FROM results); ✓
B. SELECT * FROM results WHERE mark > AVG(mark);
An aggregate like AVG cannot be used directly in WHERE. Put it in a sub-query in brackets so it returns one value to compare with.
2. What does the sub-query (SELECT AVG(mark) FROM results) return?
A. A list of every mark in the table
B. One single value: the average mark ✓
An aggregate function collapses many rows into one value, so the outer WHERE has a single number to compare each row against.
3. Which sub-query gives the highest mark, so you can find the top result?
A. (SELECT MAX(mark) FROM results) ✓
B. (SELECT MIN(mark) FROM results)
MAX returns the largest value. MIN returns the smallest. Both return one value, so both can be used in a sub-query.
4. Which WHERE clause lists products cheaper than the average price?
A. WHERE price < SELECT AVG(price) FROM products
B. WHERE price < (SELECT AVG(price) FROM products) ✓
The sub-query must be wrapped in brackets. Without them the query is a syntax error.
5. A sub-query that returns one aggregate value is compared with the outer field using which kind of operator?
A. A comparison operator such as >, < or = ✓
B. The aggregate keyword GROUP BY
Because the sub-query gives a single value, comparison operators like > , < and = work. GROUP BY is not needed.

Match the Pairs

WHERE mark > (SELECT AVG(mark) FROM results) → Keeps only rows whose mark is above the average mark of all results
WHERE price = (SELECT MAX(price) FROM products) → Keeps only the product or products with the highest price
Find students whose points are below the lowest-scoring value in a comparison, using MIN in a sub-query to return one value → SELECT firstName FROM students WHERE points = (SELECT MIN(points) FROM students);
Compare each row to the average by putting an aggregate sub-query in brackets after the comparison operator → SELECT studentId FROM results WHERE mark > (SELECT AVG(mark) FROM results);
SELECT * FROM results WHERE mark > SELECT AVG(mark) FROM results; → The sub-query is not wrapped in brackets, so SQL cannot tell where the inner query starts and ends.
SELECT * FROM results WHERE mark > (SELECT mark FROM results); → The sub-query returns every mark, not one value, so the outer WHERE cannot compare a single mark to it.

Key Term

WHERE mark > (SELECT AVG(mark) FROM results)
A sub-query in brackets returns one value, like the average. The outer WHERE compares each row's field to it. MIN or MAX work too.

Fill in the Blanks

1. SELECT studentId, mark FROM results WHERE mark > (SELECT AVG(mark) FROM results);
Hint: Type the aggregate function that works out the average mark.
2. SELECT studentId, mark FROM results WHERE mark > (SELECT AVG(mark) FROM results);
Hint: Type the operator that keeps only marks above the sub-query value.
3. SELECT name, price FROM products WHERE price = (SELECT MAX(price) FROM products);
Hint: Type the aggregate function that finds the highest value.
4. SELECT firstName, points FROM students WHERE points = (SELECT MIN(points) FROM students);
Hint: Type the aggregate function that finds the lowest value.
5. SELECT name, price FROM products WHERE price < (SELECT AVG(price) FROM products);
Hint: Type the table name that the sub-query reads its average from.

Spot the Error

Question 1
A SELECT firstName, points,
B FROM students
C WHERE points >
D (SELECT AVG(points)
E FROM students);
Line A — There is a trailing comma after points, so SQL expects another field before FROM. Remove it: SELECT firstName, points.
Question 2
A SELECT name, price
B FROM products
C WHERE price
D (SELECT MAX(price)
E FROM products);
Line C — The WHERE clause has no comparison operator, so it cannot compare price with the single value from the sub-query. It should be WHERE price <.
Question 3
A SELECT studentId, mark
B FROM result
C WHERE mark >
D (SELECT AVG(mark)
E FROM results);
Line B — The table is called results, not result. The outer query must name the real table: FROM results.
Question 4
A SELECT name, stock
B FROM products
C WHERE stock <
D (SELECT stock
E FROM products);
Line D — This sub-query returns every stock value, not a single value. Compare with one number by using an aggregate: (SELECT AVG(stock).
Question 5
A SELECT firstName, points
B FROM students
C WHERE points ==
D (SELECT MIN(points)
E FROM students);
Line C — SQL uses a single = to compare values, not ==. It should be WHERE points =.

Student with a class code? Join your class →