Working out the output of an SQL statement
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which order do you follow to work out a query's output from a table?
2. A table has 6 records and exactly 2 have Year = 10. How many rows does SELECT Name FROM Students WHERE Year = 10; output?
3. For SELECT Name, Score FROM Results ORDER BY Score DESC; which row appears first in the output?
4. For SELECT Name FROM Pupils WHERE Age > 15; the pupils are Ana (14), Ben (16), Cal (17). What is the output?
5. For SELECT Name FROM Pupils WHERE Age > 15 AND House = 'Red'; which rows pass the WHERE?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1SELECT firstName FROM students WHERE house = 'Red' ORDER BY points DESC; | AKeeps only the Year 7 rows, then outputs a single number: how many rows passed the WHERE condition. | |
| 2SELECT COUNT(*) FROM students WHERE yearLevel = 7; | BAND needs both conditions to be true, but no student is in Year 7 and Year 8 at once. No row passes, so the count is 0. OR should be used to count both years. | |
| 3If no row passes the WHERE condition, the output shows the field headings but no records. | CWHERE is placed after ORDER BY. The clause order is wrong, so the query will not run. WHERE must come first to pick the rows before they are sorted. | |
| 4With OR, a row is kept if either condition is true. Rows are picked first, then the chosen fields are shown. | DSELECT firstName FROM students WHERE house = 'Red' OR yearLevel = 7; | |
| 5SELECT firstName FROM students ORDER BY points DESC WHERE house = 'Red'; | ESELECT firstName FROM students WHERE points > 500; | |
| 6SELECT COUNT(*) FROM students WHERE yearLevel = 7 AND yearLevel = 8; | FKeeps only the Red house rows, sorts them from highest points to lowest, then shows just the firstName column. |
C
Definitions
WHERE, ORDER BY, SELECT
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT firstName, points
FROM students
_____ points > 100;
2.
SELECT firstName, points
FROM students
ORDER BY points ____;
3.
SELECT firstName
FROM students
WHERE house = 'Red' ___ points > 100;
4.
SELECT _____(*)
FROM students
WHERE house = 'Blue';
5.
SELECT firstName, points FROM students
WHERE house = 'Red'
________ points DESC;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT firstName, house
B
FROM students
C
WHERE house = Red
D
ORDER BY firstName;
Question 2
A
SELECT name, price
B
FROM products
C
WHERE stock == 0
D
ORDER BY name;
Question 3
A
SELECT firstName, points
B
FROM students
C
WHERE yearLevel = 8
D
ORDER points DESC;
Question 4
A
SELECT SUM points
B
FROM students
C
WHERE house = 'Blue';
Question 5
A
SELECT COUNT(*)
B
FORM students
C
WHERE points > 100;
Answer Key
Working out the output of an SQL statement
Section A: Quick Questions
Q1
B — Keep rows that pass WHERE, sort with ORDER BY, then show the SELECT fields
Q2
B — 2 rows
Q3
A — The row with the highest Score
Q4
A — Ben and Cal under the heading Name
Q5
A — Only rows where both conditions are true
Section B: Match the Pairs
Q1
SELECT firstName FROM students WHERE house = 'Red' ORDER BY points DESC; → Keeps only the Red house rows, sorts them from highest points to lowest, then shows just the firstName column.
Q2
SELECT COUNT(*) FROM students WHERE yearLevel = 7; → Keeps only the Year 7 rows, then outputs a single number: how many rows passed the WHERE condition.
Q3
If no row passes the WHERE condition, the output shows the field headings but no records. → SELECT firstName FROM students WHERE points > 500;
Q4
With OR, a row is kept if either condition is true. Rows are picked first, then the chosen fields are shown. → SELECT firstName FROM students WHERE house = 'Red' OR yearLevel = 7;
Q5
SELECT firstName FROM students ORDER BY points DESC WHERE house = 'Red'; → WHERE is placed after ORDER BY. The clause order is wrong, so the query will not run. WHERE must come first to pick the rows before they are sorted.
Q6
SELECT COUNT(*) FROM students WHERE yearLevel = 7 AND yearLevel = 8; → AND needs both conditions to be true, but no student is in Year 7 and Year 8 at once. No row passes, so the count is 0. OR should be used to count both years.
Section C: Definitions
Q1
WHERE, ORDER BY, SELECT — To find a query's output, first keep the rows that pass WHERE, then sort them with ORDER BY, then show only the SELECT fields or the SUM or COUNT result.Section D: Fill in the Blanks
Q1
SELECT firstName, points
FROM students
WHERE points > 100;Q2
SELECT firstName, points
FROM students
ORDER BY points DESC;Q3
SELECT firstName
FROM students
WHERE house = 'Red' AND points > 100;Q4
SELECT COUNT(*)
FROM students
WHERE house = 'Blue';Q5
SELECT firstName, points FROM students
WHERE house = 'Red'
ORDER BY points DESC;Section E: Spot the Error
Q1
C — Text values must be in quotes. Without them Red is read as a field name, so no rows can be kept. It should be WHERE house = 'Red'.
Q2
C — SQL tests equality with a single =. The double == is not valid, so the WHERE step fails. It should be WHERE stock = 0.
Q3
D — The sorting clause is ORDER BY, not ORDER. Without BY the sort step cannot be applied. It should be ORDER BY points DESC;
Q4
A — SUM needs brackets round the field it adds up. The result is the total of points for the rows that pass WHERE. It should be SELECT SUM(points).
Q5
B — The keyword is FROM, not FORM. FROM names the table whose rows WHERE then filters. It should be FROM students.