What are questions to test a programmers knowledge of SQL? [closed]

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.