WHERE on text, Boolean, character and date fields
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which WHERE clause correctly finds students who live in Leeds?
2. Which WHERE clause correctly finds records where Grade is the character A?
3. Which WHERE clause correctly finds members when Member is a Boolean field?
4. Which is the correct way to find records where Member is FALSE?
5. Which WHERE clause correctly matches a Year field that is an integer, with the value 11?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1WHERE Town = 'Leeds' | AThe text value Leeds has no quotes, so it is read as a field name instead of text. It should be 'Leeds'. | |
| 2WHERE Member = TRUE | BTRUE is a Boolean value and must not be in quotes, which turn it into text. It should be Member = TRUE. | |
| 3A single character field such as a grade is compared using the character in quotes | CSelects only records where the Boolean field Member is true, with TRUE written without quotes | |
| 4A date field is compared with a date value written in quotes, using a comparison operator | DWHERE Grade = 'A' | |
| 5WHERE Town = Leeds | ESelects only records where the text field Town holds Leeds, so the text value is written in quotes | |
| 6WHERE Member = 'TRUE' | FWHERE JoinDate > '2024-01-01' |
C
Definitions
WHERE Town = 'Leeds'
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
-- current members only
SELECT Name FROM Club
WHERE Member = ____;
2.
-- students who got grade A
SELECT Name FROM Student
WHERE _____ = 'A';
3.
SELECT * FROM Customer
_____ Town = 'Leeds';
4.
SELECT * FROM Orders
WHERE OrderDate _ #01/01/2024#;
5.
-- members who have not renewed
SELECT Name FROM Club
WHERE Renewed = _____;
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT Name FROM Student
B
WHERE Grade = A
C
ORDER BY Name;
Question 2
A
SELECT Name, Town
B
FROM Customer
C
WHERE Member = TRUE
D
AND Town = Leeds;
Question 3
A
SELECT Name, Member
B
FROM Club
C
WHERE Member = 'TRUE';
Question 4
A
SELECT Name, Fee
B
FROM Club
C
WHERE Member = TRUE
D
AND Fee > '20';
Question 5
A
SELECT Name, Gender
B
FROM Pupil
C
WHERE Gender = F;
Answer Key
WHERE on text, Boolean, character and date fields
Section A: Quick Questions
Q1
A — WHERE Town = 'Leeds'
Q2
A — WHERE Grade = 'A'
Q3
B — WHERE Member = TRUE
Q4
A — WHERE Member = FALSE
Q5
B — WHERE Year = 11
Section B: Match the Pairs
Q1
WHERE Town = 'Leeds' → Selects only records where the text field Town holds Leeds, so the text value is written in quotes
Q2
WHERE Member = TRUE → Selects only records where the Boolean field Member is true, with TRUE written without quotes
Q3
A single character field such as a grade is compared using the character in quotes → WHERE Grade = 'A'
Q4
A date field is compared with a date value written in quotes, using a comparison operator → WHERE JoinDate > '2024-01-01'
Q5
WHERE Town = Leeds → The text value Leeds has no quotes, so it is read as a field name instead of text. It should be 'Leeds'.
Q6
WHERE Member = 'TRUE' → TRUE is a Boolean value and must not be in quotes, which turn it into text. It should be Member = TRUE.
Section C: Definitions
Q1
WHERE Town = 'Leeds' — The value in a WHERE condition must match the field's data type: text and characters go in quotes, numbers do not, and Boolean fields use TRUE or FALSE, e.g. Member = TRUE.Section D: Fill in the Blanks
Q1
-- current members only
SELECT Name FROM Club
WHERE Member = TRUE;Q2
-- students who got grade A
SELECT Name FROM Student
WHERE Grade = 'A';Q3
SELECT * FROM Customer
WHERE Town = 'Leeds';Q4
SELECT * FROM Orders
WHERE OrderDate > #01/01/2024#;Q5
-- members who have not renewed
SELECT Name FROM Club
WHERE Renewed = FALSE;Section E: Spot the Error
Q1
B — Grade is a character field, so the value must be in quotes. Without them, A looks like a field name. It should be WHERE Grade = 'A'.
Q2
D — Town is a text field, so Leeds needs quotes. Without them SQL treats Leeds as a field name. It should be AND Town = 'Leeds';
Q3
C — Member is a Boolean field, so TRUE is written without quotes. Quotes would turn it into a text string. It should be WHERE Member = TRUE;
Q4
D — Fee is a number field, so the value 20 must not be in quotes. Quotes make it text. It should be AND Fee > 20;
Q5
C — Gender holds a single character, and character values need quotes just like text. It should be WHERE Gender = 'F';