50 SQL Exercise Questions

This collection of 50 SQL exercises offers a comprehensive set of

questions designed to challenge and enhance our SQL skills. Covering a
wide range of topics, from basic queries to advanced database
management techniques, these exercises will help us gain practical
experience in working with relational databases.

1. Sales Table
The Sales table records information about product sales, including
the quantity sold, sale date, and total price for each sale. It serves as a
transactional data source for analyzing sales trends.
Query:
-- Create Sales table

CREATE TABLE Sales (

sale_id INT PRIMARY KEY,
product_id INT,
quantity_sold INT,
sale_date DATE,
total_price DECIMAL(10, 2),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

-- Insert sample data into Sales table

INSERT INTO Sales (sale_id, product_id, quantity_sold, sale_date,

total_price) VALUES
(1, 101, 5, '2024-01-01', 2500.00),
(2, 102, 3, '2024-01-02', 900.00),
(3, 103, 2, '2024-01-02', 60.00),
(4, 104, 4, '2024-01-03', 80.00),
(5, 105, 6, '2024-01-03', 90.00);
Output:

Sales Table
2. Products Table
The Products table contains details about products, including
their names, categories, and unit prices. It provides reference data for
linking product information to sales transactions.
Query:
-- Create Products table

CREATE TABLE Products (

product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
unit_price DECIMAL(10, 2)
);

-- Insert sample data into Products table

INSERT INTO Products (product_id, product_name, category,

unit_price) VALUES
(101, 'Laptop', 'Electronics', 500.00),
(102, 'Smartphone', 'Electronics', 300.00),
(103, 'Headphones', 'Electronics', 30.00),
(104, 'Keyboard', 'Electronics', 20.00),
(105, 'Mouse', 'Electronics', 15.00);
Output:

Products Table
SQL Practice Exercises for Beginners
This section provides practical SQL practice exercises for beginners,
focusing on fundamental operations such as SELECT, INSERT, UPDATE,
and DELETE. The exercises utilize a schema with tables
like Sales and Products to demonstrate how to retrieve, modify, and
manage data. These hands-on examples are designed to build a strong
foundation in SQL and prepare us for real-world database tasks.
1. Retrieve all columns from the Sales table.
Query:
SELECT * FROM Sales;
Output:
sale_id product_id quantity_sold sale_date total_price

1 101 5 2024-01-01 2500.00

2 102 3 2024-01-02 900.00

3 103 2 2024-01-02 60.00

4 104 4 2024-01-03 80.00

5 105 6 2024-01-03 90.00

Explanation:
This SQL query selects all columns from the Sales table, denoted by
the asterisk (*) wildcard. It retrieves every row and all associated columns
from the Sales table.
2. Retrieve the product_name and unit_price from the Products
table.
Query:
SELECT product_name, unit_price FROM Products;
Output:
product_name unit_price

Laptop 500.00

Smartphone 300.00

Headphones 30.00

Keyboard 20.00

Mouse 15.00

Explanation:
This SQL query selects the product_name and unit_price columns from
the Products table. It retrieves every row but only the specified columns,
which are product_name and unit_price.
3. Retrieve the sale_id and sale_date from the Sales table.
Query:
SELECT sale_id, sale_date FROM Sales;
Output:
sale_id sale_date

1 2024-01-01

2 2024-01-02

3 2024-01-02

4 2024-01-03

5 2024-01-03

Explanation:
This SQL query selects the sale_id and sale_date columns from the Sales
table. It retrieves every row but only the specified columns, which are
sale_id and sale_date.
4. Filter the Sales table to show only sales with a total_price
greater than $100.
Query:
SELECT * FROM Sales WHERE total_price > 100;
Output:
sale_id product_id quantity_sold sale_date total_price

1 101 5 2024-01-01 2500.00

2 102 3 2024-01-02 900.00

Explanation:
This SQL query selects all columns from the Sales table but only returns
rows where the total_price column is greater than 100. It filters out sales
with a total_price less than or equal to $100.
5. Filter the Products table to show only products in the
'Electronics' category.
Query:
SELECT * FROM Products WHERE category = 'Electronics';
Output:
product_id product_name category unit_price

101 Laptop Electronics 500.00

102 Smartphone Electronics 300.00

103 Headphones Electronics 30.00

104 Keyboard Electronics 20.00

105 Mouse Electronics 15.00

Explanation:
This SQL query selects all columns from the Products table but only
returns rows where the category column equals 'Electronics'. It filters out
products that do not belong to the 'Electronics' category.
6. Retrieve the sale_id and total_price from the Sales table for
sales made on January 3, 2024.
Query:
SELECT sale_id, total_price
FROM Sales
WHERE sale_date = '2024-01-03';
Output:
sale_id total_price

4 80.00

5 90.00

Explanation:
This SQL query selects the sale_id and total_price columns from the Sales
table but only returns rows where the sale_date is equal to '2024-01-03'.
It filters out sales made on any other date.
7. Retrieve the product_id and product_name from the Products
table for products with a unit_price greater than $100.
Query:
SELECT product_id, product_name
FROM Products
WHERE unit_price > 100;
Output:
product_id product_name

101 Laptop

102 Smartphone

Explanation:
This SQL query selects the product_id and product_name columns from
the Products table but only returns rows where the unit_price is greater
than $100. It filters out products with a unit_price less than or equal to
$100.
8. Calculate the total revenue generated from all sales in the
Sales table.
Query:
SELECT SUM(total_price) AS total_revenue
FROM Sales;

total_revenue

3630.00

Explanation:
This SQL query calculates the total revenue generated from all sales by
summing up the total_price column in the Sales table using
the SUM() function.
9. Calculate the average unit_price of products in the Products
table.
Query:
SELECT AVG(unit_price) AS average_unit_price
FROM Products;
Output:
average_unit_price

173

Explanation:
This SQL query calculates the average unit_price of products by averaging
the values in the unit_price column in the Products table using
the AVG() function.
10. Calculate the total quantity_sold from the Sales table.
Query:
SELECT SUM(quantity_sold) AS total_quantity_sold
FROM Sales;
Output:
total_quantity_sold

20

Explanation:
This SQL query calculates the total quantity_sold by summing up the
quantity_sold column in the Sales table using the SUM() function.
11. Count Sales Per Day from the Sales table
Query:
SELECT sale_date, COUNT(*) AS sales_count
FROM Sales
GROUP BY sale_date
ORDER BY sale_date;
Output:
sale_date sales_count

2024-01-01 1

2024-01-02 2

2024-01-03 2

Explanation:
This query groups sales by date and counts the number of transactions
per day, enabling analysis of daily sales patterns.
12. Retrieve product_name and unit_price from the Products
table with the Highest Unit Price
Query:
SELECT product_name, unit_price
FROM Products
ORDER BY unit_price DESC
LIMIT 1;
Output:
product_name unit_price

Laptop 500.00

Explanation:
This query sorts the Products table by unit_price in descending order and
retrieves the product with the highest price using the LIMIT clause.
13. Retrieve the sale_id, product_id, and total_price from the
Sales table for sales with a quantity_sold greater than 4.
Query:
SELECT sale_id, product_id, total_price
FROM Sales
WHERE quantity_sold > 4;
Output:
sale_id product_id total_price

1 101 2500.00

5 105 90.00

Explanation:
This SQL query selects the sale_id, product_id, and total_price columns
from the Sales table but only returns rows where the quantity_sold is
greater than 4.
14. Retrieve the product_name and unit_price from the
Products table, ordering the results by unit_price in descending
order.
Query:
SELECT product_name, unit_price
FROM Products
ORDER BY unit_price DESC;
Output:
product_name unit_price

Laptop 500.00

Smartphone 300.00

Headphones 30.00

Keyboard 20.00

Mouse 15.00

Explanation:
This SQL query selects the product_name and unit_price columns from the
Products table and orders the results by unit_price in descending order
using the ORDER BY clause with the DESC keyword.
15. Retrieve the total_price of all sales, rounding the values to
two decimal places.
Query:
SELECT ROUND(SUM(total_price), 2) AS total_sales
FROM Sales;
Output:
total_sales

3630.00

Explanation:
This SQL query calculates the total sales revenu by summing up the
total_price column in the Sales table and rounds the result to two
decimal places using the ROUND() function.
16. Calculate the average total_price of sales in the Sales table.
Query:
SELECT AVG(total_price) AS average_total_price
FROM Sales;
Output:
average_total_price

726.000000

Explanation:
This SQL query calculates the average total_price of sales by averaging
the values in the total_price column in the Sales table using the AVG()
function.
17. Retrieve the sale_id and sale_date from the Sales table,
formatting the sale_date as 'YYYY-MM-DD'.
Query:
SELECT sale_id, DATE_FORMAT(sale_date, '%Y-%m-%d') AS
formatted_date
FROM Sales;
Output:
sale_id formatted_date

1 2024-01-01

2 2024-01-02

3 2024-01-02

4 2024-01-03

5 2024-01-03

Explanation:
This SQL query selects the sale_id and sale_date columns from the Sales
table and formats the sale_date using the DATE_FORMAT() function to
display it in 'YYYY-MM-DD' format.
18. Calculate the total revenue generated from sales of
products in the 'Electronics' category.
Query:
SELECT SUM(Sales.total_price) AS total_revenue
FROM Sales
JOIN Products ON Sales.product_id = Products.product_id
WHERE [Link] = 'Electronics';
Output:
total_revenue

3630.00

Explanation:
This SQL query calculates the total revenue generated from sales of
products in the 'Electronics' category by joining the Sales table with the
Products table on the product_id column and filtering sales for products in
the 'Electronics' category.
19. Retrieve the product_name and unit_price from the
Products table, filtering the unit_price to show only values
between $20 and $600.
Query:
SELECT product_name, unit_price
FROM Products
WHERE unit_price BETWEEN 20 AND 600;
Output:
product_name unit_price

Laptop 500.00

Smartphone 300.00

Headphones 30.00

Keyboard 20.00

Explanation:
This SQL query selects the product_name and unit_price columns from the
Products table but only returns rows where the unit_price falls within the
range of $20 and $600 using the BETWEEN operator.
20. Retrieve the product_name and category from the Products
table, ordering the results by category in ascending order.
Query:
SELECT product_name, category
FROM Products
ORDER BY category ASC;
Output:
product_name category

Laptop Electronics

Smartphone Electronics

Headphones Electronics

Keyboard Electronics

Mouse Electronics

Explanation:
This SQL query selects the product_name and category columns from the
Products table and orders the results by category in ascending order using
the ORDER BY clause with the ASC keyword.
SQL Practice Exercises for Intermediate
These intermediate-level exercises are tailored to challenge us
beyond basic queries by delving into more complex data manipulation
and analysis. By solving these problems, we'll strengthen our
understanding of advanced SQL concepts such
as joins, subqueries, aggregate functions, and window functions,
preparing us for handling real-world data scenarios.
1. Calculate the total quantity_sold of products in the
'Electronics' category.
Query:
SELECT SUM(quantity_sold) AS total_quantity_sold
FROM Sales
JOIN Products ON Sales.product_id = Products.product_id
WHERE [Link] = 'Electronics';
Output:
total_quantity_sold

20

Explanation:
This SQL query calculates the total quantity_sold of products in the
'Electronics' category by joining the Sales table with the Products table on
the product_id column and filtering sales for products in the 'Electronics'
category.
2. Retrieve the product_name and total_price from the Sales
table, calculating the total_price as quantity_sold multiplied by
unit_price.
Query:
SELECT product_name, quantity_sold * unit_price AS total_price
FROM Sales
JOIN Products ON Sales.product_id = Products.product_id;
Output:
product_name total_price

Laptop 2500.00

Smartphone 900.00

Headphones 60.00

Keyboard 80.00

Mouse 90.00

Explanation:
This SQL query retrieves the product_name from the Sales table and
calculates the total_price by multiplying quantity_sold by unit_price,
joining the Sales table with the Products table on the product_id column.
3. Identify the Most Frequently Sold Product from Sales table
Query:
SELECT product_id, COUNT(*) AS sales_count
FROM Sales
GROUP BY product_id
ORDER BY sales_count DESC
LIMIT 1;
Output:
product_id sales_count

101 1

Explanation:
This query counts the number of sales for each product (COUNT(*)) and
identifies the product with the highest sales count. It groups data
by product_id, orders it in descending order of sales, and limits the result
to the top record.
4. Find the Products Not Sold from Products table
Query:
SELECT product_id, product_name
FROM Products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM Sales);
Output:
product_id product_name

None None

Explanation:
This query identifies products from the Products table that do not have any
sales records in the Sales table by using a NOT IN subquery. It ensures a
thorough comparison to list unsold products.
5. Calculate the total revenue generated from sales for each
product category.
Query:
SELECT [Link], SUM(s.total_price) AS total_revenue
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY [Link];
Output:
category total_revenue

Electronics 3630.00

Explanation:
This query joins the Sales and Products tables on the product_id column,
groups the results by product category, and calculates the total revenue
for each category by summing up the total_price.
6. Find the product category with the highest average unit
price.
Query:
SELECT category
FROM Products
GROUP BY category
ORDER BY AVG(unit_price) DESC
LIMIT 1;
Output:
category

Electronics

Explanation:
This query groups products by category, calculates the average unit price
for each category, orders the results by the average unit price in
descending order, and selects the top category with the highest average
unit price using the LIMIT clause.
7. Identify products with total sales exceeding 30.
Query:
SELECT p.product_name
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY p.product_name
HAVING SUM(s.total_price) > 30;
Output:
product_name

Headphones

Keyboard

Laptop

Mouse
product_name

Smartphone

Explanation:
This query joins the Sales and Products tables on the product_id column,
groups the results by product name, calculates the total sales revenue
for each product, and selects products with total sales exceeding 30
using the HAVING clause.
8. Count the number of sales made in each month.
Query:
SELECT DATE_FORMAT(s.sale_date, '%Y-%m') AS month, COUNT(*) AS
sales_count
FROM Sales s
GROUP BY month;
Output:
month sales_count

2024-01 5

Explanation:
This query formats the sale_date column to extract the month and year,
groups the results by month, and counts the number of sales made in
each month.
9. Retrieve Sales Details for Products with 'Smart' in Their
Name
Query:
SELECT s.sale_id, p.product_name, s.total_price
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
WHERE p.product_name LIKE '%Smart%';
Output:
sale_id product_name total_price

2 Smartphone 900.00

Explanation:
This query uses a LIKE clause to match products with "Smart" in their
name, joining the Sales and Products tables to provide sales details for
these products.
10. Determine the average quantity sold for products with a
unit price greater than $100.
Query:
SELECT AVG(s.quantity_sold) AS average_quantity_sold
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
WHERE p.unit_price > 100;
Output:
average_quantity_sold

4.0000

Explanation:
This query joins the Sales and Products tables on the product_id column,
filters products with a unit price greater than $100, and calculates the
average quantity sold for those products.
11. Retrieve the product name and total sales revenue for each
product.
Query:
SELECT p.product_name, SUM(s.total_price) AS total_revenue
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY p.product_name;
Output:
product_name total_revenue

Laptop 2500.00

Smartphone 900.00

Headphones 60.00

Keyboard 80.00

Mouse 90.00
Explanation:
This query joins the Sales and Products tables on the product_id column,
groups the results by product name, and calculates the total sales
revenue for each product.
12. List all sales along with the corresponding product names.
Query:
SELECT s.sale_id, p.product_name
FROM Sales s
JOIN Products p ON s.product_id = p.product_id;
Output:
sale_id product_name

1 Laptop

2 Smartphone

3 Headphones

4 Keyboard

5 Mouse

Explanation:
This query joins the Sales and Products tables on the product_id column
and retrieves the sale_id and product_name for each sale.
13. Retrieve the product name and total sales revenue for each
product.
Query:
SELECT [Link],
SUM(s.total_price) AS category_revenue,
(SUM(s.total_price) / (SELECT SUM(total_price) FROM Sales))
* 100 AS revenue_percentage
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY [Link]
ORDER BY revenue_percentage DESC
LIMIT 3;
Output:
category category_revenue revenue_percentage

Electronics 3630.00 100.000000

Explanation:
This query will give you the top three product categories contributing to
the highest percentage of total revenue generated from sales. However,
if you only have one category (Electronics) as in the provided sample
data, it will be the only result.
14. Rank products based on total sales revenue.
Query:
SELECT p.product_name, SUM(s.total_price) AS total_revenue,
RANK() OVER (ORDER BY SUM(s.total_price) DESC) AS
revenue_rank
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY p.product_name;
Output:
product_name total_revenue revenue_rank

Laptop 2500.00 1

Smartphone 900.00 2

Mouse 90.00 3

Keyboard 80.00 4

Headphones 60.00 5

Explanation:
This query joins the Sales and Products tables on the product_id column,
groups the results by product name, calculates the total sales revenue for
each product, and ranks products based on total sales revenue using
the RANK() window function.
15. Calculate the running total revenue for each product
category.
Query:
SELECT [Link], p.product_name, s.sale_date,
SUM(s.total_price) OVER (PARTITION BY [Link] ORDER BY
s.sale_date) AS running_total_revenue
FROM Sales s
JOIN Products p ON s.product_id = p.product_id;
Output:
category product_name sale_date running_total_revenue

Electronics Laptop 2024-01-01 2500.00

Electronics Smartphone 2024-01-02 3460.00

Electronics Headphones 2024-01-02 3460.00

Electronics Keyboard 2024-01-03 3630.00

Electronics Mouse 2024-01-03 3630.00

Explanation:
This query joins the Sales and Products tables on the product_id column,
partitions the results by product category, orders the results by sale date,
and calculates the running total revenue for each product category using
the SUM() window function.
16. Categorize sales as "High", "Medium", or "Low" based on
total price (e.g., > $200 is High, $100-$200 is Medium, < $100
is Low).
Query:
SELECT sale_id,
CASE
WHEN total_price > 200 THEN 'High'
WHEN total_price BETWEEN 100 AND 200 THEN 'Medium'
ELSE 'Low'
END AS sales_category
FROM Sales;
Output:
sale_id sales_category

1 High
sale_id sales_category

2 High

3 Low

4 Low

5 Low

Explanation:
This query categorizes sales based on total price using a CASE statement.
Sales with a total price greater than $200 are categorized as "High", sales
with a total price between $100 and $200 are categorized as "Medium",
and sales with a total price less than $100 are categorized as "Low".
17. Identify sales where the quantity sold is greater than the
average quantity sold.
Query:
SELECT *
FROM Sales
WHERE quantity_sold > (SELECT AVG(quantity_sold) FROM Sales);
Output:
sale_id product_id quantity_sold sale_date total_price

1 101 5 2024-01-01 2500.00

5 105 6 2024-01-03 90.00

Explanation:
This query selects all sales where the quantity sold is greater than the
average quantity sold across all sales in the Sales table.
18. Extract the month and year from the sale date and count
the number of sales for each month.
Query:
SELECT CONCAT(YEAR(sale_date), '-', LPAD(MONTH(sale_date), 2, '0'))
AS month,
COUNT(*) AS sales_count
FROM Sales
GROUP BY YEAR(sale_date), MONTH(sale_date);
Output:
month sales_count

2024-01 5

Explanation:
This query extracts the year and month from the sale_date column
using YEAR() and MONTH(), formats them as YYYY-
MM using CONCAT() and LPAD() for proper padding, and counts the number
of sales (COUNT(*)) for each month.
19. Calculate the number of days between the current date and
the sale date for each sale.
Query:
SELECT sale_id, DATEDIFF(NOW(), sale_date) AS days_since_sale
FROM Sales;
Output:
sale_id days_since_sale

1 185

2 184

3 184

4 183

5 183

Explanation:
This query calculates the number of days between the current date and
the sale date for each sale using the DATEDIFF function.
20. Identify sales made during weekdays versus weekends.
Query:
SELECT sale_id,
CASE
WHEN DAYOFWEEK(sale_date) IN (1, 7) THEN 'Weekend'
ELSE 'Weekday'
END AS day_type
FROM Sales;
Output:
sale_id day_type

1 Weekday

2 Weekday

3 Weekday

4 Weekend

5 Weekend

Explanation:
This query categorizes sales based on the day of the week using the
DAYOFWEEK function. Sales made on Sunday (1) or Saturday (7) are
categorized as "Weekend", while sales made on other days are
categorized as "Weekday".
SQL Practice Exercises for Advanced
This advanced section focuses on complex SQL queries that utilize
advanced features such as window functions, self-joins, and intricate data
manipulation techniques. These exercises are designed to refine our SQL
skills further, enabling you to handle complex data analysis scenarios with
confidence and precision.
1. List the Top 3 Products by Revenue Contribution Percentage
Query:
SELECT p.product_name,
SUM(s.total_price) AS total_revenue,
(SUM(s.total_price) / (SELECT SUM(total_price) FROM Sales))
* 100 AS revenue_percentage
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY p.product_name
ORDER BY revenue_percentage DESC
LIMIT 3;
Output:
product_name total_revenue revenue_percentage

Laptop 2500.00 68.91

Smartphone 900.00 24.80

Mouse 90.00 2.48

Explanation:
This query calculates the revenue percentage for each product and lists
the top 3 products by their contribution to total revenue, using SUM() and
a subquery for total sales.
2. Write a query to create a view named Total_Sales that
displays the total sales amount for each product along with
their names and categories.
Query:
CREATE VIEW Total_Sales AS
SELECT p.product_name, [Link], SUM(s.total_price) AS
total_sales_amount
FROM Products p
JOIN Sales s ON p.product_id = s.product_id
GROUP BY p.product_name, [Link];
SELECT * FROM Total_Sales;
Output:
product_name category total_sales_amount

Laptop Electronics 2500.00

Smartphone Electronics 900.00

Headphones Electronics 60.00

Keyboard Electronics 80.00

Mouse Electronics 90.00

Explanation:
This query creates a view named Total_Sales that displays the total sales
3. Retrieve the product details (name, category, unit price) for
products that have a quantity sold greater than the average
quantity sold across all products.
Query:
SELECT product_name, category, unit_price
FROM Products
WHERE product_id IN (
SELECT product_id
FROM Sales
GROUP BY product_id
HAVING SUM(quantity_sold) > (SELECT AVG(quantity_sold) FROM
Sales)
);
Output:
product_name category unit_price

Laptop Electronics 500.00

Mouse Electronics 15.00

Explanation:
This query retrieves the product details (name, category, unit price) for
products that have a quantity sold greater than the average quantity sold
across all products.
4. Explain the significance of indexing in SQL databases and
provide an example scenario where indexing could significantly
improve query performance in the given schema.
Query:
-- Create an index on the sale_date column
CREATE INDEX idx_sale_date ON Sales (sale_date);

-- Query with indexing

SELECT *
FROM Sales
WHERE sale_date = '2024-01-03';
Output:
sale_id product_id quantity_sold sale_date total_price

4 104 4 2024-01-03 80.00

sale_id product_id quantity_sold sale_date total_price

5 105 6 2024-01-03 90.00

Explanation:
With an index on the sale_date column, the database can quickly locate
the rows that match the specified date without scanning the entire table.
The index allows for efficient lookup of rows based on the sale_date value,
resulting in improved query performance.
5. Add a foreign key constraint to the Sales table that
references the product_id column in the Products table.
Query:
ALTER TABLE Sales
ADD CONSTRAINT fk_product_id
FOREIGN KEY (product_id)
REFERENCES Products(product_id);
Output:
No output is generated, but the constraint is applied to the table.
Explanation:
This query adds a foreign key constraint to the Sales table that references
the product_id column in the Products table, ensuring referential integrity
between the two tables.
6. Create a view named Top_Products that lists the top 3
products based on the total quantity sold.
Query:
CREATE VIEW Top_Products AS
SELECT p.product_name, SUM(s.quantity_sold) AS total_quantity_sold
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_quantity_sold DESC
LIMIT 3;
Output:
product_name total_quantity_sold

Mouse 6

Laptop 5
product_name total_quantity_sold

Keyboard 4

Explanation:
This query creates a view named Top_Products that lists the top 3
products based on the total quantity sold.
7. Implement a transaction that deducts the quantity sold from
the Products table when a sale is made in the Sales table,
ensuring that both operations are either committed or rolled
back together.
Query:
START TRANSACTION; -- Begin the transaction

-- Deduct the quantity sold from the Products table

UPDATE Products p
JOIN Sales s ON p.product_id = s.product_id
SET p.quantity_in_stock = p.quantity_in_stock - s.quantity_sold;

-- Check if any negative quantities would result from the update

SELECT COUNT(*) INTO @negative_count
FROM Products
WHERE quantity_in_stock < 0;

-- If any negative quantities would result, rollback the

transaction
IF @negative_count > 0 THEN
ROLLBACK;
SELECT 'Transaction rolled back due to insufficient stock.' AS
Message;
ELSE
COMMIT; -- Commit the transaction if no negative quantities
would result
SELECT 'Transaction committed successfully.' AS Message;
END IF;

START TRANSACTION;
UPDATE Products SET quantity_in_stock = 10 WHERE product_id = 101;
INSERT INTO Sales (product_id, quantity_sold) VALUES (101, 5);
COMMIT;
Output:
Transaction committed successfully.
Explanation:
The quantity in stock for product with product_id 101 should be updated
to [Link] transaction should be committed successfully.
8. Create a query that lists the product names along with their
corresponding sales count.
Query:
SELECT p.product_name, COUNT(s.sale_id) AS sales_count
FROM Products p
LEFT JOIN Sales s ON p.product_id = s.product_id
GROUP BY p.product_name;
Output:
product_name sales_count

Headphones 1

Keyboard 1

Laptop 1

Mouse 1

Smartphone 1

Explanation:
This query selects the product names from the Products table and counts
the number of sales (using the COUNT() function) for each product by
joining the Sales table on the product_id. The results are grouped by
product name using the GROUP BY clause.
9. Write a query to find all sales where the total price is greater
than the average total price of all sales.
Query:
SELECT *
FROM Sales
WHERE total_price > (SELECT AVG(total_price) FROM Sales);
Output:
sale_id product_id quantity_sold sale_date total_price

1 101 5 2024-01-01 2500.00

sale_id product_id quantity_sold sale_date total_price

2 102 3 2024-01-02 900.00

Explanation:
The subquery (SELECT AVG(total_price) FROM Sales) calculates the
average total price of all sales. The main query selects all columns from
the Sales table where the total price is greater than the average total price
obtained from the subquery.
10. Analyze the performance implications of indexing the
sale_date column in the Sales table, considering the types of
queries commonly executed against this column.
Query:
-- Query without indexing
EXPLAIN ANALYZE
SELECT *
FROM Sales
WHERE sale_date = '2024-01-01';

-- Query with indexing

CREATE INDEX idx_sale_date ON Sales (sale_date);

EXPLAIN ANALYZE
SELECT *
FROM Sales
WHERE sale_date = '2024-01-01';
Output:
Query without Indexing:

Operation Details

Filter: (sales.sale_date = (cost=0.75 rows=1) (actual

DATE'2024-01-01') time=0.020..0.031 rows=1 loops=1)

(cost=0.75 rows=5) (actual

Table scan on Sales
time=0.015..0.021 rows=5 loops=1)

Query with Indexing:

 Operation Details

