May 24, 2012 at 4:04 pm
derek.colley (5/24/2012)
Oh god, not another bunfight. My last post started a row between two MVPs.
Nah... these things are fun and frequently educational for everyone because MVPs tend to pull out all the stops. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 24, 2012 at 4:16 pm
Lynn Pettis (5/24/2012)
Cadavre (5/24/2012)
Fizzbuzz is your evil interview? Really? It's as old as dirt (as are the solutions) 😛
WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
FROM t3 x, t3 y)
SELECT t.number,
CASE WHEN t.number%3 = 0 AND t.number%5 = 0
THEN 'FizzBuzz'
WHEN t.number%3 = 0
THEN 'Fizz'
WHEN t.number%5 = 0
THEN 'Buzz'
ELSE CONVERT(VARCHAR(3),t.number)
END AS result
FROM tally t
WHERE t.number <= 100 AND t.number >= 1;
--edit--
Oh, and as for banning - I say "no". You don't have to answer them, you can ignore anyone you wish. If people start getting banned for asking "basic questions", where does the line get drawn? Who draws the lines?
Here is my FizzBuzz solution:
WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select
isnull(nullif(case when N % 3 = 0 then 'Fizz' else '' end + case when N % 5 = 0 then 'Buzz' else '' end, ''),cast(N as varchar))
from
cteTally
where
N <= 100
Brevity and obscurity
declare @ char(3)
set @=0
while @<100
begin
set @=@+1
case when @%15=0 then 'FizzBuzz'
when @%3=0 then 'Fizz'
when @%5=0 then 'Buzz'
else @ end
end
May 24, 2012 at 8:07 pm
Koen Verbeeck (5/24/2012)
Sean Lange (5/24/2012)
Koen Verbeeck (5/24/2012)
Sean Lange (5/24/2012)
Always keep in mind that the questions you find super easy today are a great chance for some less experienced folks to start answering. This helps to give them some confidence. We need this type of thing to maintain this place over the long haul.
I say "Up the Basic Questions"!!!!
In my opinion, there's a big difference between basic questions of people who are trying to learn, or interview questions of someone too lazy to do research and who's trying to cheat through the interview.
This is true. It is also incredibly obvious when these types of questions arise. It is up to the individual to decide not to answer, or dog pile the OP as the thread in question does. 😛
And any interviewer who has conducted more than 1 interview will be able to through a smokescreen, especially one as obvious this person's. Sheesh a simple select statement might prove challenging for that person. That still is no reason to ban them. It just goes against the nature of this site entirely.
Just to be clear, I don't want to ban him.
I do however reserve the right to be annoyed by interview questions 😀
Not only can you reserve that right but you have to make room next to me with the annoyance. Not only the right to be annoyed but the right to vent that annoyance loudly!!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 25, 2012 at 1:54 am
Michael Valentine Jones (5/24/2012)
Brevity and obscurity
declare @ char(3)
set @=0
while @<100
begin
set @=@+1
case when @%15=0 then 'FizzBuzz'
when @%3=0 then 'Fizz'
when @%5=0 then 'Buzz'
else @ end
end
If I was asked this in an interview, I'd produce the solution I gave first then this one second. I always try and produce two or more solutions to every interview problem I'm given.
May 25, 2012 at 2:31 am
Koen Verbeeck (5/24/2012)
What!? You want to ban the biggest comedian of this entire forum?😀 😎 :hehe:
true
MVDBA
June 4, 2012 at 8:06 am
Cadavre (5/25/2012)
Michael Valentine Jones (5/24/2012)
Brevity and obscurity
declare @ char(3)
set @=0
while @<100
begin
set @=@+1
case when @%15=0 then 'FizzBuzz'
when @%3=0 then 'Fizz'
when @%5=0 then 'Buzz'
else @ end
end
If I was asked this in an interview, I'd produce the solution I gave first then this one second. I always try and produce two or more solutions to every interview problem I'm given.
If you gave me this answer, I'd put you lower on the list, because that's a loop and solving this with a loop is RBAR.
That said, of the 10 or so DBA wannabe's who I've had the pleasure to interview, only one ever came up with a solution for FizzBuzz at all in the hour we gave them.
oh btw, when we do a SQL test, we give you a PC with a copy of SQL 2008 R2 installed and a test database with a tally table populated up to 11,000 in it.
June 4, 2012 at 8:55 am
mtassin (6/4/2012)
If you gave me this answer, I'd put you lower on the list, because that's a loop and solving this with a loop is RBAR.That said, of the 10 or so DBA wannabe's who I've had the pleasure to interview, only one ever came up with a solution for FizzBuzz at all in the hour we gave them.
oh btw, when we do a SQL test, we give you a PC with a copy of SQL 2008 R2 installed and a test database with a tally table populated up to 11,000 in it.
I think you've just let me know that I don't want to work for you 😉
As I said, I like to produce multiple solutions to any problem then narrow down the best one for our particular set-up. RBAR does win in some situations, as much as we SQL developers don't like to admit it, so when I produce a solution I always include at least one RBAR alternative. Normally, this is just used in the stats that I produce to show that it is most definitely not a good idea. But sometimes, it wins.
For FizzBuzz, I'd produce the following two solutions: -
Cadavre (5/24/2012)
Fizzbuzz is your evil interview? Really? It's as old as dirt (as are the solutions) 😛
WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
FROM t3 x, t3 y)
SELECT t.number,
CASE WHEN t.number%3 = 0 AND t.number%5 = 0
THEN 'FizzBuzz'
WHEN t.number%3 = 0
THEN 'Fizz'
WHEN t.number%5 = 0
THEN 'Buzz'
ELSE CONVERT(VARCHAR(3),t.number)
END AS result
FROM tally t
WHERE t.number <= 100 AND t.number >= 1;
DECLARE @number INT = 1;
DECLARE @fizzBuzz TABLE (number INT, result CHAR(8));
WHILE @number <= 100
BEGIN
INSERT INTO @fizzBuzz (number, result)
SELECT @number, CASE WHEN @number%3 = 0 AND @number%5 = 0 THEN 'FizzBuzz'
WHEN @number%3 = 0 THEN 'Fizz'
WHEN @number%5 = 0 THEN 'Buzz'
ELSE CONVERT(VARCHAR(3),@number) END;
SET @number = @number + 1;
END;
SELECT number, result
FROM @fizzBuzz;
I'd like to think that I'd have demonstrated to a potential interviewer that I don't just assume that there is one solution to a problem. If I failed to get a job because I've produced more than one working solution, I'm pretty sure I didn't want the job anyway.
June 5, 2012 at 6:03 am
Michael Valentine Jones (5/24/2012)
Brevity and obscurity
declare @ char(3)
set @=0
while @<100
begin
set @=@+1
case when @%15=0 then 'FizzBuzz'
when @%3=0 then 'Fizz'
when @%5=0 then 'Buzz'
else @ end
end
I didn't realize that you didn't need any characters after the @ for a SQL variable. Thanks for that little tidbit (even though I probably would only use it to test a small script). 🙂
June 5, 2012 at 6:37 am
I've tried the ol' FizzBuzz test on some folks. A lot of them can't even begin it because they have never worked with things like Modulo (%) before. If you want someone with those extra bit of math skills (even though they seem simple to many), it's an ok litmus strip. If not, then I'd pick a different test. Remember that the purpose of interviews isn't to see how many people you can reject... the purpose is to find someone who has enough skills to do at least an adequate job and who has the ability to learn over time.
As a side bar, anyone who writes code as well formed as Cadavre did, even though it was a loop, has already caught my attention. I can train them how to avoid the loop. It's much more difficult to train someone to take pride in their code and to show sensible formatting even when they're under the gun.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2012 at 6:39 am
LightVader (6/5/2012)
Michael Valentine Jones (5/24/2012)
Brevity and obscurity
declare @ char(3)
set @=0
while @<100
begin
set @=@+1
case when @%15=0 then 'FizzBuzz'
when @%3=0 then 'Fizz'
when @%5=0 then 'Buzz'
else @ end
end
I didn't realize that you didn't need any characters after the @ for a SQL variable. Thanks for that little tidbit (even though I probably would only use it to test a small script). 🙂
Doing this has been depreciated by Microsoft.
Edit: Bit by the quote bug.
June 5, 2012 at 6:59 am
Lynn Pettis (6/5/2012)
LightVader (6/5/2012)
Michael Valentine Jones (5/24/2012)
Brevity and obscurity
declare @ char(3)
set @=0
while @<100
begin
set @=@+1
case when @%15=0 then 'FizzBuzz'
when @%3=0 then 'Fizz'
when @%5=0 then 'Buzz'
else @ end
end
I didn't realize that you didn't need any characters after the @ for a SQL variable. Thanks for that little tidbit (even though I probably would only use it to test a small script). 🙂
Doing this has been depreciated by Microsoft.
Edit: Bit by the quote bug.
Thanks Lynn. I'll keep that in mind. In general, I like to name my variables, so it wasn't something that I thought I would use often, if at all. I had just assumed that you needed to have at least one character after the @ and at some point, that assumption had been wrong.
June 7, 2012 at 7:04 am
I'm sure this will come off sounding stupid, but I have never heard of Fizzbuzz. I see the solutions, but I don't understand what you're solving.
Could someone elaborate for poor little uneducated me?
Danke.
June 7, 2012 at 7:08 am
Brandie Tarvin (6/7/2012)
I'm sure this will come off sounding stupid, but I have never heard of Fizzbuzz. I see the solutions, but I don't understand what you're solving.Could someone elaborate for poor little uneducated me?
Danke.
FizzBuzz was invented by IBM I believe to test your ability to develop.
It's a made up problem where you have to return all the numbers from 1 to 100.
Numbers divisible by 3, you return Fizz instead of the number
Numbers divisible by 5, you return buzz instead of the number
Numbers divisible by both, you return FizzBuzz instead of the number.
It's more of a test of your ability to think than anything.
June 7, 2012 at 7:14 am
Brandie Tarvin (6/7/2012)
I'm sure this will come off sounding stupid, but I have never heard of Fizzbuzz. I see the solutions, but I don't understand what you're solving.Could someone elaborate for poor little uneducated me?
Danke.
Pfieuw. I thought I was the only one 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 7, 2012 at 7:16 am
mtassin (6/7/2012)
Brandie Tarvin (6/7/2012)
I'm sure this will come off sounding stupid, but I have never heard of Fizzbuzz. I see the solutions, but I don't understand what you're solving.Could someone elaborate for poor little uneducated me?
Danke.
FizzBuzz was invented by IBM I believe to test your ability to develop.
It's a made up problem where you have to return all the numbers from 1 to 100.
Numbers divisible by 3, you return Fizz instead of the number
Numbers divisible by 5, you return buzz instead of the number
Numbers divisible by both, you return FizzBuzz instead of the number.
It's more of a test of your ability to think than anything.
Thank you. That helps me understand why the posted code solutions are doing what they are doing.
"And knowing is half the battle!"
Viewing 15 posts - 16 through 30 (of 63 total)
You must be logged in to reply to this topic. Login to reply