October 24, 2011 at 6:59 am
Captain Miserable (10/24/2011)
I agree - a simple case statement seems much more straight forward. Is there a reason why the subquery would be more effective?
No.
Either the question's author was not aware of the simpler methods (in which case this question was a learning experience for everyone), or he was deliberately trying to confuse people.
October 24, 2011 at 7:05 am
Hugo Kornelis (10/24/2011)Either the question's author was not aware of the simpler methods (in which case this question was a learning experience for everyone), or he was deliberately trying to confuse people.
Presumably the former, given the explanation: "To update the columns value which contain the duplicate data we have to use subquery and case to match the criteria"
October 24, 2011 at 7:32 am
Hugo Kornelis (10/24/2011)
Either the question's author was not aware of the simpler methods (in which case this question was a learning experience for everyone), or he was deliberately trying to confuse people.
Or he was just trying to test people's knowledge of what is possible. It is an interesting thing to know that SQL is capable of even though I will hopefully never have to use it in place of one of the more elegant solutions.
October 24, 2011 at 7:43 am
Great follow up discussion with alternative answers.
October 24, 2011 at 7:51 am
I actually thought this was a trick question today since it showed table1 having a column called "column1" not "col1". All the answers had col1 in them so I checked not possible. I agree with the others that the question author's solution choices are more complex than need be and that there are much simpler answers out there.
October 24, 2011 at 8:17 am
I got the answer correct but the question is very misleading. The name of the column is Column1 not col1 as it's referred to in the answers. So really the correct answer is None of the above!
October 24, 2011 at 8:35 am
I have been doing these questions for a little while now, and really enjoy them, but this is my first time posing about these questions, and I have to say, I agree with several of the above comments. Given as the question is "Which code segment will update column1 and set the column1 value to 1 where the column1 value is 0 and vice versa", surely the correct answer, given the information we have, is none of them? Every single code block would error due to invalid column names? I realise I may be taking this too literally, but hey, I'm a programmer, it's what I do 😀
October 24, 2011 at 8:38 am
lrosini (10/24/2011)
I got the answer correct but the question is very misleading. The name of the column is Column1 not col1 as it's referred to in the answers. So really the correct answer is None of the above!
If the answer option "None of the above" had been present, then I would agree that this is a misleading question.
But there was no such answer option. It was obvious that the name change of the column was not intentional.
October 24, 2011 at 8:41 am
Hugo Kornelis (10/24/2011)
PS: There's even one more way to achieve the intended change:
UPDATE TOP(1) Table1SET Column1 = 0
WHERE Column1 = 1;
Since row order in a relational table does not matter and there are no other columns in the table to identify rows, the end result of this query will be a table with 7 single-column rows, four of them with the value 0 and three with the value 1. Just like the result of all the other options presented in this topic.
I'm sorry. I didn't understand how this solution would work, so I tried it out, but it didn't work correctly for me.
I must be missing something?
Oh! I got it!! (Yes, sly!)
October 24, 2011 at 8:52 am
Thanks for the question. This one got me thinking and I like to alternate solutions that have been presented.
http://brittcluff.blogspot.com/
October 24, 2011 at 9:00 am
Hugo Kornelis (10/24/2011)
Captain Miserable (10/24/2011)
I agree - a simple case statement seems much more straight forward. Is there a reason why the subquery would be more effective?No.
Either the question's author was not aware of the simpler methods (in which case this question was a learning experience for everyone), or he was deliberately trying to confuse people.
The explanation claims that a subquery with a case statement is needed ("we have to use ...". That seems to indicate that the author though it had to be done like that, and didn't realise that it was an unnecessarily (and undesirably) complex approach, not trying to confuse people.
Tom
October 24, 2011 at 9:22 am
Sometimes questions here look more like riddles rather than teaching questions, still there thanks for the effort... 😀
October 24, 2011 at 9:29 am
dg1407 (10/24/2011)
Sometimes questions here look more like riddles rather than teaching questions, still there thanks for the effort... 😀
Oh, but they do teach us a lot. 🙂
Sometimes a QoTD followed by a great debate is better than reading a big book chapter on the subject.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
October 24, 2011 at 9:39 am
codebyo (10/24/2011)
dg1407 (10/24/2011)
Sometimes questions here look more like riddles rather than teaching questions, still there thanks for the effort... 😀Oh, but they do teach us a lot. 🙂
Sometimes a QoTD followed by a great debate is better than reading a big book chapter on the subject.
Best regards,
hahaha, ok i have to give you that one...:-P
October 24, 2011 at 9:59 am
>>Sometimes a QoTD followed by a great debate is better than reading a big book chapter on the subject.
Well said, codebyo. This question is a great example of such.
Viewing 15 posts - 31 through 45 (of 76 total)
You must be logged in to reply to this topic. Login to reply