Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 191:

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

  Select and Place:

  

  

  ---

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

• Question 192:

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

  ---

  As there are two separate INSERT INTO statements we cannot ensure that both or neither records are inserted.

• Question 193:

  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 contains a single table named tblVehicleRegistration. The table is defined as follows:

  

  You run the following query:

  

  The query output window displays the following error message: "Conversion failed when converting the varchar value `AB012' to data type int." You need to resolve the error. Solution: You modify the Transact-SQL statement as follows:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 194:

  SIMULATION

  You have a database that contains the following tables.

  

  You need to create a query that lists the lowest-performing salespersons based on the current year-to-date sales period. The query must meet the following requirements:

  1. Return the LastName and SalesYTD for the three salespersons with the highest year-to-date sales values

  2. Include the current year-to-date sales for each salesperson.

  3. Exclude salespersons that have no value for TerritoryID.

  Construct the query using the following guidelines:

  1. Use the first letter of a table name as the table alias.

  2. Use two-part column names.

  3. Do not surround object names with square brackets.

  4. Do not use implicit joins.

  5. Use only single quotes for literal text.

  6. Use aliases only if required.

  

  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. Please see explanation
  Please see explanation

  ---

  SELECT TOP (3) LastName, S.SalesYTD FROM Person as P INNER JOIN SalesPerson AS S ON P.PersonID = S.SalesPersonID WHERE S.TerritoryID IS NOT NULL ORDER BY S.SalesYTD DESC

  On ordering: ASC | DESC Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values.

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

• Question 195:

  DRAG DROP You need to create a stored procedure that meets the following requirements: Produces a warning if the credit limit parameter is greater than 7,000 Propagates all unexpected errors to the calling process How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segments 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: THROW 51000, 'Warning: Credit limit is over 7,000!",1

  THROW raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server.

  THROW syntax:

  THROW [ { error_number | @local_variable },

  { message | @local_variable },

  { state | @local_variable } ]

  [ ; ]

  Box2: RAISERROR (@ErrorMessage, 16,1)

  RAISERROR generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is

  returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct. New applications should use THROW instead.

  Severity levels from 0 through 18 can be specified by any user. Severity levels from 19through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19

  through 25, the WITH LOG option is required.

  On Severity level 16. Using THROW to raise an exception

  The following example shows how to use the THROW statement to raise an exception.

  Transact-SQL

  THROW 51000, 'The record does not exist.', 1;

  Here is the result set.

  Msg 51000, Level 16, State 1, Line 1

  The record does not exist.

  Note: RAISERROR syntax:

  RAISERROR( { msg_id | msg_str | @local_variable }

  { ,severity ,state }

  [ ,argument [ ,...n ] ] )

  [ WITH option [ ,...n ] ]

  Note: The ERROR_MESSAGE function returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run.

  References:

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

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

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

• Question 196:

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

  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.

  Multiple processes use the data from a table named Sales and place it in other databases across the organization. Some of the processes are not completely aware of the data types in the Sales table. This leads to data type conversion errors.

  You need to implement a method that returns a NULL value id data conversion fails instead of throwing an error.

  What should you implement?

  A. the COALESCE function
  B. a view
  C. a table-valued function
  D. the TRY_PARSE function
  E. a stored procedure
  F. the ISNULL function
  G. a scalar function
  H. the TRY_CONVERT function
  H. the TRY_CONVERT function

  ---

  TRY_CONVERT returns a value cast to the specified data type if the cast succeeds; otherwise, returns null. References: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql

• Question 198:

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

• Question 199:

  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 below
  A. Check the answer below

  ---

  Correct Answer:

  1 SELECT avg (normalizedreading) as AverageReading, location as Mountain 2 FROM GroundSensors 3 WHERE normalizedreading is not null

  Note: On line 1 change to AverageReading and change to Mountain.

• Question 200:

  SIMULATION

  You have a database that contains the following tables.

  

  You need to create a query that lists all complaints from the Complaints table, and the name of the person handling the complaints if a person is assigned. The ComplaintID must be displayed first, followed by the person name.

  Construct the query using the following guidelines:

  1. Use two-part column names.

  2. Use one-part table names.

  3. Do not use aliases for column names or table names.

  4. Do not use Transact-SQL functions.

  5. Do not use implicit joins.

  6. 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 below
  A. Check the answer below

  ---

  Correct Answer:

  

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