T SQL

  • When someone said they ran the SQL, I said with the corrected question they couldn't get those results.

    The newsletter is what the question goes out as, but it could potentially be changed if we find issues. I'm not sure what you want. Do you want us to correct questions or not? It we correct them, the newsletter will be different.

  • Please supply the right list of options when you post a question.

    the correct answer is

    Em Ma

    Vyas Mohan

    Mohan Shobha

    Shridhar Mohan

    Sourabh Mohan

  • Mark Pratt (5/27/2009)


    I don't look at these challenges much. Today's item doesn't encourage me to do so in the future.

    I used to be the same, but now I try to figure out what the questioner is trying to test. I've learned a lot from these questions. I can be 'morally' right and still lose the point - I've still figured something out.

  • cj_logan (5/27/2009)


    Question

    Why is the inner join the recommended option ? I know Microsoft recommends this way but no where can I find a reason ? According to the execution plan both are the same and both cost 50%

    SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid

    order by t1.en

    SELECT t1.en [Em], t2.en [Ma] FROM E t1 inner join E t2 on t1.mid = t2.eid

    order by t1.en

    Joins in ANSI syntax can be made explicit without mangling the where logic. Parsers and optimizations have less ambiguity to deal with under certain conditions. The old syntax of using *= for left and right joins can now be done away. Cross joins also become explicit. For programming, the cleaner and more explicit ANSI syntax could lead to faster debugging. It took me a while to transition, but now that I did, I like it.

  • sjsubscribe (5/27/2009)


    Joins in ANSI syntax can be made explicit without mangling the where logic. Parsers and optimizations have less ambiguity to deal with under certain conditions. The old syntax of using *= for left and right joins can now be done away. Cross joins also become explicit. For programming, the cleaner and more explicit ANSI syntax could lead to faster debugging. It took me a while to transition, but now that I did, I like it.

    I feel lucky... when I got involved with SQL Server, DBs, and DB Theory ANSI style joins were available. I actually had queries back on SQL 7 that performed better when I specified ANSI joins than when I used that kack nasty syntax (for whatever reason, but I was seeing actual differences in performance back then).

    I remember looking at a 12 table join in the older where clause syntax and spending quite a bit of time trying to figure out how all the joins worked. The first thing I did after I figured it out was to convert the query to ANSI syntax.

    For a few years I was teaching Intro to DB Theory at a local community college. I explained the Where clause Join and told my students if they ever turned in work using it that I'd take off points for bad style.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Steve Jones - Editor (5/27/2009)


    The newsletter is what the question goes out as, but it could potentially be changed if we find issues. I'm not sure what you want. Do you want us to correct questions or not? It we correct them, the newsletter will be different.

    How about a friendly suggestion to indicate on the web page that the question is different from what was mailed out? Maybe right above the list of answer choices? I like the QOTD's. They've helped me learn more about MSSQL.

  • I too have seleted option 3 hence wrong.... BAD LUCK!!

    I do agree with STEVE JONES; we need to be positive why people are more worried about points more than that wright or wrong? I liked positive approach of "ppcx" by giving suggestion.

    All are human beings and made mistakes... We are not COMPUTERS!

    We need to encourage people to post more and wright about SQL.

    SUCCESS COMES OUT OF FAILURE ONLY.

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

  • Hi Guys

    This answer is correct when there is no order by clause in the question.

    row1 - Vyas Mohan

    row2 - Mohan Shobha

    row3 - Shridhar Mohan

    row4 - Sourabh Mohan

    else

    this is correct.

    Row 1 - Mohan Shobha | Row 2 - Shridhar Mohan | Row 3 - Sourabh Mohan | Row 4 - Vyas Mohan

  • first Row- Mohan Shobha

    Second Row-Shridhar Mohan

    third Row- Sourabh Mohan

    Fourth Row- Vyas Mohan

  • Barry McConnell (5/27/2009)


    SanjayAttray (5/27/2009)


    WOW.........never seen so many complaints for a single question on QOD before. Good job Reddy. You made all forum posters stand united.

    You must not have seen the MDX Except question then! :w00t:

    Yep Barry. Look like I missed that fun before.

    SQL DBA.

  • The problem is that the order by clause was left out on the initail page.

    Today's Question:

    What is the output for the query below?

    CREATE TABLE E (eid int, mid int, en char(10) )

    INSERT E SELECT 1,2,'Vyas'

    INSERT E SELECT 2,3,'Mohan'

    INSERT E SELECT 3,NULL,'Shobha'

    INSERT E SELECT 4,2,'Shridhar'

    INSERT E SELECT 5,2,'Sourabh'

    SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid

    when you click the "Click Here" the question has an additional line with the order by clasue

    CREATE TABLE E (eid int, mid int, en char(10) )

    INSERT E SELECT 1,2,'Vyas'

    INSERT E SELECT 2,3,'Mohan'

    INSERT E SELECT 3,NULL,'Shobha'

    INSERT E SELECT 4,2,'Shridhar'

    INSERT E SELECT 5,2,'Sourabh'

    SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid

    order by t1.en

  • The question was edited to include an ORDER BY. Questions get edited if mistakes are found.

  • easy one! option 1 is the only answer that satisfies the ORDER BY!

  • Was the typo intentional? I know Attention to detail is important for the DBA - but intentional typo's are bad form.

Viewing 14 posts - 61 through 73 (of 73 total)

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