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 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 until you 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 menu 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

Provide a list of employees in last name order, include their title and hire date. The employee name should combine their title, the first letter of their first name, and their last name. Periods should be used after the title and the first letter of the first name. For example: Mr. S. Buchanan

As you prepare to create this query, decide what information needs to be displayed – this information will be listed in the SELECT clause. Review the tables to determine what table to use – this 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. What functions, if any, will you need?

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.

Your result set should look like this:

3. Select all of the SQL in your query window, right click, and select Copy.

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

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

Repeat Problem 1, but only display the year part of the hire date field.

This problem continues from the first question.

1. This produces the same list, but changes the format of the hire date field. What function do you use to change the data type of a field and also change the formatting of that field?

2. The SQL that accomplishes this task is listed below. Type it into your SQL Window:

3. Press the Execute button to get this result:

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

Northwind Traders has an international presence. Provide a count of the number of customers in each country. The output should be in descending order of customer 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 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 which table will this information come? Customers. (FROM clause)

b. What fields have been requested? There is a count showing the number of customers in each country, so you will need the count and the country name. (SELECT clause)

c. How will you get the count? It is not in the table. This will be an aggregate function, and because we want a count for each country, we will have to group the customers based on country.

d. Give the count-field column a name (known as an alias).

e. In what order will the results be listed? Remember, the ORDER BY clause can use a field name or the field’s position in the SELECT clause.

The first rows in the 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 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

Northwind Traders is constantly changing the products that are offered to their customers. Provide a count of the number of products currently listed as discontinued. Also, determine how many of these discontinued products are currently still in stock. Check the table to find out what is stored in the discontinued field.

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? Products. (FROM clause)

b. What fields are required in the output? A count of the different products and the sum of the number of products in stock.

c. What aggregate functions will you need?

d. What field will be included in the WHERE clause?

e. What does this field contain – the words Yes or No? This is important to know, as it will affect how you test it. The Discontinued field is a bit datatype; a bit can hold 1, 0, or NULL. The string values ‘True’ and ‘False’ can be converted to bit. ‘True’ is 1 and ‘False’ is 0. That means that you can test Discontinued being either 1 or ‘True’ in your WHERE clause.

The 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

The HR Department wants to find the average age of the Northwind employees, as well as the age of the oldest employee and the age of the youngest employee.

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? Employees. (FROM clause)

b. What fields are required in the output? The average age of all of the employees, the age of the oldest, and the age of the youngest employee.

c. What aggregate functions will you need?

The 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 of customers, showing company name, city, region, and country fields. The region field can contain a null value. For the rows where region is null, display N/A instead of Null.

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? Customers. (FROM clause)

b. What fields have been requested? Look at the table and decide exactly what fields will be required. (SELECT clause)

c. The Region field may contain a NULL, but we want to see N/A rather than NULL. What function will you need to deal with NULLS?

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

Each product is assigned to a specific category with some categories containing more products than others. Display the CategoryID and the count of products in each category, and display the data in a column named “No. of Products.”

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? Categories. (FROM clause)

b. What fields have been requested? CategoryID and a count of the products in each category. (SELECT clause)

c. On what field will you group? (GROUP BY clause)

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

Change the previous query to produce a list of categories that have less than 10 products in the category. Again, display the CategoryID and the count of products.

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

2. This problem uses the same SELECT statement, but limits the output. Are we limiting the rows going into the query, or are we limiting the groups resulting from the query? What clause do we need? Where or Having?

The 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 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 Customer Service Department wants to review the time that is typically taken to fill an order. Find the average number of days between the date that an order is placed and the date that it is 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. (FROM clause)

b. What fields have been requested? Only one field – the average number of days to fill an order. (SELECT clause)

c. You will need two functions for this problem – a date function to find the number of days between the date that the order was placed and the date that it was shipped, and an aggregate function.

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

Part J

Using the Order Details table, produce a list showing the OrderID, ProductID, unit price, quantity ordered, and the total dollar value of the line item; don’t forget the discount. Display the total dollar value of the line item with commas around the thousands and two decimal places.

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. From what table will this information come? [Order Details] (FROM clause)

b. What fields have been requested? OrderID, ProductID, unit price, quantity ordered, and a calculated field to determine the extended price. Check the table for the actual field names. (SELECT clause)

c. The extended price is calculated by multiplying the quantity ordered by the price. How do you deal with the discount? How is the discount stored in the table – this will determine how you treat it in the calculation.

d. To display commas around the thousands, we need to convert a money data type to a varchar and make use of the style identifier. Yet, is the calculated field a money data type, or do we have to, first of all, convert it to money?

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 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 2. Well done! Submit your Word document to the Week 2 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. Provide a list of employees in last-name order, and include their title and hire date. The employee name should include their title, the first letter of their first name, and their last name. Periods should be used after the title and the first letter of the first name. For example: Mr. S. Buchanan

2. Repeat Problem 1, but display only the year part of the hire-date field.

3. Northwind Traders has an international presence. Provide a count of the number of customers in each country. The output should be in descending order of customer count.

4. Northwind Traders is constantly changing their list of products offered. Provide a count of the number of products currently listed as discontinued. Also, determine how many of these discontinued products are currently still in stock. Check the table to find out what is stored in the discontinued field.

5. The HR Department wants to find the average age of the Northwind employees, as well as the age of the oldest employee and the age of the youngest employee.

6. Produce a list of customers showing company name, city, region, and country fields. The region field can contain a null value. For the rows where region is null, display N/A instead of Null.

7. Each product is assigned to a specific category, with some categories containing more products than others. Display the CategoryID and the count of products in each category.

8. Change the previous query to produce a list of categories that have less than 10 products in the category. Again, display the CategoryID and the count of products.

9. The Customer Service Department wants to review the time typically taken to fill an order. Find the average number of days between the date that an order is placed and the date that it is shipped.

10. Using the Order Details table, produce a list showing the OrderID, ProductID, unit price, quantity ordered, and the total dollar value of the line item; don’t forget the discount. Display the total dollar value of the line item with commas around the thousands and two decimal places.