NULL Values and Joins

  • SanDroid

    SSChampion

    Points: 10068

    Comments posted to this topic are about the item NULL Values and Joins

  • Seth Kramer

    SSC Eights!

    Points: 953

    I accept I got it wrong, but what do you mean the second transaction fails? Only the insert null into the primary key field statement fails. There are no failed inserts on Table2.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question, but only 1 point?

    Transactions and the NULL insert in the primary key have actually nothing to do with the actual question, they just serve as trickery in my opinion.

    And I don't really get the last line of the explanation. B not C is the correct answer?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • vk-kirov

    SSCertifiable

    Points: 7686

    No rows were inserted into table2? B not C is the correct answer? Terrible, terrible explanation 🙂

  • Carlo Romagnano

    SSC-Insane

    Points: 21972

    vk-kirov (12/20/2010)


    No rows were inserted into table2? B not C is the correct answer? Terrible, terrible explanation 🙂

    The explanation is more terrible than the question: I should read it at least 7 times!

    :-D:-D:-D:-D:-D:-D:-D

  • philip.cullingworth

    SSCrazy

    Points: 2150

    I have to disagree with the answer. I ran the code on SQL server 2005 and got the following output:

    a b c d

    4 join1 4 join2

    4 join1 4 join2

    2 join3 2 one

    2 join3 2 one

    1 one NULL NULL

    NULL NULL NULL two

    NULL NULL NULL three

    As I had worked out the same output before running and answering the question I decided that A, B, D and E could not possibly be correct so went for C as the only remaining option.

    What have I missed?

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    philip.cullingworth (12/20/2010)


    I have to disagree with the answer. I ran the code on SQL server 2005 and got the following output:

    a b c d

    4 join1 4 join2

    4 join1 4 join2

    2 join3 2 one

    2 join3 2 one

    1 one NULL NULL

    NULL NULL NULL two

    NULL NULL NULL three

    As I had worked out the same output before running and answering the question I decided that A, B, D and E could not possibly be correct so went for C as the only remaining option.

    What have I missed?

    Your output has 7 rows and the column d contains "three" for the 7th row, so it should be answer D.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • philip.cullingworth

    SSCrazy

    Points: 2150

    da-zero (12/20/2010)


    Your output has 7 rows and the column d contains "three" for the 7th row, so it should be answer D.

    But Answer D is

    7 rows where column d = three

    I only have 1 row where column d = three :ermm:

  • Carlo Romagnano

    SSC-Insane

    Points: 21972

    philip.cullingworth (12/20/2010)


    da-zero (12/20/2010)


    Your output has 7 rows and the column d contains "three" for the 7th row, so it should be answer D.

    But Answer D is

    7 rows where column d = three

    I only have 1 row where column d = three :ermm:

    Please, read carefully the question: it asks how many rows are in the results and what is the value of column "d" on the last row.

  • philip.cullingworth

    SSCrazy

    Points: 2150

    Carlo Romagnano (12/20/2010)


    Please, read carefully the question: it asks how many rows are in the results and what is the value of column "d" on the last row.

    :blush: That will teach me. I read the code and assumed what the actual question would be. Can I blame being up in the middle of the night defrosting water pipes?

  • vk-kirov

    SSCertifiable

    Points: 7686

    philip.cullingworth (12/20/2010)


    But Answer D is

    7 rows where column d = three

    I only have 1 row where column d = three :ermm:

    I had read the answers several times before I realized there were some skipped words: "7 rows where [the value of] column d [in the last row] = three" :hehe:

  • ian.grace

    SSC Rookie

    Points: 35

    I don't understand how the first transaction could succeed given the attempt to insert the null value into the not-null field "a". Also, why is the last insert of the second transaction, VALUES(2 ,'one'), invalid? Help, I'm confused.

  • Carlo Romagnano

    SSC-Insane

    Points: 21972

    ian.grace (12/20/2010)


    I don't understand how the first transaction could succeed given the attempt to insert the null value into the not-null field "a". Also, why is the last insert of the second transaction, VALUES(2 ,'one'), invalid? Help, I'm confused.

    As posted above by vk-kirov and others, the explanation is terrible with a lot of mistakes.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    ian.grace (12/20/2010)


    I don't understand how the first transaction could succeed given the attempt to insert the null value into the not-null field "a". Also, why is the last insert of the second transaction, VALUES(2 ,'one'), invalid? Help, I'm confused.

    The first transaction succeeds because it is an explicit transaction. Those will only fail if you explicitly issue a ROLLBACK statement, which isn't the case. Since a COMMIT was issued, the 2 other INSERTS in the transaction are committed to the database.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 42 total)

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