SQL Functions

Many built-in functions are provided by SQL to perform operations on data. These built-in functions are handy when performing mathematical calculations, string concatenations, sub-strings, and so on. There are two categories of SQL functions

• Aggregate Functions
• Scalar Functions

Aggregate Functions

The aggregate functions return a single value after performing calculations on a group of values. Below are some of the frequently used Aggregrate functions.

AVG() Function

The Average function returns an average value after calculating it from values in a numeric column.

This is how its syntax looks:

SELECT AVG(column_name) FROM table_name

Let’s consider the following Emp table

Also, the SQL query to find the average salary will be,

SELECT avg(salary) from Emp;

This table shows the result of the above query:

COUNT() Function

In this case, the count function returns the number of rows present in the table either based on some condition or without condition.

This is how its syntax looks:

SELECT COUNT(column_name) FROM table-name

Let’s consider the Emp table below:

Also, the SQL query to count employees, that will satisfy the specified condition is given as: 

SELECT COUNT(name) FROM Emp WHERE salary = 8000;

This table shows the result of the above query:

Example of COUNT(distinct)

Let’s consider the Emp table below:

SQL query is,

SELECT COUNT(DISTINCT salary) FROM emp;

This table shows the result of the above query:

FIRST() Function

This function is typically used to return the first value of a selected column

This is how its syntax looks:

SELECT FIRST(column_name) FROM table-name;

Using FIRST() function

Let’s consider the Emp table below:

SQL query will be,

SELECT FIRST(salary) FROM Emp;

This table shows the result of the above query:

LAST() Function

The LAST function is used to return the return last value of the selected column.

This is how its syntax looks:

SELECT LAST(column_name) FROM table-name;

Using LAST() function

Let’s consider the Emp table below:

SQL query will be,

SELECT LAST(salary) FROM emp;

Result of the above query will be,

MAX() Function

The MAX function is used to return the maximum value from the selected column of the table.

This is how its syntax looks:

SELECT MAX(column_name) from table-name;

Using MAX() function

Consider the following Emp table

SQL query to find the Maximum salary will be,

SELECT MAX(salary) FROM emp;

Result of the above query will be,

MIN() Function

The MIN function when used returns the minimum value from a selected column of the table.

This is how its syntax looks:

SELECT MIN(column_name) from table-name;

Using MIN() function

Consider the following Emp table,

SQL query to find minimum salary is,

SELECT MIN(salary) FROM emp;

Result will be,

SUM() Function

The SUM function is typically used to return the total sum of selected columns' numeric values.

This is how its syntax looks:

SELECT SUM(column_name) from table-name;

Using SUM() function

Consider the following Emp table

SQL query to find sum of salaries will be,

SELECT SUM(salary) FROM emp;

Result of above query is,

Scalar Functions

The Scalar functions will return a single value from an input value. Below are some frequently used SQL Scalar Functions.

UCASE() Function

It is used to convert the value of a string column to Uppercase characters.

This is how its syntax looks:

SELECT UCASE(column_name) from table-name;

Using UCASE() function

Consider the following Emp table

SQL query for using UCASE is,

SELECT UCASE(name) FROM emp;

Result is,

LCASE() Function

The LCASE function can be used to convert the value of string columns to Lowercase characters.

This is how its syntax looks:

SELECT LCASE(column_name) FROM table-name;

Using LCASE() function

Consider the following Emp table

SQL query for converting string value to Lower case is,

SELECT LCASE(name) FROM emp;

Result will be,

MID() Function

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

This is how its syntax looks:

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

Using MID() function

Consider the following Emp table

SQL query will be,

SELECT MID(name,2,2) FROM emp;

Result will come out to be,

ROUND() Function

The ROUND function is typically used to round a numeric field to a number of nearest integers and is used on decimal point values.

This is how its syntax looks:

SELECT ROUND(column_name, decimals) from table-name;

Using ROUND() function

Consider the following Emp table

SQL query is,

SELECT ROUND(salary) from emp;

Result will be,