The T-SQL Quiz
-
Ok. We reach the agreement about Andrew's "concat' logic, yes?
The answer to your question, IMHO, really simple: sequentially. 3 times. It's the same as replace with the last one.
-
First off, everyone is taking this FAR too seriously.
That said, most of the functional solutions I've seen work fine for numbers divisible by 15. Inherently anything divisible by 15 is going to be both divisible by 3 and by 5. Numbers are fun that way. There is no requirement to explicitly state anything about 15 in the logic of the question (which, say it with me, is a simple test, not the be all and end all of coding knowledge).
I agree with Adam by the way, ELSE is a perfectly functional mechanism.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Poor logic!
Did you suggest CASE?
Case will pick 1st match, it will never come to the last one.
> The answer to your question, IMHO, really simple: sequentially
How is this?
Can you show how you "sequentual" answer looks like?
_____________
Code for TallyGenerator -
1. CASE:
print
case
sign(@i%5) + 2*sign(@i%3)
when 0 then 'BizzBuzzBizzBuzz'
when 1 then 'Bizz'
when 2 then 'Buzz'
when 3 then cast(@i as varchar(10))
end
2. sequential
declare
@replaced varchar(20)
set
@replaced = 15
set
@replaced = replace(@replaced, @replaced, 'Bizz')
set
@replaced = replace(@replaced, @replaced, 'Buzz')
set
@replaced = replace(@replaced, @replaced, 'BizzBuzz')
print
@replaced
-
Exactly my point! "Where do you see - "divisible by 3 but not divisible by 5"? Everyone just assumed that each number must fall into 1 and only 1 case, the question however does not imply this.
O yeah, about the concat, if you look at my solution I dont concat anything!
Bottom line the specs were ambiguous and if you looked at the question, before looking at some of the solutions, the article asked you to do so, then you may have strayed from the group, as I did.
-
I kind of hate these silly quibbles, especially over something trivial, but... The statement did in fact imply that only one case should be applied. It did not state that only one case should be applied but relied on the implication. Not that it really and truly matters a whit.
Everyone repeat to themselves: It was just a simple exercise. It was just a simple exercise. It was just a ....
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Actually, the directions do state quite implicitly that it can only fall into one category. It uses the word "substitute". When you substitute for something, the original thing is no longer present and therefore can NOT be substituted for again.
For example, consider this piece of code:
Declare @Number varchar(10)
Set @Number = 15
If @Number % 3 = 0 Then Replace(@Number, @Number, 'Bizz')
If @Number % 5 = 0 Then Replace(@Number, @Number, 'Buzz')
If @Number % 15 = 0 Then Replace(@Number, @Number, 'BizzBuzz')
See? Once you make the first subtitution, the number is no longer there to be substituted.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Good point, Im convinced, I would have failed the interview...darn!
This is just an excercise, This is just an excercise, This is just an exercise...
-
This thread should be printed out and preserved in acrylic. It was as much a Rorschach test as a SQL test. The responses perfectly illustrate many of the good traits of programmers (logic, knowledge of SQL, documentation, making queries more efficient, persistence) with several of the bad ones (the obsessive nitpicking that is the mixed blessing of the IT personality, condescending and unconstructive criticism of mistaken approaches, and taking things too seriously).
All in all, an instant classic.
Thanks again, Grant!
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
If nothing else, it certainly was a thought-provoking, compelling article. Very few of these things even come close to double digit pages of responses.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Agree.
I got even more from the discussion than from the original article.
It means that article is really good.
-
-
No.
'BizzBuzz'
-
Yes, it's quite obvious that only one case should be applied.
But which one?
Computer does not do any implications.
Unless you specify the rules for it.
Andrew demonstrated the very computer-like way of thinking.
And very non-trivial one.
Grant, it's interesting, would such guy pass your test?
Because I would stop test right there and start discuss his working hours, etc.
And I would not be interested if he can write a single row in SQL or C# without syntax errors.
_____________
Code for TallyGenerator -
Sergiy, I'm trying to understand your point.
1. The guy himself is already convinced:
"Im convinced, I would have failed the interview...darn!"
2. Two people provided identical snippets that should explain you sequential replacement logic.
3. You were not exactly right about CASE.
Now. You're still protecting the logic from original Andrew's post, despite that even he agreed that it's wrong.
Viewing 15 posts - 166 through 180 (of 309 total)
You must be logged in to reply to this topic. Login to reply