The 70-461 exam is meant to be taken by SQL Server database administrators, implementers, system engineers, and developers who have two or more years of experience and are seeking to prove their skills and knowledge in writing queries. Microsoft states that the exam is meant to be taken by those whose primary responsibilities may include:
- Apply built-in scalar functions.
- Apply ranking functions.
- Combine datasets.
- Create and alter DML triggers.
- Create and alter indexes.
- Create and alter stored procedures.
- Create and alter tables.
- Create and alter user-defined functions (UDFs).
- Create and alter views.
- Create and modify constraints.
- Design T-SQL stored procedures.
- Design views.
- Implement aggregate queries.
- Implement data types.
- Implement error handling.
- Implement subqueries.
- Implement the sequence generator
- Manage transactions.
- Modify data by using INSERT, UPDATE, and DELETE statements.
- Modify data by using MERGE statements.
- Optimize queries.
- Query and Manage XML data.
- Query data by using SELECT statements.
- Return data by using the OUTPUT clause.
After passing the Querying Microsoft SQL Server 2012 (70-461) exam, you complete the requirements for the following certification(s):
- MCSA: SQL Server 2012
- MCSE: Data Platform
- MCSE: Business Intelligence
- Number of Questions: Approximately 45-55 questions (Since Microsoft does not publish this information, the number of exam questions may change without notice.)
- Types of Questions: This test format is multiple choice and multiple choice multiple answer. You may also see several scenario questions.
- Passing Score: 700
- Time Limit: 120 minutes
This passing score does not mean that you must answer 70 percent of the items correctly in order to pass the exam. The actual percentage varies from exam to exam and may be more or less than 70 percent. There is no penalty for guessing. No points are deducted for incorrect answers. If a question specifies that you must choose multiple correct answers, you must choose the exact number of correct answers specified in the question in order to earn a point for that item. Some of the questions on the exam may not count toward the calculation of your score. Microsoft will often throw a question in that is meant to gather data that will help them improve the exam.
As with any exam, it will vary from person to person as to what is deemed to be difficult. Be sure you are familiar with each of the topics in the exam objectives listed below.
The Order By option in the SQL SELECT statement has been enhanced in SQL Server 2012. Using both the OFFSET and FETCH along with ORDER BY allows you to control paging through a result set.
Full Text Search has also been enhanced by allowing you to search and index data stored in extended properties or metadata. The newest release of SQL Server will allow you to search things such as a PDF document that has properties filled in such as Name, Address, State, etc.