Functions
SQL is not a procedural language, but it does provide some
data-transformation capabilities. In addition to the string
concatenation operator (||
), the
SQL-92 specification defines two sets of functions: aggregate and
value.
Aggregate Functions
In the section on the SELECT
statement, we saw an
aggregate function used to count the
number of records within a group. Mainly aggregate functions act on
all the records of query, counting rows, averaging fields, and so
forth. For example, here’s how to count the number of rows returned
by a SELECT
statement:
SELECT COUNT(*) FROM CUSTOMERS
Instead of returning each row of the CUSTOMERS
table, this query returns a
single-column, single-row result that contains the number of records
in CUSTOMERS
.
The other aggregate functions are AVG
, SUM
, MAX
, and MIN
. Unlike COUNT
, which works on either a single
column or all columns, the other functions work on only a single
column. AVG
and SUM
can be applied against numerical data
types only (integers, reals, etc.) while MAX
and MIN
work with any data type. Here are some
examples:
SELECT MIN(AGE) FROM GUESTS SELECT MAX(NAME) FROM GUESTS SELECT AVG(AGE), SUM(AGE) FROM GUESTS
Value Functions
Value functions work on particular column values and return modified data. Some of them also generate values from system information.
Date/time functions
There are three date and time functions that retrieve the current date, current time, and current timestamp, respectively, from the database:
CURRENT_DATE CURRENT_TIME[( ...
Get Java Enterprise in a Nutshell, Third Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.