I'm looking to get a few questions answered on creating some SQL procedures for a database. The database along with data can be found here: http://wikisend.com/download/205022/database_info.txt
Completion time-frame is today at 5PM.
1. New customer registration: create a new customer (with name, email address as user name, password, home address, phone number). The system needs to check whether the email address exists in the database. If so (the customer already registered), the procedure will tell the customer he or she has already registered. Otherwise the system needs to create a new customer in the database and store provided information. The procedure needs to print out a message indicating the result (registration is successful, or registration fails for what reason).
2. Allow a customer to search for cellphones in a given price range. The input is the price range and the procedure prints out a list of cellphones within the price range and also displays brand, model, price, operating system, screen size, camera resolution, network, RAM size, storage size, battery, and color, and the number of that type of phones in stock.
3. Allow a customer to place an order for a cell phone. The input is the Product ID of the phone, quantity, shipping method, credit card number and expiration date. The procedure will first check whether the product ID exists and whether there is enough in stock (i.e., quantity <= number in stock). If not, please print out that there is not enough in stock and return. Otherwise, the procedure creates a new order and computes total due, which equals (price of phone*quantity + shipping cost)*1.06 (assuming 6% sales tax). Each order will have a unique order ID. Please print out the order date (you can use sysdate), order ID, model of the phone being ordered, quantity, shipping mode, and total due. Please also update the number of this type of phones in stock accordingly (by subtracting the order quantity from the current number in stock).
4. Update the shipping status of an order. The input is the order ID and new shipping status. Please also update the shipping date if the shipping status is updated from ‘not shipped yet’ to ‘shipped’ (you can use sysdate as shipping date).
5. Allow a customer to check the shipping status of an order. The input is the order ID and the procedure prints out the shipping status and shipping date (if available). Please handle the case when the order ID is invalid.