The FizzBuzz Oracle Database Coding Challenge

26 07 2011

July 26, 2011

Through a web search I located a page titled “Coding Horror: Why Can’t Programmers.. Program?” A simple question was asked in an interview, and apparently 199 of 200 programmers struggled to build a solution for the problem in less than ten minutes. The problem must be that the 199 people who did not succeed did not have access to an Oracle Database. The same question was posed to SQL Server developers in the form of a quiz. Before looking at the articles, see if you are able to solve the following problem with the help of Oracle Database:

Write a program 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”.

How many different solutions are there for this problem? Think about the problem before scrolling down.

My solution:

SELECT
  NVL(DECODE(ROWNUM/3,TRUNC(ROWNUM/3),'Fizz',NULL)||DECODE(ROWNUM/5,TRUNC(ROWNUM/5),'Buzz',NULL),TO_CHAR(ROWNUM)) FIZZBUZZ
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

I think that I remember solving a similar problem using an IBM PC Jr. using BASICA years ago. How may ways can this problem be solved with the help of Oracle Database? Before you answer, you might be thinking to yourself why would someone ask such a simple question? Could there be an overly complex solution, something that the interviewer had never seen before, that was the intended response to the question?

Actions

Information

Date : July 26, 2011
Categories : SQL

25 responses

26 07 2011

Radoslav Golian (09:07:18) :

some trivial solutions:

declare 
  t boolean;
begin
  for i in 1..100 loop
    t := false;
    if mod(i, 3) = 0 then
      dbms_output.put('Fizz');
      t := true;
    end if;

    if mod(i, 5) = 0 then
      dbms_output.put('Buzz');    
      t:= true;
    end if;
    
    if not t then
      dbms_output.put(i);
    end if;

    dbms_output.new_line;    
  end loop;
end;

SELECT CASE
         WHEN MOD(LEVEL, 15) IN (3, 6, 9, 12) THEN
          'Fizz'
         WHEN MOD(LEVEL, 15) IN (5, 10) THEN
          'Buzz'
         WHEN MOD(LEVEL, 15) = 0 THEN
          'FizzBuzz'
         ELSE
          CAST (LEVEL AS VARCHAR2(3))
       END
  FROM dual
CONNECT BY LEVEL <= 100

26 07 2011

Charles Hooper (09:47:05) :

Nice start to the list of solutions.

I have another solution to the problem that is a bit different (a bit longer), and I suspect that there are several other solutions to this problem.

In defense of the programmers that were interviewed, I suspect that 198 of the 199 developers spent the 10 minutes trying to determine what programming environment and language should be used for the problem, and then attempting to determine if only the integers between 1 and 100 need to be considered, or if all real numbers need to be output. There was probably only a single programmer that was willing to build something without having the complete specifications. :-)

—

As a reminder for people attempting to post code solutions in the comments section, less than (<) and greater than (>) signs tend to cause problem because those are characters with special meaning in HTML. In the comments section:
Less than < — use: & lt; (without the space between the & and the l)
Greater than > — use: & gt; (without the space between the & and the g)
There are several ways to produce monospaced code sections that retain spacing. I typically use <pre> before the code section and </pre> after the code section.

26 07 2011

Mark W. Farnham (10:11:08) :

I like your little puzzles. Soothing after I lose to my wife at sudoku.
If you like to be explicit in following user specifications, then the column below “Fizzbuzz Explicit” more generally follows that path (realizing that the case statement grabs the first true when clause’s then result, so you really don’t need to be quite so tricky), or if you enjoy applying isofunctional axiomatic transforms you can use the least common multiple of 3 and 5 to find values that are commonly divided by 3 and 5 to provide a slightly cheaper solution (though less explicit). The first four columns are just there so beginners can see why it works.

I’ve always wondered how many of the 199 thought they had to write their own mod function. That probably does take more than 10 minutes to do decently in most languages without the library call.

Regards,

