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 OM database tables. The lab will utilize a set of tables that are represented by the ERD (OM_ERD.docx) and are created and populated by the script file (create_OM_db.sql). Follow the instructions in the file CreateOMTables.docx to create your database, tables, and data.

10. Write a query to display an invoice for order 693 including the title, quanty, price, and a calculated column to display the line item subtotal (quantity times price) with a heading of Subtotal.

11. Display the order id, last name, title , and quantity for any items where customers have ordered 2 or more of a particular item in an order.

12. The employees table contains a list of employees of a company. An employee may be managed by another employee. To denote an employees manager, the employee_id of the manager is entered into the manager_id field of the employee reporting to that manager. In order to produce a list of which managers supervise which employees, the table may be joined to itself using these two attributes (employee_id, manager_id). This is known as a SELF-JOIN. Use a SELF-JOIN on the employees table to produce a result containing two columns: the concatenated last_name and first_name of the manager, and the concatenated last_name and first_name of the employee. Label the first-column supervisor, and the second-column employee. Order the output by the supervisors last_name and first_name.

13. Write a query to display the order id, order date, ship date, and customer last name for all SHIPPED orders placed on the internet (no employee id). Show the most recently shipped orders first.

14. Write a query that displays the order id, order date, and employee name as a single field labeled Employee for each order that an employee assisted with.

15. Write a query that displays the order id, order date, customer name as a single field labeled Customer for all orders for the sales rep Thomas Hardy, sorted by customer last name.

Database Model for Labs 4-7 customers PK customer id INTEGER orders PK order id INTEGER customer_first_name VARCHAR(20) customerlast name VARCHAR(20) customer_address VARCHAR(50) customer city customer state customer_zip customer phone CHAR(10) customer fax order_date DATETIME shipped_date DATETIME VARCHAR(20) CHAR(2) CHAR(S) FK1 customer idINTEGER FK2 employee_id INTEGER CHAR(10) Employees PK em id INTEGER order details PK,FK1order id INTEGER PK,FK2 item idINTEGER last_name VARCHAR(20) first name VARCHAR(20) FK1 manager idINTEGER order qty INTEGER items Artists PK item id INTEGER PK artist id INTEGER title VARCHAR(40) unit_price DECIMAL(9,2) artist_name VARCHAR(30) FK1 artist_id INTEGER