Data Management

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

If a SQL Server database developer aces the <a href='http://www.techrepublic.com/article/5100-9592_11-6126230.html' target='_blank'>verbal portion of the interview</a>, 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.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

27 comments
msypm23
msypm23

I have a database like

PROD_ID, SALE_DATE, SALE_AMOUNT

1, 01-Mar-2011,   20.00

1,  05-Feb-2011,   32.00

2  , 09-JUN-2011,   35.99

2, 02-Apr-2011,   12.00

1, 04-May-2011,   13.00

need a TSQL to get the total sale of each product in each month . Output should be like

PROD    JAN    FEB  MAR  APR  MAY  JUN  JULY  AUG  SEP  OCT --------- DEC

1        NULL  NULL  20.00  NULL  NULL NULL NULL

2         NULL  NULL  NULL  35.99  NULL   NULL  12.00    ---------------------

CAN Anybody  help me please

njliviu
njliviu

Please post the code

njliviu
njliviu

Please post the code

njliviu
njliviu

select p.category, avg(s.saleprice) from sales s, products p where s.productid=p.productid group by p.category having sum(s.customerid) >= 2

Elroy123
Elroy123

Hi, Just wondering if perhaps your first inner join code is incorrect? I think its returning too many customers, i.e. more than those who've bought more than one product. Your code: SELECT s.CustomerID FROM Sales s GROUP BY s.CustomerID HAVING COUNT(CustomerID) >= 2 I think it should be: SELECT s.CustomerID FROM Sales s GROUP BY s.CustomerID HAVING COUNT(distinct ProductID) >= 2 Do you agree or am I missing something?

Shakeelajb
Shakeelajb

Answer to Test item #10 is wrong. Right query is select * , ( select sum(s2.salePrice) from sales s2 where s1.SaleDate >= s2.SaleDate ) as SumSale from Sales s1 order by s1.SaleDate

keremelaye
keremelaye

Hello Tim, I got this article very nice and i hope it will help both the developers and hiring managers to achive their goals. However, have you checked the last questions solution labled as 'listing K'. I do not think that it really hits its purpose. I suggest that it can be changed as: select Saledate, SUM(SalePrice) TotalPrice from Sales group by SaleDate order by SaleDate May i get your feed back soon. Gruum keremelaye@gmail.com

nmwanza
nmwanza

Gud test really i would say I've learnt one more new thing on delete.

msi77
msi77

Try to pass at least through the Basic Knowledge tests at SQL Exercises to prove your skills.

andrew.ramka
andrew.ramka

I like and appreciate the test. Where can I get more questions like this? Also, how long should a test like this take a developer to complete?

gsquared
gsquared

I don't see anything in this test that would qualify anyone as an exceptional anything. Given the database structure, this is all VERY basic T-SQL. Like 6th Grade math - being great at it doesn't make one a Ph.D., but it does allow for ballancing a checkbook and paying bills (basic skills). Yes, the test will find basic T-SQL skills. It gives a decent range of the basics. But I certainly wouldn't call any of it challenging or exceptional. Or am I looking at this from the wrong end of way too much T-SQL experience? Are these actually challenging questions and I just don't realize it? (And, yes, I was able to answer all the questions without looking at your solutions.)

george_c57
george_c57

For Test item #7: "Return the Product Category and the average Sale Price for those customers who have purchased two or more products." If what you really asked (as I believe you intended to ask) was: "Return the Product Category and the average Sale Price for those customers who have purchased two or more different products" then your solution is not 100% correct. Here is what I think IMHO a form of the correct answer should be: select p.Category,AVG(s2.SalePrice) from sales s2 join products p on s2.ProductID = p.ProductID where s2.CustomerID in ( select A.CustomerID from ( SELECT s.CustomerID,s.productid FROM Sales s GROUP BY s.CustomerID,s.productid ) A group by A.CustomerID HAVING COUNT(*) >= 2 ) group by p.Category George

Elroy123
Elroy123

Sorry, just seen that George was alluding to the same thing in the first post. Can you clarify what exactly you intended to ask, as my understanding of the question was the same as George's.

ipedrossubs
ipedrossubs

Should question 8's solution not be: on s.ProductID = p.ProductID rather than on s.ProductID = s.ProductID I'm a web developer btw :) Thanks P

jwilliamsoh
jwilliamsoh

LOL.... 5 years later I come across this and I am sure the comment will never be read; none the less.... Could not have said it better myself.

chapman.tim
chapman.tim

Yes. No, I don't think it is that hard of a test, but I am a database administrator. You would be really, really surprised at how many good developers don't fare well at this test. From the experience you have, the test probably should look pretty easy to you...however, a test like this has been very challenging to the majority of the applicants I have given it to. Perhaps it has been the pool of applicants, perhaps not.

BrooklynPennyPincher
BrooklynPennyPincher

When I went to save this article and its answers, I ran into the duplication of answer 1 's file name for question 10's answer. If you edit the URL given for answer 10, and change the trailing "1" to "10", you get a more reasonable answer.

chapman.tim
chapman.tim

Hi George, Actually, my query is 100% correct for what I asked (and what I intended to ask). Yours is 100% for what you thought I intended to ask. The records returned are the same, but the avgs are diff due to the differences in the questions. Thank you for the comment. If you see anymore ambiguity in the questions I ask please let me know. Tim

njliviu
njliviu

I rearranged the statement and now I understand it well. Thank you. select p.category,avg(s.saleprice) from sales s, products p, (select customerid from sales group by customerid having count(customerid) >= 2) xxx where p.productid=s.productid and xxx.customerid=s.customerid group by category

gsquared
gsquared

If the people you're testing are primarily developers and not DBAs, I can see where this would be challenging. I thought you were talking about testing DBAs with this test. If any experienced/trained DBA couldn't solve all of these, relatively easily, I would be inclined to call him embarassingly incompetent. But a web developer or some such, yeah, I can see this as a valid test of SQL knowledge.

tbrittnacher
tbrittnacher

I am a 25 year old software developer with, what I think, an advanced understanding and use of TSQL. These questions were a breeze for me, as well as your previous article of questions. Would you say I am an advanced SQL developer because I can do these tests VERY easily. Or would that put me in the competent area.

raajnarang
raajnarang

Well the result you mentioned in Test 10 does not seems 100 % correct. The is becuase you may have many transaction for a date. I fixed the query as below. (Let me know if I understood the question wrong) SELECT s.SaleDate, Sum(s.SalePrice) , ( SELECT SUM(SalePrice) FROM Sales s2 WHERE s2.SaleDate

chapman.tim
chapman.tim

I should have made the title include something about it being a test for developers, not DBAs.

chapman.tim
chapman.tim

I agree with Tony, you are well above average. Like I said in the article, I've only had two applicants get all of the questions right on a test like this. People who say they are "SQL Experts" know very little than simple join statements.

Tony Hopkinson
Tony Hopkinson

A dba should be able to do something like this in their sleep, but a lot of developers fall straight into coding mode with this sort of thing and start doing it on the client. For a lot of developers SQl stops are basic select may be a simple inner join. I've recently been interviewing for a position and many of the developers with 'years' of experience in SQL couldn't describe the difference between an inner and outer join.