BIS 345 Lab help

bonbons20000

documents--bis345_w4_pt1_ilab_instructions.docx

Home >Computer Science homework help >BIS 345 Lab help

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.

$C:\Users\Ruminari\Desktop\2.jpg$

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:

$C:\Users\Jose\Desktop\Capture.JPG$

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.

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.

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.

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.