SQL Functions

SQL provides many built-in functions to perform operations on data. These functions are useful while performing Mathematical calculations, string concatenations, sub-strings etc.

SQL functions are divided into two catagories,

1. Aggregrate Functions
2. Scalar Functions

Aggregrate Functions:

These functions return a single value after calculating from a group of values. Following are some frequently used Aggregrate functions.

AVG()

Average returns average value after calculating from values in a numeric column. Its general Syntax is,

SELECT AVG(column_name) from table_name

Example using AVG()

COUNT()

Count returns the number of rows present in the table either based on some condition or without condition. Its general Syntax is,

SELECT COUNT(column_name) from table-name

Example using COUNT()

FIRST()

First function returns first value of a selected column. Syntax for FIRST function is,

SELECT FIRST(column_name) from table-name

Example of FIRST()

LAST()

LAST return the return last value from selected column. Syntax of LAST function is,

SELECT LAST(column_name) from table-name Example of LAST()

MAX()

MAX function returns maximum value from selected column of the table. Syntax of MAX function is,

SELECT MAX(column_name) from table-name

Example of MAX()

MIN()

MIN function returns minimum value from a selected column of the table. Syntax for MIN function is,

SELECT MIN(column_name) from table-name

Example of MIN()

SUM()

SUM function returns total sum of a selected columns numeric values. Syntax for SUM is,

SELECT SUM(column_name) from table-name

Example of SUM()

Scalar Functions:

Scalar functions return a single value from an input value. Following are some frequently used Scalar Functions.

UCASE()

UCASE function is used to convert value of string column to Uppercase character.

Syntax of UCASE,

SELECT UCASE(column_name) from table-name

Example of UCASE()

LCASE()

LCASE function is used to convert value of string column to Lowecase character.

Syntax for LCASE is,

SELECT LCASE(column_name) from table-name

Example of LCASE()

MID()

MID function is used to extract substrings from column values of string type in a table.

Syntax for MID function is,

SELECT MID(column_name, start, length) from table-name

ROUND()

ROUND function is used to round a numeric field to number of nearest integer. It is used on Decimal point values.

Syntax of Round function is

SELECT ROUND(column_name, decimals) from table-name