Exam 70-761: Querying Data with Transact-SQL

Certifications from Microsoft provide you with a professional edge by demonstrating your skills and abilities to embrace new technologies. By being globally recognised, these certifications verify your skills and unlock an avenue of opportunities for you.

The Microsoft Querying Data with Transact-SQL 70-761 exam helps you validate your skills and knowledge that is necessary to manage and query data and to program databases. This exam covers a vast syllabus and thus requires deep learning. The Microsoft 70-761 exam will catapult your career and help you achieve new heights.

What is Microsoft 70-761 Exam?

Microsoft Querying Data with Transact-SQL 70-761 exam is one of the first exams related to querying data with Transact in SQL Server. This exam validates your mastery of SQL Server 2016 Transact- SQL data management, database programming and queries.

This exam is designed for IT professionals who are willing to advance their status by focusing on the critical-thinking and decision-making acumen needed for success at the MCSA level.

Why Choose Microsoft 70-761 Exam?

The Microsoft 70-761 is a highly acclaimed certification. This exam makes your resume shine and grabs the attention of your potential employers. The benefits of Microsoft 70-761 Exam are-

This certification validates your mastery in SQL Server 2016 Transact
Microsoft Certification enhances your employability and makes you job ready.
It also helps you chase further advanced goals in the field of IT.
This certification guarantees high growth opportunities. About 23% of Microsoft certified professionals reported receiving up to a 20% salary increase.
Microsoft Certifications provide you with a competitive edge over non certified candidates. It is estimated that certified employees earn 15% more than those without certification and are nearly 20% more productive.
You’ll make immediate impact as a Microsoft Certified Professional. This credential will help you in getting noticed and bring recognition.

Also most importantly, once you’re certified you will be able to share your achievements through your Microsoft Badge. This Microsoft Badge can be shared online within your professional network as it trusted and verifiable. It also helps find jobs easily and increases your salary potential.

Microsoft 70-761 exam, Querying with Transact-SQL badge

Microsoft Certification Details

The Microsoft 70-761 exam is your first step towards earning the MCSA certification.

On successful completion of both Microsoft 70-761: Querying Data with Transact-SQL and Microsoft 70-762: Developing SQL Databases you will achieve the Expert Certification of MCSA: SQL 2016 Database Development.

The MCSA: SQL 2016 Database Development demonstrates your skills as a database professional, for both on-premises and cloud-based databases.

Objectives of Microsoft 70-761

Microsoft Querying Data with Transact-SQL 70-761 exam focuses on the following objectives-

Filter, sort, join, aggregate, and modify data
Use sub queries, table expressions, grouping sets, and pivoting
Query temporal and non-relational data, and output XML or JSON
Create views, user-defined functions, and stored procedures
Implement error handling, transactions, data types, and nulls

Exam Details

The Microsoft 70-761 Exam is definitely your way ahead to become a Microsoft Certified MCSA Expert. Following are some basic details about the exam.

Target Audience

This exam is intended for SQL Server database administrators, system engineers, and developers who are seeking to validate their skills and knowledge in writing queries

Prerequisite for Microsoft 70-761 Exam

The candidate must have an experience of two or more years in Microsoft SQL Server.

How to Schedule the exam?

The Microsoft Querying Data with Transact-SQL 70-761 exam is built to test your abilities of SQL Server 2016 Transact- SQL data management, database programming and queries. The exam can be scheduled as

For non-students interested in technology at Pearson Vue

For students or instructors at Certiport

Exam retake policy

Microsoft allows 5 attempts per year for a specific exam. The exam retake policy for Microsoft 70-761 Exam is as follows-

If a candidate is not able to clear the exam in the first attempt, the candidate must wait at least 24 hours before retaking the exam.
If a candidate does not clear the exam the second time, candidate must wait at least 14 days before retaking the exam a third time.
A 14-day waiting period is also there for the fourth and fifth subsequent exam retakes.

Cancellation and Reschedule Policy: Microsoft 70-761 Exam

Microsoft allows free cancelling of the Exam within 24 hours prior to your scheduled appointment.

But, if you fail to show up on your exam day without rescheduling or cancelling your exam, Microsoft reserves all rights to forfeit your entire exam fee.

Rescheduling and Cancellation of your scheduled exams can be done through the Certification Dashboard.

Exam FAQ

For more queries visit: Microsoft 70-761 Exam FAQ’s

Exam Course

The Microsoft 70-761 Exam covers the following domains. Percentage against each domain represents its weight age in the exam.

Domain 1- Manage data with Transact-SQL (40–45%)

Create Transact-SQL SELECT queries

Identify proper SELECT query structure (Microsoft Documentation: SELECT (Transact-SQL))
Write specific queries to satisfy business requirements
Construct results from multiple queries using set operators (Microsoft Documentation: Set Operators – UNION (Transact-SQL))
Distinguish between UNION and UNION ALL behavior
Identify the query that would return expected results based on the provided table structure and/or data

Query multiple tables by using joins

Write queries with join statements based on provided tables, data, and requirements (Microsoft Documentation: Joins (SQL Server), Join tables and queries)
Determine proper usages of INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, and CROSS JOIN (Microsoft Documentation: Joins (SQL Server))
Construct multiple JOIN operators using AND OR (Microsoft Documentation: AND (Transact-SQL), OR (Transact-SQL))
Determine the correct results when presented with multi-table SELECT statements and source data (Microsoft Documentation: Create a query based on multiple tables, Use a union query to combine multiple queries)
Write queries with NULLs on joins (Microsoft Documentation: Joins (SQL Server))

Implement functions and aggregate data

