How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Scott Your Own Question
Scott, MIT Graduate
Category: Homework
Satisfied Customers: 3040
Experience:  MIT Graduate (Math, Programming, Science, and Music)
Type Your Homework Question Here...
Scott is online now
A new question is answered every 9 seconds


Customer Question


Submitted: 1 year ago.
Category: Homework
Expert:  Russell H. replied 1 year ago.

Hi, thank you for contacting 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.

Customer: replied 1 year ago.

Hi Russell,

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:

Completion time-frame is today at 5PM.

SQL Questions:

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.

Customer: replied 1 year ago.

Hi Russell,

Is the 5PM time frame doable?

Expert:  Russell H. replied 1 year ago.

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.

Customer: replied 1 year ago.

Ok - thanks for your help and advice Russell.