SQL Functions

In this, we perform several Operations on data SQL which has many built-in functions. These are categorized into two types mainly. They are :

• Aggregate Functions
• Scalar Functions

Aggregate Functions

In this, the functions used to do operations from the value of a column and single are returned.

• SUM()
• FIRST()
• LAST()
• MIN()
• MAX()

Explanation

Firstly we need to create a student table then we can perform aggregate functions.

Id	name	MARKS	AGE
1	vivek	90	19
2	ravi	50	22
3	thanmayee	80	24
4	rithesh	95	29
5	aksharaa	85	28

SUM()

The SUM() function will return the sum of all values of the selected column.

Syntax:

SELECT SUM(column_name) FROM table_name;

 Fetching summation of total marks among students from the Students table.

SELECT SUM(MARKS) AS TotalMArks FROM Students;

Output:

 400

 Note: Similarly the sum can be executed for each column

FIRST ()

In this, the FIRST() function will return the first value of the selected column.

Syntax:

SELECT FIRST(column_name) FROM table_name;

Fetching the marks of the first student from the Students table.

SELECT FIRST(MARKS) AS MarksFirst FROM Students;

Output:

MARKSFIRST 90

LAST ()

In this, The LAST() function will return the last value of the selected column. It can be used only in Microsoft Access.

Syntax:

 SELECT  LAST(column_name) FROM table_name;

 Fetching  the marks of the last student from the Students table

SELECT LAST(MARKS) AS Markslast FROM Students;

Output:

MARKSLAST 82

MIN ()

In this, the MIN() function will return the minimum value of the selected column.

Syntax:

SELECT MIN(column_name) FROM table_name;

Fetching the minimum marks among students from the Students table.

SELECT MIN(MARKS) AS MinMarks FROM Students;

Output:

MinMarks  80

MAX ()

In this, the MAX() function will return the maximum value of the selected column.

Syntax:

SELECT MAX(column_name) FROM table_name;

Fetching the maximum marks among students from the Students table.

SELECT MAX(MARKS) AS MaxMarks FROM Students;

Output:

MaxMarks  95

Scalar Functions

These functions are based on the user input, these to return a single value from the function. They are categorized into several categories.

• UCASE()
• LCASE()
• MID()
• LEN()
• ROUND()

UCASE ()

Here, UCASE will convert the value of field into upper case.

Syntax:

SELECT UCASE(NAME) FROM table_name;

Converting the names of students from the table Students to uppercase.

SELECT UCASE(NAME) FROM Students;

OutPut

NAME
VIVEK
RAVI
THANMAYEE
RITHESH
AKSHARAA

LCASE ()

Here, LCASE will convert the value of the field into a Lower case.

Syntax

SELECT LCASE(NAME) FROM table_name;

Converting the names of students from the table Students to lowercase.

SELECT LCASE(NAME) FROM Students;

Output:

name
vivek
ravi
thanmayee
rithesh
aksharaa

MID ( )

Here, the mid function will extract the text from the text field.

SYNTAX: SELECT MID(column_name,start,length) AS some_name FROM table_name;

Fetching the first four characters of names of the students from the Students table.

SELECT MID(NAME,1,4) FROM Students;

Output:

NAME
VIVE
RAVI
THANM
RITH
AKSH

LEN ()

Here, The LEN function will return the length of value in a text field.

Syntax:

SELECT LENGTH(column_name) FROM table_name;

Fetching the length of names of the students from the Students table.

SELECT LENGTH(Name) FROM Students;

Output:

Name
5
4
9
8
8