mwf

 select
    rownum,
    mod(rownum,15),mod(rownum,5),mod(rownum,3),
    case
       when mod(rownum,5) = 0 and mod(rownum,3) =0 then 'Fizzbuzz'
       when mod(rownum,5)  = 0 then 'Buzz'
       when mod(rownum,3)  = 0 then 'Fizz'
       else to_char(rownum)
    end "Fizzbuzz explicit",
    case
       when mod(rownum,15) = 0 then 'Fizzbuzz'
       when mod(rownum,5)  = 0 then 'Buzz'
       when mod(rownum,3)  = 0 then 'Fizz'
       else to_char(rownum)
    end "Fizzbuzz cheaper by lcm"
 from sys.obj$ where rownum < 101;

26 07 2011

Charles Hooper (11:06:36) :

Mark,

Nice demonstration.

—

I am starting to wonder if my initial solution to this problem is a little short-sighted. Could it be the case that the 1 programmer that correctly answered the question was the only one without the printed result in the 10 minute time period (disclaimer, I thought of this about a half hour after posting this article)? If we read the interview question again and then think about the following joke, is there possibly a reason why only 1 programmer could correctly answer the question – something that was apparently missed in all of the other articles?

Two programmers walk into two different bars, the mathematician walks between the bars.

26 07 2011

T. J. Kiernan (11:31:13) :

Here’s my PL/SQL example to make it more like a programmer did it.

declare l_output varchar2(8);
begin
  for i in 1..100 loop
    l_output := case
                  when mod(i, 15) = 0 then 'FizzBuzz'
                  when mod(i, 3) = 0 then 'Fizz'
                  when mod(i, 5) = 0 then 'Buzz'
                  else to_char(i) end;
    dbms_output.put_line(l_output);
  end loop;
end;
/

26 07 2011

Raj (11:39:17) :

select 
       coalesce(
                decode(mod(rownum, 3), 0, 'Fizz')
                ||decode(mod(rownum, 5), 0, 'Buzz'),
                cast(rownum as varchar2(10))
               ) col2 
from dual
connect by level <= 100

26 07 2011

Charles Hooper (13:02:16) :

T.J Kiernan and Raj, nice examples.

—

Any other possible solutions? I have two more solutions ready to post to the blog article, cases where efficiency is not the first, second, or even third goal. Now that I think of it, is it possible to create a solution that is simultaneously inefficient and at the same time efficient… I think that I just came up with a third additional solution?

26 07 2011

Andrew Rosati (13:43:00) :

select decode(mod(level,3),0,’fizz’,null) || decode(mod(level,5),0,’buzz’,null) from dual connect by level < 101;

26 07 2011

Charles Hooper (15:22:51) :

Andrew,

That is very close. You just need a small adjustment to satisfy the first part of the requirement:

… that prints the numbers from 1 to 100 …

Your solution completes the hard part, but you might have lost a little bit of your final solution when you posted to the blog article.

—

I now have 3 other solutions, and I will likely post those in the next 14 hours unless someone posts very similar solutions. The efficient solutions are (fairly) well covered now – how about some inefficient solutions, or efficiently inefficient solutions?

26 07 2011

Raj (15:50:23) :

create or replace function f_ret_fizzbuzz(p_number in number)
return sys.aq$_midarray pipelined
as
begin
  for i in 1..p_number
  loop
    pipe row (case when mod(i, 3) = 0 and mod(i, 5) = 0
                   then 'FizzBuzz'
                   when mod(i, 3) = 0
                   then 'Fizz'
                   when mod(i, 5) = 0
                   then 'Buzz'
                   else to_char(i)
              end
             );
  end loop;
  return;
end f_ret_fizzbuzz;
/

select *
from table(f_ret_fizzbuzz(100))
/

26 07 2011

Charles Hooper (17:15:52) :

Raj,

I like that solution. I think that might have been the first SQL related solution on this blog that used a pipelined function.

26 07 2011

Charles Hooper (18:13:57) :

This is not one of the three solutions that I am saving. I just thought that I would throw recursion into the mix:

CREATE OR REPLACE FUNCTION NEXT_NUMBER(START_NUMBER IN NUMBER, MAX_NUMBER IN NUMBER) RETURN VARCHAR2
IS
  FIZZBUZZ VARCHAR2(8);
  FIZZTEMP VARCHAR2(8);
