2023 BIS 345 Data Analysis for Business Lab 4 Part 1 Table Joins and functions Overview Using | Assignment Collections

BIS-345: Data Analysis for Business

Lab 4 Part 1: Table Joins and functions

Overview

Using the Northwind database, you have been requested to select data for specific needs. Once selected, your data will be presented to management. You will have to determine which fields are the most appropriate to be selected. Do not include any fields that management does not need to see.

Below is a screenshot of the Northwind Database schema:

Log into SQL Server and access the database engine. Once into SQL Server’s database engine, select the Northwind database as usual.  

Part A

Northwind Traders deals with a number of suppliers. The Products table includes the supplier ID for each product. Go through the Products table and provide a count of the number of products from each company. The output should show the name of the supplier and a count of the number of products it supplies.

As you prepare to create this query, decide which information needs to be displayed – this will be listed in the SELECT clause. Review the tables to determine which tables to use – these will be listed in the FROM clause. This problem will require an aggregate function – which one? What fields will be in the GROUP BY? If more than one table is needed, how will these tables be joined?

1. Using the query window, type the SELECT statement needed to produce the desired results. The SQL is given below and after you’ve typed your query, click on the Execute button to run the query and see the results.

The first rows of your result set should look like this:

2. Open up the Lab 4 Student Answer Sheet located in Doc Sharing, and answer the questions related to this part of the lab.

3. You must provide copies of your SQL statement and/or results. You may be asked to take a screenshot or cut and paste the SQL into the Word document. Follow the instructions on your lab answer sheet.

  (To take a screenshot, press CTRL-ALT-PRINTSCREEN. Nothing appears to happen on your screen, but this set of keystrokes places a picture of your screen on the clipboard. In Word, just put your mouse where you want the screenshot to go, and then right click and press Paste. The screenshot will appear in your Word document after a few seconds).

Part B

The “Orders” and “Order Details” tables contain the data relating to current invoices. Provide a list showing order ID, order date, and order total (don’t forget to include the discount). Round the order total to two decimals; display the order date as mm/dd/yyyy (no timestamp).

As you prepare to create this query, decide which information needs to be displayed – this will be listed in the SELECT clause. Review the tables to determine which tables to use – these will be listed in the FROM clause. This problem will require a calculated field and an aggregate function. What fields will be in the GROUP BY? If more than one table is needed, how will these tables be joined?

1. Using the query window, type the SELECT statement needed to produce the desired results. The SQL is given below:

2. Press the Execute button. A partial result set is displayed below:

3. Answer the questions under Part B of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.

Part C

Change the previous query to include customer name and sort the list into customer name order.

This problem builds on the previous query.

1. Use the same query that is currently in the query window.

2. What changes are required?

a.       The customer name is in the Customers table. We are already joining Orders and Order Details. Which of these tables can we join Customers to? This table is joined to the Orders table on the CustomerID field.

b.      How will you order the output?

A partial list of results returned will be:

Try the statement on your own; if you have problems, check the solution at the end of this document.

3. Answer the questions under Part C of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.

Part D

Produce a list of employees and the name of the territory they have been assigned to. The list should be ordered on last name and then first name order.

This problem requires a new SQL statement. Read the problem statement carefully and then review the tables.

1. You can either work in the current query window by deleting the existing statement that has already been pasted into your Word document; or open a new query window by clicking on the New Query tool on the toolbar. You should still be accessing the Northwind database.

2. Build the statement step by step:

a.       What tables will this information come from? Employee first and last name comes from Employees; the territory name comes from the Territories table.

b.      What is the joining field between these tables? There is no field in common between Employees and Territories. So how do we get the data? We will have to use a linking table. For instance, EmployeeTerritories allows us to join the Employees table to the Territories.

c.       Join Employees to EmployeeTerritories; and EmployeeTerritories to Territories.

A partial list of results returned will be:

3. Answer the questions under Part D of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.

Part E

Produce a list of all orders placed in December 1996. The list needs to show the order ID, order date, product name and the quantity ordered.

