Completing an SQL script
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Task: show only the Name of every student. Which completes SELECT ___ FROM Student; ?
2. Task: list students in Year 10 only. Which completes SELECT Name FROM Student ___ ; ?
3. Task: show the products with the highest Price first. Which completes SELECT Name FROM Product ORDER BY Price ___ ; ?
4. Task: show students in Year 9 who are also in House 'Red'. Which completes WHERE Year = 9 ___ House = 'Red'; ?
5. Task: show students who are in House 'Red' or House 'Blue'. Which completes WHERE House = 'Red' ___ House = 'Blue'; ?
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, points FROM students WHERE house = 'Red' ORDER BY points DESC; | ASELECT name FROM products WHERE price > 5 OR stock = 0; | |
| 2SELECT COUNT(studentId) FROM students WHERE yearLevel = 8 AND house = 'Blue'; | BCounts the students who are in Year 8 and also in Blue house | |
| 3The task asks for the total of all the points in the table, so the missing function in the gap is SUM | CShows the first name and points of Red house students, with the highest points first | |
| 4The task asks for products costing more than 5 OR with no stock, so the missing keyword in the gap is OR | DThe sort direction is missing. ORDER BY defaults to ASC, so the cheapest comes first. DESC must be added after price. | |
| 5SELECT name, price FROM products ORDER BY price; -- task: most expensive first | ESELECT SUM(points) FROM students; | |
| 6SELECT firstName FROM students WHERE; -- task: students with more than 100 points | FThe condition after WHERE is missing. It needs a field, an operator and a value, such as points > 100. |
C
Definitions
SELECT field FROM table WHERE condition;
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT ________
FROM students;
2.
SELECT firstName
FROM students
WHERE ____________;
3.
SELECT firstName, points
FROM students
ORDER BY points ____;
4.
SELECT firstName FROM students
WHERE yearLevel = 7 ___ points > 100;
5.
SELECT ___(points)
FROM students
WHERE house = 'Red';
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
-- Show first name and points
B
SELECT firstName, house
C
FROM students
D
WHERE house = 'Red';
Question 2
A
-- Highest points first
B
SELECT firstName, points
C
FROM students
D
ORDER BY points ASC;
Question 3
A
-- How many students are Red?
B
SELECT SUM(studentId)
C
FROM students
D
WHERE house = 'Red';
Question 4
A
-- Products with stock under 10
B
SELECT name, stock
C
FROM products
D
WHERE stock > 10;
Question 5
A
SELECT firstName, price
B
FROM students
C
WHERE house = 'Blue'
D
ORDER BY points DESC;
Answer Key
Completing an SQL script
Section A: Quick Questions
Q1
B — Name
Q2
A — WHERE Year = 10
Q3
A — DESC
Q4
A — AND
Q5
B — OR
Section B: Match the Pairs
Q1
SELECT firstName, points FROM students WHERE house = 'Red' ORDER BY points DESC; → Shows the first name and points of Red house students, with the highest points first
Q2
SELECT COUNT(studentId) FROM students WHERE yearLevel = 8 AND house = 'Blue'; → Counts the students who are in Year 8 and also in Blue house
Q3
The task asks for the total of all the points in the table, so the missing function in the gap is SUM → SELECT SUM(points) FROM students;
Q4
The task asks for products costing more than 5 OR with no stock, so the missing keyword in the gap is OR → SELECT name FROM products WHERE price > 5 OR stock = 0;
Q5
SELECT name, price FROM products ORDER BY price; -- task: most expensive first → The sort direction is missing. ORDER BY defaults to ASC, so the cheapest comes first. DESC must be added after price.
Q6
SELECT firstName FROM students WHERE; -- task: students with more than 100 points → The condition after WHERE is missing. It needs a field, an operator and a value, such as points > 100.
Section C: Definitions
Q1
SELECT field FROM table WHERE condition; — Completing an SQL script: read the task, then fill the gap with the right field, WHERE condition, ASC or DESC, AND or OR, or SUM or COUNT so the query returns exactly what is asked.Section D: Fill in the Blanks
Q1
SELECT lastName
FROM students;Q2
SELECT firstName
FROM students
WHERE points > 100;Q3
SELECT firstName, points
FROM students
ORDER BY points DESC;Q4
SELECT firstName FROM students
WHERE yearLevel = 7 AND points > 100;Q5
SELECT SUM(points)
FROM students
WHERE house = 'Red';Section E: Spot the Error
Q1
B — The task asks for first name and points, but the query selects house. The missing field after SELECT should be points: SELECT firstName, points
Q2
D — Highest first means descending order. The sort direction must be DESC, not ASC.
Q3
B — The task asks how many, so the query must count records. It should use COUNT, not SUM. Adding up ID numbers gives a meaningless total.
Q4
D — Stock under 10 needs the less-than operator. The condition should be WHERE stock < 10; using > returns the wrong records.
Q5
A — The students table has no price field. The missing field after SELECT should be a real field from students, such as points, which is also used in ORDER BY.