Here is a useful exam that you can administer in either 10 or 15 minutes, either oral or written, when you interview for hiring permanent or contract workers for jobs in SQL Server development or support. (This is not a test for SQL Data Base Administrators.) After the list of 15 questions (below) you will find the answers, each with a reference. After the answers you will find a discussion of why this exam has worked reasonably well in my consulting practice at Business Intelligence Consultants LLC, and can be a useful part (but not as the whole) of your hiring decisions. I welcome your opinions, comments and workplace stories in the forum.

Questions

1. Can an inner join be written without the JOIN ON clause? If so, in which clause is the joining condition specified?
2. What do SQL aggregate functions do with NULLs?
3. Your SQL queries are aborting and you suspect it is due to table and column name case sensitivity. What database property tells you if your objects are case sensitive?
4. You need to access 2 SQL servers in 1 query (this is called a distributed query). You decide to use 4 part names for these servers. What are the 4 parts of a 4 part name? What must you or your DBA or someone with sufficient privileges do first?
5. Assume table t has column c. What is the difference between COUNT(*), COUNT(c), COUNT(1) on a SELECT from table t?
6. What is the difference between #TableName and ##TableName?
7. What is a clustered index (please answer in 1 sentence)? How many can a table have?
8. Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?
9. What is a Cartesian Product? Is this the same as a Cross Join? Is it “Best Practices” to never use either of them?
10. In a SELECT query, which (if any) of these combinations of clauses is valid? GROUP BY without HAVING, GROUP BY with HAVING, HAVING without GROUP BY?
11. Which (if any) of the following objects allow parameters? Stored Procedures, Views, Functions, Triggers, SELECT Queries.
12. A subquery is also called an inner query or inner select, while the statement containing a subquery is called an outer query or outer select. Which (if any) of these SELECT statement clauses cannot contain a subquery? SELECT list clause, FROM, JOIN ON, WHERE, GROUP BY, HAVING, ORDER BY.
13. In a SELECT statement, can logical operators AND, OR, NOT appear as predicates in the JOIN ON clause? If so, how would placing them in the JOIN ON clause be different from placing them in the WHERE clause?
14. In the ANSI ISO standard, what happens when a NULL is compared to a NULL?
15. What (if anything) does this statement output? SELECT COALESCE(NULL, 1, 2, 3, ‘TRUE’, ‘FALSE’)

Answers are below, but you should attempt to answer these before reading them.

 

 

 

 

Answers with References

1) Yes. WHERE clause. For example, an “old style inner join” has the join in the WHERE clause. Reference:

• http://technet.microsoft.com/en-us/library/ms191472(v=sql.105).aspx

2) Aggregate functions disregard NULLs (except for COUNT() – see question #5). Reference:

• http://technet.microsoft.com/en-us/library/ms173454.aspx

3) Collation. Reference:

• http://technet.microsoft.com/en-us/library/ms144260(v=SQL.105).aspx

4) A 4 part name consists of ServerName.DatabaseName.SchemaName.ObjectName. First the DBA must create a linked server using execute sp_addlinkedserver; or in SSMS Object Explorer, right-click Server Objects, select New, and select Linked Server. References:

• http://technet.microsoft.com/en-us/library/ms190479(v=sql.110).aspx
• http://technet.microsoft.com/en-us/library/ms191277(v=SQL.105).aspx

5) COUNT(*) and COUNT(1) count all rows; but Count(c) only counts rows with non NULL in column c. Reference:

• http://technet.microsoft.com/en-us/library/ms173276(v=sql.105).aspx

6) #TableName is a local temporary table visible only in the current session; ##TableName is a global temporary table visible to all sessions. Reference:

• http://technet.microsoft.com/en-us/library/ms174979.aspx

7) A clustered index is the index of the physical order of the rows of the table on disk. Therefore every table has at most one, since it can only physically exist in one sort order on disk. Reference:

• http://technet.microsoft.com/en-us/library/ms189051(v=sql.105).aspx

8) Yes. The OUTPUT clause of the INSERT statement. Reference:

• http://technet.microsoft.com/en-us/library/ms174335(SQL.110).aspx

9) A Cartesian Product, also known as a CROSS JOIN, consists of all rows of the left table, joined to all rows of the right table. They have many uses which meet “Best Practices” and can be elegant in some solutions. References:

• http://technet.microsoft.com/en-us/library/ms190690(v=SQL.90).aspx
• SQL Cookbook by Anthony Molinaro: http://shop.oreilly.com/product/9780596009762.do

10) All. References:

• http://technet.microsoft.com/en-us/library/ms177673(v=sql.110).aspx
• http://technet.microsoft.com/en-us/library/ms180199.aspx

11) Stored Procedures and Functions allow parameters. Triggers, Views and Select Queries do not allow parameters. References:

• http://technet.microsoft.com/en-us/library/ms187926(v=sql.110).aspx
• http://technet.microsoft.com/en-us/library/ms186755.aspx
• http://technet.microsoft.com/en-us/library/ms189799.aspx
• http://technet.microsoft.com/en-us/library/ms187956(v=sql.110).aspx
• http://technet.microsoft.com/en-us/library/ms189499.aspx

12) GROUP BY and ORDER BY. References:

• http://technet.microsoft.com/en-us/library/ms189575(v=SQL.105).aspx
• http://technet.microsoft.com/en-us/library/ms143179(v=SQL.100).aspx
• http://www.archivum.info/microsoft.public.sqlserver.programming/2005-08/02666/Re:-Saving-Time-in-12-hour-format.html

13) Yes. (This is called a “complex join” or a “theta join”.) On an INNER JOIN, logical predicates in the JOIN ON clause have the same effect as placing them in the WHERE clause; however on an OUTER JOIN logical predicates in the JOIN ON clause can set joined columns to NULL without eliminating their respective rows. References:

• http://technet.microsoft.com/en-us/library/ms187518(v=SQL.105).aspx
• http://en.wikipedia.org/wiki/Relational_algebra#.CE.B8-join_and_equijoin

14) UNKNOWN. (Note: SQL uses ‘Three Valued Logic’ (3VL) which uses TRUE, FALSE, UNKNOWN.) Reference:

• http://technet.microsoft.com/en-us/library/ms188048.aspx
• http://en.wikipedia.org/wiki/SQL

15) 1. Reference:

• http://technet.microsoft.com/en-US/library/ms190349(v=sql.110).aspx

 

Discussion

In my consulting practice I am sometimes invited to interview a candidate for a SQL developer or support role, either solo or with a team,. Often there is a fear that resume inflation by candidates for contract or permanent jobs will result in a junior SQL developer being hired into a senior role, or that a person who recently “read a book” or “took a class” on SQL will be hired into a job requiring several years of full time equivalent SQL programming experience. Some managers and senior SQL programmers tell me that they need an exam, either oral or written, to administer to job candidates to certify that only experienced, senior level SQL staff are hired because they have been burned in the past by hiring a junior person for a senior role. We can all sympathize with this legitimate need to separate junior from senior SQL staff in hiring, and many of us have some horror story to tell regarding resume inflation.

Testing a complex subject is difficult. Test creation, evaluation and measurement is a profession unto itself, often staffed with senior professionals holding Ph.D.s in fields such as Educational Psychology and Cognitive Measurement. In how to test and measure numerically what you really need to test, fairly, accurately and without bias. To illustrate my point, I would cite the necessary sophistication of college entrance exams, SAT and ACT, or graduate school entrance exams, the LSAT, GRE, or MEDCAT tests. The SQL test included in this article is not a sophisticated test. However, it can quickly, in 15 minutes or less, and with reasonable success, separate senior SQL developers and maintainers from junior staff or those who “just read the book” or “just finished a class” on SQL programming or who have just 1 or 2 years of SQL programming full time equivalent experience. This test does not purport to do more than that.

