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
-
2I would definitely give a real live example and ask to write a complex query. For example ask to select the most profitable month for each of last 10 years, if you have the table of the purchases. BUT since you ask about question and answer as well, that probably means you are not expert and cannot judge. In this case you can try some third party testing service, at least as initial filter prior to interview. I would suggest tests4geeks.com. They have SQL test.– Dhaval PatelDec 16, 2015 at 14:29
Add a comment
|
5 Answers
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.
-
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?– blundersOct 11, 2010 at 2:49
-
3The 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").– JohnFxOct 11, 2010 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.– JohnFxOct 11, 2010 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.– blundersOct 11, 2010 at 18:50
-
And yes, the target for the questions would be a programmer, not a database-admin.– blundersOct 11, 2010 at 18:51
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 vs. 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.
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.
-
1Isn't the answer: never sanitize inputs, use a prepared statement? Oct 30, 2011 at 18:37
-
-
@gablin, I really don't see why that is so. Could you explain a bit? How many SQL books discuss this subject?– NoChanceOct 30, 2011 at 20:26
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.
answered Oct 31, 2011 at 7:41
-
1I'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– cjmUKOct 31, 2011 at 12:46
-
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.
