views:

637

answers:

5

We are looking to hire a SQL programmer and need a good screening question similar to the FizzBuzz question but for SQL.

While it is certainly possible to write a FizzBuzz solution using SQL, I think the effort is misplaced. The FizzBuzz question assesses coding fundamentals such as looping, conditionals, output, and basic math. With SQL, I think something related to queries, joins, projections, and the like would be more appropriate. But, just as with FizzBuzz, it should be simple enough that 'good' SQL programmers can write a solution on paper in a couple minutes.

What is a good 'FizzBuzz' question for a SQL programmer?

A: 

SQL Developer or SQL DBA ? for a developer something about cursors; the syntax is a pain and a good one would question why you need to use it. For a dba give them a cursor and ask them to fix it ;)

u07ch
You really don't want someone who knows cursor syntax off the bat. Developers should NOT be writing cursors very often.
HLGEM
I don't think cursors is the right approach. It should be something fundamental.
g .
Rather give them a simple cursor example and let them write a query that does the same without the cursor.
Guffa
@Guffa, good idea. Shows whether they understand iterative vs. set-based query logic.
John M Gant
The cursor-syntax is highly dependent on which db-vendor you use. The syntax for using cursors is very easy when you use Oracle. A lot of people say that you should not use cursors because cursors are slow(er). The question why cursors are slower is imho a very complicated question, not a FizzBuzz question.
tuinstoel
+2  A: 

I would probably do something that requires an inner join, a left join and a where clause with both an AND and an OR condition. Also specify what fields you want returned. You would be looking to see if they recognize that they need a left join fromthe problem description, that they use explicit join syntax and that they use () to make the meaning of the and/or clear. You would also be looking to see if they used select * even though you specified what fields you wanted.

HLGEM
+5  A: 

A "FizzBuzz" is supposed to be so simple that anyone who can program at all should be able to solve it, and a good programmer should be able to solve it almost without thinking, right?

So maybe something like this:

First, take two tables, Employees and Departments, with a foreign key from Employees that shows which department each employee works for. (Typical boring example, from almost any database textbook.) Then let them write a query that involves both tables, such as "give me the names of all employees who work for the Cleaning department".

Then do exactly the same thing, but not with employees that work for departments, but with mice that are eaten by cats, or something else that is not an exact copy of the employee-department or student-course examples in the database textbook.

If they can find who works at the Cleaning department, but have no idea how to find which mice were eaten by the cat Tom, don't hire!

Thomas Padron-McCarthy
This is the right idea -- I like the approach.
g .
+8  A: 

We typically use something like this as a bare minimum for SQL:

Given the tables:

Customers: CustomerID, CustomerName

Orders: OrderID, CustomerID, ProductName, UnitPrice, Quantity

Calculate the total value of orders for each customer showing CustomerName and TotalPrice.

In our view, this is a pretty simple question requiring a join on two tables, grouping, and an aggregate function. We're amazed at how many people we talk to that presumably write database code in their job can't remember join syntax (and we never care which syntax they use, MSSQL style or Oracle style or something else).

What I like about this question is it lends itself to follow up questions like

How would you find all customers that ordered more than $1000 total? How would you normalize these tables? How would you optimize the queries?

Sam
Whether a dev can "remember join syntax" or has to look it up on Google (even to do a simple join) is precisely NOT a FizzBuzz question. FizzBuzz questions are about whether you can -- in code, pseudocode, or whatever -- describe HOW you would do something. I won't downvote this because I think it's a good answer. You should revise the syntax part, IMO.
Yar
@yar, I agree **how** is more important than **syntax**, however, developers should be able to remember the very basic syntax of the languages they use. With respect to the OP's question specifically, he asked about a SQL question, so pseudocode doesn't apply, this is SQL. Any developer working with SQL should be able to remember simple join syntax.
Sam
Perhaps true... especially because the OP asked for a "sql programmer"...
Yar
if you have to look up join syntax on your advertised db, you fail!
dotjoe
I'd let a .NET developer slip on some SQL syntax or some Javascript syntax, but if you are hiring a "SQL Programmer" then I would expect them to know the syntax you learn on page 9 of any SQL book.
JoshBaltzell
@JoshBaltzell, every shop is different and will have different requirements based on the specific applications they're developing. We never do anything that's not database related, so SQL skills are required of everyone.
Sam
A: 

Stick with fizzbuzz, just change the number from 100 to 10000000 and say that the solution has to be reasonably efficient. TomT.

Tom Thomson
The thing is that fizzbuzz doesn't test basic SQL knowledge, since the fundamental ideas are joining tables and such, not writing data manipulation in the SELECT clause.
David Thornley