Assignment 1: Calculating a Shopper's Total Number of Orders
Another commonly used statistic in reports is the total number of orders a shopper has placed. Follow these steps to create a function named NUM_PURCH_SF that accepts a shopper ID and returns a shopper's total number of orders. Use the function in a SELECT statement to display the number of orders for shopper 23. 1. Develop and run a CREATE FUNCTION statement to create the NUM_PURCH_SF function. The function code needs to tally the number of orders (using an Oracle built-in function) by shopper. Keep in mind that the ORDERPLACED column contains a 1 if an order has been placed. 2. Create a SELECT query by using the NUM_PURCH_SF function on the IDSHOPPER column of the BB_SHOPPER table. Be sure to select only shopper 23.
Assignment 2: Identifying the Weekday for an Order Date
The day of the week that baskets are created is often analyzed to determine consumer shopping patterns. Create a function named DAY_ORD_SF that accepts an order date and returns the weekday. Use the function in a SELECT statement to display each basket ID and the weekday the order was created. Write a second SELECT statement, using this function to display the total number of orders for each weekday. (Hint: Call the TO_CHAR function to retrieve the weekday from a date.) 1. Develop and run a CREATE FUNCTION statement to create the DAY_ORD_SF function. Use the DTCREATED column of the BB_BASKET table as the date the basket is created. Call the TO_CHAR function with the DAY option to retrieve the weekday for a date value. 2. Create a SELECT statement that lists the basket ID and weekday for every basket. 3. Create a SELECT statement, using a GROUP BY clause to list the total number of baskets per weekday. Based on the results, what's the most popular shopping day?
Assignment 3: Calculating Days Between Ordering and Shipping
An analyst in the quality assurance office reviews the time elapsed between receiving an order and shipping the order. Any orders that haven't been shipped within a day of the order being placed are investigated. Create a function named ORD_SHIP_SF that calculates the number of days between the basket's creation date and the shipping date. The function should return a character string that states OK if the order was shipped within a day or CHECK if it wasn't. If the order hasn't shipped, return the string Not shipped. The IDSTAGE column of the BB_BASKETSTATUS table indicates a shipped item with the value 5, and the DTSTAGE column is the shipping date. The DTORDERED column of the BB_BASKET table is the order date. Review data in the BB_BASKETSTATUS table, and create an anonymous block to test all three outcomes the function should handle.
Assignment 4: Adding Descriptions for Order Status Codes
When a shopper returns to the Web site to check an order's status, information from the BB_BASKETSTATUS table is displayed. However, only the status code is available in the BB_BASKETSTATUS table, not the status description. Create a function named STATUS_DESC_SF that accepts a stage ID and returns the status description. The descriptions for stage IDs are listed in Table 6-3. Test the function in a SELECT statement that retrieves all rows in the BB_BASKETSTATUS table for basket 4 and displays the stage ID and its description.
Assignment 5: Calculating an Order's Tax Amount
Create a function named TAX_CALC_SF that accepts a basket ID, calculates the tax amount by using the basket subtotal, and returns the correct tax amount for the order. The tax is determined by the shipping state, which is stored in the BB_BASKET table. The BB_TAX table contains the tax rate for states that require taxes on Internet purchases. If the state isn't listed in the tax table or no shipping state is assigned to the basket, a tax amount of zero should be applied to the order. Use the function in a SELECT statement that displays the shipping costs for a basket that has tax applied and a basket with no shipping state.
Assignment 6: Identifying Sale Products
When a product is placed on sale, Brewbean's records the sale's start and end dates in columns of the BB_PRODUCT table. A function is needed to provide sales information when a shopper selects an item. If a product is on sale, the function should return the value ON SALE!. However, if it isn't on sale, the function should return the value Great Deal!. These values are used on the product display page. Create a function named CK_SALE_SF that accepts a date and product ID as arguments, checks whether the date falls within the product's sale period, and returns the corresponding string value. Test the function with the product ID 6 and two dates: 10-JUN-12 and 19-JUN-12. Verify your results by reviewing the product sales information.