How many rows are returned from the query below?

Question

How many rows are returned from the query below?

Viewing 15 posts - 46 through 60 (of 110 total)

You must be logged in to reply to this topic. Login to reply

Longy SSCommitted Points: 1613 More actions · Answer 1

james-887814 (3/3/2010)
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.

If there was Karma on here, you should get it for this!!

Would be a big fan of the whining being in a separate thread!

garret06 Grasshopper Points: 11 More actions · Answer 2

Toreador (3/3/2010)
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.

So what you're saying is that I should be able to get my answer by doing?

SELECT D.Color FROM Duck D WHERE Left_Leg = Right_Leg;

I would probably be safer in my duck color gathering if I created a stored procedure and passed the leg in as a parameter. Now, I'll just need to determine the data type of the leg and I think I'll be set.

Chris Houghton SSCrazy Points: 2497 More actions · Answer 3

Chad Crawford (3/3/2010)
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

The complete results from the tblusers (lets assume 2 x users) are cross joined against the results from the CTE (which only selects 1 row from tblusers using top 1) you therefore get 2 x rows returned from the tblusers (using my assumption) each joined with the 1 row selected from the CTE - Users table.

Therefore the 1st two columns (tblusers) return unique results by row whereas the second two colums (users CTE) return the same results twice by row.

1chris1chris

2jon1chris

Hope this makes sense.

Oleg Netchaev SSCertifiable Points: 5272 More actions · Answer 4

I think that this was an OK QoD, maybe a bit too easy to deduce the correct answer, but not every question of the day has to be complex. On the other hand one must make few assumptions before figuring the answer. Though the assumption are reasonable, it still would be better if the conditions were disclosed. Something like this in the notes after the query: Assume that your server is SQL Server 2005 and your database has a table named tblUsers, which has many records (horrendous name though :hehe:, violates just about every reasonable naming convention).

Then there would be no ambiguity about the correct answer.

Oleg

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 5

I think sometimes we are guilty of over analyzing a problem. Questions such as "How many rows are currently contained in tblUsers?" leads to a judgement that the question is flawed. I too noticed that if tblUsers contained 0 or 1 rows, the results would be different. But the reasonable assumption is that a table of users in a production database contains more than 1 user and given that, the results will always be two rows.

Sometimes a question is just a simple question with something to learn.

Converting oxygen into carbon dioxide, since 1955.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

bitbucket-25253 (3/3/2010)
kaspencer
Your reputation is now properly in the gutter.
Remember that this should be a group of professionals, and trash talk is not appropriate, nor appreciated by those of us who are professionals.
ChiragNS
I got an error. There is no script for table creation and data. How can we come to any conclusion.
Answer: By thinking.
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

Ron makes some very good points here - I agree.