GetWell Inc MS Access Database

Description

GetWell, Inc. is a leading pharmaceutical organization. The organization wants to store all its customer and order details in an MS Access database. You need to perform the following tasks:

Create a database called GetWell.
Create two tables, Customer and OrderDetails, with the following information:

Customer
FieldName Type Size
CustomerID Text 5
CustomerName Text 25
CustomerAge Text 3
CustomerAddress Text 30

CustomerID will be the primary key in the Customer table.

OrderDetails
FieldName Type Size
OrderNumber Text 5
CustomerID Text 5
OrderDate Date/Time
OrderValue Number
Discount Number

OrderNumber will be the primary key in the OrderDetails table.

Enter following records in the Customer table:

CustomerID CustomerName CustomerAge CustomerAddress
C0001 Debbie Howe 28 123, Florida
C0002 Ed Young 33 New York, 234
C0003 Tom Wilkins 47 A-234 New Jersey
C0004 XXXXX XXXXXe 76XXXXXSuite
C0005 Ken Burton 52XXXXXNorth
C0006 XXXXX XXXXX 65 53, N. Dakota
C0007 XXXXX XXXXX 29 655, Wisconsin
C0008 John Barrett 55 2234, Georgia

Enter the following records in the Order table:

OrderNumber CustomerID OrderDate OrderValue Discount
O1207 C0003 10/1/2004 10500 10
O2222 C0008 4/30/2005 26790 15
O2657 C0001 11/23/2004 50000 15
O3412 C0003 2/3/2004 222 0
O4532 C0004 3/27/2005 12320 10
O4578 C0006 6/8/2004 1110 5
O4778 C0001 7/8/2004 3378 5
O5732 C0007 4/15/2005 98700 15

Write SQL statements for the database you have created to do each of the following:

Display details of all the customers whose age is greater than 45 years.
Display the names and addresses of all the customers who have placed orders with a value more than $10,000.
Display the names of customers along with the values of the orders they placed after March 01, 2005.
Create an INSTEAD OF trigger to display the message “You cannot update Order Value”, if a user tries to update the OrderValue field in the Order table.

Perform all the above activities using MS Access.