Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

What are questions to test a programmers knowledge of SQL? What is the answer to the question? And what would a lack of an correct answer mean in terms of time likely to understand the concept(s) related to the question?

GOOGLED: sql challenge

share|improve this question

closed as not constructive by Anna Lear Nov 7 '11 at 5:27

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

add comment

5 Answers

up vote 18 down vote accepted

It depends on how difficult you want it to be. Also, I'm a little wary of giving you the answer because most SQL problems have multiple acceptable ways to do things and there are also ways of solving SQL problems in sloppy ways that will cause other issues. The person "grading" the answer definitely needs to be able to solve it on their own.

That said, here are a few I came up with off the top of my head.

Extremely Easy Level:
Given an employees table with the columns EmpID, FirstName, Lastname, HireDate, and TerminationDate:
Write a query to return all employees still working for the company with last names starting with "Smith" sorted by last name then first name.

Easy Level
Given the Employee table above, plus a new table "AnnualReviews" with the columns EmpID, and ReviewDate:
Write a query to return all employees who have never had a review sorted by HireDate.

Medium Level Given the employee table above, write a query to calculate the difference (in days) between the most and least tenured employee still working for the company?

Hard Level
Given the employee table above, write a query to calculate the longest period (in days) that the company has gone without a hiring or firing anyone.

Harder Level
Again using the same tables, write a query that returns each employee and for each row/employee include the greatest number of employees that worked for the company at any time during their tenure and the first date that maximum was reached. Extra points for not using cursors.

share|improve this answer
    
Nice, answer -- really like the evolution of the questions. Why does the person asking the questions need to be able to answer them? Why not just write unit test based on output and runtime? Also, what would you say the number of connective weeks of progressing mastery of SQL concepts it would take the average programmer to reach the hardest level given by you? Would you also believe that a programmer able to answer the hardest question you give would be able to effectively address a very high majority of SQL related tasks? –  blunders Oct 11 '10 at 2:49
3  
The reason I'd want the asker (or at least the "grader") to be able to answer them is that the approach may tell you as much as the result. I also worry about a non-tech interviewer having a pre-written answer and not accepting other potentially correct answers (The card says "Moop"). –  JohnFx Oct 11 '10 at 14:35
    
As to whether these questions demonstrate the ability to address a very high majority of SQL related tasks. If their job will be to write queries, probably so. If you are expecting them to administer a DB server, then you'd need to ask questions specific to that. –  JohnFx Oct 11 '10 at 14:37
    
Thanks for the clarifications, the non-tech interviewer in this case would not be a person, but a system, hence the unit test and runtime. Clearly systems are not writing or reading code, humans are, but there's so many programmers in the world, and the need in my opinion to have them onsite is limited at best. Guess if a person failed the test, but thought they were right they could always flag as correct. –  blunders Oct 11 '10 at 18:50
    
And yes, the target for the questions would be a programmer, not a database-admin. –  blunders Oct 11 '10 at 18:51
show 1 more comment

I had a hand in developing a technical test for database programmers. The questions were what I consider quite basic: write the CREATE TABLE statements for a given table structure; do some simple queries; etc.

Most job applicants who called themselves experts at SQL flunked the test. One said that although he had been an SQL developer for many years, he had never written a CREATE TABLE statement because the GUI did it for him.

We've had similar experiences with other technical tests. For Windows support staff, the tasks are similar to "create a domain user," "add a printer," "change permissions on a file." Most people can't do those tasks, especially under pressure. We figure that if you can do even the simple stuff, you're probably pretty competent.

share|improve this answer
1  
I'm afraid I'm on the side of the developer using the GUI. You can spend an entire career without regularly writing CREATE TABLE scripts. Many people develop data models in CASE tools which automatically generate the DDL for you. In general, I shy away from any tests that mainly rely on memorising syntax, in favour of those that test a broader understanding of the issues involved –  cjmUK Oct 31 '11 at 12:46
    
+1 @Barry Brown: I agree, and it's an interesting point. –  blunders Oct 31 '11 at 20:39
add comment

If you prefer to ask more open questions: Ask general questions about the DATE, DATETIME... data types. Ask about differences between different vendor implementations / products. Talk about command line tools, bulk loaders, pretty printers... maybe you can learn a new trick during the interview.

share|improve this answer
add comment

I only generally sit on interviews for data specialists, so my questions tend to be hard. But one thing I would require of anyone who will be writing SQL is knowledge of joins and when to use a left join vice an inner join. Anyone who doesn't understand that has no business querying a database in any way shape or form.

Another thing I would do is make sure they understand how to do a GROUP BY and use Aggregate functions.

And the differnce between UNION and UNION ALL has eliminated a lot of poor candidates at my job.

share|improve this answer
add comment

I would ask "Why and how should you sanitize input values given from the user which will be used in an SQL query?"

This is needed to prevent SQL injections, and being able to answer this requires good knowledge about SQL syntax and commands (such as SELECT, UPDATE, DROP, DELETE, etc.), as well how those can be circumvented by use of SQL comments to break a query and inject what the malignant user may wish to do.

share|improve this answer
1  
Isn't the answer: never sanitize inputs, use a prepared statement? –  kevin cline Oct 30 '11 at 18:37
    
@Emmad Kareem, anyone who can't answer that shouldn't be writing SQL. –  HLGEM Oct 30 '11 at 19:03
    
@gablin, I really don't see why that is so. Could you explain a bit? How many SQL books discuss this subject? –  Emmad Kareem Oct 30 '11 at 20:26
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.