Assessment Instructions
Bike Stores is a small business chain that sells bicycles at stores in California, Texas, and New York. The business owners would like several reports to be generated that provide information on company sales. These reports will leverage data currently stored in the BikeStores database. You will install Microsoft Report Builder on the same system you have Microsoft SQL Server and SQL Server Management Studio (SSMS) installed. You will then generate column chart, bar chart, line chart, pie chart, and tabular reports presenting various views of BikeStores sales.
It is very important that you watch the Module 5 videos associated with SQL prior to completing the assessment. You will need to install and use Microsoft SQL Server Express and Microsoft SQL Server Management Studio (SSMS) for this course. You can download the latest versions of these free software products here:
Microsoft SQL Server Express
Microsoft SSMS
Navigate to the Academic Tools area of this Module and select Library then Required Readings to access your texts and videos. You will need to install and use Microsoft SQL Server Express and Microsoft SQL Server Management Studio (SSMS) for this course.
You must have SQL Server Express and SQL Server Management Studio (SSMS) installed to complete this assessment. The sample database for this module is called BikeStores. The database creation script, installation instructions, and database diagram can be downloaded below:
- Create BikeStores Database Script
- Instructions for Establishing the BikeStores Database
- Bikestores Database Design Diagram
Use the BikeStores database design diagram to help address applicable assessment tasks. You will need to fully understand the BikeStores database design for this assessment.
You must also install the Microsoft Report Builder application. The following document provides instructions on how to install Microsoft Report Builder:
Provide your assessment task responses in a Microsoft® Word® report document. Also, incorporate a screenshot of each completed Microsoft Report Builder report into the assessment document as proof of completed work.
Task 1 – Generate a Column Chart Report
You are tasked to generate a column chart report showing the top five bike sales. Use the following guidance document to generate the column chart report:
Describe how the query contained in the common table expression (CTE) works. Are correlated or uncorrelated subqueries leveraged? Are the query results filtered? If yes, how are the results filtered? Are table joins used in the query? If yes, what tables are linked together? Are aggregate functions leveraged in the query? If yes, describe what results are being sought along with the applicable grouping parameters.
Examine the generated column chart report. What information can be drawn from this report? What assertions can be made from this report?
Task 2 – Generate a Pie Chart Report
You are tasked to generate a pie chart report showing the sales percentage for each bike category. Use the following guidance document to generate the pie chart report:
Describe how the query contained in the common table expression (CTE) works. Are correlated or uncorrelated subqueries leveraged? Are the query results filtered? If yes, how are the results filtered? Are table joins used in the query? If yes, what tables are linked together? Are aggregate functions leveraged in the query? If yes, describe what results are being sought along with the applicable grouping parameters.
Examine the generated pie chart report. What information can be drawn from this report? What assertions can be made from this report?
Task 3 – Generate a Bar Chart Report
You are tasked to generate a bar chart report showing monthly sales for each BikeStores location in the year 2017. Use the following guidance document to generate the bar chart report:
Describe how the query contained in the common table expression (CTE) works. Are correlated or uncorrelated subqueries leveraged? Are the query results filtered? If yes, how are the results filtered? Are table joins used in the query? If yes, what tables are linked together? Are aggregate functions leveraged in the query? If yes, describe what results are being sought along with the applicable grouping parameters.
Examine the generated bar chart report. What information can be drawn from this report? What assertions can be made from this report?
Task 4 – Generate a Line Chart Report
You are tasked to generate a line chart report showing monthly sales for each bike category in the year 2016. Use the following guidance document to generate the line chart report:
Describe how the query contained in the common table expression (CTE) works. Are correlated or uncorrelated subqueries leveraged? Are the query results filtered? If yes, how are the results filtered? Are table joins used in the query? If yes, what tables are linked together? Are aggregate functions leveraged in the query? If yes, describe what results are being sought along with the applicable grouping parameters.
Examine the generated line chart report. What information can be drawn from this report? What assertions can be made from this report?
Task 5 – Generate a Tabular Report
You are tasked to generate a tabular report showing bike category sales by year. Use the following guidance document to generate the tabular report:
Describe how the query contained in the common table expression (CTE) works. Are correlated or uncorrelated subqueries leveraged? Are the query results filtered? If yes, how are the results filtered? Are table joins used in the query? If yes, what tables are linked together? Are aggregate functions leveraged in the query? If yes, describe what results are being sought along with the applicable grouping parameters.
Examine the generated tabular report. What information can be drawn from this report? What assertions can be made from this report?