← VCE Data Analytics U3O1

Boolean operators AND, OR and NOT in a WHERE clause

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.

Part of: VCE Data Analytics - U3O1 - Data Analytics — Module 25, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 29, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 63, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 30, QCE Digital Solutions Unit 3 — Innovative Digital Solutions — Module 29, WA Computer Science ATAR Year 11 — Data Management — Module 63

Understanding Boolean operators AND, OR and NOT in a WHERE clause in VCE Data Analytics U3O1

What it does: NOT flips the result of house = 'Blue', so only rows from other houses are returned. The brackets make the house OR test happen first, then points > 90 must also be true for every row returned. OR needs at least one condition to be true. A product has only one category, so it can never be both Tech and Sport, and AND would return no rows. NOT goes in front of the condition it reverses. Placing NOT after the = sign is a syntax error. The Calculator passes both tests. The Water Bottle fails both, since its price is under 10 and its stock is 0, so AND rejects it.

Common mistakes: 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. 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.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Boolean operators AND, OR and NOT in a WHERE clause to a class →

This module's key terms are also in a printable crossword (Modules 17–49) and word search (Modules 17–91).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

Which WHERE clause returns only students in year 7 who are also in the Red house?

WHERE yearLevel = 7 AND house = 'Red'WHERE yearLevel = 7 OR house = 'Red'

Matches

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.

Applying

Meanings

Term

AND, OR, NOT

Meaning

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.

Practice

SELECT firstName, house, points

Testing

Gaps

WHERE yearLevel = 7 house = 'Red';

Check Answer

Spots

1SELECT name FROM products

2WHERE price < 30 OR stock > 0

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which WHERE clause returns only students in year 7 who are also in the Red house?

A. WHERE yearLevel = 7 OR house = 'Red'

B. WHERE yearLevel = 7 AND house = 'Red' ✓

AND needs both conditions to be true, so a row must be year 7 and Red. OR would also return year 7 students from other houses and Red students from other year levels.

2. Which WHERE clause returns products that are in the Tech category or the Sport category?

A. WHERE category = 'Tech' OR category = 'Sport' ✓

B. WHERE category = 'Tech' AND category = 'Sport'

OR needs at least one condition to be true. A product has only one category, so it can never be both Tech and Sport, and AND would return no rows.

3. True or false: in a WHERE clause, a row is returned by an OR condition only when BOTH conditions are true.

A. False: OR returns a row when at least one condition is true ✓

B. True: OR needs both conditions to be true

OR needs just one true condition. It is AND that needs both conditions to be true.

4. Which query returns all products that are NOT in the Stationery category?

A. WHERE category = NOT 'Stationery'

B. WHERE NOT category = 'Stationery' ✓

NOT goes in front of the condition it reverses. Placing NOT after the = sign is a syntax error.

5. Which WHERE clause correctly returns Red or Blue house students who have more than 100 points?

A. WHERE (house = 'Red' OR house = 'Blue') AND points > 100 ✓

B. WHERE house = 'Red' OR house = 'Blue' AND points > 100

Brackets control the order. Without them AND is worked out first, so the points test applies only to Blue students and every Red student is returned.

Match the Pairs

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.

WHERE yearLevel = 7 OR yearLevel = 8 → Returns students who are in Year 7 or in Year 8; only one condition needs to be true.

NOT reverses a condition, so this query returns every student who is not in Blue house → WHERE NOT house = 'Blue'

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

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.

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.

Key Term

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.

Fill in the Blanks

1. SELECT firstName, house FROM students WHERE yearLevel = 7 AND house = 'Red';

Hint: Type the operator that needs both conditions to be true.

2. SELECT name, category FROM products WHERE category = 'Tech' OR category = 'Sport';

Hint: Type the operator that returns a row when at least one condition is true.

3. SELECT firstName, house FROM students WHERE NOT house = 'Red';

Hint: Type the operator that reverses a condition so it excludes matching rows.

4. SELECT firstName FROM students WHERE (house = 'Red' OR house = 'Blue') AND points > 100;

Hint: Type the operator inside the brackets that lets a student match either house.

5. SELECT name FROM products WHERE category = 'Tech' AND NOT stock = 0;

Hint: Type the operator that removes products with no stock from the Tech results.

Spot the Error

Question 1

A SELECT name FROM products

B WHERE price < 30 OR stock > 0

C -- cheap AND in stock

Line 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.

Question 2

A SELECT * FROM students WHEN

B house = 'Red'

C AND yearLevel = 8;

Line 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.

Question 3

A SELECT name FROM products

B WHERE category NOT = 'Tech'

C ORDER BY name;

Line B — NOT goes in front of the condition it reverses, not in the middle of it. It should be WHERE NOT category = 'Tech'.

Question 4

A SELECT * FROM orders WHERE AND

B (quantity=1 OR quantity=3)

C AND studentId = 1;

Line 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.

Question 5

A SELECT name, price

B FROM products

C WHERE category = 'Tech'

D AND WHERE price > 20;

Line 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;

Student with a class code? Join your class →