BIAM 410 Week 3 Lab SQL SELECT Queries

Want create site? With you can do it easy.

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.

Don't use plagiarized sources. Get Your Custom Essay on
BIAM 410 Week 3 Lab SQL SELECT Queries
From $10/Page
Order Essay

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.

Did you find apk for android? You can find new and apps.

Calculate the price
Make an order in advance and get the best price
Pages (550 words)
$0.00
*Price with a welcome 20% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know how difficult it is to be a student these days. That's why our prices are one of the most affordable on the market, and there are no hidden fees.

Instead, we offer bonuses, discounts, and free services to make your experience outstanding.
How it works
Receive a 100% original paper that will pass Turnitin from a top essay writing service
step 1
Upload your instructions
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
Pro service tips
How to get the most out of your experience with My Course Writer
One writer throughout the entire course
If you like the writer, you can hire them again. Just copy & paste their ID on the order form ("Preferred Writer's ID" field). This way, your vocabulary will be uniform, and the writer will be aware of your needs.
The same paper from different writers
You can order essay or any other work from two different writers to choose the best one or give another version to a friend. This can be done through the add-on "Same paper from another writer."
Copy of sources used by the writer
Our college essay writers work with ScienceDirect and other databases. They can send you articles or materials used in PDF or through screenshots. Just tick the "Copy of sources" field on the order form.
Testimonials
See why 10k+ students have chosen us as their sole writing assistance provider
Check out the latest reviews and opinions submitted by real customers worldwide and make an informed decision.
Healthcare Writing & Communications
Excellent!! You’re awesome, thank you very much for all of your hard work & help!!
Customer 452483, October 24th, 2021
Mathematics
Thank you, great work!
Customer 452483, July 19th, 2021
Healthcare Writing & Communications
You guys are awesome! Appreciate all of your hard work! Thank you.
Customer 452483, October 15th, 2021
Microbiology
Perfect! Thank you guys for all of your great help! :)
Customer 452483, August 23rd, 2021
Nursing
Excellent writing !! Definitely I will use your services again... Thank you.
Customer 452487, August 22nd, 2021
Nursing
Excellent job! super recommended.
Customer 452487, August 24th, 2021
Mathematics
Job WELL DONE! Thank You very much!!
Customer 452483, July 12th, 2021
Psychology
Thank you so much for all of your hard work, appreciate every bit of it!
Customer 452483, September 6th, 2021
Communications
came in earlier than I thought and has all the sources listed and cited in APA format, overall all perfect!
Customer 452461, May 24th, 2021
Psychology
Thank you so much for all of your hard work & help! It’s perfect! Appreciate it!!
Customer 452483, September 11th, 2021
Healthcare Writing & Communications
Perfect! Thank you so much for all of your help!
Customer 452483, October 31st, 2021
Psychology
Thank you for all of your help and hard work, truly appreciate you guys always being there with my hectic schedule. Thank you again!
Customer 452483, September 20th, 2021
1159
Customer reviews in total
96%
Current satisfaction rate
2 pages
Average paper length
47%
Customers referred by a friend
OUR GIFT TO YOU
20% OFF your first order
Use a coupon 20OFF and enjoy expert help with any task at the most affordable price.
Claim my 20% OFF Order in Chat