Boolean operators AND, OR and NOT 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 students in year 7 who are also in the Red house?
2. Which WHERE clause returns products that are in the Tech category or the Sport category?
3. True or false: in a WHERE clause, a row is returned by an OR condition only when BOTH conditions are true.
4. Which query returns all products that are NOT in the Stationery category?
5. Which WHERE clause correctly returns Red or Blue house students who have more than 100 points?
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' AND points > 100 | AReturns students who are in Year 7 or in Year 8; only one condition needs to be true. | |
| 2WHERE yearLevel = 7 OR yearLevel = 8 | BReturns only students who are in Red house and also have more than 100 points; both conditions must be true. | |
| 3NOT reverses a condition, so this query returns every student who is not in Blue house | CWithout brackets, AND is worked out before OR, so Red students of any points are returned. It should be (house = 'Red' OR house = 'Blue') AND points > 90. | |
| 4Brackets control the order, so the OR is checked first and then the result must also pass the AND test | DWHERE NOT house = 'Blue' | |
| 5WHERE house = 'Red' AND house = 'Blue' | EA student has only one house, so both conditions can never be true at once and no rows are returned. It should use OR instead of AND. | |
| 6WHERE house = 'Red' OR house = 'Blue' AND points > 90 | FWHERE (house = 'Red' OR house = 'Blue') AND points > 90 |
C
Definitions
AND, OR, NOT
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT firstName, house FROM students
WHERE yearLevel = 7 ___ house = 'Red';
2.
SELECT name, category FROM products
WHERE category = 'Tech' __
category = 'Sport';
3.
SELECT firstName, house FROM students
WHERE ___ house = 'Red';
4.
SELECT firstName FROM students
WHERE (house = 'Red' __ house = 'Blue')
AND points > 100;
5.
SELECT name FROM products
WHERE category = 'Tech' AND ___ stock = 0;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT name FROM products
B
WHERE price < 30 OR stock > 0
C
-- cheap AND in stock
Question 2
A
SELECT * FROM students WHEN
B
house = 'Red'
C
AND yearLevel = 8;
Question 3
A
SELECT name FROM products
B
WHERE category NOT = 'Tech'
C
ORDER BY name;
Question 4
A
SELECT * FROM orders WHERE AND
B
(quantity=1 OR quantity=3)
C
AND studentId = 1;
Question 5
A
SELECT name, price
B
FROM products
C
WHERE category = 'Tech'
D
AND WHERE price > 20;
Answer Key
Boolean operators AND, OR and NOT in a WHERE clause
Section A: Quick Questions
Q1
B — WHERE yearLevel = 7 AND house = 'Red'
Q2
A — WHERE category = 'Tech' OR category = 'Sport'
Q3
A — False: OR returns a row when at least one condition is true
Q4
B — WHERE NOT category = 'Stationery'
Q5
A — WHERE (house = 'Red' OR house = 'Blue') AND points > 100
Section B: Match the Pairs
Q1
WHERE house = 'Red' AND points > 100 → Returns only students who are in Red house and also have more than 100 points; both conditions must be true.
Q2
WHERE yearLevel = 7 OR yearLevel = 8 → Returns students who are in Year 7 or in Year 8; only one condition needs to be true.
Q3
NOT reverses a condition, so this query returns every student who is not in Blue house → WHERE NOT house = 'Blue'
Q4
Brackets control the order, so the OR is checked first and then the result must also pass the AND test → WHERE (house = 'Red' OR house = 'Blue') AND points > 90
Q5
WHERE house = 'Red' AND house = 'Blue' → A student has only one house, so both conditions can never be true at once and no rows are returned. It should use OR instead of AND.
Q6
WHERE house = 'Red' OR house = 'Blue' AND points > 90 → Without brackets, AND is worked out before OR, so Red students of any points are returned. It should be (house = 'Red' OR house = 'Blue') AND points > 90.
Section C: Definitions
Q1
AND, OR, NOT — Combine WHERE conditions: AND needs both true, OR needs at least one true, NOT reverses a condition. Brackets set the order and decide which rows are returned.Section D: Fill in the Blanks
Q1
SELECT firstName, house FROM students
WHERE yearLevel = 7 AND house = 'Red';Q2
SELECT name, category FROM products
WHERE category = 'Tech' OR
category = 'Sport';Q3
SELECT firstName, house FROM students
WHERE NOT house = 'Red';Q4
SELECT firstName FROM students
WHERE (house = 'Red' OR house = 'Blue')
AND points > 100;Q5
SELECT name FROM products
WHERE category = 'Tech' AND NOT stock = 0;Section E: Spot the Error
Q1
B — The comment says items must be cheap AND in stock. OR returns a row if only one condition is true, so it would include out-of-stock items. It should be WHERE price < 30 AND stock > 0.
Q2
A — SQL conditions must follow the keyword WHERE, not WHEN. The AND on the later line only works once WHERE has started the condition. It should be SELECT * FROM students WHERE.
Q3
B — NOT goes in front of the condition it reverses, not in the middle of it. It should be WHERE NOT category = 'Tech'.
Q4
A — AND joins two conditions, so it cannot come straight after WHERE with nothing before it. The first condition must come first. It should be SELECT * FROM orders WHERE.
Q5
D — WHERE is used only once, at the start of the conditions. AND then adds the next condition by itself. It should be AND price > 20;