BIAM 530 Week 3 Lab Querying the Database With SQL DeVry

1. Log in to the Citrix Lab environment (this Lab MUST be performed in Citrix to access the Adventure Works database). See the Course Resources for information on how to access the Citrix Lab environment.
2. If necessary, locate the SQL2016 Server Management Studio app in the Citrix All Apps menu and click to add it to your Citrix menu screen.
3. Click the SQL2016 Server Management Studio icon to launch the application.
4. In SQL Server Management Studio, connect to the Database Engine d1w-sqlp00am01\BIAM530.
5. Click to place your cursor in the blank query pane on the right.
6. In the Available Databases list, change the selected database from master to AdventureWorks2016. This is the operational database for Adventure Works Cycles that contains the data you will need for your analysis.

Management has asked you to analyze current-year and prior-sales for each geographic territory. Currently, for Adventure Works, in most cases, an entire country is a single territory. However, the United States is currently divided into multiple territories. You will create a query to display the sales for each territory and analyze the results.

1. In the Object Explorer list, expand the entry for the Sales.SalesTerritory table and review the columns in this table. You will use these in constructing your query.
2. In the blank query pane on the right, enter a SQL SELECT command that displays the following columns from the Sales.SalesTerritory table: TerritoryID, Name, CountryRegionCode, SalesYTD, and SalesLastYear. Include a clause that sorts the output in descending order by SalesYTD, so that the territory with the highest year-to-date sales will be displayed first.
3. Execute the query and review the results.
4. Capture a screen shot showing your SQL code for the query and the results. Paste this into a Microsoft Word document under the heading “Sales by Territory.”
5. Below the screen shot, write at least a one-paragraph analysis of what these results tell you about the business situation for Adventure Works Cycles. Include at least one recommended action that management should take based on these results.
6. Save the Word document as LabWeek3xxx.docx (where xxx = your initials). Leave the document open, because you will be adding to it in subsequent steps.

Management has asked you to analyze sales by country, so that the United States as a whole can be compared to other countries. You will create a query to display the sales for each country and analyze the results. Because the Adventure Works database does not have a sales table with one row per country, you will use the Sales.SalesTerritory table again, grouping and summarizing the sales data for all rows with the same country.

1. Open a new blank query pane.
2. Enter a SQL SELECT command that displays the following from the Sales.SalesTerritory table: CountryRegionCode, sum of SalesYTD, and sum of SalesLastYear. Use aggregate functions for the sums. Assign appropriate column headings, such as TotalSalesYTD and TotalSalesLastYear to the sums. Include a clause that will group results based on CountryRegionCode.
3. Execute the query and review the results.
4. Capture a screen shot showing your SQL code for the query and the results. Paste this into your Microsoft Word document under the heading “Sales by Country.”
5. Below the screen shot, write at least a one-paragraph analysis of what these results tell you about the business situation for Adventure Works Cycles. Include at least one recommended action that management should take based on these results.
6. Save the Word document and leave it open, because you will be adding to it in subsequent steps.

Management has asked you to analyze the percentage growth rate in sales from prior year to current year to date for each territory within the United States. You will create a query to display year-to-date sales, prior-year sales, and the percentage change between these for each U.S. territory. You will continue to use the Sales.SalesTerritory table for this query.

1. Open a new blank query pane.
2. Enter a SQL SELECT command that displays the following from the Sales.SalesTerritory table: TerritoryID, Name, SalesYTD, SalesLastYear, and a formula that calculates the percent change in sales. The formula for percent change should divide the difference between SalesYTD and SalesLastYear by SalesLastYear and multiply by 100. Assign an appropriate column heading such as PercentChange to this formula. Include a clause that will limit results to rows where the CountryRegionCode is ‘US’.
3. Execute the query and review the results.
4. Capture a screen shot showing your SQL code for the query and the results. Paste this into your Microsoft Word document under the heading “Sales Growth by U.S. Territory.”
5. Below the screen shot, write at least a one-paragraph analysis of what these results tell you about the business situation for Adventure Works Cycles. Include at least one recommended action that management should take based on these results.
6. Save the Word document and leave it open, because you will be adding to it in subsequent steps.

Management has asked you to analyze products that generate high sales revenue. You will create a query to display the total sales (dollars) for each product with sales greater than $2 million and analyze the results. You will use the Sales.SalesOrderDetail table for this analysis. Because each row in this table represents a single order line item, you will group and summarize all order lines for the same product ID to get total sales per product.

1. Open a new blank query pane.
2. Enter a SQL SELECT command that displays the following from the Sales.SalesOrderDetails table: ProductID and sum of LineTotal. Use an aggregate function for the sum. Assign an appropriate column heading such as TotalSales to the sum. Include a clause that will group results based on ProductID. Include a clause that will limit the displayed results to those having a sum of LineTotal greater than $2 million. Include a clause that will sort the results in descending order by the sum or LineTotal, so the product with the highest sales will be displayed first.NOTE: In SQL, numeric values should be entered using only digits and (if necessary) the decimal point. Do not use commas, the dollar sign, or any other currency symbol. The figure $2 million should be entered as 2000000. Be sure to count the number of zeros correctly.
3. Execute the query and review the results.
4. Capture a screen shot showing your SQL code for the query and the results. Paste this into your Microsoft Word document under the heading “Best-Selling Products.”
5. Below the screen shot, write at least a one-paragraph analysis of what these results tell you about the business situation for Adventure Works Cycles. Include at least one recommended action that management should take based on these results.
6. Save the Word document and leave it open, because you will be adding to it in subsequent steps.

Management would like to see the product names in addition to the product ID numbers for the best-selling products. You will modify your query from the previous step to display the product names. Because only product IDs and not product names are stored in the Sales.SalesOrderDetail table, you will need to include another table, Production.Product, in your query to include the names.

1. Select and copy the text of your SQL command for best-selling products written in the previous step.
2. Open a new blank query pane and paste the best-selling products SQL command into this pane. Execute the query to make sure it still works.
3. Modify the FROM clause of the query to join the Sales.SalesOrderDetail table to the Production.Product table, using the ProductID column in both tables for the join.
4. Modify the SELECT clause of the query to display the Name column (from the Production.Product table) after the ProductID column in the query results.
5. Modify the GROUP BY clause of the query to group by both ProductID and Name. (This is necessary because columns can only appear in the SELECT clause of a grouped query if they also appear in the GROUP BY column, or if they are used in an aggregate function.)
6. Wherever the ProductID column appears in the query, ensure that it is fully qualified, that is, that it has the name of the appropriate table and a period in front of it (that is, either SalesOrderDetail.ProductID or Product.ProductID). This is necessary because there is a ProductID column in both tables and you must tell SQL which one you mean in each case.
7. Execute the query and review the results.
8. Capture a screen shot showing your SQL code for the query and the results. Paste this into your Microsoft Word document under the heading “Best-Selling Products With Product Names.”
9. Below the screen shot, write at least a one-paragraph analysis of what these results tell you about the business situation for Adventure Works Cycles. Include at least one recommended action that management should take based on these results. Consider what additional information you have, now that you know the product names and not just the product IDs.
10. Save the Word document and close it.

Submit your completed file as instructed.