← QCE Digital Solutions Unit 3

SQL sub-selection (subquery)

A sub-selection is a SELECT inside brackets within another statement. The inner query runs first and its result is used by the outer query, such as in a WHERE condition.

Part of: QCE Digital Solutions Unit 3 — Innovative Digital Solutions — Module 34

Understanding SQL sub-selection (subquery) in QCE Digital Solutions Unit 3

What it does: The inner SELECT in brackets finds the highest year level, and the outer query uses that result to filter the rows. IN checks whether a value matches any value in the list the inner query returns. Comparing with > expects a single value, not a list. A sub-selection must be inside brackets: WHERE points > (SELECT AVG(points) FROM students). Select only the matching column in the subquery: (SELECT productId FROM orders).

Common mistakes: A sub-selection must be wrapped in round brackets ( ), not square brackets. It should be WHERE points > (. A trailing comma after the last field in the SELECT list is a syntax error. It should be SELECT firstName.

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-selection (subquery) to a class →

This module's key terms are also in a printable crossword (Modules 251–337) and word search (Modules 194–337).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which describes a sub-selection (subquery)?

A SELECT written inside brackets within another statementA SELECT that is saved as a separate table

Matches

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

↓

The inner SELECT is not wrapped in brackets, so the sub-selection is not valid

Applying

Meanings

Term

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

Meaning

A sub-selection is a SELECT inside brackets within another statement. The inner query runs first and its result is used by the outer query, such as in a WHERE condition.

Practice

SELECT firstName, points

Testing

Gaps

WHERE points > ( AVG(points)

Check Answer

Spots

2FROM students

3WHERE points > [

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 describes a sub-selection (subquery)?

A. A SELECT written inside brackets within another statement ✓

B. A SELECT that is saved as a separate table

A sub-selection is a SELECT placed inside brackets within another statement. Its result is used by the outer query.

2. Which query finds 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); ✓

Aggregate functions like AVG cannot be used directly in WHERE. A sub-selection in brackets works out the average first, and the outer query compares against it.

3. In a query with a sub-selection, which part runs first?

A. The outer SELECT

B. The inner SELECT in brackets ✓

The inner query runs first. Its result is then used by the outer query.

4. Which sub-selection is written correctly?

A. WHERE price > (SELECT AVG(price) FROM products) ✓

B. WHERE price > SELECT AVG(price) FROM products

The sub-selection must be wrapped in brackets so the database knows where the inner query starts and ends.

5. A sub-selection returns a list of studentId values. Which keyword suits the outer WHERE?

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

B. WHERE studentId > (SELECT studentId FROM orders)

IN checks whether a value matches any value in the list the inner query returns. Comparing with > expects a single value, not a list.

Match the Pairs

SELECT firstName FROM students WHERE points > (SELECT AVG(points) FROM students); → Lists students whose points are above the average points of all students

SELECT name FROM products WHERE productId IN (SELECT productId FROM orders); → Lists the products that appear in at least one order

A sub-selection is a SELECT written inside brackets within another statement → SELECT * FROM students WHERE yearLevel = (SELECT MAX(yearLevel) FROM students);

A subquery can return a list of values, so the outer WHERE can test membership using IN → SELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM results WHERE mark > 90);

SELECT firstName FROM students WHERE points > SELECT AVG(points) FROM students; → The inner SELECT is not wrapped in brackets, so the sub-selection is not valid

SELECT name FROM products WHERE productId IN (SELECT productId, quantity FROM orders); → The inner query returns two columns, but IN needs a subquery that returns only one column to compare with productId

Key Term

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

A sub-selection is a SELECT inside brackets within another statement. The inner query runs first and its result is used by the outer query, such as in a WHERE condition.

Fill in the Blanks

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

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

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

Hint: Type the aggregate function that works out the average value in the inner query.

3. SELECT name FROM subjects WHERE subjectId IN (SELECT subjectId FROM results WHERE mark > 85);

Hint: Type the keyword that checks whether a value is in the list of values the inner query returns.

4. SELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM orders);

Hint: Type the table name that the inner query reads student ids from to find students who have placed an order.

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

Hint: Type the column name the inner query averages so products can be compared with it.

Spot the Error

Question 1

A SELECT firstName

B FROM students

C WHERE points > [

D SELECT AVG(points)

E FROM students);

Line C — A sub-selection must be wrapped in round brackets ( ), not square brackets. It should be WHERE points > (

Question 2

A SELECT firstName,

B FROM students

C WHERE studentId IN (

D SELECT studentId

E FROM results

F WHERE mark > 90);

Line A — A trailing comma after the last field in the SELECT list is a syntax error. It should be SELECT firstName

Question 3

A SELECT name, price

B FROM products

C WHERE price > (

D SELECT AVG(cost)

E FROM products);

Line D — The products table has no cost column, so the inner query fails. It should be SELECT AVG(price)

Question 4

A SELECT firstName, house

B FROM student

C WHERE yearLevel IN (

D SELECT yearLevel

E FROM students

F WHERE points > 100);

Line B — The table is called students, so the outer query needs FROM students. The table name must match exactly.

Question 5

A SELECT name

B FROM subjects

C WHERE subjectId IN (

D SELECT subjectId, mark

E FROM results

F WHERE mark < 50);

Line D — A subquery used with IN must return a single column to compare with subjectId. It should be SELECT subjectId

Student with a class code? Join your class →