Skip to main content

Common SQL Functions

Introduction

SQL functions are built-in commands that are used to manipulate data in a relational database. They are a fundamental part of SQL (Structured Query Language) and are used to perform calculations, manipulate strings, dates, and perform other operations on data. SQL functions are a powerful tool that can make your SQL queries more dynamic and efficient.

In this tutorial, we will cover some of the most common SQL functions that you will find useful in everyday data manipulation tasks.

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. Let's take a look at some of the most commonly used aggregate functions:

COUNT()​

The COUNT() function returns the number of rows that matches a specified criteria.

Example:

SELECT COUNT(CustomerID) 
FROM Customers;

This will return the number of CustomerID in the Customers table.

SUM()​

The SUM() function returns the total sum of a numeric column.

Example:

SELECT SUM(Quantity) 
FROM OrderDetails;

This will return the total sum of the Quantity in the OrderDetails table.

AVG()​

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

Example:

SELECT AVG(Price) 
FROM Products;

This will return the average Price from the Products table.

Scalar Functions

Scalar functions return a single value based on the input value.

UCASE()​

The UCASE() function converts the value of a field to uppercase.

Example:

SELECT UCASE(FirstName) 
FROM Employees;

This will return the FirstName of all employees in uppercase.

LCASE()​

The LCASE() function converts the value of a field to lowercase.

Example:

SELECT LCASE(FirstName) 
FROM Employees;

This will return the FirstName of all employees in lowercase.

Date Functions

SQL provides several functions to handle date and time values.

NOW()​

The NOW() function returns the current system date and time.

Example:

SELECT NOW() 
FROM Employees;

This will return the current date and time.

DATEDIFF()​

The DATEDIFF() function returns the difference between two date values, based on the date part that is defined.

Example:

SELECT DATEDIFF(day, '2022-01-01', '2022-01-31') 
AS Difference;

This will return the difference in days between '2022-01-01' and '2022-01-31'.

Conclusion

These are just a few examples of the many SQL functions available. SQL functions are an essential part of SQL and they can significantly improve the efficiency of your SQL queries. By mastering SQL functions, you can perform complex calculations, manipulate strings, dates, and much more. Remember, practice is key when it comes to learning SQL functions, so don't hesitate to use these functions in your SQL queries.