The T-SQL Quiz
-
Aw... I know what to do with this... Bankers Rounding! Helps the 15's even out
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Try to run the code.
_____________
Code for TallyGenerator -
Point is:
The quiz as it's done allows ambiguity.
For value "15" there are 3 answers, each of them is right.
And Andrew is the only one who caught it.
Everyone else sucks.
P.S. You're cheating about CASE. You told about picking the last value, but you reversed the order because code picks the first one.
I don't like cheaters. Very much.
_____________
Code for TallyGenerator -
1. I'm giving up
If you still didn't get it you will never get it 
2.
"P.S. You're cheating about CASE. You told about picking the last value, but you reversed the order because code picks the first one. I don't like cheaters. Very much."
Sergiy, if you do not understand the code you should ask and stop calling people names.
OK. You see, I changed the order
The result is the same. Feel better?declare
@i int
set
@i = 15
print
case
sign(@i%5) + 2*sign(@i%3)
when
1 then 'Bizz' -- divisible by 3
when
2 then 'Buzz' -- divisible by 5
when
0 then 'BizzBuzzBizzBuzz' --divisible by both (It's Andrew's version, in correct version we should use 'BizzBuzz')
else
cast(@i as varchar(10))
end
Run it.
Do you understand NOW?
P.S. Try to behave
-
Your code does not implement the logic of query correctly.
If you cannot get it even after you've been pointed...
Sorry.
Write code that counts from 1 to 100 For each number evenly divisible by 3, substitute 'Bizz' ...
15 is evenly divisible by 3. It must be substituted by 'Bizz'
Right answer is 'Bizz'
... For each number evenly divisible by 5, substitute 'Buzz' ...
15 is evenly divisible by 5. It must be substituted by 'Buzz'
Right answer is 'Buzz'
For each number divisible by both substitute 'BizzBuzz'
15 is evenly divisible by both. It must be substituted by 'BizzBuzz'
Right answer is 'BizzBuzz'
3 different answers, and all are right.
Do you understand NOW?
------------
You query implements "divisible by 3 but not divisible by 5".
Where did you find it in the quiz?
You supposed to implement the algorithm of the original question, not to invent your own.
If cannot follow the logic of the task ...
_____________
Code for TallyGenerator -
Grant, I quoted you quiz and it made me reread you article.
You ruled out solution from Chris and did not realize that he just accurately implemented the logic you requested to implement.
You've got result not the same as you expected but you did not bother to think why.
Not good, man, not good.
_____________
Code for TallyGenerator -
"P.S. You're cheating about CASE. You told about picking the last value, but you reversed the order because code picks the first one.I don't like cheaters. Very much."
Sergiy,
You call me "cheater".
1. It's rude in any situation
2. I showed, that you simply didn't understand 10 lines of code and in this CASE the order is not relevant.
What part of "Apology Required " do you not understand?
-
Where your post with CASE from previous page?
That one you were referencing when saying
"3. You were not exactly right about CASE." ?
Cheater always remain cheater.
_____________
Code for TallyGenerator -
This is rather amazing. I'm shocked it's still going on.
The quiz (not my invention by the way, it was old when I found it over on the programming web site) is clear enough. Case 1 do one thing, Case 2 do another, Case 1 and 2 combined, do a third. We're building a simple program that manipulates integers. This should be enough information. If we were building some sort of heart regulator that was going to have to be accurate down to the 64th decimal place... Yeah, we would need a lot more specificity as to the requirements.
Again, repeat as necessary; it's just an exercise, it's just an exercise...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
And... they're exercising! It's OK! Let 'em go...
Articles are supposed to spark conversation and your's certainly has. Take the good with the bad and look at all the ideas and techniques coming out over such a simple problem. Sure, some folks get a bit personal, but there's a lot of information on this thread from the best way (or worst) to solve the problem to the best way (or worst) to read the problem. It's all useful information and you should be really proud to have sparked such an amount of conversation with such a simple problem. 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yeah, Grant, it's really amazing.
Such a simple quiz brought so many unexpected points.
Case 1 do one thing, Case 2 do another, Case 1 and 2 combined, do a third.
Problem is it's not exactly what was in original question.
Here I see concatenation - which was objected earlier in this topic.
But even here:
"Case 1 and 2 combined" match criteria for both "Case 1" and "Case 2".
So, any answer is right.
You missed it despite the hint from Chris, and Andrew perfectly spotted it.
Again, repeat as necessary; it's just an exercise, it's just an exercise...
There is no point of any exercise if you don't learn anything from it.
_____________
Code for TallyGenerator -
I believe, there is no point in exercizing, if you do not analyze the results
Let's start
Page 1:
Sergiy:
It's funny nobody, even author, mentioned an obvious solution:
SELECT
ISNULL(
NULLIF(
CASE WHEN Number % 3 = 0 THEN 'Bizz' ELSE '' END +
CASE WHEN Number % 5 = 0 THEN 'Buzz' ELSE '' END
, '')
, CAST(Number AS nvarchar(10) )
)
from master.dbo.spt_values
where type = 'P' AND Number > 0 AND Number < 100
order by Number
When you're doing databases you should not reference the same value twice in your query.
Interesting sentence
The statement above referenced the same value Number not twice, but five(5) times
We could get it down to 4, if we use
Number Between 1 AND 100
instead of
Number > 0 AND Number < 100
Even more, using Between should protect as from the Development 101 mistake (in original statement we have only 1-99 instead of 1-100).
To be continued..
-
This should be enough information. If we were building some sort of heart regulator that was going to have to be accurate down to the 64th decimal place... Yeah, we would need a lot more specificity as to the requirements.
As an aside, that quote brings a story to mind. I've been on more than one job interview (we probably all have) where the interviewer pulled some technical problem out of thin air for me to solve. In one case an interviewer asked me to design a database based on two paragraphs of overly-simplistic requirements.
Based on the info. he provided, I designed a ten-table 5NF database for him in about 5 minutes. He was surprisingly upset with the result, however, and demanded to know why I was using so many tables. He claimed I should have used no more than two tables total, normalization be damned!
All this comes to mind because when I told him that he didn't specify a level of normalization in his requirements, he said something similar: "That should be enough information." Of course his attitude spoke volumes about the type of company and calibre of people they were, so I graciously thanked him for his time and consideration, said good-bye, and went on to the next interview.
-
I'm right with you on that one... the interview process should definitely be a two way street.
And, you did it very professionally, too.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Boy, you better don't start.
You're not the first one who was trying...
No survivors so far.
You better spend some time learning some things about SQL.
Not to humiliate yourself talking that BETWEEN references value not the same way as >= and <=.
_____________
Code for TallyGenerator
Viewing 15 posts - 181 through 195 (of 309 total)
You must be logged in to reply to this topic. Login to reply