Directions

For this assignment, you will group data and resolve query errors via solutions appropriate to each.

For each query scenario in this assignment, compose a Word document that provides and explains the SQL code you have written accompanied by an Excel spreadsheet presenting the associated data. You will also create a Visio diagram of relationships you used to arrive at your conclusions, but note that one Visio diagram may or may not be required for each individual scenario; some scenarios may use the same Visio diagram as explanation. As such, you will compile and submit all five query scenario solutions into one submission, with the appropriate number of Word documents, Excel spreadsheets, and Visio diagrams as needed.

Your submission will be assessed on:

1. Completion of all required elements (i.e., Word document, Excel spreadsheet, and Visio diagram).
2. The success of each query (i.e., how well the query works and performs as intended).
3. Thoroughness of the information and data provided.

Query Scenarios

Questions needing answered -

Problem One: Your manager needs you to compile a list of all the products the company sells as well as the time required to manufacture them in order to provide this information to the sales department for the presentation of timelines in an upcoming company-wide sales meeting. Your manager specifically needs to know which products take (1) less than 4 days to manufacture, or (2) more than 4 days to manufacture. Looking at the "Production.Product" table and using CASE logic, identify each product with a tag of "Under 4 days" or "More than 4 days" and title the field "Timeframe."

Problem Two: Using the query you created from Problem One as a basis, your manager wants to know the numbers of how many products fall into each of the two categories. Note: This process requires two separate SUM statements using CASE logic through the Manufacturing time.

Problem Three: Also related to the upcoming sales meeting, the Sales Manager is building a report for which a list of states using a custom market name is required. Using the "Person.StateProvince" table, use CASE logic to turn TerritoryID into a named market for each of the following: Western Market, North Eastern Market, Mid-Western Market, South Western Market, and South Eastern Market. Make sure to include the TerritoryID and to order your results by this field.

Problem Four: The Sales Manager has also identified a need to know Year to Date sales by the new market segments using the same descriptions you developed in Problem Three. Compile this task as per the assignment requirements.

Problem Five: Now that you have provided the Sales Manager with YTD sales by market segment in Problem Four, the Sales Manager has one more task to complete all the information required for the sales meeting. The Sales Manager now asks you to provide sales by state year to date. This is not realistically possible through the existing company database setup. Explain why and what would be needed for the database to accomplish this dataset in the future.