BEGIN
  IF START_NUMBER < MAX_NUMBER THEN
    FIZZTEMP := NEXT_NUMBER(START_NUMBER+1, MAX_NUMBER);  
  ELSE
    FIZZTEMP := '100';
  END IF;
 
  FIZZBUZZ:=NULL;
  IF START_NUMBER/3 = TRUNC(START_NUMBER/3) THEN
    FIZZBUZZ:=FIZZBUZZ||'Fizz';
  END IF;
  IF START_NUMBER/5 = TRUNC(START_NUMBER/5) THEN
    FIZZBUZZ:=FIZZBUZZ||'Buzz';
  END IF;
  FIZZBUZZ:=COALESCE(FIZZBUZZ,TO_CHAR(START_NUMBER));
 
  DBMS_OUTPUT.PUT_LINE(FIZZBUZZ);
 
  RETURN FIZZBUZZ;
END NEXT_NUMBER;
/
 
CREATE OR REPLACE PROCEDURE START_ME(START_NUMBER IN NUMBER, MAX_NUMBER IN NUMBER)
IS
  FIZZBUZZ VARCHAR2(8);
BEGIN
  FIZZBUZZ := NEXT_NUMBER(START_NUMBER, MAX_NUMBER);  
END;
/
 
EXEC START_ME(1,100)

The output:

Buzz
Fizz
98
97
Fizz
Buzz
94
Fizz
92
91
FizzBuzz
89
88
Fizz
...
Buzz
4
Fizz
2
1

There are cleaner ways of doing the above – I was headed in a couple of different directions when I created the above solution, and I stopped refining the solution when the expected output was achieved.

27 07 2011

Radoslav Golian (02:46:19) :

Number of different solutions = inf. :)

CREATE OR REPLACE TYPE fizzbuzz_impl AS OBJECT
(
    fizzbuzz varchar2(4000),

    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT fizzbuzz_impl) RETURN NUMBER,
    MEMBER FUNCTION odciaggregateiterate(SELF  IN OUT fizzbuzz_impl,
                                         VALUE IN VARCHAR2) RETURN NUMBER,
    MEMBER FUNCTION odciaggregatemerge(SELF IN OUT fizzbuzz_impl,
                                       ctx2 IN fizzbuzz_impl) RETURN NUMBER,
    MEMBER FUNCTION odciaggregateterminate(SELF        IN OUT fizzbuzz_impl,
                                           returnvalue OUT VARCHAR2,
                                           flags       IN NUMBER) RETURN NUMBER

)
/

CREATE OR REPLACE TYPE BODY fizzbuzz_impl IS

    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT fizzbuzz_impl) RETURN NUMBER IS
    BEGIN
        ctx := fizzbuzz_impl(null);
        RETURN odciconst.success;
    END odciaggregateinitialize;

    MEMBER FUNCTION odciaggregateiterate(SELF  IN OUT fizzbuzz_impl,
                                         VALUE IN VARCHAR2) RETURN NUMBER IS
    BEGIN

        SELF.fizzbuzz := SELF.fizzbuzz || 
                         CASE WHEN MOD(CAST(VALUE AS NUMBER), 15) = 0 
                              THEN 'FizzBuzz' 
                              WHEN MOD(CAST(VALUE AS NUMBER), 5) = 0
                              THEN 'Buzz'
                              WHEN MOD(CAST(VALUE AS NUMBER), 3) = 0
                              THEN 'Fizz'
                              ELSE VALUE 
                          END;

        RETURN odciconst.success;
    END odciaggregateiterate;

    MEMBER FUNCTION odciaggregatemerge(SELF IN OUT fizzbuzz_impl,
                                       ctx2 IN fizzbuzz_impl) RETURN NUMBER IS
    BEGIN
        SELF.fizzbuzz := SELF.fizzbuzz || ctx2.fizzbuzz;

        RETURN odciconst.success;
    END odciaggregatemerge;

    MEMBER FUNCTION odciaggregateterminate(SELF        IN OUT fizzbuzz_impl,
                                           returnvalue OUT VARCHAR2,
                                           flags       IN NUMBER) RETURN NUMBER IS
    BEGIN
        returnvalue := SELF.fizzbuzz;
        RETURN odciconst.success;
    END odciaggregateterminate;

