SQL HAVING
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which clause filters groups after GROUP BY using an aggregate condition?
2. Which query correctly lists houses with more than 2 students?
3. When does WHERE filter data compared with HAVING?
4. Which is the correct clause order?
5. Which condition belongs in WHERE, not HAVING?
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(*) > 2 | ASELECT house, COUNT(*) FROM students WHERE yearLevel = 7 GROUP BY house; | |
| 2HAVING AVG(mark) >= 75 | BKeeps only the groups whose average mark is 75 or higher. | |
| 3WHERE filters individual rows before grouping, so it can remove Year 7 students before they are counted. | CHAVING is placed before GROUP BY. It must come after GROUP BY, as it filters the groups that GROUP BY creates. | |
| 4Clause order: HAVING comes after GROUP BY and before ORDER BY. | DWHERE cannot use an aggregate like COUNT(*) because it filters rows before groups exist. The condition should be in HAVING after GROUP BY. | |
| 5SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 2 GROUP BY house; | ESELECT house, SUM(points) FROM students GROUP BY house HAVING SUM(points) > 200 ORDER BY house; | |
| 6SELECT house, COUNT(*) FROM students HAVING COUNT(*) > 2 GROUP BY house; | FGroups students by house, then keeps only the houses that have more than 2 students. |
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 house, COUNT(*) FROM students
GROUP BY house
HAVING _____(*) > 2;
3.
SELECT house, COUNT(*) FROM students
_____ yearLevel = 8
GROUP BY house;
4.
SELECT category, COUNT(*) FROM products
________ category
HAVING COUNT(*) > 2;
5.
SELECT subjectId, AVG(mark) FROM results
GROUP BY subjectId
HAVING AVG(mark) _ 70;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT house FROM students
B
GROUP house
C
HAVING COUNT(*) > 2;
Question 2
A
SELECT house, AVG(points)
B
FROM students
C
ORDER BY house
D
HAVING AVG(points) > 100;
Question 3
A
SELECT house, COUNT(*)
B
FROM students
C
GROUP BY house
D
HAVING COUNT > 2;
Question 4
A
SELECT subjectId AVG(mark)
B
FROM results
C
GROUP BY subjectId
D
HAVING AVG(mark) > 70;
Question 5
A
SELECT category, COUNT(*)
B
FROM products
C
GROUP BY category
D
WHERE COUNT(*) >= 2
E
ORDER BY COUNT(*) DESC;
Answer Key
SQL HAVING
Section A: Quick Questions
Q1
B — HAVING COUNT(*) > 2
Q2
A — SELECT house FROM students GROUP BY house HAVING COUNT(*) > 2;
Q3
A — WHERE filters rows before grouping; HAVING filters groups after grouping
Q4
A — SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
Q5
B — yearLevel = 8
Section B: Match the Pairs
Q1
GROUP BY house HAVING COUNT(*) > 2 → Groups students by house, then keeps only the houses that have more than 2 students.
Q2
HAVING AVG(mark) >= 75 → Keeps only the groups whose average mark is 75 or higher.
Q3
WHERE filters individual rows before grouping, so it can remove Year 7 students before they are counted. → SELECT house, COUNT(*) FROM students WHERE yearLevel = 7 GROUP BY house;
Q4
Clause order: HAVING comes after GROUP BY and before ORDER BY. → SELECT house, SUM(points) FROM students GROUP BY house HAVING SUM(points) > 200 ORDER BY house;
Q5
SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 2 GROUP BY house; → WHERE cannot use an aggregate like COUNT(*) because it filters rows before groups exist. The condition should be in HAVING after GROUP BY.
Q6
SELECT house, COUNT(*) FROM students HAVING COUNT(*) > 2 GROUP BY house; → HAVING is placed before GROUP BY. It must come after GROUP BY, as it filters the groups that GROUP BY creates.
Section C: Definitions
Q1
HAVING — Filters groups after GROUP BY using a condition on an aggregate, e.g. HAVING COUNT(*) > 2. WHERE filters rows before grouping. HAVING comes after GROUP BY.Section D: Fill in the Blanks
Q1
SELECT house, COUNT(*) FROM students
GROUP BY house
HAVING COUNT(*) > 2;Q2
SELECT house, COUNT(*) FROM students
GROUP BY house
HAVING COUNT(*) > 2;Q3
SELECT house, COUNT(*) FROM students
WHERE yearLevel = 8
GROUP BY house;Q4
SELECT category, COUNT(*) FROM products
GROUP BY category
HAVING COUNT(*) > 2;Q5
SELECT subjectId, AVG(mark) FROM results
GROUP BY subjectId
HAVING AVG(mark) > 70;Section E: Spot the Error
Q1
B — The grouping clause is GROUP BY, not GROUP. The line should be GROUP BY house, and HAVING then filters those groups.
Q2
C — HAVING filters groups, so the groups must be created first with GROUP BY. The line should be GROUP BY house, not ORDER BY.
Q3
D — COUNT is a function, so it needs brackets and an argument. It should be HAVING COUNT(*) > 2; to count the rows in each group.
Q4
A — Items in the SELECT list must be separated by a comma. It should be SELECT subjectId, AVG(mark).
Q5
D — WHERE filters rows before grouping, so it cannot use an aggregate like COUNT(*). A condition on a group count belongs in HAVING, after GROUP BY.