SQL sub-select (subquery)
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which query uses a sub-select to find students with points above the average?
2. Where does a sub-select go in the query below?
3. True or false: the sub-select runs first, and its result is used by the outer query.
4. A sub-select returns a list of several studentId values. Which operator should the outer WHERE use?
5. Which sub-select is correct for finding the product with the highest price?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1WHERE points > (SELECT AVG(points) FROM students) | AThe sub-select can return many productId values, but = only compares with one value. IN should be used to match a list. | |
| 2WHERE studentId IN (SELECT studentId FROM orders) | BKeeps only the rows whose points are above the average, which the inner query works out first | |
| 3A sub-select that returns a single value can be used after = to find the rows that match it, such as the highest price | CSELECT name FROM products WHERE productId IN (SELECT productId FROM orders); | |
| 4When a sub-select returns a list of values, use IN so the outer query matches any value in that list | DKeeps only the students whose ID appears in the list of IDs the inner query returns from orders | |
| 5SELECT firstName FROM students WHERE points > SELECT AVG(points) FROM students; | EThe sub-select is missing its brackets. A sub-select must be wrapped in ( ) so the database knows where the inner query starts and ends. | |
| 6SELECT name FROM products WHERE productId = (SELECT productId FROM orders); | FSELECT name FROM products WHERE price = (SELECT MAX(price) FROM products); |
C
Definitions
(SELECT ...)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT firstName FROM students
WHERE points > (SELECT ___(points)
FROM students);
2.
SELECT name FROM products
WHERE productId __ (SELECT productId
FROM orders);
3.
SELECT firstName, points FROM students
WHERE points = (SELECT ___(points)
FROM students);
4.
SELECT name FROM products
WHERE price > (______ AVG(price)
FROM products);
5.
SELECT firstName FROM students
WHERE studentId IN (SELECT _________
FROM results WHERE mark > 90);
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT name, price
B
FROM products
C
WHERE price > SELECT
D
AVG(price) FROM products);
Question 2
A
SELECT name FROM products
B
WHERE productId =
C
(SELECT productId
D
FROM orders);
Question 3
A
SELECT firstName FROM students
B
WHERE firstName IN (SELECT
C
studentId FROM results
D
WHERE mark > 90);
Question 4
A
SELECT name, price,
B
FROM products
C
WHERE price = (SELECT
D
MAX(price) FROM products);
Question 5
A
SELECT studentId, mark
B
FROM subjects
C
WHERE mark > (SELECT
D
AVG(mark) FROM results);
Answer Key
SQL sub-select (subquery)
Section A: Quick Questions
Q1
B — SELECT firstName FROM students WHERE points > (SELECT AVG(points) FROM students);
Q2
B — Inside brackets in the WHERE clause: WHERE price > (SELECT AVG(price) FROM products)
Q3
A — True
Q4
A — WHERE studentId IN (SELECT studentId FROM orders)
Q5
A — SELECT name FROM products WHERE price = (SELECT MAX(price) FROM products);
Section B: Match the Pairs
Q1
WHERE points > (SELECT AVG(points) FROM students) → Keeps only the rows whose points are above the average, which the inner query works out first
Q2
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
Q3
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);
Q4
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);
Q5
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.
Q6
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.
Section C: Definitions
Q1
(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.Section D: Fill in the Blanks
Q1
SELECT firstName FROM students
WHERE points > (SELECT AVG(points)
FROM students);Q2
SELECT name FROM products
WHERE productId IN (SELECT productId
FROM orders);Q3
SELECT firstName, points FROM students
WHERE points = (SELECT MAX(points)
FROM students);Q4
SELECT name FROM products
WHERE price > (SELECT AVG(price)
FROM products);Q5
SELECT firstName FROM students
WHERE studentId IN (SELECT studentId
FROM results WHERE mark > 90);Section E: Spot the Error
Q1
C — A sub-select must be wrapped in brackets. The opening bracket is missing, so the line should be WHERE price > (SELECT.
Q2
B — The sub-select returns a list of many productId values. = only compares with one value, so it should be WHERE productId IN.
Q3
B — The sub-select returns studentId values, so the outer column must be studentId too. It should be WHERE studentId IN (SELECT.
Q4
A — The extra comma after price is followed by FROM, which is a syntax error. The line should be SELECT name, price.
Q5
B — The mark column is in the results table, not subjects. The outer query should be FROM results.