One of the incredibly powerful features of SQL-based databases is the ability to write queries with very specific criteria to return just the information you are looking for. This is how businesses analyse data. The singular of criteria is criterion; to keep things simple I will only use the plural form, criteria.
In this tutorial:
- Criteria
- Comparison operators
- >
- <
- =
- <>
- <=
- >=
- Like
- Not like
- In
- Wildcard
- #Date# & #Time#
- Evaluating text with > & <
- NULL
- Comparison operators
- AND or OR
- Show
- Assessment time
Required knowledge:
1. Criteria
I find it incredibly disheartening to see how many students cannot tell the difference between the greater than symbol and the less than symbol.
1.1 Comparison Operators
>
Greater than
<
Less than
>=
Greater than or equal to
<=
Less than or equal to
=
Equal to
<>
Not equal to.
Some programming languages use !=.
Like
Not like
In
Not in
*
The * (asterisk) is also referred to as the wildcard.
1.2 #Date# & #Time#
Date & time values used as criteria must have a # (hash) symbol inserted immediately before and after the value.
1.3 Evaluating text with > & <
A < B < C
A < a
Software records and calculates everything as strings of 1’s and 0’s. Because all characters are therefore represented in binary representation, they were all each first given a number. As a result, even letters — or their binary representations — can be compared as values with > or < comparison operators. A look at how binary and the ASCII table work will help you understand this concept.
1.4 NULL
“Null, in a database context, is the total absence of a value in a certain field and means that the field value is unknown. Null is not the same as a zero value for a numerical field, text field or space value. Null implies that a database field value has not been stored.” [1]
2. AND or OR
We can add multiple criteria to one field. We can also add criteria to multiple fields at the same time. We can add multiple criteria to multiple fields in one criteria. It gets messy!
3. Show
Why would you include a field in a query if you did not want its values displayed in the resulting recordset? Because you may want to use that field in your criteria.
4. Assessment time
I have seen questions in Cambridge AS-level theory papers where a screenshot of an Access query in Design View is included and the candidate is instructed to describe the data that is being looked for as well as identify the data that will be returned.
References:
- Techopedia. (2017). Null. Available at: https://www.techopedia.com/definition/5539/null (Accessed: 30 November 2023).