Querying Data with Transact-SQL – Microsoft: 70-761 exam. Share the latest 70-761 dumps and Practice test questions for free. Real and effective exam questions and answers. 70-761 pdf online Download, 70-761 YouTube video online learning to improve skills! Examthings share 30 Practice test questions for FREE! Get the full 70-761 exam dumps: https://www.leads4pass.com/70-761.html (Total questions:182 Q&A)
[PDF] Free Microsoft MCSA 70-761 pdf dumps download from Google Drive: https://drive.google.com/open?id=1RH2MOMUVVB6XAq8a_qtntBzPuVkYTB_m
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1AwBFPqkvdpJBfxdZ3nGjtkHQZYdBsRVz
Exam 70-761: Querying Data with Transact-SQL – Microsoft: https://www.microsoft.com/en-us/learning/exam-70-761.aspx
Latest effective Microsoft MCSA 70-761 Exam Practice Tests
QUESTION 1
You need to create a table named MiscellaneousPayment that meets the following requirements:
Which Transact-SQL statement should you run? 
A. B. C. D.
Correct Answer: D
Incorrect Answers:
A: For column Reason we must use nvarchar, not varchar, as multilingual values must be supported.
B: We cannot use INT for the Id column as new values must be automatically generated.
C: For column Reason we must use nvarchar, not varchar, as multilingual values must be supported.
Note: Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is the
choice. Varchar stores ASCII data and should be your data type of choice for normal use.
References: https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql
QUESTION 2
HOTSPOT
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 thescenario is
exactly the same in each question in this series.
You are developing a database to track customer orders. The database contains the following tables: Sales.Customers,
Sales.Orders, and Sales.OrderLines. The following table describes the columns in Sales.Customers.
The following table describes the columns in Sales.OrderLines. 
You need to create a database object that calculates the total price of an order including the sales tax. The database
object must meet the following requirements:
Reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated execution.
Return a value.
Be callable from a SELECT statement.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in
the answer area.
Hot Area: 
Box 1: FUNCTION
To be able to return a value we should use a scalar function.
CREATE FUNCTION creates a user-defined function in SQL Server and Azure SQL Database. The return value can
either be a scalar (single) value or a table.
Box 2: RETURNS decimal(18,2)
Use the same data format as used in the UnitPrice column.
Box 3: BEGIN
Transact-SQL Scalar Function Syntax include the BEGIN ..END construct.
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,…n ]
]
)
RETURNSreturn_data_type
[ WITH [ ,…n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
Box 4: @OrderPrice * @CalculatedTaxRate
Calculate the price including tax.
Box 5: END
Transact-SQL Scalar Function Syntax include theBEGIN ..END construct.
References: https://msdn.microsoft.com/en-us/library/ms186755.aspx
QUESTION 3
You have a database that stored information about servers and application errors. The database contains the following
tables. Servers
You need to return all error log messages and the server where the error occurs most often. Which Transact-SQL
statement should you run? 
C. D.
Correct Answer: C
QUESTION 4
You need to create a database object that meets the following requirements:
accepts a product identifies as input
calculates the total quantity of a specific product, including quantity on hand and quantity on order
caches and reuses execution plan
returns a value
can be called from within a SELECT statement
can be used in a JOIN clause
What should you create?
A. a temporary table that has a columnstore index
B. a user-defined table-valued function
C. a memory-optimized table that has updated statistics
D. a natively-complied stored procedure that has an OUTPUT parameter
Correct Answer: B
A table-valued user-defined function can also replace stored procedures that return a single result set. The table
returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored
procedures that return result sets cannot.
References: https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx
QUESTION 5
You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)
You need to create a list of all customers and the date that the customer placed their last order. For customers who
have not placed orders, you must substitute a zero for the order ID and 01/01/1990 for the date. Which Transact-SQL
statement should you run? 
A. B. C. D.
Correct Answer: A
COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not
evaluate to NULL. References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql
QUESTION 6
You have a database that includes the following tables.
HumanResources.Employee
The HumanResources.Employee table has 2,500 rows, and the Sales.SalesPerson table has 2,000 rows. You review
the following Transact-SQL statement: 
You need to determine the performance impact of the query.
How many times will a lookup occur on the primary key index on the Sales.SalesPerson table?
A. 200
B. 2,000
C. 2,500
D. 5,500
Correct Answer: C
QUESTION 7
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 have a database that tracks orders and deliveries for customers in North America. The database contains the
following tables:
Sales.Customers
The company\\’s development team is designing a customer directory application. The application must list customers
by the area code of their phone number. The area code is defined as the first three characters of the phone number.
The main page of the application will be based on an indexed view that contains the area and phone number for all
customers.
You need to return the area code from the PhoneNumber field.
Solution: You run the following Transact-SQL statement: 
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
As the result of the function will be used in an indexed view we should use schemabinding. References:
https://sqlstudies.com/2014/08/06/schemabinding-what-why/
QUESTION 8
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 table variable in the stored procedure.
Does this meet the goal?
A.
Yes
B.
No
Correct Answer: B
QUESTION 9
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 have a database that includes the tables shown in the exhibit (Click the Exhibit button.)
You need to create a Transact-SQL query that returns the following information:
the customer number
the customer contact name
the date the order was placed, with a name of DateofOrder
a column named Salesperson, formatted with the employee first name, a space, and the employee last name
orders for customers where the employee identifier equals 4
The output must be sorted by order date, with the newest orders first. The solution must return only the most recent
order for each customer.
Solution: You run the following Transact-SQL statement: 
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
We should use a WHERE clause, not a HAVING clause. The HAVING clause would refer to aggregate data.
QUESTION 10
DRAG DROP
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 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 query for the Sales Managers report.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-
SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
Select and Place: 
From scenario: Sales Manager report: This report lists each sales manager and the total sales amount for all employees
that report to the sales manager.
Box 1:..WHERE Title=\\’Sales representative\\’
The valid values for the Title column are Sales Representative manager, and CEO.
First we define the CTE expression.
Note: A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution
scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived
table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be
self-referencing and can be referenced multiple times in the same query.
Box 2:
Use the CTE expression one time.
Box 3: UNION
Box 4:
Use the CTE expression a second time.
References: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
QUESTION 11
SIMULATION
You create a table named Sales.Orders by running the following Transact-SQL statement:
You need to write a query that removes orders from the table that have a Status of Canceled.
Construct the query using the following guidelines:
use one-part column names and two-part table names
use single quotes around literal values
do not use functions
do not surround object names with square brackets
do not use variables
do not use aliases for column names and table names 
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that
resolves the problem and meets the stated goals or requirements. You can add code within the code that has been
provided as well as below it. 
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character
position.
Correct Answer: explanation
Please see explanation
1. DELETE from sales.orders where status=\\’Canceled\\’
Note: On line 1 change calceled to Canceled
Example: Using the WHERE clause to delete a set of rows
The following example deletes all rows from the ProductCostHistory table in the AdventureWorks2012 database in
which the value in the StandardCost column is more than 1000.00.
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql
QUESTION 12
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 create a table named Customer by running the following Transact-SQL statement:
You need to ensure that both records are inserted or neither record is inserted. Solution: You run the following Transact-
SQL statement: 
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
As there are two separate INSERT INTO statements we cannot ensure that both or neither records are inserted.
QUESTION 13
HOTSPOT
You have two tables as shown in the following image:
You need to analyze the following query. (Line numbers are included for reference only.) 
Use the drop-down menus to select the answer choice that completes each statement based on the information
presented in the graphic. NOTE: Each correct selection is worth one point.
Hot Area: 
To compare char(5) and nchar(5) an implicit conversion has to take place.
Explicit conversions use the CAST or CONVERT functions, as in line number 6.
References: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine#implicit-and-
explicit-conversion
QUESTION 14
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 have a table named Products that stores information about products your company sells. The table has a column
named ListPrice that stores retail pricing information for products.
Some products are used only internally by the company. Records for these products are maintained in the Products
table for inventory purposes. The price for each of these products is $0.00. Customers are not permitted to order these
products.
You need to increase the list price for products that cost less than $100 by 10 percent. You must only increase pricing
for products that customers are permitted to order.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
Mathematical equation will only return 10 % of the value.
QUESTION 15
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 creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a nonclustered index on the primary key column that does NOT include columns.
Does this meet the goal?
A. Yes
B. No
Correct Answer: A
References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-
described?view=sql-server-2017
QUESTION 16
You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)
You need to create a list of all customers and the date that the customer placed their last order. For customers who
have not placed orders, you must substitute 01/01/1990 for the date. Which Transact-SQL statement should you run? 
A. B. C. D.
Correct Answer: A
QUESTION 17
DRAG DROP
You have a database that stored information about servers and application errors. The database contains the following
tables.
Servers
Errors
You are building a webpage that shows the three most common errors for each server.
You need to return the data for the webpage.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to
the correct location. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to
drag
the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place: 
QUESTION 18
DRAG DROP
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 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 a query to return the data for the Sales Summary report.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate
Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct
order.
Select and Place: 
Use two CTE expressions, one for salesYear and one for SalesQuarter, and combine them with a SELECT statement.
Note: A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution
scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived
table in that it is not stored as an object and lasts only for the duration of the query.
References:https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
QUESTION 19
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 have a table that was created by running the following Transact-SQL statement:
TotalUnitPrice is calculated by using the following formula:
TotalUnitPrice = UnitPrice * (UnitsInStock + UnitsOnOrder)
You need to ensure that the value returned for TotalUnitPrice for ProductB is equal to 600.00.
Solution: You run the following Transact-SQL statement: 
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
ISNULL ( check_expression , replacement_value )
Arguments:
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly
convertible to the type of check_expresssion.
References: https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql
QUESTION 20
SIMULATION
You work for an organization that monitors seismic activity around volcanos. You have a table named GroundSensors.
The table stored data collected from seismic sensors. It includes the columns describes in the following table:
The database also contains a scalar value function named NearestMountain that accepts a parameter of type
geography and returns the name of the mountain that is nearest to the sensor.
You need to create a query that shows the average of the normalized readings from the sensors for each mountain. The
query must meet the following requirements:
Return the average normalized readings named AverageReading.
Return the nearest mountain name named Mountain.
Do not return any other columns.
Exclude sensors for which no normalized reading exists.
Construct the query using the following guidelines:
Use one part names to reference tables, columns and functions.
Do not use parentheses unless required.
Define column headings using the AS keyword.
Do not surround object names with square brackets. 
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that
resolves the problem and meets the stated goals or requirements. You can add code within the code that has been
provided as well as below it. 
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character
position.
A. Check the answer in explanation.
Correct Answer: A
QUESTION 21
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may
be correct for more than one question in the series. Each question is independent of the other questions in this series.
Information and details provided in a question apply only to that question.
You have a database that contains several connected tables. The tables contain sales data for customers in the United
States only.
You have the following partial query for the database. (Line numbers are included for reference only.)
You need to complete the query to generate the output shown in the following table. 
Which statement clause should you add at line 3?
A. GROUP BY
B. MERGE
C. GROUP BY ROLLUP
D. LEFT JOIN
E. GROUP BY CUBE
F. CROSS JOIN
G. PIVOT
H. UNPIVOT
Correct Answer: A
QUESTION 22
You have a database named DB1 that contains two tables named Sales.Customers and Sales.CustomerTransaction.
Sales.CustomerTransactions has a foreign key relationship to column named CustomerID in Sales.Customers.
You need to recommend a query that returns the number of customers who never completed a transaction.
Which query should you recommend?
A. B. C. D.
Correct Answer: A
Incorrect Answers:
B: The count should be on the Cust instance of Sales.Customers as it is to the right side of the join.
C: Need a WHERE statement with an IS NULL clause.
D: Must use a LEFT JOIN to obtain the NULL values.
References: https://technet.microsoft.com/en-us/library/ms190014(v=sql.105).aspx
QUESTION 23
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 create a table named Products by running the following Transact-SQL statement:
You need to modify the stored procedure to meet the following new requirements:Insert product records as a single unit of work.
Return error number 51000 when a product fails to insert into the database.
If a product record insert operation fails, the product information must not be permanently written to the database.
Solution: You run the following Transact-SQL statement: 
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
With X_ABORT ON the INSERT INTO statement and the transaction will be rolled back when an error is raised, it would
then not be possible to ROLLBACK it again in the IF XACT_STATE() O ROLLBACK TRANSACTION statement.
Note: A transaction is correctly defined for the INSERT INTO ..VALUES statement, and if there is an error in the
transaction it will be caughtant he transaction will be rolled back, finally an error 51000 will be raised.
Note: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is
terminated and rolled back. XACT_STATE is a scalar function thatreports the user transaction state of a current running
request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is
capable of being committed.
The states of XACT_STATE are:
0 There is no active user transaction for the current request.
1 The current request has an active user transaction. The request can perform any actions, including writing data and
committing the transaction.
2 The current request has an active user transaction, but an error hasoccurred that has caused the transaction to be
classified as an uncommittable transaction.
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ms189797.aspx
QUESTION 24
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 have a database that tracks orders and deliveries for customers in North America. The database contains the
following tables:
Sales.Customers
Application.Cities 
Sales.CustomerCategories 
The company\\’s development team is designing a customer directory application. The application must list customers
by the area code of their phone number. The area code is defined as the first three characters of the phone number.
The
main page of the application will be based on an indexed view that contains the area and phone number for all
customers.
You need to return the area code from the PhoneNumber field.
Solution: You run the following Transact-SQL statement: 
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
The function should return nvarchar(10) and not a TABLE.
References: https://sqlstudies.com/2014/08/06/schemabinding-what-why/
QUESTION 25
DRAG DROP
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.
You are developing a database to track customer orders. The database contains the following tables: Sales.Customers,
Sales.Orders, and Sales.OrderLines. The following table describes the columns in Sales.Customers.
The following table describes the columns in Sales.OrderLines.
You need to create a stored procedure that inserts data into the Customers table. The stored procedure must meet the
following requirements:
Data changes occur as a single unit of work.
Data modifications that are successful are committed and a value of 0 is returned.
Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a value of -1 is
returned.
The stored procedure uses a built-it scalar function to evaluate the current condition of data modifications.
The entire unit of work is terminated and rolled back if a run-time error occurs during execution of the stored procedure.
How should complete the stored procedure definition? To answer, drag the appropriate Transact-SQL segments to the
correct targets. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag
the
split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place: 
Box 1: XACT_ABORT
XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL
statement raises a run-time error. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-timeerror,
the
entire transaction is terminated and rolled back.
Box 2: COMMIT
Commit the transaction.
Box 3: XACT_STATE
Box 4: ROLLBACK
Rollback the transaction
Box 5: THROW
THROW raises an exception and the severity is set to 16.
Requirement: Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a
value of -1 is returned.
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ee677615.aspx
QUESTION 26
DRAG DROP
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.
You query a database that includes two tables: Project and Task. The Project table includes the following columns:
When running an operation, you updated a column named EndTime for several records in the Project table, but updates
to the corresponding task records in the Task table failed.
You need to synchronize the value of the EndTime column in the Task table with the value of the EndTime column in
the project table. The solution must meet the following requirements:
If the EndTime column has a value, make no changes to the record.
If the value of the EndTime column is null and the corresponding project record is marked as completed, update the
record with the project finish time.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-
SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
Select and Place: 
Box 1: UPDATE T SET T.EndTime = P.EndTime
We are updating the EndTime column in the Task table.
Box 2: FROM Task AS T
Where are updating the task table.
Box 3:INNER JOIN Project AS P on T.ProjectID = P.ProjectID
We join with the Project table (on the ProjectID columnID column).
Box 4: WHERE P.EndTime is NOT NULL AND T.EndTime is NULL
We select the columns in the Task Table where the EndTime column in the Project table has a value (NOT NULL),but
where it is NULL in the Task Table.
References: https://msdn.microsoft.com/en-us/library/ms177523.aspx
QUESTION 27
You have a database named DB1 that contains a temporal table named Sales.Customers.
You need to create a query that returns the credit limit that was available to each customer in DB1 at the beginning of
2017.
Which query should you execute?
Correct Answer: B
QUESTION 28
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 creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a nonclustered index on the primary key column that includes the bookmark lookup columns.
Does this meet the goal?
A. Yes
B. No
Correct Answer: B
QUESTION 29
You create a table by running the following Transact-SQL statement:
You need to view all customer data.
Which Transact-SQL statement should you run? 
A. B. C. D. E. F. G. H.
Correct Answer: B
The FOR SYSTEM_TIME ALL clause returns all the row versions from both the Temporal and History table.
References: https://msdn.microsoft.com/en-us/library/dn935015.aspx
QUESTION 30
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 create a table named Customer by running the following Transact-SQL statement:
You must insert the following data into the Customer table: 
You need to ensure that both records are inserted or neither record is inserted. Solution: You run the following Transact-
SQL statement: 
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
As there are two separate INSERT INTO statements we cannot ensure that both or neither records are inserted.
We offer more ways to make it easier for everyone to learn, and YouTube is the best tool in the video.Follow channels: https://www.youtube.com/@lead4pass116 get more useful exam content.
Latest Microsoft MCSA 70-761 YouTube videos:
Share 30 of the latest and effective 70-761 exam dumps and Practice test questions for free, 100% real and effective exam questions and answers! Get the full 70-761 dumps:https://www.leads4pass.com/70-761.html
(Total questions:182 Q&A)
[PDF] Free Microsoft MCSA 70-761 pdf dumps download from Google Drive: https://drive.google.com/open?id=1RH2MOMUVVB6XAq8a_qtntBzPuVkYTB_m
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1AwBFPqkvdpJBfxdZ3nGjtkHQZYdBsRVz
leads4pass Promo Code 12% Off
related: https://www.dumpscertification.com/microsoft-application-lifecycle-management-070-496-dumps/