Chapter 3 Textbook Assignment

Purpose: To learn how to use the SELECT command and its clauses

To see videos explaining how to complete each task, view:

Task #1 - Creating simple select queries

Launch SSMS and use the New Query command. At the top of the query window, type /* Your Name, press the enter key and type Task #1 */ Placing text withing /* and */ creates a comment that will be ignored when you run the query.

Scenario 1: Our boss would like you to display all data from the Invoices table in the AP database

a) Enter the following query:

USE AP;
SELECT * FROM Invoices;

b) Execute the query using the execute command button (or press F5)

c) Move the closing comment symbol */ to the end of the query which will comment out the query

Example:

/* Lisa Balbach
Task #1

USE AP;
SELECT * FROM Invoices; */

Scenario 2: Our boss would like a similar query, but only wants the following columns displayed: invoice ID, invoice date and invoice total. They would like the result-set sorted by invoice total in descending order.

a) Enter the query below the closing comment.

b) Run the query to make sure it works

c) Move the closing comment symbol */ to the end of the query which will comment out the query

Example:

/* Lisa Balbach
Task #1

USE AP;
SELECT * FROM Invoices;

USE AP;
SELECT InvoiceID, InvoiceDate, InvoiceTotal
FROM Invoices
ORDER BY InvoiceTotal DESC;*/

Scenario 3: Our boss is only interested in seeing the top 5% of invoices from the previous query.

a) Copy the previous query by dragging over the query and pressing CTL + C or the copy button on the toolbar.

b) Paste the query below the closing comment

c) Modify the SELECT statement to the following:

SELECT TOP 5 PERCENT InvoiceID, InvoiceDate, InvoiceTotal

d) Run the query to make sure it works

e) Move the closing comment symbol */ to the end of the query which will comment out the query

Scenario 4: Our boss would like a similar query, but instead of invoice date, they want total credits which are calculated using the credit total + payment total. They also want you to filter the result set so only invoice ID 17 displays.

a) Copy the previous query by dragging over the query and pressing CTL + C or the copy button on the toolbar.

b) Paste the query below the closing comment

c) Change the SELECT clause to the following:

SELECT InvoiceID, InvoiceTotal

d) Insert PaymentTotal + CreditTotal AS 'Total Credits' after InvoiceTotal (so, your select should have InvoiceID, InvoiceTotal, PaymentTotal + CreditTotal AS 'Total Credits' for the columns)

e) Add a WHERE clause to filter InvoiceID to display only ID 17. NOTE: This goes between the FROM and ORDER BY clauses

WHERE InvoiceID = 17

f) Run the query

g) Move the closing comment symbol */ to the end of the query which will comment out the query

h) Save the queries using the Save command button. Name the query chapter3-textbook-assignment (SSMS will put a .sql extension on the end of the name). Make sure you save the file to a jump drive or a directory on your computer that you can easily find (if you are on-campus, please save to your Q drive so you don't lose your work!)

Task #2 - Limiting rows using the WHERE clause

Scenario 1: Our boss would like to see all cities beginning with SAN for Vendors in the AP database.

a) Enter Task #2 below the existing queries and move the closing comment symbol */ after the text

b) Create a SELECT query that eliminates duplicates and displays VendorCity. You will need to use the AP database and the Vendors table.

c) The tricky part of the query is the WHERE clause. You will need to use the LIKE operator. Because the first 3 letters are specified and the rest don't matter, you will need to use the %

WHERE VendorCity LIKE 'SAN%'

d) Run the query to make sure it works (you should see 4 cities listed)

e) Move the closing comment symbol */ to the end of the query which will comment out the query

f) Save your changes.

Scenario 2: Our boss would like to see VendorContactFName and VendorContactLName for vendors whose last name begins with DAMI followed by an E or O and ending in the letter N.

a) Create a SELECT query that displays VendorContactFName and VendorContactLName from the Vendors table in the AP database.

b) For the WHERE clause, you will need to use a LIKE operator. To indicate that it should look for different letters in a specific position, you need to put the letters in brackets.

WHERE VendorContactLName LIKE 'DAMI[EO]N'

c) Run the query to make sure it works

d) Move the closing comment symbol */ to the end of the query which will comment out the query

e) Save your changes.

Scenario 3: Our boss would like to see vendors who are in zipcodes that begin with a 9 and end with a 1

a) Create a SELECT query that displays VendorName and VendorZipCode from the Vendors table in the AP database.

b) For the WHERE clause, you will need to use a LIKE operator. To indicate that it should look for different numbers between the 9 and 1, enter an underscore (_) for each number

WHERE VendorZipCode LIKE '9___1'

c) Run the query to make sure it works (all zipcodes should begin with a 9 and end with a 1)

d) Move the closing comment symbol */ to the end of the query which will comment out the query

e) Save your changes.

Scenario 4: Our boss would like to see vendors who who have a second address line.

a) Create a SELECT query that displays everything from the Vendors table in the AP database.

b) For the WHERE clause, you will need only show rows where VendorAddress2 IS NOT NULL

WHERE VendorAddress2 IS NOT NULL

c) Run the query to make sure it works

d) Move the closing comment symbol */ to the end of the query which will comment out the query

e) Save your changes.

Task #3 - Creating more complex queries using the WHERE clause

Scenario 1: Our boss would like you to modify the query shown below to display rows that have an invoice date between 2020-01-01 and 2020-01-31

USE AP;
SELECT InvoiceID, InvoiceDate, InvoiceTotal
FROM Invoices
ORDER BY InvoiceDate;

a) Enter Task #3 below the existing queries and move the closing comment symbol */ after the text

b) Copy and paste the query shown above letter a)

c) Add the following WHERE clause between the FROM and ORDER BY clauses

WHERE InvoiceDate BETWEEN '2020-01-01' AND '2020-01-31'

NOTE: When you enter dates, they are placed in single quotes

d) Run the query

e) Move the closing comment symbol */ to the end of the query which will comment out the query

f) Save your changes

Scenario 2: Our boss would like you to modify the query to display the balance due which is a calculation (invoice total - payment total - credit total). They would also like the filter modified to show invoices with a balance due > 0 and the sort order to be in descending order by the balance due

A few things to note here:

1) Balance Due is a calculated column with a column alias

2) You cannot use a column alias in a WHERE clause

3) You CAN use a column alias in an ORDER BY clause

a) Copy and paste the query you just created

b) Add the balance due calculation to the end of the select column list

SELECT InvoiceID, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS 'Balance Due'

c) Change the WHERE clause to use the calculation instead of the InvoiceDate

WHERE InvoiceTotal-PaymentTotal-CreditTotal > 0

d) Change the ORDER BY clause to use 'Balance Due' instead of InvoiceDate

ORDER BY 'Balance Due' DESC;

e) Run the query

f) Move the closing comment symbol */ to the end of the query which will comment out the query

g) Save your changes

Scenario 3: Our boss would like you to modify the filter in the last query to display only rows that have an InvoiceDate > '2020-01-01' AND either an InvoiceTotal >500 OR a Balance Due >0

A few things to note here:

1) You cannot use a column alias in a WHERE clause

2) When there are multiple criteria in a WHERE clause, you can use parenthesis to change the order of evaluation. Since the boss only wants InvoiceTotal or Balance Due, they will need to be in parenthesis with the OR operator between them

a) Copy and paste the last query

b) Modify the WHERE clause to the following:

WHERE InvoiceDate > '2020-01-01' AND (InvoiceTotal > 500 OR InvoiceTotal - PaymentTotal - CreditTotal > 0)

c) Run the query. If you look at the results, you will notice that when Balance Due is 0, the InvoiceTotal is > 500. when the InvoiceTotal is < 500, the Balance Due is > 0

d) Move the closing comment symbol */ to the end of the query which will comment out the query

Task #4 - Creating more complex queries using AND with <>

Scenario 1: Our boss would like to see employees in the Northwind database who do not live in Seattle and who are not salesreps.

The employees table contains the following data:

northwind employees

To display the rows our boss would like to see, we are going to use the criteria

City <> 'Seattle' AND Title <> 'Sales Representative'

In normal programming, this type of filter would result in all rows displaying except the first row (so 8 rows would display). In SQL, things are procesesed a little differently. It will process the first condition and apply it to all rows, removing any row with a city = Seattle. Then, it will process all titles, removing any row with a Title = Sales Representative. The result is 2 rows displaying.

a) Start by specifying the Northwind database:

USE Northwind;

b) Select all columns in the Employees table:

SELECT * FROM Employees

c) Add the selection criteria City <> 'Seattle' AND Title <> 'Sales Representative' using a where clause.

d) Run the query (you should see rows for employees 2 and 5 display)

e) Save your changes and upload the chapter3-textbook-assignment.sql file to the dropbox