Sunday, June 03, 2007

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

Comments:
12 rules you say... so you wont hire the ones that say 18? :)
 
Great questions. When DBA'ing at a university, I interviewed the expected number of "recently graduated but rating myself as expert" candidates. The NULL questions consistently had 'em running for the hills. "Huh? Three-valued logic, what's that?"

:-)
 
Great post. As a candidate would I get extra marks for spotting your trick question?

'Do they know that both NULL = NULL and NULL <> NULL are false?'

declare
x BOOLEAN;
begin
x := (null=null);
if (x)
then
dbms_output.put_line('NULL=NULL IS TRUE');
elsif not (x)
then
dbms_output.put_line('NULL=NULL IS FALSE');
elsif x is null
then
dbms_output.put_line('NULL=NULL IS NULL');
end if;

x:= (null<>null);
if (x)
then
dbms_output.put_line('NULL<>NULL IS TRUE');
elsif not (x)
then
dbms_output.put_line('NULL<>NULL IS FALSE');
elsif x is null
then
dbms_output.put_line('NULL<>NULL IS NULL');
end if;
end;
/

NULL=NULL IS NULL
NULL<>NULL IS NULL

PL/SQL procedure successfully completed.
 
Hahaha, definitely RGT!!!
 
Articles like this really wind me up. In my experience, the resident Oracle 'techie' should be the last person to tech screen a prospective DBA candidate. Their hidden agenda is often two fold. First, don't let your boss hire someone smarter or more knowledgeable that you so you retain your 'head honcho' status. Second, find the most obscure used-once-in-a-career questions to catch the candidate out and make yourself feel superior.

Understanding relational concepts and knowing how to ADMINISTER the Oracle database should be expected. Knowing the ins and outs of SQL analytical functions? Erm....wouldn't that be more important and RELEVANT if you were an Oracle DEVELOPER?

The range of options, configurations, platforms, operating systems, storage set-ups, etc. is huge when it comes to administering Oracle databases. No DBA, and I mean NO DBA, can possibly know them all like the back of their hand.

Again, in my experience, the best DBAs obviously know a great deal about many specific subject areas and those they're not so sure about they know where to look or who to ask.

Personally, with 15+ years of Oracle DBA experience working on virtually every flavor of UNIX and Windows/Linux, ranging from thousands of online users to just a handful, from T bytes of data to a few hundred M bytes, backed with every OCP DBA certification you can get going all the way back to 7.3 and a degree in Computer Science thrown in just for fun, I get a bit insulted when some Johnny-Come-Lately Oracle tech guru wannabe starts asking me in an interview when I'd use BETWEEN and when I'd use HAVING.

An interview is supposed to be a two way street of open communication. Respect to flow BOTH ways. The best interviews I've been involved with have been conducted by people who KNOW HOW TO INTERVIEW and allow the candidate to expand on what they're good and and know how to do rather than focus on what they don't know and don't know how to do.
 
Quick question: where did I saying anything about DBAs?

This is about SQL.

If you want to hire a DBA and your first question is "how good is his/her SQL?" I think you have a problem.

By the way, did you click on that very first link? Do the arguments I made over one year ago look familiar? :)
 
Point taken. Perhaps you're unaware that your site is being linked to in today's searchoracle.com email newsletter, in an article called "SQL interview questions; getting hired as a DBA". Hence my response with my DBA hat on. I meant no disrespect to you personally, hence I stressed "in my experience".

I did check out the "first link" you mentioned. Kudos Robert. It oozed common sense. I especially appreciated the 'does not need to regurgitate syntax' philosophy. Writing something from memory in 1 minute or looking it up in 3. What's the practical difference?

Interviewing highly technical people is difficult. No question about it. The hiring manager asking his existing staff to either contribute questions or do the interview for them is very common, in my experience. And also quite wrong, in my opinion.

As I mentioned, I've been a DBA for a long time, but does that qualify me to interview other DBAs or even Oracle developers? I don't think so. I'm certainly able to ask technical questions and know if the answer that comes back is correct or not, but that's not really an interview, is it?

Unfortunately, in the dozens of permanent and contract positions I've held over the years, only a small handful have been managed by someone who (a) knew what they were doing; (b) knew what I did and (c) understood/appreciated what I needed to know/do in order to get my work done. I'm sure this is the root cause why the not-so-good candidates get jobs. Better screening is needed and some of your tips and pointers are helpful. I know one thing for certain, Oracle's OCP DBA exams ain't it! ;-)
 
Advice to all of you, don't ask a question in an interview that you just happened to stumble upon or are very happy it's a question that you alone or a selected few know. That means that tid bit of information is not really that pertinent, how can you be happy to send candidates running for the hills? I have studied under the tutelage of some very brilliant people, one created the tracking system for UPS (Nardi) and as they taught me it's very easy to find hard questions that people can't answer. I feel most of you are full of cyber arrogance.....

I don't care who I offend
 
I strongly URGE people who feel the same way to visit that first link (http://thinkoracle.blogspot.com/2006/02/oracle-interview-questions.html).

Check out the fifth rule. Quoting myself: "Just remember, the purpose of the interview isn't to make YOU look clever"

That being said, I don't care if someone wrote UPS ... if they don't understand what NULL is, they probably made some serious mistakes. If they don't know about isolation levels their app might corrupt the data as more users use it simultaneously. Without using bind variables, it'll be slower than molasses. These are all highly pertinent, which ones do you think aren't?

You're not offending me, but I offered to you what has worked for me. If you've got a better system, stick with it, but it is a little rude to assume I'm arrogant. Maybe that means you're insecure?
 
Hey,

Long time no hear (see?). Hope things are going well for you.

LewisC
 
Hi Lewis. I changed jobs March 2006, and this new job doesn't involve as much Oracle as previous positions. End result: fewer posts.

But yes, all is well. I'm happy to leave this resource to others, and there will still be new content. Best thing is to use my RSS feed so you can be alerted when there's something new.
 
This comment has been removed by the author.
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
Good technique information, thanks for the share
 
Good blog here. Looks like the comments got slightly heated for a second.

Thanks for the input.
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
Man this post is a spam-magnet!
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
Thanks for sharing us about this interview. For a job interview, there lies some trick questions that you need to know and get through smoothly. Interview Coaching Calgary helps you figure out what the are the right things to say.
 
Interesting perspective on how to interview people to test their SQL skils. When preparing, I found these questions helpful:

Advanced SQL Interview Questions
 
Hi

I read this post two times.

I like it so much, please try to keep posting.

Let me introduce other material that may be good for our community.

Source: Department manager interview questions

Best regards
Henry

 
These are good insights to keep in mind but it's better to undergo interview coaching Edmonton. It will help you boost your confidence.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?