How to use functions in SQL

Introduction

When working with relational databases and Structured Query Language (SQL), you can store, manage, and retrieve data from a relational database management system. SQL can retrieve data intact, as it was stored in the database.

SQL can also perform calculations and manipulate data through the use of functions. For example, you can use functions to retrieve product prices rounded to the nearest dollar, calculate the average number of product purchases, or determine the number of days left until the warranty on a purchase expires.

In this tutorial, you will use various SQL functions to perform mathematical calculations, manipulate strings and dates, and calculate summaries using aggregate functions.

Prerequisites

To follow this guide, you need a computer running a SQL-based relational database management system (RDBMS). The instructions and examples in this guide have been validated using the following environment:

• A server running Ubuntu 20.04 with a non-root user with administrative privileges and a firewall configured with UFW, as described in the Initial Server Setup Guide for Ubuntu 20.04.
• MySQL is installed and secured on the server, as described in the “How to Install MySQL on Ubuntu 20.04” guide. This guide assumes using a non-root MySQL user that was created with the process described in Step 3.
• A basic introduction to executing SELECT queries to select data from a database, as explained in the "How to SELECT Rows from Tables in SQL" guide.

Note: Many RDBMSs use their own implementation of SQL. Although the commands described in this tutorial work in most RDBMSs, the standard SQL syntax specifies only a limited number of functions. Additionally, support for the standard syntax varies among different database engines. The exact syntax or output may differ if you test them on a system other than MySQL.

You will also need a database with tables loaded with sample data so that you can use the functions. We encourage you to review the “Connecting to MySQL and Setting Up a Sample Database” section for details on connecting to a MySQL server and creating the test database used in the examples in this guide.

Connecting to MySQL and setting up a sample database

In this section, you will connect to a MySQL server and create a sample database so you can follow the examples in this guide.

If your SQL database system runs on a remote server, log in to your server via SSH:

ssh sammy@your_server_ip

Then open the MySQL server environment, instead of Sammy Enter your MySQL username:

mysql -u sammy -p

Create a database called bookstore:

CREATE DATABASE bookstore;

If the database was created successfully, you will receive output similar to the following:

Output Query OK, 1 row affected (0.01 sec)

To select the bookstore database, run the USE command:

USE bookstore;

You will see the following output:

Output Database changed

Once you have selected a database, you can create sample tables in it. For this guide, we will use a fictional library that sells a variety of books by different authors.

The inventory table contains information about the books available in the bookstore. It holds the following columns:

• book_id: This column contains the ID of each book represented by the int data type. This column becomes the primary key of the table and each value becomes a unique identifier for the corresponding row.
• Author: This column contains the name of the book's author, expressed using the varchar data type with a maximum of 50 characters.
• Title: This column contains the title of the purchased book, expressed using the varchar data type with a maximum of 200 characters.
• introduction_date: This column uses the date data type to record the date each book was introduced by the bookstore.
• Stock: This column holds the number of books the bookstore has in stock using the integer data type.
• Price: This column stores the retail price of the book using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.

Create the sample table with the following command:

CREATE TABLE inventory (book_id int, author varchar(50), title varchar(200), introduction_date date, stock int, price decimal(5, 2), PRIMARY KEY (book_id) );

If the following output is printed, the table has been created:

Output Query OK, 0 rows affected (0.00 sec)

Then, load the purchases table with some sample data by executing the following INSERT INTO operation:

INSERT INTO inventory VALUES (1, 'Oscar Wilde', 'The Picture of Dorian Gray', '2022-10-01', 4, 20.83), (2, 'Jane Austen', 'Pride and Prejudice', '2022-10-04', 12, 42.13), (3, 'Herbert George Wells', 'The Time Machine', '2022-09-23', 7, 21.99), (4, 'Mary Shelley', 'Frankenstein', '2022-07-23', 9, 17.43), (5, 'Mark Twain', 'The Adventures of Huckleberry Finn', '2022-10-01', 14, 23.15);

The INSERT INTO operation adds five books with the specified values to the inventory table. The following output shows that all five rows have been added:

Output Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0

With that, you are ready to follow the rest of the guide and start using functions in SQL.

Understanding SQL functions

Functions are named expressions that take one or more values, perform calculations or transformations on the data, and return a new value as a result. You can think of SQL functions in a similar way to mathematical functions. For example, the log(x) function takes a value x and returns the logarithm of x.

For example, if you wanted to retrieve all book titles with their prices, in order from most expensive to cheapest, you could run the following command:

SELECT title, price, introduction_date FROM inventory ORDER BY price DESC;

You will get output similar to the following:

Output +----------------------------------+-------+-------------------+ | title | price | introduction_date | +----------------------------------+-------+-------------------+ | Pride and Prejudice 42.13 | 2022-10-04 | | The Adventures of Huckleberry Finn | 23.15 2022-10-01 | | The Time Machine | 21.99 | 2022-09-23 | | The Picture of Dorian Gray | 20.83 | 2022-10-01 | | Frankenstein | 17.43 2022-07-23 | +--------------------------------------------------+-------+------------------+ 5 rows in set (0.000 sec)

In this command, title, price and introduction_date are the names of the columns, and in the output, the database shows the intact values retrieved from those columns for each book: the full title of the book, the price, and the date the book arrived at the library.

However, you may want to retrieve values from the database after some kind of processing or manipulation. You might be interested in book prices rounded to the nearest dollar, book titles in uppercase, or the year of introduction, excluding the month or day. This is when you use a function.

SQL functions can be broadly classified into several groups, depending on the type of data they operate on. These are the most commonly used functions:

• Mathematical functions: Functions that operate on numeric values and perform calculations, such as rounding, logarithms, square roots, or powers.
• String manipulation functions: Functions that operate on strings and text fields that perform text transformations, such as converting text to uppercase, truncating, or replacing words in values.
• Date and time functions: Functions that operate on date fields. These functions perform calculations and conversions, such as adding a number of days to a given date or subtracting a year from a full date.
• Aggregate functions: A special case of mathematical functions that operate on values obtained from multiple rows, such as calculating the average price for all rows.

Note: Most relational databases, including MySQL, extend the standard set of functions defined by the SQL standard with additional operations specific to the database engine. Many functions outside the standard set of SQL functions work similarly across many databases, while others are specific to a single RDBMS and its unique features. You can refer to the documentation for your chosen database to learn more about the functions that the database provides. In the case of MySQL, you can learn more about built-in functions and operator references.

The following example shows the general syntax for using a fictitious, non-existent function named EXAMPLE to modify the results for price values in a bookstore inventory database using a SELECT query:

SELECT EXAMPLE(price) AS new_price FROM inventory;

The function (EXAMPLE) takes the column name (price) as an argument in parentheses. This part of the query tells the database to run the EXAMPLE function on the price values in the column and return the results of this operation. AS new_price tells the database to assign a temporary name (new_price) to the values calculated during the query. With it, you can identify the results of the function in the output and you can refer to the calculated values using the WHERE and ORDER BY clauses.

In the following section, you will use mathematical functions to perform common calculations.

Using mathematical functions

Mathematical functions operate on numeric values, such as the price of a book or the number of books in stock in the sample database. They can be used to perform calculations in the database to tailor the results to your needs.

Rounding is one of the most common uses of mathematical functions in SQL. Suppose you need to retrieve the prices of all books, but you only want the values rounded to the nearest dollar. To do this, you can use the function ROUND Use which performs the rounding operation.

Run the following command:

SELECT title, price, ROUND(price) AS rounded_price FROM inventory;

You will see output similar to the following:

Output +----------------------------------+-------+---------------+ | title | price | rounded_price | +----------------------------------+-------+---------------+ | The Picture of Dorian Gray | 20.83 | 21 | | Pride and Prejudice 42.13 | 42 | | The Time Machine | 21.99 | 22 | | Frankenstein | 17.43 17 | The Adventures of Huckleberry Finn | 23.15 23 +--------------------------------------------------+-------+---------------+ 5 rows in set (0.000 sec)

The above command returns the values of the columns. title and price with a temporary column rounded_price Which results from the function ROUND(price) This function takes one argument, which is the column name (here, price) and rounds the values in that column in the table to the nearest integer.

The ROUND function can also accept additional arguments that specify the number of decimal places to which the rounding should be performed, as well as arithmetic operations instead of a column name. For example, run the following query:

SELECT title, price, ROUND(price * stock, 1) AS stock_price FROM inventory;

You will get the following output:

Output +----------------------------------+-------+-------+-------------+ | title | stock | price | stock_price | +--------------------------------------------------+-------+-------+-------------+ | The Picture of Dorian Gray | 4 20.83 | 83.3 | | Pride and Prejudice 12 | 42.13 | 505.6 | | The Time Machine | 7 | 21.99 | 153.9 | | Frankenstein | 9 17.43 156.9 | | The Adventures of Huckleberry Finn | 14 23.15 324.1 | +--------------------------------------------------+-------+-------+-------------+ 5 rows in set (0.000 sec)

Execution ROUND(Price * Stock, 1) It first multiplies the price of a single book by the number of books available, and then rounds the resulting price to the first decimal place. The result will be presented in the temporary column Stock_Price.

Other mathematical functions built into MySQL include trigonometric functions, square roots, powers, logarithms, and exponentials. You can learn more about using mathematical functions in SQL in the tutorial How to Use Mathematical Expressions and Aggregation Functions in SQL.

In the next section, you will manipulate text from the database using SQL functions.

Using text functions

Text functions in SQL can be used to manipulate and change string values. For example, you might want to retrieve book names in a more orderly manner by removing extra spaces from the beginning and end of their titles.

To do this, you can use the function TRIM Use this function to remove all spaces and extra characters at the beginning and end of a string.

Consider the following example:

SELECT LOWER(title) AS title_lowercase FROM inventory;

The following output is printed to the screen:

Output +------------------------------------------------+ | title_lowercase | +-----------------------------------------+ | the picture of dorian gray | | pride and prejudice | | the time machine | | Frankenstein | | the adventures of huckleberry finn | +--------------------------------------------------+ 5 rows in set (0.001 sec)

The SQL function LOWER takes a single argument and converts its contents to lowercase. Through the alias column AS title_downcase, the resulting data is presented in a temporary column named title_downcase.

Now retrieve all the authors, this time converted to uppercase. Try running the following SQL query:

SELECT UPPER(author) AS author_uppercase FROM inventory;

You will get the following output:

Output +------------------------+ | author_uppercase | +-----------------------+ | OSCAR WILDE | | JANE AUSTEN | | HERBERT GEORGE WELLS | | MARY SHELLEY | | MARK TWAIN | +----------------------+ 5 rows in set (0.000 sec)

Instead of the LOWER function, you used the UPPER function, which works similarly but converts the text to uppercase. Both functions can be used if you want to ensure case consistency when retrieving data.

Another useful string manipulation function is CONCAT, which takes multiple arguments that hold text values and concatenates them together. Try retrieving authors and book titles in a single column. To do this, run the following statement:

SELECT CONCAT(author, ': ', title) AS full_title FROM inventory;

This command returns the following output:

Output +------------------------------------------------+ | full_title | +------------------------------------------------+ | Oscar Wilde: The Picture of Dorian Gray | | Jane Austen: Pride and Prejudice | | Herbert George Wells: The Time Machine | | Mary Shelley: Frankenstein | | Mark Twain: The Adventures of Huckleberry Finn | +------------------------------------------------+ 5 rows in set (0.001 sec)

The CONCAT function concatenates multiple strings and is executed with three arguments. The first, author, refers to the author column containing the author's name. The second, :, is an arbitrary string value to delimit authors and book titles with a colon. The last, title, refers to the column containing the book titles.

As a result of this query, the authors and titles are returned in a temporary column called full_title, which is directly concatenated by the database engine.

Other string functions built into MySQL include functions for searching and replacing strings, retrieving substrings, padding and trimming string values, and applying regular expressions, etc. You can learn more about using SQL functions to concatenate multiple values in How to Manipulate Data with CAST Functions and SQL Concatenation Expressions. You can also refer to String Functions and Operators in the MySQL documentation.

In the next section, you will use SQL functions to manipulate dates from the database.

Using date and time functions

Date and time functions in SQL enable you to manipulate values stored in columns that hold dates and timestamps while processing SQL queries. They can be used to extract portions of date information, perform date calculations, or format dates and timestamps into required output formats.

Suppose you need to split the book's publication date into year, month, and day separately instead of having a single date column in the output.

Try running the following command:

SELECT introduction_date, YEAR(introduction_date) as year, MONTH(introduction_date) as month, DAY(introduction_date) as day FROM inventory;

You will see output similar to the following:

Output +------------------+------+-------+------+ | introduction_date | year | month | day +------------------+------+-------+------+ | 2022-10-01 | 2022 | 10 | 1 | | 2022-10-04 | 2022 | 10 | 4 | 2022-09-23 | 2022 | 9 23 | 2022-07-23 | 2022 | 7 | 23 | 2022-10-01 | 2022 | 10 | 1 | +------------------+------+-------+------+ 5 rows in set (0.000 sec)

This SQL statement uses three separate functions: YEAR, MONTH, and DAY. Each function takes the name of the column where the dates are stored as an argument and extracts only one part of the full date: a year, a month, or a day, respectively. Using these functions, you can access individual pieces of a date in SQL queries.

Another useful function for manipulating dates is DATEDIFF, which allows you to retrieve the number of days between two dates. Now, check how many days have passed between the date each book was introduced and the current date.

Run the following query:

SELECT introduction_date, DATEDIFF(introduction_date, CURRENT_DATE()) AS days_since FROM inventory;

The following output is printed to the screen:

Output +------------------+------------+ | introduction_date | days_since | +------------------+------------+ | 2022-10-01 | -30 | | 2022-10-04 | -27 | | 2022-09-23 | -38 | | 2022-07-23 | -100 | | 2022-10-01 | -30 | +------------------+------------+ 5 rows in set (0.000 sec)

The DATEDIFF function takes two arguments: a start date and an end date. The DATEDIFF function calculates the number of days that separate these two points. If the end date is earlier, the result may be a negative number. In this example, the first argument is the name of the introduction_date column, which holds the dates in the inventory table. The second argument is another function called CURRENT_DATE, which represents the current system date. Running this query retrieves the number of days between these two points in time and places the results in a temporary column called days_since.

Note: DATEDIFF is not part of the official SQL standard function set. While many databases support this function, the syntax often varies between different database engines. This example follows the native MySQL syntax.

Other date manipulation functions built into MySQL include adding and subtracting time intervals and dates, formatting dates for different language formats, retrieving day and month names, or creating new date values. You can learn more about working with dates in SQL in the How to Work with Dates and Times in SQL tutorial. You can also refer to the Date and Time Functions in the MySQL documentation.

In the next section, you will learn how to use aggregate functions.

Using aggregate functions

In all of the previous examples, you used SQL functions to apply transformations or calculations to individual column values in a row, representing a book in a bookstore. SQL provides a way to perform mathematical calculations on multiple rows to help you find aggregate information about the entire data set.

• Basic aggregate functions in SQL include:
• AVG for the average of the values on which calculations are performed.
• COUNT for the number of values on which calculations are performed.
• MAX for maximum value.
• MIN for minimum value.
• SUM for the sum of all values.

You can include multiple aggregate functions in your SELECT query. Imagine you want to check the number of books listed in a bookstore, the maximum price of each book available, and the average price across the entire catalog. To do this, run the following statement:

SELECT COUNT(title) AS count, MAX(price) AS max_price, AVG(price) AS avg_price FROM inventory;

This command returns the following output:

Output +-------+-----------+-----------+ | count | max_price | avg_price | +-------+-----------+-----------+ | 5 | 42.13 | 25.106000 | +-------+-----------+-----------+ 1 row in set (0.001 sec)

The above query uses three aggregate functions at once. The COUNT function counts the rows that the query searches. In this example, the title is passed as an argument, but since the number of rows will be the same for each marked column, you can use any other column name as the function argument. The MAX function calculates the maximum value from the price column: Here, the column name is important, because the calculation is performed on the values in that column. The last function is the AVG function, which calculates the average of all the prices from the price column.

Using aggregate functions in this way results in a row being returned to the database with temporary columns that represent the values of the aggregate calculations. The source rows are used internally for the calculation but are not returned through the query. In this example, you used aggregate functions to calculate statistical values from the entire inventory table at once, taking all rows into account for the summary.

With SQL, you can also divide a table's rows into groups and then calculate the sum of those groups separately. For example, you can calculate the average price of books by different authors to find out which author has published the most expensive titles. You can learn more about grouping rows for such calculations in the tutorial How to Use GROUP BY and ORDER BY in SQL. You can also learn more about using aggregates in the tutorial How to Use Mathematical Expressions and Aggregation Functions in SQL.

Result

In this guide, you learned how to use functions in SQL to perform calculations on data and create more complex queries. These functions help you manipulate data in different ways and get better results. Keep in mind that the exact syntax of SQL may vary depending on your type of relational database management system (RDBMS).