easy computer work

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. Review the Guide to SQL Server Management Studio 2008, located in the Doc Sharing tab of the course. Your instructor will provide you with the server name.

Now you are in the SQL Server Management Studio.

1. Expand the Databases folder by clicking on the plus sign to its left; the number and names of the databases in the image may differ from the ones in your environment. You should have a database named either Northwind, or BIS345_Northwind. Locate the Northwind database and expand it to see the tables; you can further expand a table to see the columns in each table:

2. Click on the New Query tool on the far left-hand side of the toolbar to open the Query Window. If you are asked to connect to the database again, just click connect. You will enter your SQL statements in this window.

Before you begin, you need to select the correct database. Currently, the Master database is selected. Click on the dropdown of available databases on the toolbar (it currently says master) and select the Northwind database:

Now you’re ready to write some SQL Queries, starting with Part A.

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.

(Solutions to Problems 3-10 are listed below).

SQL Solutions, Problems 3-10

Part C:

Part D:

Part E:

Part F:

Part G:

Part H:

Part I:

Part J:

BIS345: iLab Problems

(These are the problems that you just solved, all in one place).

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

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

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

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

5. Produce a list of all products and the name of the supplier of each product; also, list the city where the supplier is based.

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

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

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

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

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