NULL Values and Joins

Question

NULL Values and Joins

SanDroid

SSChampion

Points: 10068
More actions
December 18, 2010 at 3:23 pm

#228981

Comments posted to this topic are about the item NULL Values and Joins
December 20, 2010 at 1:26 am

This was removed by the editor as SPAM

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

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

Seth Kramer Ten Centuries Points: 1013 More actions · Answer 1

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: 259105 More actions · Answer 2

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

vk-kirov SSCertifiable Points: 7686 More actions · Answer 3

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

Carlo Romagnano SSC-Insane Points: 22662 More actions · Answer 4

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 More actions · Answer 5

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

abcd

4join14join2

2join32one

1oneNULLNULL

NULLNULLNULLtwo

NULLNULLNULLthree

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: 259105 More actions · Answer 6

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:
abcd
4join14join2
4join14join2
2join32one
2join32one
1oneNULLNULL
NULLNULLNULLtwo
NULLNULLNULLthree
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 More actions · Answer 7

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: 22662 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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: 22662 More actions · Answer 12

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: 259105 More actions · Answer 13

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