[solved]-Need Help Writing Query T Get Invoices Table Build Know Part Either 1 Write Select Stateme Q39038104
Need help writing this query.
I can’t get this invoices table to build so I do not know onthat part either.
1) Write a SELECT statement that returns twocolumns from the invoices table: vendorID and paymentSum wherepaymentSum is the SUM() of the paymentTotalcolumn. (Since we have vendorID in the SELECTline, it must be in the GROUP BY clause.) Groupthe result set by vendorID and ORDER BYpaymentSum. (34 rows)
2) Write a SELECT statement that returns twocolumns: vendorName and paymentSum where paymentSum is theSUM() of the paymentTotal column. (Since we havevendorName in the SELECT line, it must be in theGROUP BY clause.) Group the result set byvendorName and ORDER BY paymentSum. (This time 33rows!!? This tells us something! Review problem five from chapterfour, part b)
Are you noticing that these data come from two tables whereasthe data in exercise one were taken from only one table?
3) Write a SELECT statement that returns threecolumns: vendorName, invoiceCount, and invoiceSum whereinvoiceCount is the COUNT() of the rows returnedand invoiceSum is the SUM() of the invoiceTotalcolumn Group the result set by vendorName. Sort the result set suchthat the with the greatest number of invoices appears first. I have33 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 threecolumns: accountDescription, lineItemCount, and lineItemSum.lineItemCount is the number of entries in the invoiceLineItemstable that have that accountDescription. lineItemSum is the sum ofthe invoiceLineItemAmount column for that accountDescription.Filter the set to include only groups with lineItemCount greaterthan 1. Group the set by accountDescription and sort it bydescending lineItemCount.
Hint: Join the glAccounts table to the invoiceLineItemstable.
I found this one to be challenging! A few observations:
- Join the tables with an INNER JOIN onaccountNo
- If you alias COUNT(*) ASlineItemCount on the SELECT line youmay use the alias on the ORDER BY line, but not inthe HAVING clause! You will have to useCOUNT(*)>1 in theHAVING clause.
- This one was adapted from #4 on page 181. IMO, the text’sversion is incomprehensible! I edited the exercise to make it alittle bit more clear what it wanted.
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 ormore different accountNo values with identical accountDescription?Nothing is blocking that insert! When we get to chapter 11, youwill block that problem; for now, we’ll just check it.
Write an SQL query that will check for duplicateaccountDescription on different accountNo in the glAccountstable.
Hint: Select both fields and count the accountNo; group onaccountDescription and look for a count > 1. Well, therecurrently aren’t any… we got lucky, I guess, but databaseprofessionals do not trust luck! to test it, insert an accountNo of633 with a description of ‘Cash’ into the table and that’ll giveyou something to find. Afterwards, just delete it.
6) Modify the solution to exercise four to filter for invoiceswhere the invoiceTotal is greater than 1000. (Add the invoicestable to the join and put that condition in a WHERE clause; it’seasy!)
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 theinvoiceLineItemAmount column. For each vendor and accountNo (lightbulb: that’s probably the group by!) return the lineItemCount andlineItemSum sorted first by vendor, thenby accountDescription.
My question is: what do they mean “sorted by vendor”? Does thatmean the name or the vendorID? Since I can’t really tell, I’ll tossa coin… OK, we’ll sort by vendorName. It could be done either wayeasily.
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 toinvoiceLineItems by going through invoices; thus, you have anINNER JOIN on four tables. Other than that, it’spretty straightforward. (I’m getting 36 rows returned.)
8) Write a SELECT statement that answers thequestion: “Which vendors are being paid from more than oneaccount?” Return two columns: vendorName and the total number ofaccounts that apply to that vendor. Only include rows that have acount exceeding one.
Discussion: You might well find aCOUNT(DISTINCT… to be useful for this one.Essentially, what we need to count is in invoiceLineItems, so wedon’t need the glAccounts table like we did in #7 (It asked for theaccountDescription!) It’s a pretty straightforward join of threetables. (I see Wells Fargo Bank with 3 accounts and Zylka Designwith 2).
PreviousNext
Expert Answer
Answer to Need help writing this query. I can’t get this invoices table to build so I do not know on that part either. 1) Write a … . . .
OR
