Skills RequiredThe 70-461 exam comprise of questions from the below area:
- Create database objects (20–25%)
- Work with data (25–30%)
- Modify data (20–25%)
- Troubleshoot and optimize (25–30%)
Practice TestYou can take the Microsoft Official Practice Test (not free), to help you prepare better for the actual exam. I was very satisfied with the quality of the questions in the Practice Test, and it helped me to identify areas where I had to focus upon. All the questions had detailed explanations for the answer options mentioned, which aided me in revising the skills required for this exam. Overall the practice tests did help me to prep up for the exam and gain confidence, but I must say that the actual 70-461 was a lot tougher than the practice exam. Microsoft provides special offers on the Certification exams – in the form of an additional retake or even a practice test. It might not be a bad decision to improve your exam odds, by adding an extra retake. It’s always a good feeling to have the cushion of an extra attempt to pass the exam. You can learn more about the certification and exam offers here.
Actual Certification ExamThe exam is a good mix of multiple choice questions, rearrange statements to form the correct stored procedure/Function/Triggers, select correct options from the drop down lists, case studies and more. The toughest part of the exam will be questions where you would need to actually write the T-SQL Code – query, stored procedure, triggers, function etc. All your hands-on experience in SQL will come to play here. If you find these set of 8-10 questions though, I would suggest not to spend much time on it upfront and get discouraged. Mark them for review and come back to it later, once you have completed rest of the questions. You will always have few questions, which you would be able to answer right away. Knock them down first!
Type of questions and formatsBefore appearing for the exam, it is always helpful to know the question pattern and be familiar with the exam interface. I am listing below the different categories of questions you will find in the 70-461 exam, to help you better prepare for the exam: Build List There will be a scenario mentioned and 4-5 actions to perform in a sequence. You will need to arrange this list of actions in the correct order to achieve the goal. Basically, it will be a kind of Drag and Drop type question and you need to put your selections in the correct order. To tackle these types of questions it will be great if you can thoroughly practice various T-SQL syntax like Creating/Modifying Constraints and DML Triggers, Creating Stored Procedures and Functions, Error Handling and more. Case Studies There will be a Business case with multiple questions associated with it. Make sure you patiently read through the entire business requirement before proceeding to answer the questions. Flipping back and forth between the questions and the case details might take unnecessary time. A helpful tip here will be to read through the various options available in the answer section to quickly understand the intent of the case study. Drag and Drop There will be a number of questions which will need you to drag and drop correct answers from the given list of options. This might get tricky sometimes since there might be cases when the same answer option needs to be dragged multiple times to answer correctly. Multiple Choice Questions These will be the easiest of all the question types and you will need to select the correct option for the given question. A pro tip will be to ensure that you answer all such questions and not leave any unanswered. Remember that there are no negative marks for wrongly answered questions. If you are not aware of the right answer, try to eliminate wrong options and narrow down the set of probable answers to the question. Once you do that, you can try your luck by guessing the correct answer. Writing SQL Code Snippets These will be the toughest questions to crack. A text area will be provided to write the code to resolve a real-life problem. It might be either writing a stored procedure, trigger, user defined function or similar code snippets. SQL Keywords are provided in a separate tab to assist you with writing code. There will be no intelligence when you start writing your code in this text area, so I would say to answer these questions you would need thorough hands on knowledge. There were about 8-9 such questions and took me the most time to answer them correctly.
Study MaterialsThere are a lot of useful resources available online to prepare you for the exam: I would strongly recommend you to go through the book – ‘Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012′. This book covers all the topics and skills which will be required for you to pass this exam. Make sure that you are simultaneously doing a lot of hands-on querying as well. Microsoft Virtual Academy has great content and detailed training material on the various focus areas required for 70-461 – Querying Microsoft SQL Server 2012 Databases Jump Start If you are new to SQL Server, then I would suggest you to go through this SQL Server Tutorial in YouTube. I found it very beneficial to recap some of the areas where I had minimal hands on experience. You can check out these Pluralsight Courses to get detailed walkthrough on the various areas:
- SQL Server 2012 Querying (70-461) Part 1
- SQL Server 2012 Querying (70-461) Part 2
There is lot of information about this exam in the Microsoft Learning Page. Make sure to check it out.
You can install SQL Server Management Studio 2017 here. It is an independent web installer and is decoupled from the underlying SQL Server engine.
Online Proctored Certification ExamYou have the option of taking the certification exam from the nearest Certification center or even from the comfort of your home or office. If you decide to take the Online Proctored exam, like I did, you will be monitored by an offsite proctor via your webcam and microphone. Make sure that you review the system requirements ahead of time and take the available system test from the same computer before the exam day. On the exam day, ensure that your room is distraction free. Keep in mind that during the exam you cannot leave your desk, or else your exam will be canceled. Make sure that you log-in 30 min before the exam scheduled start time so that you have 15 min for the initial verification and launch process by the offsite proctor. In my case, I was put in a queue for 10 min before I was able to get a proctor.
Microsoft Certification BadgesMicrosoft has partnered with Acclaim and will provide you a Certification badge after passing the exam. Acclaim provides an easy way to manage and share your Microsoft Certifications. Having a digital badge for your certification is always helpful since you can embed it in your resume, your personal website or even an email. Not to forget to share the badge online across different social media platforms. This is a completely free service provided by Microsoft. You can find additional information about it here.
ConclusionQuerying Microsoft SQL Server 2012/2014 validates the skills required to create database objects, work with and modify data, and finally troubleshoot and optimize queries. I had a great learning experience while preparing for this exam. There are numerous benefits of earning a certification and advancing your career by acquiring comprehensive technical knowledge tops the list. Good Luck on your certification exams and do let me know in case you have any questions. I would be happy to help.
Latest posts by Samir Behara (see all)
- What’s new in SQL Server Management Studio 17.4; SQL Vulnerability assessment and more - December 26, 2017
- Review of SQL Cop for SQL unit testing - September 29, 2017
- Querying Microsoft SQL Server 2012/2014 – Preparing for Exam 70-461 - September 8, 2017