Common mistakes in SQL scripts
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which query correctly finds students in house Red?
2. A student wants all products that cost 10 or more. Which condition is correct?
3. A query must find students in year 7 OR year 8. Which WHERE clause is correct?
4. Which query has its clauses in the correct order?
5. Which query names the table correctly?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1SELECT * FROM students WHERE house = Red; | AError: the clauses are in the wrong order. WHERE must come before ORDER BY. | |
| 2SELECT * FROM students ORDER BY points WHERE yearLevel = 7; | BSELECT firstName FROM students WHERE points >= 100; | |
| 3To find students with 100 points or more, use >= because > would leave out students on exactly 100. | CSELECT * FROM students WHERE yearLevel = 7 OR yearLevel = 8; | |
| 4To find students in year 7 or year 8, use OR because no student can be in both years at once. | DThe table name is missing: FROM and the table name (students) must follow the field list. | |
| 5SELECT firstName, points WHERE points > 50; | EError: the text value Red has no quotes, so SQL treats it as a field name. It should be 'Red'. | |
| 6SELECT * FROM products WHERE category = 'Tech' AND category = 'Sport'; | FAND is used when OR is needed: a product cannot be in two categories at once, so no records match. |
C
Definitions
SQL error
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT firstName
FROM ________
WHERE points > 100;
2.
-- Students with 100 points or more
SELECT firstName FROM students
WHERE points __ 100;
3.
-- Products in either category
SELECT name FROM products
WHERE category = 'Tech' __
category = 'Sport';
4.
SELECT firstName FROM students
WHERE house = 'Red'
________ points DESC;
5.
SELECT name FROM products
_____ category = 'Tech'
ORDER BY price;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT firstName, house
B
FROM
C
WHERE house = 'Red';
Question 2
A
SELECT name FROM products
B
WHERE category = Tech
C
ORDER BY price;
Question 3
A
SELECT firstName
B
FROM students
C
WHERE house = 'Red'
D
AND house = 'Blue';
Question 4
A
-- Points of 100 or more
B
SELECT firstName FROM students
C
WHERE points > 100
D
ORDER BY points;
Question 5
A
SELECT COUNT(*) FROM students
B
SELECT SUM(points)
C
FROM students;
Answer Key
Common mistakes in SQL scripts
Section A: Quick Questions
Q1
A — SELECT * FROM students WHERE house = 'Red';
Q2
B — WHERE price >= 10
Q3
A — WHERE yearLevel = 7 OR yearLevel = 8
Q4
A — SELECT * FROM students WHERE points > 100 ORDER BY lastName;
Q5
B — SELECT firstName FROM students;
Section B: Match the Pairs
Q1
SELECT * FROM students WHERE house = Red; → Error: the text value Red has no quotes, so SQL treats it as a field name. It should be 'Red'.
Q2
SELECT * FROM students ORDER BY points WHERE yearLevel = 7; → Error: the clauses are in the wrong order. WHERE must come before ORDER BY.
Q3
To find students with 100 points or more, use >= because > would leave out students on exactly 100. → SELECT firstName FROM students WHERE points >= 100;
Q4
To find students in year 7 or year 8, use OR because no student can be in both years at once. → SELECT * FROM students WHERE yearLevel = 7 OR yearLevel = 8;
Q5
SELECT firstName, points WHERE points > 50; → The table name is missing: FROM and the table name (students) must follow the field list.
Q6
SELECT * FROM products WHERE category = 'Tech' AND category = 'Sport'; → AND is used when OR is needed: a product cannot be in two categories at once, so no records match.
Section C: Definitions
Q1
SQL error — A mistake in an SQL script, such as text without quotes, > instead of >=, AND instead of OR, clauses in the wrong order, a missing table after FROM or a missing semicolon.Section D: Fill in the Blanks
Q1
SELECT firstName
FROM students
WHERE points > 100;Q2
-- Students with 100 points or more
SELECT firstName FROM students
WHERE points >= 100;Q3
-- Products in either category
SELECT name FROM products
WHERE category = 'Tech' OR
category = 'Sport';Q4
SELECT firstName FROM students
WHERE house = 'Red'
ORDER BY points DESC;Q5
SELECT name FROM products
WHERE category = 'Tech'
ORDER BY price;Section E: Spot the Error
Q1
B — The table name is missing after FROM. SQL needs to know which table to read, so it should be FROM students.
Q2
B — Text values in a WHERE condition must be inside quotes. It should be WHERE category = 'Tech'.
Q3
D — A student is in only one house, so AND would return no rows. To get students from either house, use OR.
Q4
C — The task needs 100 points or more, so 100 must be included. > leaves out students with exactly 100, so use >=.
Q5
A — The first statement is missing its semicolon, so it runs into the next statement. It should end with ;.