How I Passed 70-761 Exam: Querying Data with Transact-SQL – Microsoft?

Saturday (18th May 2019) I went through the 70-761 Exam and passed it. This post is all about the how I planned and passed 70-761 exam with details analysis of the exam.

I was looking for an opportunity to check skills on SQL Server. My Senior suggested me to go for 70-761 Microsoft certification. I did my certification out of my busy job schedule.

How I Planned?

After going through the 70-761 syllabus, I planned it to complete in three weeks.

  1. 1st– 2nd Week – Studying the basics of T-SQL and all new/important features introduced in latest versions of SQL Server like Temporal Tables, Working with JSON  etc.
  2. 3rd Week – Practicing Mock Test from Measureup(https://www.measureup.com/)

How I Prepared for the exam?

“T-SQL Fundamental” By Itzik Ben-Gan and “Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012” By Dejan Sarka Itzik, Ben-Gan and Ron Talmage covers all the topics for the 70-761 exam.

I read each chapter, answered the quick check questions, lesson summary, lesson review questions, and case scenarios and completed these two books in two weeks. Some of the topics which I had never used much at my work, I practiced those enough like Temporal Tables.

Finally, I went through the mock test by measureup and made sure that I am ready to take the exam.

Scheduling and Taking the exam:

18th April 2019, Saturday, I schedule the exam at my office and successfully passed the exam.

Followings are the highlights from the exam:

  • There were total 45 Questions.
  • Total time to finish the exam was 2 Hrs.
  • Total Marks: 1000
  • Passing Marks: 700
  • Most of the questions were objective type with real world problem description specified.
  • Some were subjective where you have to select from multiple dropdowns, or you need to complete the query and submit it.
  • Few were multiple choice-based questions based on real-world problem description specified.
  • Few were multiple choice-based questions based on real-world problem description specified.

Exam Questions Highlights:

  • There were 1 question to distinguish UNION from UNION ALL.
  • There were 1 question to distinguish the difference between EXCEPT, INTERSECT SET Operators.
  • There were 3 questions about CTEs, where two were to set up recursive scenarios.
  • There was 1 question to build a stored procedure based on requirement.
  • There were 2 or 3 questions about error handling and Transactions.
  • Several questions with LEFT JOINS/ RIGHT / FULL OUTER JOIN / CROSS JOIN (Questions for each of these joins, it is convenient to understand the differences well)
  • There was 1 question about JSON/XML query where the purpose was to distinguish JSON AUTO from JSON PATH
  • There was 1 question based on PIVOT Operator.
  • There were a question for writing from scratch a query related to grouping and Windows ranking functions (MAX, AVG, COUNT, RANK(),DENSERANK() etc..)
  • There were 3 question related to performance tuning and index usage (you need to optimize the query to properly use the index defined, why query planned is using index spool operator etc.)
  • There were 1 or 2 questions where we had to understand Indexed Views and data inserting using indexed views.
  • There were 1 question based on understanding of CROSS APPLY and OUTER APPLY usage.
  • There were 4 questions about temporal tables, where at least one of them was important to note the difference of the various expressions AS OF, BETWEEN, CONTAINED and FROM clause.
  • There was 3 question where you had to create a table from scratch based on the requirements (Identifying usage of different data types of same category like out of 6 date data type which one best suits for the problem).
  • NO Functions and MERGE based questions Which doesn’t mean that you can’t.

If you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.

Happy Learning and Good Luck For Your Exam 🙂

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.