 ## Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

• February 25, 2015

# FizzBuzz FizzBuzz is a classic programming problem / interview question. In this post we'll solve FizzBuzz in SQL using a row generator.

The FizzBuzz problem:

• Write a program that prints the integers from 1 to 100.
• For multiples of 3 print "Fizz" instead of the number,
• For multiples of 5 print "Buzz“ instead of the number,
• For numbers which are multiples of both 3 and 5, print "FizzBuzz".

If I have a table my_numbers with the numbers from 1 to 100 in it, then this is trivial to do in SQL:

```select case
when mod(the_number,15)=0 then 'FizzBuzz'
when mod(the_number,3)=0 then 'Fizz'
when mod(the_number,5)=0 then 'Buzz'
else to_char(the_number)
end as "FizzBuzz!"
from my_numbers
order by the_number;
```
```FizzBuzz!
----------------------------------------
1
2
Fizz
4
Buzz
Fizz
7
8
Fizz
Buzz
11
Fizz
13
14
FizzBuzz
...
97
98
Fizz
Buzz

100 rows selected.
```

But, how to get that table of the natural numbers from 1-100?

Well, I could create a table and then populate it using PL/SQL; or I could do this:

```--10 rows
select 1 as num from dual union all
select 2 as num from dual union all
select 3 as num from dual union all
select 4 as num from dual union all
select 5 as num from dual union all
select 6 as num from dual union all
select 7 as num from dual union all
select 8 as num from dual union all
select 9 as num from dual union all
...
select 100 as num from dual;
```

but that's not only tedious but completely unnecessary. There are several much better ways to create row sources in SQL.

The simplest, and most frequently used, row generator uses CONNECT BY LEVEL:

```select  level
from    dual
connect by level <= 100 ;
```

What's going on here?

• CONNECT BY is part of the hierarchical query clause
• LEVEL is a pseudocolumn available when using CONNECT BY.

And we can use this directly to solve FizzBuzz:

```select case
when mod(level,15)=0 then 'FizzBuzz'
when mod(level,3)=0 then 'Fizz'
when mod(level,5)=0 then 'Buzz'
else to_char(level)
end as "FizzBuzz!"
from dual
connect by level <= 100;
```

In 11.2 and above, Oracle supports Common Table Expressions, a.k.a. recursive subquery factoring. So we can rewrite the row generator using recursive subquery factoring, and then rewrite the fizzbuzz solution as:

```WITH natural (n) as
( select 1 as n from dual
union all
select n+1 from natural
where n<100
)
select case
when mod(n,15)=0 then 'FizzBuzz'
when mod(n,3)=0 then 'Fizz'
when mod(n,5)=0 then 'Buzz'
else to_char(n)
end as "FizzBuzz!"
from natural;
```

Unlike CONNECT BY, recursive subquery factoring is ANSI compliant. So the above gives us an ANSI-compliant solution to FizzBuzz.

Here's another, even simpler, way to get our 100 rows of numbers 1..100:

```select * from xmltable ('1 to 100');
```

and also...

```select rownum from
( select 1 from dual group by cube( 1, 1, 1, 1, 1, 1, 1 ) ) where rownum < 101;
```

Confused? Wonder what's going on here? Group by cube gives you every aggregation. In the case of "select 1 from dual", every aggregation is 1. Try this:

```--2 rows
select 1 from dual group by cube (1);
--4 rows
select 1 from dual group by cube (1,1);
--8 rows
select 1 from dual group by cube (1,1,1);
```

so the "group by cube" is creating 2^n rows for you, where "n" is the number of cols listed in the CUBE.

Now, we've looked at several ways to generate a table of N natural numbers from 1...N. Which is better / faster? In the next post we'll compare execution times, statistics and explain plans for the above row generators. Then, we'll look at a few more things you can do with row generators.

#### Join the discussion

• Scott Brickey Wednesday, March 4, 2015

Seems like a CTE's performance would be the best.

• Natalka Wednesday, March 4, 2015

Yes, you can see the CTE version about halfway thru the post. In Oracle, connect by level is slightly faster than CTE - see the next post in the series
for details:
https://blogs.oracle.com/sql/entry/row_generators_part_2

• guest Tuesday, December 20, 2016

Solution without case when
select rownum,replace(replace(replace(replace(','||rownum||','
,','||trunc(rownum/15)*15||',','FizzBuzz')
,','||trunc(rownum/3)*3||',','Fizz')
,','||trunc(rownum/5)*5||',','Buzz')
,',','')
from dual connect by level<=100