Part -1:
Assignment 5-5: Updating Order Status
Create a procedure named STATUS_SHIP_SP that allows an employee in the Shipping Department to update an order status to add shipping information. The BB_BASKETSTATUS table lists events for each order so that a shopper can see the current status. date, and comments as each stage of the order process is finished. The IDSTAGE column of the BB_BASKETSTATUS table identifies each stage: the value 3 in this column indicates that an order has been shipped.
The procedure should allow adding a row with an IDSTAGE of 3. date shipped. tracking number. and shipper. The st STATUS_SE0 sequence is used to provide a value for the primary key column. Test the procedure with the following information:
Basket # = 3
Date shipped = 20-FEB-12 Shipper = UPS
Tracking # = ZW2384YXM957
Assignment 5-6: Returning Order Status Information
Create a procedure that returns the most recent order status information for a specified basket. This procedure should determine the most recent ordering-stage entry in the BB_BASKETSTATUS table and return the data. Use an IF or CASE clause to return a stage description instead of an IDSTAGE number. which means little to shoppers. The IDSTAGE column of the BB_BASKETSTATUS table identifies each stage as follows:
• 1-Submitted and received
• 2-Confirmed, processed. sent to shipping
• 3-Shipped
• 4-Cancelled
• 5-Back-ordered
The procedure should accept a basket ID number and return the most recent status description and date the status was recorded. If no status is available for the specified basket ID, return a message stating that no status is available. Name the procedure STATUS_SP. Test the procedure twice with the basket ID 4 and then 6.
Assignment 5-7: Identifying Customers
Brewbean's wants to offer an incentive of free shipping to customers who haven't returned to the site since a specified date. Create a procedure named PROMO_SHIP_SP that determines who these customers are and then updates the BB_PROMOLIST table accordingly.
The procedure uses the following information:
• Date cutoff-Any customers who haven't shopped on the site since this date should be included as incentive participants. Use the basket creation date to reflect shopper activity dates.
• Month-A three-character month (such as APR) should be added to the promotion table to indicate which month free shipping is effective.
Year-A four-digit year indicates the year the promotion is effective.
promo_f lag-1 represents free shipping.
The BB_PROMOLIST table also has a USED column. which contains the default value N and is updated to Y when the shopper uses the promotion. Test the procedure with the cutoff date 15-FEB-12. Assign free shipping for the month APR and the year 2012.
Assignment 5-8: Adding Items to a Basket
As a shopper selects products on the Brewbean's site. a procedure is needed to add a newly selected item to the current shoppers basket. Create a procedure named BASKET_ADD_SP that accepts a product ID. basket ID. price. quantity. size code option (1 or 2). and form code option (3 or 4) and uses this information to add a new item to the BB_BASKETITEM table. The tables PRIMARY KEY column is generated by BB_IDBASKETITF-v._SEQ. Run the procedure with the following values:
• Basket ID-14
• Product ID-8
• Price-10.80
• Quantity-1
• Size code-2
• Form code-4
Assignment 5-9: Creating a Logon Procedure
The home page of the Brewbean's Web site has an option for members to log on with their IDs and passwords. Develop a procedure named MEMBER_CIC_SP that accepts the ID and password as inputs. checks whether they make up a valid logon. and returns the member name and cookie value. The name should be returned as a single text string containing the first and last name.
The head developer wants the number of parameters minimized so that the same parameter is used to accept the password and return the name value. Also, if the user doesn't enter a valid username and password. return the value INVALID in a parameter named p_check. Test the procedure using a valid logon first. with the username rat 55 and password kilo. Then try it with an invalid logon by changing the usemame to rat.
Hands-On Assignments
Assignment 5-10: Returning a Record
Create a procedure named DDPROJ_SP that retrieves project information for a specific project based on a project ID. The procedure should have two parameters: one to accept a project ID value and another to return all data for the specified project. Use a record variable to have the procedure return all database column values for the selected project. Test the procedure with an anonymous block.
Assignment 5-11: Creating a Procedure
Create a procedure named DDPAY_SP that identities whether a donor currently has an active pledge with monthly payments. A donor ID is the input to the procedure. Using the donor ID. the procedure needs to determine whether the donor has any currently active pledges based on the status field and is on a monthly payment plan. If so. the procedure is to return the Boolean value TRUE. Othenvise. the value FALSE should be returned. Test the procedure with an anonymous block.
Assignment 5-12: Creating a Procedure
Create a procedure named DDCRPAY_SP that confirms whether a monthly pledge payment is the correct amount. The procedure needs to accept two values as input: a payment amount and a pledge ID. Based on these inputs. the procedure should confirm that the payment is the correct monthly increment amount. based on pledge data in the database. If it isn't. a custom Oracle error using error number 20050 and the message "Incorrect payment amount - planned payment = ??" should be raised. The ?? should be replaced by the correct payment amount. The database query in the procedure should be formulated so that no rows are returned if the pledge isn't on a monthly payment plan or the pledge isn't found. If the query returns no rows. the procedure should display the message "No payment information: Test the procedure with the pledge ID 104 and the payment amount 525. Then test with the same pledge ID but the payment amount 520. Finally. test the procedure with a pledge ID for a pledge that doesn't have monthly payments associated with it.
Assignment 5-13: Creating a Procedure
Create a procedure named DDCKBAL_SP that verifies pledge payment information. The procedure should accept a pledge ID as input and return three values for the specified pledge: pledge amount. payment total to date, and remaining balance. Test the procedure with an anonymous block.
1. Develop and run a CREATE FUNCTION statement to create the Ntaf_PURCH_SP 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.
Part -2:
Assignment 6-4: 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 6-5: 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_SP 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 6-6: Adding Descriptions for Order Status Codes
When a shopper returns to the Web site to check an order's status. information from the BB_8ASKETSTATUS table is displayed. However, only the status code is available in the BB_BASKETSTATUS table, not the status description. Create a function earned STATUS_DESC_SF that accepts a stage ID and returns the status desorption. The descriptions for stage IDs are listed in Table 6-3. Test the function in a SELECT statement that retrieves al rows in the BB_BASKETSTATUS table for basket a and displays the stage ID and its desorption.
TABLE 6-3 Basket Stage Descriptions
| Stage ID |
Description |
| 1 |
Order submitted |
| 2 |
Accepted, tent to shipping |
| 3 |
Back-ordered |
| 4 |
Cancelled |
| 5 |
Shipped |
Assignment 6-7: Calculating an Orders 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 adv. The tax is determined by the shipping state. Mach 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 haled 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-8: 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 an sale. the function should return tie vas Great Deal. These values are used on the product display page. Create a function named cx_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 sting 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.
Hands-On Assignments
Assignment 6-9: Determining the Monthly Payment Amount
Create a function named DD MTHPAY SF that calculates and returns the monthly payment amount for donor pledges paid on a monthly basis. Input values should be the number of monthly payments and the pledge amount. Use the function in an anonymous PUSQL block to show its use with the following pledge information: pledge amount = $240 and monthly payments = 12. Also, use the function in an SQL statement that displays information for all donor pledges in the database on a monthly payment plan.
Assignment 6-10: Calculating the Total Project Pledge Amount
Create a function named DD PROJTOT SF that determines the total pledge amount for a project. Use the function in an SQL statement that lists all projects, displaying project ID, project name, and project pledge total amount. Format the pledge total to display zero if no pledges have been made so far, and have it show a dollar sign, comma, and two decimal places for dollar values.
Assignment 6-11: Identifying Pledge Status
The DoGood Donor organization decided to reduce SQL join activity in its application by eliminating the DD STATUS table and replacing it with a function that returns a status description based on the status ID value. Create this function and name it DD PLSTAT SF. Use the function in an SQL statement that displays the pledge ID, pledge date, and pledge status for all pledges. Also, use it in an SQL statement that displays the same values but for only a specified pledge.
Attachment:- Tables and data.pdf