IS 342 - Enterprise Database Management
(Quad Cities Campus)

 

 

Assignment 2: Due 22 March, 2010

Instructions:

NOTE: There are useful handouts dealing with using MySQL on the hand outs page!!!

- Create a MySQL database on your MySQL server named 'assign2'

Instructions:

  1. - SSH to the ecom server (ecom2.wiu.edu) (link on the hand outs page to down load Putty or TerraTerm Pro if you need an SSH client)
    2.  
  2. - At the unix prompt, run this command:
    cp /home/mfjlw1/assign2_load.txt ~/assign2_load.txt
    3.  
  3. - At the unix prompt, run this command:
    chmod 755 assign2_load.txt
    4.  
  4. - At the unix prompt, run this command:
    touch assign2_load.log
    5.  
  5. - To create your new database, at the unix prompt, run this command:
    mysql -vvv -u root -p < assign2_load.txt >> assign2_load.log

    6. NOTE: you will be prompted for your root password
  6. - To view the results of running that command, at the unix prompt, run this command:
    cat assign2_load.log | more

    7. Check the results for errors. (NOTE: you can move forward in the more command by either using the enter key (one line at a time) or space bar (one page at a time), use Cntrl-c to exit)

    - 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 (NOTE: These are same queries from assignment 1):

    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.

    - Run your queries against your assign2 database

    What to turn in: 

    - Print outs of your queries and the results of running your queries from the MySQL prompt.

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

    Home | Syllabus | Assignments
    Schedule | Hand Outs | Lesson Outlines
    College of Business and Technology | Information Management & Decision Sciences
    Western Illinois University | Western Illinois University Regional Center