March 3, 2010 at 6:54 am
Toreador (3/3/2010)
Lynn Pettis (3/3/2010)
Second, it doesn't matter how many rows are in the table tblUsers (unless it only contained one row, but everyone so far has assumed more than one), this query will ALWAYS return two rows. The CTE selects exactly ONE row (SELECT TOP 1) and the following select (SELECT TOP 2) returns exactly TWO rows. The cartesian product that results is therefore TWO ROWS.I didn't assume it contained more than one. As I said before, I didn't know, so made a guess. It could have contained 1 row (a newly shipped database is likely to contain a single pre-defined admin user), or no rows (a newly shipped database could equally contain no users).
And if you reread my post you will see that I actually stated that in my post. Nor am I the one that made the initial assumtion that tblUsers contained more than one row, just read the posts prior to mine, quite a few people made that same assumption.
March 3, 2010 at 7:01 am
bitbucket-25253 (3/3/2010)
Fatal Exception Error
I think part of his point is that versions were not specified which is fairly important.
Disagree. If you are attempting to be a database developer or a DBA you should be familiar with the features / limitations of each version of SQL Server that you might have the good fortune to work with.
Remember the object of the QOD is to teach or test each individuals knowledge of various aspect of SQL. So even if you answered the question incorrectly remember you did learn something.
So all told a good question of the day
I knew this would not work in versions prior to SQL Server 2005.
I got the question wrong simply because I made a wrong assumption. I know I am not alone in this and it is not the first and probably will not be the last time that someone gets a question wrong.
To all of you people out there saying to "Think", I have a thought for you.
What happens if I submit this same question six months from now when we have all forgotten about this and make the answer Error. The explanation would be include something along the lines of tblUsers does not exist since we never created it SQL Server will spit out an error..
March 3, 2010 at 7:03 am
honza.mf (3/3/2010)
Lynn Pettis (3/3/2010)
Second, it doesn't matter how many rows are in the table tblUsers (unless it only contained one row, but everyone so far has assumed more than one), this query will ALWAYS return two rows. The CTE selects exactly ONE row (SELECT TOP 1) and the following select (SELECT TOP 2) returns exactly TWO rows. The cartesian product that results is therefore TWO ROWS.Please, don't count me in your everyone. I don't like such assumptions.
You omitted the possibility tblUsers is empty. And why not?
I'm familiar with a database with some tables, that are known to have always exactly one row. Very similar tables can have more rows and never can be empty (Due to some logic there must be at least two rows).
Without this knowledge, you are guessing from the coffee grounds.
I am also familiar with databases that have had tables that contained exactly one row, it was never a Users table. I have never seen a database with zero or one record in a Users table though I could see it in a brand new database for a brand new company or organization that just opened its doors, but I don't think it would stay at zero or one record for very long.
And I'm sorry if you don't like assumptions, do you work with users that always tell you everything you need need the first time? Must be nice, most users I have had to work with take several rounds of discovery to learn enough to not have to make any more assumptions while working on a project. Usually a learning process on both sides.
March 3, 2010 at 7:06 am
Fatal Exception Error (3/3/2010)
bitbucket-25253 (3/3/2010)
Fatal Exception Error
I think part of his point is that versions were not specified which is fairly important.
Disagree. If you are attempting to be a database developer or a DBA you should be familiar with the features / limitations of each version of SQL Server that you might have the good fortune to work with.
Remember the object of the QOD is to teach or test each individuals knowledge of various aspect of SQL. So even if you answered the question incorrectly remember you did learn something.
So all told a good question of the day
I knew this would not work in versions prior to SQL Server 2005.
I got the question wrong simply because I made a wrong assumption. I know I am not alone in this and it is not the first and probably will not be the last time that someone gets a question wrong.
To all of you people out there saying to "Think", I have a thought for you.
What happens if I submit this same question six months from now when we have all forgotten about this and make the answer Error. The explanation would be include something along the lines of tblUsers does not exist since we never created it SQL Server will spit out an error..
I would put that one in the "Trick Question" category. What would be the point of the question? You are making the assumption here that every QotD must assume that everyone has the same environment. If you were to state that this query were run in a pristine (unmodified) AdventureWorks database, then I might agree with you, MIGHT.
March 3, 2010 at 7:24 am
Fatal Exception Error
Here is a challenge for you.
1. Compose a QOD that full fills the objectives of the QOD.
You might want to start by reading Steve Jones article on the objective of the QOD at:
http://www.sqlservercentral.com/articles/SQLServerCentral/62764/
Good luck ....
March 3, 2010 at 7:25 am
I'm of the opinion that it should be clear exactly what the question is testing for. If the author had specified SQL Server 2005 or 2008 and created a temp #tblusers then referenced it within the query it would have dispensed with the ambiguity and the resultant negative comments. Since this would have required only a few more seconds work on the authors part and given the traditional criticism of incomplete, ambiguous or misleading questions on this board, I cannot see why this wasn't done.
March 3, 2010 at 7:29 am
Lynn Pettis (3/3/2010)
And I'm sorry if you don't like assumptions, do you work with users that always tell you everything you need need the first time? Must be nice, most users I have had to work with take several rounds of discovery to learn enough to not have to make any more assumptions while working on a project. Usually a learning process on both sides.
90% of users I know are liers, greater liers and horrible liers. Thats why I don't like assumptions. Certainly I must make many assumptions, but I must ask the bloody bastard if they are correct. Any work done without his answers is just lost time.
Just as an example: Customers DBA asked me: Why our users don't see X on form? I asked him some questions and then I wanted a profiler trace. The trace came after several weeks. From the trace I discovered he hasn't checked the box "Show X on forms". One of my bad assumption. :hehe:
I feel we are writing about same things, but we two are little bit different.
March 3, 2010 at 7:43 am
honza.mf (3/3/2010)
Lynn Pettis (3/3/2010)
And I'm sorry if you don't like assumptions, do you work with users that always tell you everything you need need the first time? Must be nice, most users I have had to work with take several rounds of discovery to learn enough to not have to make any more assumptions while working on a project. Usually a learning process on both sides.
90% of users I know are liers, greater liers and horrible liers. Thats why I don't like assumptions. Certainly I must make many assumptions, but I must ask the bloody bastard if they are correct. Any work done without his answers is just lost time.
Just as an example: Customers DBA asked me: Why our users don't see X on form? I asked him some questions and then I wanted a profiler trace. The trace came after several weeks. From the trace I discovered he hasn't checked the box "Show X on forms". One of my bad assumption. :hehe:
I feel we are writing about same things, but we two are little bit different.
My point on assumptions is this, sometimes you have to make them as you start working on a project. They normally lead to additional questions that the users need to answer to fill in the gaps.
I know users never tell you everything. Previous employer, we had an intermittent problem where sometimes a user would edit a quote and close as an order, but it would retain the quote number instead of getting a new order number. This was a problem for many years that we developers just could not recreate. We finally solved the problem after we started logging user interaction when we moved to PC's and terminal emulators from dumb tubes. Turned out the problem occurred when the user entered BO (Build Order) while editting the quote. User never told us this nor that they had to reenter all the customers address info as well.
But back to the QotD, here, you sometimes just have to make an assumption using common sense. In this case, it makes sense that the table tblUsers would have more than 2 rows of data.
March 3, 2010 at 7:50 am
I have no problem with the question - I think the assumptions that needed to be made although not clear, were reasonable.
I do have a question about the answer though, if anyone would mind helping me. It states: "with same row value used for both rows of the table Users."
It sounds like the result should be the same row returned twice, but I don't see how that is enforced (no order by and it's a cross join so no join criteria). When I ran the query against an existing table, I did in fact get two different rows returned. Did I misunderstand the answer, or am I missing some construct?
Thanks,
Chad
March 3, 2010 at 8:03 am
Lynn Pettis (3/3/2010)
But back to the QotD, here, you sometimes just have to make an assumption using common sense. In this case, it makes sense that the table tblUsers would have more than 2 rows of data.
You are right. Using common sense 2 rows of data are returned. Answer "2" is correct.
Most of these discussions on "poor wording in QotDs" is just playing with words, their precise meaning or possible assumptions.
March 3, 2010 at 8:11 am
I did most of my learning from the posts. I think that makes it a good question (even if I made a bad assumption and lost a point). 😉
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
March 3, 2010 at 8:15 am
Wow. A few people get the question wrong and they assume the person writing the question must cover every version/release of SQL, must write the query to make sure every user database in the world must contain the table the query is written against, etc.
It's 1 point, get over it. There are assumptions on almost every question especially the T-SQL ones.
Personally, I think the question was a no brainer because I assumed any user table would have at least a handful of rows (ie >=2) in it.
Forum Moderator, as a side note, I REALLY liked a question a few days ago that was a .jpg so users couldn't copy and paste the response. That method at least makes a user think rather than copy and paste for an answer.
March 3, 2010 at 8:24 am
I think sometimes people complain just to complain.
Maybe we can set up two threads for every question - one for learning one for whining?
Assumption 1: I am using a version released within the past 5 years even though the production database I use is SQL 2000. I know there are a lot of SQL 2000 users out there, but unless the question states to use 10-year-old technology I am going to assume it is using something more current.
Assumption 2: The query is looking for a table - the question is not trying to show that if you reference a nonexistent table you will get an error. If you have to learn that then you may not deserve to get this "advanced" question correct.
Assumption 3: I have more than one user in a "users" table. If I only had one user I would be better off writing the user's info on a sticky note and not spending the effort on SQL.
Assumption 4: These questions are tools for learning. Thoughtful discussion promotes learning. Whining does not. If you want to post that it does not work on all versions that would be great useful info. But everybody who got it wrong does not have to try using the version card to get points awarded.
Assumption 5: Nobody cares about how many points you have and it will not make anybody like you any more. If you can give relevant helpful advice in the forums you will gain respect of your piers.
March 3, 2010 at 8:33 am
Ok, I got it wrong. But, what color is the duck?
Seriously, I need to know.
o_0
March 3, 2010 at 8:35 am
garret06 (3/3/2010)
Ok, I got it wrong. But, what color is the duck?Seriously, I need to know.
o_0
I'm not sure. But I do know that one of its legs is both the same.
Hope this helps.
Viewing 15 posts - 31 through 45 (of 110 total)
You must be logged in to reply to this topic. Login to reply