SQL Functions: An Overview

Jun 2, 2023 | SQL

To support my work, this post may contain affiliate links (these are referrer links sharing products and services. I get paid a small commission if you make a purchase through the link)

SQL functions are a powerful tool for managing and querying data in a relational database.

SQL functions can be grouped into several categories based on their purpose and complexity.

In this article, we will explore the different groups of SQL functions, provide examples for each, and offer tips for using them effectively.

1. Basic Functions

Basic SQL functions are the most commonly used functions and are relatively easy to understand.

These functions perform simple arithmetic and aggregation operations on a set of values.

COUNT()

The COUNT() function returns the number of rows or non-null values in a set of values.

Here is an example query that uses COUNT() to count the number of employees in a database:


SELECT COUNT(*) as num_employees
FROM employees;

SUM():

The SUM() function returns the sum of a set of values.

Here is an example query that uses SUM() to calculate the total revenue generated by a company:


SELECT SUM(revenue) as total_revenue
FROM sales;

AVG():

The AVG() function returns the average value of a set of values. Here is an example query that uses AVG() to calculate the average salary of employees in a database:


SELECT AVG(salary) as avg_salary
FROM employees;

MIN():

The MIN() function returns the minimum value in a set of values. Here is an example query that uses MIN() to find the smallest product price in a database:


SELECT MIN(price) as min_price
FROM products;

MAX():

The MAX() function returns the maximum value in a set of values. Here is an example query that uses MAX() to find the largest product price in a database:


SELECT MAX(price) as max_price
FROM products;

2. String Functions

String functions are used to manipulate and transform string values in a database. These functions are useful for performing text-based operations, such as concatenation and case conversion.

CONCAT():

The CONCAT() function concatenates two or more strings into a single string.

Here is an example query that uses CONCAT() to combine the first name and last name of employees in a database:


SELECT CONCAT(first_name, ' ', last_name) as full_name
FROM employees;

UPPER():

The UPPER() function converts a string to uppercase.

Here is an example query that uses UPPER() to convert all product names in a database to uppercase:


SELECT UPPER(name) as name_upper
FROM products;

LOWER():

The LOWER() function converts a string to lowercase. Here is an example query that uses LOWER() to convert all product names in a database to lowercase:


SELECT LOWER(name) as name_lower
FROM products;

LENGTH():

The LENGTH() function returns the length of a string. Here is an example query that uses LENGTH() to find the length of each product name in a database:


SELECT name, LENGTH(name) as name_length
FROM products;

SUBSTRING():

The SUBSTRING() function returns a substring of a string. Here is an example query that uses SUBSTRING() to find the first three characters of each product name in a database:


SELECT name, SUBSTRING(name, 1, 3) as name_prefix
FROM products;

3. Date/Time Functions

Date/time functions are used to manipulate and transform date and time values in a database. These functions are useful for performing date-based operations, such as extracting the year or month of a date.

YEAR():

The YEAR() function extracts the year component of a date value. Here is an example query that uses YEAR() to find the birth year of each employee in a database:


SELECT YEAR(birthdate) as birth_year
FROM employees;

MONTH():

The MONTH() function extracts the month component of a date value. Here is an example query that uses MONTH() to find the month of the hire date of each employee in a database:


SELECT MONTH(hire_date) as hire_month
FROM employees;

DAY():

The DAY() function extracts the day component of a date value. Here is an example query that uses DAY() to find the day of the birthdate of each employee in a database:


SELECT DAY(birthdate) as birth_day
FROM employees;

DATE():

The DATE() function returns a date value. Here is an example query that uses DATE() to create a date from year, month, and day values:


SELECT DATE(2022, 3, 21) as today_date
FROM employees;

GETDATE():

The GETDATE() function returns the current date and time. Here is an example query that uses GETDATE() to find the current date and time:


SELECT GETDATE() as current_datetime
FROM employees;

4. Conditional Functions

Conditional functions are used to perform conditional operations on data in a database. These functions are useful for filtering and transforming data based on certain conditions.

CASE():

The CASE() function performs a conditional operation on a set of values.

Here is an example query that uses CASE() to assign a grade to students based on their test score:


SELECT student_name,
CASE
    WHEN test_score >= 90 THEN 'A'
    WHEN test_score >= 80 THEN 'B'
    WHEN test_score >= 70 THEN 'C'
    ELSE 'F'
END as grade
FROM students;

COALESCE():

The COALESCE() function returns the first non-null value in a set of values.

Here is an example query that uses COALESCE() to find the name of each employee in a database or ‘Unknown’ if the name is null:


SELECT COALESCE(name, 'Unknown') as name_or_unknown
FROM employees;

NULLIF():

The NULLIF() function compares two expressions and returns null if they are equal. Here is an example query that uses NULLIF() to find the sales revenue for each product in a database or null if the product has no sales:


SELECT product_name, NULLIF(SUM(revenue), 0) as sales_revenue
FROM sales
GROUP BY product_name;

5. Advanced Functions

Advanced SQL functions are more complex and are typically used by more experienced SQL users.

These functions are useful for advanced calculations and analysis.

RANK():

The RANK() function assigns a rank to each row within a result set.

Here is an example query that uses RANK() to assign a rank to each product based on its price:


SELECT product_name, 
       price, 
       RANK() OVER (ORDER BY price DESC) as price_rank

FROM products;

ROW_NUMBER():

The ROW_NUMBER() function returns the sequential number of a row within a result set. Here is an example query that uses ROW_NUMBER() to assign a row number to each product:


SELECT product_name, 
       ROW_NUMBER() OVER (ORDER BY product_name) as row_number

FROM products;

NTILE():

The NTILE() function divides a result set into a specified number of groups. Here is an example query that uses NTILE() to divide the products in a database into four price-based groups:


SELECT product_name, 
       price, 
       NTILE(4) OVER (ORDER BY price) as price_group

FROM products;

LEAD():

The LEAD() function accesses data from the next row in a result set.

Here is an example query that uses LEAD() to find the price difference between each product and the next most expensive product in a database:


SELECT product_name, 
       price, 
       LEAD(price) OVER (ORDER BY price DESC) - price as price_difference

FROM products;

LAG():

The LAG() function accesses data from the previous row in a result set.

Here is an example query that uses LAG() to find the price difference between each product and the previous product in a database:


SELECT product_name, 
       price, 
       price - LAG(price) OVER (ORDER BY price) as price_difference

FROM products;

Tips for Using SQL Functions Effectively

Use the appropriate function for the task at hand.
Consider the purpose and complexity of the function when selecting a function to use in your query.
Be mindful of the data types in your database.
SQL functions may produce unexpected results if used on the wrong data type.
Avoid using too many functions in a single query.
This can make the query more difficult to read and understand.
Test your queries before using them in production.
Use sample data to verify that the query returns the expected results.
Document your queries.
Clearly document the purpose of your query and the functions used to ensure that others can understand and use your query effectively.

Conclusion