END;
/

CREATE OR REPLACE FUNCTION 
fizzbuzz(x IN VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING fizzbuzz_impl;
/

SELECT fizzbuzz(CAST(LEVEL AS VARCHAR2(10))) 
FROM dual
CONNECT BY LEVEL <= 100
GROUP BY LEVEL
;

SELECT fizzbuzz(CAST(LEVEL AS VARCHAR2(10))) OVER (PARTITION BY LEVEL)
FROM dual
CONNECT BY LEVEL <= 100
;

27 07 2011

Radoslav Golian (03:04:44) :

11gr2


with fizzbuzz(fb, num) as (
  select cast(1 as varchar2(3)), 1 from dual
  union all
  select case when mod(num+1, 15) = 0 then 'FizzBuzz'
              when mod(num+1, 5)  = 0 then 'Buzz'
              when mod(num+1, 3)  = 0 then 'Fizz'
              else cast(num+1 as varchar2(3))              
         end fb,
         num + 1 num         
  from fizzbuzz
  where num <= 100
)
select fb
from fizzbuzz

27 07 2011

Charles Hooper (05:28:57) :

Radoslav,

Three very nice examples! I will have to spend some time trying to determine how the first 2 of those examples work. It appears that the third example counts to 101, so maybe we should swap the 100 for 99 in that example.

27 07 2011

Radoslav Golian (06:31:01) :

You’re right, the 100 is not correct, it should be swapped for 99

27 07 2011

Chadders (04:56:51) :

How about just :

begin
  dbms_output.put_line('1
2
Fizz
4
Buzz
Fizz
7
8
Fizz
Buzz
11
Fizz
13
14
FizzBuzz
16
17
Fizz
19
Buzz
Fizz
22
23
Fizz
Buzz
26
Fizz
28
29
FizzBuzz
31
32
Fizz
34
Buzz
Fizz
37
38
Fizz
Buzz
41
Fizz
43
44
FizzBuzz
46
47
Fizz
49
Buzz
Fizz
52
53
Fizz
Buzz
56
Fizz
58
59
FizzBuzz
61
62
Fizz
64
Buzz
Fizz
67
68
Fizz
Buzz
71
Fizz
73
74
FizzBuzz
76
77
Fizz
79
Buzz
Fizz
82
83
Fizz
Buzz
86
Fizz
88
89
FizzBuzz
91
92
Fizz
94
Buzz
Fizz
97
98
Fizz
Buzz');
end;
/

;-)

27 07 2011

Charles Hooper (05:30:38) :

Chadders,

For some reason, I really like that solution. :-)

27 07 2011

Charles Hooper (05:38:45) :

I promised three more solutions to the problem. The first two follow.

The solution that just selects the answers you want, and sorts the result into sequential order technique:

SELECT
  FIZZBUZZ
FROM
(WITH N AS (
  SELECT
    LEVEL N
  FROM
    DUAL
  CONNECT BY
    LEVEL<=100)
SELECT
  N.N,
  TO_CHAR(N.N) FIZZBUZZ
FROM
  N
WHERE
  MOD(N,3)>0
  AND MOD(N,5)>0
UNION ALL
SELECT
  N.N,
  'Fizz'
FROM
  N
WHERE
  MOD(N,3)=0
  AND MOD(N,5)>0
UNION ALL
SELECT
  N.N,
  'Buzz'
FROM
  N
WHERE
  MOD(N,3)>0
  AND MOD(N,5)=0
UNION ALL
SELECT
  N.N,
  'FizzBuzz'
FROM
  N
WHERE
  MOD(N,3)=0
  AND MOD(N,5)=0)
ORDER BY
  N;

The “I shall confuse you with WITH blocks that reference each other and then left outer join the result to output what I want you to see”:

WITH V1 AS
(SELECT
  ROWNUM N,
  TO_CHAR(ROWNUM) T
FROM
  DUAL
CONNECT BY
  LEVEL<=100),
V2 AS
(SELECT
  ROWNUM*12 N,
  'FizzBuzz' T
FROM
  DUAL
CONNECT BY
  LEVEL<=6),
V3 AS
(SELECT
  *
FROM
  (SELECT
    ROWNUM*3 N,
    'Fizz' T
  FROM
    DUAL
  CONNECT BY
    LEVEL<=33) V3
WHERE
  V3.N NOT IN 
    (SELECT
      N
    FROM
      V2)),
V4 AS(
SELECT
  *
FROM
  (SELECT
    ROWNUM*5 N,
    'Buzz' T
  FROM
    DUAL
  CONNECT BY
    LEVEL<=20) V4
WHERE
  V4.N NOT IN 
    (SELECT
      N
    FROM
      V2))
SELECT
  COALESCE(V2.T,V3.T,V4.T,V1.T) FIZZBUZZ
FROM
  V1,
  V2,
  V3,
  V4
WHERE
  V1.N=V2.N(+)
  AND V1.N=V3.N(+)
  AND V1.N=V4.N(+)
ORDER BY
  V1.N;

27 07 2011

Asif Momen (05:40:38) :

Hi Charles,

You made me work on my vacation. ;)

Below are three versions from my side (although the CASE is already mentioned in one of the comments above).

– simple CASE

select level,
       case 
         when mod(level, 3) + mod(level, 5) = 0 then 'FizzBuzz'
         when mod(level, 3) = 0 then 'Fizz'
         when mod(level, 5) = 0 then 'Buzz'
         else to_char(level)
       end txt
  from dual
  connect by level <= 100;

–Cascaded DECODE

select level,
       decode(mod(level, 3) + mod(level, 5), 0, 'FizzBuzz', decode(mod(level, 3), 0, 'Fizz', decode(mod(level, 5), 0, 'Buzz', to_char(level)))) txt
  from dual
  connect by level <= 100;

– simple DECODE

select level,
       Nvl(decode(mod(level, 3), 0, 'Fizz') || decode(mod(level, 5), 0, 'Buzz'), to_char(level)) txt
  from dual
  connect by level <= 100;

27 07 2011

Charles Hooper (08:52:43) :

This blog article was made accessible 24 hours ago, so I thought that I would formally share my opinion of why 199 of 200 programmers interviewed simply could not produce the correct answer to the question in less than 10 minutes – I arrived at this conclusion roughly 30 minutes after this blog article was posted. Revisiting my joke shared in a reply to Mark:

Two programmers walk into two different bars, the mathematician walks between the bars.

What? Here is the original specification:

Write a program 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”.

Think about that specification – is there sufficient information to produce a “correct” solution? In one of my comments in this article I showed a solution that uses recursion to display the integers from 1 to 100 in reverse numerical order, with the “Fizz” and “Buzz” keywords placed as requested. Did this solution meet the requirements? Maybe? Or should a double quote (“) have appeared at each end of the keywords? Should the numbers have appeared in numerical order? Should I have restricted the numbers output to just the integers between 1 and 100 (inclusive)? We cannot safely assume that only the integer values should be considered – maybe the whole point of the exercise is to indentify those developers that will produce a solution without fully understanding the desired outcome (I did not see this perspective until it was 30 minutes too late).

A mathematician reading “Write a program that prints the numbers from 1 to 100″ might produce a very different solution than a typical programmer. While there are a finite number of integers (whole numbers) between 1 and 100, there are an infinite number of numbers between 1 and 2, between 2 and 3, between 3 and 4, etc. OK, but computers store numbers in a certain precision that eliminates the possibility of an infinite number of number positions to the right of the decimal, so it might be helpful to pick a datatype for the solution. For fun, let’s pick the 32 bit datatype BINARY_FLOAT (4 data bytes plus one length byte). The number 1/3, written in decimal form, has an infinite number of “3″ digits to the right of the decimal point. Let’s see how many digits a 32 bit BINARY_FLOAT supports when holding the value of 1/3:

SET SERVEROUTPUT ON
 
DECLARE
  NUM BINARY_FLOAT;
BEGIN
  NUM:=1/3;
  DBMS_OUTPUT.PUT_LINE(NUM);
END;
/
 
3.33333343E-001
 
PL/SQL procedure successfully completed.

It appears that roughly 9 digits to the right of the decimal are supported. Where did that “4″ digit come from? Let’s check the documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i16209
BINARY_FLOAT and BINARY_DOUBLE are approximate numeric datatypes. They store approximate representations of decimal values, rather than exact representations. For example, the value 0.1 cannot be exactly represented by either BINARY_DOUBLE or BINARY_FLOAT. They are frequently used for scientific computations. Their behavior is similar to the datatypes FLOAT and DOUBLE in Java and XMLSchema.

OK, so we must be a little careful when using that datatype (if it is good enough for science, no problem?). Let’s try an experiment – we will retrieve all of the numbers with 2 decimal places of accuracy between 1 and 100, and produce the requested output:

DECLARE
  NUM1 BINARY_FLOAT;
  NUM2 BINARY_FLOAT;
  I BINARY_FLOAT;
  FIZZBUZZ VARCHAR2(8);
BEGIN
  FOR NUM1 IN 1..99 LOOP
    FOR I IN 0..99 LOOP
      NUM2:=NUM1 + 0.01 * I;
   
      IF I = 0 THEN
        IF NUM2/15 = TRUNC(NUM2/15) THEN
          FIZZBUZZ:='FizzBuzz';
        ELSE
          IF NUM2/3 = TRUNC(NUM2/3) THEN
            FIZZBUZZ:='Fizz';
          ELSE
            IF NUM2/5 = TRUNC(NUM2/5) THEN
              FIZZBUZZ:='Buzz';
            ELSE
              FIZZBUZZ:=NULL;
            END IF;
          END IF;
        END IF;
      ELSE
        FIZZBUZZ:=NULL;
      END IF;
      DBMS_OUTPUT.PUT_LINE(COALESCE(FIZZBUZZ,TO_CHAR(NUM2,'990.0000000')));
    END LOOP;
  END LOOP;
 
  NUM2:=100;
  IF NUM2/15 = TRUNC(NUM2/15) THEN
    FIZZBUZZ:='FizzBuzz';
  ELSE
    IF NUM2/3 = TRUNC(NUM2/3) THEN
      FIZZBUZZ:='Fizz';
    ELSE
      IF NUM2/5 = TRUNC(NUM2/5) THEN
        FIZZBUZZ:='Buzz';
      ELSE
        FIZZBUZZ:=NULL;
      END IF;
    END IF;
  END IF;
  DBMS_OUTPUT.PUT_LINE(COALESCE(FIZZBUZZ,TO_CHAR(NUM2,'990.0000000')));
END;
/
 
99.9000015
99.9100037
99.9199982
99.9300003
99.9400024
99.9499969
99.9599991
99.9700012
99.9800034
99.9899979
Buzz

Nice, although we have a couple seemingly random accuracy problems beyond the first digit to the right of the decimal point (but its good enough for science, so says the documentation). You can possibly see why I used nested FOR loops – I wanted to make certain that the numbers that are supposed to be integers actually return as integer values.

Now, extending the previous example, we will retrieve all of the numbers with 7 decimal places of accuracy between 1 and 100, and produce the output that was requested at the start of this blog article:

DECLARE
  NUM1 BINARY_FLOAT;
  NUM2 BINARY_FLOAT;
  I BINARY_FLOAT;
  FIZZBUZZ VARCHAR2(8);
BEGIN
  FOR NUM1 IN 1..99 LOOP
    FOR I IN 0..9999999 LOOP
      NUM2:=NUM1 + 0.0000001 * I;
   
      IF I = 0 THEN
        IF NUM2/15 = TRUNC(NUM2/15) THEN
          FIZZBUZZ:='FizzBuzz';
        ELSE
          IF NUM2/3 = TRUNC(NUM2/3) THEN
            FIZZBUZZ:='Fizz';
          ELSE
            IF NUM2/5 = TRUNC(NUM2/5) THEN
              FIZZBUZZ:='Buzz';
            ELSE
              FIZZBUZZ:=NULL;
            END IF;
          END IF;
        END IF;
      ELSE
        FIZZBUZZ:=NULL;
      END IF;
      DBMS_OUTPUT.PUT_LINE(COALESCE(FIZZBUZZ,TO_CHAR(NUM2,'990.0000000')));
    END LOOP;
  END LOOP;
 
  NUM2:=100;
  IF NUM2/15 = TRUNC(NUM2/15) THEN
    FIZZBUZZ:='FizzBuzz';
  ELSE
    IF NUM2/3 = TRUNC(NUM2/3) THEN
      FIZZBUZZ:='Fizz';
    ELSE
      IF NUM2/5 = TRUNC(NUM2/5) THEN
        FIZZBUZZ:='Buzz';
      ELSE
        FIZZBUZZ:=NULL;
      END IF;
    END IF;
  END IF;
  DBMS_OUTPUT.PUT_LINE(COALESCE(FIZZBUZZ,TO_CHAR(NUM2,'990.0000000')));
END;
/

Did the output of the above program finish in 10 minutes or less? If Yes, then try the BINARY_DOUBLE datatype and head out to 15 places to the right of the decimal.

Repeating, I do not think that the problem was that 199 of 200 programmers could not solve the problem – I think that the problem was that 199 (or maybe just 150) of the programmers solved a different problem from what was actually requested. :-)

27 07 2011

Neil Johnson (16:12:35) :

Do I win a prize for least efficient method?

select	actual
,	case 	when mod(actual,15) = 0 then 'FizzBuzz'
	 	when mod(actual,3) = 0 then 'Fizz'
	 	when mod(actual,5) = 0 then 'Buzz'
	else	   nvl2(say_thousands,say_thousands||' ',null)
		|| nvl2(say_hundreds,say_hundreds||' ',null)
		|| filler
		|| say_tens
	end in_words
from (
	select	actual
	,	case 	when actual_length = 4 then
			thousand_desc||' thousand'
	end	say_thousands
	,	case 	when hundred_desc is not null then
				hundred_desc||' hundred'
		end	say_hundreds
	,	case	when actual_length >= 3 and last_2_digits > 0 then
			'and '
		end filler
	,	case 	when last_2_digits between 1 and 15 then
				actual_desc
			when last_2_digits between 16 and 19 then
				unit_desc||ten_desc
			when last_2_digits between 20 and 99 then
				ten_desc||' '||unit_desc
		end	say_tens
	from (
		with bakers as
			(select 1 num,'one' dsc from dual union all
			select 2,'two' from dual union all
			select 3,'three' from dual union all
			select 4,'four' from dual union all
			select 5,'five' from dual union all
			select 6,'six' from dual union all
			select 7,'seven' from dual union all
			select 8,'eight' from dual union all
			select 9,'nine' from dual union all
			select 10,'ten' from dual union all
			select 11,'eleven' from dual union all
			select 12,'twelve' from dual union all
			select 13,'thirteen' from dual union all
			select 14,'fourteen' from dual union all
			select 15,'fifteen' from dual)
		, tens as
			(select 1 num,'teen' dsc from dual union all
			select 2,'twenty' from dual union all
			select 3,'thirty' from dual union all
			select 4,'fourty' from dual union all
			select 5,'fifty' from dual union all
			select 6,'sixty' from dual union all
			select 7,'seventy' from dual union all
			select 8,'eighty' from dual union all
			select 9,'ninety' from dual)
		, number_stack as
			(select	to_char(rownum,'0999') string_number
			,	rownum actual
			from dual
			connect by level <= 9999)
		select	stack.actual
		,	stack.actual_string
		,	stack.last_2_digits
		,	bakers1.dsc actual_desc
		,	stack.actual_length
		,	bakers4.dsc thousand_desc
		,	bakers3.dsc hundred_desc
		,	stack.hundreds
		,	stack.tens
		,	tens.dsc ten_desc
		,	stack.units
		,	bakers2.dsc unit_desc
		from (
			select 	substr(string_number,length(string_number)-3,1) thousands
			,	substr(string_number,length(string_number)-2,1) hundreds
			,	substr(string_number,length(string_number)-1,1) tens
			,	substr(string_number,length(string_number),1) units
			,	actual
			,	ltrim(string_number,' 0') actual_string
			,	to_number(ltrim(substr(string_number,length(string_number)-1,2),' 0')) last_2_digits
			,	length(ltrim(string_number,' 0')) actual_length
			from	number_stack
		  ) stack
		, bakers bakers1
		, bakers bakers2
		, bakers bakers3
		, bakers bakers4
		, tens
		where 	bakers1.num (+) = stack.last_2_digits
		and	bakers2.num (+) = stack.units
		and	bakers3.num (+) = stack.hundreds
		and	bakers4.num (+) = stack.thousands
		and	tens.num (+) = stack.tens
		and 	stack.actual between 1 and 100 
	)
)
order by actual
/

    ACTUAL IN_WORDS
