InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 22:37:43 GMT20http://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxWhoever posts question in QoD column, always makes sure your question is correct with zero syntax and semantic errors and also make sure when you ignore for syntax/semantic error atleast do not put the answer which evaluates according to SQL parser.E.g., Error : can not insert NULL in column b. This should be removed from being selected. People who answer the SQL puzzles always first look at the syntax first.Mon, 28 Feb 2011 03:50:04 GMTvinod.andani-874416http://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxsir you can help to me and send to my account the total marks just i have untill nowTue, 04 Jan 2011 00:14:18 GMTidiras17http://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxwhy you give me that without beeing before respondingTue, 04 Jan 2011 00:12:10 GMTidiras17http://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxThanks for the question, and the explanation of why the explanation seemed so weird.Tue, 28 Dec 2010 11:04:42 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx:) don't worry, it happened to me too, I did not read carefully the question. Morale of the story: it is better to pay attention at the requirements and just not knowing the correct answer, than missing the question and answer the wrong thing. However, the explanation they provided, that 'no rows are inserted in the Table2' is completely bizarre. (repairing frozen pipes qualify for not reading carefully)Sun, 26 Dec 2010 17:01:58 GMTSQLZealothttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxThis was an ... interesting question.I don't call it a good question, because of the complexity - not the complexity of the subject matter, but that of the number of statements, and the number of rows. We had to deal with behaviour of a failed insert in a transaction, then the behaviour of two kinds of joins, union, ordering, and the position of NULL values in an order by (which, for the record, is not the same in all RDBMS's; whether NULLs go first or last is not defined in the ANSI standard, but left as an implementation-dependant choice). And all that on tables with three or four rows.I think a good QotD should test one, maybe two subjects only. And preferably with a combination of statement complexity and number of rows that enables those with an understanding of the subject to work out the results in their head. This one severely pushed my ability to work it out in my head.SanDroid, I'd love to see more questions from you, as the idea for this question is great - but as a suggestion for the next time, consider using two or three rows in the tables, not more. And don't add extra complexity by adding weird primary keys, failed inserts, etc. Just giving the table population, a query with a join on nullable columns, and some believable but incorrect answer options would have been enough.As to the erroneous explanation - well, you already explained that this is the result of changes to question and explanation getting out of sync. Stuff like that happens, 'nuff said.Thu, 23 Dec 2010 03:31:29 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]volter_ddun-856145 (12/23/2010)[/b][hr]first thing it can't insert null value in to Table1 AND after running that query in sqlserver i didn't get result as mentioned Correct answer.[b]Correct answer: 7 rows where column d = three [/b]can any body explain how it is possible?thanks[/quote]Please, read the last post of the first page.Thu, 23 Dec 2010 01:04:03 GMTCarlo Romagnanohttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxsame result i got:-)Thu, 23 Dec 2010 00:31:56 GMTvolter_ddun-856145http://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxfirst thing it can't insert null value in to Table1 AND after running that query in sqlserver i didn't get result as mentioned Correct answer.[b]Correct answer: 7 rows where column d = three [/b]can any body explain how it is possible?thanksThu, 23 Dec 2010 00:15:11 GMTvolter_ddun-856145http://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]Bradley Deem (12/20/2010)[/b][hr]Ian, I know you've received an explanation already, but I wanted to add to that. You can control the behavior on error as follows in the comments below.[code="sql"]BEGIN TRAN INSERT INTO [Table1]( a,b) VALUES(1 ,'one') INSERT INTO [Table1]( a,b) -- Insert fails because the NOT NULL constraint on column [a] VALUES(NULL ,'five') -- Even though the above insert [b]STATEMENT[/b] fails, execution of the [b]BATCH [/b]continues. -- To halt execution after error use SET XACT_ABORT = ON (stops the [b]BATCH [/b]from executing) -- To reroute execution after error use TRY CATCH ([b]recommended[/b]) or check @@Error and use GOTO INSERT INTO [Table1]( a,b) VALUES(4 ,'join1') INSERT INTO [Table1]( a,b]) VALUES(2 ,'join3')COMMIT TRANGO[/code][/quote]That's a good explanation, thank you. I'm usually a bit lazy with my error handling and use of transactions, hence my confusion.Wed, 22 Dec 2010 03:47:56 GMTian.gracehttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxI got to wrong by misunderstanding the answer.Wed, 22 Dec 2010 01:09:50 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]Seth Kramer (12/18/2010)[/b][hr]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.[/quote]It looks to me as if the explanation paragraph following the reference to BoL has got sucked in from some other explanation, as it certainly has no connection with this question. Also, the wording of the possible answers is horrible and could easily have been fixed by changing "where" to "last" and sticking a comma in front of it.At first I thought I would have to rack my brains and remember how nulls are treated by ORDER BY, but then I noticed that there were only two options with 7 rows and neither of them had NULL as the last d value, so the ordering of nulls was irrelevant after all and it was actually a no-brainer question.Tue, 21 Dec 2010 08:53:29 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxThe question was very good! I almost answered it wrong because I thought it was left outer join instead of FULL outer join. For some reason the explanation does not match the correct answer.Tue, 21 Dec 2010 07:46:48 GMTEnigma475http://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxA confusing question, with a confusing explanation!I read it several times and couldn't work out if it was a true test or some kind of trick ;-)So I didn't learn anything today :-(Tue, 21 Dec 2010 05:10:46 GMTjts_2003http://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxThanks for the question.Mon, 20 Dec 2010 10:03:08 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]Carlo Romagnano (12/20/2010)[/b][hr][quote][b]ian.grace (12/20/2010)[/b][hr]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.[/quote]As posted above by vk-kirov and others, the explanation is terrible with a lot of mistakes.[/quote]Ian, I know you've received an explanation already, but I wanted to add to that. You can control the behavior on error as follows in the comments below.[code="sql"]BEGIN TRAN INSERT INTO [Table1]( a,b) VALUES(1 ,'one') INSERT INTO [Table1]( a,b) -- Insert fails because the NOT NULL constraint on column [a] VALUES(NULL ,'five') -- Even though the above insert [b]STATEMENT[/b] fails, execution of the [b]BATCH [/b]continues. -- To halt execution after error use SET XACT_ABORT = ON (stops the [b]BATCH [/b]from executing) -- To reroute execution after error use TRY CATCH ([b]recommended[/b]) or check @@Error and use GOTO INSERT INTO [Table1]( a,b) VALUES(4 ,'join1') INSERT INTO [Table1]( a,b]) VALUES(2 ,'join3')COMMIT TRANGO[/code]Mon, 20 Dec 2010 08:51:03 GMTBradley Deemhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[code="plain"]a b c d4 join1 4 join24 join1 4 join22 join3 2 one2 join3 2 one1 one NULL NULLNULL NULL NULL twoNULL NULL NULL three[/code]The reason I missed the answer is that I thought if you order by column d, "two" would be at the bottom. Doesn't "two" come after "three" in an order clause? Well, I ignored the "desc" after the order by. Darn it. Details, details.Mon, 20 Dec 2010 08:36:22 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]mtassin (12/20/2010)[/b][hr]They should be listed as 7 rows returned, the last row has column d = threeor something like that.[/quote]You are absolutely right about that.Mon, 20 Dec 2010 07:58:15 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]Carlo Romagnano (12/20/2010)[/b][hr][quote][b]philip.cullingworth (12/20/2010)[/b][hr][quote][b]da-zero (12/20/2010)[/b][hr]Your output has 7 rows and the column d contains "three" for the 7th row, so it should be answer D.[/quote]But Answer D is [quote]7 rows where column d = three[/quote]I only have 1 row where column d = three :ermm:[/quote]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.[/quote]Yes, and the answer, the correct answer, states in a sentance that there will be 7 rows where the value of column d will be three.Mon, 20 Dec 2010 07:49:46 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxThe answer has the following text in it.7 rows where column d = threeThe wording of the answers were confusing as hell.They should be listed as 7 rows returned, the last row has column d = threeor something like that.I got it right, but I spent more time analyzing the answers trying to figure out what they meant than I did actually analyzing the question. I kept looking at the code, and saying to myself, "None of the rows repeat enough time to get the word three seven times, etc etc... I ruled out the insert error because the error that comes back is about inserting into column a, not b.Mon, 20 Dec 2010 07:48:22 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]da-zero (12/20/2010)[/b][hr][quote][b]SanDroid (12/20/2010)[/b][hr]However there is a HUGE typo in the explination... B not C is the Answer?Disapointing.[/quote]May I ask you a question:why is the first post in this thread from you? (announcing which question this thread belong to).I was under the impression that you made the question...[/quote]Sorry I should have worded this reply better.The disapointing thing is that this question is eaxactly like it was originaly submitted.It was edited twice for errors after that, but the corrected version is not what was posted today.At least the answers and the script match, even if the explination does not.I took the time to edit and correct my question so it was easy to read, and have a good explination.It would have been nice to have seen that posted, and not the original with mistakes.UPDATE: I sent Steve an email explaining to him what happend. I asked that everyone get a free point for having to deal with such things on a Monday.Mon, 20 Dec 2010 07:27:19 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]SanDroid (12/20/2010)[/b][hr]I agree that if you read the question the answer is obvious.However there is a HUGE typo in the explination... B not C is the Answer?Disapointing.[/quote]May I ask you a question:why is the first post in this thread from you? (announcing which question this thread belong to).I was under the impression that you made the question...Mon, 20 Dec 2010 07:12:34 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxI agree that if you read the question the answer is obvious.However there is a HUGE typo in the explination... B not C is the Answer?Disapointing.Mon, 20 Dec 2010 07:08:01 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxI'm completely confused by this explanation. Especially this part:[quote]However, since the last insert in the second Transaction was invalid, no rows were inserted into table2. So B not C is the correct answer.[/quote]But D is given as the correct answer, and as far as I can tell, rows are inserted into table2. What am I missing?RonMon, 20 Dec 2010 06:23:28 GMTronmoseshttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxGood Monday morning question - math test and logic while clearing the cobwebs and trying to get my first cup of coffee;-)Made me think, question and reread.Mon, 20 Dec 2010 06:11:19 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxso the correct answer should be: "7 rows where [u]the last row's [/u]column d = three", not: "7 rows where column d = three", going by the way the question is phrased none of the answers are correct.Mon, 20 Dec 2010 05:44:29 GMTMarek Grzymalahttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxThanks for thatMon, 20 Dec 2010 04:14:03 GMTian.gracehttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]ian.grace (12/20/2010)[/b][hr]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.[/quote]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.Mon, 20 Dec 2010 04:06:05 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]ian.grace (12/20/2010)[/b][hr]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.[/quote]As posted above by vk-kirov and others, the explanation is terrible with a lot of mistakes.Mon, 20 Dec 2010 03:57:58 GMTCarlo Romagnanohttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxI 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.Mon, 20 Dec 2010 03:53:59 GMTian.gracehttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]philip.cullingworth (12/20/2010)[/b][hr]But Answer D is [quote]7 rows where column d = three[/quote]I only have 1 row where column d = three :ermm:[/quote]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:Mon, 20 Dec 2010 03:24:00 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]Carlo Romagnano (12/20/2010)[/b][hr]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.[/quote]: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?Mon, 20 Dec 2010 03:19:23 GMTphilip.cullingworthhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]philip.cullingworth (12/20/2010)[/b][hr][quote][b]da-zero (12/20/2010)[/b][hr]Your output has 7 rows and the column d contains "three" for the 7th row, so it should be answer D.[/quote]But Answer D is [quote]7 rows where column d = three[/quote]I only have 1 row where column d = three :ermm:[/quote]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.Mon, 20 Dec 2010 02:45:57 GMTCarlo Romagnanohttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]da-zero (12/20/2010)[/b][hr]Your output has 7 rows and the column d contains "three" for the 7th row, so it should be answer D.[/quote]But Answer D is [quote]7 rows where column d = three[/quote]I only have 1 row where column d = three :ermm:Mon, 20 Dec 2010 02:35:35 GMTphilip.cullingworthhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]philip.cullingworth (12/20/2010)[/b][hr]I have to disagree with the answer. I ran the code on SQL server 2005 and got the following output:[code="plain"]a b c d4 join1 4 join24 join1 4 join22 join3 2 one2 join3 2 one1 one NULL NULLNULL NULL NULL twoNULL NULL NULL three[/code]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?[/quote]Your output has 7 rows and the column d contains "three" for the 7th row, so it should be answer D.Mon, 20 Dec 2010 02:26:26 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxI have to disagree with the answer. I ran the code on SQL server 2005 and got the following output:[code="plain"]a b c d4 join1 4 join24 join1 4 join22 join3 2 one2 join3 2 one1 one NULL NULLNULL NULL NULL twoNULL NULL NULL three[/code]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?Mon, 20 Dec 2010 02:17:46 GMTphilip.cullingworthhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspx[quote][b]vk-kirov (12/20/2010)[/b][hr]No rows were inserted into table2? B not C is the correct answer? Terrible, terrible explanation :-)[/quote]The explanation is more terrible than the question: I should read it at least 7 times!:-D:-D:-D:-D:-D:-D:-DMon, 20 Dec 2010 01:40:31 GMTCarlo Romagnanohttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxNo rows were inserted into table2? B not C is the correct answer? Terrible, terrible explanation :-)Mon, 20 Dec 2010 01:32:57 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxThe question itself is acceptable, but, in my opunion, the explanation is somewhat lacking.Mon, 20 Dec 2010 01:26:17 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1036927-2870-1.aspxNice 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?Sun, 19 Dec 2010 06:37:13 GMTKoen Verbeeck