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.
Products with high list prices
Shipping methods with low rates
Total sales by country
Average vacation hours by job title
Total sales by product
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
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.
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.
Click the SQL Server Management Studio icon to launch the It should look similar to the following:
The Server Connection dialog box
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.
Right-click on the AdventureWorks2008R2 database and select New Query from the pop-up
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.
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.
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;
Click the ! execute icon on the toolbar to execute this Results will be displayed at the bottom of the Query window.
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”.
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.
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
Also in your Word document, write at least one recommendation you would make to management based on these
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.
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.
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
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.
Click the ! Execute button on the toolbar to execute this Results will be displayed at the bottom of the Query window.
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.)
In your Word document, enter the heading “Shipping Methods with Low ” Paste your query screenshot into your Microsoft Word document below this heading.
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.
Also in your Word document, write at least one recommendation you would make to management based on these
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.
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.
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
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;
Click the ! Execute button on the toolbar to execute this Results will be displayed at the bottom of the Query window.
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.)
In your Word document, enter the heading “Total Sales by ” Paste your query screenshot into your Microsoft Word document below this heading.
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.
Also in your Word document, write at least one recommendation you would make to management based on these
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.
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.
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
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.
Click the ! Execute button on the toolbar to execute this Results will be displayed at the bottom of the Query window.
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.)
In your Word document, enter the heading “Average Vacation Hours by Job ” Paste your query screenshot into your Microsoft Word document below this heading.
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.
Also in your Word document, write at least one recommendation you would make to management based on these
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.
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.
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
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;
Click the ! Execute button on the toolbar to execute this Results will be displayed at the bottom of the Query window.
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.)
In your Word document, enter the heading “Total Sales by ” Paste your query screenshot into your Microsoft Word document below this heading.
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.
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.
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.
You may also need to refer to the column list for the Person table to get the first and last names.
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
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.
Click the ! Execute button on the toolbar to execute this Results will be displayed at the bottom of the Query window.
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.)
In your Word document, enter the heading “Year-to-Date Sales by ” Paste your query screenshot into your Microsoft Word document below this heading.
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.
Also in your Word document, write at least one recommendation you would make to management based on these
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.

Struggling to find relevant content or pressed for time? – Don’t worry, we have a team of professionals to help you on
BIAM 410 Week 3 Lab SQL SELECT Queries
Get a 15% Discount on this Paper
Order Now
Calculate the price
Make an order in advance and get the best price
Pages (550 words)
$0.00
*Price with a welcome 15% 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.
Sign up, place your order, and leave the rest to our professional paper writers in less than 2 minutes.
step 1
Upload assignment 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.
s
Get personalized services with MyCoursebay
One writer for all your papers
You can select one writer for all your papers. This option enhances the consistency in the quality of your assignments. Select your preferred writer from the list of writers who have handledf your previous assignments
Same paper from different writers
Are you ordering the same assignment for a friend? You can get the same paper from different writers. The goal is to produce 100% unique and original papers
Copy of sources used
Our homework writers will provide you with copies of sources used on your request. Just add the option when plaing your order
What our partners say about us
We appreciate every review and are always looking for ways to grow. See what other students think about our do my paper service.
Social Work and Human Services
Great Work!
Customer 452587, August 31st, 2021
Other
great
Customer 452813, June 30th, 2022
Social Work and Human Services
Perfect. Thank you.
Customer 452469, July 10th, 2021
Nursing
Great! Thanks again!
Customer 452707, July 4th, 2022
Business and administrative studies
Thank you very much for your help.
Customer 452861, September 16th, 2022
ENG 099
Excellent Paper. The only 100 in the class.
Customer 452775, July 19th, 2022
Nursing
Paid for the paper to be completed 5 days prior than the day I received the paper.
Customer 452693, July 13th, 2022
Social Work and Human Services
Awesome Work!
Customer 452587, October 20th, 2021
Nursing
excellent service! Not a beat missed!
Customer 452453, October 17th, 2021
IT, Web
Paper was great and accomplished everything I needed.
Customer 452885, October 27th, 2022
Business and administrative studies
Thanks
Customer 452701, August 16th, 2022
Other
NICE
Customer 452813, June 30th, 2022
OUR GIFT TO YOU
15% OFF your first order
Use a coupon FIRST15 and enjoy expert help with any task at the most affordable price.
Claim my 15% OFF Order in Chat

Good News ! We now help with PROCTORED EXAM. Chat with a support agent for more information

NEW

Thank you for choosing MyCoursebay. Your presence is a motivation to us. All papers are written from scratch. Plagiarism is not tolerated. Order now for a 15% discount

Order Now