Consulting in data analytics is a demanding profession that requires both broad and deep technical knowledge in a variety of tools and technologies. One key skillset expected of just about any analytics consultant is the ability to retrieve data from a source system and manipulate it to fit the client’s needs. Oftentimes, the source is a relational database and in order to retrieve and manipulate data from a database, one must have fluency in writing SQL queries.
Passing a certification exam can be one of the best ways to demonstrate your fluency in SQL. Certification exams often have a rigorous nature and demonstrate that you meet a certain standard of knowledge in the technology you are practicing, establishing you as a subject matter expert. Although there is a lot of overlap in the SQL syntax among major relational database management systems (RDBMS) such as Microsoft SQL Server and Oracle, it is important to study for the RDBMS that you will primarily be working in because some syntax will still vary. Below is an overview of the exam and resources one can use to study for the Querying Microsoft SQL Server 2012 Exam #70-461, which tests in T-SQL, the SQL syntax specific to querying and administering Microsoft SQL Server databases.
Breaking Down the Exam
First, let’s discuss the structure of the exam before we walk through suggestions on how to best prepare for it. Each 70-461 exam contains 40 to 60 questions. Microsoft provides a breakdown of the skills that are assessed during the exam:
- 20% -25% on creating database objects
- 25% -30% on working with data
- 20% -25% on modifying data
- 25% -30% on troubleshooting and optimizing
The exam is 120 minute long and results are available a few minutes after completion. While discounts can be available, the cost is usually around $150. Oftentimes, you have the ability purchase a voucher that includes a free exam retake.
There are a variety of different resources that can help you learn T-SQL well enough to pass this exam. Below is a list of the resources our team used to study and what we found helpful about each one.
- Kudvenkat’s Youtube channel – Kudvenkat’s Youtube playlist on SQL Server is an extremely helpful introduction into writing T-SQL. Kudvenkat’s teaching style provides enough detail to give beginners a thorough introduction to unfamiliar T-SQL concepts. It is highly recommended to follow along the videos by writing the code demonstrated in the videos in a SQL Server environment as practice.
- Beginning SQL 2012 Joes 2 Pros (Vol 1 – 5) – These DVDs are very organized and come with scripts which you can copy/paste into SQL Server to load the databases queried throughout the video. This makes it easy to follow along with the videos without a lot of prep time building the databases and tables yourself. Also, there are practice questions at the end of each lesson to solidify what you have learned. Be sure to get Volumes 1 – 5.
- Training Kit (Exam 70-461) – This textbook is a great reference guide. It is designed specifically for the 70-461 exam and covers nearly everything you need for the exam. It’s lacking in practice exercises, however, which makes it difficult to understand how the concepts are applied. We recommend it should therefore only be used as an informative source rather than a means of practicing your SQL skills.
- Project experience – On-the-job training will be your greatest source of practicing all the concepts you’ve learned from the above sources. In order to pass the exam, you will have to know the concepts tested like the back of your hand, and this will not be possible unless you’ve written the commands hundreds of times. If you can’t integrate regular SQL writing into your job, try to come up with your own querying and data architecting exercises using datasets available on the web.
Below are some additional tips that should help you prepare.
- Create a schedule – Pick an exam date and stick to it. Give yourself ample time to go through all of these resources at a reasonable pace by breaking them into chunks. For example, commit to watching 10 videos and taking one practice assignment / week.
- Review the exam topics – Make sure that you thoroughly understand each topic and subtopic listed in the “skills measured” portion of the 70-461 exam website here prior to taking the exam.
- Find a mentor – Find someone who you can meet with on a regular basis to review what you are learning and quiz you on how well you understand it. This will also help hold you accountable to your schedule.
- Practice – As you work through study materials, always write out SQL queries for practice. Pause videos to practice queries. Type out queries, rather than copy and pasting them to help yourself memorize the commands. Take practice exams to become comfortable with the exam format.
If you follow the above tips you should be well prepared to pass the 70-461 exam. Passing this exam does not only give you a deeper knowledge of SQL and greater confidence in your skills, but it also allows others to understand the depth of your SQL capabilities; including teammates, clients, and potential employers.
That being said, it is important not to be discouraged by failure. If you don’t pass the first time, reassess how to best study for the exam and what to focus on. At certain times of the year Microsoft will offer “second-shot” vouchers, so that you can get a free retake with the exam if you don’t pass the first time. It is a difficult test and not everyone passes on the first try. Best of luck studying and if you have any other tips or questions about preparing for the exam, please leave a comment below.