Study material for exam 70-761 Querying Data with Transact-SQL

By: Daniel Calbimonte   |   Updated: 2017-01-25   |   Comments (13)   |   Related: More > Professional Development Certifications

Problem

I want to pass my first Microsoft SQL Server 2016 certification exam, what exam should 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 first exam related to querying data with Transact-SQL, which is the query language 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?

The 70-762, 70-764, 70-765, 70-767 and 70-768 Exam.

Is this exam similar to the SQL Server exam 70-461: Querying Microsoft SQL Server 2012/2014?

It is similar. Some topics are modified, some are in a different order and some were removed. For example, The Troubleshoot and Optimize module was removed in the new SQL Server 2016 exam. One topic that may require extra effort is the JSON topic. This is a new SQL Server 2016 feature that is included in the exam. Another new feature is temporal 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 are excellent books.
• Beginning Microsoft SQL Server 2012 Programming 1st Edition is another option if 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 have any experience in T-SQL and you find the other books difficult.
• Learn SQL Server Intuitively: Transact-SQL: The Solid Basics is another book for beginners. It cover more topics than the beginner SQL Programming book mentioned before.
• Murach's SQL Server 2012 for Developers (Training & Reference) is another option. It is a complete book with a different style. It covers many of the topics.

Do you recommend videos for this exam?

Yes, these books may be useful:

• Introduction to 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 teacher rather than reading books or watching videos, it is a good choice to start.

Other courses recommended are the following:

• SolidQ is a solid in the Course Catalogue, there are several basic and advanced T-SQL courses.
• Pluralsight is another 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
  • SELECT (Transact-SQL)
  • SELECT Examples (Transact-SQL)
  • A LIST OF SQL BEST PRACTICES
  • Set Operators (Transact-SQL)
  • SQL Tutorial
• 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
  • UPDATE (Transact-SQL)
  • INSERT (Transact-SQL)
  • DELETE (Transact-SQL)
  • Controlling Constraint Checking by Bulk Import Operations
  • OUTPUT Clause (Transact-SQL)
  • Data Definition Language (DDL) Statements (Transact-SQL)

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
  • Ranking Functions (Transact-SQL)
  • GROUP BY (Transact-SQL)
  • Built-in Functions (Transact-SQL)
  • Using PIVOT and UNPIVOT
  • How to replace NULL value by 0 in the Dynamic Pivot result Sql Server
• 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
  • CREATE PROCEDURE (Transact-SQL)
  • Create User-defined Functions (Database Engine)
  • CREATE VIEW (Transact-SQL)
  • SQL Views
  • Difference between scalar, inline and table valued functions
  • Deterministic and Nondeterministic Functions
  • Create Indexed Views
• Implement error handling and transactions
  • Data Definition Language (DDL) Statements
  • SQL Server 2005 Try and Catch Exception Handling
  • TRY...CATCH (Transact-SQL)
  • New THROW statement in SQL Server 2012 (vs. RAISERROR)
  • THROW (Transact-SQL)
  • TRY...CATCH in SQL Server 2005 An Easier Approach to Rolling Back Transactions in the Face of an Error
• 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:

• Exam 70-761
• MCSA: SQL 2016
• Exam policies and FAQ

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