Sub-query returning a single value
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which query finds results with a mark above the average mark?
2. What does the sub-query (SELECT AVG(mark) FROM results) return?
3. Which sub-query gives the highest mark, so you can find the top result?
4. Which WHERE clause lists products cheaper than the average price?
5. A sub-query that returns one aggregate value is compared with the outer field using which kind of operator?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1WHERE mark > (SELECT AVG(mark) FROM results) | ASELECT firstName FROM students WHERE points = (SELECT MIN(points) FROM students); | |
| 2WHERE price = (SELECT MAX(price) FROM products) | BKeeps only rows whose mark is above the average mark of all results | |
| 3Find students whose points are below the lowest-scoring value in a comparison, using MIN in a sub-query to return one value | CThe sub-query returns every mark, not one value, so the outer WHERE cannot compare a single mark to it. | |
| 4Compare each row to the average by putting an aggregate sub-query in brackets after the comparison operator | DSELECT studentId FROM results WHERE mark > (SELECT AVG(mark) FROM results); | |
| 5SELECT * FROM results WHERE mark > SELECT AVG(mark) FROM results; | EKeeps only the product or products with the highest price | |
| 6SELECT * FROM results WHERE mark > (SELECT mark FROM results); | FThe sub-query is not wrapped in brackets, so SQL cannot tell where the inner query starts and ends. |
C
Definitions
WHERE mark > (SELECT AVG(mark) FROM results)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT studentId, mark FROM results
WHERE mark > (SELECT ___(mark)
FROM results);
2.
SELECT studentId, mark FROM results
WHERE mark _ (SELECT AVG(mark)
FROM results);
3.
SELECT name, price FROM products
WHERE price = (SELECT ___(price)
FROM products);
4.
SELECT firstName, points FROM students
WHERE points = (SELECT ___(points)
FROM students);
5.
SELECT name, price FROM products
WHERE price < (SELECT AVG(price)
FROM ________);
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT firstName, points,
B
FROM students
C
WHERE points >
D
(SELECT AVG(points)
E
FROM students);
Question 2
A
SELECT name, price
B
FROM products
C
WHERE price
D
(SELECT MAX(price)
E
FROM products);
Question 3
A
SELECT studentId, mark
B
FROM result
C
WHERE mark >
D
(SELECT AVG(mark)
E
FROM results);
Question 4
A
SELECT name, stock
B
FROM products
C
WHERE stock <
D
(SELECT stock
E
FROM products);
Question 5
A
SELECT firstName, points
B
FROM students
C
WHERE points ==
D
(SELECT MIN(points)
E
FROM students);
Answer Key
Sub-query returning a single value
Section A: Quick Questions
Q1
A — SELECT * FROM results WHERE mark > (SELECT AVG(mark) FROM results);
Q2
B — One single value: the average mark
Q3
A — (SELECT MAX(mark) FROM results)
Q4
B — WHERE price < (SELECT AVG(price) FROM products)
Q5
A — A comparison operator such as >, < or =
Section B: Match the Pairs
Q1
WHERE mark > (SELECT AVG(mark) FROM results) → Keeps only rows whose mark is above the average mark of all results
Q2
WHERE price = (SELECT MAX(price) FROM products) → Keeps only the product or products with the highest price
Q3
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);
Q4
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);
Q5
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.
Q6
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.
Section C: Definitions
Q1
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.Section D: Fill in the Blanks
Q1
SELECT studentId, mark FROM results
WHERE mark > (SELECT AVG(mark)
FROM results);Q2
SELECT studentId, mark FROM results
WHERE mark > (SELECT AVG(mark)
FROM results);Q3
SELECT name, price FROM products
WHERE price = (SELECT MAX(price)
FROM products);Q4
SELECT firstName, points FROM students
WHERE points = (SELECT MIN(points)
FROM students);Q5
SELECT name, price FROM products
WHERE price < (SELECT AVG(price)
FROM products);Section E: Spot the Error
Q1
A — There is a trailing comma after points, so SQL expects another field before FROM. Remove it: SELECT firstName, points.
Q2
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 <.
Q3
B — The table is called results, not result. The outer query must name the real table: FROM results.
Q4
D — This sub-query returns every stock value, not a single value. Compare with one number by using an aggregate: (SELECT AVG(stock).
Q5
C — SQL uses a single = to compare values, not ==. It should be WHERE points =.