LAB OVERVIEW

Scenario and Summary

Lab 5 will introduce the concept of multi-table JOINS in order to work with data in two or more related tables simultaneously. This lab may be completed using either DeVrys Omnymbus EDUPE-APP lab environment, or a local copy of the MySQL database running on your own computer using the ACME database tables. The lab will utilize a set of tables that are represented by the ERD (ACME.docx) and are created and populated by the script file (create_ACME_db.sql). Use the ACME ERD to do these problems.

7. Write a query using the implicit join syntax that will display the company name, phone number, and Order amount, and product description for all products that have an order greater than $5,000. Sort it by order amount.

8. Write a query using the implicit join syntax that displays the office ID, city, sales rep name, company name (customer), and product descriptions of products that were bought by the customers. Sort it by company name

9. Write a query using the explicit join syntax to display the office id, office city, sales rep name, and date of hire for only those sales reps that were hired after 2010.

Offices SalesReps PK OfficelD ReplD PK City Name Region Hire Date MgriD Quota Target Sales ales FK Officel D Customers Orders PK CustID Products PK Ordid Company PK ProductID FK CustID Phone Description OrderDate CreditLimit Product Price FK ProductiD FK CustReplD Quantity_On Hand FK RepID QTY TotalAmt