We Offer 100% Job Guarantee Courses (Any Degree / Diploma Candidates / Year GAP / Non-IT / Any Passed Outs). Placement Records
Hire Talent (HR):+91-9707 240 250

# SQL Functions

## 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;`

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;`

```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;`

```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;`

```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;`

```Name
5
4
9
8
8
```