October 12, 2011 at 9:58 pm
Hello guys,
I have multiple choises question that I find them confusing so I decided to put them here so I can get a little help.
Which of the statements below best describes the result set returned by this SELECT statement?
SELECT VendorID,
SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
GROUP BY VendorID
a.The unpaid balance for each invoice
b.The total unpaid balance due for each VendorID
c.The total amount invoiced by each VendorID
d.The total of paid invoices for each VendorID
and the second one
Code example 5-1
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,
SUM(InvoiceTotal) AS InvoiceAvg
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE VendorState < 'e'
GROUP BY VendorState, VendorCity, VendorName
HAVING SUM(InvoiceTotal) > 500
ORDER BY VendorState, VendorCity, VendorName
(Please refer to code example 5-1.) Although this query runs as coded, it contains this logical error:
a.The condition in the WHERE clause should be coded in the HAVING clause.
b.The column name for the fifth column in the result set doesn’t match the data.
c.The three columns in the ORDER BY clause should use the DESC keyword so the city totals will be in the right sequence.
d.The condition in the HAVING clause should be coded in the WHERE clause.
Third one
Code example 5-1
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,
SUM(InvoiceTotal) AS InvoiceAvg
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE VendorState < 'e'
GROUP BY VendorState, VendorCity, VendorName
HAVING SUM(InvoiceTotal) > 500
ORDER BY VendorState, VendorCity, VendorName
(Please refer to code example 5-1.) When this summary query is executed, the result set will contain one summary row for
a.each city with invoice totals over $500 c.each city with invoice average over $500
b.each vendor with invoice totals over $500 d.each vendor with invoice average over $500
October 12, 2011 at 10:05 pm
Homework? Test?
No one here is going to give you the ansers. How about you tell us which answer you think is correct and why you think it is correct. We are more than willing to help you reason out the answers so that you learn something rather than having it handed to you.
October 12, 2011 at 10:14 pm
ok for the first one I think it is b but c confuses me
The second one I think the answer is b but is this a logical error ? I don't think so. may be i'm wrong. It could be a because you can specify fields in the having clause that are in the grouping by clause.
The third one, I think the answer is a.
I am not trying to cheat or something and I studied the book before I posted the topic here. I have some answers in my mind but I'm the kind of person who wants to make sure. To prove that I'm not trying to cheat is what kind of teacher that gives a test with only 3 questions. I am good with other 27 questions only those three confused me.
I'm waiting for your replies guys, and thank you in advance.
Regards
October 12, 2011 at 10:22 pm
You have given us guesses, but you haven't tried to support your answers by analyzing the code. Put together some sample data, 5 or 6 rows and step through the code and see if what you get matches what you think the answer may be. As an instructor might say, show us your work. If your logic is good we'll tell you. If you are off base we'll let you know that as well and then push you in the right direction.
October 12, 2011 at 10:23 pm
Let me add this, what you need to do is the same thing we do when trying to debug code others have written. The skills we are trying to help you build will benefit you far beyond the classroom.
October 12, 2011 at 10:53 pm
VendorID--------------Column2
37--------------------224.00
72---------------------85.31
80---------------------90.36
83---------------------579.42
106--------------------503.20
So I think that because I used the group by clause it is going to give me the unpaid balance which is calculated from SUM for each vendorid, and I know that it is going to grap only rows that has balance due because of the where clause. That is how I chose answer b for the first question.
Second question :
VendorState----------VendorCity-------------VendorName------------------InvoiceQty-----------------InvoiceAvg
AZ--------------------Phoenix---------------Wells Fargo Bank-------------------1------------------------662.00
CA---------------------Fresno---------------Digital Dreamworks------------------1------------------------7125.34
CA---------------------Fresno------------Fresno County Tax Collector------------1------------------------856.92
CA--------------------Fresno--------------------Pollstar-------------------------1-----------------------1750.00
CA--------------------Fresno-------------------Yesmed, Inc---------------------1-----------------------4901.26
The only problem I see from the choises is that the fifth field name is wrong but the choice says it does'nt match the data type (It's just a name) that's what make me hesitate. choice d is wrong cause you can't have aggregate functions in the where clause. choice a could work but it is not wrong to leave it in the where clause and choice c doesn't make any sense.
The third question same result set from question 2 because it is the same query
VendorState----------VendorCity-------------VendorName------------------InvoiceQty-----------------InvoiceAvg
AZ--------------------Phoenix---------------Wells Fargo Bank-------------------1------------------------662.00
CA---------------------Fresno---------------Digital Dreamworks------------------1------------------------7125.34
CA---------------------Fresno------------Fresno County Tax Collector------------1------------------------856.92
CA--------------------Fresno--------------------Pollstar-------------------------1-----------------------1750.00
CA--------------------Fresno-------------------Yesmed, Inc---------------------1-----------------------4901.26
this one drove me crazy. I excluded c and d because we are doing total not average so that leaves me between a and b.
Now I have multiple rows for the same city but different vendors I think the answer is a because it gives me a row whenever a city has a total is over 500 but choice b confused me because it gives me a row for each vendor that has a total over 500 .
Sorry for the unorganaized result sets but that is the only way I could make work
Thank you
Waiting ....
October 13, 2011 at 7:01 am
Assuming that a vendor exists in only one location, what do you think this code will return?
Look at the following to help you:
SELECT
VendorState, -- These values are the group by, one row for each unique combination
VendorCity, -- of VendorState, VendorCity, VendorName
VendorName, --
COUNT(*) AS InvoiceQty, -- Number of Invoices for each unique group
SUM(InvoiceTotal) AS InvoiceAvg -- Sum of invoice totals for each unique group
FROM
Invoices
JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE
VendorState < 'e' -- Select all records where VendorState < 'e'
GROUP BY
VendorState,
VendorCity,
VendorName
HAVING
SUM(InvoiceTotal) > 500 -- restrict result set to those where the sum of the InvoiceTotal for each unique group
ORDER BY
VendorState,
VendorCity,
VendorName;
October 13, 2011 at 5:25 pm
It will return one row for each vendor that meet the two conditions in where and having.
October 14, 2011 at 3:48 pm
pfm200586 (10/12/2011)Second question :
VendorState----------VendorCity-------------VendorName------------------InvoiceQty-----------------InvoiceAvg
AZ--------------------Phoenix---------------Wells Fargo Bank-------------------1------------------------662.00
CA---------------------Fresno---------------Digital Dreamworks------------------1------------------------7125.34
CA---------------------Fresno------------Fresno County Tax Collector------------1------------------------856.92
CA--------------------Fresno--------------------Pollstar-------------------------1-----------------------1750.00
CA--------------------Fresno-------------------Yesmed, Inc---------------------1-----------------------4901.26
The only problem I see from the choises is that the fifth field name is wrong but the choice says it does'nt match the data type (It's just a name) that's what make me hesitate. choice d is wrong cause you can't have aggregate functions in the where clause. choice a could work but it is not wrong to leave it in the where clause and choice c doesn't make any sense.
I would say that your instructor's wording could use some work -- the problem isn't that the field name does not match the data type, but that it does not match the data -- a sum is not an average. I would not dismiss the name as "It's just a name", however, since poor naming creates a lot of problems in programming when one makes assumptions based on the name without realizing that it does not actually describe the data. I think you are correct that the naming issue is what the question was trying to address -- your hesitation, is probably just the result of questionable wording.
The third question same result set from question 2 because it is the same query
VendorState----------VendorCity-------------VendorName------------------InvoiceQty-----------------InvoiceAvg
AZ--------------------Phoenix---------------Wells Fargo Bank-------------------1------------------------662.00
CA---------------------Fresno---------------Digital Dreamworks------------------1------------------------7125.34
CA---------------------Fresno------------Fresno County Tax Collector------------1------------------------856.92
CA--------------------Fresno--------------------Pollstar-------------------------1-----------------------1750.00
CA--------------------Fresno-------------------Yesmed, Inc---------------------1-----------------------4901.26
this one drove me crazy. I excluded c and d because we are doing total not average so that leaves me between a and b.
Now I have multiple rows for the same city but different vendors I think the answer is a because it gives me a row whenever a city has a total is over 500 but choice b confused me because it gives me a row for each vendor that has a total over 500 .
As you noted, there are multiple rows for the same city, so I think you can reject "a" immediately -- it specified that there would be a single summary row for each city and that's not the case. Keep in mind that each successive member of the "group by" specifies a subgroup within the previously identified groups so, at the lowest level you are grouping by vendors within cities within states - your summary rows, then, are by vendor. While your observation that it outputs rows for every city with a total over 500 may be true -- if so, that is, no doubt, a coincidence in the data. If you want to prove to yourself that it is actually outputting a row for each vendor with a total over 500, and not each state with a total over 500, create data records where all vendors in a state have totals less than 500 but the total of all the vendors in the state (and thus the total for the state) is over 500. I think you will find that no data is output for that state.
- Les
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply