web development basic

You will log into our Citrix Server, access 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 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.

3. Select the database to use.

Before you begin, you need to select the correct database. Currently the Master database is selected. Click on the drop down 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

You have been requested to provide a customer list. The list must show customer name, contact person, and how to contact the company via mail and phone. Northwind Traders has many overseas customers, so the list must be sorted in country order and the customers in each country must also be sorted in alphabetical order.

As you prepare to create this query, decide what information needs to be displayed – this 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.

1. Using the query window, type the SELECT statement needed to produce the desired results. The SQL is given below:

2. After you’ve typed your query, click on the Execute button to run the query and see the results.

Not all rows are visible in the image, but your result set should look like this:

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

4. 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 use your mouse to put the cursor 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

Change the SQL from No. 1 so that only the customers in France and Germany are displayed.

This problem continues from the first question. It produces the same list, but only certain customers are to be displayed. This requires you to restrict the rows being returned to only customers in certain countries. How do you restrict rows? You will need to add a WHERE clause. What will the condition be? The Country field contains the names of the country; you want to see the customers living in either France OR Germany. Where do you place the WHERE clause? The WHERE clause goes immediately after the FROM clause. When you have typed your query, click on the Execute button to run the query and see the results.

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

2. Press the Execute Button to get this result (not all rows are visible in the image):

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 ten rows of the results.

Supplementary Note: Instead of using the logical operator OR, you could also use the special operator IN to get the same result set. Here is an alternate solution:

Part C

You have been requested to provide a list of products sold by the company. Along with the product name, show the unit price, the number of items in stock for each product, and the total value (dollar amount) of the items in stock. The products must be listed in descending order of value.

1. This problem requires a new SQL statement. Read the problem statement carefully and then review the tables. Build the statement step by step:

a. What table will this information come from? Products. (FROM clause)

b. What fields have been requested? Product name, unit price, units in stock, and the total value of the items in stock. Check the table for the exact field names. (SELECT clause)

c. Where will total value come from? It is not in the table. This will be a calculated field. The total dollar value will be the result of multiplying unit price by units in stock.

d. Give the calculated 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:

2. 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. 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 ten rows of the results.

Part D

Management wants to see the same details as in the previous list, but only for the products that have been discontinued.

This problem will use a similar SELECT statement as in the previous question. The same fields are required, but there is a restriction on the rows.

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

2. How will you change it to return only the products that are discontinued?

What field will be included in the WHERE clause?

What does this field contain – the words Yes or No? This is important to know because 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 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 ten rows of the results.

Part E

Management wants to see the same details as in the previous problem, but only list the products for which the in-stock quantity is between 100 and 200 items.

This problem will use the same SELECT statement as in question 4. The same fields are required, but there will be a different restriction on the rows.

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

2. Delete the existing WHERE clause.

3. How will you change it to return just the products that have an in-stock quantity between 100 and 200 items?

What field will be in the WHERE clause? Between 100 and 200 items is testing for a range of values.

What comparison operator will you use to test this field? There is more than one way to write the 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.

4. 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 ten rows of the results.

Part F

This question will require a new query.

You have been requested to provide a list of our suppliers in Canada, Brazil, and the U.S.A. For each supplier provide the company name and contact information of the supplier. Sort the list into an appropriate order.

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

b. What fields have been requested? Look at the table and decide exactly what fields will be required for the purposes of contacting this company. (SELECT clause)

c. The list will be limited to certain countries. What field will you be testing in the WHERE clause?

d. There is more than one country; will you need a logical operator? Or is there a special operator you can use? Again, there is more than one way of writing the WHERE clause.

e. What order makes the most sense for the results to be listed in? Remember, the ORDER BY clause can use a field name or the field’s position in the SELECT clause.

The results returned will be as follows (not all the fields are showing in the screenshot):

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 ten rows of the results.

Part G

The Marketing Department wants to send details of upcoming sales events to customers via fax. If a customer has not provided a fax number, then some other form of communication must be used to notify them of the sales events. Provide a list of customers who do not have a fax number; include a contact person name and phone number. Sort the list in an appropriate order.

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

b. What fields have been requested? Look at the table and decide exactly what fields will be required for the purposes of contacting these customers. (SELECT clause)

c. The list will be limited to customers who do not have a fax number. What field will you be testing in the WHERE clause?

d. How will you know if there is no fax number? The field will contain a NULL. How do you test a field for NULL?

e. What order makes the most sense for the results to be listed in? 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 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, should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first ten rows of the results.

Part H

The Marketing Department wants to examine freight charges for orders shipped within the U.S.A. Produce a list showing the order ID, the date of the order, the shipping company used (ShipVia field), the freight, and the country specified in the shipping address. Limit the list to orders with a freight charge of more than $50.

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

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

c. The list will be limited by two separate criteria. What are they? What fields will you be testing in the WHERE 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 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 ten rows of the results.

Part I

Produce a list of Employees hired after 1/1/1994. Include the employee first and last names concatenated into one field, their position (title), and hire date. Sort the list in last-name order.

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

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

c. The employee name will be a calculated field combining first name and then last name – you will be transforming strings for this calculated field. Don’t forget to use a column alias.

d. Only list the employees hired after 1/1/94. What field will you be testing in the WHERE clause? Date fields are enclosed in single quotes, just like string fields.

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 ten rows of the results.

Part J

Management would like to see a list of the tofu products we currently stock. List all products that have tofu in the name of the product.

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

b. What fields have been requested? The fields have not been specified, so use your judgment. Look at the table and decide exactly what fields to include. (SELECT clause)

c. We don’t know where in the product name field the word ‘tofu’ appears. That means we can’t test for a field to be exactly equal to tofu. We will have to use the wildcard operator and the keyword LIKE.

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 ten rows of the results.

You have now completed Lab 1. Well done! Submit your Word document to the Week 1 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 you just solved, all in one place.)

1. You have been requested to provide a customer list. The list must show customer name, contact person, and how to contact the company via mail and phone. Northwind Traders has many overseas customers, so the list must be sorted in country order and the customers in each country must also be sorted in alphabetical order.

2. Change the SQL from number 1 so that only the customers in France and Germany are displayed.

3. You have been requested to provide a list of products sold by the company. Along with the product name, show the unit price, the number of items in stock for each product, and the total value (dollar amount) of the items in stock. The products must be listed in descending order of value.

4. Management wants to see the same details as in the previous list, but only for the products that have been discontinued.

5. Management wants the see the same details as in number 4, but only list the products for which the in-stock quantity is between 100 and 200 items.

6. You have been requested to provide a list of our suppliers in Canada, Brazil, and the U.S.A. For each supplier, provide the company name and contact information of the supplier. Sort the list into an appropriate order.

7. The Marketing Department wants to send details of upcoming sales events to customers via fax. If a customer has not provided a fax number, then some other form of communication must be used to notify them of the sales events. Provide a list of customers who do not have a fax number; include a contact person name and phone number. Sort the list in an appropriate order.

8. The Marketing Department wants to examine freight charges for orders shipped within the U.S.A. Produce a list showing the order ID, the date of the order, the shipping company used (ShipVia field), the freight, and the country specified in the shipping address. Limit the list to orders with a freight charge of more than $50.

9. Produce a list of Employees hired after 1/1/1994. Include the employee first and last names concatenated into one field along with the position (title) and hire date. Sort the list in last name order.

10. Management would like to see a list of the tofu products we currently stock. List all products that have tofu in the name of the product.