Skip to content

Preparing for and taking exam 70-461 (Querying Microsoft SQL Server 2012)

Microsoft Certified Solutions Associate (MCSA): SQL ServerOne of my goals is to become a Microsoft Certified Solutions Expert: Business Intelligence. I decided to start from scratch and take the three exams required for Microsoft Certified Solutions Associate (MCSA): SQL Server 2012 to learn as much as possible. It has taken me a lot longer than I thought it would and I’m not there yet, but I’m one step closer to my goal: I passed the 70-461 exam (Querying Microsoft SQL Server 2012) in January :)

Preparing for the exam
My plan was to work my way through each chapter in the Training Kit book. I read each chapter and answered the quick check questions, lesson review questions, and case scenarios out loud to myself. I did all the exercises, but none of the suggested practices. Instead, I tried to use what I had learned at work. For example, I rewrote some of our queries to use the new window functions, and it was easier to understand how FOR XML worked when I queried data I was already familiar with.

The practice tests that came with the book were generally completely useless and not even close to the actual exam. The only thing they were useful for was to identify which topics I still had to work on. For example, there were a couple of questions about transaction isolation levels that made me realize I didn’t know the topic well enough, so I looked it up online and read more about it there.

I completed the Querying Microsoft SQL Server 2012 Databases Jump Start on Microsoft Virtual Academy and I watched these prep videos:

MCSA Certification Prep | Exam 461: Querying Microsoft SQL Server 2012

Finally, I went through the list of skills measured and made sure that I felt comfortable with everything mentioned there.

However, the main preparation for this exam has no doubt been working with SQL Server every day for years. I already felt very comfortable with most of the skills measured, so I focused on learning the things I don’t do every day such as the new SQL Server 2012 features (we still used SQL Server 2008 R2 when I took the exam), XML, transactions, triggers, functions and cursors.

Taking the exam
I tried taking the exam online twice without getting past the check-in process. (You can read about that in my other blog post.) The third attempt was at an on-site test center. The staff was very friendly, professional and walked me through all the rules and details – all in all a very pleasant experience. You also get to use a whiteboard on-site in case you want to write or draw something, you can’t do that if you take the exam online.

The exam itself consisted of 42 questions. Some were simple questions with one correct answer, some were multiple-choice, some were drag-and-drop where you had to choose a certain number of code snippets and place them in the correct order, and some were questions where you had to type the answer. On the questions where you had to type the answer you could click a button to check the syntax so you don’t have to worry about typos. The different kinds of questions made the exam a lot more interesting. As soon as you end your exam you will see your score and if you pass or fail.

I probably spent way more time preparing for this exam than I had to, but I really wanted to take this opportunity to learn as much as I could.

Good luck on your exam! :)

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, Microsoft Certified Solutions Expert, international speaker, author, blogger, and chronic volunteer who loves teaching and sharing knowledge. She works as a Senior Business Intelligence Consultant at Inmeta, focusing on Azure Data and the Microsoft Data Platform. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Hi Catherine congratulations on passing exam 70-461, and thank you for sharing your tips on preparing for the exam. I plan on following some of your preparation steps, and hopefully, will take the exam in May or June 2015. I also hope to become an MSCE one day. I do not work with SQL, but work with Data and Reporting, which is the reason for being introduced to SQL.I too feel as though the training kit is not enough to pass the exam, so I am referring to other sources. I also read the T-SQL fundamentals by Itzik Ben-Gan and follow the exercises.

I enjoy studying and practicing with SQL, as I learn something new and exciting. It’s interesting!

Hi
congratulations on passing the exam.I have no experience in sql server or any kind of programming. I have started studying Sql and want to a certification .I have been practicing on sql express. Do u think I have a shot at passing it without any real time experience

Thank you. In my opinion you should get at least a few years of real experience working with SQL Server before you take any certifications.

Hi, I was just wondering why would you need years of SQL experience? In my experience work stuff is often one-sided, which means you might write tens of queries a day but still never touch much of what SQL Server can do… It’s not every day you need transactions, query performance, querying xml, whatever. I’ve been using different versions of SQL for years now on both school and work projects and I don’t think I ever needed any of those things I just mentioned, and there’s probably more I don’t even know about.

