Exercises follow:

1) Write an SQL SELECT statement that will return all rows and all columns from the vendors table sorted by Zip Code. (This one is done as an example above.)

2) Write an SQL SELECT statement that will return three columns from the vendors table: vendorContactFName, vendorContactLName, and vendorName. Sort the result set by last name, then by first name. (Examine the field names carefully; *I* would have used simply v_lname instead of vendorContactLName; I tend to use terse field names.)

3) Write an SQL SELECT statement that will return four columns from the invoices table named number, total, credits, and balanceDue (use AS to assign column aliases):

i_num

Column alias for the invoiceNumber field

total

Column alias for the invoiceTotal field

credits

The sum of the paymentTotal and creditTotal fields

balanceDue

invoiceTotal minus the credits (defined on the previous line)

You might be inclined to try:
SELECT paymentTotal + creditTotal AS credits, invoiceTotal - credits AS balanceDue
and it would certainly make sense to do so; however, it won't work in SQL Server.
Order the output by invoiceNumber.

4) Write an SQL SELECT statement that will return one column from the vendors table named fullName. Derive this column from the vendorContactFName and vendorContactLName Format it as follows: last name followed by a comma followed by a space followed by the first name. For example: if the vendorContactFName were 'Joe' and the vendorContactLName were 'Bloe', then fullName would be 'Bloe, Joe'. To review how to do this, see pages 94 and 95 in Chapter Three of the textbook. Sort the result set by last name, then by first name.

5) Write an SQL SELECT statement that will return three columns

invoiceTotal
From the invoices table

tenPerCent

invoiceTotal * 0.1 (or 10%)

plusTenPercent

The value of invoiceTotal plus 10%

(For example: if invoiceTotal were 10,000, then 10% of that would be 1,000. plusTenPercent would be 1,1000.) Only return rows where where balanceDue is greater than 1,000 (and, if you forgot how to do that, review the way you calculated that field in exercise #3). Sort the result set by invoiceTotal in descending order (largest invoice first). Do not worry about trailing zeros to the right of the decimal in the output.

6) Modify the solution to exercise #3 to return only rows with an invoiceTotal greater than or equal to 500 but less than or equal to 10,000. (Many programmers would choose to use '<=' and '>=' with an "AND" clause; however, the BETWEEN operator covered on pages 110, 111 would also work equally well. Just bear in mind that "between" can be ambiguous with respect to the end values; BETWEEN will work as you want it to here.)

7) Modify the solution to exercise #4 to return only contacts whose last name (vendorContactLName) begins with the letter 'A', 'B', 'C', or 'E'. There exist a number of ways to accomplish this. The LEFT(vendorContactLName, 1) function described on page 98 and 99 with the IN operator on pages 108 and 109 might be handy. It could also be accomplished using a series of LIKE statements from pages 112 and 113 joined by OR.

CHALLENGE!!!
8) Write an SQL SELECT statement that will return the invoiceNumber, paymentDate, and balanceDue (just like we did it in #3 and #6 above) for any invalid paymentDate values. To be valid, paymentDate must be NULL if the balanceDue is greater than zero and NOT NULL if the balanceDue = 0. Now, this exercise pushes your logic! Notice that it asks you to return invalid rows; however, the exercise defines what a valid row is! Yes, that was deliberate! Order the output by invoiceNumber.