← QCE Digital Solutions Unit 4

SQL sub-select (subquery)

A sub-select is a SELECT inside brackets in a WHERE clause. It runs first and its result is used by the outer query, e.g. to compare with an AVG or to match values using IN.

Part of: QCE Digital Solutions Unit 4 — Prototype Digital Data Exchanges — Module 75

Understanding SQL sub-select (subquery) in QCE Digital Solutions Unit 4

What it does: The inner SELECT runs first and returns one value (the maximum price). The outer query uses = to compare each price with that value. A sub-select is a complete SELECT written inside brackets within the WHERE clause. It is not a separate statement. The sub-select must be wrapped in brackets. Without the brackets the query is a syntax error. Sub-selects supply a value or list that is calculated from the data, such as an average. Sorting is done by ORDER BY. IN checks each row against the list of values returned by the sub-select, so only students who have placed an order match.

Common mistakes: A sub-select must be wrapped in brackets. The opening bracket is missing, so the line should be WHERE price > (SELECT. The sub-select returns a list of many productId values. = only compares with one value, so it should be WHERE productId IN.

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 SQL sub-select (subquery) to a class →

This module's key terms are also in a printable crossword (Modules 334–401) and word search (Modules 127–401).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which query uses a sub-select to find students with points above the average?

SELECT firstName FROM students WHERE points > (SELECT AVG(points) FROM students);SELECT firstName FROM students WHERE points > AVG(points);

Matches

SELECT firstName FROM students WHERE points > SELECT AVG(points) FROM students;

↓

The sub-select is missing its brackets. A sub-select must be wrapped in ( ) so the database knows where the inner query starts and ends.

Applying

Meanings

Term

(SELECT ...)

Meaning

A sub-select is a SELECT inside brackets in a WHERE clause. It runs first and its result is used by the outer query, e.g. to compare with an AVG or to match values using IN.

Practice

SELECT firstName, points

Testing

Gaps

WHERE points > (SELECT (points)

Check Answer

Spots

2FROM products

3WHERE price > SELECT

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

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 uses a sub-select to find students with points above the average?

A. SELECT firstName FROM students WHERE points > AVG(points);

B. SELECT firstName FROM students WHERE points > (SELECT AVG(points) FROM students); ✓

AVG() cannot be used directly in a WHERE clause. Put it in a sub-select in brackets. The sub-select runs first and gives one value to compare with.

2. Where does a sub-select go in the query below?

A. After a semicolon as a separate line: WHERE price > ; SELECT AVG(price) FROM products

B. Inside brackets in the WHERE clause: WHERE price > (SELECT AVG(price) FROM products) ✓

A sub-select is a complete SELECT written inside brackets within the WHERE clause. It is not a separate statement.

3. True or false: the sub-select runs first, and its result is used by the outer query.

A. True ✓

B. False

The database works out the inner SELECT first. Then the outer query uses that result in its WHERE condition.

4. A sub-select returns a list of several studentId values. Which operator should the outer WHERE use?

A. WHERE studentId IN (SELECT studentId FROM orders) ✓

B. WHERE studentId = (SELECT studentId FROM orders)

The = operator expects one value. When a sub-select returns many values, use IN to match any value in the list.

5. Which sub-select is correct for finding the product with the highest price?

A. SELECT name FROM products WHERE price = (SELECT MAX(price) FROM products); ✓

B. SELECT name FROM products WHERE price = SELECT MAX(price) FROM products;

The sub-select must be wrapped in brackets. Without the brackets the query is a syntax error.

Match the Pairs

WHERE points > (SELECT AVG(points) FROM students) → Keeps only the rows whose points are above the average, which the inner query works out first

WHERE studentId IN (SELECT studentId FROM orders) → Keeps only the students whose ID appears in the list of IDs the inner query returns from orders

A sub-select that returns a single value can be used after = to find the rows that match it, such as the highest price → SELECT name FROM products WHERE price = (SELECT MAX(price) FROM products);

When a sub-select returns a list of values, use IN so the outer query matches any value in that list → SELECT name FROM products WHERE productId IN (SELECT productId FROM orders);

SELECT firstName FROM students WHERE points > SELECT AVG(points) FROM students; → The sub-select is missing its brackets. A sub-select must be wrapped in ( ) so the database knows where the inner query starts and ends.

SELECT name FROM products WHERE productId = (SELECT productId FROM orders); → The sub-select can return many productId values, but = only compares with one value. IN should be used to match a list.

Key Term

(SELECT ...)

A sub-select is a SELECT inside brackets in a WHERE clause. It runs first and its result is used by the outer query, e.g. to compare with an AVG or to match values using IN.

Fill in the Blanks

1. SELECT firstName FROM students WHERE points > (SELECT AVG(points) FROM students);

Hint: Type the aggregate function that works out the average points in the sub-select

2. SELECT name FROM products WHERE productId IN (SELECT productId FROM orders);

Hint: Type the operator that matches a value against a list of values returned by the sub-select

3. SELECT firstName, points FROM students WHERE points = (SELECT MAX(points) FROM students);

Hint: Type the aggregate function that finds the highest value for the outer query to compare with

4. SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products);

Hint: Type the keyword that starts the inner query inside the brackets

5. SELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM results WHERE mark > 90);

Hint: Type the column the sub-select must return so it matches the outer IN comparison

Spot the Error

Question 1

A SELECT name, price

B FROM products

C WHERE price > SELECT

D AVG(price) FROM products);

Line C — A sub-select must be wrapped in brackets. The opening bracket is missing, so the line should be WHERE price > (SELECT.

Question 2

A SELECT name FROM products

B WHERE productId =

C (SELECT productId

D FROM orders);

Line B — The sub-select returns a list of many productId values. = only compares with one value, so it should be WHERE productId IN.

Question 3

A SELECT firstName FROM students

B WHERE firstName IN (SELECT

C studentId FROM results

D WHERE mark > 90);

Line B — The sub-select returns studentId values, so the outer column must be studentId too. It should be WHERE studentId IN (SELECT.

Question 4

A SELECT name, price,

B FROM products

C WHERE price = (SELECT

D MAX(price) FROM products);

Line A — The extra comma after price is followed by FROM, which is a syntax error. The line should be SELECT name, price.

Question 5

A SELECT studentId, mark

B FROM subjects

C WHERE mark > (SELECT

D AVG(mark) FROM results);

Line B — The mark column is in the results table, not subjects. The outer query should be FROM results.

Student with a class code? Join your class →