SQL Interview Questions

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 favorites 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.

Comments

Unknown said…
Hallo,


I love all the posts, I really enjoyed.
I would like more information about this, because it is very nice., Thanks for sharing.


We have Transaction replication has
configured from 2008 R3 to 2014
(pull subscriptions) . Technically working fine. Now I need to configure other replication from same prod DB on 2008R3 to 2016 SP1 CU7. Previous replication (2018R3 to 2014) is keep remain as it is.I understand technically this is possible but Microsoft support wise de supported. That is fine .
What is best way to configure
1. 2018R3 to 2016 R1 ( two publishers create on same server(2008R3 ) replicate to two different versions(2014 and 2016R1) subscribers
2. 2014(replicated DB) to 2016 R1






But great job man, do keep posted with the new updates.


Thanks a heaps,
Ajeeth Kapoor
Mohamed Sami said…
Hi, This was from 10 years :). I do not have any idea now how to do this. I think you should contact Microsoft Support

Popular posts from this blog

Manage Your Database Connection

Number of Days in Current Month