Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««89101112»»»

How many rows are returned from the query below? Expand / Collapse
Author
Message
Posted Friday, March 5, 2010 9:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 7:31 AM
Points: 155, Visits: 386
Chad Crawford (3/5/2010)

If it's the points you need - there are about a half dozen different ways to guarantee you get the QOD right every time, and I'd be happy to share if you want some (I use two regularly - I allow myself to Google/BOL before answering and sometimes I'll read the discussion before answering if there is something I'm not clear on, but I'll already have my decision made based on what I expect to find before looking. Neither of those guarantees success, but there are two other techniques that do).


I don't care about the points. I have gotten more than twice the points from the discussion.

Yes I get satisfaction from getting answers correct but really its about all the cool stuff I learn and I find more value in the discussions and debate that sometimes comes out of a QOTD and an article.

My issue is that all of the answers could have been correct with the information that was given. A few people point this out and many more come along and say the assumptions you had to make to get the correct listed answer were reasonable.

I think it is very reasonable to assume that we are using SQL Version 9+. Just keep in mind some noobies might be stuck on 2000 and may not know it is a 2005+ feature.

I think it is somewhat reasonable to assume the table exists.

I do not think it is reasonable to make an assumption about the data in a table in any case ever.


User table contains X amount of records.


Just adding that would have changed the whole outlook on this question.

As for the post your own QOTD crowd. Right now I am trying to think of a question to prove my point about ambiguous answers and assumptions. The best one I can think of so far is this particular question but with a different answer and explanation.
Post #877753
Posted Friday, March 5, 2010 10:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
Fatal Exception Error (3/5/2010)
Chad Crawford (3/5/2010)

If it's the points you need - there are about a half dozen different ways to guarantee you get the QOD right every time, and I'd be happy to share if you want some (I use two regularly - I allow myself to Google/BOL before answering and sometimes I'll read the discussion before answering if there is something I'm not clear on, but I'll already have my decision made based on what I expect to find before looking. Neither of those guarantees success, but there are two other techniques that do).


I don't care about the points. I have gotten more than twice the points from the discussion.

Yes I get satisfaction from getting answers correct but really its about all the cool stuff I learn and I find more value in the discussions and debate that sometimes comes out of a QOTD and an article.

My issue is that all of the answers could have been correct with the information that was given. A few people point this out and many more come along and say the assumptions you had to make to get the correct listed answer were reasonable.

I think it is very reasonable to assume that we are using SQL Version 9+. Just keep in mind some noobies might be stuck on 2000 and may not know it is a 2005+ feature.

I think it is somewhat reasonable to assume the table exists.

I do not think it is reasonable to make an assumption about the data in a table in any case ever.


User table contains X amount of records.


Just adding that would have changed the whole outlook on this question.

As for the post your own QOTD crowd. Right now I am trying to think of a question to prove my point about ambiguous answers and assumptions. The best one I can think of so far is this particular question but with a different answer and explanation.


If that is the point of your question, what good is it? Is it going to teach us something about SQL Server, SSIS, SSAS, SSRS, Database Design, Humor (Steve has posted some humor related questions), etc? Is it going to test our knowledge about any of these topics? If the answer is no to both, is it worth asking the question?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #877789
Posted Sunday, March 7, 2010 5:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 6, 2014 12:28 AM
Points: 176, Visits: 565
This discussion may have ended a few pages ago if those who complained gave a specific makeover of how the QOTD could have been.

When members feel there is a better way of presenting the QOTD, they should post the redone QOTD in the discussion.

Then, we all can say if it would have been better compared to the original.

I am no longer learning from the question's discussion, but reading a ceaseless back-forth of two sides entrenched in a situation unlike World War I.

May I see we all leave this discussion, or see an improved version for us to learn again?

Post #878371
Posted Sunday, March 7, 2010 6:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:53 PM
Points: 262, Visits: 300
Maybe I've missed something, but I think at least 3 concrete suggestions have been given as how to alter the question...

So how about:

WITH Users(UserID, UserName) AS
(
SELECT TOP 1 intUserID, strUserName FROM tblUsers
)
SELECT TOP 2 tblUsers.*, Users.* FROM tblUsers, Users

Assuming tblUsers is a valid table containing multiple rows, how many rows would be returned by the above query?
Post #878377
Posted Sunday, March 7, 2010 6:32 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:29 PM
Points: 8,563, Visits: 9,063
Open Minded (3/7/2010)
May I see we all leave this discussion, or see an improved version for us to learn again?


Maybe I shouldn't do this, but here goes:


With the following query:
WITH Users(UserID, UserName) AS
(
SELECT TOP 1 intUserID, strUserName FROM tblUsers
)
SELECT TOP 2 tblUsers.*, Users.* FROM tblUsers, Users

Check all of the following which are not possible results:

1. Error message: invalid column name strUsername
2. Two rows differing from each other in all attributes
3. One row
4. The commands completed correctly (no rows)
5. Error message: invalid column name intUserID
6. Error message: table tblUsers does not exist
7. Two rows, differing from each other only in the attributes userID and UserName
8. Error message: table Users does not exist
9. Two rows, differing from each other only in the attributes strUserName and intUserID
10. Two rows, differing from eachother only in the attribute intUserID
11. Two rows, differing from each other only in the attribute strUserName


I could extend the list further further (even without adding privilege errors), but I won't. And I'll leave it as an excercise for others to work out the correct answer.

I think two things are there to be learnt from the ghastly concoction above (it really is ghastly, a proper question would prune the possible answers list sensibly).

(A) The question as stated requires a lot of assumptions to be made to define the result closely.
(B) Many (indeed most) of those assumptions are assumptions that one would expect to be made in the context of QOTD.

(A) above suggests it's an extremely bad question, and that was my first reaction to it.
But (B) suggests that maybe it isn't so bad - in fact in my view the only assumption required to get the stated answer that isn't justifiable in a QOTD context (when the question is stated as posed) is the assumption that the tblUsers table contains at least two rows and that the top two rows are in fact distinct, and in reality most people will make that assumption and give the "correct" answer. This makes me think that the question was less bad than I thought.

When stated in the new form above, of course, all of the possible assumptions have to be considered, and results from all combinations of assumptions considered; this is very different, because none of the assumptions can be taken for granted, so the answers are different. For example 6 above is not one of the impossible results, but it would be impossible with the question as stated if I'm right about which assumptions are reasonable in a QOTD context with the question stated like that.


Tom
Post #878381
Posted Sunday, March 7, 2010 6:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 6, 2014 12:28 AM
Points: 176, Visits: 565
To the ones who posted the redone (one concise, another well-defined) , I can say your versions were well thought-of.

Here's to hoping the QOTDs will be much better with the constructive examples you gave under discussion.
Post #878386
Posted Monday, March 15, 2010 10:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 27, 2011 1:29 AM
Points: 54, Visits: 141
I agree this question has too many assumtions making it impossible to get the answer right unless you use probability to decide which is the most likely set of assumptions.

I am surprised at how many of you are saying "I ran this and..." or similar. To me the questions are about YOUR knowledge of sql not your servers knowledge. You server knows everything about sql or there is a problem which requires patching. Why don't some of you try and answer the question from you own knowledge and take the odd wrong answer? How are you ever going to drop a point if you get the server to answer the question for you?
Post #883138
Posted Monday, March 15, 2010 12:25 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:29 PM
Points: 8,563, Visits: 9,063
david.murden (3/15/2010)
I am surprised at how many of you are saying "I ran this and..." or similar. To me the questions are about YOUR knowledge of sql not your servers knowledge. You server knows everything about sql or there is a problem which requires patching. Why don't some of you try and answer the question from you own knowledge and take the odd wrong answer? How are you ever going to drop a point if you get the server to answer the question for you?


Maybe people do answer from their own knowlege, see that their answer is said to be wrong, and then try copying the code into a query window and seeing what happens - that is actually a good way of learning. I suspect a lot of people do that. I do it myself sometimes (but not, in my case, with questions like this one, where it seems so obvious that you just have to pick the right assumptions that copying the code and running it is utterly pointless - but to some people it will be less obvious and maybe it's still a sensible thing to do.)


Tom
Post #883242
Posted Monday, March 15, 2010 3:23 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 9, 2011 8:51 AM
Points: 291, Visits: 389
My answer was right (correct). As written, the only correct answer to this question is "Error". I would like my 2 points please.
Post #883386
Posted Tuesday, March 16, 2010 3:17 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 11:08 PM
Points: 21,385, Visits: 9,602
Where's the frighing unsubscribe button????
Post #883611
« Prev Topic | Next Topic »

Add to briefcase «««89101112»»»

Permissions Expand / Collapse