BIS 345: Subcategory Report

Lab 4 of 7: Joins with Functions and Aggregates and Product List by Subcategory Report

You have been requested to create two reports using the AdventureWorks database.

Part 1: Table Joins Using Functions and Aggregates

You will continue with the multitable joins to extract data for more complex business needs. These statements use the Northwind sample database.

Part 2: Creating a Simple Report

A report has been requested that will show a full list of products offered by the company. Products are divided into categories and each category is further subdivided in subcategories. For example, the category Bikes consists of subcategories such as Mountain Bikes, Touring Bikes, and so forth. The report uses the AdventureWorks sample database.

Upon completing this lab, you will be able to

1. 1.    join three tables;
2. 2.    use functions and aggregates in join statements;
3. 3.    identity the data required to produce the report (referencing ER diagram);
4. 4.    design an effective report layout;
5. 5.    display data fields and include calculated fields on the report;
6. 6.    add appropriate headings and labels; and
7. 7.    specify paging information – placing page breaks, page numbers, and so forth.

NOTE

Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these step-by-step instructions.

(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

Following along with two instruction files, you will document your completion of the following tasks:

1. 1.    Download Guide_to_SQL_Server_Management_Studio_2008.docx from Doc Sharing.
2. 2.    Download BIS345_Lab4_Questions.docx from Doc Sharing and save a copy using the naming structureLastName_FirstName_Lab4_Questions.docx. You will answer the questions and provide screen prints as required for each part of the lab.
3. 3.    To login to the Citrix remote lab, follow the instructions located in the iLab item under Course Home.
4. 4.    Follow the steps in Guide_to_SQL_Server_Management_Studio_2008.docx to

• o   start SQL Server Management Studio;
• o   create a new project;
• o   add a query file; and
• o   get the required output.

This week’s lab instructions are broken into two documents.

• Complete the steps for Parts A–H of this lab as directed in BIS345_W4_pt1_iLab_instructions.docx.
• Complete the steps for Parts I and J of this lab as directed in BIS345_W4_pt2_iLab_instructions.docx.