SQL Practice Problems to Train Your Teams

In today's data-driven world, the ability to harness the power of data is an invaluable skill. And SQL is the language of databases. It's the key that unlocks the treasure troves of information stored in data repositories, allowing us to retrieve, manipulate, and analyze data with precision and efficiency.

To help your team develop a more thorough understanding of SQL, we have compiled a series of five SQL practice problems. These problems cover a range of advanced scenarios, allowing your team to develop SQL knowledge that will be useful in practical contexts.

Problem #1: SQL query processing with grouping, aggregation, filtering, and sorting

Consider a simplified database system with two tables: Employees and Departments. The Employees table contains information about employees, including their EmployeeID, EmployeeName, Salary, and DepartmentID. The Departments table contains information about departments, including DepartmentID and DepartmentName.

Write a step-by-step description of the logical query processing of the following SQL query:

Explain each step of how this query is processed logically, including any temporary results or intermediate stages.

Problem #2: Calculating running total of sales using window functions

You have a database with a table named Sales that contains information about sales transactions. The Sales table has the following columns: SaleID, ProductID, SaleDate, and SaleAmount.

Write an SQL query that retrieves the following information for each sale:

The running total of sales (RunningTotal) for each product, sorted by SaleDate.

Your query should return all the sales and include the running total of sales for each product at the time of that sale, ordered by SaleDate.

Data set

Sales Table:

Solution

You can use a window function, specifically the SUM window function, to calculate the running total of sales for each product. Here's the SQL query to solve this problem:

This query:

1. Retrieves the SaleID, SaleDate, SaleAmount, and ProductID columns from the Sales table.
   
   
2. Uses the SUM window function to calculate the running total of SaleAmount for each product. The PARTITION BY ProductID clause ensures that the running total is calculated separately for each product, and the ORDER BY SaleDate clause specifies the order in which the running total is calculated (in ascending order of SaleDate).
   
   
3. Orders the results by SaleDate to get the sales and their running totals in chronological order.

This query will give you a result set with each sale, its date, amount, product, and the running total of sales for that product at the time of the sale.

Problem #3: Query tuning and optimization for customer order count

You have a database with two tables: Customers and Orders. The Customers table contains information about customers, including their CustomerID, CustomerName, and Country. The Orders table contains information about orders, including OrderID, CustomerID (which links to the CustomerID in the Customers table), and OrderDate.

You are given the following SQL query, which is intended to retrieve the list of customer names along with the total count of orders they have made:

However, you have noticed that this query is performing poorly, particularly when there are a large number of customers and orders in the database. Your task is to optimize this query to improve its performance.

Solution

Here are some optimization steps you can take:

Indexing: Ensure that there are appropriate indexes on the join columns (CustomerID in both tables) to speed up the join operation.

Selecting Only Necessary Columns: In the SELECT clause, you are only interested in the CustomerName and the count of orders. Therefore, you can rewrite the query to avoid selecting unnecessary columns. This can reduce the amount of data that needs to be processed.

Aggregating with INNER JOIN: If the database allows it, you can use an INNER JOIN instead of a LEFT JOIN when you're only interested in customers who have placed orders. This can potentially optimize the query.

Regular Database Maintenance: Perform regular database maintenance tasks like index reorganization and statistics updates to ensure the database is running efficiently.

Query Profiling: Use query profiling tools to analyze the execution plan of the query and identify performance bottlenecks. This can help you see where the query might be consuming the most resources and where you can make further improvements.

Optimizing this query involves database-specific considerations, but these general steps can help you get started. The goal is to reduce the amount of data processed and to make efficient use of indexes and joins.

As with any optimization scenario, effective query optimization may involve trade-offs depending on your specific use case and database system.

 

Problem #4: Retrieving top-selling books by sales quantity

You are developing a web-based e-commerce application for a bookstore. You need to write an SQL query to retrieve a list of the top-selling books from the database.

Each book's sales data is stored in the Books and Sales tables. The Books table contains information about the books, including BookID, Title, Author, and Price. The Sales table contains information about sales, including SaleID, BookID, Quantity, and SaleDate.

Write an SQL query to retrieve the top 10 best-selling books by the total number of copies sold, along with their titles, authors, and total sales quantity. Ensure that the list is sorted in descending order of sales quantity.

Solution

You can use the following SQL query to solve this problem:

This query:

1. Joins the Books and Sales tables on the BookID column to associate each sale with the corresponding book.
   
   
2. Groups the results by book title and author using the GROUP BY clause.
   
   
3. Calculates the total sales quantity for each book using the SUM function.
   
   
4. Orders the results in descending order of total sales quantity using the ORDER BY clause.
   
   
5. Limits the result set to the top 10 records using the LIMIT clause.

The query will provide you with a list of the top-selling books in descending order of sales quantity, along with their titles, authors, and total sales quantity.

Problem #5: Analyzing customer transactions for e-commerce insights

You are a data scientist working with a dataset of customer transactions for an e-commerce platform. The dataset contains two tables: Customers and Transactions. The Customers table contains information about customers, including CustomerID, CustomerName, and Country. The Transactions table contains information about individual transactions, including TransactionID, CustomerID (which links to the CustomerID in the Customers table), TransactionDate, and Amount.

Your task is to write an SQL query to analyze the data and find insights regarding customer behavior. Specifically, you need to find the following information:

1. The total number of transactions in the dataset.
   
   
2. The total revenue generated from all transactions.
   
   
3. The average transaction amount.
   
   
4. The country with the highest total revenue from transactions.
   
   
5. The customer who has made the highest number of transactions.

Write an SQL query that retrieves this information from the Customers and Transactions tables.

Solution

You can use the following SQL query to obtain the desired insights:

 

This query:

1. Counts the total number of transactions in the dataset.
   
   
2. Calculates the total revenue from all transactions.
   
   
3. Computes the average transaction amount.
   
   
4. Identifies the country with the highest total revenue from transactions.
   
   
5. Determines the customer who has made the highest number of transactions.

These insights can help data scientists better understand customer behavior and the financial performance of the e-commerce platform.