**Predicate** is an expression taking truth value. It may be both
a single expression or any combination of a numberless expressions
built by means of Boolean operators **AND**, **OR**, and **NOT**. Besides,
these combinations may inclide SQL-operator **IS**, and parentheses that
define the order in which operations are to be executed.

SQL predicate evaluates to **TRUE**, **FALSE** and **UNKNOWN**.
The following predicates are exeptions to this rule:
**NULL**, **EXISTS**, **UNIQUE**, and
**MATCH**, which could not evaluate to UNKNOWN.

To remember combination rules for these three truth values, let us denote TRUE as 1, FALSE as 0, and UNKNOWN as 1/2 (somewhere between true and false).

- AND with two truth values gives minimum of these values. For example, TRUE AND UNKNOWN is UNKNOWN.
- OR with two truth values gives maximum of these values. For example, FALSE OR UNKNOWN is UNKNOWN.
- Negation of truth value is 1 minus this truth value. For example, NOT UNKNOWN is UNKNOWN.

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 a

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.* Get information on computers with processor speed not less
than 500 MHz and price below $800:

SELECT * FROM Pc WHERE speed >= 500 AND price < 800; |

The query returns the following data:

code |
model |
speed |
ram |
hd |
cd |
price |

1 | 1232 | 500 | 64 | 5 | 12x | 600.0 |

3 | 1233 | 500 | 64 | 5 | 12x | 600.0 |

7 | 1232 | 500 | 32 | 10 | 12x | 400.0 |

10 | 1260 | 500 | 32 | 10 | 12x | 350.0 |

* Example.* Get information on all those printers that
are not matrix and priced below $300:

SELECT * FROM Printer WHERE NOT (type = 'matrix') AND price < 300; |

Here is the result of that query:

code |
model |
color |
type |
price |

2 | 1433 | y | Jet | 270.0 |

3 | 1434 | y | Jet | 290.0 |

**Syntax**

BETWEEN::=

<expression to test> [NOT] BETWEEN

<begin expression> AND <end expression>

The predicate

exp1 BETWEEN exp2 AND exp3

is equal to the predicate

exp1>=exp2 AND exp1<=exp3

And the predicate

exp1 NOT BETWEEN exp2 AND exp3

is equal to the predicate

NOT (exp1 BETWEEN exp2 AND exp3)

If the value of the predicate exp1 BETWEEN exp2 AND exp3 is TRUE, it does not generally
mean that the value of predicate exp1 BETWEEN exp3 AND exp2 is TRUE also, because
the first one may be interpreted as the predicate

exp1>=exp2 AND exp1<=exp3

while the second one may be considered as

exp1>=exp3 AND exp1<=exp2

* Example.* Find model and processor speed of computers priced
between and including $400 through $600:

SELECT model, speed FROM Pc WHERE price BETWEEN 400 AND 600; |

model |
speed |

1232 | 500 |

1233 | 500 |

1232 | 500 |

**Syntax**

IN::=

<expression to test> [NOT] IN (<subquery>)

| (<expression1>,...)

* Example.* Find the model, processor speed and hard drive capacity
for those computers having the hard drive of 10Mb or 20Mb:

SELECT model, speed, hd FROM Pc WHERE hd IN (10, 20); |

model |
speed |
hd |

1233 | 750 | 20 |

1232 | 500 | 10 |

1232 | 450 | 10 |

1260 | 500 | 10 |

* Example.* Find the model, processor speed and hard drive capacity
for those computers having hard drive of 10Mb or 20Mb and produced by
the manufacturer A:

SELECT model, speed, hd FROM Pc WHERE hd IN (10, 20) AND model IN (SELECT model FROM product WHERE maker = 'A'); |

model |
speed |
hd |

1233 | 750 | 20 |

1232 | 500 | 10 |

1232 | 450 | 10 |

Home | SELECT exercises (rating stages) |
DML exercises |
Developers |