Javatpoint Logo
Javatpoint Logo

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:

  1. Aggregate Functions
  2. 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:

How to create functions in SQL

The following SQL calculates the average salary of the employees.

Result:

How to create functions in SQL

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:

How to create functions in SQL

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

Output:

How to create functions in SQL

Example of COUNT (distinct)

Consider the following Emp table:

How to create functions in SQL

The SQL query is:

Output:

How to create functions in SQL

FIRST() Function

The function returns the first value of the specified column.

Syntax:

Using FIRST() function

Consider the following Emp table:

How to create functions in SQL

The SQL query will be:

Output:

How to create functions in SQL

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:

How to create functions in SQL

SQL Query is:

Output:

How to create functions in SQL

MAX() Function

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

Syntax:

Consider the following Emp table:

How to create functions in SQL

The following SQL query fetch the maximum salary.

Output:

How to create functions in SQL

MIN() Function

MIN function returns the minimum value of selected column.

The syntax of MIN function:

Using MIN () function

Consider the below Emp table:

How to create functions in SQL

SQL query to find the minimum salary:

Output:

How to create functions in SQL

SUM() Function

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

The syntax for SUM:

See the following Emp table

How to create functions in SQL

Sum of salaries are:

Output:

How to create functions in SQL

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:

How to create functions in SQL

SQL query of UCASE:

Result:

How to create functions in SQL

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

How to create functions in SQL

SQL query for converting the string value to Lowercase:

Output:

How to create functions in SQL

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:

How to create functions in SQL

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

Output:

How to create functions in SQL

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:

How to create functions in SQL

The following SQL query rounds the amount of salary column.

Output:

How to create functions in SQL




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA