SQL Exercises : SQL Practice with Solution for Beginners and Experienced

SQL (Structured Query Language) is a powerful and flexible tool for managing and manipulating relational databases. Regardless of our experience level, practising SQL exercises is essential for improving our skills. Regular practice not only enhances our understanding of SQL concepts but also builds confidence in tackling various database challenges effectively.

In this free SQL Exercises page, we offer a comprehensive collection of practice problems covering a wide range of topics. These exercises serve beginners, intermediate, and advanced learners, providing hands-on experience with SQL tasks. We'll work on everything from basic data retrieval and filtering to advanced topics like joins, window functions, and stored procedures.

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:

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:

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:

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:

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:

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:

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:

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:

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:

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;

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:

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:

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:

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:

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:

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:

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:

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:

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:

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 Products.category = 'Electronics';

Output:

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:

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:

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 Products.category = 'Electronics';

Output:

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:

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:

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:

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 p.category, SUM(s.total_price) AS total_revenue
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY p.category;

Output:

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:

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:

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:

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:

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:

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:

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:

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 p.category, 
       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 p.category
ORDER BY revenue_percentage DESC
LIMIT 3;

Output:

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:

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 p.category, p.product_name, s.sale_date, 
       SUM(s.total_price) OVER (PARTITION BY p.category ORDER BY s.sale_date) AS running_total_revenue
FROM Sales s
JOIN Products p ON s.product_id = p.product_id;

Output:

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:

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:

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:

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:

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:

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:

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, p.category, 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, p.category;
SELECT * FROM Total_Sales;

Output:

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:

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:

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:

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 5.The 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:

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:

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:

Query with Indexing:

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:

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,
    p.category,
    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, p.category, p.unit_price;

Output:

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:

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 
    p.category,
    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 
    p.category;

Output:

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?

Conclusion

Mastering SQL requires consistent practice and hands-on experience. By working through these SQL practice exercises, we'll enhance our skills and build confidence in querying and managing relational databases. Whether we're a beginner or an experienced professional refining your expertise, these exercises offer valuable opportunities to strengthen your SQL abilities. Keep practicing, and you'll be well-prepared to tackle real-world data challenges effectively with SQL.