I have the following tables created:
CREATE TABLE Orders
(OrderID int
Constraint Order_ID_pk primary key
, CustomerID nchar (5)
, EmployeeID int
, OrderDate datetime
, RequiredDate datetime
, ShippedDate datetime
, ShipVia int
, Freight money
, ShipName nvarchar (40)
, ShipAddress nvarchar (60)
, ShipCity nvarchar (15)
, ShipRegion nvarchar (15)
, ShipPostalCode nvarchar (10)
, ShipCountry nvarchar (15)
)
CREATE TABLE Order_Details
(OrderID int NOT NULL
, ProductID int NOT NULL
, UnitPrice money NOT NULL
, Quantity smallint NOT NULL
, Discount real NOT NULL
, constraint Order_Details_PK
Primary key (OrderID, ProductID)
)
and I need to create a Transact SQL procedure, customer_activity, that would, for a given CustomerID, return the number of orders that customer has made, average amount of all the customer orders, and the maximum customers order. The CustomerID should be the stored procedures input parameter. The stored procedure should use the view customer_orders.
Can you tell me where i went wrong?
Create procedure customer_activity (@customerID nchar(5))
as
select sum(orders) as Sum_Customer_Orders, max(orders)as Max_Customer_Orders, avg(orders) as Avg_Customer_Orders
from Order_Details inner join Orders on
Order_Details.OrderID = Orders.OrderID
where customerid = @customerID
the view i created is this:
Create view customer_orders
as
select sum(Quantity * UnitPrice * (1.00-Discount)) as OrderAmount, order_details.OrderID, orders.CustomerID
from Order_Details inner join Orders on
Order_Details.OrderID = Orders.OrderID
group by order_details.orderID, orders.customerid
1. how would i populate records in those tables to test the view and procedure.
2. how would i create a trigger for any order entered (inserted), print the order amount as well as customer average and max order using view and store procedure above
3. I would like to know how to test the trigger running the transaction that would insert an order (one row in the orders table) with three order details.
your assistance is appreciated.
Start Free Trial
