Counting and summarising with a query
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which query function tells you HOW MANY records match your criteria?
2. A query uses AVG(Price) on a Games table. What does it give you?
3. Which query would answer 'How many Year 7 students are in the Students table?'
4. True or false: COUNT, SUM, AVG, MIN and MAX are all aggregate functions.
5. You want the average test mark from a Results table. Which is correct?
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 Students WHERE Year = 7 | AAVG needs a field name to average, such as AVG(Score). The star only works with COUNT, which counts records. | |
| 2SELECT AVG(Score) FROM Results | BThe FROM keyword is missing, so the query doesn't say which table to count. It should be FROM Students. | |
| 3Find the total number of records in a Pets table that are dogs | CSELECT AVG(Price) FROM Canteen | |
| 4Find the average price of every item in a Canteen table | DSELECT COUNT(*) FROM Pets WHERE Type = 'Dog' | |
| 5SELECT AVG(*) FROM Results | EWorks out the average of all the values in the Score field | |
| 6SELECT COUNT(*) Students WHERE Year = 8 | FCounts how many records in Students have a Year of 7 and gives back one number |
C
Definitions
aggregate function
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT _____(*) FROM Pets;
2.
SELECT COUNT(*) FROM Pets _____ Age > 5;
3.
SELECT ___(Score) FROM Results;
4.
SELECT COUNT(_) FROM Games;
5.
SELECT ___(Score)
FROM Results
WHERE Year = 7;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT COUNT(*)
B
FROM Students
C
WHERE Year = 8
D
AND Pet = Dog;
Question 2
A
SELECT AVG(Score)
B
FROM Tests
C
WHERE Subject == 'Maths';
Question 3
A
SELECT AVG(Age)
B
FROM Players
C
WHERE Team = 'Reds'
D
AND Age => 12;
Question 4
A
SELECT COUNT(*
B
FROM Library
C
WHERE Genre = 'Sci-Fi';
Question 5
A
SELECT
B
SUM[Goals]
C
FROM Matches
D
WHERE Team = 'Eagles';
Answer Key
Counting and summarising with a query
Section A: Quick Questions
Q1
B — COUNT(*)
Q2
A — One number: the average price of all the games
Q3
A — SELECT COUNT(*) FROM Students WHERE Year = 7
Q4
A — True
Q5
B — SELECT AVG(Mark) FROM Results
Section B: Match the Pairs
Q1
SELECT COUNT(*) FROM Students WHERE Year = 7 → Counts how many records in Students have a Year of 7 and gives back one number
Q2
SELECT AVG(Score) FROM Results → Works out the average of all the values in the Score field
Q3
Find the total number of records in a Pets table that are dogs → SELECT COUNT(*) FROM Pets WHERE Type = 'Dog'
Q4
Find the average price of every item in a Canteen table → SELECT AVG(Price) FROM Canteen
Q5
SELECT AVG(*) FROM Results → AVG needs a field name to average, such as AVG(Score). The star only works with COUNT, which counts records.
Q6
SELECT COUNT(*) Students WHERE Year = 8 → The FROM keyword is missing, so the query doesn't say which table to count. It should be FROM Students.
Section C: Definitions
Q1
aggregate function — A query function like COUNT, SUM, AVG, MIN or MAX that looks at many records and gives one answer, such as how many records match or the average of a field.Section D: Fill in the Blanks
Q1
SELECT COUNT(*) FROM Pets;Q2
SELECT COUNT(*) FROM Pets WHERE Age > 5;Q3
SELECT AVG(Score) FROM Results;Q4
SELECT COUNT(*) FROM Games;Q5
SELECT MAX(Score)
FROM Results
WHERE Year = 7;Section E: Spot the Error
Q1
D — Text values in a query criterion need quote marks. Without them, Dog looks like a field name. It should be AND Pet = 'Dog';
Q2
C — A query criterion uses a single = to compare values, not ==. It should be WHERE Subject = 'Maths';
Q3
D — The 'greater than or equal to' operator is written >=, not =>. It should be AND Age >= 12;
Q4
A — The COUNT function is missing its closing bracket. It should be SELECT COUNT(*)
Q5
B — Aggregate functions use round brackets around the field name, not square brackets. It should be SUM(Goals)