OracleBlog

You pick up the candidate's resume and it proudly proclaims "SQL Expert: 10 Years." Your boss trusts you, as the technical expert on the team, to participate briefly in the interview to gauge this individual's knowledge of SQL. Where to begin?

I have asked literally hundreds of different questions during interviews over the past decade. Some were simple questions that were nevertheless remarkably effective ways of rating a candidate's comfort-level in SQL, whereas others just wasted precious interview time. Let me save you the latter!

First I ask the candidate how they would personally rate their own understanding of SQL. I'm also curious what databases and tools they've used to write their SQL. This gives me a good idea where I should begin my questioning.

Basic Questions

Asking a question about joins is the common consensus for an opening question. More specifically, does the candidate know the different types of joins, or at least the difference between an inner join and an outer join?

Assuming that goes well, I follow up with a question that involves using GROUP BY and/or HAVING. For example, given an EMPLOYEE table and a DEPARTMENT table, how would you select the combined salary of each department that has a higher combined salary than mine?

Intermediate Questions

With the sanity checks out of the way, we can get into some more meaningful technical discussions. Among my favourites is to solicit their perspective of what NULL is and what it means. After all, to write correct SQL queries, isn't this understanding important? How many queries have you read that caused problems because the author mishandled NULLs? Will this candidate make the same mistake? Do they know that NULL and nothing are different, and in what ways? Do they know that neither NULL = NULL nor NULL <> NULL are true?

Before proceeding to the grand finale, I pepper the candidate with variations of the following three questions that, though simple, seem to have done an exemplary job narrowing down someone's level of experience. First, do they know the four isolation levels? Secondly, do they know what a bind variable is and why they should be used (do they even know what a soft parse is)? Finally, what is the DUAL table?

Advanced Questions

All of this is leading up to analytic functions, one of my true litmus tests to the proficiency of one's knowledge of SQL. There are literally dozens of questions I have asked: YOU need to pick one that specifically relates to the type of work relevant to the open position. Here are some possibilities to choose from:
- Open with the basic concept of selecting data over a partition (a running total, perhaps)
- Follow-up by writing a Top-N query
- Put together a standard pivot/crosstab query
- Try handling hierarchical/recursive queries (using CONNECT BY)

I feel there is no real need to know the exact analytic function and write out the precise syntax, but to truly qualify your SQL at the higher level requires the ability to know how to truly unleash the power of an Oracle database. So tell me, did they head in the right direction, or ask for a glass of water and slip out?

Wrapping Up

Generally I have managed to keep the discussion under the previously unrealistic 15-minute limit that's imposed upon me. On rare occasions I'm treated to the delightful combination of a candidate that has breezed through the interview and a boss taking his time re-joining us. In that case, I've been known to serve up some advanced history questions. Who is Ted Codd? Did they know about his 12 rules? Do they get a blank expression on their face when I ask them what "System R" is?

At the very least, even if they know SQL backwards and forwards, they'll still have something interesting to look up when they get home.