Construct queries using scalar-valued and table-valued functions (Microsoft Documentation: CREATE FUNCTION (Transact-SQL), Create User-defined Functions (Database Engine))
Identify the impact of function usage to query performance and WHERE clause variability (Microsoft Documentation: WHERE (Transact-SQL))
Identify the differences between deterministic and non-deterministic functions (Microsoft Documentation: Deterministic and Nondeterministic Functions
Use built-in aggregate functions (Microsoft Documentation: Aggregate Functions (Transact-SQL))
Use arithmetic functions, date-related functions, and system functions (Microsoft Documentation: Date and Time Data Types and Functions (Transact-SQL))

Modify data

Write INSERT, UPDATE, and DELETE statements (Microsoft Documentation: Insert, update, and delete records from a table using Access SQL)
Determine which statements can be used to load data to a table based on its structure and constraints (Microsoft Documentation: Unique Constraints and Check Constraints, CREATE TABLE (Transact-SQL), Primary and Foreign Key Constraints)
Construct Data Manipulation Language (DML) statements using the OUTPUT statement (Microsoft Documentation: Queries, OUTPUT Clause (Transact-SQL))
Determine the results of Data Definition Language (DDL) statements on supplied tables and data (Microsoft Documentation: Transact-SQL statements)

Domain 2- Query data with advanced Transact-SQL components (30–35%)

Query data by using sub queries and APPLY

Determine the results of queries using subqueries and table joins (Microsoft Documentation: Subqueries (SQL Server))
Evaluate performance differences between table joins and correlated subqueries based on provided data and query plans (Microsoft Documentation: usage of joins in Microsoft Query, Subqueries (SQL Server))
Distinguish between the use of CROSS APPLY and OUTER APPLY (Microsoft Documentation: FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)
Write APPLY statements that return a given data set based on supplied data (Microsoft Documentation: Writing Transact-SQL Statements)

Query data by using table expressions

Identify basic components of table expressions (Microsoft Documentation: WITH common_table_expression (Transact-SQL))
Define usage differences between table expressions and temporary tables (Microsoft Documentation: WITH common_table_expression (Transact-SQL), Temporary tables in Synapse SQL pool)
Construct recursive table expressions to meet business requirements (Microsoft Documentation: Data Points: Common Table Expressions, WITH common_table_expression (Transact-SQL))

Group and pivot data by using queries

Use windowing functions to the group and rank the results of a query (Microsoft Documentation: SELECT – OVER Clause (Transact-SQL))
Distinguish between using windowing functions and GROUP BY (Microsoft Documentation: Introduction to Stream Analytics windowing functions)
Construct complex GROUP BY clauses using GROUPING SETS, and CUBE (Microsoft Documentation: SELECT – GROUP BY- Transact-SQL)
Construct PIVOT and UNPIVOT statements to return desired results based on supplied data (Microsoft Documentation: FROM – Using PIVOT and UNPIVOT)
Determine the impact of NULL values in PIVOT and UNPIVOT queries (Microsoft Documentation: FROM – Using PIVOT and UNPIVOT)

Query temporal data and non-relational data

Query historic data by using temporal tables, query and output JSON data, and query and output XML data (Microsoft Documentation: Format Query Results as JSON with FOR JSON (SQL Server), FOR XML (SQL Server), Querying data in a system-versioned temporal table)

Domain 3- Program databases by using Transact-SQL (25–30%)

Create database programmability objects by using Transact-SQL

Create stored procedures, table-valued and scalar-valued user-defined functions, triggers, and views (Microsoft Documentation: Create User-defined Functions (Database Engine), CREATE FUNCTION (Transact-SQL), User-Defined Functions)
Implement input and output parameters in stored procedures (Microsoft Documentation: Configuring parameters and parameter data types, Using a stored procedure with output parameters)
Identify whether to use scalar-valued or table-valued functions (Microsoft Documentation: Scalar UDF Inlining)
Distinguish between deterministic and non-deterministic functions (Microsoft Documentation: Deterministic and Nondeterministic Functions)
Create indexed views (Microsoft Documentation: Create Indexed Views)

Implement error handling and transactions

Determine the results of Data Definition Language (DDL) statements based on transaction control statements (Microsoft Documentation: Transact-SQL statements)
Implement TRY…CATCH error handling with Transact-SQL (Microsoft Documentation: TRY…CATCH (Transact-SQL), ERROR_MESSAGE (Transact-SQL))
Generate error messages with THROW and RAISERROR (Microsoft Documentation: RAISERROR (Transact-SQL), THROW (Transact-SQL))
Implement transaction control in conjunction with error handling in stored procedures (Microsoft Documentation: TRY…CATCH (Transact-SQL), Database engine errors)

Implement data types and NULLs

Evaluate results of data type conversions (Microsoft Documentation: Data type conversion (Database Engine), CAST and CONVERT (Transact-SQL))
Determine proper data types for given data elements or table columns (Microsoft Documentation: data types and field properties, DataColumn.DataType Property)
Identify locations of implicit data type conversions in queries (Microsoft Documentation: Data type conversion (Database Engine))
Determine the correct results of joins and functions in the presence of NULL values (Microsoft Documentation: Joins (SQL Server), Handling null values)
Identify the proper usage of ISNULL and COALESCE functions (Microsoft Documentation: COALESCE (Transact-SQL))

Preparatory Guide for Microsoft 70-761 Exam

The key to successfully pass an exam is by preparing right. Preparations demand consistency and determination. We present you our specially curated preparatory guide to help you achieve the Microsoft 70-761 Exam Certification in the very first attempt. Let’s discuss step by step.

Step1- Refer the Official Exam Guide

Firstly, you need to go through the official guide of Microsoft 70-761 exam. Familiarise yourself with all the objectives and course domains of the exam. Devote enough time to each topic and have in depth knowledge of the subject.

Step 2- Devise a Study Plan

The next step is to strategize a study plan for your preparations. Following your plan will help you remain consistent and also help in avoiding distractions. Remember the key to excel is to start early, hence begin with your preparations and stay motivated.

Step 3- Books – your Best friends

Books are the most important ingredient of preparation. There are a vast variety of books available in the market which you can use to prepare for the Microsoft 70-761 Exam. Therefore, our suggestion to you would be to find books for the Microsoft 70-761 exam from genuine sources.

We recommend you to refer the Exam Ref 70-761 Querying Data with Transact-SQL by Microsoft Press Store. This book focuses on the critical-thinking and decision-making acumen needed for success at the MCSA level.

Additionally you can also refer the Hands on Guide for SQL Data manipulation.

Step 4- Learn from Online Resources

There are plenty of Online Resources Available online for the preparation of your Microsoft 70-761 Exam. Each resource tries to offer you clarity about your exam domains. We recommend you to refer the official Microsoft Documentation. Also, you can refer the following documentation for further learning-

Database Functions

Transaction Statements

Tutorial on basic T-SQL operations

Subquery Fundamentals

Step 5- Go for a Training Course

Microsoft provides its own Training Courses for the preparation of this Exam. We know that being a practical exam, it requires hands on training. To solve this issue Microsoft has come up with training options in two modes. Choose the one that best suits you.

Instructor-Led Training- Microsoft instructor-led training is similar to the traditional classroom training setting. You can in-depth understanding of the exam course and hands-on experience.
Online Training– Online Training offered by Microsoft is an ultimate resource for you to prepare. Such training helps you learn on your own schedule, at your own pace, and in your own place.

Step 6- Practice Tests

Practice tests are really significant to check your preparations. They provide you with insights about your strengths and weaknesses. Strengthening your weakness will help you ace the exam. Attempt as many practice papers as you can and try to perform better than the previous one each time. There are plenty of practice papers available online. Make sure you choose the right and authentic ones. Start practicing for Microsoft 70-761 Now!

Step 7- Join a community

Online forums and study groups are really beneficial while preparing for the exam. Study groups help you stay connected with the other people who are also going through the same journey as yours. Moreover, you can also ask a question related to the topic you’re having difficulty with. You get to learn from the expertise of your peers and therefore joining a community is an important step while preparing.

Your best career opportunity is just a few tests away. Start preparing for your Microsoft 70-761: Querying Data with Transact-SQL certification exam now!

Querying Data with Transact-SQL

Microsoft Exam 70-761–helps demonstrate your real-world mastery of SQL Server 2016 Transact-SQL data management, queries, and database programming. Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical-thinking and decision-making acumen needed for success at the MCSA level.

The Microsoft Exam 70-7610 focuses on the expertise measured by these objectives Filter, sort, join, aggregate, and modify data, Use subqueries, table expressions, grouping sets, and pivoting, Query temporal and non-relational data, and output XML or JSON, Create views, user-defined functions, and stored procedures and Implement error handling, transactions, data types, and nulls.

Microsoft Exam 70-761 Table of Content

Chapter 1 – Manage data with Transact-SQL

Transact-SQL (T-SQL) is the main language used to manage and manipulate data in Microsoft SQL Server and Azure SQL Database. This chapter focuses on managing data with T-SQL and it covers the elements of the SELECT statement, how to combine data from multiple tables with set operators and joins, use of built-in functions, and how to modify data.

Chapter 2 – Query data with advanced Transact-SQL components

This chapter covers a number of T-SQL components that allow you to manipulate and analyze data. The module elaborates how to nest queries and use the APPLY operator, work with table expressions, apply data analysis calculations with grouping, pivoting, and windowing, query historical data from temporal tables, and query and output XML and JSON data.

Chapter 3 – Program databases by using Transact-SQL

The chapter covers programmability features in T-SQL. It starts with programmability objects like views, user-defined functions, and stored procedures. It then covers handling errors with the TRY-CATCH construct, and working with transactions. The chapter completes with coverage of handling of data types and treatment of NULLs.