12 SQL Functions Practice Questions

SQL functions allow us to manipulate data, including string and numeric values. This article presents 12 SQL functions practice questions – with solutions and detailed explanations – that will help you solidify your SQL knowledge.

Practicing SQL is important if you want to improve your skills. But finding SQL practice exercises is hard. We want to help, so we’ve been publishing free compilations of SQL practice exercises. In this article, we’ve provided 12 SQL functions practice questions, complete with solutions and explanations. If you enjoy this article, check out our comprehensive SQL Practice track; you can try the first few exercises for free!

Okay, let’s get started. Before we jump into the SQL function exercises, let’s quickly review the different types of SQL functions and look at our dataset.

What Is an SQL Function?

An SQL function takes one or more arguments as input, processes the argument(s), and returns an output. SQL offers a set of predefined functions that work on string values (e.g. UPPER(), SUBSTRING()), numeric values (e.g. FLOOR(), GREATEST()), and NULL values (e.g. ISNULL()).

SQL functions can further be classified as scalar, aggregate, and window functions. Scalar functions take a single argument and return a single result, e.g. LEN(name) would return the number of characters in each name. Aggregate functions return a single value for a group of rows (i.e. they return an aggregate value). For example, the SUM(sales) returns the total of all the values in the sales column. Aggregate functions are paired with GROUP BY to compute statistics.

Window functions work on groups of rows (called windows) that can change in relation to the current row. Like aggregate functions, window functions are applied to a group of rows; unlike aggregate functions, they return one value per row. You can learn more about the difference between aggregate and window functions here.

If you want to really dig into SQL functions, I recommend our Standard SQL Functions course. Its 211 coding challenges will certainly give you plenty of practice!

Introducing the Dataset

Now, let’s quickly get to know the dataset. Our SQL function practice exercises will use a sample DVD Rental dataset that we’ve imported into a PostgreSQL database. Here’s the dataset schema:

And these are the tables we’ll use in our exercises:

• The customer table stores information about DVD rental customers. It connects to the rental and payment tables; each customer can have zero or more records in these tables.
• The rental table stores information about each rental made by customers. It connects to the inventory table, as each rental requires exactly one inventory item. It also connects to the payment table so that a payment record can be assigned to each rental.
• The payment table stores information about each rental’s payment. It also connects to the rental and staff tables to allow us to assign a rental and a staff member to each payment.
• The staff table stores information about DVD store staff members. It connects to the payment and rental tables to assign a staff member to each payment and rental.
• The inventory table stores all items available in the DVD rental’s inventory. It connects to the rental table to assign a single inventory item to each rental. It also connects to the film table to assign each film to a rental store.
• The film table stores details about all the films available for rental. It connects to the inventory table to assign each film to a store. It also connects to the language table to indicate which language(s) the film is available in. Finally, it connects to the film_actor table to assign actors to films (and films to actors).
• The language table stores all languages of the films offered by the DVD rental. It connects to the film table to assign one language to each film.
• The film_actor table stores the many-to-many relationships between the film and actor tables (because each actor can play in many films, and each film can have many actors). It connects to the film and actor tables to assign each film to all actors that played in it and each actor to every film they’ve been in.
• The actor table stores all the actors who appeared in the films available at the DVD rental. It connects to the film_actor table that assigns actors to films.

Now that we know the dataset, let’s get started with practicing SQL functions!

SQL Functions Practice: Strings

SQL provides numerous functions that let you manipulate string values. Here are some of the most common ones:

• CONCAT() concatenates two or more string values into a single
• LENGTH() returns the number of characters contained in the string value.
• LOWER() turns the string into all lowercase letters.
• REPLACE() replaces part of the string with another string.
• SUBSTRING() extracts part of the string.
• UPPER() turns the string into all uppercase

Read this article to learn about all SQL’s string functions.

Exercise 1: Update Staff Emails

Question: The Sakila DVD rental store has opened branches in the UK. Update the staff email addresses by replacing the current email domain sakilastaff.com with sakila.uk; call this column  new_email.

Solution:

SELECT REPLACE(email, 'sakilastaff.com', 'sakila.uk')
          AS new_email
FROM staff;

Explanation: We use the REPLACE() function to replace part of the string with another string.

Each value from the email column of the staff table is passed to the REPLACE() function, which replaces sakilastaff.com with sakila.uk.

Exercise 2: List All Customers

Question: List the names of all active customers using the format last name, first letter of first name. Call this column customer_name and order the list alphabetically.

(Hint: Active customers have a 1 in the active column.)

Solution:

SELECT CONCAT(last_name, ', ', SUBSTRING(first_name, 1, 1))
          AS customer_name
FROM customer
WHERE active = 1
ORDER BY customer_name;

Explanation: We select the first and last names from the customer table. To select only active customers, we define a WHERE clause condition that the value of active must be 1.

To return customer names in the given format, we use the CONCAT() function to concatenate all the parts into one string:

• The first part is the last_name column.
• The second part is a comma.
• The third part is the first letter of the first name, which is extracted by the SUBSTRING() function.

Finally, we order the list alphabetically by the last_name column.

Exercise 3: List Films with Short Descriptions

Question: Display each film title and the first 100 characters of its description, followed by three dots if the description is longer than 100 characters. Name this column truncated_description. If the description has 100 or fewer characters, display the entire description in the same column.

Solution:

SELECT title,
       CASE
         WHEN LENGTH(description) <= 100 THEN description
         ELSE SUBSTRING(description, 1, 100) || '...'
       END AS truncated_description
FROM film;

Explanation: We select the title and description columns from the film table.

We use the CASE WHEN statement to decide the content for the truncated_description column. It is equivalent to the IF…ELSE… statement from other programming languages.

• We use the LENGTH() function to get the number of characters present in the description column. WHEN the number of characters is less than or equal to 100, THEN we display the entire description.
• We use the SUBSTRING() function to get the first 100 characters from the description column. We concatenate it with three dots using the || This content is stored into the truncated_description column if the condition in the WHEN clause is not fulfilled.

Exercise 4: Select Films and Actors

Question: Display all films using the format film title (release year) as the film column. Also display all actors assigned to  films using the format last name, first name as the actor column. Order the data chronologically by release year and then alphabetically by the film title.

Solution:

SELECT f.title || ' (' || f.release_year || ')' AS film,
       a.last_name || ', ' || a.first_name AS actor
FROM film f
JOIN film_actor fa
ON f.film_id = fa.film_id
JOIN actor a
ON fa.actor_id = a.actor_id
ORDER BY f.release_year, f.title;

Explanation:

To select actors’ names and films they played in, we need to join the actor, film, and film_actor tables on their common columns. Refer to this SQL JOIN practice to learn more about joining multiple tables.

In the SELECT statement, we concatenate films’ titles and release years for the entire film table with actors’ first and last names from the actor table.

Finally, we order the data first by film.release_year; within each year, we further order the data alphabetically by the title column.

SQL Function Practice: Numeric Values

SQL provides numerous functions that let you manipulate numeric values. Here are some of the common ones:

• ABS() returns the absolute value of its argument.
• CEILING() returns the value rounded up.
• FLOOR() returns the value rounded down.
• GREATEST() returns the largest number from a group of numbers.
• LEAST() returns the smallest number from a group of numbers.
• ROUND() rounds the number to a defined number of decimal places.

Read this article to learn about all SQL’s numeric functions.

Exercise 5: Calculate the Average Description Length

Question: Show the average length of all film descriptions. Name this column average_film_desc_length. Round the result down to the nearest integer.

Solution:

SELECT 
  FLOOR(AVG(LENGTH(description)))
          AS average_film_desc_length
FROM film;

Explanation:

We select the description column from the film table. We wrap it in three functions to get the average description length rounded down to the nearest integer:

• The LENGTH() function, with description as its argument, gets the number of characters per description.
• We wrap the output of LENGTH() in the AVG() function to calculate the average of all description lengths.
• Finally, we wrap AVG(LENGTH(description) in the FLOOR() function to round the average down to the nearest integer.

Exercise 6: Find the Longest Films

Question: List the title(s) of film(s) with the longest duration.

Solution:

SELECT title
FROM film
WHERE length = (SELECT MAX(length) FROM film);

Explanation:

We select the title column from the film table.

As we want to list only the film(s) with the longest duration, we define a WHERE clause condition. We use a subquery that returns the maximum length value found in the length column. The condition asserts that the length value of the film titles to be displayed must be equal to the value returned by the subquery.

For more practice with SQL subqueries, check out these subquery practice exercises.

Exercise 7: List Rental Statistics

Question: Show the average rental duration in days (as the avg_rental_duration_days column) and the average payment per rental (as the avg_payment_per_rental column). Round both values to 2 decimal places.

Solution:

SELECT 
  ROUND(AVG(EXTRACT(DAY FROM r.return_date - r.rental_date)), 2)
          AS avg_rental_duration_days,
  ROUND(AVG(p.amount), 2) 
          AS avg_payment_per_rental
FROM rental r
JOIN payment p
ON r.rental_id = p.rental_id;

Explanation:

First, we join the rental and payment tables on their common column.

To get the average rental duration in days, we need to find the average difference between the rental_date and return_date columns from the rental table. To do that, we use the EXTRACT() function to get the number of days that the rental lasted (as integer values); we then use AVG() to calculate the average value. Finally, we wrap this in the ROUND() function, providing the average value as the first argument and 2 as the second argument to get the rounded average value.

To get the average payment per rental, we use AVG() yet again – this time passing the amount column from the payment table as its argument. As before, we wrap it in ROUND() to round the result to two decimal places.

Exercise 8: Discount Rental Rates

Question: Sakila is running a special discount on rentals as follows:

• 50% discount on G-rated films.
• 40% discount on PG films.
• 30% discount on PG-13 films.

Select film titles along with their updated rental rates (name this column new_rental_rate).

Solution:

SELECT title,
       CASE
         WHEN rating = 'G' THEN rental_rate * 0.5
         WHEN rating = 'PG' THEN rental_rate * 0.6
         WHEN rating = 'PG-13' THEN rental_rate * 0.7
         ELSE rental_rate
       END AS new_rental_rate
FROM film;

Explanation:

We select the title column from the film table and the updated rental rates based on the rating column.

We use the CASE WHEN statement to apply discounts based on the values in the rating column:

• If the rating is G, then the rental rate is discounted by 50 percent (rental_rate * 0.5).
• If the rating is PG, then the rental rate is discounted by 40 percent (rental_rate * 0.6).
• If the rating is PG-13, then the rental rate is discounted by 30 percent (rental_rate * 0.7).
• Otherwise, we return the rental_rate

SQL Function Practice: NULLs

SQL provides numerous functions that let you manage NULL values. Here are some of the common ones:

• ISNULL() returns true if its argument is a NULL value; otherwise, it returns
• IFNULL() returns a stated value if the original value is NULL.
• COALESCE() returns the first non-NULL value from its arguments.

Exercise 9: Find Customers with Current Rentals

Question: Select first and last names of customers who have ongoing rentals.

Solution:

SELECT c.first_name, c.last_name
FROM customer AS c
JOIN rental AS r
ON c.customer_id = r.customer_id
WHERE r.return_date IS NULL;

Explanation:

We select the first and last names of customers from the customer table.

To get the customers who have ongoing rentals, we must find records in the rental table where the return_date column value is NULL. To do that, we join the customer and rental columns on their common column.

Exercise 10: Select Films and Their Languages

Question: Select all film titles with information on the language(s) that the film is available in. Create a column (named film_language_info) with values following this format:

<film title> is available in <language name>. 

If the language name is NULL, then use the phrase an unknown language instead of the language name.

Solution:

SELECT f.title || ' is available in ' || 
          COALESCE(l.name, 'an unknown language') AS film_language_info
FROM film AS f
JOIN language AS l
ON f.language_id = l.language_id;

Explanation:

We select the title column from the film table and the name column from the language table. Therefore, we must join the film and language tables on their common column.

We concatenate the film titles with the phrase “is available in” and the language name. Note that when selecting the language name, we use the COALESCE() function to replace the language name with “unknown language” if the language name is NULL.

Exercise 11: Find Customers Who Recently Rented a DVD

Question: The Sakila marketing team wants to email customers who haven't rented any DVDs for two weeks. List email addresses of all customers who’ve returned their most recent rental less than two weeks ago. Do not include customers who have ongoing rentals.

Solution:

WITH filtered_customers AS (
    SELECT customer_id,
           MAX(return_date) AS most_recent_return_date
    FROM rental
    WHERE return_date IS NOT NULL
    GROUP BY customer_id
    HAVING MAX(return_date) < CURRENT_DATE - INTERVAL '2 weeks'
)

SELECT c.email
FROM customer c
JOIN filtered_customers fc
ON c.customer_id = fc.customer_id;

Explanation:

First, we create a common table expression (CTE) to filter the customers; we name it filtered_customers. In the CTE, we select the customer_id column and the most recent value of the return_date column per customer from the rental table; therefore, we use the GROUP BY clause. We select only those customers who do not have ongoing rentals – i.e. where the return_date column IS NOT NULL. And we impose a condition on the most_recent_return_date value to be less than two weeks ago.

Then, we join the output returned by this CTE with the customer table and select the email column, so we can send emails to customers who haven’t been active in the past two weeks.

Exercise 12: Update NULL Rental End Dates

Question: Select the customer IDs and rental IDs for all customers, along with the start and end dates of each rental. If the rental end date is NULL, replace it by adding as many days as indicated in the rental duration for the given film to the rental start date.

Solution:

SELECT r.customer_id, 
       r.rental_id, 
       r.rental_date, 
       COALESCE(r.return_date,
                r.rental_date + INTERVAL '1 day' * f.rental_duration)
          AS return_date
FROM rental AS r
JOIN inventory AS i
ON r.inventory_id = i.inventory_id
JOIN film AS f
ON i.film_id = f.film_id;

Explanation:

We select the customer_id, rental_id, and rental_date columns from the rental table. Then, we use the COALESCE() function, passing the return_date column as its first argument. If the return_date column is NULL, then we replace it with the rental_date column plus as many days as indicated in the rental_duration column of the film table.

Note that in order to match the f.rental_duration column value with the r.rental_id column value, we must join the rental table with the film table through the inventory table on their common columns.

Want More SQL Functions Practice?

This article presented real-world examples of how to use SQL functions to get valuable data insights. Go ahead and check out this course on Standard SQL Functions to practice more.

If you’re not ready to commit to a full course, here are some advanced SQL practice exercises that really put your SQL knowledge to the test. You can also try these practice exercises for SQL window functions. Follow this SQL practice guide to make the most of LearnSQL.com’s resources.

Good luck!