Mixing AND and OR with brackets in a WHERE clause
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which WHERE clause returns only Year 7 students who are in Red or Blue house?
2. In WHERE (A OR B) AND C, which part is worked out first?
3. A record meets condition A but not C. Is it returned by WHERE (A OR B) AND C?
4. A record meets condition A but not B or C. Is it returned by WHERE A OR (B AND C)?
5. The same conditions A, B and C are used, but the brackets are moved. Will the same records always be returned?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1WHERE (house = 'Red' OR house = 'Blue') AND yearLevel = 7 | AAND is used between two values of the same field. A record cannot be in two houses at once, so no rows are returned. It should be (house = 'Red' OR house = 'Blue'). | |
| 2WHERE house = 'Red' OR (house = 'Blue' AND yearLevel = 7) | BWHERE (yearLevel = 7 OR yearLevel = 8) AND points > 100 | |
| 3Brackets round an OR on the same field mean a record must match one of those two values and also meet the extra AND condition. | CReturns every Red house student, plus only the Year 7 students from Blue house. | |
| 4Brackets round an AND mean those two conditions are checked together, so a record can also qualify by meeting the single condition on the other side of the OR. | DThe brackets are missing. AND is worked out before OR, so all Year 7 students are returned whatever their house. The query should be WHERE (yearLevel = 7 OR yearLevel = 8) AND house = 'Red'. | |
| 5WHERE yearLevel = 7 OR yearLevel = 8 AND house = 'Red' | EWHERE points > 100 OR (house = 'Red' AND yearLevel = 7) | |
| 6WHERE (house = 'Red' AND house = 'Blue') AND yearLevel = 7 | FReturns only Year 7 students who are in Red house or Blue house. |
C
Definitions
WHERE (A OR B) AND C
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT firstName FROM students
WHERE (house = 'Red' OR house = 'Blue')
___ yearLevel = 7;
2.
SELECT firstName FROM students
WHERE (house = 'Red' __ house = 'Blue')
AND yearLevel = 7;
3.
SELECT firstName FROM students
WHERE house = 'Red'
OR (house = 'Blue' ___ yearLevel = 8);
4.
SELECT name FROM products
WHERE (category = 'Tech' OR category =
'Sport')
AND _____ > 0;
5.
SELECT firstName FROM students
WHERE (yearLevel = 7 AND house = 'Red')
__ points > 100;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT firstName FROM students
B
WHERE house = 'Red'
C
OR house = 'Blue')
D
AND points > 100;
Question 2
A
SELECT name FROM products
B
WHERE category = 'Tech';
C
AND (price < 20
D
OR stock > 50);
Question 3
A
SELECT name products
B
WHERE (category = 'Tech'
C
OR category = 'Sport')
D
AND stock > 0;
Question 4
A
SELECT firstName FROM students
B
WHERE [yearLevel = 7
C
OR yearLevel = 8)
D
AND house = 'Red';
Question 5
A
SELECT name, price,
B
FROM products
C
WHERE category = 'Tech'
D
OR (category = 'Sport'
E
AND price < 10);
Answer Key
Mixing AND and OR with brackets in a WHERE clause
Section A: Quick Questions
Q1
A — WHERE (house = 'Red' OR house = 'Blue') AND yearLevel = 7
Q2
B — The OR inside the brackets
Q3
A — No, because C must also be true
Q4
A — Yes, because A alone is enough
Q5
B — No, moving the brackets can change the records returned
Section B: Match the Pairs
Q1
WHERE (house = 'Red' OR house = 'Blue') AND yearLevel = 7 → Returns only Year 7 students who are in Red house or Blue house.
Q2
WHERE house = 'Red' OR (house = 'Blue' AND yearLevel = 7) → Returns every Red house student, plus only the Year 7 students from Blue house.
Q3
Brackets round an OR on the same field mean a record must match one of those two values and also meet the extra AND condition. → WHERE (yearLevel = 7 OR yearLevel = 8) AND points > 100
Q4
Brackets round an AND mean those two conditions are checked together, so a record can also qualify by meeting the single condition on the other side of the OR. → WHERE points > 100 OR (house = 'Red' AND yearLevel = 7)
Q5
WHERE yearLevel = 7 OR yearLevel = 8 AND house = 'Red' → The brackets are missing. AND is worked out before OR, so all Year 7 students are returned whatever their house. The query should be WHERE (yearLevel = 7 OR yearLevel = 8) AND house = 'Red'.
Q6
WHERE (house = 'Red' AND house = 'Blue') AND yearLevel = 7 → AND is used between two values of the same field. A record cannot be in two houses at once, so no rows are returned. It should be (house = 'Red' OR house = 'Blue').
Section C: Definitions
Q1
WHERE (A OR B) AND C — Brackets make SQL work out the OR first, then the AND. A record must meet C and also A or B. Moving the brackets, as in A OR (B AND C), can return different records.Section D: Fill in the Blanks
Q1
SELECT firstName FROM students
WHERE (house = 'Red' OR house = 'Blue')
AND yearLevel = 7;Q2
SELECT firstName FROM students
WHERE (house = 'Red' OR house = 'Blue')
AND yearLevel = 7;Q3
SELECT firstName FROM students
WHERE house = 'Red'
OR (house = 'Blue' AND yearLevel = 8);Q4
SELECT name FROM products
WHERE (category = 'Tech' OR category =
'Sport')
AND stock > 0;Q5
SELECT firstName FROM students
WHERE (yearLevel = 7 AND house = 'Red')
OR points > 100;Section E: Spot the Error
Q1
B — The opening bracket is missing, so the closing bracket on line 3 has no partner. It should be WHERE (house = 'Red'. The brackets make the OR work first, then the AND.
Q2
B — The semicolon ends the statement too early, so the AND (...) part is never read. The semicolon belongs only at the very end of the whole WHERE clause.
Q3
A — The FROM keyword is missing, so SQL does not know which table to use. It should be SELECT name FROM products.
Q4
B — SQL brackets must be round ( ), not square [ ]. The line should be WHERE (yearLevel = 7 so the OR on the same field is worked out first.
Q5
A — There is an extra comma after the last field, so SQL expects another field before FROM. It should be SELECT name, price with no trailing comma.