Assignment 7

BUSA 326 Data and Information Management Fall 2019

Instructor: Prof. Brodnax

Assignment: Please post the following in the class dropbox NLT midnight, 3 Dec. 2019:

1. .sql file with SQL script that executed the requirements noted below
2. The Word document that contains the translation of the requirement to the SELECT
3. statement that executes the requirements noted below

1. Use the salesordersdb326 database schema

2. Copy this assignment into a Word Document and provide the Translation, Cleanup, and SQL that accomplishes the following requirements:

- Requirement a. "Shall provide a display which includes total sales for each product in inventory.". Make sure it looks exactly like the example output (at the end of this assignment document. Required sql construct: INNER JOIN. Hint: 38 rows generated.

- Requirement b. "Shall provide a display which includes all customers and their respective state.". Make sure it looks exactly like the example output (at the end of this assignment document. Required sql construct: CASE. Hint: 28 rows generated.

4. Apply the SELECT statements to the MySQL Workbench to accomplish the requests noted.

5. Be sure and meet both explicit requirements as noted in the requirements statement as well as implicit requirement that are provided in the output grid.

6. As noted above, post both the Word document with the translation statements and the SQL (sql) file that executes the required query. Both files (only 2 files submitted) must be submitted into the dropbox by the due date to receive full credit. Note that the one (1) sql file will have all queries (sql) contained in it. There should not be more than one sql file posted.

7. Outputs for each requirement (request) is shown below. Remember, your query should generate the exact same output (including headers) as shown below.

a. Translation:

TRANSLATION:

CLEANUP:

SQL:

b. Translation

TRANSLATION:

CLEANUP:

SQL:

\fResult Grid Filter Rows: ProductName TotalSales AeroFlo ATB Wheels 75458.25 Clear Shade 85-T Glasses 14581.35 Cosmic Elite Road Warrior Wheels 63803.85 Cycle-Doc Pro Repair Stand 62157.04 Dog Ear Aero-Flow Floor Pump 36029.40 Dog Ear Cyclecomputer 2238.75 Dog Ear Helmet Mount Mirrors 767.73 Dog Ear Monster Grip Gloves 2779.50\f