Don't let the Lockdown slow you Down - Enroll Now and Get 3 Course at 24,999/- Only. Explore Now!

General

SQL Functions

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
1vivek9019
2ravi5022
3thanmayee8024
4rithesh9529
5aksharaa8528

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
Scroll Up
Besant Technologies WhatsApp