1) Write a SELECT statement that returns two columns from the invoices table: ve
ID: 3861480 • Letter: 1
Question
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).
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
Explanation / Answer
Queries:
1) select vendorID,SUM(paymentTotal) as paymentSum from invoices group by vendorID order by paymentSum;
Explaination: SUM is aggregate function needs group by to group the group function with an attribute and finally ordered by order by clause with paymentSum. paymentTotal has a aliasing name called paymentSum. Finally VendorID is grouped with paymentSum
2) select vendorName,SUM(paymentTotal) as paymentSum from invoices group by vendorName order by paymentSum;
Explaination: SUM is aggregate function needs group by to group the group function with an attribute and finally ordered by order by clause with paymentSum. paymentTotal has a aliasing name called paymentSum. Finally VendorName is grouped with paymentSum
3) select vendorName, count(vendorName), SUM(invoiceTotal) as invoiceSum group by vendorName order by invoiceSum DESC;
Explaination: COUNT, SUM are aggregate functions needs group by to group the group function with an attribute and count the number number of vendors and summation of totalinvoice. It is finally ordered by order by clause with invoiceSum. invoiceTotal has a aliasing name called InvoiceSum. Finally VendorName is grouped with invoiceSum and vendor count.
4) select accountDescription a,(selectcount(*) from invoiceLineItems i where i.accountDescription = a) as lineItemCount, SUM(invoiceLineItemAmount) as lineItemSum from glaccounts where lineItemCount > 1 group by accountDescription order by lineItemCount DESC;
Explaination: Joining invoicelineItems and glaccounts for getting accountDescription and SUM of amount for all the accounts in glaccounts whose lineItemCount>1, grouped by accountDescription and lineItemCount in descending order.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.