SQL aggregate functions: COUNT, SUM, AVG, MIN, MAX
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which query counts every row in a table called students?
2. Which query finds the total of all values in the column points?
3. True or false: SELECT AVG(score) FROM results; returns one value, the mean score.
4. Which function gives you the highest value in a column?
5. Which query finds the lowest price in a table called shop?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1SELECT COUNT(*) FROM results; | ASELECT SUM(price) FROM sales; | |
| 2SELECT MAX(score) FROM results; | BCounts how many rows are in the results table and returns that single number. | |
| 3Add up all the values in a column to get a total | CThe column name is missing brackets. An aggregate function needs brackets around the column, like AVG(score). | |
| 4Use WHERE to work out an average for only some rows | DAVG is stuck on the end of the query. A function must go in the SELECT part, such as SELECT AVG(score), not after WHERE. | |
| 5SELECT AVG score FROM results; | ESELECT AVG(score) FROM results WHERE year = 9; | |
| 6SELECT COUNT(*) FROM results WHERE score > 50 AVG; | FReturns the highest score in the results table. |
C
Definitions
SELECT AVG(score) FROM results;
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT _____(*) FROM results;
2.
SELECT ___(score) FROM results;
3.
SELECT ___(score) FROM results;
4.
SELECT MIN(score), ___(score)
FROM results;
5.
SELECT COUNT(*) FROM results
_____ score > 50;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT COUNT(*)
B
FROM results
C
WHERE subject = Maths;
Question 2
A
SELECT SUM(price)
B
FROM orders
C
WHERE status == 'paid';
Question 3
A
SELECT MIN(score)
B
FROM results
C
WHERE year = 10
D
AND subject = 'Maths;
Question 4
A
SELECT AVERAGE(score)
B
FROM results
C
WHERE year = 9;
Question 5
A
SELECT COUNT(*)
B
FROM students
C
WHERE age => 15;
Answer Key
SQL aggregate functions: COUNT, SUM, AVG, MIN, MAX
Section A: Quick Questions
Q1
A — SELECT COUNT(*) FROM students;
Q2
A — SELECT SUM(points) FROM games;
Q3
A — True
Q4
B — MAX(score)
Q5
B — SELECT MIN(price) FROM shop;
Section B: Match the Pairs
Q1
SELECT COUNT(*) FROM results; → Counts how many rows are in the results table and returns that single number.
Q2
SELECT MAX(score) FROM results; → Returns the highest score in the results table.
Q3
Add up all the values in a column to get a total → SELECT SUM(price) FROM sales;
Q4
Use WHERE to work out an average for only some rows → SELECT AVG(score) FROM results WHERE year = 9;
Q5
SELECT AVG score FROM results; → The column name is missing brackets. An aggregate function needs brackets around the column, like AVG(score).
Q6
SELECT COUNT(*) FROM results WHERE score > 50 AVG; → AVG is stuck on the end of the query. A function must go in the SELECT part, such as SELECT AVG(score), not after WHERE.
Section C: Definitions
Q1
SELECT AVG(score) FROM results; — An aggregate function works out one value from many rows. COUNT, SUM, AVG, MIN and MAX give the count, total, mean, lowest and highest. This query returns the mean score.Section D: Fill in the Blanks
Q1
SELECT COUNT(*) FROM results;Q2
SELECT SUM(score) FROM results;Q3
SELECT AVG(score) FROM results;Q4
SELECT MIN(score), MAX(score)
FROM results;Q5
SELECT COUNT(*) FROM results
WHERE score > 50;Section E: Spot the Error
Q1
C — Text values in SQL must be in quotes. Maths should be written as 'Maths', otherwise SQL thinks it is a column name.
Q2
C — SQL uses a single = to compare values, not ==. It should be WHERE status = 'paid';
Q3
D — The text value is missing its closing quote. It should be AND subject = 'Maths';
Q4
A — AVERAGE is not an SQL aggregate function. The function for the mean is AVG, so it should be SELECT AVG(score).
Q5
C — The 'greater than or equal to' operator is >=, not =>. It should be WHERE age >= 15;