Scribd
Upload
6 views4 pages

Sales Analysis System for AdventureWorks

Uploaded by

Megha Sharma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views4 pages

Sales Analysis System for AdventureWorks

Uploaded by

Megha Sharma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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;

Common questions

Powered by AI

Without a procedure to restrict product price reductions, a sales data analysis system could face issues like revenue loss from pricing below sustainable levels, damage to customer perception from inconsistent pricing, and decreased competitive advantage. It could also contribute to financial discrepancies and errors in profit calculations, leading to strategic and financial misalignments .

Parameterized queries enhance both the security and efficiency of SQL procedures. In the AdventureWorks system, using parameters like '@ProductID' and '@NewPrice' in the stored procedure prevents SQL injection attacks by treating inputs as parameters instead of executable code, ensuring the server processes them as intended. They also optimize query execution plans by allowing the server to reuse plans for different parameter values, improving performance and reliability .

JOIN operations in the 'vw_SalesOrderDetails' view are crucial as they are used to amalgamate necessary data from multiple tables: SalesOrderHeader, Customer, SalesOrderDetail, and Product. These operations ensure that each piece of sales data is linked with the corresponding customer and product information, enabling a comprehensive view that combines all aspects of a sales transaction into one accessible format .

Sorting by 'TotalPrice' when querying sales data is beneficial as it allows the management to quickly identify and analyze the most significant sales transactions. This prioritization can inform strategic decisions about customer relationships and pricing strategies by highlighting which orders contribute most to revenue for a specific customer .

The Sales Analysis System at AdventureWorks ensures compliance with company price update policies by implementing a stored procedure that checks whether the new price for a product is not less than 50% of the current price. If a proposed new price is too low, an error message is displayed, and the update is prevented .

The AdventureWorks system effectively uses SQL joins, concatenation, calculated fields, and views to display a customer's order history. It combines data from several tables via JOIN operations, concatenates fields for readability, calculates total prices, and consolidates this information into a single view with defined structure ('vw_SalesOrderDetails') for easy access and analysis .

The use of the 'vw_SalesOrderDetails' view in the AdventureWorks database facilitates sales analysis by providing a simplified, organized presentation of relevant sales data, including sales order details, customer information, and financial calculations. This enables easier querying and analysis without needing to manually join and filter the raw data each time .

The creation of the 'vw_SalesOrderDetails' view involves the following key steps: selecting the necessary fields from the SalesOrderHeader, Customer, SalesOrderDetail, and Product tables, joining these tables appropriately on keys, and concatenating the customer's first and last names to form 'CustomerName'. It calculates the total price by multiplying the order quantity by the unit price and selects these final fields for the view to satisfy the project requirements .

The logic within the stored procedure performs several operations: it first retrieves the current product price, then checks for rule compliance (new price must be at least 50% of the current price). If non-compliance occurs, it triggers an error message and halts further execution to prevent incorrect updates. If the conditions are met, it proceeds to update the product price and confirms success with a message .

The stored procedure in the AdventureWorks system safeguards against incorrect price updates by declaring a variable to hold the current price, then checking if the new price is at least 50% of this current price. If the condition is not met, it prints an error message and aborts the update process to maintain data integrity .

You might also like