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
only on 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, Second 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.