Comparison predicates
Comparison predicate is two expressions separated by a comparison operator. There are six conventional comparison operators: =, >, <, >=, <=, <>.
The data of NUMERIC type (numbers) are compared in accordance with their algebraic values.
The data of CHARACTER STRING type are compared in accordance with their alphabetic sequences. If a1a2…an and b1b2…bn are two character sequences, the first of these is “less” than the second if а1 < b1 or а1 = b1 and а2 < b2 and so on. Also, it is believed to be а1а2…аn < b1b2…bm if n < m and а1а2…аn = b1b2…bn, i.e. if the first string is the prefix of second one. For example, ‘folder’ < ‘for’ because the two first letters of these strings coincide, while the third letter of the string ‘folder’ precedes the third letter in the string ‘for’. Inequality ‘bar’ < ‘barber’ is also correct because its first string is the prefix of the second string.
The data of DATETIME type is compared in a chronological order.
The data of INTERVAL type (time range) are converted into corresponding types and then compared as ordinary numeric values (of NUMERIC type).
Example 5.2.1
SELECT *
FROM PC
WHERE speed >= 500 AND
price < 800;
[[ column ]] |
---|
[[ value ]] |
The query returns the following data:
code | model | speed | ram | hd | cd | price |
---|---|---|---|---|---|---|
1 | 1232 | 500 | 64 | 5 | 12x | 600 |
3 | 1233 | 500 | 64 | 5 | 12x | 600 |
7 | 1232 | 500 | 32 | 10 | 12x | 400 |
10 | 1260 | 500 | 32 | 10 | 12x | 350 |
Example 5.2.2
SELECT *
FROM printer
WHERE NOT (type = 'matrix') AND
price < 300;
[[ column ]] |
---|
[[ value ]] |
Here is the result of that query:
code | model | color | type | price |
---|---|---|---|---|
2 | 1433 | y | Jet | 270 |
3 | 1434 | y | Jet | 290 |
Suggested exercises: 108