SQL HAVING
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. You want only the houses that have more than 2 students. Which clause filters on COUNT(*) after grouping?
2. Which statement about WHERE and HAVING is true?
3. Which is the correct clause order in a grouped query?
4. Which query correctly lists houses with more than 2 students?
5. You want to ignore students in Year 7 BEFORE counting students in each house. Which condition goes in WHERE?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1HAVING COUNT(*) > 2 | AKeeps only the groups that contain more than two rows | |
| 2GROUP BY house HAVING SUM(points) >= 200 | BKeeps only the houses whose total points are at least 200 | |
| 3WHERE filters individual rows before they are grouped, so it cannot use an aggregate like COUNT(*) | CHAVING is in the wrong place. It must come after GROUP BY, not before it. | |
| 4The correct clause order is WHERE, then GROUP BY, then HAVING | DWHERE runs before grouping, so it cannot use an aggregate function like COUNT(*). Use HAVING COUNT(*) > 2 after GROUP BY instead. | |
| 5SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 2 GROUP BY house; | ESELECT house, COUNT(*) FROM students WHERE yearLevel = 7 GROUP BY house; | |
| 6SELECT house, COUNT(*) FROM students HAVING COUNT(*) > 2 GROUP BY house; | FSELECT house, COUNT(*) FROM students WHERE points > 50 GROUP BY house HAVING COUNT(*) > 2; |
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, COUNT(*) FROM products
_____ price > 2
GROUP BY category;
3.
SELECT subjectId, AVG(mark) FROM results
GROUP BY subjectId
HAVING ___(mark) > 70;
4.
SELECT studentId, COUNT(*) FROM orders
________ studentId
HAVING COUNT(*) > 1;
5.
SELECT house, SUM(points) FROM students
GROUP BY 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
C
GROUP BY house
D
WHERE COUNT(*) > 2;
Question 2
A
SELECT house, COUNT(*)
B
FROM student
C
WHERE yearLevel > 7
D
GROUP BY house
E
HAVING COUNT(*) > 1;
Question 3
A
SELECT house, COUNT(*)
B
FROM students HAVING points>50
C
GROUP BY house
D
HAVING COUNT(*) > 2;
Question 4
A
SELECT house COUNT(*)
B
FROM students
C
GROUP BY house
D
HAVING COUNT(*) > 2;
Question 5
A
SELECT subjectId, AVG(marks)
B
FROM results
C
GROUP BY subjectId
D
HAVING AVG(mark) > 70;
Answer Key
SQL HAVING
Section A: Quick Questions
Q1
A — HAVING COUNT(*) > 2
Q2
B — WHERE filters rows before grouping; HAVING filters groups after grouping
Q3
B — WHERE, GROUP BY, HAVING
Q4
A — SELECT house, COUNT(*) FROM students GROUP BY house HAVING COUNT(*) > 2;
Q5
A — WHERE yearLevel > 7
Section B: Match the Pairs
Q1
HAVING COUNT(*) > 2 → Keeps only the groups that contain more than two rows
Q2
GROUP BY house HAVING SUM(points) >= 200 → Keeps only the houses whose total points are at least 200
Q3
WHERE filters individual rows before they are grouped, so it cannot use an aggregate like COUNT(*) → SELECT house, COUNT(*) FROM students WHERE yearLevel = 7 GROUP BY house;
Q4
The correct clause order is WHERE, then GROUP BY, then HAVING → SELECT house, COUNT(*) FROM students WHERE points > 50 GROUP BY house HAVING COUNT(*) > 2;
Q5
SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 2 GROUP BY house; → WHERE runs before grouping, so it cannot use an aggregate function like COUNT(*). Use HAVING COUNT(*) > 2 after GROUP BY instead.
Q6
SELECT house, COUNT(*) FROM students HAVING COUNT(*) > 2 GROUP BY house; → HAVING is in the wrong place. It must come after GROUP BY, not before it.
Section C: Definitions
Q1
HAVING — Filters groups after GROUP BY using an aggregate condition, e.g. HAVING COUNT(*) > 2. WHERE filters rows before grouping. Order: WHERE, GROUP BY, HAVING.Section D: Fill in the Blanks
Q1
SELECT house, COUNT(*) FROM students
GROUP BY house
HAVING COUNT(*) > 2;Q2
SELECT category, COUNT(*) FROM products
WHERE price > 2
GROUP BY category;Q3
SELECT subjectId, AVG(mark) FROM results
GROUP BY subjectId
HAVING AVG(mark) > 70;Q4
SELECT studentId, COUNT(*) FROM orders
GROUP BY studentId
HAVING COUNT(*) > 1;Q5
SELECT house, SUM(points) FROM students
GROUP BY house
HAVING SUM(points) > 200;Section E: Spot the Error
Q1
D — WHERE filters rows before grouping, so it cannot use an aggregate like COUNT(*). Filtering groups by an aggregate needs HAVING COUNT(*) > 2 after GROUP BY.
Q2
B — The table is called students, not student. The FROM clause must name a real table or the query fails.
Q3
B — A condition on single rows (points>50) is a WHERE condition and runs before grouping. HAVING comes after GROUP BY and is for aggregate conditions, so this should be WHERE points>50.
Q4
A — Columns and aggregates in SELECT must be separated by a comma. It should be SELECT house, COUNT(*).
Q5
A — The column in the results table is mark, not marks. It should be AVG(mark), the same as in the HAVING line.