Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 161:

  HOTSPOT

  You are a database administrator for an online retail store. You create a table to track orders by running the following Transact-SQL statement:

  

  You are developing a query that will be used as a JSON data source for a web application. The web application requires JSON in the following format:

  

  You need to build the query to return the data for the web application.

  How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  Explanation:

  Box 1: Order.Date

  In PATH mode, you can use the dot syntax - for example, 'Item.UnitPrice' - to format nested output.

  

  Here's a sample query that uses PATH mode with the FOR JSON clause. The following example also uses the ROOT option to specify a named root element.

  Box 2: JSON_VALUE(LineTotal)

  JSON_VALUE extracts a scalar value from a JSON string.

  Box 3: Order.Product

  Box 4: FOR JSON PATH

  When you use the FOR JSON clause, you can specify the structure of the JSON output explicitly, or let the structure of the SELECT statement determine the output.

  To maintain full control over the format of the JSON output, use FOR JSON PATH. You can create wrapper objects and nest complex properties.

  To format the JSON output automatically based on the structure of the SELECT statement, use FOR JSON AUTO.

  Box 5: FOR JSON PATH, ROOT('ORDER)

  Use the ROOT option to specify a named root element.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql

• Question 162:

  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:

  1.

  Insert product records as a single unit of work.

  2.

  Return error number 51000 when a product fails to insert into the database.

  3.

  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
  B. No

  ---

  A transaction is correctly defined for the INSERT INTO ..VALUES statement, and if there is an error in the transaction it will be caught ant he transaction will be rolled back. However, error number 51000 will not be returned, as it is only used

  in an IF @ERROR = 51000 statement.

  Note: @@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

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

• Question 163:

  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 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:

  

  

  You need to identify the owner of each task by using the following rules:

  Return each task's owner if the task has an owner.

  If a task has no owner, but is associated with a project that has an owner, return the project's owner.

  Return the value-1for all other cases.

  How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  Box 1: COALESCE

  COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

  Box 2: T.UserID, p.UserID, -1

  Return each task's owner if the task has an owner.

  If a task has no owner, but is associated with a project that has an owner, return the project's owner.

  Return the value -1 for all other cases.

  Box 3: RIGHT JOIN

  The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. Here the right side could be NULL as the projectID of the task could be NULL.

  References:

  https://msdn.microsoft.com/en-us/library/ms190349.aspx

  http://www.w3schools.com/Sql/sql_join_right.asp

• Question 164:

  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 global temporary table in the stored procedure.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 165:

  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 run the following Transact-SQL statement:

  

  You need to return the total annual revenue for all customers, followed by a row for each customer that shows the customer's name and annual revenue. 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
  A. Option A

• Question 166:

  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:

  1.

  Insert product records as a single unit of work.

  2.

  Return error number 51000 when a product fails to insert into the database.

  3.

  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
  B. No

  ---

  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 167:

  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:

  

  The Products table includes the data shown in the following table:

  

  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
  A. Yes

  ---

  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 168:

  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 create a cursor by running the following Transact-SQL statement:

  

  If the credit limit is zero, you must delete the customer record while fetching data. You need to add the DELETE statement.

  Solution: You add the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 169:

  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.

  A. Check the answer below
  A. Check the answer below

  ---

  Correct Answer:

  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 170:

  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
  A. Yes

  ---

  With the INSERT INTO..VALUES statement we can insert both values with just one statement. This ensures that both records or neither is inserted. References:https://msdn.microsoft.com/en-us/library/ms174335.aspx