How many rows are returned from the query below?

  • free_mascot

    One Orange Chip

    Points: 27168

    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: 27168

    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: 4207

    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

    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

    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

    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

    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

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

  • Noel McKinney

    Hall of Fame

    Points: 3377

    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

    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: 442116

    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: 11222

    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

    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

    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.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

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

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

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