Introduction
Structured Query Language (SQL) is an essential tool for managing and analyzing data in relational databases. In this blog, we will explore a set of SQL test questions along with step-by-step explanations and solutions. If you’re preparing for an SQL interview or want to sharpen your querying skills, this guide is for you.
Understanding the Database Schema
Before diving into queries, let’s familiarize ourselves with the database structure.
Tables:
1. Sales Table
| Column | Data Type | Description |
|---|---|---|
| sales_id | INT | Unique identifier for each sale |
| customer_id | INT | Customer who made the purchase |
| product_id | INT | Purchased product ID |
| sale_date | DATE | Date of sale |
| quantity | INT | Number of items sold |
| total_amount | DECIMAL | Total amount of the sale |
2. Customers Table
| Column | Data Type | Description |
|---|---|---|
| customer_id | INT | Unique identifier for each customer |
| customer_name | VARCHAR | Name of the customer |
| sales_region | VARCHAR | Geographic region of the customer |
| sign_up_date | DATE | Date when the customer signed up |
3. Products Table
| Column | Data Type | Description |
|---|---|---|
| product_id | INT | Unique identifier for each product |
| product_name | VARCHAR | Name of the product |
| category | VARCHAR | Category of the product |
| price | DECIMAL | Price per unit of the product |
SQL Queries and Solutions
1. Retrieve Sales Data from the Last 30 Days
Query:
SELECT c.customer_name, p.product_name, s.total_amount
FROM Sales s
JOIN Customers c ON s.customer_id = c.customer_id
JOIN Products p ON s.product_id = p.product_id
WHERE s.sale_date >= CURDATE() - INTERVAL 30 DAY;
Explanation: This query fetches sales transactions from the last 30 days by filtering sale_date. It joins the Sales table with Customers and Products to retrieve customer names and product names.
2. Calculate Total Revenue by Product Category in the Last Year
Query:
SELECT p.category, SUM(s.total_amount) AS total_revenue
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
WHERE s.sale_date >= CURDATE() - INTERVAL 1 YEAR
GROUP BY p.category;
Explanation: This query aggregates total sales revenue per product category from the last year using SUM(s.total_amount).
3. Retrieve Customers from “West” Region Who Purchased in 2023
Query:
SELECT DISTINCT c.customer_id, c.customer_name
FROM Customers c
JOIN Sales s ON c.customer_id = s.customer_id
WHERE c.sales_region = 'West'
AND YEAR(s.sale_date) = 2023;
Explanation: This query selects distinct customers who made a purchase in 2023 and belong to the “West” region.
4. Calculate Total Sales, Quantity, and Revenue per Customer
Query:
SELECT c.customer_name, COUNT(s.sales_id) AS total_sales,
SUM(s.quantity) AS total_quantity, SUM(s.total_amount) AS total_revenue
FROM Sales s
JOIN Customers c ON s.customer_id = c.customer_id
GROUP BY c.customer_name;
Explanation: This query uses COUNT() to find total sales and SUM() for total quantity sold and revenue per customer.
5. Identify the Top 3 Customers by Revenue in 2023
Query:
SELECT c.customer_name, SUM(s.total_amount) AS total_revenue
FROM Sales s
JOIN Customers c ON s.customer_id = c.customer_id
WHERE YEAR(s.sale_date) = 2023
GROUP BY c.customer_name
ORDER BY total_revenue DESC
LIMIT 3;
Explanation: Orders customers by revenue in descending order and limits results to the top 3.
6. Rank Products by Sales Quantity in 2023
Query:
SELECT p.product_name, SUM(s.quantity) AS total_quantity,
RANK() OVER (ORDER BY SUM(s.quantity) DESC) AS rank
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
WHERE YEAR(s.sale_date) = 2023
GROUP BY p.product_name;
Explanation: Uses the RANK() window function to assign ranks based on total quantity sold.
7. Categorize Customers as “New” or “Existing”
Query:
SELECT customer_name, sales_region,
CASE WHEN sign_up_date >= CURDATE() - INTERVAL 6 MONTH THEN 'New'
ELSE 'Existing' END AS customer_category
FROM Customers;
Explanation: Customers who signed up within the last 6 months are labeled “New”, others “Existing”.
8. Monthly Sales Trend for the Last 12 Months
Query:
SELECT YEAR(sale_date) AS year, MONTH(sale_date) AS month, SUM(total_amount) AS total_sales
FROM Sales
WHERE sale_date >= CURDATE() - INTERVAL 12 MONTH
GROUP BY YEAR(sale_date), MONTH(sale_date)
ORDER BY year DESC, month DESC;
Explanation: Groups sales data by month and year to show trends over the past year.
9. Find Product Categories Generating Over $50,000 in Revenue in the Last 6 Months
Query:
SELECT p.category, SUM(s.total_amount) AS total_revenue
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
WHERE s.sale_date >= CURDATE() - INTERVAL 6 MONTH
GROUP BY p.category
HAVING total_revenue > 50000;
Explanation: Filters categories with revenue exceeding $50,000 in the past 6 months.
10. Identify Sales Where total_amount is Incorrect
Query:
SELECT * FROM Sales s
JOIN Products p ON s.product_id = p.product_id
WHERE s.total_amount <> (s.quantity * p.price);
Explanation: Checks for inconsistencies where total_amount does not match quantity * price.
Conclusion
Mastering SQL queries is crucial for data analysis and reporting. By practicing these queries, you can gain confidence in handling real-world database scenarios. Keep experimenting with different questions and optimize them for better performance!. See most asked Data Engineering Questions.
Did you find this guide helpful? Share it with your peers and let us know your thoughts in the comments! 🚀
