Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 51:

  HOTSPOT

  You have the following Transact-SQL statement:

  DELETE FROM Person

  WHERE PersonID = 5

  You need to implement error handling.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017

• Question 52:

  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

  

  Your company is developing a new social application that connects customers to each other based on the distance between their delivery locations.

  You need to write a query that returns the nearest customer.

  Solution: You run the following Transact-SQL statement:

  

  The variable @custID is set to a valid customer. Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 53:

  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.Orders.

  

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

  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 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.Orders. 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 to the calling procedure.

  Data modifications that are unsuccessful are rolled back. You must display a message that uses severity level 16 and a value of -1.

  The stored procedure uses a built-in 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 55:

  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

  ---

  Use a WHERE CURRENT OF clause, which deletes at the current position of the specified cursor. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql

• Question 56:

  You have a database that tracks customer complaints.

  The database contains a table named Complaints that includes the following columns:

  

  You need to create a query that lists complaints about defective products. The report must include complaints where the exact phrase "defective product" occurs, as well as complaints where similar phrases occur.

  

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

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-2017

• Question 57:

  You are building a stored procedure named SP1 that calls a stored procedure named SP2.

  SP2 calls another stored procedure named SP3 that returns a Recordset. The Recordset is stored in a temporary table.

  You need to ensure that SP2 returns a text value to SP1.

  What should you do?

  A. Create a temporary table in SP2, and then insert the text value into the table.
  B. Return the text value by using the ReturnValue when SP2 is called.
  C. Add the txt value to an OUTPUT parameter of SP2.
  D. Create a table variable in SP2, and then insert the text value into the table.
  C. Add the txt value to an OUTPUT parameter of SP2.

• Question 58:

  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 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 preparing a promotional mailing. The mailing must only be sent to customers in good standing that live in medium and large cities. You need to write a query that returns all customers that are not on credit hold who live in cities with a population greater than 10,000.

  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.

  Select and Place:

  

  

  ---

  Box 1: IN (

  The IN clause determines whether a specified value matches any value in a subquery or a list.

  Syntax: test_expression [ NOT ] IN ( subquery | expression [ ,...n ] )

  Where subquery is a subquery that has a result set of one column. This column must have the same data type as test_expression.

  Box 2: WHERE

  Box 3: AND [IsOnCreditHold] = 0

  Box 4: )

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

• Question 59:

  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
  E. GROUP BY CUBE

  ---

  Example of GROUP BY CUBE result set:

  In the following example, the CUBE operator returns a result set that has one grouping for all possible combinations of columns in the CUBE list and a grand total grouping.

  

  References: https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

• Question 60:

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

  1.

  tblProvince.ProvinceId

  2.

  tblProvince.ProvinceName

  3.

  a derived column named LargeCityCount that presents the total count of large cities for the province Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We should use CROSS APPLY rather than OUTER APPLY.

  Note:

  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.

  There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set,

  and rows that do not, with NULL values in the columns produced by the table-valued function.

  References:

  https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx