I was asked this question a few months ago and liked it due to its simplicity, and how it can be done on paper which is how we’ve been administering our interview tests (this is currently not on my test).

Imagine you have a packaging system and there is a table that defines what packages a user can access. There are two columns uid (user id) and pid (package id). Every row in this table provides a specific user with access to a package, for example there may be rows [1,1] or [3,5] where as user 1 has access to package 1 or user 3 has access to package 5. A user can have access to multiple packages which would result in rows like [1,1], [1,2], [1,3], etc… However, a user will be considered a “Super User” if they have a pid of -1 assigned to their user id. Often times a user is given access to multiple packages only to later be given “Super User” access. Write a query for this table to display all user access however if a user is a “Super User”, only display the one row for them demonstrating that they are a “Super User”.

Now, more often then not people will come up with some hackish query involving “UNION” or “IN”. When they do, tell them both are horrible for performance if not for any reason other then there are multiple executions within one query. Ask them to try and rewrite it using joins and not “UNION” or “IN”. This brain teaser is simple and should demonstrate their confidence with joins and ability to think beyond “Step 1, Step2″ answers. There is no one right answer, I’ve included mine below:

This rap is straight dope yo’

Yea thats right, we finally get a good excuse for taking on less work. For the details, read on. By the way this is ripped off verbatim from Slashdot.

“Multitasking messes with the brain in several ways. At the most basic level, the mental balancing acts that it requires — the constant switching and pivoting — energize regions of the brain that specialize in visual processing and physical coordination and simultaneously appear to shortchange some of the higher areas related to memory and learning. We concentrate on the act of concentration at the expense of whatever it is that we’re supposed to be concentrating on… studies find that multitasking boosts the level of stress-related hormones such as cortisol and adrenaline and wears down our systems through biochemical friction, prematurely aging us. In the short term, the confusion, fatigue, and chaos merely hamper our ability to focus and analyze, but in the long term, they may cause it to atrophy.”