DBMS SQL function ~ GNIITHELP

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 catagories,

• Aggregrate Functions
• Scalar Functions

---

Aggregrate Functions

These functions return a single value after calculating from a group of values.Following are some frequently used Aggregrate 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)
6000

---

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 soe 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