So from that point of view, unless you are lucky to have a job that actually requires using a lot of different features, work experience does seem valuable only up to a point. If the exam is like a typical school exam, i.e. covering advanced things that you need less than 1% of the time, I think a study book is key.

Hi Catherine I have been working on Sql server since last two years, and I have finally decided to give this exam I have bought the book Microsoft Sql server 2012 T Sql fundamentals and plan on reading this.

Also I plan on reading Querying Microsoft SQL Server 2012 Exam 70-461 Training Kit. Are these 2 books sufficient for preparation?.

Make sure you get plenty of hands-on experience writing queries as well as reading the books :)

Hi Catherine, congratulations! I wish I had come across you post before taking the exam. I have real world experience, but heavily relied on the practice exam that came with the Exam Prep. I have gone through the tutorials on MVA, but still spent 80% of my time going through the practice exams. Needless to say I flunked. My approach this time is not to even look at any practice exam, but read each sentence, do every practice task from cover to cover of Microsoft T-SQL Fundamentals 2012 and the Exam Training Kit.

Yes, the practice test from the training kit book is definitely not enough to pass the 70-461 exam. It sounds like your new approach is a good one!

Excellent post. Looking to take 70-461 myself very soon. Appreciate you sharing

Really useful post, Cathrine. I have one question, do you think I can take this exam soon without attending a course if I never worked with SQL? I only have the book and the MVP videos.

Hi Daniel, why do you want to take the exam without having worked with SQL? If you want to pursue a SQL Server career and just don’t have any relevant work experience yet, I recommend getting plenty of hands-on experience in a virtual lab or development environment before taking the certification exam. The book and the videos could be enough for you, but I would not recommend anyone taking the exam without real-life experience with SQL Server.

Hi Cathrine, Congratulations! and Many thanks for the article, I am from IT Support background , I do have a fair knowledge of SQL Server and use for simple queries and table creations in my day to day job. I am planing to move to MSBI and have started doing self studies for SQL Server 2012 , Could you please suggest some good books for the SQL Server certification and and suggestion on how to slowly migrate to MSBI.

Kind regards
Niranjan

Hi Niranjan, take a look at the blog posts I’ve written about the three MCSA: SQL Server 2012 certification exams. Good luck! :)

Many Thanks Cathrine, ordered the 70-461 books today.

Hi Cathrine, I am getting ready for this exam. Can you write here few questions from exam if you still remember them. :)

Hi Nicholas. No, I will not violate the exam policies by sharing any questions from the exam.

Hi Catherine,
Congrats on passing your exam and thanks for writing this post. I am in the beginner stage (studied basics of SQL in university). I am planning to enhance my skills and decided to learn more about Querying SQL Server 2012 and to take up the test eventually. Could you please advise me how to prepare?
I understand that we need a real time experience to clear this exam but I am working as a software tester and we use SQL server 2012 for our DB. I am currently practicing by writing simple queries but I need to learn more. will the books MS training Kit – querying SQL Server 2012 and T-SQL help? do I need to study both books or anyone is fine??

Also, which book to study first and how to practice?

Thanks and Regards,
Hema

Hi Hema, it’s great that you’re already getting hands-on experience with SQL Server! Take a look at the links I’ve included in this post, especially the list of skills measured. That should help you map what skills you still need to work on. Depending on how you learn best, you can use Books Online and then practice writing queries, or you can buy the Training Kit and work through that. Either way, I recommend practice, practice, practice :)

Hi Catherine,
Thanks for sharing your experience. I am taking this exam in May 2016 and was wondering if it will include any questions on new features introduced in SQL 2014.
Also, since you mentioned that the practice test in training kit is useless, should I buy the practice test sold by Microsoft to get a feel of what kind of questions will be asked or will that be a waste of money.
Would you say that the exam tests your understanding of the concepts more rather than how good you are with memorizing some of the syntax and T-SQL functions ?

Thanks in advance !

Regards,
Avi

Hi Avi, on the Microsoft website it says: “In December 2015, this exam was evaluated for applicability to both SQL Server 2012 and SQL Server 2014. It was determined that no updates to exam objectives were required.” You will absolutely need to study all the new features introduced in SQL Server 2012, though.

