Practical Questions for SQL Query
(100 Marks) - 2 Hours
SET 1 (10 Marks Each)
1. Create a table named Products with the following specifications:
product_id should be the primary key and an auto-incrementing number starting from 101.
product_name should not allow null values.
category should only accept values: 'Electronics', 'Home Appliances', or 'Furniture'.
unit_price should always be greater than 0.
And Insert the following records
Table Name: Products
[Link] a table named Sales with the following specifications:
sales_id should be the primary key and an auto-incrementing number starting from 1.
product_id should not allow null values.
Quantity_sold should only accept values between 1 to 100.
sales_date if the value to this filed will not be provided at the time of insert it will take the default value
as current date.
Total_price should not allow negative value
And Insert the following records
Table Name: Sales
SET 2(3 Marks Each)
Write SQL Query to:
3. Retrieve the total_price of all sales, rounding the values to two decimal places.
4. Calculate the total revenue generated from sales of products in the 'Electronics' category.
5. Retrieve the product_name and total_price from the Sales table, calculating the total_price as
quantity_sold multiplied by unit_price.
6. Identify the Most Frequently Sold Product from Sales table
7. Find the Products Not Sold from Products table
8. Calculate the total revenue generated from sales for each product category.
9. Find the product category with the highest average unit price.
10. Identify products with total sales exceeding 30
11. Count the number of sales made in each month.
12. Retrieve the product name and total sales revenue for each product.
13. Categorize sales as "High", "Medium", or "Low" based on total price (e.g., > $200 is High, $100-$200 is
Medium, < $100 is Low).
14. Calculate the number of days between the current date and the sale date for each sale.
15. Identify sales made during weekdays versus weekends.
16. Retrieve the product details (name, category, unit price) for products that have a quantity sold greater than
the average quantity sold across all products.
17. Add a foreign key constraint to the Sales table that references the product_id column in the Products table.
SET 3 (5 Marks Each)
18. Create a view named Product_Sales_Info that displays product details along with the total number of sales
made for each product.
19. 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.
20. 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.
21. Develop a stored procedure named sp_Sales_Insert that will take parameters as product_id and
quantity_sold. It will insert a record into the sales table.
22. Create a trigger which will delete all the transaction from the Sales table when we delete a record from
Products table.
SET 4 (10 Marks Each)
23. Write a stored procedure named InsertSalesRecord that performs the following tasks:
Input Parameters:
productName (name of the product being sold)
quantitySold (number of units sold)
The procedure should:
Check if the productName exists in the Products table. If the product does not exist, return an error
message: "Product not found in inventory."
If the product exists, retrieve the product_id and unit_price for that product.
Validate that quantitySold is within the range of 1 to 100. If not, return an error message: "Quantity
sold must be between 1 and 100."
Calculate the total_price as unit_price * quantitySold.
Insert a record into the Sales table with the product_id, quantity_sold, sales_date (default to the
current date if not provided), and total_price.
After successful insertion, the procedure should return a success message: "Sales record inserted
successfully."