Description
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:
- Work with a real-world Scenario and use real-world data
- Learn how SQL is used with DML and manage the data in the database
- Describe and analyze query results
- 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
- 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.
- 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.
- Click the SQL2016 Server Management Studio icon to launch the application.
- In SQL Server Management Studio, connect to the Database Engine d1w-sqlp00am01\BIAM530.
- Click to place your cursor in the blank query pane on the right.
- 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.
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.
- 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.
- 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.
- Execute the query and review the results.
- 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.”
- 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.
- 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.
- Open a new blank query pane.
- 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.
- Execute the query and review the results.
- 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.”
- 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.
- 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.
- Open a new blank query pane.
- 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’.
- Execute the query and review the results.
- 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.”
- 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.
- 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.
- Open a new blank query pane.
- 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.
- Execute the query and review the results.
- 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.”
- 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.
- 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.
- Select and copy the text of your SQL command for best-selling products written in the previous step.
- 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.
- 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.
- 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.
- 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.)
- 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.
- Execute the query and review the results.
- 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.”
- 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.
- Save the Word document and close it.
STEP 7: Submit the Deliverable
Submit your completed file as instructed.
Rubric
Content/Development | Excellent | Competent | Fair | Poor | Unacceptable | Points Available |
---|---|---|---|---|---|---|
Step 2: Write Query to show TerritoryID, Name, CountryRegionCode, SalesYTD, and SalesLastYear fields for all territories, in descending order by SalesYTD. Analysis and recommendations are reasonable, based on query results and are professionally written with no grammar, spelling, or typographical errors. | Sales by Territory: Query shows TerritoryID, Name, CountryRegionCode, SalesYTD, and SalesLastYear fields for all territories, in descending order by SalesYTD. Analysis and recommendations are reasonable based on query results and are professionally written with no grammar, spelling, or typographical errors. | Sales by Territory: Query shows TerritoryID, Name, CountryRegionCode, SalesYTD, and SalesLastYear fields for all territories, descending order by SalesYTD is omitted or a field is omitted. Analysis and recommendations are reasonable based on query results and are written with no grammar, spelling, or typographical errors, however analysis discussion is limited or recommendation is missing. Analysis and recommendation is missing or discussion is limited and/or missing recommendations. | Sales by Territory:Query is incomplete or missing fields and use of ORDER BY command. Analysis and recommendation is missing or discussion is limited and/or missing recommendations. | The student is not able to complete the query, and also is not able to analyze the results | The lab does not meet this requirement | |
Step 2 Point Scale | 15-12 | 11-9 | 8-4 | 3-0 | 3-0 | 15 |
Step 3: Sales by Country: Write a Query that shows CountryRegionCode, Total of SalesYTD, and Total of SalesLastYear for each country; totals reflect sums over all territories in each country. Analysis and recommendations are reasonable based on query results and are professionally written with no grammar, spelling, or typographical errors. | Sales by Country: Query shows CountryRegionCode, Total of SalesYTD, and Total of SalesLastYear for each country; totals reflect sums over all territories in each country.Analysis and recommendations are reasonable based on query results and are professionally written with no grammar, spelling, or typographical errors. | Sales by Country: Query shows CountryRegionCode, Total of SalesYTD, and Total of SalesLastYear for each country; the sums over all territories in each country are not calculated or missing. Analysis and recommendations are reasonable based on query results and are written with no grammar, spelling, or typographical errors, however analysis discussion is limited or recommendation is missing. | Sales by Country: Query is missing important information or is incomplete. Analysis or recommendation is missing or discussion is limited and/or missing recommendations. | The student is not able to complete the query, and also is not able to analyze the results | The lab does not meet this requirement | |
Step 3 Point Scale | 15-12 | 11-9 | 8-4 | 3-0 | 3-0 | 15 |
Step 4: Sales Growth by U.S. Territory: Write a Query that shows TerritoryID, Name, SalesYTD, SalesLastYear, and % increase in sales for all territories for which CountryRegionCode is ‘US’. Analysis and recommendations are reasonable based on query results and are professionally written with no grammar, spelling, or typographical errors. | Sales Growth by U.S. Territory: Query shows TerritoryID, Name, SalesYTD, SalesLastYear, and % increase in sales for all territories for which CountryRegionCode is ‘US’. Analysis and recommendations are reasonable based on query results and are professionally written with no grammar, spelling, or typographical errors. | Sales Growth by U.S. Territory: Query shows TerritoryID, Name, SalesYTD, SalesLastYear. % increase in sales for all territories for which CountryRegionCode is ‘US’ is partially completed, but incorrect. Analysis and recommendations are reasonable based on query results and are written with no grammar, spelling, or typographical errors, however analysis discussion is limited or recommendation is missing. | Sales Growth by U.S. Territory: Query shows TerritoryID, Name, SalesYTD, SalesLastYear. % increase in sales for all territories for which CountryRegionCode is ‘US’is imissing and/or incorrect. Analysis or recommendation is missing or discussion is limited and/or missing recommendations. | The student is not able to complete the query, and also is not able to analyze the results | The lab does not meet this requirement | |
Step 4 Point Scale | 15-12 | 11-9 | 8-4 | 3-0 | 3-0 | 15 |
Step 5: Best-Selling Products:Write a Query that shows the ProductID and sum of LineTotal for products having sum of LineTotal greater than $2 million, in descending order by sum of LineTotal. Analysis and recommendations are reasonable based on query results and are professionally written with no grammar, spelling, or typographical errors. | Best-Selling Products:Query shows the ProductID and sum of LineTotal for products having sum of LineTotal greater than $2 million, in descending order by sum of LineTotal. Analysis and recommendations are reasonable based on query results and are professionally written with no grammar, spelling, or typographical errors. | Best-Selling Products: Query shows the ProductID and sum of LineTotal for products having sum of LineTotal greater than $2 million, in descending order by sum of LineTotal. Oneor Two of the three elements listed above are missing or are incorrect. Analysis and recommendations are reasonable based on query results and are written with no grammar, spelling, or typographical errors, however analysis discussion is limited or recommendation is missing. | Best-Selling Products: Query is partially completed and/or missing elemnts that are required. Analysis and recommendation is missing or discussion is limited and/or missing recommendations. | The student is not able to complete the query, and also is not able to analyze the results | The lab does not meet this requirement | |
Step 5 Point Scale | 15-12 | 11-9 | 8-4 | 3-0 | 3-0 | 15 |
Step 6: Best-Selling Products With Product Names: Query shows the Product ID, Product Name, and sum of LineTotal for products having sum of LineTotal greater than $2 million, in descending order by sum of LineTotal (i.e., same as the previous query, but with product names shown in addition to product IDs). Analysis and recommendations are reasonable based on query results and are professionally written with no grammar, spelling, or typographical errors. | Best-Selling Products With Product Names: Query shows the Product ID, Product Name, and sum of LineTotal for products having sum of LineTotal greater than $2 million, in descending order by sum of LineTotal (i.e., same as the previous query, but with product names shown in addition to product IDs). Analysis and recommendations are reasonable based on query results and are professionally written with no grammar, spelling, or typographical errors. | Best-Selling Products With Product Names: Query shows the Product ID, Product Name, and sum of LineTotal for products having sum of LineTotal greater than $2 million, in descending order by sum of LineTotal (i.e., same as the previous query, but with product names shown in addition to product IDs). One or more of the elements listed above is missing or the JOIN is not used. Analysis and recommendations are reasonable based on query results and are written with no grammar, spelling, or typographical errors, however analysis discussion is limited or recommendation is missing. | Best-Selling Products With Product Names: Most elements from the query are missing or incorrect. Analysis or recommendation is missing or discussion is limited and/or missing recommendations. | The student is not able to complete the query, and also is not able to analyze the results | The lab does not meet this requirement | |
Step 6 Point Scale | 20-16 | 15-11 | 10-6 | 5-1 | 5-0 | 20 |
Total from Grading Criterion | 80-64 | 59-45 | 42-22 | 17-1 | 17-0 | 80 |
Course Tutor helps in providing the best essay writing service. If you need 100% original papers for BIAM 530 Week 3 Structured Query Language DeVry, then contact us through call or live chat.
BIAM 530 Week 3 Structured Query Language DeVry
![Best BIAM 530 Week 3 Structured Query Language DeVry](http://coursetutor.us/wp-content/uploads/2018/05/eb-10.png)
ACCT 553 DeVry, BIAM 500 DeVry, CIS 500 STR, CIS 558 STR, ENG 105 GCU, FIN 390 DeVry, FIN 504 GCU, HCA 545 GCU, HCA 699 GCU, HLT 306 GCU, HLT 362 GCU, HLT 555 GCU, HLT 610 GCU, HLT 665 GCU, HOSP 594 DeVry, HRM 600 DeVry, MAT 144 GCU, MGMT 600, MGT 599 STR, MGT 655 GCU, MKT 373 GCU, PSY 362 GCU, PSY 565 GCU, PSY 575 GCU, PSY 665 GCU, SOC 102 GCU, SOC 320 GCU, SOC 372 GCU, SOC 412 GCU, NSG 6440 SU, HIM 515 GCU, NSG 4029 SU, HIM 615 GCU, NSG 3029 SU, NSG 4055 SU, NSG 6630 SU, NSG 6005 SU, CRMJ 310, PSY 510 GCU, CRMJ 300 Devry, CRMJ 425, SPD 200 GCU, HLT 490 GCU ,ECH 340 GCU , ECH 440 GCU , ECH 355 GCU ,ECH 350 GCU ,LDR 461 GCU ,ECH 425 GCU ,REL 212 STR ,SCI 115 STR ,CIS 505 STR ,JUS 652 GCU, MGT 640 GCU, CIS 527 STR, SOC 436 GCU,ACC 502 GCU, FIN 504 GCU , MATH 260 DeVry ,ETHC 445 DeVry ,ECET 220 DeVry , CARD 405 DeVry, NETW 203 DeVry, NETW 205 DeVry, ECET 365 DeVry ,MATH 270 DeVry ,PHYS 310 DeVry, BIB 106 GCU ,CIS 512 STR ,SYM 506 GCU ,ECN 601 GCU ,BIAM 570 DeVry, PSY 402 GCU , SOC 480 GCU , BIAM 530 DeVry
Reviews
There are no reviews yet.