Do you think IIT Guwahati certified course can help you in your career?
No
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.
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 BYclause 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.
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.