Assess a SQL Server database developer applicant's skills with this TSQL test

by Tim Chapman | Oct 23, 2006 8:19:00 PM

Tags: Databases, server, Tim Chapman, Sale Price, applicant, database, Microsoft SQL Server, SQL Server Newsletter

Takeaway: If a SQL Server database developer aces the verbal portion of the interview, a TSQL test is the perfect way to ascertain the true level of their programming skills. Tim Chapman presents 10 questions that will thoroughly test an applicant's TSQL expertise.

Last week, I presented a list of the baseline technical questions I ask SQL Server database developers during an interview. If I am satisfied with an applicant's responses to the verbal portion of the interview, I like to test their TSQL programming ability; there is no better way to set up scenarios in a database for the developers to solve. This test should be a good measuring stick for your potential database developers.

A couple of disclaimers

Most, if not all, of the questions in this TSQL test can be answered in different ways. The answers I provide are the way in which I like to write queries, and the way I prefer to see them answered. However, since the questions may be answered in various ways, it is important that the test is graded by someone who is very well versed in SQL programming so that any differences can be investigated and graded accordingly.

Weekly SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

In this test, you will not see any questions regarding cursors, stored procedures, or triggers. I am looking for the applicant's ability to answer questions about complicated queries and data modification language statements. If someone performs well on this test, I can feel confident that they will have very few problems writing stored procedures, triggers, etc.

Pre-test script

Before I start the test, I need a schema and some data to run the queries against. Listing A will create what I need.

Once I have the data loaded, I can start working on the test questions. (Tip: I like to have the applicant save the numbered SELECT/UPDATE/INSERT/DELETE statements he or she writes into a text file, so that I can view them later at my leisure.)

The test

Test item #1: Return the First Name, Last Name, Product Name, and Sale Price for all products sold in the month of October 2005. Answer: Listing B.

Test item #2: Return the CustomerID, First Name, and Last Name of those individuals in the Customer table who have made no Sales purchases. Answer: Listing C.

Test item #3: Return the First Name, Last Name, Sale Price, Recommended Sale Price, and the difference between the Sale Price and Recommended Sale Price for all Sales. The difference must be returned as a positive number. Answer: Listing D.

Test item #4: Return the average Sale Price by Product Category. Answer: Listing E.

Test item #5: Add the following Customer and Sale information to the database.

FirstName: Chris
LastName: Kringle
City: Henryville
State: IN
Zip: 47126
ProductID: 3
SalePrice: 205
SaleDate: 12/31/2005

Answer: Listing F.

Test item #6: Delete the customer(s) from the database who are from the state of Maine ('ME'). Answer: Listing G.

Test item #7: Return the Product Category and the average Sale Price for those customers who have purchased two or more products. Answer: Listing H.

Test item #8: Update the Sale Price to the Recommended Sale Price of those Sales occurring between 6/10/2005 and 6/20/2005. Answer: Listing I.

Test item #9: Number of Sales by Product Category where the average Recommended Price is 10 or more dollars greater than the average Sale Price. Answer: Listing J.

Test item #10: Without using a declared iterative construct, return Sale Date and the running total for all sales, ordered by the Sale Date in Ascending Order. Answer: >Listing K.

Scoring

I've given a test similar to this one to many SQL Server database developer applicants. From what I can recall, only two applicants have been able to answer all of the questions correctly.

The overall average score is around 50 - 60%. If an applicant performs above that average, I would qualify him or her as a good TSQL programmer; if the applicant scores over 90%, that individual is an exceptional programmer.

If you have questions about any of my answers or have comments about this test, please post your feedback in the article discussion or feel free to e-mail me directly.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005.

14 comment(s)