Test

PURPOSE

The purpose of this test is to test your Database Design skill. Please take your time to think of the proper design.

Prerequisite

• SQL Express (https://www.microsoft.com/en-us/sql-server/sql-server-downloads)
• SQL Management Studio (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

INSTRUCTIONS

• Please use SQL Management Studio.
• Create a simple database with tables that can hold a number of products and order requests.
• Please create 2 stored procedures:
  • Store procedure to add a new product. The product consists of:
    • Product name
    • Quantity in stock
    • Current Price of the product
  • Store procedure to add a new order. The order consists of:
    • Order number (Please Do NOT use auto number)
    • Order date
    • Delivery Address
    • List of the ordered products and its quantity
• Add New Order Store Procedure Requirement:
  • Within 1 stored procedure, you allow a list of products to be passed together with the order number
  • When the quantity is ordered, please decrease the Quantity In Stock in the product table
  • For example:
    Order: 123
    Order Date: 2022-01-01 09:02:32.232
    Delivery Address: 1 Smith St, Melbourne
    Order Items:
    • ProductId: 1
      Quantity: 10
    • ProductId: 6
      Quantity: 1
    • ProductId: 8
      Quantity: 18
    HINTS: Please use Table Value Parameter to complete this Store Procedure
• Once you have completed and tested, right click on your database, go to Task and click on Generate Scripts...
• Please send the generated script file to us

NOTE:
If you do not have a computer or enough storage on your computer to install the prerequisite software mentioned above, please send us an email. We can arrange for you to come onsite to complete the test.

---