Table of contents
1.
Introduction
2.
What are SQL Functions?
3.
Why do we need SQL functions?
4.
Rules for SQL Functions
5.
SQL Aggregate Functions
5.1.
1. AVG()
5.2.
2. COUNT()
5.3.
3. FIRST()
5.4.
4. LAST()
5.5.
5. MAX()
5.6.
6. MIN()
5.7.
7. SUM()
6.
SQL Scalar Functions
6.1.
1. UCASE()
6.2.
2. LCASE()
6.3.
3. MID()
6.4.
4. LEN()
6.5.
5. ROUND()
6.6.
6. NOW()
6.7.
7. FORMAT()
7.
Frequently Asked Questions
7.1.
What are the functions of SQL?
7.2.
How many SQL functions are there?
7.3.
What is any function in SQL?
8.
Conclusion
Last Updated: Mar 27, 2024
Easy

SQL Functions

Author Yukti Kumari
1 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Today's world is all about data. With the increasing popularity of the internet, the amount of data is also increasing rapidly. 

How to manage such a large amount of data efficiently?

SQL comes to the rescue. SQL is a Structured Query Language that we use to manage data held in the relational database management system. You can perform multiple operations on a database using SQL, like inserting new data, retrieving desired information, updating data, etc.

sql functions

In this article, we will learn SQL functions, their types, and a detailed description of each function. Let’s get started.

What are SQL Functions?

SQL functions are a set of statements that perform a specific task. Generally, the SQL functions take inputs, perform certain operations, and return results as a single value or a table. They run in the database to perform activities on datasets or database objects. Furthermore, they can be classified into DDL(Data Definition Language) functions, DML(Data manipulation language ) functions, and DQL(Data Query Language) functions.

Why do we need SQL functions?

We need SQL functions for many reasons: they allow us to replicate and use tasks in multiple places. Also, they are used to manipulate the data inside our database, like concatenation, mathematical calculations, etc. It also makes the code reusable, reducing the code duplication. It further provides a certain level of abstraction. Also, it breaks down the code into manageable pieces. Next, they can also be utilized to control access to private data by providing security and standardization. Finally, they can also be used to perform optimization tasks.

Rules for SQL Functions

Rules for defining SQL functions are as follows:

  • Each function should have a name that must not start with any special characters like @, $, #, etc.
     
  • Functions should always return a value.
     
  • A function is compiled every time it is called.
     
  • You cant use TRY and CATCH inside functions.
     
  • We can use functions with the SELECT query, like AVG, COUNT, SUM, etc.

SQL Aggregate Functions

The SQL functions which perform functions on a set of values or multiple rows in a table and return a single value are called aggregate functions. GROUP BY clause often uses the aggregate functions along with SELECT statements. 

It is important to note that all the aggregate functions are deterministic and ignore the null values in a table except for the COUNT(*) function. 

Some of the aggregate functions are AVG(), SUM(), COUNT(), FIRST(), LAST(), etc. 

We will consider the Customer table below for performing these functions and see their respective outputs.

Customers Table

customer_id

first_name

last_name

age

country

1

John

Doe

31

USA

2

Robert

Luna

22

USA

3

David

Robinson

22

UK

4

John

Reinhardt

25

UK

5

Betty

Doe

28

UAE

1. AVG()

The AVG() function returns the average value of all values of a numeric column.

Syntax:

SELECT AVG(column_name) FROM table_name;

 

Example:

Computing the average age of the customers

Query

SELECT AVG(age) FROM Customers;

 

Output

25.6


2. COUNT()

The COUNT() function returns the number of rows that the SELECT statement returns.

We cant use this function in MS ACCESS.

Syntax:

SELECT COUNT(column_name) FROM table_name;

 

Example:

Computing the number of rows in the customers table

 

Query

SELECT COUNT(customer_id) FROM Customers;

 

Output

5


3. FIRST()

The FIRST() function returns the first value from the selected column in a table.

Syntax:

SELECT FIRST(column_name) FROM table_name;

 

Example:

Finding the first country from the country column in the table.

 

Query

SELECT FIRST(country) FROM Customers;

 

Output

USA


4. LAST()

The LAST() function returns the last value from the selected column in a table. We can use this function only in MS ACCESS.

Syntax:

SELECT LAST(column_name) FROM table_name;

 

Example:

Finding the last country from the country column in the table.

Query

SELECT LAST(country) FROM Customers;

 

