Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The T-SQL Quiz

By Grant Fritchey,

TSQL Quiz

I was reading the latest Simple-Talk email that linked me to an article on Coding Horror, "Why Can't Programmers - Program?" In the article they talked about a simple test that they gave developers to verify their abilities and decide whether or not to continue the interview. Here's the test: Write code that counts from 1 to 100 For each number evenly divisible by 3, substitute 'Bizz' For each number evenly divisible by 5, substitute 'Buzz' For each number divisible by both substitute 'BizzBuzz'

I decided to try it out in TSQL. Once I had a solution (it took about five minutes, it would have been about two but I kept getting syntax errors on the CASE statement). I had so much fun that I sent it out to our team to see who else could meet the requirements and I added one more: no cursors are allowed

Before you read any further, try it out for yourself. It's not hard. A solution should take you less than 10 minutes.

Here are the various solutions that we came up with on our team.

First up, mine:

DECLARE @i VARCHAR(3) ;
DECLARE @s VARCHAR(8) ;
SET @i = 1 ;

WHILE @i < 101
    BEGIN
        SELECT  @s = CASE WHEN ( ( @i % 3 = 0 )
                                 AND ( @i % 5 = 0 )
                               ) THEN 'BizzBuzz'
                          WHEN ( @i % 3 = 0 ) THEN 'Bizz'
                          WHEN ( @i % 5 = 0 ) THEN 'Buzz'
                          ELSE @i
                     END ;

        PRINT @s ;
        SET @i = @i + 1 ;
    END ;
I didn't need to use the @s variable for the print statements, but overall, first pass, it was simple and worked fine. I'm assuming I'd still get interviewed although my pay scale may have dropped a grade because I introduced parameters that weren't really necessary.

The next submission came in from Chris:

declare @i int
set @i = 1

while( @i < 101)
 begin
	if @i%3 = 0 print 'Bizz'
	else if @i%5 = 0 print 'Buzz'
	else if @i%3 = 0 and @i%5 = 0 print 'BizzBuzz'
	else print @i
	
	set @i = @i+1
 end
He fixed my problem with the string, but he introduced a new one. Go back, reread the code and see if you can spot it. Give up? Because he checked for 3 then 5 then the combination, none of his code found the combination of 3 and 5. End of interview. Sorry Chris. It's also worth mentioning that the CASE statement resolves in a single pass where as the IF statements check each condition. Take a look at the estimated query plans:

Next came one from Det:

CREATE TABLE Nums ( num int primary key )
GO

DECLARE @i int
SET @i = 1

WHILE @i <= 100
    BEGIN
        INSERT  Nums ( num )
        VALUES  ( @i )
        SET @i = @i + 1
    END
 

SELECT  CASE WHEN num % 3 = 0
                  AND num % 5 = 0 THEN 'BizzBuzz'
             WHEN num % 3 = 0 THEN 'Bizz'
             WHEN num % 5 = 0 THEN 'Buzz'
             ELSE CAST(num AS nvarchar)
        END
FROM    nums
Det's worked very well, but he created a permanent table and didn't include a drop statement. When I went back to look at the query plan, I got an error. We'll continue the interview, but Det shouldn't count on getting a top spot on the team. He submitted a second query as well:
DECLARE @i int
SET @i = 1

WHILE @i <=100
BEGIN
SELECT CASE WHEN @i % 3 = 0  AND @i % 5 = 0 THEN 'BizzBuzz'
            WHEN @i %3 = 0 THEN 'Bizz'
            WHEN @i %5 = 0 THEN 'Buzz'
            ELSE CAST(@i AS nvarchar) END
SET @i = @i + 1
END
This worked in a repeatable fashion and instead of printing output lines to the message window, it returned actual results. 100 separate result sets, but even so, it worked. Points for perseverance.

Next came Scott Abrants:

DECLARE @int AS INT;
DECLARE @immdResult AS VARCHAR(100);

SET @int = 1;

WHILE @int < 101
BEGIN
    SET @immdResult = CASE WHEN @int % 3 = 0
                                AND @int % 5 <> 0 THEN 'Bizz'
                           WHEN @int % 5 = 0
                                AND @int % 3 <> 0 THEN 'Buzz'
                           WHEN @int % 3 = 0
                                AND @int % 5 = 0 THEN 'BizzBuzz'
                      END ;

    PRINT 'The number is ' + CONVERT(VARCHAR(10), @int);

    IF LEN(@immdResult) > 0 
        BEGIN
            PRINT @immdResult;
        END

    SET @int = @int + 1;
END;
Not at all surprisingly, Scott's is dead on accurate and extremely thorough. He reintroduced the extra parameter that I had, but instead of a crutch, he makes real use of it. At this point Scott's at the head of the class

Freely admitted, this is a silly little test and doesn't really demonstrate much in the way of coding skill, or more especially TSQL skills. However, it spawned a lot of discussion within the team. We started running the queries to see what performance looked like. Since most of them didn't do anything approaching data manipulation, there really weren't query plans to look at. Performance was very similar to a degree:
Chris - 15ms
Grant - 31ms
Scott - 46ms
Det 1 - 62ms
Det 2 - 93ms

One of the discussions that came up was, shouldn't there be a simple way to do this with a CTE? So after lunch, I set out to try it. This is what I came up with:

WITH Nbrs(n) AS (
	SELECT 1 
	UNION ALL
	SELECT 1 + n FROM Nbrs WHERE n < 100)
SELECT CASE WHEN n%5=0 AND n%3=0 THEN 'BizzBuzz'
	WHEN n%3 = 0 THEN 'Bizz'
	WHEN n%5 = 0 THEN 'Buzz'
	ELSE CAST(n AS VARCHAR(8))
	END
FROM Nbrs
OPTION (MAXRECURSION 100);
This worked well, looks neat, and it ran in 15ms. The only problem with it was, it created one scary looking query plan:

That's it. We had some fun writing these queries and I decided to share them. The results speak for themselves. There is no great and magnificent truth revealed here. It's simply a starting point for conversation and a method of verifying a basic level of knowledge along with the ability to apply logic. There are probably much more elegant ways to do this. I looked up some methods for generating counting data that could be used for something like this or for something a heck of a lot more sophisticated like a Numbers table and found a nice summary of information here. If you have a better way to skin this cat, please share it. If you're applying at my company, you might want to anticipate this as a question at the start of the interview.

Total article views: 17821 | Views in the last 30 days: 26
 
Related Articles
FORUM

In store procedure where i need to declare "Declare @SQL1 varchar(800)" to access within Begin statement

I have a complex store procedure and I need to take print of output from store procedure that's why ...

BLOG

A pattern to do beginning of period calculations.

Beginning of period calculations are common things. Rolling month reports, queries to pull everythi...

FORUM

Query

Query

FORUM

Must declare the scalar variable - help required

Must declare the scalar variable - help required

FORUM

SQLCMD Gives Error Must declare the scalar variable

SQLCMD Gives Error Must declare the scalar variable

Tags
sql puzzles    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones