Combining query criteria with AND and OR
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which query finds students who are in Year 7 AND scored over 80?
2. A query uses: Pet = "Cat" OR Pet = "Dog". Which student is shown?
3. True or false: Year = 8 AND Score > 90 shows a Year 7 student who scored 95.
4. You want to see students whose pet is a Cat or a Hamster. Which criteria should you use?
5. In query design, where do you type criteria so they are joined with AND?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1Year = 7 AND Score > 80 | ARow 1: "Cat" Row 2: "Dog" (under Pet) | |
| 2Pet = "Cat" OR Pet = "Dog" | BYear = 8 AND Score <= 50 | |
| 3To find Year 8 students who also scored 50 or less, both conditions must be true | CA pet cannot be a cat and a dog at the same time, so no records would match. Use OR to get cats or dogs. | |
| 4In query design, criteria typed on different rows are joined with OR | DShows records where the pet is a cat or a dog. Only one condition needs to be true. | |
| 5Pet = "Cat" AND Pet = "Dog" | EOR 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. | |
| 6Year = 7 OR Score > 80 | FShows only students who are in Year 7 and also scored over 80. Both conditions must be true. |
C
Definitions
AND / OR
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
Year = 7 ___ Score > 80
2.
Pet = "Cat" __ Pet = "Dog"
3.
Year = 7 AND Score _ 80
4.
Criteria: Pet = "Cat"
__: Pet = "Dog"
5.
Pet = "Cat" OR ___ = "Dog"
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT Name Score
B
FROM Students
C
WHERE Year = 7 AND Score > 80
Question 2
A
SELECT * FROM Students
B
WHERE Pet = Cat
C
OR Pet = "Dog"
Question 3
A
SELECT * FROM Students
B
WHERE Year = 7 Score > 80
C
ORDER BY Score DESC
Question 4
A
SELECT Name, Pet
B
FROM Students
C
WHERE Pet="Cat" OR "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
Answer Key
Combining query criteria with AND and OR
Section A: Quick Questions
Q1
B — Year = 7 AND Score > 80
Q2
A — A student whose pet is a Dog
Q3
A — False
Q4
A — Pet = "Cat" OR Pet = "Hamster"
Q5
B — On the same row, under different fields
Section B: Match the Pairs
Q1
Year = 7 AND Score > 80 → Shows only students who are in Year 7 and also scored over 80. Both conditions must be true.
Q2
Pet = "Cat" OR Pet = "Dog" → Shows records where the pet is a cat or a dog. Only one condition needs to be true.
Q3
To find Year 8 students who also scored 50 or less, both conditions must be true → Year = 8 AND Score <= 50
Q4
In query design, criteria typed on different rows are joined with OR → Row 1: "Cat" Row 2: "Dog" (under Pet)
Q5
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.
Q6
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.
Section C: Definitions
Q1
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.Section D: Fill in the Blanks
Q1
Year = 7 AND Score > 80Q2
Pet = "Cat" OR Pet = "Dog"Q3
Year = 7 AND Score > 80Q4
Criteria: Pet = "Cat"
or: Pet = "Dog"Q5
Pet = "Cat" OR Pet = "Dog"Section E: Spot the Error
Q1
A — The fields in a SELECT list must be separated by a comma. It should be SELECT Name, Score.
Q2
B — Text values in query criteria need quote marks. It should be WHERE Pet = "Cat".
Q3
B — Two conditions must be joined with AND or OR. The query needs AND between them: WHERE Year = 7 AND Score > 80.
Q4
C — Each side of OR needs a full condition. "Dog" on its own is not a condition. It should be Pet="Dog".
Q5
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.