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?
some trivial solutions:
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.
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
Mark,
Nice demonstration.
—
I am starting to wonder if my initial solution to this problem is a little shortsighted. 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?
Here’s my PL/SQL example to make it more like a programmer did it.
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?
select decode(mod(level,3),0,’fizz’,null)  decode(mod(level,5),0,’buzz’,null) from dual connect by level < 101;
Andrew,
That is very close. You just need a small adjustment to satisfy the first part of the requirement:
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?
Raj,
I like that solution. I think that might have been the first SQL related solution on this blog that used a pipelined function.
This is not one of the three solutions that I am saving. I just thought that I would throw recursion into the mix:
The output:
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.
Number of different solutions = inf. :)
11gr2
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.
You’re right, the 100 is not correct, it should be swapped for 99
How about just :
;)
Chadders,
For some reason, I really like that solution. :)
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:
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”:
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
–Cascaded DECODE
— simple DECODE
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:
What? Here is the original specification:
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:
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:
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. :)
Do I win a prize for least efficient method?
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 ;) ):
Simple select, NO case, decode, mod :
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.