SQL Functions: How to write a Function in SQL?

Structured Query Language aka SQL is used to handle data in databases. It provides various in-built functions and commands to access and manage databases according to our requirements. In this article on SQL Functions, I will discuss the various in-built functions to perform different types of calculation on the data.

The following topics will be covered in this article:

• What are SQL functions?
• Aggregate Functions
  1. SUM()
  2. COUNT() 
  3. AVG() 
  4. MIN()
  5. MAX()
  6. FIRST()
  7. LAST() 
• Scalar Functions

1. 1. LCASE()
   2. UCASE() 
   3. LEN()
   4. MID() 
   5. ROUND() 
   6. NOW()
   7. FORMAT()

Before we delve into the different types of functions offered by SQL, let us understand what are functions.

What are functions?

Functions are methods used to perform data operations. SQL has many in-built functions used to perform string concatenations, mathematical calculations etc.

SQL functions are categorized into the following two categories:

1. Aggregate Functions
2. Scalar Functions

Let us look into each one of them, one by one.

Aggregate SQL Functions

The Aggregate Functions in SQL perform calculations on a group of values and then return a single value. Following are a few of the most commonly used Aggregate Functions:

Let us look into each one of the above functions in depth. For your better understanding, I will be considering the following table to explain to you all the examples.

SUM()

Used to return a total sum of numeric column which you choose.

Syntax:

SELECT SUM(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the sum of marks of all students from the Students table.

SELECT SUM(Marks)
FROM Students;

Output:

359

COUNT() 

Returns the number of rows present in the table either based on some condition or without any condition.

Syntax:

SELECT COUNT(ColumnName)
FROM TableName
WHERE Condition;

Example:

Write a query to count the number of students from the Students table.

SELECT COUNT(StudentID)
FROM Students;

Output:

5

Example:

Write a query to count the number of students scoring marks > 75 from the Students table.

SELECT COUNT(StudentID)
FROM Students
WHERE Marks >75;

Output:

2

 AVG() 

This function is used to return the average value of a numeric column.

Syntax:

SELECT AVG(ColumnName)
FROM TableName;

Example:

Write a query to calculate the average marks of all students from the Students table.

SELECT AVG(Marks)
FROM Students;

Output:

71.8

MIN()

Used to return the minimum value of a numeric column.

Syntax:

SELECT MIN(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the minimum marks out of all students from the Students table.

SELECT MIN(Marks)
FROM Students;

Output:

45

MAX()

Returns the maximum value of a numeric column.

Syntax:

SELECT MAX(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the maximum marks out of all students from the Students table.

SELECT MAX(Marks)
FROM Students;

Output:

92

FIRST()

This function returns the first value of the column which you choose.

Syntax:

SELECT FIRST(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the marks of the first student.

SELECT FIRST(Marks)
FROM Students;

Output:

64

LAST() 

Used to return the last value of the column which you choose.

Syntax:

SELECT LAST(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the marks of the last student.

SELECT LAST(Marks)
FROM Students;

Output: 92

Well, with that we come to an end to SQL Aggregate Functions. Next in this article on SQL Functions, let us understand the various Scalar Functions.

Scalar SQL Functions

The Scalar Functions in SQL are used to return a single value from the given input value.  Following are a few of the most commonly used Aggregate Functions:

 Let us look into each one of the above functions in depth. 

LCASE()

Used to convert values of a string column to lowercase characters.

Syntax:

SELECT LCASE(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the names of all students in lowercase.

SELECT LCASE(StudentName)
FROM Students;

Output:

sanjay
varun
akash
rohit
anjali

UCASE()

Used to convert values of a string column to uppercase characters.

Syntax:

SELECT UCASE(ColumnName)
FROM TableName;

Example:

Write a query to retrieve the names of all students in lowercase.

SELECT UCASE(StudentName)
FROM Students;

Output:

 
SANJAY
VARUN
AKASH
ROHIT
ANJALI

LEN()

Used to retrieve the length of the input string.

Syntax:

SELECT LENGTH(String) AS SampleColumn;

Example:

Write a query to extract the length of the student name “Sanjay”.

SELECT LENGTH(“Sanjay”) AS StudentNameLen;

Output:

6

MID()

This function is used to extract substrings from columns having string data type.

Syntax:

SELECT MID(ColumnName, Start, Length)
FROM TableName;

Example:

Write a query to extract substrings from the StudentName column.

SELECT MID(StudentName, 2, 3)
FROM Students;

Output:

anj
aru
kas
ohi
nja

ROUND()

This function is used to round off a numeric value to the nearest integer.

Syntax:

SELECT ROUND(ColumnName, Decimals)
FROM TableName;

Example:

For this example, let us consider the following Marks table in the Students table.

Write a query to round the marks to the integer value. 

SELECT ROUND(Marks)
FROM Students;

Output:

91
80
54
73
68

NOW()

Used to return the current date and time. The date and time are returned in the “YYYY-MM-DD HH-MM-SS” format.

Syntax:

SELECT NOW();

Example:

Write a query to retrieve the current date and time.

SELECT NOW();

Output:

FORMAT()

This function formats the way a field must be displayed.

Syntax:

FORMAT(InputValue, Format)

Example:

Write a query to display the numbers “123456789” in the format “###-###-###”

SELECT FORMAT(123456789, “###-###-###”);

Output:

123-456-789

With this, we come to an end to this article on SQL Functions. I hope you understood how to use the various types of functions in SQL. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

If you wish to learn MongoDB and build a career in the Database User Roles and Database Administration Roles, then check out our interactive, live-online MongoDB Online Course here, which comes with 24*7 support to guide you throughout your learning period.

Got a question for us? Please mention it in the comments section of ”SQL Functions” and I will get back to you.