Java Enterprise in a Nutshell, Third Edition

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[( ...