The FizzBuzz Oracle Database Coding Challenge

26072011

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.

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?

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.

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.

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.

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?

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?

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.

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
)
/

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.

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;

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:

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:

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. 🙂

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.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: