IS-342 - Assignment 1 MS Access

IS 342 - Enterprise Database Management
(Quad Cities Campus)

Assignment 1: Due 22 February, 2010

Instructions:

- Download this MS Access file OrdEntryChpt4A2000NoSolution.mdb

- Rename the file "myname_assignment1_is342.mdb" (where myname is replaced with your last name..)

- Using that database, insert the following values into the Customer table:

CustNo:         C4000009 
   
CustFirstName:  [Your First Name] 
  
CustLastName:   [Your Last Name] 
    
CustStreet:     [Your Street Address] 
      
CustCity:       [Your City] 
        
CustState:      [Your State Abrv.]  
      
CustZip:        [Your Zip]  
        
CustBal:        [350.99]

Create the queries to answer the following queries:

Query 1: List the Customer Number, the name (first and last) and the balance of all customers.

Query 2: List the Customer Number, the name (first and last) and the balance of customers who live in your state.

Query 3: List the customer number, the name (first and last), the city, and the balance of customers who reside in Seattle with a balance greater than $150 or reside in your city with a balance greater than $300.

Query 4: List the customer number, the name (first then last), the customer's city, and the order's city for customers who have orders where the order city does not match the customer's city (customer had an order shipped some place different than their home address.) Do not display duplicate rows.

Query 5: List all columns of the Product table that contain the words "Ink Jet" in the product name.

Query 6: List all columns of the Product table for products costing more than $90. Order the result by manufacturer ascending, then by product price descending.

-Name each of your queries like this example "query_2", "query_3", etc

What to turn in:

- Print outs of the results of running your queries and the sql for each query. (use the sql view in MS Access to get the sql)

- Ensure your print outs are stapled together and have your name labeled on all pages.