The exam is about querying, so it tests how good you are at writing queries and remembering the syntax and functions. You probably won’t see many questions (if any) about concepts.

If you’ve never taken a Microsoft exam before, I would recommend considering Microsoft’s offer that is valid through August 31st, 2016: the Microsoft Certification Booster Pack (https://www.microsoft.com/en-us/learning/offers.aspx). You can choose different packages with practice tests and/or exam retakes.

Good luck! :)

Thanks for replying back and all the suggestions.
You are super responsive here, which is so great. Its like having a mentor.
I will definitely let you know once I pass the exam. I’d like to follow the same path 70-461 -> 70-463 -> 70-462 (one exam at a time).

Regards,
Avi

Thanks for share this.

Hi Catherine,

Just love the way you explain your preparation to achieve this certif.
I’ve passed it (461) once and i missed (2 years ago)…
Actually, in this summer, i’m enjoying the training kit (just feel so comfortable when my experiences with T-SQL is confirmed by the book & discover lots of new logic of SQL Server).
And now, by reading your post, i’ll take in account the T-SQL book by Itzik Ben-Gan :p

Thanks for sharing your experiences with us.
Dan

P/s: honestly, if i fail one more time, i think that i will not regret, the 2 books will offer me lots of useful knowledge ;)

Hi Catherine,
I registered to take classes to prepare me for the Certification exams 70-461,70-462.
I was however surprised that the school was teaching using Microsoft SQL Server 2008.
It is November 2016, should I continue with the class?

Which center are you taking the course? Is it in Montreal?

Hi Cathrine – do you get an MCP tag after you pass 70-461? Thanks.

Hello Cathrine,

First at all I want to give my congrats to this excellent article. I agree with you when you said that Beginners (like me) need hands-on experience before trying to take the exam. For that reason I wanna ask your help if you know any other resources (Book, link, etc..) that could help us with this. I have worked as application support and I’m taking the SQL course (70-461) at plurarlsight.com now but I know that is not enough. and I have seen in different sites everybody say the same (the training kit book doesn’t help to take the exam)…

Thanks in advance,

Jhancarlo Veras

Hi Cathrine,
Thanks a lot for sharing your experiences, it is very useful for people like me, who’s planning to take this exam. I am stuck with one confusion, can you please clarify? I have been working in Sql for the past 2 years and I am planning to take 70-461 exam. However, to pursue MCSA certification (Sql 2012/14), we need to take 3 exams (70-461, 462 & 463). I am not much familiar with the courses of 462 & 463 exams. But if I take 461 exam only (instead of full certification), will I be able to project it my resume, that I completed 70-461 Sql 2012/14 exam?

Also, will you be able to tell the differences between the exams 98-364 (MTA) and 70-461?
Thanks for your inputs!!

Regards
Satya

Hi Cathrine,

I completed Microsoft 70-461(SQL Server 2012/2014) assessment on July 8th, I like to know how long the certification will be valid.

Thanks and Regards,
Krishna Prasad S P

I have a few questions I need help with: (1) I have SQL Server 2012 installed on my computer and books to study for Exam 70-461 referring to SQL Server 2012. Can I still study from Books and Videos that refer to SQL Server 2012 for the current Exam 70-461? (2) Can I start working in a career after passing Exam 70-461? If so, what are the names of the careers That I can work in after passing this exam? (3) Are there many jobs available for a person who passes SQL Server Exam 70461? Thanks for your help.

Hi Cathrine. Thanks for sharing your experience and resources for passing this exam. I’m just wondering – is there a reason you chose 70-461 over 70-761 and do you know the difference between the two? I understand that 70-761 is more recent but it seems like from what I have read in various forums and from other blogs is that the topics covered are essentially the same. Not sure which one I should go for!

Hi Jenna, I passed exam 70-461 in January 2015. Exam 70-761 was not released until October 2016, so there was no deliberate choice of one over the other. I took the one available at the time that was required for the certification I wanted ;)

Definitely go for 70-761, which focuses on SQL Server 2016 and the newer features. There is absolutely no point in pursuing an exam focused on SQL Server 2012/2014 in 2019, unless it is required by your employer because those are the versions you will be using for years to come :)

Hi! This is Cathrine (again). Just a reminder. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Leave a Reply to Cathrine Cancel reply