DoGood Donor team requested data for the following queries. Write SQL statements to perform the following tasks:
- List each donor who is from Norfolk, VA. Include first name, last name, pledge date, and pledge amount.
- List each month in year 2012 and the total amount of pledge payments made in that month. Display the month name, year and the total amount.
- Find out the project, which had the highest number of credit card payments. Display the project id, name, project fund goal and the number of credit card pledge payments. (Hint: Use cc for the credit card pledge payment type.)
- Display the donors who have made more than one pledge. Include the donor ID, first name, last name and number of pledges.
- Display all pledges made before September 01, 2012. Include all column data from the DD_PLEDGE table.
The DoGood Donor application contains a page that allows administrators to change the ID assigned to a donor in the DD_DONOR table. Create a PL/SQL block to handle this task. Include exception-handling code to address an error raised by attempting to enter a duplicate donor ID. If this error occurs, display the message "This ID is already assigned." Test the code by changing donor ID 305. (Do not include a COMMIT statement; roll back any DML actions used.)
Brewbeans wants to offer an incentive of free shipping to customers who have not 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 have not 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_flag - 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.
When a Brewbeans 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 the table. 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.