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,
These functions return a single value after performing calculations on a group of values. Following are some of the frequently used Aggregrate functions.
AVG()
FunctionAverage returns average value after calculating it from values in a numeric column.
Its general syntax is,
SELECT AVG(column_name) FROM table_name
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 9000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 6000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 8000 |
SQL query to find average salary will be,
SELECT avg(salary) from Emp;
Result of the above query will be,
avg(salary) |
---|
8200 |
COUNT()
FunctionCount 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
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 9000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 6000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 8000 |
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 |
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 9000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 6000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 8000 |
SQL query is,
SELECT COUNT(DISTINCT salary) FROM emp;
Result of the above query will be,
count(distinct salary) |
---|
4 |
FIRST()
FunctionFirst function returns first value of a selected column
Syntax for FIRST function is,
SELECT FIRST(column_name) FROM table-name;
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 9000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 6000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 8000 |
SQL query will be,
SELECT FIRST(salary) FROM Emp;
and the result will be,
first(salary) |
---|
9000 |
LAST()
FunctionLAST function returns the return last value of the selected column.
Syntax of LAST function is,
SELECT LAST(column_name) FROM table-name;
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 9000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 6000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 8000 |
SQL query will be,
SELECT LAST(salary) FROM emp;
Result of the above query will be,
last(salary) |
---|
8000 |
MAX()
FunctionMAX function returns maximum value from selected column of the table.
Syntax of MAX function is,
SELECT MAX(column_name) from table-name;
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 9000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 6000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 8000 |
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()
FunctionMIN function returns minimum value from a selected column of the table.
Syntax for MIN function is,
SELECT MIN(column_name) from table-name;
Consider the following Emp table,
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 9000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 6000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 8000 |
SQL query to find minimum salary is,
SELECT MIN(salary) FROM emp;
Result will be,
MIN(salary) |
---|
6000 |
SUM()
FunctionSUM function returns total sum of a selected columns numeric values.
Syntax for SUM is,
SELECT SUM(column_name) from table-name;
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 9000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 6000 |
404 | Scott | 44 | 10000 |
405 | Tiger | 35 | 8000 |
SQL query to find sum of salaries will be,
SELECT SUM(salary) FROM emp;
Result of above query is,
SUM(salary) |
---|
41000 |
Scalar functions return a single value from an input value. Following are some frequently used Scalar Functions in SQL.
UCASE()
FunctionUCASE function is used to convert value of string column to Uppercase characters.
Syntax of UCASE,
SELECT UCASE(column_name) from table-name;
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | anu | 22 | 9000 |
402 | shane | 29 | 8000 |
403 | rohan | 34 | 6000 |
404 | scott | 44 | 10000 |
405 | Tiger | 35 | 8000 |
SQL query for using UCASE is,
SELECT UCASE(name) FROM emp;
Result is,
UCASE(name) |
---|
ANU |
SHANE |
ROHAN |
SCOTT |
TIGER |
LCASE()
FunctionLCASE function is used to convert value of string columns to Lowecase characters.
Syntax for LCASE is,
SELECT LCASE(column_name) FROM table-name;
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | Anu | 22 | 9000 |
402 | Shane | 29 | 8000 |
403 | Rohan | 34 | 6000 |
404 | SCOTT | 44 | 10000 |
405 | Tiger | 35 | 8000 |
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()
FunctionMID 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;
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | anu | 22 | 9000 |
402 | shane | 29 | 8000 |
403 | rohan | 34 | 6000 |
404 | scott | 44 | 10000 |
405 | Tiger | 35 | 8000 |
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()
FunctionROUND 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;
Consider the following Emp table
eid | name | age | salary |
---|---|---|---|
401 | anu | 22 | 9000.67 |
402 | shane | 29 | 8000.98 |
403 | rohan | 34 | 6000.45 |
404 | scott | 44 | 10000 |
405 | Tiger | 35 | 8000.01 |
SQL query is,
SELECT ROUND(salary) from emp;
Result will be,
ROUND(salary) |
---|
9001 |
8001 |
6000 |
10000 |
8000 |