Project Scenario: Sales Analysis System
Scenario:
The management team at AdventureWorks wants a Sales Analysis System to analyze sales
trends and manage product prices. They have outlined the following requirements:
1. Create a view to display all sales orders along with customer names, product names,
and order totals.
2. Create a stored procedure to update product prices, ensuring that the new price is not
lower than 50% of the product's current price.
3. Write a query to retrieve data from the view for analysis.
4. Execute the procedure to update the price of a specific product.
AdventureWorks Sample Tables
Tables to Use:
● [Link]: Contains information about each sales order.
● [Link]: Contains details of the products in each order.
● [Link]: Contains customer details.
● [Link]: Contains product details.
1. Create the View
View Requirement:
The view should display the following details:
● Sales Order ID
● Customer Name (FirstName + LastName)
● Product Name
● Quantity Ordered
● Total Price for the Product (OrderQty * UnitPrice)
CREATE VIEW vw_SalesOrderDetails AS
SELECT
[Link],
CONCAT([Link], ' ', [Link]) AS CustomerName,
[Link] AS ProductName,
[Link],
[Link],
([Link] * [Link]) AS TotalPrice
FROM
[Link] soh
JOIN
[Link] c
ON
[Link] = [Link]
JOIN
[Link] sod
ON
[Link] = [Link]
JOIN
[Link] p
ON
[Link] = [Link];
2. Create the Stored Procedure
Procedure Requirement:
Create a stored procedure to update a product's price (ListPrice) in the [Link]
table. Ensure the new price is not less than 50% of the current price. If the new price is too low,
display an error message.
CREATE PROCEDURE UpdateProductPrice
@ProductID INT,
@NewPrice DECIMAL(10, 2)
AS
BEGIN
-- Declare a variable to hold the current price
DECLARE @CurrentPrice DECIMAL(10, 2);
-- Fetch the current price
SELECT @CurrentPrice = ListPrice
FROM [Link]
WHERE ProductID = @ProductID;
-- Check if the new price is valid
IF @NewPrice < (@CurrentPrice * 0.5)
BEGIN
PRINT 'Error: New price cannot be less than 50% of the current
price.';
RETURN;
END
-- Update the price if valid
UPDATE [Link]
SET ListPrice = @NewPrice
WHERE ProductID = @ProductID;
PRINT 'Product price updated successfully.';
END;
3. Query the View
Requirement:
Query the view vw_SalesOrderDetails to retrieve all sales orders for a specific customer,
sorted by TotalPrice in descending order.
SELECT *
FROM vw_SalesOrderDetails
WHERE CustomerName = 'John Doe'
ORDER BY TotalPrice DESC;
4. Execute the Stored Procedure
Requirement:
Update the price of a product with ProductID = 680 to $50.00 using the stored procedure. If
the new price violates the 50% rule, the procedure should reject the update.
EXEC UpdateProductPrice
@ProductID = 680,
@NewPrice = 50.00;
