Microsoft 70-761 Exam Questions and Answers 2021

NEW QUESTION 1
You have two tables named UserLogin and Employee respectively.
You need to create a Transact-SQL script that meets the following requirements:
- The script must update the value of the IsDeleted column for the UserLogin table to 1 if the value of the Id column for the UserLogin table is equal to 1.
- The script must update the value of the IsDeleted column of the Employee table to 1 if the value of the Id column is equal to 1 for the Employee table when an update to the UserLogin table throws an error.
- The error message “No tables updated!” must be produced when an update to the Employee table throws an error.
Which five 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.

Answer:

Explanation:

NEW QUESTION 2
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

Answer: B

NEW QUESTION 3
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 create a table by running the following Transact-SQL statement:

You are developing a report that aggregates customer data only for the year 2014. The report requires that the data be denormalized.
You need to return the data for the report. Which Transact-SQL statement should you run?

• A. Option A
• B. Option B
• C. Option C
• D. Option D
• E. Option E
• F. Option F
• G. Option G
• H. Option H

Answer: G

NEW QUESTION 4
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 on this series.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:

Details for the Application.Cities table are shown in the following table:

Details for the Sales.CustomerCategories table are shown in the following table:

You discover an application bug that impacts customer data for records created on or after January 1, 2014. In order to fix the data impacted by the bug, application programmers require a report that contains customer data as it existed on December 31, 2013.
You need to provide the query for the report. Which Transact-SQL statement should you use?

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

Answer: D

Explanation: The datetime datetype defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
The DATEFROMPARTS function returns a date value for the specified year, month, and day.

NEW QUESTION 5
You have a database named DB1 that contains a table named HR.Employees. HR.Employees contains two columns named ManagerID and EmployeeID. ManagerID refers to EmployeeID.
You need to create a query that returns a list of all employees, the manager of each employee, and the numerical level of each employee in your organization’s hierarchy.
Which five statements should you add to the query in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

Answer:

Explanation: References:
https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-p

NEW QUESTION 6
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 have the following stored procedure:

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

Answer: A

Explanation: If the INSERT INTO statement raises an error, the statement will be caught and an error 51000 will be thrown. In this case no records will have been inserted.
Note:
You can implement error handling for the INSERT statement by specifying the statement in a TRY…CATCH construct.
If an INSERT statement violates a constraint or rule, or if it has a value incompatible with the data type of the column, the statement fails and an error message is returned.
References: https://msdn.microsoft.com/en-us/library/ms174335.aspx

NEW QUESTION 7
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.

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

NEW 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.
A database has two tables as shown in the following database diagram:

You need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents. The query must return the following columns:

Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.
This is not what is required in this scenario.
References: https://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx

NEW QUESTION 9
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 2021.
Which query should you execute?

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

Answer: B

NEW QUESTION 10
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 on this series.
You have a database that tracks orders and deliveries for customers in North America. System versioning is enabled for all tables. The database contains the Sales.Customers, Application.Cities, and Sales.CustomerCategories tables.
Details for the Sales.Customers table are shown in the following table:

Details for the Application.Cities table are shown in the following table:

Details for the Sales.CustomerCategories table are shown in the following table:

You are creating a report to show when the first customer account was opened in each city. The report contains a line chart with the following characteristics:
- The chart contains a data point for each city, with lines connecting the points.
- The X axis contains the position that the city occupies relative to other cities.
- The Y axis contains the date that the first account in any city was opened. An example chart is shown below for five cities:

During a sales promotion, customers from various cities open new accounts on the same date. You need to write a query that returns the data for the chart.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. 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.

Answer:

Explanation: Box 1: RANK() OVER
RANK returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5).

NEW QUESTION 11
You have a table named HumanResources.Department that was created with the query shown in the exhibit. (Click the Exhibit button.)

You need to query temporal data in the table.
In the table below, identify the Transact-SQL segments that must be used to retrieve the appropriate data. NOTE: Make only one selection in each column.

Answer:

Explanation: AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.
CONTAINED IN: If you search for non-current row versions only, we recommend you to use CONTAINED IN as it works only with the history table and will yield the best query performance.

NEW QUESTION 12
You develop and deploy a project management application. The application uses a Microsoft SQL Server database to store data. You are developing a software bug tracking add-on for the application.
The add-on must meet the following requirements:
Allow case sensitive searches for product.
Filter search results based on exact text in the description.
Support multibyte Unicode characters.
You run the following Transact-SQL statement:

Users report that searches for the product Salt also return results for the product salt. You need to ensure that the query returns the correct results.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: References:
https://stackoverflow.com/questions/1831105/how-to-do-a-case-sensitive-search-in-where-clause-im-using-sql-s

NEW QUESTION 13
You have a database that contains a table named Products in the sales schema. The table was created by running the following Transact-SQL statement:

The table includes the data shown below:

You are developing a report that displays the following values and column headers in the order listed below:
• average price of a product named Average
• the smallest number of products in stocK-named LowestNumber
• the highest product price named HighestPrice
You need to write a query to return the results for the report The query must meet the following requirements: You need to write a query to return the results for the report. The query must meet the following requirements:
• Use built-in, aggregate anfa*mathematical functions.
• Use two-part names and tables.
• Use the table alias to qualify column names.
• Define the alias for all fields by using the AS keyword.
• Use the first letter of the table name as the table alias.
• Do not use the Row_number function.
• Do not surround object names with square brackets.
• Do not use variables.
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.

Answer:

Explanation: TRY_Convert

NEW QUESTION 14
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 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.Orders.

The following table describes the columns in Sales.OrderLines.

End of repeated scenario
You need to create a common table expression (CTE) that returns the total number of orders per year for each customer.
Which five 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.

Answer:

Explanation:

NEW QUESTION 15
You need to create an indexed view that requires logic statements to manipulate the data that the view displays.
Which two database objects should you use? Each correct answer presents a complete solution.

• A. a user-defined table-valued function
• B. a CRL function
• C. a stored procedure
• D. a user-defined scalar function

Answer: AC

NEW QUESTION 16
You have a database that contains the following tables: Customer

CustomerAudit

Where the value of the CustomerID column equals 3, you need to update the value of the CreditLimit column to 1000 for the customer. You must ensure that the change to the record in the Customer table is recorded on the CustomerAudit table.
Which Transact-SQL statement should you run?

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

Answer: D

Explanation: The OUTPUT Clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
Note: If the column modified by the .RITE clause is referenced in an OUTPUT clause, the complete value of the column, either the before image in deleted.column_name or the after image in inserted.column_name, is returned to the specified column in the table variable.

NEW QUESTION 17
You have a table named HR.Employees as shown in the exhibit. (Click the exhibit button.)

You need to write a query that will change the value of the job title column to Customer Representative for any employee who lives in Seattle and has a job title of Sales Representative. If the employee does not have a manager defined, you must not change the title.
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.

Answer:

Explanation: References: https://msdn.microsoft.com/en-us/library/ms177523.aspx

NEW QUESTION 18
You have a database that contains the following tables: tblRoles, tblUsers, and tblUsersInRoles. The table tblRoles is defined as follows.

You have a function named ufnGetRoleActiveUsers that was created by running the following Transact-SQL statement:

You need to list all roles and their corresponding active users. The query must return the RoleId, RoleName,
and UserName columns. If a role has no active users, a NULL value should be returned as the UserName for that role.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation: