BIAM 410 Week 3 Lab SQL SELECT Queries

BIAM 410 Week 3 Lab SQL SELECT Queries

BIAM410 Week 3 Lab SQL SELECT Queries

Scenario/Summary

Adventure Works Cycles is a fictional company that manufactures metal and composite bicycles for sale to commercial distributors in North America, Europe, and Asia. Adventure Works is a multinational company headquartered in Bothell, WA. The firm recently acquired a manufacturing plant in Mexico that makes touring bicycles and subcomponents of other bicycles. The company is currently seeking to expand its market share by targeting high-volume customers, expanding availability of products on the Web, and lowering production costs.

Executives have requested some information to help them make strategic decisions to carry out this business plan. As a business analyst for Adventure Works Cycles, you will write SQL queries to retrieve the needed information from the corporate database, analyze this information, and make recommendations to management.

Deliverables

After completing the steps below, submit a single Microsoft Word file named LabWeek3xxx.docx (where xxx = your initials). This file will contain six queries, including the SQL code, results, and analysis or recommendations for each query.

1. Products with high list prices
2. Shipping methods with low rates
3. Total sales by country
4. Average vacation hours by job title
5. Total sales by product
6. Year-to-date sales by salesperson

Grading Rubric 

Section	Deliverable	Points
2	Query shows ProductID, Name, Color, and ListPrice fields for products with list prices greater than $3,000, in descending order by ListPrice. SQL code and query results are provided. Analysis and recommendations are reasonable and show good business judgment based on query results; and are written professionally with no grammar, spelling, or typographical  errors.	6
3	Query shows ShipMethodID, Name, ShipBase, and ShipRate fields for shipping methods with shipping rates less than $1.50 in ascending order by ShipRate. Screenshot includes SQL code and results. Analysis and recommendations are reasonable and show good business judgments based on query results; and are written professionally with no grammar,  spelling, or typographical errors.	6

 

4	Query shows CountryRegionCode and sum of SalesYTD for each country in descending order by sum of SalesYTD. Screenshot includes SQL code and results. Analysis and recommendations are reasonable and show good business judgments based on query results; and are written professionally  with no grammar, spelling, or typographical errors.	6
5	Query shows JobTitle and average of VacationHours for each job title in descending order by average of VacationHours. Screenshot includes SQL code and results. Analysis and recommendations are reasonable and show good business judgments based on query results; and are written  professionally with no grammar, spelling, or typographical errors.	6
6	Query shows ProductID, product Name and sum of LineTotal for each product in descending order by sum of LineTotal. Screenshot includes SQL code and results. Analysis and recommendations are reasonable and show good business judgments based on query results; and are written  professionally with no grammar, spelling, or typographical errors.	8
7	Query shows BusinessEntityID, FirstName, LastName, and SalesYTD for each salesperson, in descending order by sum of SalesYTD. Screenshot includes SQL code and results. Analysis and recommendations are reasonable and show good business judgments based on query results; and are written professionally with no grammar, spelling, or typographical  errors.	8
	Total	40

 

Required Software

Microsoft SQL Server Management Studio

Access the software through the Citrix Virtual Lab Environment at . You must use the Citrix Virtual Lab to connect with the shared Adventure Works database you will use in this lab activity.

The lab instructions and videos were created using Microsoft SQL Server Management Studio 2016. Any recent version of SQL Server Management Studio will work similarly, but some adjustments to instructions may be needed.

Steps: All

Microsoft Office: Word

Access the software through the Citrix Virtual Lab Environment at or download and install a personal copy from your student Office365 account, accessed through the student portal.

Steps: All

Lab Steps

Step 1: Launch SQL Server Management Studio and connect to database

1. Log in to the Citrix Virtual Lab See the Lab Resources section of the Course Resources page in the Introduction & Resources Module for information on how to access the Citrix Virtual Lab.
2. Select the Apps tab and search for SQL Server Management If more than one icon appears, select the one with SQL Server Management Studio 2016 as the name.
3. Click the SQL Server Management Studio icon to launch the It should look similar to the following:
4. The Server Connection dialog box
5. In the Connect to Server dialog box, copy/paste in the following server name:

d1w-sqlp00am11BIAMSQL2008

Ensure that authentication is set to Windows Authentication. Click Connect.

In the Object Explorer on the left side, expand the Databases folder by clicking the plus sign (+) in front of Under Databases, click on the AdventureWorks2008R2 database to select it; then click the plus sign in front to expand the database. Under this, expand the Tables folder to see a list of tables in this database.

1. Right-click on the AdventureWorks2008R2 database and select New Query from the pop-up
2. A blank query pane will open. Click in the query pane and check that the AdventureWorks2008R2 database appears in the drop-down list at the upper If instead you see master or any other database, click the drop-down arrow and change the database to AdventureWorks2008R2.

You are now ready to write your first query.

Step 2: Create and Analyze Query for Products with High List Prices

Management has asked to see a list of all products with prices greater than $3,000 sorted so that the highest priced product is at the top. You will create a query to display this list and analyze the results.

1. In the Object Explorer pane, scroll down in the list of tables under the AdventureWorks2008R2 database until you see the Product table. Expand Production.Product and then under that, expand the Columns folder to see the list of columns in this table. We will refer to this list in composing our query.
2. Referring to the list of columns, enter the following SQL code in the blank query pane on the NOTE: numbers must be all digits with no formatting such as $ or commas.

SELECT ProductID, Name, Color, ListPrice FROM Production.Product

WHERE ListPrice > 3000 ORDER BY ListPrice DESC;

1. Click the ! execute icon on the toolbar to execute this Results will be displayed at the bottom of the Query window.
2. Open a blank Word At the top, enter the title “BIAM410 Week 3 Lab” and

your name. Below this, enter the heading “Products with High List Prices”.

1. Capture a screenshot showing both your SQL code in the upper pane and the query results in the lower (On a Windows computer, to capture a screenshot of the active window, press Alt + PrintScreen, or use the Windows Snipping Tool.) Paste this screenshot into your Word document.
2. In your Word document, below the screenshot of the query and results write a one- paragraph analysis of what these results tell you about Adventure Works’s
3. Also in your Word document, write at least one recommendation you would make to management based on these
4. Save the Word document as docx (where xxx = your initials). Leave the document open because you will be adding more to it in subsequent steps.

Step 3: Create and Analyze Query for Shipping Methods with Low Rates

Management has asked to see a list of all shipping methods with shipping rates less than $1.50 per pound, sorted so that the method with the lowest rateis at the top. You will create a query to display this list and analyze the results.

1. In the Object Explorer pane, scroll down in the list of tables under the AdventureWorks database until you see the Purchasing.ShipMethod table. Expand ShipMethod, and then under that, expand the Columns folder to see the list of columns in this table. You will refer to this list in composing your query.
2. Highlight and delete the code for your previous query, OR press Ctrl + N or click the New Query button to open a new query tab. Ensure that the AdventureWorks2008R2 database is still selected in the drop-down list at the upper left of the
3. Referring to the list of columns, enter the SQL code for the desired query in the New Query pane on the Your query should display the ShipMethodID, Name, ShipBase, and ShipRate columns for all rows in which ShipRate is less than 1.50 sorted so that the lowest ShipRate value appears at the top. Refer to the query in the previous step for an example of the SQL code for a similar query.
4. Click the ! Execute button on the toolbar to execute this Results will be displayed at the bottom of the Query window.
5. Capture a screenshot showing both your SQL code in the upper pane and the query results in the lower (On a Windows computer, to capture a screenshot of the active window, press Alt + PrintScreen, or use the Windows Snipping Tool.)
6. In your Word document, enter the heading “Shipping Methods with Low ” Paste your query screenshot into your Microsoft Word document below this heading.
7. In your Word document, below the screenshot of the query and results write a one-

paragraph analysis of what these results tell you about AdventureWorks’s business.

1. Also in your Word document, write at least one recommendation you would make to management based on these
2. Save the Word Leave the document open because you will be adding more to it in subsequent steps.

Step 4: Create and Analyze Query for Total Sales by Country

Management has asked to see a list of year-to-date sales totals by country, sorted so that the country with the highest total sales year-to-date is at the top. You will create a query to display this list and analyze the results.

1. In the Object Explorer pane, scroll down in the list of tables under the AdventureWorks database until you see the Sales.SalesTerritory table. Expand Sales.SalesTerritory, and then under that, expand the Columns folder to see the list of columns in this You will refer to this list in composing your query.
2. Highlight and delete the code for your previous query, OR press Ctrl + N or click the New Query button to open a new query tab. Ensure that the AdventureWorks2008R2 database is still selected in the drop-down list at the upper left of the
3. Referring to the list of columns, enter the following SQL code for the desired query in the New Query pane on the

 

SELECT CountryRegionCode, FORMAT(SUM(SalesYTD), ‘C’) As TotalSalesYTD FROM Sales.SalesTerritory

GROUP BY CountryRegionCode ORDER BY SUM(SalesYTD) DESC;

1. Click the ! Execute button on the toolbar to execute this Results will be displayed at the bottom of the Query window.
2. Capture a screenshot showing both your SQL code in the upper pane and the query results in the lower (On a Windows computer, to capture a screenshot of the active window, press Alt + PrintScreen, or use the Windows Snipping Tool.)
3. In your Word document, enter the heading “Total Sales by ” Paste your query screenshot into your Microsoft Word document below this heading.
4. In your Word document, below the screenshot of the query and results write a one-

paragraph analysis of what these results tell you about AdventureWorks’s business.

1. Also in your Word document, write at least one recommendation you would make to management based on these
2. Save the Word Leave the document open because you will be adding more to it in subsequent steps.

Step 5: Create and Analyze Query for Average Vacation Hours by Job Title

Management has asked to see a list of all job titles and the average number of vacation hours accumulated by employees with each job title. This will assist in planning what types of temporary help may be needed while regular employees are on vacation. You will create a query to display this list and analyze the results.