Index lookup on Sales using (cost=0.35 rows=1) (actual

idx_sale_date (sale_date=DATE'2024- time=0.024..0.024 rows=1
01-01') loops=1)

Explanation:
Without indexing, the query performs a full table scan, filtering rows
based on the sale date, which is less efficient. With indexing, the query
uses the index to quickly locate the relevant rows, significantly improving
query performance.
11. Add a check constraint to the quantity_sold column in the
Sales table to ensure that the quantity sold is always greater
than zero.
Query:
ALTER TABLE Sales
ADD CONSTRAINT chk_quantity_sold CHECK (quantity_sold > 0);

-- Query to check if the constraint is applied successfully

SELECT * FROM Sales;
Output:
sale_id product_id quantity_sold sale_date total_price

1 101 5 2024-01-01 2500.00

2 102 3 2024-01-02 900.00

3 103 2 2024-01-02 60.00

4 104 4 2024-01-03 80.00

5 105 6 2024-01-03 90.00

Explanation:
All rows in the Sales table meet the condition of the check constraint, as
each quantity_sold value is greater than zero.
12. Create a view named Product_Sales_Info that displays
product details along with the total number of sales made for
each product.
Query:
CREATE VIEW Product_Sales_Info AS
SELECT
p.product_id,
p.product_name,
[Link],
p.unit_price,
COUNT(s.sale_id) AS total_sales
FROM
Products p
LEFT JOIN
Sales s ON p.product_id = s.product_id
GROUP BY
p.product_id, p.product_name, [Link], p.unit_price;
Output:
product_id product_name category unit_price total_sales

101 Laptop Electronics 500.00 1

102 Smartphone Electronics 300.00 1

103 Headphones Electronics 30.00 1

104 Keyboard Electronics 20.00 1

105 Mouse Electronics 15.00 1

Explanation:
This view provides a concise and organized way to view product details
alongside their respective sales information, facilitating analysis and
reporting tasks.
13. Develop a stored procedure named Update_Unit_Price that
updates the unit price of a product in the Products table based
on the provided product_id.
Query:
DELIMITER //
CREATE PROCEDURE Update_Unit_Price (
IN p_product_id INT,
IN p_new_price DECIMAL(10, 2)
)
BEGIN
UPDATE Products
SET unit_price = p_new_price
WHERE product_id = p_product_id;
END //

DELIMITER ;
Output:
There is no direct output shown here as this is a stored procedure
definition
Explanation:
The above SQL code creates a stored procedure named
Update_Unit_Price. This stored procedure takes two parameters:
p_product_id (the product ID for which the unit price needs to be updated)
and p_new_price (the new unit price to set).
14. Implement a transaction that inserts a new product into the
Products table and then adds a corresponding sale record into
the Sales table, ensuring that both operations are either fully
completed or fully rolled back.
Query:
CREATE PROCEDURE Update_Unit_Price (
@product_id INT,
@new_unit_price DECIMAL(10, 2)
)
AS
BEGIN
UPDATE Products
SET unit_price = @new_unit_price
WHERE product_id = @product_id;
END;

EXEC Update_Unit_Price @product_id = 101, @new_unit_price = 550.00;

SELECT * FROM Products;
Output:
product_id product_name category unit_price

101 Laptop Electronics 550.00

product_id product_name category unit_price

102 Smartphone Electronics 300.00

103 Headphones Electronics 30.00

104 Keyboard Electronics 20.00

105 Mouse Electronics 15.00

Explanation:
This will update the unit price of the product with product_id 101 to
550.00 in the Products table.
15. Write a query that calculates the total revenue generated
from each category of products for the year 2024.
Query:
SELECT
[Link],
SUM(s.total_price) AS total_revenue
FROM
Sales s
JOIN
Products p ON s.product_id = p.product_id
WHERE
strftime('%Y', s.sale_date) = '2024'
GROUP BY
[Link];
Output:
category total_revenue

Electronics 3630.00

Explanation:
When you execute this query, you will get the total revenue generated
from each category of products for the year 2024.
More Questions For Practice
If you're looking to sharpen your SQL skills and gain more confidence
in querying databases, consider delving into these articles. They're
packed with query-based SQL questions designed to enhance your
understanding and proficiency in SQL.
By practicing with these exercises, you'll not only improve your SQL
abilities but also boost your confidence in tackling various database-
related tasks. The Questions are as follows:
• How to Insert a Value that Contains an Apostrophe in SQL?
• How to Select Row With Max Value in SQL?
• How to Efficiently Convert Rows to Columns in SQL?
• How To Use Nested Select Queries in SQL
• How to Select Row With Max Value on a Column in SQL?
• How to Specify Condition in Count() in SQL?
• How to Find the Maximum of Multiple Columns in SQL?
• How to Update Top 100 Records in SQL?
• How to Select the Last Records in a One-To-Many Relationship
Using SQL Join
• How to Join First Row in SQL?
• How to Insert Row If Not Exists in SQL?
• How to Use GROUP BY to Concatenate Strings in SQL?
• How Inner Join works in LINQ to SQL
• How to Get the Identity of an Inserted Row in SQL
• How to Declare a Variable in SQL?