New Tutorials:

# What are 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 categories,

1. Aggregate Functions
2. Scalar Functions

## Aggregate Functions

These functions return a single value after performing calculations on a group of values. Following are some of the frequently used Aggregrate functions.

### `AVG()` Function

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

Its general syntax is,

``SELECT AVG(column_name) FROM table_name``

#### Using AVG() function

Consider the following Emp table

eidnameagesalary
401Anu229000
402Shane298000
403Rohan346000
404Scott4410000
405Tiger358000

SQL query to find average salary will be,

``SELECT avg(salary) from Emp;``

Result of the above query will be,

avg(salary)
8200

### `COUNT()` Function

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``

#### Using COUNT() function

Consider the following Emp table

eidnameagesalary
401Anu229000
402Shane298000
403Rohan346000
404Scott4410000
405Tiger358000

SQL query to count employees, satisfying specified condition is,

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

Result of the above query will be,

count(name)
2

#### Example of COUNT(distinct)

Consider the following Emp table

eidnameagesalary
401Anu229000
402Shane298000
403Rohan346000
404Scott4410000
405Tiger358000

SQL query is,

``SELECT COUNT(DISTINCT salary) FROM emp;``

Result of the above query will be,

count(distinct salary)
4

### `FIRST()` Function

First function returns first value of a selected column

Syntax for FIRST function is,

``SELECT FIRST(column_name) FROM table-name;``

#### Using FIRST() function

Consider the following Emp table

eidnameagesalary
401Anu229000
402Shane298000
403Rohan346000
404Scott4410000
405Tiger358000

SQL query will be,

``SELECT FIRST(salary) FROM Emp;``

and the result will be,

first(salary)
9000

### `LAST()` Function

LAST function returns the return last value of the selected column.

Syntax of LAST function is,

``SELECT LAST(column_name) FROM table-name;``

#### Using LAST() function

Consider the following Emp table

eidnameagesalary
401Anu229000
402Shane298000
403Rohan346000
404Scott4410000
405Tiger358000

SQL query will be,

``SELECT LAST(salary) FROM emp;``

Result of the above query will be,

last(salary)
8000

### `MAX()` Function

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

Syntax of MAX function is,

``SELECT MAX(column_name) from table-name;``

#### Using MAX() function

Consider the following Emp table

eidnameagesalary
401Anu229000
402Shane298000
403Rohan346000
404Scott4410000
405Tiger358000

SQL query to find the Maximum salary will be,

``SELECT MAX(salary) FROM emp;``

Result of the above query will be,

MAX(salary)
10000

### `MIN()` Function

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

Syntax for MIN function is,

``SELECT MIN(column_name) from table-name;``

#### Using MIN() function

Consider the following Emp table,

eidnameagesalary
401Anu229000
402Shane298000
403Rohan346000
404Scott4410000
405Tiger358000

SQL query to find minimum salary is,

``SELECT MIN(salary) FROM emp;``

Result will be,

MIN(salary)
6000

### `SUM()` Function

SUM function returns total sum of a selected columns numeric values.

Syntax for SUM is,

``SELECT SUM(column_name) from table-name;``

#### Using SUM() function

Consider the following Emp table

eidnameagesalary
401Anu229000
402Shane298000
403Rohan346000
404Scott4410000
405Tiger358000

SQL query to find sum of salaries will be,

``SELECT SUM(salary) FROM emp;``

Result of above query is,

SUM(salary)
41000

## Scalar Functions

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

### `UCASE()` Function

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

Syntax of UCASE,

``SELECT UCASE(column_name) from table-name;``

#### Using UCASE() function

Consider the following Emp table

eidnameagesalary
401anu229000
402shane298000
403rohan346000
404scott4410000
405Tiger358000

SQL query for using UCASE is,

``SELECT UCASE(name) FROM emp;``

Result is,

UCASE(name)
ANU
SHANE
ROHAN
SCOTT
TIGER

### `LCASE()` Function

LCASE function is used to convert value of string columns to Lowecase characters.

Syntax for LCASE is,

``SELECT LCASE(column_name) FROM table-name;``

#### Using LCASE() function

Consider the following Emp table

eidnameagesalary
401Anu229000
402Shane298000
403Rohan346000
404SCOTT4410000
405Tiger358000

SQL query for converting string value to Lower case is,

``SELECT LCASE(name) FROM emp;``

Result will be,

LCASE(name)
anu
shane
rohan
scott
tiger

### `MID()` Function

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;``

#### Using MID() function

Consider the following Emp table

eidnameagesalary
401anu229000
402shane298000
403rohan346000
404scott4410000
405Tiger358000

SQL query will be,

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

Result will come out to be,

MID(name,2,2)
nu
ha
oh
co
ig

### `ROUND()` Function

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;``

#### Using ROUND() function

Consider the following Emp table

eidnameagesalary
401anu229000.67
402shane298000.98
403rohan346000.45
404scott4410000
405Tiger358000.01

SQL query is,

``SELECT ROUND(salary) from emp;``

Result will be,

ROUND(salary)
9001
8001
6000
10000
8000