This problem requires a new SQL statement. Read the problem statement carefully and then review the tables.

1. You can either work in the current query window by deleting the existing statement that has already been pasted into your Word document, or you can open a new query window by clicking on the New Query tool on the toolbar. You should still be accessing the Northwind database.

2. Build the statement step by step:

a.       Which tables will this information come from? Product name comes from Products, order ID and order date comes from Orders, and quantity ordered comes from Order Details.

b.      How will these tables be joined? Orders is joined to Order Details on OrderID; Order Details is joined to Products on ProductID.

c.       How will you test for orders in December 1996? The DatePart function allows you to extract specific parts of the date. This problem will require two DatePart functions – one to extract the month and one to extract the year.

A partial list of results returned will be:

3. Answer the questions under Part E of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.

Part F

The company would like to reward its best customers. Provide a list of customers who currently have more than 10 orders with the company. Display the company name and a count of its orders. Display the list in descending order of the count.

This problem requires a new SQL statement. Read the problem statement carefully and then review the tables.

1. You can either work in the current query window by deleting the existing statement that has already been pasted into your Word document, or you can open a new query window by clicking on the New Query tool on the toolbar. You should still be accessing the Northwind database.

2. Build the statement step by step:

a.       Which tables will this information come from? Customer name comes from Customers; the count of the number of orders comes from Orders.

b.      How will these tables be joined? Customers is joined to Orders on CustomerID.

c.       What aggregate function will you need?

d.      You also need a sort order. How do you sort in descending order?

A partial list of results returned will be:

3. Answer the questions under Part F of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.

Part G

Northwind Traders currently uses only three shipping companies; management would like to review the shipping choices made by customers for their orders. Produce a list showing customer name, order ID, and date; the amount charged for freight; and the name of the shipping company used. Display the freight amount as currency. Display just the date, not the date and time. The list should be in shipper name order and then in company order.

This problem requires a new SQL statement. Read the problem statement carefully and then review the tables.

1. You can either work in the current query window by deleting the existing statement that has already been pasted into your Word document, or you can open a new query window by clicking on the New Query tool on the toolbar. You should still be accessing the Northwind database.

2. Build the statement step by step:

a.       What tables will this information come from? Customer name comes from Customers; Order ID, date, and freight amount comes from Orders; the shipping company name comes from Shippers.

b.      How will these tables be joined? Customers is joined to Orders on CustomerID and Orders is joined to Shippers but the joining field is named differently in each table – it is ShipVia in Orders and ShipperID in Shippers.

c.       How will you format freight for currency?

d.      How will you format the date to display the date only?

e.       What are the sort fields?

A partial list of results returned will be:

3. Answer the questions under Part G of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.

Part H

Produce a list showing the customer name and the total amount of each order placed by a customer. Make the alias of the total amount of each order read “Total Owed” or, if you wish, “Total Amount.”  Display the list in customer name order.

This problem requires a new SQL statement. Read the problem statement carefully and then review the tables.

1. You can either work in the current query window by deleting the existing statement that has already been pasted into your Word document, or you can open a new query window by clicking on the New Query tool on the toolbar. You should still be accessing the Northwind database.

2. Build the statement step by step:

a.       What tables will this information come from? Customer name comes from Customers; the total of each order will be the result of an aggregate function and a calculation – we will have to sum the extended line item total for each item on each order.

b.      How will these tables be joined? The Customers table is joined to Orders on CustomerID; to sum the extended line item amounts we will need the Order Details table. This table is not related directly to Customers, so we will need to go through the Orders table; Customers is joined to Orders and Orders is joined to Order Details.

c.       What aggregate function will you need?

d.      What field will you group on?

e.       What are the sort fields?

A partial list of results returned will be:

3. Answer the questions under Part H of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.

You have now completed Lab 4 Part 1. Well done! Submit your Word document to the Week 4 iLab Dropbox.  

(Referenced solutions are provided below.)

SQL Solutions, Problems 3–8

Part C:

Part G:

BIS-345 iLab Problems

(These are the problems you just solved in word problem format. This represents how you might be presented with these types of problems in a professional environment.)

