SQL sub-selection (subquery)
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which describes a sub-selection (subquery)?
2. Which query finds students with points above the average?
3. In a query with a sub-selection, which part runs first?
4. Which sub-selection is written correctly?
5. A sub-selection returns a list of studentId values. Which keyword suits the outer WHERE?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1SELECT firstName FROM students WHERE points > (SELECT AVG(points) FROM students); | ASELECT * FROM students WHERE yearLevel = (SELECT MAX(yearLevel) FROM students); | |
| 2SELECT name FROM products WHERE productId IN (SELECT productId FROM orders); | BSELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM results WHERE mark > 90); | |
| 3A sub-selection is a SELECT written inside brackets within another statement | CThe inner query returns two columns, but IN needs a subquery that returns only one column to compare with productId | |
| 4A subquery can return a list of values, so the outer WHERE can test membership using IN | DLists the products that appear in at least one order | |
| 5SELECT firstName FROM students WHERE points > SELECT AVG(points) FROM students; | EThe inner SELECT is not wrapped in brackets, so the sub-selection is not valid | |
| 6SELECT name FROM products WHERE productId IN (SELECT productId, quantity FROM orders); | FLists students whose points are above the average points of all students |
C
Definitions
WHERE points > (SELECT AVG(points) FROM students)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT firstName FROM students
WHERE points > (______ AVG(points)
FROM students);
2.
SELECT firstName, points FROM students
WHERE points > (SELECT ___(points)
FROM students);
3.
SELECT name FROM subjects
WHERE subjectId __ (SELECT subjectId
FROM results WHERE mark > 85);
4.
SELECT firstName FROM students
WHERE studentId IN (SELECT studentId
FROM ______);
5.
SELECT name FROM products
WHERE price > (SELECT AVG(_____)
FROM products);
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT firstName
B
FROM students
C
WHERE points > [
D
SELECT AVG(points)
E
FROM students);
Question 2
A
SELECT firstName,
B
FROM students
C
WHERE studentId IN (
D
SELECT studentId
E
FROM results
F
WHERE mark > 90);
Question 3
A
SELECT name, price
B
FROM products
C
WHERE price > (
D
SELECT AVG(cost)
E
FROM products);
Question 4
A
SELECT firstName, house
B
FROM student
C
WHERE yearLevel IN (
D
SELECT yearLevel
E
FROM students
F
WHERE points > 100);
Question 5
A
SELECT name
B
FROM subjects
C
WHERE subjectId IN (
D
SELECT subjectId, mark
E
FROM results
F
WHERE mark < 50);
Answer Key
SQL sub-selection (subquery)
Section A: Quick Questions
Q1
A — A SELECT written inside brackets within another statement
Q2
B — SELECT firstName FROM students WHERE points > (SELECT AVG(points) FROM students);
Q3
B — The inner SELECT in brackets
Q4
A — WHERE price > (SELECT AVG(price) FROM products)
Q5
A — WHERE studentId IN (SELECT studentId FROM orders)
Section B: Match the Pairs
Q1
SELECT firstName FROM students WHERE points > (SELECT AVG(points) FROM students); → Lists students whose points are above the average points of all students
Q2
SELECT name FROM products WHERE productId IN (SELECT productId FROM orders); → Lists the products that appear in at least one order
Q3
A sub-selection is a SELECT written inside brackets within another statement → SELECT * FROM students WHERE yearLevel = (SELECT MAX(yearLevel) FROM students);
Q4
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);
Q5
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
Q6
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
Section C: Definitions
Q1
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.Section D: Fill in the Blanks
Q1
SELECT firstName FROM students
WHERE points > (SELECT AVG(points)
FROM students);Q2
SELECT firstName, points FROM students
WHERE points > (SELECT AVG(points)
FROM students);Q3
SELECT name FROM subjects
WHERE subjectId IN (SELECT subjectId
FROM results WHERE mark > 85);Q4
SELECT firstName FROM students
WHERE studentId IN (SELECT studentId
FROM orders);Q5
SELECT name FROM products
WHERE price > (SELECT AVG(price)
FROM products);Section E: Spot the Error
Q1
C — A sub-selection must be wrapped in round brackets ( ), not square brackets. It should be WHERE points > (
Q2
A — A trailing comma after the last field in the SELECT list is a syntax error. It should be SELECT firstName
Q3
D — The products table has no cost column, so the inner query fails. It should be SELECT AVG(price)
Q4
B — The table is called students, so the outer query needs FROM students. The table name must match exactly.
Q5
D — A subquery used with IN must return a single column to compare with subjectId. It should be SELECT subjectId