Lab 1

Due September 18

Goal

To gain experience using phpmyadmin to create useful queries

Activity

A. Create your own copy of the database CSC3032PREMIERE:

  1. Select the CSC3032PREMIERE database
  2. Select the "export Tab"
  3. Hit "Go" and save to the desktop with a name you choose
  4. Go to the "Databases" Tab
  5. There should a database "XXXPremiere", where XXX is your username
  6. Go to Databases and open your new database
  7. Hit "Import", select the file you saved, and "Go"
  8. You should now have your own copy of the database
B. On your new database, add the tables relationship links

  1. Go to the database page for XXXPremiere
  2. Select the "Query" tab.
  3. Hit the "Switch to Visual Builder link
  4. Use the tool to connect each table with a foreign key, to the table holding the primary key.
  5. Screen copy and paste the table with link view into you word document.
C. Use phpmyadmin to create and run queries for questions:
  1. List the number and name of all customers.
  2. List the complete Part table.
  3. List the number and name of all customers represented by sales rep 35.
  4. List the number and name of all customers that are represented by sales rep 35 and that have a credit limit of $10,000 or higher.
  5. List the number and name of all customers that are represented by sales rep 35 or that have a credit limit of $10,000 or higher
  6. For each order, list the order number, order date, number of the customer that placed the order, and name of the customer that placed the order.
  7. List the number and name of all customers represented by Juan Perez. (must be multi-table)
  8. List The name and address of all customers who have ordered a Washer.
  9. List all columns and all records in the Part table. Sort the results by part description.
  10. List all columns and all records in the Part table. Sort the results by part number within item class.
  11. List the item class and the Description of the parts. Sort the results by item class.
  12. In the Parts table, change the description of part BV06 to “Fitness Gym.” Don't attempt by query, rather by clicking on the edit option in browse mode.
  13. List all parts that have been sold by rep 35. answer
For each, build a query and run. Check the answer to make sure it is right.

Make a Word file with the following:

  1. For each query, turn in the SQL that was generated
  2. The query results. The results can be included as a screen shot, or you may export to CSV and copy into you document.
Turn in the complete results on Moodle
Topic revision: r3 - 2013-10-21 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback