By: Daniel Calbimonte |Comments (13) | Related: More > Professional Development Certifications
Problem
I want to pass my first Microsoft SQL Server 2016 certification exam, what examshould I take and how can I study for this?
Solution
The first exam for SQL Server 2016 is the Microsoft Exam 70-761. It is the firstexam related to querying data with Transact-SQL, which is the querylanguage used in SQL Server.
Which exam should I take before this one?
If you want to get a Microsoft certification, we recommend taking this exam first.
Is this exam difficult?
If you have experience using T-SQL, it should not be very difficult.
What is the score to pass?
700/1000
What other exams are available for the SQL Server 2016 certification?
The70-762,70-764,70-765,70-767 and 70-768 Exam.
Is this exam similar to the SQL Server exam 70-461: Querying MicrosoftSQL Server 2012/2014?
It is similar. Some topics are modified, some are in a different order and somewere removed. For example, The Troubleshoot and Optimize module was removed in thenew SQL Server 2016 exam. One topic that may require extra effort is theJSON topic. This is a new SQL Server 2016 feature that is included in the exam.Another new feature istemporal tables. Please pay attention to these two topics.
Which books do you recommend for this exam?
Unfortunately, there is no Microsoft Training Kit book for this exam yet. However,these books will help you pass the exam:
- Itzik Ben-Gan books,specially T-SQL fundamentals, T-SQL querying and 70-461 Training kits. They areexcellent books.
- Beginning Microsoft SQL Server 2012 Programming 1st Edition is another optionif you do not like Itzik Ben-Gan books. This book covers most of the topics.
- Beginner SQL Programming Using Microsoft SQL Server 2016 is a beginner book.It does not cover most of the topics, but it is a good book if you do not haveany experience in T-SQL and you find the other books difficult.
- Learn SQL Server Intuitively: Transact-SQL: The Solid Basics is anotherbook for beginners. It cover more topics than the beginner SQL Programming bookmentioned before.
- Murach's SQL Server 2012 for Developers (Training & Reference) is anotheroption. It is a complete book with a different style. It covers many of thetopics.
Do you recommend videos for this exam?
Yes, these books may be useful:
- Introductionto JSON in SQL Server 2016 Part1
- Working with XML and SQL Server
- SQL Server tutorial for beginners
- Beyond T-SQL: non-relational features in SQL Server 2016
- Querying Microsoft SQL Server 2012 Databases
- Querying with Transact-SQL
Which courses do you recommend for the exam?
The official Microsoft course is this one:
Course 20761A: Querying Data with Transact-SQL
This course does not include all the topics, but if you prefer a teacherrather thanreading books or watching videos, it is a good choice to start.
Other courses recommended are the following:
- SolidQ is a solidin the Course Catalogue, there are several basic and advanced T-SQL courses.
- Pluralsight isanother company that offers several courses related to T-SQL.
Which links do you recommend to this pass?
Here you have some links that we recommend:
Manage data with Transact-SQL
- Create Transact-SQL SELECT queries
- Query multiple tables by using joins
- SQL Joins
- FROM (Transact-SQL)
- Join SQL Server tables where columns include NULL values
- Joining Three or More Tables
- Implement functions and aggregate data
- Create User-defined Functions (Database Engine)
- Avoid SQL Server functions in the WHERE clause for Performance
- Deterministic and Nondeterministic Functions
- Built-in Functions (Transact-SQL)
- Modify data
Query data with advanced Transact-SQL components
- Query data by using subqueries and APPLY
- SQL Server CROSS APPLY and OUTER APPLY
- FROM (Transact-SQL)
- Query data by using table expressions
- WITH common_table_expression (Transact-SQL)
- Recursive Queries using Common Table Expressions (CTE) in SQL Server
- CTEs vs. Temp tables an Opinion
- Group and pivot data by using queries
- Query temporal data and non-relational data
- Introducing JSON for SQL Server 2016
- SQL Server 2016 Advanced JSON Techniques - Part 1
- SQL Server XML Tips
- JSON Data (SQL Server)
- OPENXML (Transact-SQL)
- Introduction to SQL Server 2016 Temporal Tables
- Temporal Tables
Program databases by using Transact-SQL
- Create database programmability objects by using Transact-SQL
- Implement error handling and transactions
- Implement data types and NULLs
- Data Types (Transact-SQL)
- CAST y CONVERT (Transact-SQL)
- Join SQL Server tables where columns include NULL values
- ISNULL (Transact-SQL)
- COALESCE (Transact-SQL)
Next Steps
For more information, refer to these links:
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Comments For This Article
| Saturday, August 3, 2019 - 3:37:53 PM - Abdel | Back To Top (81952) |
Hi Daniel Many thanks for laying all of this out. I would like to ask for some advice. I am planning to take the EXAM 70-764- Administering a SQL Database Infrastructure 2016 I want to pursue this path, Do I have to take exams before this 70-761 and 70762 or it is fine if go for the above mentioned. I understand it is not a prerequisite but in a real-life would this matter? or after an MCSA one can be employable? | |
| Monday, October 15, 2018 - 10:54:39 PM - Adriana | Back To Top (77955) |
Hi guys! I have a question! It's possible to get the sample databese that use in the book for the exam? I'm studying but no have the database to copy+paste the querys and run it. Thanks! | |
| Wednesday, January 3, 2018 - 10:14:34 PM - gulab tejwani | Back To Top (74734) |
Hi, Use this pratice test for70-761 exam.It will help you clear your concepts. https://www.udemy.com/microsoft-mcsa-70-761-for-sql-server-practice-test/?couponCode=MCSA707619 | |
| Wednesday, November 15, 2017 - 9:37:25 PM - Chad | Back To Top (69805) |
Great article, thank you! Looks like the Deterministic and Nondeterministic Functions link underImplement functions and aggregate data is pointing to the wrong URL. | |
| Friday, November 10, 2017 - 4:17:12 PM - Srini | Back To Top (69536) |
Hi, It’s very useful to the SQL DBAs& Developers to prep for the Certfication Exams. Thanks for the details. Srini | |
| Thursday, November 2, 2017 - 7:02:08 AM - Kris Wenzel | Back To Top (69089) |
Hi, I put together some resources your readers may appreciate. They are located athttps://www.essentialsql.com/70-761-resource-page/ They will compliment you you listing. The study guide I put togheter details the various topics you'll need to understand to pass the 70-761. It is really well organized, linked to helpful articles, and layed out in a logical manner. Please check it out! | |
| Monday, October 9, 2017 - 9:23:45 AM - Daniel | Back To Top (67104) |
There are some non-official exams in Google. | |
| Monday, October 9, 2017 - 8:44:13 AM - Philip | Back To Top (67100) |
Hi Daniel. I am intending to take the Microsoft 70-761 exam. Is there a practice test that I can use as preparation before I go ? | |
| Tuesday, July 4, 2017 - 5:54:48 AM - Renu | Back To Top (58834) |
Microsoft has introduced the book: https://www.microsoftpressstore.com/store/exam-ref-70-761-querying-data-with-transact-sql-9781509304332tting. | |
| Thursday, March 16, 2017 - 4:28:31 PM - Daniel Calbimonte | Back To Top (51255) |
Thanks, we updated the link | |
| Sunday, March 12, 2017 - 12:44:26 PM - Kyle | Back To Top (50892) |
Thank you for laying all of this out. Just a quick observation: Your link for Deterministic and Nondeterministic Functions does not point to that, it directs you to joining three or more tables. | |
| Wednesday, January 25, 2017 - 12:07:48 PM - Daniel Calbimonte | Back To Top (45571) |
Yes, there is a text search in this web page. Write 70-462 and press the search button. You will find an article similar to this one. | |
| Wednesday, January 25, 2017 - 11:40:31 AM - Lau | Back To Top (45570) |
Thank you very much for the information. I was a little disoriented because I can not find a study kit for the exam. Do you have information , links or study books for the 70-762 exam? | |
