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 calculating from a group of values.Following are some frequently used Aggregate functions.


1) AVG()

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

Its general Syntax is

SELECT AVG(column_name) from table_name

Example using AVG()

Consider 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 of salary will be

SELECT avg(salary) from Emp;

Result of the above query will be

avg(salary)
8200

2) COUNT()

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

Example using COUNT()

Consider 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

Example of COUNT(distinct)

Consider 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

3) FIRST()

First function returns first value of a selected column

Syntax for FIRST function is

SELECT FIRST(column_name) from table-name

Example of FIRST()

Consider 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

SELECT FIRST(salary) from Emp;

Result will be

first(salary)
9000


4) LAST()

LAST return the return last value from selected column

Syntax of LAST function is

SELECT LAST(column_name) from table-name

Example of LAST()

Consider 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

5) MAX()

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

Syntax of MAX function is

SELECT MAX(column_name) from table-name

Example of MAX()

Consider 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 Maximum salary is

SELECT MAX(salary) from emp;

Result of the above query will be

MAX(salary)
10000

6) MIN()

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

Syntax for MIN function is

SELECT MIN(column_name) from table-name

Example of MIN()

Consider 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)
8000

7) SUM()

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

Syntax for SUM is

SELECT SUM(column_name) from table-name

Example of SUM()

Consider 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

Scalar functions return a single value from an input value. Following are Showing frequently used Scalar Functions.


1) UCASE()

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

Syntax of UCASE

SELECT UCASE(column_name) from table-name

Example of UCASE()

Consider 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

2) LCASE()

LCASE function is used to convert value of string column to Lowecase character.

Syntax for LCASE is

SELECT LCASE(column_name) from table-name

Example of LCASE()

Consider 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

3) MID()

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

Example of MID()

Consider 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

4) ROUND()

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

Example of ROUND()

Consider 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

Admin has written 171 articles

Leave a Reply