Microsoft 070-761 Exam : Querying Data with Transact-SQL

Exam Code: 070-761
Exam Name: Querying Data with Transact-SQL
Updated: Nov 05, 2026
Q & A: 205 Questions and Answers

Microsoft 070-761 Exam Syllabus Topics:

Section

Weight

Objectives

Topic 1: Program databases by using Transact-SQL

25-30%

- Create database programmability objects by using Transact-SQL

1. Create stored procedures
2. Create views
3. Create user-defined functions

- Implement data types and NULLs

1. Use implicit and explicit conversions
2. Choose appropriate data types
3. Handle NULL values

- Implement error handling and transactions

1. Implement transactions
2. Use TRY...CATCH
3. Use THROW and RAISERROR
4. Manage transaction isolation

Topic 2: Manage data with Transact-SQL

40-45%

- Modify data

1. Write UPDATE statements
2. Write DELETE statements
3. Use the OUTPUT statement
4. Write INSERT statements

- Implement functions and aggregate data

1. Use scalar functions
2. Use GROUP BY and HAVING
3. Use ranking functions
4. Use aggregate functions

- Query multiple tables by using joins

1. Write queries using cross joins
2. Handle NULL values in join columns
3. Write queries using self joins
4. Write queries using inner joins
5. Write queries using outer joins

- Create Transact-SQL SELECT queries

1. Identify proper SELECT query structure
2. Write queries to satisfy business requirements
3. Identify queries that return expected results based on table structure or data
4. Distinguish between UNION and UNION ALL behavior
5. Construct results from multiple queries using set operators

Topic 3: Query data with advanced Transact-SQL components

30-35%

- Group and pivot data by using queries

1. Use ROLLUP and CUBE
2. Use grouping sets
3. Use PIVOT and UNPIVOT

- Query data by using subqueries and APPLY

1. Use correlated and non-correlated subqueries
2. Use APPLY
3. Use EXISTS and NOT EXISTS

- Query temporal and non-relational data

1. Query XML data
2. Query JSON data
3. Output results as XML or JSON
4. Query temporal tables

- Query data by using table expressions

1. Use derived tables
2. Use inline table-valued functions
3. Use common table expressions
4. Use views

070-761 Exam FAQ: Answers for Busy Candidates

How much does the 070-761 exam cost to take, and what's a passing score?

Budget $165 USD for registration, and aim for 700/1000 to pass. Since the fee resets to full price on every retake, the cheapest strategy is preparation: work the 205 practice questions from VerifiedDumps until the pass mark becomes your floor, not your ceiling.

What is the Microsoft Querying Data with Transact-SQL and why does it matter?

The Microsoft Querying Data with Transact-SQL leads to the MCP certification at the Associate level. Employers notice it in interviews — it's shorthand for proven ability, and it often decides who reaches the final round. It also pairs with related credentials like MCSA: SQL 2016 Database Development if you plan to keep climbing.

Is there a free trial of the 070-761 product?

There is. The free PDF demo shows exactly what you're buying — real questions, real answer quality. After purchase, 365 days of free updates keep the material current, and renewing an expired update period costs 50% of the price through your member zone.

What are the main topics in the 070-761 exam?

The Microsoft Querying Data with Transact-SQL blueprint spans 3 domains, including Manage data with Transact-SQL (40-45%), Query data with advanced Transact-SQL components (30-35%), Program databases by using Transact-SQL (25-30%). Those percentages are the vendor telling you where the points are — spend your limited study hours accordingly. The complete outline above breaks down every subtopic.

What do I need before registering for the 070-761 exam?

No formal prerequisite exam. Intended for SQL Server database administrators, system engineers, and developers with two or more years of experience writing Transact-SQL queries. Exam 70-762 was also required to earn MCSA: SQL 2016 Database Development. Because eligibility rules change over time, treat the vendor as the final authority before paying any fees.

How long is the 070-761 exam, and how many questions will I face?

The exam packs 40-60 questions into 120 minutes. Busy professionals, take note: the time pressure is real but trainable. Set a pace target per question, practice skipping without guilt, and run timed mock exams in the VerifiedDumps engine until finishing on time becomes a habit rather than a hope.

What if I fail the exam, and how is delivery handled?

Delivery takes about a minute — payment confirms, files unlock for instant download, an email copy follows, and you can install on unlimited computers; our responsive support team steps in if 2 hours pass with nothing received. If the exam itself goes badly, the 100% Money Back Guarantee applies: take the corresponding 070-761 exam within 60 days of purchase, and if you fail, submit a scanned enrollment slip plus the official Score Report PDF within 2 days of the exam — refunds are processed within 7 days. The conditions: no exams within 3 days of purchase, the candidate name must match the payer's, and free or expired products are excluded. Alternatively, exchange for two equal-value exam products at no cost while keeping your updates.

