Expressions Used in SQL Statements

Relational Data Framework Reference › SQL Statements › Expressions Used in SQL Statements

Expressions Used in SQL Statements

The SQL statements described in this chapter are used to perform various functions related to SQL tables and the data in them. These statements consist of a verb that enacts the main function (for example, SELECT) along with required and optional arguments. The arguments that you provide in these statements can include clauses, predicates, operators, functions, keywords, and data type definitions.

The following table describes the various types of expressions that may be used in these statements. Additional usage information about these expressions is provided in the syntax pages that follow. Provided with each expression shown in the table is an example of its usage.

Expression	Type	Usage	Example
AND	Operator	Adds conditions to a statement.	WHERE COL1 = 'A' AND COL2 = 'B'
AS	Keyword	Creates aliases for table names.	TABLE1 AS T
AVG	Function	Determines the average value of a column.	AVG(COL1)
CHAR	Data Type	Defines a fixed length character-type data column.	ADD COLUMN COL1 CHAR(8)
COUNT	Function	Counts the number of rows in a table.	COUNT (*) FROM TABLE1
DATE	Data Type	Defines a date-type data column.	ADD COLUMN COL2 DATE
DECIMAL	Data Type	Defines a decimal-type data column.	ADD COLUMN COL3 DECIMAL
ESCAPE	Keyword	Causes the next character to be taken literally.	LIKE '10$%' ESCAPE '$'
HEX	Data Type	Defines a hexadecimal-type data column.	ADD COLUMN COL4 HEX(2)
IN	Predicate	Matches groups of data.	WHERE COL1 IN ('A','B')
INTEGER	Data Type	Defines an integer-type data column.	ADD COLUMN COL5 INTEGER
LIKE	Predicate	Matches strings to column values.	WHERE COL1 LIKE 'AB%'
MAX	Function	Determines the maximum value in a column.	MAX (COL1)
MIN	Function	Determines the minimum value in a column.	MIN (COL1)
NOT	Operator	Specifies a negative match.	WHERE NOT COL1 = 'A'
OR	Operator	Allows for one of two statements to be true.	WHERE COL1 = 'A' OR COL2 = 'B'
SMALLINT	Data Type	Defines a half-word integer-type data column.	ADD COLUMN COL6 SMALLINT
SUBSTR	Function	Allows string selection for comparison.	SUBSTR ('abc' FROM 1 FOR 3)
SUM	Function	Determines the sum of all values in a column.	SUM (COL1)
TIME	Data Type	Defines a time-type data column.	ADD COLUMN COL7 TIME
TIMESTAMP	Data Type	Defines a date/time-type data column.	ADD COLUMN COL8 TIMESTAMP
VARCHAR	Data Type	Defines a variable length character-type data column.	ADD COLUMN COL1 VARCHAR(300)
WHERE	Clause	Establishes search criteria for a statement.	WHERE COL1 = 'A'
=,<>,<=,>=,<,>	Operator	Used to compare values.	WHERE COL1 >= 2