Assignment 6-2: Calculating a Shopper’s Total Spending Many of the reports gener
ID: 3734634 • Letter: A
Question
Assignment 6-2: Calculating a Shopper’s Total Spending Many of the reports generated from the system calculate the total dollars in a shopper’s purchases. Follow these steps to create a function named TOT_PURCH_SF that accepts a shopper ID as input and returns the total dollars the shopper has spent with Brewbean’s. Use the function in a SELECT statement that shows the shopper ID and total purchases for every shopper in the database.
1. Develop and run a CREATE FUNCTION statement to create the TOT_PURCH_SF function. The function code needs a formal parameter for the shopper ID and to sum the TOTAL column of the BB_BASKET table.
2. Develop a SELECT statement, using the BB_SHOPPER table, to produce a list of each shopper in the database and his or her total purchases.
Explanation / Answer
Note: Those mentioned within the square brackets i.e., '[]', are optional. Also, details with respect to the columns and data type of columns are not clearly specified.
1. Create function as follows -
CREATE FUNCTION [ schema_name. ] TOT_PURCH_SF ( [ { @SHOPPER_ID [ AS ][ type_schema_name. ] text [ = default ] [ READONLY ] } ] )RETURNS total
[ AS ] BEGIN
END [ ; ]
2. go
select [ schema_name. ] TOT_PURCH_SF (id) ,id from bb_basket where id in (select distinct id from bb_shopper)
Hope this was helpful, All the best :)