---------- -----------------
         1 one
         2 two
         3 Fizz
         4 four
         5 Buzz
         6 Fizz
         7 seven
         8 eight
         9 Fizz
        10 Buzz
        11 eleven
...
        96 Fizz
        97 ninety seven
        98 ninety eight
        99 Fizz
       100 Buzz

27 07 2011

Charles Hooper (20:10:07) :

Partially inspired by Neil’s solution (it is interesting, but I am still trying to understand how Neil’s solution works – yes, so far it holds the prize ;-) ):

WITH N AS
(SELECT
  1 N
FROM
  ALL_OBJECTS
WHERE
  ROWNUM<=10)
SELECT
  DECODE(
    TRUNC(ROUND(ABS(COS(1.04719733*ROWNUM)),1))+
      TRUNC(ROUND(ABS(COS(0.6283184*ROWNUM)),1))*10,
    11,'FizzBuzz',1,'Fizz',10,'Buzz',ROWNUM) FIZZBUZZ,
  TO_CHAR(TO_DATE(ROWNUM,'J'),'Jsp') FIZZBUZZ2
FROM
  N N1,
  N N2;
  
FIZZBUZZ FIZZBUZZ2
-------- ---------
1        One
2        Two
Fizz     Three
4        Four
Buzz     Five
Fizz     Six
7        Seven
8        Eight
Fizz     Nine
Buzz     Ten
11       Eleven
Fizz     Twelve
13       Thirteen
14       Fourteen
FizzBuzz Fifteen
...
Buzz     Ninety-Five
Fizz     Ninety-Six
97       Ninety-Seven
98       Ninety-Eight
Fizz     Ninety-Nine
Buzz     One Hundred

2 08 2011

utecistu (09:51:44) :

Simple select, NO case, decode, mod :

select greatest(to_char(level),
                replace(instr(level/3,'.'),0,'Fizz'),
                replace(instr(level/5,'.'),0,'Buzz'),
                replace(instr(level/15,'.'),0,'FizzBuzz')
               )
from dual connect by level<=100;

2 08 2011

Charles Hooper (10:46:52) :

utecistu,

I must admit that I did not see your approach as a possible solution until I tried your SQL statement – the problem description does set itself up rather nicely for alphanumeric sorting. It should be possible to slightly alter your SQL statement to derive another solution using COALESCE rather than GREATEST.

	Tablespaces and part… on Tables and Their Indexes Shoul…
	Dynamic sampling NOT… on Dynamic Sampling Changes
	Charles Hooper on Install Nagios on a Synology D…
	Markus on Install Nagios on a Synology D…
	Charles Hooper on Connecting to an Oracle Databa…
	akila on Connecting to an Oracle Databa…
	Dird on Book Review: Oracle Database 1…
	Charles Hooper on Book Review: Oracle Database 1…
	Dird on Book Review: Oracle Database 1…
	Charles Hooper on Book Review: Oracle Database 1…
	Dird on Book Review: Oracle Database 1…
	jkstill on Brain Teaser: 10046 Extended S…
	Scrabble Word Finder… on The New Order Oracle Coding Ch…
	Charles Hooper on SQL – DENSE_RANK, PERCENT_RANK…
	Irshad on SQL – DENSE_RANK, PERCENT_RANK…

Charles Hooper's Oracle Notes