Hi, thank you for contacting JustAnswer.com. My name is Russell. I will do what I can to provide the right answer to your question.
Please state your (SQL) question. Give some details. Please mention if there is a time-frame for completion that you need to have fulfilled. Thanks.
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.
Is the 5PM time frame doable?
I'm sorry! such a time frame is not do-able. I would need more time, and you have delayed letting me know what the task is until near the last-minute/hour.
To give you a chance at being helped by another Expert that could help within that time frame, I have Opted Out, opening the case to other Experts.
For the benefit of the next Expert, please state what SQL language/type - e.g. MS SQL, MySQL, etc. - this code has to be done in. Thanks.
Ok - thanks for your help and advice Russell.