BIS345: Data Analysis for Business
Lab 3 Questions:
Lab 3 Student Answer Sheet
Student Name:
Using Lab 3, answer the questions and provide copies of your SQL Statements and/or results for each Section listed below
Part A Join two tables (4 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part B: Join two tables and apply WHERE clause (4 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part C: Inner join two tables (4 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part D: Inner join two tables and apply WHERE clause – Practice # 1 (5 points)
c. Paste your SQL Statement here:
d. Screen print your results and paste here:
Part E: Inner join two tables – Practice #2 (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part F: Inner join two tables – Practice #3 (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part G: Join two tables with sub-query – Practice # 1 (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part H: Join two tables with sub-query -Practice #2 (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part I: Single table join (5 points)
c. Paste your SQL Statement here:
d. Screen print your results and paste here:
Part J: Complex join (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:

Lab 3: Table Joins, SubQueries, and Views

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 ones to be selected. Do not include any fields that management does not need to see.

Below is a screenshot of the Northwind database schema:

You will log into our Citrix Server, access the SQL Server and the Northwind database, and then write some queries using SQL. These queries extract information from a single table.
Part A

Northwind Traders has divided the sales area into territories. Produce a list of regions and all territories in each region. The list must be in alphabetical order.

As you prepare to create this query, decide what information needs to be displayed – these details will be listed in the SELECT clause. Review the tables to determine what tables to use – these will be listed in the FROM clause. Determine the field(s) needed to order the output – this will be listed in the ORDER BY clause. 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. 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 3 Student Answer Sheet located in the 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

Each product is assigned to a specific category. Produce a list showing category name, product name, unit price, the number of items in stock, and the reorder level. Only show the products in which the number of items in stock is less than the reorder level. Order the list in category-name order, and then in product-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. The SQL that accomplishes this task is listed below. Type it into your SQL Window:

3. Press the Execute Button. A partial result set is displayed below:

4. 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

The ‘Orders’ and ‘Order Details’ tables contain the data relating to current invoices. Provide a list showing order id, order date, product id, unit price, and 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 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. From what tables will this information come? Order id and order date are in Orders; product id, unit price, and quantity ordered are in Order Details.
b. What is the joining field between these tables? OrderID.

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

Show all orders for employee, Laura Callahan. Include Order ID, order date, employee last name, and first name.

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. From what tables will this information come? Order id and order date come from the Orders table; the employee last name and first name fields come from Employees.
b. What is the joining field between these tables? EmployeeID.
c. The problem is asking for the orders for one particular employee.
d. What field(s) will the WHERE clause need to test?

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 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 products and the name of the supplier of each product; also list the city where the supplier is based.

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. From what tables will this information come? Product name comes from the Products table; the supplier name and city fields come from the Suppliers table.
b. What is the joining field between these tables? SupplierID.

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 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

Produce a list showing all order ids and the date that they were shipped. Include the name of the shipping company.

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. From what tables will this information come? Order ID and order date comes from the Orders table; the shipping company name comes from the Shippers table.
b. What is the joining field between these tables? ShipVia. (This time, the join field is not the same name in both tables).

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 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

Produce a list of products in which the product price is less than the average product price.

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. There are two questions in this problem – what is the average price of all products and what products have a price that is less than the average? To answer both questions in one statement, we will need a subquery.
b. The subquery will return the average price.
c. The main query will list all products in which the price is less than the average price returned from the subquery.
d. What fields have been requested? These fields will be in the field list of the main query.

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 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

List all of the products that have a price less than the price of Chai. List product id, product name, and price fields.

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. There are two questions in this problem – what is the price of Chai and what other products are less than that price? To answer both questions in one statement, we will need a subquery.
b. The subquery will return the price of Chai.
c. The main query will list all products in which the price is less than the price returned from the subquery.
d. What fields have been requested? These will be in the field list of the main query.

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 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.

Part I

The company would like to check the efficiency of our shipping department. Provide a list of all orders that were shipped after their required date. Use the [Orders Qry] view; display the company name, order id, date that the order was required, and the date that it was shipped.

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. From what table will this information come? Orders Qry view.
b. What fields are required from the view?
c. Only certain rows are required. What will the WHERE clause be?

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 I 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 just as you did in the previous question. The screenshot should show at least the first 10 rows of the results.

Part J

The [Sales by Category] view contains a list of total product sales for each product in each category. Using this view, provide a list showing the average, maximum, and minimum sales amount for each category. Include the category id and the category name in the result. Order the result by category name.

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. Read the problem statement carefully and then review the tables. Build the statement step-by-step:
a. This statement will use a view and no tables. (FROM clause)
b. What fields have been requested? The average, maximum, and minimum sales for each category, so include both category id and name. (SELECT clause)
c. What aggregate functions will you need?
d. What field will you group on?

A partial result set 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 J 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 just 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 3. Well done! Submit your Word document to the Week 3 iLab Dropbox.