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 :
SELECT AVG(column_name) FROM table_name
Using AVG() function
Consider the following Emp table:
The following [SQL] calculates the average salary of the employees.
SELECT avg(salary) from Emp;
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,
SELECT COUNT(column_name) FROM table-name
Using COUNT() function
Consider the following Emp table:
SQL query to count the number of rows that satisfies the condition.
SELECT COUNT(name) FROM Emp WHERE salary = 10000;
Output:
Example of COUNT (distinct)
Consider the following Emp table:
The SQL query is:
SELECT COUNT(DISTINCT salary) FROM emp;
Output:
FIRST() Function
The function returns the first value of the specified column.
Syntax:
SELECT FIRST(column_name) FROM table-name;
Using FIRST() function
Consider the following Emp table:
The SQL query will be:
SELECT FIRST(salary) FROM EMP;
Output:
LAST() Function
The LAST function returns the return last value of the selected column.
Syntax of the LAST function is:
SELECT LAST(column_name) FROM table-name;
Using LAST() function
Consider the following Emp table:
SQL Query is:
SELECT LAST(salary) FROM emp;
Output:
MAX() Function
MAX() function returns the maximum value from the selected column of the table.
Syntax:
SELECT MAX(column_name) from table-name;
Consider the following Emp table:
The following SQL query fetch the maximum salary.
SELECT MAX(salary) FROM emp;
Output:
MIN() Function
MIN function returns the minimum value of selected column.
The syntax of MIN function:
SELECT MIN(column_name) from table-name;
Using MIN () function
Consider the below Emp table:
SQL query to find the minimum salary:
SELECT MIN(salary) FROM emp;
Output:
SUM() Function
SUM () function returns the total of the specified columns.
The syntax for SUM:
SELECT SUM (column_name) from table-name;
See the following Emp table
Sum of salaries are:
SELECT SUM(salary) FROM emp;
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
SELECT UCASE(column_name) from table-name;
Using UCASE() function
Consider the below Emp table:
SQL query of UCASE:
SELECT UCASE(name) FROM emp;
Result:
LCASE() Function
LCASE() function is used to convert the value of string columns to Lowercase.
The syntax for LCASE:
SELECT LCASE(column_name) FROM table-name;
Using LCASE() function
Consider the following Emp table
SQL query for converting the string value to Lowercase:
SELECT LCASE(name) FROM emp;
Output:
MID() Function
MID() function is used to extract substrings from column values in the table.
The syntax for the MID function is:
SELECT MID(column_name, start, length) from table-name;
Using MID() function
Consider the following Emp table:
The following SQL query returns the substring start from the second character.
SELECT MID(name,2,2) FROM emp;
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:
SELECT ROUND(column_name, decimals) from table-name;
Using ROUND() function
Consider the following Emp table:
The following SQL query rounds the amount of salary column.
SELECT ROUND(salary) from emp;
Output: