Eugene Elutin (11/29/2012)
Sorry for my a bit unorthodox view on " FizzBuzz" problem in T-SQL, but couldn't keep myself quiet :hehe:I don't think it's a good T-SQL question at all..
Create a million row FizzBuzz table. FizzBuzz isn't a comment. It's the name of a very common problem based on a child's game, is frequently given on interviews, and is actually a wonderful teaching tool to show people the difference between RBAR, Hidden RBAR, and Set Based methods that use "Pseudo Cursors". Have them do it with explicit loops, with a recursive CTE, without either of those at least 3 different ways, and then measure and discuss the resource usage and performance differences. So far as I'm concerned, it's one of the more important concepts in SQL Server
Agree on this, but I wouldn't ask specifically for FizzBuzz. Probably question like "Could you show few ways to produce a list of N [sequential] numbers in T-SQL" would do good enough for finding if the man can think in "SETs" and aware of T-SQL features such as CTE's, windowed functions and even IDENTITY function which can produce it together with creating a table while used in SELECT INTO.
If I want to check knowledge of CASE in T-SQL, I would prefer to ask the question where it's need to be used in ORDER BY...
I had to do a FizzBuzz on an interview two years ago! I was appalled to be told that a lot of people failed it. The trick that even the experienced guys miss is MOD(n, 15) rather than just MOD(n, 3) and MOD(n, 5) in the CASE expression.
Actually, in my experience, there are more people who miss data conversion on number to varchar required in the ELSE part of this CASE...
If the coder attempting to solve the "fizz-buzz" puzzle (a) knows how to generate a set of sequential integers in T-SQL without a loop construct, (b) understands that the "fizz-buzz-fizzbuzz" requirement is most easily and efficiently satisfied in T-SQL with a CASE expression, (c) recognizes that the "fizzbuzz" condition must come first in the CASE expression, and (d) codes the "fizzbuzz" condition to produce the correct result (either "WHEN n % 3 = 0 AND n % 5 = 0" or "WHEN n % 15 = 0"), he or she has passed with flying colors, in my opinion!
I cannot say that knowing MOD defines you as person who knows the basic Math functions in T-SQL. What makes MOD more basic than ABS or POWER for example?
I do agree that Fizz-Buzz question is a good one for other programming languages, but T_SQL...
Now, the final one: If you really want to make an impression (good or bad) on the interview, when asked such question, then ask interviewer if he want to see common solution using MOD or kind of "native" T-SQL which doesn't need one!
When I've done it myself once, I caused a bit of surprised. Technical interviewer (I think at least 10 years younger than me), in front of his manager, instead of simply asking me to show how, replied back: "It's impossible, how will you check the number can be divided by 3 or 5?" And when I said, that it's not really required in T-SQL, I've been asked to explain myself by the manager. In reality it can be done by few ways. Here I will post just one of them:
;WITH
S1 AS (SELECT 1 N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) A(N)
,(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) B(N))
,Set100
AS
( SELECT ROW_NUMBER() OVER (ORDER BY N) N, 'Fizz' F,'Buzz' B
FROM S1
)
SELECT COALESCE(S15.F + S15.B, S5.B, S3.F, CAST(S1.N AS VARCHAR))
FROM Set100 S1
LEFT JOIN Set100 S3 ON S3.N * 3 = S1.N
LEFT JOIN Set100 S5 ON S5.N * 5 = S1.N
LEFT JOIN Set100 S15 ON S15.N * 15 = S1.N
ORDER BY S1.N
No MOD, No CASE WHEN, just very basic T-SQL joins + COALESCE from ANSI beloved by J.C. 😉 ...
I'm no gonna do discuss if it's slower than CASE WHEN with MOD, that was done just to show, if you really can think in "SET's":hehe:
I would be impressed if an interviewee asked me if I wanted a fizzbuzz solution with or without the modulo operator. Eugene's solution does demonstrate a solid grasp of fundamental T-SQL concepts, but does require that the coder know that all numbers that are evenly divisible by 3 and 5 are also divisible by 15. Awareness or ignorance of that fact wouldn't tell me much about a coder's ability as a programmer.
I would ask an interviewee who presented that solution why he or she did not use the modulo operator, though. It's a bit like asking a candidate for a carpentry job why he used a brace and bit to drill a hole when a power drill was available. There could be good reasons for using a basic tool over a more advanced tool, and I'd expect a qualified candidate to be able to articulate those reasons.
The real problem with the fizzbuzz puzzle for evaluating a programmer's ability is that it is only a puzzle. Once someone has seen it solved, the puzzle presents no challenge other than recalling the solution. One need not even understand how the solution works as long as one can re-create the code.
A more useful tool for determining whether a coder is at least minimally competent with T-SQL would be a problem more like a real-world task. I might provide a candidate with a simple data model like this:
Employees table
EmployeeID int PK
LastName varchar(60)
FirstName varchar(60)
Computers table
ComputerID int PK
SerialNumber varchar(20)
ModelNumber varchar(20)
WarrantyEndDate date
EmployeeComputerAssignments table
EmployeeID int FK references Employees.EmployeeID
ComputerID int FK references Computers.ComputerID
and ask him or her to write out (yes, on paper) a query that will return the names of all employees with computers that go out of warranty in December 2012, including the employee's ID and name and the computer's serial number, model number, and warranty end date, listed in order of warranty end date and employee last name, first name. Writing the query on paper requires the candidate to demonstrate the conceptual knowledge of the SELECT-FROM-JOIN-WHERE-ORDER BY construct without fumbling through it by trial-and-error at the keyboard (smart people with weak coding skills can usually manage to cobble something like this together if they can code-execute-evaluate enough times). Come to think of it, this is the kind of exercise that the OP seemed to be looking for!
If the candidate provided an acceptable response, I might up the ante just a bit by asking for the same solution with an added column called RemoveFromServiceDate set to 5 days earlier than the WarrantyEndDate. That would demonstrate the candidate's awareness of basic date manipulation techniques.
If the candidate succeeds with that, I might add a further challenge by specifying that when the RemoveFromServiceDate would otherwise fall on a Saturday or Sunday, it should be changed to the preceding Friday. I would expect to see a CASE statement and some use of the DATEPART or DATENAME functions. However, at this stage, I might also be satisfied if the candidate does not actually solve the problem on the spot but at least makes some intelligent response, e.g, asking whether a calendar table would be available or explaining that she knows that there are T-SQL functions that provide the required functionality but that she would need to look them up on Books Online to remember their names and syntax.
Hmm, I'm actually off to participate in a panel interview of a candidate in a few minutes! Bwaa-ha-ha-ha!
Jason Wolfkill