In recent years, Microsoft has revamped their certification tracks for many of their products. One of their newer certification tracks is the Microsoft Certified Solutions Associate (MCSA), which replaces the Microsoft Certified IT Professional (MCITP). The MCSA for SQL Server 2012 is the base level certification for Microsoft’s relational database solution, and is followed by the Microsoft Certified Solutions Expert (MCSE) and the Microsoft Certified Solutions Master (MCSM). In order to achieve the MCSA for SQL Server 2012, you must pass three exams – Querying Microsoft SQL Server 2012 (Exam 70-461), Administering Microsoft SQL Server 2012 Databases (Exam 70-462), and Implementing a Data Warehouse with Microsoft SQL Server 2012 (Exam 70-463). Upon successful completion of all three exams, the MCSA: SQL Server 2012 is awarded. According to Microsoft, this certification proves that you have the necessary qualifications to land a position as a database developer or database analyst.
So what does it take to pass these three exams? What material do you have to know? Let’s start by discussing exam 70-461, Querying Microsoft SQL Server 2012. We will look at the skills measured, their associated weights as a part of the whole, and methods of preparation for the exam.
Overview: Exam 70-461, Querying Microsoft SQL Server 2012
The two areas that are most heavily weighted for this first exam are Working with Data, and Troubleshooting and Optimization. According to Microsoft’s website, Working with Data represents about 27% of the total questions, and Troubleshooting and Optimization questions represent about 25% of the total. The last two areas are equally weighted, both representing about 24% of the total questions, and they are Creating Database Objects, and Modifying Data.
What You Need To Know
In the Working with Data section, you will be tested on your ability to query data using SELECT statements, including everything from fairly simple SELECT statements against a single table to very complex SELECT statements using any and every kind of JOIN, sub-queries, Common Table Expressions (CTE), aggregate functions, XML options, and all the various data types available. In the Troubleshooting and Optimization area, you will find questions about optimizing queries, managing transactions, using TRY/CATCH and other error-handling techniques, as well as your ability to evaluate row-based and set-based operations and their differences. For the section about Creating Database Objects, you need to understand how to create and alter tables, views, constraints, and triggers, all using T-SQL. You should also know how to design a view, what its advantages are, and when you would use an indexed view vs. a regular view. Lastly, the section about Modifying Data will test you on creating and altering Stored Procedures, using INSERT, UPDATE, and DELETE commands, and working with all different types of functions– UDFs, scalar functions, Table Valued Functions (TVF), etc. In this section, you will also be tested on combining datasets, and you will very likely see at least one question testing your knowledge of the difference between UNION and UNION ALL (hint: the latter retains duplicates and is therefore much faster).
For all of the above sections, remember that Microsoft is always going to test you on any new features, so study those new 2012 functions and commands well, and know how to use them. You will see questions about them!
Preparing For the Exam
So how can you prepare for this exam? There are a number of ways, but without question, the best way to prepare for any certification exam is to get hands-on experience with the subject material. Download an evaluation copy of SQL Server 2012 and play with all of these topics. You can find many online resources and tutorials that include various practice exercises, many of which use the sample database environment that comes with SQL Server. If you have extra time and financial resources, another great way to prepare is to take a class from any number of Microsoft Training Partners. You can find links to available classes using the website below in the additional reading and reference area. If you have the motivation and aptitude, you can also use the self-training method. There are numerous books, videos, and online training programs available to help you.
Practice Makes Perfect
No matter how you decide to learn the material, you will likely want to take some practice exams to check your knowledge level. Although this can be costly, the last thing anyone wants to do is go into an exam and come out with a failing score. It is costly to retake the exam, and it is just generally disheartening, so search online for a provider of practice exams, and at least check out whatever free options there are so that you have a feel for some of the types of questions you will be asked and how they are asked. You should be prepared to provide a BEST response out of more than one potentially correct answer, and you should be able to go through the answers and start removing ones that are clearly wrong, so that if you ever do have to guess, you can at least narrow the choices down before flipping a coin.
With all this knowledge, preparation, and practice, you should be able to sit through the exam and come out with a passing score. Microsoft is even running a promotion through March 2014 that offers you a “Second Chance” at these exams, so there is no better time to decide that you are going to take this first step towards your MCSA: SQL Server 2012!
For more information on becoming certified, check out How to Become Certified in SQL Server 2012: Part 2.
Additional Reading & Reference:
Exam 70-461: Querying Microsoft SQL Server 2012 –http://www.microsoft.com/learning/en-us/exam-70-461.aspx