Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 211:

  DRAG DROP

  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.

  Select and Place:

  

  

  ---

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

• Question 212:

  HOTSPOT

  You have a database that contains the following tables: tblEmployees and tblSalesSummary. Each record contains approximately one million records.

  You use Microsoft SQL Server Management Studio (SSMS) to run two queries. The Include Actual Execution Plan option is enabled.

  Both queries return the same results. SSMS generates the execution plans shown in the exhibit. (Click the Exhibit button.)

  You need to troubleshoot the queries.

  How should you interpret the execution plans? 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/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-2017

• Question 213:

  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:

  

  The Task table includes the following columns:

  

  You plan to run the following query to update tasks that are not yet started:

  UPDATE Task SET StartTime = GETDATE() WHERE StartTime IS NULL

  You need to return the total count of tasks that are impacted by this UPDATE operation, but are not associated with a project.

  What set of Transact-SQL statements should you run?

  

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

• Question 214:

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

  ---

  We need a GROUP BY statement as we want to return an order for each customer.

• Question 215:

  You have a database named MyDb. You run the following Transact-SQL statements:

  

  A value of 1 in the IsActive column indicates that a user is active.

  You need to create a count for active users in each role. If a role has no active users. You must display a zero as the active users count.

  Which Transact-SQL statement should you run?

  

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

• Question 216:

  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
  B. a user-defined table-valued function

  ---

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

  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 is denormalized. Users make frequent changes to data in a primary table.

  You need to ensure that users cannot change the tables directly, and that changes made to the primary table also update any related tables.

  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
  B. a view

  ---

  Using an Indexed View would allow you to keep your base data in properly normalized tables and maintain data-integrity while giving you the denormalized "view" of that data. References: http://stackoverflow.com/questions/4789091/updating-redundant-denormalized-data-automatically-in-sql-server

• Question 218:

  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:

  

  The marketing department is performing an analysis of how discount affect credit limits. They need to know the average credit limit per standard discount percentage for customers whose standard discount percentage is between zero and four.

  You need to create a query that returns the data for the analysis.

  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: 0, 1, 2, 3, 4

  Pivot example:

  -- Pivot table with one rowand five columns

  SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,

  [0], [1], [2], [3], [4]

  FROM

  (SELECT DaysToManufacture, StandardCost

  FROM Production.Product) AS SourceTable

  PIVOT

  (

  AVG(StandardCost)

  FOR DaysToManufacture IN ([0], [1], [2], [3], [4])

  ) AS PivotTable;

  Box 2: [CreditLimit]

  Box 3: PIVOT

  You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple

  columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

  Box 4: 0, 1, 2, 3, 4

  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 expression[ ,... n ] is a list of expressions to test for a match. All expressions must be of the same type as test_expression.

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

• Question 219:

  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
  A. GROUP BY

• Question 220:

  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:

  

  

  Task level is defined using the following rules:

  

  You need to determine the task level for each task in the hierarchy.

  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:

  

  

  ---

  Box 1: SELECT CAST (NULL AS INT) AS ParentTaskID, etc.

  This statement selects all tasks with task level 0.

  The ParentTaskID could be null so we should use CAST (NULL AS INT) AS ParentTaskID.

  Box 2: UNION

  We should use UNION and not UNION ALL as we do not went duplicate rows.

  UNION specifies that multiple result sets are to be combined and returned as a single result set.

  Incorrect Answers:

  Not UNION ALL: ALL incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

  Box 3, Box 4, Box 5:

  These statements select all tasks with task level >0.

  References:

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