Output

UAE


5. MAX()

The MAX() function returns the maximum value among all the values in a selected column.

Syntax:

SELECT MAX(column_name) FROM table_name;

 

Example:

Computing the maximum age among the customers

Query

SELECT MAX(age) FROM Customers;

 

Output

31


6. MIN()

The MIN() function returns the minimum value among all the values in a selected column.

Syntax:

SELECT MIN(column_name) FROM table_name;

 

Example:

Computing minimum age among the customers

Query

SELECT MIN(age) FROM Customers;

 

Output

22


7. SUM()

The SUM() function returns the sum of all the values from a selected column in a table.

Syntax:

SELECT SUM(column_name) FROM table_name;

 

Example: 

Computing the sum of the ages of the customer

Query

SELECT SUM(age) FROM Customers;

 

Output

128

Also see, Natural Join in SQL

SQL Scalar Functions

The functions which return a single value of any data type from the given input parameters in SQL are called scalar functions. Scalar functions simplify the code, and we generally use them to perform complex calculations.

Some of the scalar functions are UCASE(), LCASE(), MID(), LEN(), etc. 

1. UCASE()

UCASE stands for Uppercase, which converts all the input string characters to uppercase. 

Syntax:

SELECT UCASE(column_name) FROM table_name;

 

Example:

Query

SELECT UCASE(first_name) FROM Customers;

 

Output

UCASE(first_name)

JOHN

ROBERT

DAVID

JOHN

BETTY

2. LCASE()

LCASE stands for Lowercase, and it converts all the characters of the input string to lowercase. 

Syntax:

SELECT LCASE(column_name) FROM table_name;

 

Example:

Query

SELECT LCASE(first_name) FROM Customers;

 

Output

LCASE(first_name)

john

robert

david

john

betty

3. MID()

The MID() function extracts substrings from the strings of a column.

Syntax:

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

 

Example:

Query

SELECT MID(first_name,3,2) FROM Customers; 

 

Output

first_name

hn

be

vi

hn

tt

4. LEN()

The LEN() function finds the length of the given input string.

Syntax:

SELECT LENGTH(column_name) FROM table_name;

 

Example:

Query

SELECT LENGTH(first_name) FROM Customers;

 

Output

LENGTH(first_name)

4

6

5

4

5

5. ROUND()

The ROUND() function rounds off any numeric values present in decimal form.

Syntax:

SELECT ROUND(column_name,decimals) FROM table_name; 

 

Example:

Query

SELECT ROUND(age,0) FROM Customers; 

 

Output

ROUND(age,0)

31

22

22

25

28

6. NOW()

The NOW() function fetches the present day’s data from any table.

Syntax:

SELECT NOW() FROM table_name;

 

Example:

Query

SELECT first_name, NOW() AS DateTime FROM Customers;

 

Output

first_name

DateTime

John

1/15/2023 12:30:09 PM

Robert

1/15/2023 12:30:09 PM

David

1/15/2023 12:30:09 PM

John

1/15/2023 12:30:09 PM

Betty

1/15/2023 12:30:09 PM

7. FORMAT()

The FORMAT() function specifies the format of a field. 

Syntax:

SELECT FORMAT(column_name,format) FROM table_name; 

 

Example:

Query

SELECT first_name, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM Customers; 

 

Output

first_name

Date

John

2023-01-15

Robert

2023-01-15

David

2023-01-15

John

2023-01-15

Betty

2023-01-15

Frequently Asked Questions

What are the functions of SQL?

SQL functions are a set of statements that perform a specific task. Generally, the SQL functions take inputs, perform certain operations, and return results as a single value or a table. They run in the database to perform activities on datasets or database objects.

How many SQL functions are there?

SQL provides numerous functions, including aggregate functions (COUNT, SUM) and scalar functions (UPPER, CONCAT), with variations for different database systems.

What is any function in SQL?

"ANY" in SQL is used in conjunction with comparison operators to evaluate whether any of the values in a subquery meet a specified condition.

Conclusion

In this article, we learned about SQL functions, their types, and a detailed explanation of various functions. SQL functions are powerful tools that enhance the capabilities of SQL queries. Whether it's aggregating data, manipulating strings, or performing mathematical operations, SQL functions contribute to the efficiency and flexibility of database operations.

Check out these useful blogs on - 

 

Do upvote our blog to help other ninjas grow!

Live masterclass