1. In the Object Explorer pane, scroll down in the list of tables under the AdventureWorks database until you see the HumanResources.Employee table. Expand Employee, and then under that, expand the Columns folder to see the list of columns in this table. You will refer to this list in composing your query.
2. Highlight and delete the code for your previous query, OR press Ctrl + N or click the New Query button to open a new query tab. Ensure that the AdventureWorks2008R2 database is still selected in the drop-down list at the upper left of the
3. Referring to the list of columns, enter the SQL code for the desired query in the New Query pane on the Your query should display the JobTitle and the average VacationHours, grouped by JobTitle, and sorted so that the highest average VacationHours value appears at the top. Refer to the query in the previous step for an example of the SQL code for a similar query.
4. Click the ! Execute button on the toolbar to execute this Results will be displayed at the bottom of the Query window.
5. Capture a screenshot showing both your SQL code in the upper pane and the query results in the lower (On a Windows computer, to capture a screenshot of the active window, press Alt + PrintScreen, or use the Windows Snipping Tool.)
6. In your Word document, enter the heading “Average Vacation Hours by Job ” Paste your query screenshot into your Microsoft Word document below this heading.
7. In your Word document, below the screenshot of the query and results write a one-

paragraph analysis of what these results tell you about AdventureWorks’s business.

1. Also in your Word document, write at least one recommendation you would make to management based on these
2. Save the Word Leave the document open because you will be adding more to it in subsequent steps.

Step 6: Create and Analyze Query for Total Sales by Product

 

Management has asked for a list of products and the total dollar sales for each product, with the products having the highest dollar sales at the top. Each product should be identified by both its product ID and product name. You will create a query to display this list and analyze the results.

1. In the Object Explorer pane, scroll down in the list of tables under the AdventureWorks database until you see the SalesOrderDetail table. Expand Sales.SalesOrderDetail, and then under that, expand the Columns folder to see the list of columns in this table. You will refer to this list in composing your query.
2. Highlight and delete the code for your previous query, OR press Ctrl + N or click the New Query button to open a new query tab. Ensure that the AdventureWorks2008R2 database is still selected in the drop-down list at the upper left of the
3. Referring to the list of columns, enter the following SQL code for the desired query in the New Query pane on the

 

SELECT S.ProductID, Name, FORMAT(SUM(LineTotal),’C’) As TotalSales FROM Sales.SalesOrderDetail S

JOIN Production.Product P ON S.ProductID = P.ProductID GROUP BY S.ProductID, Name

ORDER BY SUM(LineTotal) DESC;

1. Click the ! Execute button on the toolbar to execute this Results will be displayed at the bottom of the Query window.
2. Capture a screenshot showing both your SQL code in the upper pane and the query results in the lower (On a Windows computer, to capture a screenshot of the active window, press Alt + PrintScreen, or use the Windows Snipping Tool.)
3. In your Word document, enter the heading “Total Sales by ” Paste your query screenshot into your Microsoft Word document below this heading.
4. In your Word document, below the screenshot of the query and results write a one-

paragraph analysis of what these results tell you about AdventureWorks’s business.

1. Also in your Word document, write at least one recommendation you would make to management based on these

 

Save the Word document. Leave the document open because you will be adding more to it in subsequent steps.

Step 7: Create and Analyze Query for Year-to-Date Sales by SalesPerson

Management has asked for a list of total year-to-date sales by each salesperson, with the salesperson having the highest dollar sales at the top. Each salesperson should be identified by his or her business entity ID, first name, and last name. You will create a query to display this summary and analyze the results.

1. In the Object Explorer pane, scroll down in the list of tables under the AdventureWorks database until you see the SalesPerson table. Expand Sales.SalesPerson, and then under that, expand the Columns folder to see the list of columns in this table. You will refer to this list in composing your query.
2. You may also need to refer to the column list for the Person table to get the first and last names.
3. Highlight and delete the code for your previous query, OR press Ctrl + N or click the New Query button to open a new query tab. Ensure that the AdventureWorks2008R2 database is still selected in the drop-down list at the upper left of the
4. Referring to the list of columns, enter the SQL code for the desired query in the New Query pane on the Your query should display the BusinessEntityID, salesperson FirstName and LastName (from the Person.Person table), and SalesYTD, grouped by BusinessEntityID, FirstName, and LastName, and sorted so that the highest SalesYTD appears at the top. Refer to the query in the previous step for an example of the SQL code for a similar query.
5. Click the ! Execute button on the toolbar to execute this Results will be displayed at the bottom of the Query window.
6. Capture a screenshot showing both your SQL code in the upper pane and the query results in the lower (On a Windows computer, to capture a screenshot of the active window, press Alt + PrintScreen, or use the Windows Snipping Tool.)
7. In your Word document, enter the heading “Year-to-Date Sales by ” Paste your query screenshot into your Microsoft Word document below this heading.
8. In your Word document, below the screenshot of the query and results write a one-

paragraph analysis of what these results tell you about AdventureWorks’s business.

1. Also in your Word document, write at least one recommendation you would make to management based on these
2. Save and close the Word

Step 8: Submit Your Work

Submit your completed LabWeek3xxx.docx (where xxx = your initials) file on the Week 3: Lab assignment page.