1) Write a SELECT statement that returns two columns from the invoices table: vendorID and paymentSum where paymentSum is the SUM() of the paymentTotal column. (Since we have vendorID in the SELECT line, it must be in the GROUP BY clause.) Group the result set by vendorID and ORDER BY paymentSum. (34 rows)

2) Write a SELECT statement that returns two columns: vendorName and paymentSum where paymentSum is the SUM() of the paymentTotal column. (Since we have vendorName in the SELECT line, it must be in the GROUP BY clause.) Group the result set by vendorName and ORDER BY paymentSum. (This time 33 rows!!? This tells us something! Review problem five from chapter four, part b)

3) Write a SELECT statement that returns three columns: vendorName, invoiceCount, and invoiceSum where invoiceCount is the COUNT() of the rows returned and invoiceSum is the SUM() of the invoiceTotal column Group the result set by vendorName. Sort the result set such that the with the greatest number of invoices appears first. I have 33 rows; here are my first ten:

vendorName	invoiceCount	invoiceSum
Federal Express Corporation	47	4378.02
United Parcel Service	9	23177.96
Zylka Design	8	6940.25
Pacific Bell	6	171.01
Malloy Lithographing Inc	5	119892.41
Roadway Package System, Inc	4	43.67
Blue Cross	3	564.00
Cardinal Business Media, Inc.	2	265.36
Compuserve	2	19.90
Data Reproductions Corp	2	21927.31

4) Write a SELECT statement that returns three columns: accountDescription, lineItemCount, and lineItemSum. lineItemCount is the number of entries in the invoiceLineItems table that have that accountDescription. lineItemSum is the sum of the invoiceLineItemAmount column for that accountDescription. Filter the set to include only groups with lineItemCount greater than 1. Group the set by accountDescription and sort it by descending lineItemCount. Hint: Join the glAccounts table to the invoiceLineItems table. Join the tables with an INNER JOIN on accountNo

If you alias COUNT(*) AS lineItemCount on the SELECT line you may use the alias on the ORDER BY line, but not in the HAVING clause! You will have to use COUNT(*)>1 in the HAVING clause.

Here are the data I returned:

accountDescription	lineItemCount	lineItemSum
Freight	60	27599.65
Book Printing Costs	8	148759.97
Book Production Costs	8	6175.12
Telephone	7	266.01
Direct Mail Advertising	6	3900.77
Books, Dues, and Subscriptions	6	5207.32
Computer Equipment	3	2137.05
Group Insurance	3	564.00
Office Supplies	3	175.80
Outside Services	3	13394.10

5) Query #4 has an issue! How do we know that don't have two or more different accountNo values with identical accountDescription? Nothing is blocking that insert! When we get to chapter 11, you will block that problem; for now, we'll just check it. Write an SQL query that will check for duplicate accountDescription on different accountNo in the glAccounts table. Hint: Select both fields and count the accountNo; group on accountDescription and look for a count > 1. Well, there currently aren't any... we got lucky, I guess, but database professionals do not trust luck! to test it, insert an accountNo of 633 with a description of 'Cash' into the table and that'll give you something to find. Afterwards, just delete it.

6) Modify the solution to exercise four to filter for invoices where the invoiceTotal is greater than 1000. (Add the invoices table to the join and put that condition in a WHERE clause; it's easy!) Here's what I got:

accountDescription	lineItemCount	lineItemSum
Freight	9	23177.96
Book Printing Costs	7	148674.66
Outside Services	3	13394.10
Book Production Costs	2	2197.46
Books, Dues, and Subscriptions	2	4008.00
Direct Mail Advertising	2	2949.63

7) Write a select statement that returns four columns: vendorName, accountDescription, lineItemCount, and lineItemSum; lineItemCount is the row count; lineItemSum is the SUM of the invoiceLineItemAmount column. For each vendor and accountNo (light bulb: that's probably the group by!) return the lineItemCount and lineItemSum sorted first by vendor, then by accountDescription. Notice that this one has vendorName (from vendors), lineItemSum (from invoiceLineItems) and accountDescription (from glAccounts). To make it better, you can only get from vendors to invoiceLineItems by going through invoices; thus, you have an INNER JOIN on four tables. Other than that, it's pretty straightforward.

8) Write a SELECT statement that answers the question: "Which vendors are being paid from more than one account?" Return two columns: vendorName and the total number of accounts that apply to that vendor. Only include rows that have a count exceeding one. Discussion: You might well find a COUNT(DISTINCT... to be useful for this one. Essentially, what we need to count is in invoiceLineItems, so we don't need the glAccounts table like we did in #7 (It asked for the accountDescription!) It's a pretty straightforward join of three tables. (I see Wells Fargo Bank with 3 accounts and Zylka Design with 2).