1. Northwind Traders deals with a number of suppliers. The Products table includes the supplier ID for each product. Go through the Products table and provide a count of the number of products from each company. The output should show the name of the supplier and a count of the number of products it supplies.

2. The “Orders” and “Order Details” tables contain the data relating to current invoices. Provide a list showing order number, order date, and order total (don’t forget to take discount into account). Round the order total to two decimals; display the order date as mm/dd/yyyy (no timestamp).

3. Change the previous query to include customer name and sort the list into customer name order.

4. Produce a list of employees and the name of the territory they have been assigned to. The list should be ordered on last name and then first name order.

5. Produce a list of all orders placed in December 1996. The list needs to show the order ID, order date, product name, and the quantity ordered.

6.  The company would like to reward its best customers. Provide a list of customers who currently have more than 10 orders with the company. Display the company name and a count of its orders. Display the list in descending order of the count.

7.  Northwind Traders currently uses only three shipping companies. Management would like to review the shipping choices made by customers for their orders. Produce a list showing customer name, order ID, and date; the amount charged for freight; and the name of the shipping company used. Display the freight amount as currency; display just the date, not the date and time. The list should be in shipper name order and then in customer name order.

8. Produce a list showing the total amount owed to us by our customers. The list should show the customer name and the amount owed. The amount owed will be the total amount of each order placed by a customer. Display the list in customer name order.

BIS345: Data Analysis for Business

Lab 4 Part 2: Creating Simple Reports

Overview

Using the AdventureWorks database, you have been requested to create a report. The report will introduce reporting features such as using groups and a calculated field. The wizard will be used to create the report.

The AdventureWorks database contains many tables; we will be using only some of those tables in the course. Below is an ER diagram showing the tables in the AdventureWorks database that we will be using:

You will log into the iLab Citrix Server, access SQL Server Business Intelligence Development Studio 2008 and create the reports using Report Server and the AdventureWorks database.

When SQL Server BI Studio opens, you will be prompted by a security warning. Click yes and place the checkmark on the do not ask me again.

If this is the first time you have used this software, there will not be a list of previously opened projects. To create a new project, select File -> New Project.

The New Project dialog box will appear. Select Report Server Project; be sure to select a specific location to save your project file. Use your last name and the lab number in the project name:

When the new project opens, it will look similar to the following.Look at the Solution Explorer – it is showing two folders – Shared Data Sources and Reports:

Now we are ready to create the report. 

Creating the 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 into subcategories. For example, the category Bikes consists of subcategories such as Mountain Bikes, Touring Bikes, etc.

Your report needs to list the products in each subcategory. Include the subcategory name, product name, and number along with the list price and standard cost. Add a calculated field to the report to calculate the difference between the list price and standard cost.

In the dataset, the subcategory name will be repeated for each product in that subcategory. On the report, the subcategory name should not be repeated.

The report must have an appropriate title, it must display the current date, and it should include page numbers. The finished report will look like this:

We will use the wizard to create this report.

Step 1

To add a new report, right click the Reports folder in the Solution Explorer and select Add New Report.  The Report wizard will begin and step you through the process of creating this report.

Report Wizard Step 1 is a Welcome Window.

Click Next to continue.

Step 2

Report Wizard Step 2 – Select the Data Source. If this is a new project, you will create a new data source. 

Enter a meaningful name for the data source – a suggestion is to use the same name as the project for shared data sources.

To create the connection string, click the Edit button. In the Connection Properties window, enter the server name provided to you by your instructor.

Ensure Windows authentication is selected.

When you have entered a valid server name, you will be able to access the bottom half of this window where you can see a drop down for ‘Select or enter a database name’. Enter the Server Name bis345sql2008. Click on the dropdown and select the BIS345_AdventureWorks database. If you do not get a list of database names in the Connect to a database section of the screen, then your server name is incorrect; you will need to re-enter theserver name bis345sql. Your screen should look like the following:

Click OK to return to the Report Wizard Select the Data Source screen; it will now look like this:

