18:42

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

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

Get information on computers with processor speed not less than 500 MHz and price below $800:

Console

Execute

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
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

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

Console

Execute

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
3	1434	y	Jet	290

Suggested exercises: 108

comparison predicates NUMERIC DATETIME CHARACTER STRING INTERVAL

Previous [Predicates I]

[BETWEEN predicate] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
several days ago

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Predicates I

Comparison predicates