BIS345: Data Analysis for Business
Lab 2 Questions:
Lab 2 Student Answer Sheet
Student Name:
Using Lab 2, answer the questions and provide copies of your SQL Statements and/or results for each Section listed below
Part A: Employee List (4 points)
a. What does Left function do?
b. Screen print your results and paste here:
Part B: Date Function (4 points)
a. How do you display the year part of a date?
b. Paste your SQL Statement here:
c. Screen print your results and paste here:
Part C: Using a Calculation in a Query (4 points)
a. Paste your SQL Statement here
b. Screen print your results and paste here:
Part D: Practice SUM function (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part E: Practice Average, Maximum, and Minimum function (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part F: Practice function when value is NULL (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part G: Using GROUP BY clause (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part H: Using function on WHERE clause (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part I: Using function to calculate date difference (5 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:
Part J: Using conversion function (5 points)
a. What do you use to convert the numerical data into VARCHAR data type?

b. Paste your SQL Statement here:
c. Screen print your results and paste here:

Lab 2: SQL 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 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

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.