Remember also that this test is just one factor in your hiring decision, and that resume, past experience and in-person interviewing skills should also inform your hiring decisions. In my consulting practice, I have found that people who score 10 or above on this test are very likely senior SQL developers or supporters with several years of full time SQL experience; whereas people who score 5 or less are likely junior SQL “beginners”. Scores between 6 and 9 lie in an uncertain nether land where you may need to use other questions or interviewing skills to make a determination.

Although we may have sympathy for beginners wanting to enter the SQL programming profession, and even though we all must begin our SQL careers with some job on someone’s payroll, nevertheless when we require a senior experienced SQL developer or maintainer, a junior SQL staff hire will not do, and we need a quick test to guarantee, within reasonable limits of doubt, that we are hiring what our business requires.

I have found in my consulting practice that this test achieves the following goals. What we seek is:

1. A quick, simple, easy to administer, easy to take test with about 15 questions. Each question should take no more than 1 minute to answer. Either one knows the answer or one does not. Total test time: Either 10 minutes or 15 minutes, but no more.
2. A “guess proof” test such that a candidate who does not know the answer cannot score highly by guessing (e.g., a TRUE/FALSE test would have an expected grade of 50% when taken by a person who knows nothing about the profession.) For example, notice that Question #10 has 4 possible answers; therefore the probability of a person who does not know the answer guessing correctly is (1/2)**3 = 1/8 = 12.5 %. Those are not good odds for getting a job for which one is not qualified.
3. A “bullet proof” test that is clear and concise, eliminating ambiguity as far as possible. I remember with a touch of shame, and perhaps you do as well, scoring low on a test because I did not know the material and then cajoling the professor for a higher grade in college or high school. An “unarguable” test is best; consider the SAT or GRE, which are designed by experts to be unambiguous and argue-proof.
4. A test with at least one tough question (# 12, #13 are tough) so no one comes aboard on the new job with 100% “Ace” grade; Otherwise that new hire may feel they need not continue learning on the job. We should all learn something new about SQL every day of our careers. Even some senior developers may not get Questions #12 or #13 completely correct. This raises the bar on the overall test. A good test tests the extremes as well as the middle population.
5. Any test is per se a learning opportunity. I like that! Always keep learning, whenever possible. Hopefully everyone who takes this 10 or 15 minute test will learn something new from it, as well as go home and look up a few of these questions and answers.
6. A test with meaningful, important, useful questions that a SQL developer or supporter would likely encounter in a real task or problem in the first few months on the job (not once in a 35 year career). All these questions were taken from repeat problems I observe in my consulting practice at corporate sites. Bizarre or esoteric or highly peculiar SQL facts are not worth testing; such facts are learned only through unlikely coincidence and therefore have low probability of contributing to team productivity or being applicable to real problems. They address the atypical, which has a low likelihood of being useful.
7. A test which is easy to grade. This test can be graded in less than 5 minutes. No code to grade, no essay question, no diagram to grade, no online portion.
8. An unbiased test. This test contains no jokes or fluff comments that could be construed as biased or discriminatory. We must never open the door to discrimination in our hiring practices.

This test is quick to administer – give your candidates no more than 1 minute per question. There is nothing to design or construct, no code to write, no code to correct, and nothing to think deeply on. Either one knows the answers or one does not. If an even faster to administer exam suits your interviewing process (for example, many managers limit total interview time to one hour per finalist candidate), alternatively you can give the candidate only 10 minutes to answer these questions, either orally or printed on paper. In this way you can keep the overall interview process to 1 hour total per finalist job candidate.

As a final bonus to you, consider this: Anyone scoring 100% has a good chance of having read this article and therefore of being an avid reader of SQLServerCentral.com, so I hope that you would want to hire those candidates on that basis alone! I would!