X

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

Comments ( 3 )
  • Scott Brickey Wednesday, March 4, 2015

    how about a CTE?

    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

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.