SQL HAVING
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which query lists houses that have more than 2 students?
2. HAVING filters groups. WHERE filters rows. Which statement is true?
3. Which clause order is correct?
4. You want only subjects where the average mark is above 70. Which condition is correct?
5. You want to ignore students in Year 7 before counting students per house. Which is the best choice?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1GROUP BY house HAVING COUNT(*) > 3 | AKeeps only the houses that have more than 3 students | |
| 2GROUP BY subjectId HAVING AVG(mark) >= 70 | BSELECT subjectId, AVG(mark) FROM results GROUP BY subjectId HAVING AVG(mark) > 75; | |
| 3WHERE filters individual rows before grouping, so it is used for plain column conditions | CAn aggregate such as COUNT(*) is used in WHERE. WHERE runs before grouping, so move the condition to HAVING after GROUP BY. | |
| 4HAVING filters whole groups after GROUP BY, so it can use an aggregate such as AVG | DKeeps only the subjects whose average mark is 70 or more | |
| 5SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 3 GROUP BY house; | EHAVING is placed before GROUP BY. The clause order is wrong: HAVING must come after GROUP BY. | |
| 6SELECT house, COUNT(*) FROM students HAVING COUNT(*) > 3 GROUP BY house; | FSELECT house, COUNT(*) FROM students WHERE yearLevel = 7 GROUP BY house; |
C
Definitions
HAVING
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT house, COUNT(*) FROM students
GROUP BY house
______ COUNT(*) > 2;
2.
SELECT category, AVG(price) FROM products
GROUP BY category
HAVING ___(price) > 5;
3.
SELECT house, COUNT(*) FROM students
_____ yearLevel = 8
GROUP BY house;
4.
SELECT studentId, AVG(mark) FROM results
GROUP BY studentId
HAVING AVG(mark) __ 75;
5.
SELECT house, SUM(points) FROM students
________ house
HAVING SUM(points) > 200;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT house, COUNT(*)
B
FROM students GROUP house
C
HAVING COUNT(*) > 2;
Question 2
A
SELECT house, AVG(points)
B
FROM students GROUP BY house
C
WHERE AVG(points) > 100;
Question 3
A
SELECT category, AVG(price)
B
FROM products
C
GROUP BY category
D
HAVING price > 10;
Question 4
A
SELECT studentId COUNT(*)
B
FROM orders
C
GROUP BY studentId
D
HAVING COUNT(*) >= 2;
Question 5
A
SELECT studentId, COUNT(*)
B
FROM orders ORDER BY studentId
C
HAVING COUNT(*) > 1;
Answer Key
SQL HAVING
Section A: Quick Questions
Q1
A — SELECT house, COUNT(*) FROM students GROUP BY house HAVING COUNT(*) > 2;
Q2
B — WHERE filters rows before they are grouped
Q3
A — GROUP BY house HAVING COUNT(*) > 2
Q4
B — HAVING AVG(mark) > 70
Q5
A — WHERE yearLevel <> 7
Section B: Match the Pairs
Q1
GROUP BY house HAVING COUNT(*) > 3 → Keeps only the houses that have more than 3 students
Q2
GROUP BY subjectId HAVING AVG(mark) >= 70 → Keeps only the subjects whose average mark is 70 or more
Q3
WHERE filters individual rows before grouping, so it is used for plain column conditions → SELECT house, COUNT(*) FROM students WHERE yearLevel = 7 GROUP BY house;
Q4
HAVING filters whole groups after GROUP BY, so it can use an aggregate such as AVG → SELECT subjectId, AVG(mark) FROM results GROUP BY subjectId HAVING AVG(mark) > 75;
Q5
SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 3 GROUP BY house; → An aggregate such as COUNT(*) is used in WHERE. WHERE runs before grouping, so move the condition to HAVING after GROUP BY.
Q6
SELECT house, COUNT(*) FROM students HAVING COUNT(*) > 3 GROUP BY house; → HAVING is placed before GROUP BY. The clause order is wrong: HAVING must come after GROUP BY.
Section C: Definitions
Q1
HAVING — Filters groups after GROUP BY using an aggregate condition, such as HAVING COUNT(*) > 3. WHERE filters rows before grouping.Section D: Fill in the Blanks
Q1
SELECT house, COUNT(*) FROM students
GROUP BY house
HAVING COUNT(*) > 2;Q2
SELECT category, AVG(price) FROM products
GROUP BY category
HAVING AVG(price) > 5;Q3
SELECT house, COUNT(*) FROM students
WHERE yearLevel = 8
GROUP BY house;Q4
SELECT studentId, AVG(mark) FROM results
GROUP BY studentId
HAVING AVG(mark) >= 75;Q5
SELECT house, SUM(points) FROM students
GROUP BY house
HAVING SUM(points) > 200;Section E: Spot the Error
Q1
B — GROUP BY needs the word BY. The line should be FROM students GROUP BY house so the rows are grouped before HAVING filters the groups.
Q2
C — WHERE filters single rows before grouping, so it cannot use an aggregate like AVG(). To filter groups by their average, use HAVING AVG(points) > 100;
Q3
D — HAVING must test an aggregate value for each group. It should be HAVING AVG(price) > 10; because price alone is a single row value, not a group value.
Q4
A — Columns in the SELECT list must be separated by a comma. It should be SELECT studentId, COUNT(*).
Q5
B — ORDER BY only sorts rows and does not make groups. HAVING needs GROUP BY, so the line should be FROM orders GROUP BY studentId.