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

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 a开发者_开发百科ssesses 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?

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?

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!

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.

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

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 ;)