How many rows are returned from the query below?

Question

Post reply

How many rows are returned from the query below?

Viewing 15 posts - 16 through 30 (of 110 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Question asked do not mention anything about table tblUsers

When executed the query ;

WITH Users(UserID, UserName) AS

(

SELECT TOP 1 intUserID, strUserName FROM tblUsers

)

SELECT TOP 2 tblUsers.*, Users.* FROM tblUsers, Users

Reults into following error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'tblUsers'.

Looks like other's have also faced the same issue here :hehe:

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Author could have eliminate the answer option "Error Message" from Question.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

kaspencer SSCarpal Tunnel Points: 4869 More actions · Answer 3

OBJECTION! OBJECTION! OBJECTION!

Yet another useless question.

I knew I had no table named "tblUsers", and so I answered "Error Message".

And I was correct - that was the answer I got.

I demand my point. And I also demand, yet again, that standards are improved regarding the quality of these questions, and the background information to be supplied before they should be run.

And to the Question Mmoderators: do your job. Your reputation is now properly in the gutter.

Kenneth Spencer

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer

simon.whale Ten Centuries Points: 1393 More actions · Answer 4

After researching i dont think it is a stupid question, just an incomplete question. As its a cartesian join (if the CTE is relevant im not sure!?!?)

just that he / she forgot to include the create table statement for tblusers.

Robbert Hof SSCrazy Points: 2778 More actions · Answer 5

I created a table tblUsers and inserted some data.

The query failed with this error:

Server: Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'WITH'.

I am using SQL Server 2000.

On SQL Server 2005 no errors where displayed (2 rows).

mmmmm, bad question for earning points, good question for learning

Robbert

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

I got an error. There is no script for table creation and data. How can we come to any conclusion.

"Keep Trying"

ohack SSCrazy Points: 2043 More actions · Answer 7

Yes, not a great question. It's testing whether we understand what a Cartesian product/cross join is but the answer depends on how many rows tblUsers has. I assumed from its name that it would be pretty large and therefore got the point. For all the people who just copied the code into SSMS... isn't that cheating anyway? 😉 Robert - Common Table Expressions (the WITH .. bit) were new in SQL 2005 so this is not valid in 2000

Fatal Exception Error SSChasing Mays Points: 609 More actions · Answer 8

I think part of his point is that versions were not specified which is fairly important.

Noel McKinney Hall of Fame Points: 3451 More actions · Answer 9

This is a great question, if nothing else for the comments it's generated. It contains both a CTE and a cross join, and it appears that some are unfamiliar with these. There's also some learning in that CTEs are new with SQL Server 2005.

I'm not sure why anyone should be disappointed that copying/pasting the code should give the answer without any additional effort. It's not a big deal to create a two-column table called tblUsers and put some records in it.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

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

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

I don't agree that the question was poorly written, just poorly read.

First, it is obvious that this question was directed to SQL Server 2005/2008 because of the CTE. CTE's were introduced in SQL Server 2005.

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.

The fact that you don't have the table tblUsers on your system where you attempted to run the query DOES NOT make this question bad or wrong, nor entitle you to get your points back.

Reread the question and think it through logically, piece by piece.

Toreador SSChampion Points: 11358 More actions · Answer 12

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).

simon.whale Ten Centuries Points: 1393 More actions · Answer 13

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

I agree with your post.. and i dont want my point back as im learning on the way, originally i thought the tblusers was ommited on purpose to either raise an error or do some kind of black magic in the way the join works!

honza.mf SSCertifiable Points: 5519 More actions · Answer 14

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

What's the freaking point of having a users table if you have less than 2 users in the production system?

That's not a bad assumption to take into consideration.

As others stated, you had to think on this question because it's not simply copy paste. Further more you had to think because not all was in the open. That's called communication... people often say something different than what they really mean to say and that's where good listening and therefore great communication comes from.

Assuming we're all professionals and that listening to clients' needs is vital to our profession, that question is probably the best opportunity to learn about yourselves...

See if you prefer to whine or to learn and also if you need to better communicate with your clients.

Great question when all is considered. 😀