LAB OVERVIEW

Lab 4 will introduce the various aspects of the SQL select statement and the methods of retrieving data from the database tables. This lab will also introduce the fundamentals of updating and deleting records. 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.ERD.pdf) and are created and populated by the script file (Create_ACME_DB.sql). Follow the instructions in the file CreateACMETables.docx to create your database, tables, and data.

1. Write a query that displays the discount for each total amount when given a 15% discount. Display the OrderID, customer ID, total amount and the discount for each item.
2. Write a query that displays the discount for each total amount when given a 15% discount. Display the OrderID, customer ID, total amount and the discount for each item only if that discount is greater than or equal to $1,200.
3. Write a query that displays all the customer information for customers that have a phone number that starts with 818. Use the LIKE operator.
4. Write a query which displays the offices that do NOT have a manager. (Use the Null keyword)
5. Write a query that displays the employees that were hired from June 15, 2009 through March 12, 2013. Do this WITHOUT using the BETWEEN clause. Use a heading of Date_Hired.

Offices SalesReps PK OfficelD RepID PK City Name Region Hire Date MgrlD Quota Target Sales ales FK Officel Customers Orders PK CustiD Products PK Ordid Company PK ProductlD Phone FK CustID Description OrderDate CreditLimit ProductPrice FK ProductID FK CustReplD Quantity_On Hand FK RepID QTY TotalAmt