← VCE Spreadsheets, SQL & Visualisation

Combining query criteria with AND and OR

AND needs both conditions to be true, like Year = 7 AND Score > 80. OR needs at least one to be true, like Pet = "Cat" OR Pet = "Dog". They let a query match several conditions at once.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 51, 7-8 Digital Technologies: Analysing and visualising data — Module 12, IGCSE Computer Science 0478 — 9 Databases — Module 25, ACARA Digital Technologies Years 7–8 — Structured Data and Queries — Module 32

Understanding Combining query criteria with AND and OR in VCE Spreadsheets, SQL & Visualisation

What it does: AND makes both the Year and Score conditions have to be true for a record to show. Each row of criteria in query design is a separate option, so a record matching either row is shown. This works like OR. OR needs at least one condition to be true. A student with a Dog matches the second condition, so they are shown. Criteria on the same row are joined with AND. Criteria on different rows are joined with OR. AND needs both to be true, which is impossible for one field value. OR should be used instead.

Common mistakes: The fields in a SELECT list must be separated by a comma. It should be SELECT Name, Score. Text values in query criteria need quote marks. It should be WHERE Pet = "Cat".

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 Combining query criteria with AND and OR to a class →

This module's key terms are also in a printable crossword (Modules 45–54) and word search (Modules 35–54).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which query finds students who are in Year 7 AND scored over 80?

Year = 7 OR Score > 80Year = 7 AND Score > 80

Matches

Pet = "Cat" AND Pet = "Dog"

↓

A pet cannot be a cat and a dog at the same time, so no records would match. Use OR to get cats or dogs.

Applying

Meanings

Term

AND / OR

Meaning

AND needs both conditions to be true, like Year = 7 AND Score > 80. OR needs at least one to be true, like Pet = "Cat" OR Pet = "Dog". They let a query match several conditions at once.

Practice

Year = 7 AND Score > 80

Testing

Gaps

Year = 7 Score > 80

Check Answer

Spots

1SELECT Name Score

2FROM Students

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 query finds students who are in Year 7 AND scored over 80?

A. Year = 7 OR Score > 80

B. Year = 7 AND Score > 80 ✓

AND needs both conditions to be true. OR would also show Year 8 students who scored 50 in Year 7 or any student with a high score.

2. A query uses: Pet = "Cat" OR Pet = "Dog". Which student is shown?

A. A student whose pet is a Dog ✓

B. Only a student who has both a Cat and a Dog

OR needs at least one condition to be true. A student with a Dog matches the second condition, so they are shown.

3. True or false: Year = 8 AND Score > 90 shows a Year 7 student who scored 95.

A. False ✓

B. True

AND needs both conditions to be true. The student scored over 90 but is not in Year 8, so the record is left out.

4. You want to see students whose pet is a Cat or a Hamster. Which criteria should you use?

A. Pet = "Cat" OR Pet = "Hamster" ✓

B. Pet = "Cat" AND Pet = "Hamster"

One pet field can only hold one value in a record, so it can't be both a Cat and a Hamster. AND would show no records. OR shows either.

5. In query design, where do you type criteria so they are joined with AND?

A. On different rows, under different fields

B. On the same row, under different fields ✓

Criteria on the same row are joined with AND. Criteria on different rows are joined with OR.

Match the Pairs

Year = 7 AND Score > 80 → Shows only students who are in Year 7 and also scored over 80. Both conditions must be true.

Pet = "Cat" OR Pet = "Dog" → Shows records where the pet is a cat or a dog. Only one condition needs to be true.

To find Year 8 students who also scored 50 or less, both conditions must be true → Year = 8 AND Score <= 50

In query design, criteria typed on different rows are joined with OR → Row 1: "Cat" Row 2: "Dog" (under Pet)

Pet = "Cat" AND Pet = "Dog" → A pet cannot be a cat and a dog at the same time, so no records would match. Use OR to get cats or dogs.

Year = 7 OR Score > 80 → OR shows all Year 7 students and also everyone who scored over 80 in any year. To get only Year 7 students with high scores, use AND.

Key Term

AND / OR

AND needs both conditions to be true, like Year = 7 AND Score > 80. OR needs at least one to be true, like Pet = "Cat" OR Pet = "Dog". They let a query match several conditions at once.

Fill in the Blanks

1. Year = 7 AND Score > 80

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

2. Pet = "Cat" OR Pet = "Dog"

Hint: Type the keyword that matches when at least one condition is true.

3. Year = 7 AND Score > 80

Hint: Type the comparison operator that means greater than.

4. Criteria: Pet = "Cat" or: Pet = "Dog"

Hint: Type the label of the extra row in query design that gives a different option.

5. Pet = "Cat" OR Pet = "Dog"

Hint: Type the field name that is tested again in the second condition.

Spot the Error

Question 1

A SELECT Name Score

B FROM Students

C WHERE Year = 7 AND Score > 80

Line A — The fields in a SELECT list must be separated by a comma. It should be SELECT Name, Score.

Question 2

A SELECT * FROM Students

B WHERE Pet = Cat

C OR Pet = "Dog"

Line B — Text values in query criteria need quote marks. It should be WHERE Pet = "Cat".

Question 3

A SELECT * FROM Students

B WHERE Year = 7 Score > 80

C ORDER BY Score DESC

Line B — Two conditions must be joined with AND or OR. The query needs AND between them: WHERE Year = 7 AND Score > 80.

Question 4

A SELECT Name, Pet

B FROM Students

C WHERE Pet="Cat" OR "Dog"

Line C — Each side of OR needs a full condition. "Dog" on its own is not a condition. It should be Pet="Dog".

Question 5

A Year = 7 OR Score > 80

B Row A: Year 7, Score 85

C Row B: Year 8, Score 90

D Only Row A matches

Line A — With OR, Row B would match because its score is over 80. Only Row A matches if both conditions must be true, so it should be AND.

Student with a class code? Join your class →