The types of questions found on these assessment tests fall into two broad categories: multiple choice and programming. Multiple choice questions can test basic concepts as well as query analysis. Programming questions involve actually writing complete queries and/or stored procedures based on the requirements of the question (e.g. write a query that retrieves all orders placed by customer John Smith).
These multiple-choice tests will typically ask about many different aspects of SQL programming and/or database administration to see how much the test taker knows. The number of questions on a SQL test seem to range from as few as 10 - 100 questions. These assessment tests are typically designed by more senior database programmers/developers/administrators and are thus very company specific.
For programming tests, the difficulty is more easily gauged than on multiple-choice tests. Writing a query to return all the rows in a table is much easier than writing a query to order the information in the table by multiple rows in a table.
The number of questions and time to complete them vary much more than the multiple choice tests. The time allotted for programming tests depends most on whether the programming test is independent (or in place) of the multiple choice test. If the programming test is separate from the multiple-choice test, then the programming test will typically be comparably lengthy time-wise.
The main job types that require knowledge of SQL Server are database programmers, developers, and administrators. These types may require knowledge of other database programs (e.g. MySQL, Oracle). However, this document will focus exclusively on Microsoft SQL Server.
Programmers write queries and stored procedures to add/remove/edit information in the database. Developers make existing databases better through increased functionality. Administrators support and maintain existing databases. It is important that these categories be understood as the nature of the jobs will change the content of the appropriate assessment tests.
For example, programmers are much more likely to only be tested on the SQL language, administrators will be tested on the configuration and maintenance of a SQL database, and developers will likely be tested with a bit of both.
There are two main assessment companies that provide SQL assessments: Brainbench and Kenexa Prove It. Brainbench provides both administration and programming tests for SQL Server 2000, 2005, 2008, 2012, and 2014. Kenexa Prove It offers similar tests to Brainbench, but does not have any tests listed on their website for SQL Server 2014.
Below is a sample questions found on the web. Two are multiple choice questions and one is programming question.
Both were taken from SQL's website.
What is wrong with this SQL query? Correct it so it executes properly.
SELECT Id, YEAR (BillingDate) AS BillingYear FROM InvoicesWHERE BillingYear >= 2010;Write a SQL query to find the 10th highest employee salary from an Employee table.
Explain your answer. (Note: You may assume that there are at least 10 records in the Employee table).