Code Kata for SQL – FizzBuzz

by data-centric on August 2, 2012

There has been some discussion recently over on the Google Groups discussion forum for tSQLt about practicing code kata in SQL.  One suggestion was to try the time-honored FizzBuzz game and I present here a slightly modified version adapted to work with a non-object oriented, set-based language like SQL.

What is a Code Kata?

In martial arts, where the term originates, a kata is a set of movements practiced many times over, either solo or in pairs in an attempt to improve execution a little each time. The term “code kata” was first coined by Dave Thomas – author of The Pragmatic Programmer.

The idea is that developers working alone, or more often pair programming, will spend no more than 30-60 minutes trying to solve a simple logic problem using strict TDD principles. They may work on one problem every day for a week or two, pairing with different colleagues and trying to improve on the solution each time. Code kata encourage programmers to work in small baby steps, writing unit tests at each stage. Test-first development forces developers to design and write code differently and kata are a way of practicing that approach.

Why is SQL Different?

The use of code kata is an established practice in agile application development, but as TDD is still relatively new for SQL, the notion may be less familiar to database developers. As SQL developers, we also have to deal with the fact that the set-based, declarative programming paradigm we use in the database world does not always lend itself to solving some of the katas more commonly attempted by object-oriented programmers. So with this in mind I thought I would produce a modified version of a simple, common kata making it more suitable for SQL.

About this Kata

Having played with this kata a few times, I have modified it slightly to suit how SQL Developers think.  For example, I suggest returning a result set rather than printing the results, although you could write and unit test either (I have).  Working with sets is what we do and as you work though this kata you will hopefully encounter some of the same challenges in writing good tests as you would encounter in testing sets  in the real world.

FizzBuzz is a popular children’s game, often played in schools where the teacher works his way round the class to each pupil in turn.  Starting at the number 1, each student calls out the next number in sequence except that if the number is divisible by 3 the student calls “fizz”, if divisible by 5 then “buzz” and if divisible by 3 and 5 the student shouts “FizzBuzz”.

One piece of advice I would give you for any kata is do not rush ahead, complete each section before moving on to the next.  The purpose of this exercise is to learn to code in small baby steps, building up a suite of passing tests that should continue to pass as the complexity of the solution increases.  This is not about writing an optimal solution for production use right at the start, it is about learning how to do test-driven development properly.

The FizzBuzz Kata

This is based on the version I found at

Create something in SQL that returns a result set containing the numbers from 1 to 100. But for multiples of three the value should be “Fizz” instead of the number and for the multiples of five, “Buzz”.  For numbers which are multiples of both three and five the value should be “FizzBuzz”.  Your results should look like this:


Lets divide this into different steps so, we can easily write and test this.

  • Return a result set that contains numbers from 1 to 100
  • Replace any number which is divisible by 3 with “Fizz”
  • Replace any number which is divisible by 5 with “Buzz”
  • Replace any number which is divisible by both 3 and 5 with “FizzBuzz”

Stage 2 – Refactoring

If you haven’t already done so, refactor the logic that defines the string value into a separate function that accepts a single number as an input.
Create a test to determine that the correct value is returned for a given input (thinking about minimum possible use cases) and what the behaviour is if the range is outside the range 1 – 100.
Refactor the production code to remove any loops or cursors (any loops in your unit tests are OK).

Stage 3 – Changing Requirements

  • A number is Fizz if it is divisible by 3 or if it has a 3 in it
  • A number is Buzz if it is divisible by 5 or if it has a 5 in it

(Previous rules take precendence over these new requirements)

Stage 4 – New Features

Create something in SQL that will return a list of only numbers in any given range that are Fizz, Buzz or FizzBuzz
Refactor as necessary to ensure that no looping will occur during actual execution regardless of result set size

Good Luck!

I hope you enjoy playing with this SQL-friendly version of the Fizz Buzz kata.  Do please add a comment on your experiences, what you learnt and how you went about designing your solution. You could also perhaps add a a link to your code.  At some point I will do a follow-up post with one of my solutions.

Zac Harlan October 3, 2012 at 12:59 pm

Just knocked this out in about 10 minutes. I’ll spend some more time later today to remove the looping

DECLARE @StartDigit AS INT = 1
DECLARE @EndDigit AS INT = 1000;
WITH  allNumbers(digit)
        AS (
            SELECT  @StartDigit AS digit
            UNION ALL
            SELECT  digit + 1
            FROM    allNumbers
            WHERE   digit < @EndDigit
  SELECT  CASE WHEN digit % 3 = 0
                    AND digit % 5 = 0 THEN 'FizzBuzz'
               WHEN digit % 3 = 0 THEN 'Fizz'
               WHEN digit % 5 = 0 THEN 'Buzz'
               ELSE CAST(digit AS VARCHAR(4))
          END AS Result
  FROM    allNumbers WITH (NOLOCK)

Greg M Lucas October 3, 2012 at 1:13 pm

Hi Zac, I particularly like your use of a recursive CTE to get a starting point for the record set. I’ve re-posted your comment on the main post. I would also be interested in hearing about how you went about testing this?

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: