Essay BIAM 530 Week 3 Structured Query Language DeVry

Essay BIAM 530 Week 3 Structured Query Language DeVry

BIAM 530 Week 3 Structured Query Language DeVry

BIAM 530 Week 3 Discussions

DQ 1 WORKING WITH DATABASE OBJECTS & MANIPULATING DATA IN THE DATABASE

• Review the Tiny College case concerning the Free Access to Current Technology or FACT library, at the end of Chapter 7 in the textbook (pp. 340–358) from the perspective of a database administrator assigned to build this database. Select one of the entities shown in the ERD for Tiny College’s database in Figure P7.56 on page 341. What data types would you use for the attributes in this entity? How would you code a CREATE TABLE statement to implement this entity as a table in the database?
• Continue examining the Tiny College case at the end of Chapter 7 in the textbook (pp. 340-358), but this time from the perspective of a manager in the organization. What is one example of a report from the database that a manager might want to receive? What data items would appear on the report, and what attributes and entities would these data items come from? How would you code a SQL SELECT query to retrieve this information?

DQ 2 WORKOUT ROOM

Please share your expertise on this Week’s Lab Assignment. Give examples or discuss topics you might consider using and help a classmate. Please post two (2) times throughout the week. (On two different days.)

BIAM 530 Week 3 Lab Querying the Database With SQL

Lab Overview

Adventure Works Cycles is a fictional bicycle manufacturing company headquartered in Bothell, WA, that sells metal and composite bicycles to distributors in North America, Europe, and Asia. Adventure Works also sells bicycles directly to consumers via the company’s website.

Adventure Works executives are formulating a strategic plan to expand the company’s market share and are seeking information to help them. As a business analyst for Adventure Works, you will write SQL queries to retrieve the requested data from the corporate database, analyze the 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 screenshots for five queries, showing the SQL code and results. After each screen shot, you should include at least a paragraph of text with your analysis and recommendations to management based on the results of the query.

This assignment is a Signature Assignment:

The Week 3 Lab aligns with Outcomes C, D, and E. The Lab provides an existing business (Sample) database. SQL and DML will be used to manage the database and extract data to answer business questions. The results will be reported and analyzed with a description provided. These activities are common activities that take place in businesses on a daily basis around the world. You will provide a written analysis and recommendation for each SQL query.

The focus of this Lab will be:

1. Work with a real-world Scenario and use real-world data
2. Learn how SQL is used with DML and manage the data in the database
3. Describe and analyze query results
4. Create a report on the data results

Your deliverable will be evaluated according to the rubric below.

Required Software

Microsoft SQL Server Management Studio 2016

Adventure Works 2016 sample database

Access the software at: https://lab.devry.edu (Links to an external site.)Links to an external site.

Tutorial

The following tutorials will demonstrate, in general, how to use Microsoft SQL Server Management Studio, how to connect to the Adventure Works database, and how to write and execute queries similar to those in this Lab assignment. IMPORTANT: The queries created in the video are NOT the exact queries you will create in this lab. Please watch the videos to gain an understanding of how to work with SQL, but follow the written steps below to create the correct deliverable for this Lab activity, based on the Scenario/Summary and Deliverables sections at the beginning of this page.

Lab Steps

STEP 1: Launch SQL Server Management Studio and Connect to the Database

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.

Be sure the right database is selected!

Do not select the similarly-named database with the letters “DW” in the name. This is the data warehouse for Adventure Works; it is organized differently and will not work for the queries in this Lab. You will learn to work with data warehouses in a future week.

If when executing a query, you receive an error message indicating that a table name or column name is not recognized, and you are sure you spelled the table or column name correctly, double-check that the correct database is selected. Executing a query against the wrong database is a common source of errors.

STEP 2: Analyze Sales by Territory

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.

STEP 3: Analyze Sales by Country

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.

STEP 4: Analyze Sales Growth by U.S. Region

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.

STEP 5: Analyze the Best-Selling Products

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.

STEP 6: Analyze the Best-Selling Products With Product Names

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.

STEP 7: Submit the Deliverable

Submit your completed file as instructed.

Rubric

Guaranteed A+

Stop Stressing. Your Academic Success is Guaranteed.

Domyassignment is your direct route to higher grades. Thousands of students trust our PhD-level experts to deliver 100% original, custom-written papers—from tough essays to massive dissertations.

Zero Plagiarism (Free Report)

On-Time Delivery

Free Unlimited Revisions

100% Confidential

Secure Your Paper Now

Join 50,000+ success stories. Click to stop worrying and start succeeding.