Functions

SQL contains built-in functions. Functions can operate each value in a column to produce a new column:

SELECT name, SUBSTR (name, 1,3)
FROM country

The above results in a sub-string formed from the first three characters of the original string.

The keyword CAST converts one data type into another e.g. an integer into a string or vice versa.

Functions from the SQL standard

Function	Result
BIT_LENGTH (string)	number of bits in bit string
CAST	value converted to data type
CHAR_LENGTH (string)	length of character string
CURRENT_DATE	current date
CURRENT_TIME (p)	current time to precision p
CURRENT_TIMESTAMP (p)	current date and time to precision p
EXTRACT (part FROM source)	specified part (DAY, HOUR etc.) from source date or time value
LOWER (string)	string converted to lowercase
POSITION (target IN source)	position where target string appears in source string
SUBSTRING (source FROM n for l)	substring of source starting at position n of length l
UPPER (string)	string converted to uppercase

Aggregate Functions

Aggregate functions aka column or set functions produce tables consisting of a single row of data. Logical processing of the aggregate function below runs as follows:

SELECT AVG (cars)
FROM country;

• The FROM clause produces an intermediate table identical to the country table
• The SELECT clause selects the intermediate table column referred to in the aggregate function (cars)
• The function is applied to that column to produce the final table i.e. a single row

Aggregate functions cannot be used in a SELECT clause containing other kinds of expressions. The following is an invalid query:

SELECT AVG (cars), area             //invalid
FROM country;

Aggregate functions can be combined when used in a SELECT clause. The following is a valid query:

SELECT AVG (cars), AVG (deaths)             //valid
FROM country;

As previously stated - the final table will contain a single row.

COUNT

Results in a count of all non-null values in the column specified. If a DISTINCT keyword is included any duplicate values will be ignored. Where all the specified values are empty or null values, these are ignored giving a zero result.

SUM

When applied to a numeric data type, results in the sum of all non-null values in the collection specified. If a DISTINCT keyword is included any duplicate values will be ignored in the calculation. Where all the specified values are null values or empty, the function returns a null result.

MAX

MAX results in the maximum of non-null values in the collection. When applied to a character string the total will be based on the numeric codes of each character that results in the last entry as found in a dictionary. DISTINCT has no effect on this function. If all values are null or the set is empty then the result is null.

MIN

As for MAX but selects the minimum value.

Comments, suggestions, ideas to
Stuart Banner