1. Create a Mircrosoft Word document named A07.docx. Include all diagrams, screen shots, code, and answers to questions within the following sections:
   1. Understand Data Structure and Data
   2. Get Sales Data
   3. Graph Profits
   4. Graph Sales Volume
   5. Discussion
2. In the Understand Data Structure and Data section of A07.docx
   1. The goal to this assignment is to look at the profitability of AdventureWorks product sales by type of sale.
   2. Create an Entity Relationship Diagram (ERD)
      1. Include the following tables.
         1. Customer
         2. SalesOrderHeader
         3. SalesOrderDetails
         4. SpecialOfferProduct
         5. Product
         6. ProductModel
         7. ProductSubCategory
         8. ProductCategory
      2. Arrange and space those tables and relationships so that you can easily see the database structure.
      3. Copy and paste the ERD into A07.docx
   3. Understand the Tables
      1. Write and run a query to select all rows and fields from each of the tables in your ERD. Familiarize yourself with the data.
      2. For each table answer the following in A07.docx
         1. How many rows?
         2. Describe the table
      3. For each relationship answer the following in A07.docx
         1. Describe the relationship
         2. Does the relationship always exist? If not, when not?
   4. Understand Types of Sales
      1. With the exception of the Customer table, the above is the same as last assignment, but this assignment focuses on types of sales, as opposed to types of products sold, so:
         1. Write a query and review the data for SalesOrderHeader.
         2. Notice that some sales orders have salespersons and some do not have salespersons.
            1. To figure out why some sales orders have salespersons and some do not, identify the fields (there is more than one) on the SalesOrderHeader table that correlate with having and not having salespersons on the order.
            2. For each field that correlates and that you have verified with the DISTINCT clause
               1. What is the field name?
               2. What is the correlation?
            3. Given the above:
               1. What can you say about sales orders that have sales persons?
               2. What can you say about sales orders that dont have sales persons?
      2. Does the presence or absence of sales persons on sales orders have any relationship to the types of customers that we analyzed in a prior assignment?
         1. Modify your SalesOrderHeader query to add a Customer table join.
         2. Include only the following fields in your query
            1. OnlineOrderFlag
            2. PurchaseOrderNumber
            3. SalesPersonID
            4. Customer Type - Go to your prior assignment and copy your customer type case statement and paste it into your query.
         3. Include the DISTINCT clause in your query
         4. Sort by descending OnlineOrderFlag and SalesPersonID
         5. Given the above:
            1. What can you say about online sales orders?
            2. What can you say about sales orders that are not online?
      3. What can you say about AdventureWorks based on what you have found?
         1. Modify your query to add a SalesOrderDetail table join
         2. Include only the following fields in your query
            1. OnlineOrderFlag
            2. SalesPersonID
            3. PurchaseOrderNumber
            4. Customer Type - Go to your prior assignment and copy your customer type case statement and paste it into your query.
            5. OrderQty
         3. Include the DISTINCT clause in your query
         4. Sort by descending OnlineOrderFlag and SalesPersonID
         5. Submit a screen shot in A07.docx.
         6. Submit a copy of the code in A07.docx.
         7. Read the article https://www.microsoft.com/en-us/microsoft-365/business-insights-ideas/resources/wholesaling-retailing
         8. Given the results of your query and the information in the reading, what can you say about AdventureWorks sales?
3. In the Get Sales Data section of A07.docx
   1. The goal to this section is to pull sales data and export it to Excel
   2. Using your last weeks query as a starting point, write a query that includes the following fields and ordered by ascending Profit
      1. Sale Type either Retail or Wholesale sale
      2. Category Name - ProductCategory.Name
      3. SubCategory Name - ProductSubCategory.Name
      4. Product Model Name - ProductModel.Name
      5. Product Number - Product.ProductNumber
      6. Product Name - Product.Name
      7. Order Quantity - SalesOrderDetail.OrderQty
      8. Cost = SalesOrderDetail.OrderQty * Product.StandardCost
      9. Revenue = SalesOrderDetail.LineTotal
      10. Profit = Revenue Cost
      11. Markup Percentage = Profit / Cost * 100
   3. Submit a screen shot in A07.docx.
   4. Submit a copy of the code in A07.docx.
   5. Copy and paste the results of these queries to an Excel workbook named A07.xlsx on a sheet named Sales Data.
   6. On the Sales Data sheet add a column named Profit/Loss that will contain a value Profit when the Profit column is greater than zero and a value Loss when the Profit column is less than zero. The value in the column should be BreakEven if the profit is equal to zero.