Slide 11.20: The UNION command
Slide 12.1: JDBC (Java Database Connectivity)
Home

The SQL Functions

SQL has many built-in functions for counting and calculations.

The syntax for built-in SQL functions is 
 SELECT function(column) FROM table

There are several basic types and categories of functions in SQL. The basic types of functions are Aggregate Functions
Aggregate functions operate against a collection of values, but return a single value. Some of the aggregate functions are listed below.

Function Description
AVG(column) Returns the average value of a column.
COUNT(column) Returns the number of rows (without a NULL value) of a column.
COUNT(*) Returns the number of selected rows.
COUNT(DISTINCT column) Returns the number of distinct results.
FIRST(column) Returns the value of the first record in the specified field.
LAST(column) Returns the value of the last record in the specified field.
MAX(column) Returns the highest value of a column.
MIN(column) Returns the lowest value of a column.
SUM(column) Returns the total sum of a column.

Scalar Functions
Scalar functions operate against a single value, and return a single value based on the input value. Some of the scalar functions are listed below.

Function Description
UCASE(c) Converts a field to upper case.
LCASE(c) Converts a field to lower case.
LEN(c) Returns the length of a text field.


The following is an SQL test area from w3schools.com where the Customers table may be created by the following command: 
   SQL> create table  Customers (
     2    CustomerID   varchar(16),
     3    CompanyName  varchar(32), 
     4    ContactName  varchar(32),
     5    Address      varchar(64),
     6    City         varchar(32), 
     7    PostalCode   varchar(16), 
     8    Country      varchar(32) );
Note that the Customers table is for read only.

      SQL Execution Results