Make sure you check the ‘Make this a shared data source’ check box in this step. It is more efficient to share a data source between multiple reports in a project.

Click Next.

Step 3

Report Wizard Step 3 – Design the Query will appear.

In this window, you can type the SQL into the query string window or use the Query Builder option. We will use the Query Builder.

Select the Query Builder button to open the next window showing the generic query builder.

This is the Query Designer:

There are two query designers – generic and graphical. The default layout is the graphical query designer. You can click on the Edit As Text button on the top left of the toolbar to switch to the generic designer. Using the graphical designer is similar to building a query in Microsoft Access using Query by Example. 

These instructions will guide you through the use of the graphical designer. 

Step 4

Now we are ready to design our query. What fields do we need? What tables are those fields in?

To build this report we need the subcategory name from the ProductSubcategory table; and product name, product number, list price, and standard cost from the Product table. We must first add tables to our query. Click the Add Table button on the top right of the toolbar or right click in the top pane and select Add Table.

Double click each table you will need in the query – ProductSubcategory and Product. Then close the Add Table window. 

The designer adds the tables and shows the relationship between them; it also starts to build the SELECT statement automatically:

Step 5

Now we need to select the fields we want. In the top pane, place a check mark beside each field you want in the query: Name in ProductSubcategory; Name, ProductNumber, ListPrice, and StandardCost in Product. Scroll down in the Product table to see the ListPrice and StandardCost fields. In the second pane, you will see the columns that have been selected for the query. Both the ProductSubcategory table and the Product table have a field called Name – and we selected both fields; we can’t have two fields in the query with the same name. The Query Builder will give the second Name column an alias of Expr1 – you will see this in the Alias column beside this field. Use the Alias column to change this to ProductName; give the first Name column an alias of SubCategoryName and give the second Name column an alias of ProductName. This process isn’t required, but will be helpful when we are designing the report. 

Use the Sort Order column to sort first in SubCategoryName order and then within each subcategory, in ProductName order. Select the SubCategoryName Sorted Order column and enter a value of 1. Likewise, select the ProductName Sort Order and enter a value of 2. This process will create your Order by clause and denote the order in which your data will be organized.

Step 6

 Run the query to test it – click on the red exclamation mark button on the top center area of the toolbar. The results will appear in a grid in the bottom pane.

When you are satisfied with the query, right click the results pane and select Clear Results. This process will free up used memory.

Click OK to close the graphical designer and continue with the Report Wizard.

We will now be returned to the Design the Query dialog in the wizard. This window will now display the SELECT statement from the query we just created.

Click Next to continue.

Step 7

Report Wizard Step 4 – Select the report type. For this report, we are using Tabular. This will give us a table layout.

Click Next.

Step 8

Report Wizard Step 5 – Design the Table. In this screen, you will decide how you want the fields in the report to appear. 

We want the subcategory name to act like a group and only appear once for each new group. To perform this action, place SubCategoryName into the Group section of the Displayed Fields.

 Put all of the other fields into the details section. The screen will look like the following:

Click Next to continue.

Step 9

Report Wizard Step 6 – Choose the Table Layout. Leave all options as they are.

Click Next to continue.

Step 10

Report Wizard Step 7 – Choose the Table Style. Select an appropriate style. In the image below, Slate has been selected. Additionally, all color and font choices can be changed later after the report has been created.

Click Next.

Step 11

Report Wizard Step 8 – Completing the Wizard. Give the report an appropriate name to identify it; include your last name in the report name e.g. FrazerLab4Part2. Don’t worry if the connection string in your report summary doesn’t exactly match the one in the image.

Click Finish.

You will see your report in the Layout tab. It currently consists of a textbox with the report title and a table containing rows and columns.

Step 12

To see what the report looks like, click the Preview tab.

Step 13

As you can see from the preview, you need to make some adjustments. Return to the Design tab. Click any cell in the table to select the table. When the table is selected, the column headers and row indicators are visible. The table has 3 rows – a header row containing text for column headings; a group header row that will display the name of the subcategory, and a detail row that will display the values from each row in the data set. The group header and detail rows contain expressions that begin with an equal sign and include the actual field name from the data set.

	Row Indicator

Step 14

Adjust column widths by moving the mouse over the line to the right of the column header; when your mouse turns into a double headed arrow click and drag to change the width as needed.

Step 15

Merge the cells that contain the column headings for the 2^nd and 3^rd columns. These columns are labeled product name and product number. Begin by selecting both cells by clicking and dragging across the cells, then right click and select Merge Cells. Replace the text in the merged cell with ‘Product’.

Center the text in the cell by setting the Text Align property in the Property Sheet.

Step 16

Change the heading text in the first row as needed. The wizard uses the field names as suggested column headings and you may need to adjust this value. Click into the cell and make your changes.  Note you do not need an equal sign if the cell will display text only.

Step 17

We need a calculated field to show the difference between the list price and the standard cost. Add a column to right of last column by right clicking the heading of the last column and select Insert Column -> Right. 

Now we need to build the expression that this calculated field is based on. Right click the detail cell of the new column and select Expression. 

An Edit Expression dialog box opens that you will use to build the expression. An expression starts with an equal sign. Select Fields in the column on the left. Note how all of the fields in the data set will be listed in the left hand column. Double click a field to add it to the expression in the top pane. It will be added in the format: Fields!FieldName.Value where FieldName is the actual name of the field in the data set. Double click the first field, enter a space, enter a minus sign, enter another space and double click the next field so the expression looks like the following:

Click OK to close the Edit Expression dialog.

Give this column a meaningful heading (e.g. Difference).

Step 18

Each of the number fields need to be formatted for currency; right click the textbox for ListPrice, select Text Box Properties. Select the Number tab and set the Category to Currency and click OK.

Repeat this step for the standard cost field and the calculated field.

Step 19

The report title currently shows the name of the report. We need to change this and also include today’s date on a second line.

Right click the textbox for the report title, select Expression. Build the following expression:

vbNewLine is a predefined constant that will insert a new line in the heading. The Today function returns the current date. Every time the report is viewed or printed it will display the current date.

Step 20

Insert a page header to display page numbers. Select the Report menu option on the Menu bar; from the list of choices select Add Page Header to add a page header band at the top of the report. Anything in this area will appear once at the top of each page. Insert a textbox into the page header by selecting the page header, right clicking, and selecting insert -> Text Box. Feel free to alter the text box size to meet your needs. Right click the text box and select Expression.

Select Built-in Fields in the left column and in the second column you will see a list of global variables that can be used. There is a variable for the page number and a variable for the total number of pages.  Double click a variable to add it to the expression. Any text you want in the expression must be placed in double quotes.

Step 21

To turn off the borders for a particular row, select the row by clicking on the row indicator.

In the Property Panel, set the BorderStyle property to None.

Use your judgment to change the appearance of the report. The following properties will be useful:

BackgroudColor – changes the background color of the cells.

Color – changes the text color of the text in the cells.

TextDecoration – adds or removes underlining of the text.

TextAlign – specifies the alignment of the text in the cell.

Submitting your lab

You will be submitting a screen shot of your report in the layout tab and a screen shot of the running report. 

1.  Select the layout tab and take a screen shot. 

2.  Open up the Lab 4 Answer Sheet. 

3.  Still in Word, put your mouse under the heading information and paste your first snap shot into this Microsoft Word document.  

4.  Return to your report. To run the report, right click the report name in the Solution Explorer and select Run. 

This will open a new window for the report. 

5.  Take a screen shot of the report in this window. Your screen shot must show at least four of the category groups and their products; it must show all of the columns.

 (To take a screenshot, press CTRL-ALT-PRINTSCREEN. Nothing appears to happen on your screen, but this set of keystrokes places a picture of your screen on the clipboard. In Word, just put your mouse where you want the screenshot to go, and then right click and press Paste. The screenshot will appear in your Word document after a few seconds).

You have now completed Lab 4 Part 2. Well done! Submit your word document to the Weekly iLab Dropbox.