Microsoft Querying Data with Transact-SQL Sample Questions:

Question #1

You are developing a mobile app to manage meetups. The app allows for users to view the 25 closest people with similar interests. You have a table that contains records for approximately two million people. You create the table by running the following Transact-SQL statement:

You create the following table valued function to generate lists of people:

You need to build a report that shows meetings with at least two people only.
What should you use?

A. LEFT OUTER JOIN
B. OUTER APPLY
C. PIVOT
D. CROSS APPLY

Reveal Solution Discussion 0

Correct Answer: D 🗳️

Explanation: Only visible for VerifiedDumps members. You can sign-up / login (it's free).

Question #2

You have the following Transact-SQL query:

What type of functions are used in the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Reveal Solution Discussion 0

Correct Answer:

Explanation

Box 1: Scalar
The return value of a function can either be a scalar (single) value or a table.
Box 2: Table-Valued
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
References:
https://msdn.microsoft.com/en-us/library/ms186755.aspx
https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

Question #3

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are building a stored procedure that will be used by hundreds of users concurrently.
You need to store rows that will be processed later by the stored procedure. The object that stores the rows must meet the following requirements:
* Be indexable
* Contain up-to-date statistics
* Be able to scale between 10 and 100,000 rows
The solution must prevent users from accessing one another's data.
Solution: You create a local temporary table in the stored procedure.
Does this meet the goal?

A. No
B. Yes

Reveal Solution Discussion 0

Correct Answer: A 🗳️

Question #4

A. Option C
B. Option B
C. Option A
D. Option D

Reveal Solution Discussion 0

Correct Answer: D 🗳️

Explanation: Only visible for VerifiedDumps members. You can sign-up / login (it's free).

Question #5

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
Start of repeated scenario
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You review the Employee table and make the following observations:
* Every record has a value in the ManagerID except for the Chief Executive Officer (CEO).
* The FirstName and MiddleName columns contain null values for some records.
* The valid values for the Title column are Sales Representative, Manager, and CEO.
You review the SalesSummary table and make the following observations:
* The ProductCode column contains two parts: The first five digits represent a product code, and the last seven digits represent the unit price. The unit price uses the following pattern: ####.##.
* You observe that for many records, the unit price portion of the ProductCode column contains values.
* The RegionCode column contains NULL for some records.
* Sales data is only recorded for sales representatives.
You are developing a series of reports and procedures to support the business. Details for each report or procedure follow.
Sales Summary report: This report aggregates data by year and quarter. The report must resemble the following table.

Sales Manager report: This report lists each sales manager and the total sales amount for all employees that report to the sales manager.
Sales by Region report: This report lists the total sales amount by employee and by region. The report must include the following columns: EmployeeCode, MiddleName, LastName, RegionCode, and SalesAmount.
If MiddleName is NULL, FirstName must be displayed.
If both FirstName and MiddleName have null values, the world Unknown must be displayed. If RegionCode is NULL, the word Unknown must be displayed.
Report1: This report joins data from SalesSummary with the Employee table and other tables. You plan to create an object to support Report1. The object has the following requirements:
* be joinable with the SELECT statement that supplies data for the report
* can be used multiple times with the SELECT statement for the report
* be usable only with the SELECT statement for the report
* not be saved as a permanent object
Report2: This report joins data from SalesSummary with the Employee table and other tables. You plan to create an object to support Report1. The object has the following requirements:
* be joinable with the SELECT statement that supplies data for the report
* can be used multiple times for this report and other reports
* accept parameters
* be saved as a permanent object
Sales Hierarchy report. This report aggregates rows, creates subtotal rows, and super-aggregates rows over the SalesAmount column in a single result-set. The report uses SaleYear, SaleQuarter, and SaleMonth columns as a hierarchy. The result set must not contain a grand total or cross-tabulation aggregate rows.
Current Price Stored Procedure: This stored procedure must return the unit price for a product when a product code is supplied. The unit price must include a dollar sign at the beginning. In addition, the unit price must contain a comma every three digits to the left of the decimal point, and must display two digits to the left of the decimal point. The stored procedure must not throw errors, even if the product code contains invalid data.
End of Repeated Scenario
You need to create the stored procedure that returns the current unit price.
How should you complete the stored procedure definition? To answer, select the appropriate Transact-SQL segments in the answer area.