*59*

# How to create functions in SQL?

**SQL** has many **built-in functions** for performing the calculation of data. **SQL** provides **built-in** functions to perform the **operations**. Some useful functions of SQL are performing the **mathematical calculations, string concatenation** and **sub-string** etc.

SQL functions are **divided** into two parts:

- Aggregate Functions
- Scalar Functions

## SQL Aggregate Functions

SQL **Aggregate** functions return a single value which is calculated from the values.

**AVG():**It returns the average value of the column.**COUNT():**It returns the number of rows in the table.**FIRST():**It returns the first value of the column.**LAST():**It returns the last value**MAX():**It returns the largest value of the column.**MIN():**It returns the smallest value of the column.**SUM():**It returns the sum of rows of the table.

## SQL Scalar functions

SQL Scalar functions returns the single value according to the input value.

### Scalar functions:

**UCASE():**It converts the database field to uppercase.**LCASE():**It converts a field to lowercase.**MID():**It extracts characters from the text field.**LEN():**It returns the length of a text field.**ROUND():**It rounds a numeric field to the number of decimals.**NOW():**It returns the current date and time.**FORMAT():**It formats how a field is to be displayed.

### Aggregate Functions

The aggregate functions return a **single value** after performing calculations on the group of values. Some of **Aggregate functions** are explained below.

### AVG Function

**AVG ()** returns the average value of the database after calculating the values in **numeric** column.

**Syntax :**

*Using AVG() function*

Consider the following **Emp** table:

The following SQL calculates the average salary of the employees.

**Result:**

## COUNT() Function

Count returns the number of rows which are present in the database, and either it is based on the condition or without condition.

Its basic **syntax** is,

*Using COUNT() function*

Consider the following **Emp** table:

SQL query to count the number of rows that satisfies the condition.

**Output:**

**Example of COUNT (distinct)**

Consider the following **Emp** table:

The SQL query is:

**Output:**

### FIRST() Function

The function returns the first value of the specified column.

**Syntax:**

**Using FIRST() function**

Consider the following **Emp** table:

The SQL query will be:

**Output:**

### LAST() Function

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

Syntax of the LAST function is:

**Using LAST() function**

Consider the following **Emp** table:

SQL Query is:

**Output:**

### MAX() Function

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

**Syntax:**

Consider the following **Emp** table:

The following SQL query fetch the maximum salary.

**Output:**

## MIN() Function

MIN function returns the minimum value of selected column.

**The syntax** of MIN function:

**Using MIN () function**

Consider the below **Emp** table:

SQL query to find the minimum salary:

**Output:**

## SUM() Function

**SUM ()** function returns the total of the specified columns.

The syntax for **SUM:**

See the following **Emp** table

Sum of salaries are:

**Output:**

## Scalar Functions

Scalar functions return a single value from an input value. Some of the Scalar functions are given below:

### UCASE () Function

**UCASE ()** converts the value of the string column into the **Uppercase (Capital)** characters.

**Syntax**

**Using UCASE() function**

Consider the below **Emp** table:

SQL query of UCASE:

Result:

## LCASE() Function

LCASE() function is used to convert the value of string columns to Lowercase.

**The syntax** for **LCASE:**

### Using LCASE() function

Consider the following **Emp** table

SQL query for converting the string value to **Lowercase:**

**Output:**

## MID() Function

MID() function is used to extract substrings from column values in the table.

The syntax for the MID function is:

**Using MID() function**

Consider the following **Emp** table:

The following SQL query returns the substring start from the second character.

**Output:**

## ROUND() Function

The ROUND() function is used to round a numeric field to a number of the nearest integer. It is used for decimal point.

**Syntax:**

**Using ROUND() function**

Consider the following **Emp** table:

The following SQL query rounds the amount of salary column.

**Output:**