Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL -Write FIZZBUZZ without using loop

Tags:

sql

sql-server

Write a program using SQL that prints the numbers from 1 to 100.

But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”

DECLARE @counter INT
DECLARE @output VARCHAR(8)
SET @counter = 1
WHILE @counter < 101
BEGIN
SET @output = ''
IF @counter % 3 = 0
SET @output = 'Fizz'
IF @counter % 5 = 0
SET @output = @output + 'Buzz'
IF @output = ''
SET @output = @counter
PRINT @output
SET @counter = @counter + 1
END

This gives desired output. But,I am asked not to use loop,Is it possible to do this any other way? How about using CTE?

like image 304
BlackCat Avatar asked Aug 09 '16 03:08

BlackCat


People also ask

Is FizzBuzz done with a loop or a query?

Now normally FizzBuzz is done with a loop, but as Russ said, we are using T-SQL so batch code is always the goal. That said, what table should I query to get the numbers 1-100?

How do you use Fizz and buzz in Python?

Every time a number is divisible by 3, print fizz instead, when it’s divisible by 5, print buzz, and when it’s divisible by both, print fizzbuzz. The real challenge, however, is to do it in as few lines of code as possible and in our case…

What is the FizzBuzz challenge?

The challenge is two-fold. A FizzBuzz problem is a common programming interview challenge that asks a coder to print the numbers from 1 to 100. Every time a number is divisible by 3, print fizz instead, when it’s divisible by 5, print buzz, and when it’s divisible by both, print fizzbuzz.

Where can I find FizzBuzz?

You could throw it up on sqlfiddle, github or any number of other places and we could take a look at it for you. I’d enhance your inner SELECT (this one with the FOR XML) with the PRINT command and just EXECUTE (@FizzBuzz).


1 Answers

;With cte(n)--this is a recursive cte
as
(
select  1--anchor part
union all
select n+1
from cte where n<100 --recursive part
)
select 
case when n%3=0 and n%5=0 then 'Fizz Buzz'
     when n%5=0 then 'Buzz'
     when n%3=0 then 'Fiz'
     else cast(n as varchar(4)) end
from cte

From that table ,we are using case to calculate modulo.Read out below articles on why numbers tables are usefull and how they can replace loops..

1.http://dataeducation.com/you-require-a-numbers-table/
2.http://www.sqlservercentral.com/articles/T-SQL/62867/
3.https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

like image 86
TheGameiswar Avatar answered Oct 18 '22 04:10

TheGameiswar