Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 151:

  DRAG DROP

  You are developing a training management solution. You run the following Transact-SQL statement:

  

  You need to create an indexed view to return all the courses where the value of TrainerEval is equal to or higher than 8.5.

  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://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views

• Question 152:

  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 named DB1 that contains two tables named Sales.Customers and Sales.Orders. Sales.Customers has a foreign key relationship to a column named CustomerID in Sales.Orders.

  You need to recommend a query that returns all the customers. The query must also return the number of orders that each customer placed in 2016.

  Solution: You recommend the following query:

  

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 153:

  DRAG DROP

  

  You run the following Transact-SQL statement:

  You need to create a stored procedure that meets the following requirements:

  Inserts data into the Employees table.

  Processes all data changes as a single unit of work.

  Sets the exception severity level to 16 and an error number of 60, 000 when any error occurs.

  If a Transact-SQL statement raises a runtime error, terminates and reverts the entire unit of work, and indicates the line number in the statement where the error occurred.

  Inserts the value New Employee for the Title column if no title is provided.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segment to the correct target. 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:

  

  

  ---

  References: https://stackoverflow.com/questions/180075/executing-a-stored-procedure-inside-begin-end-transaction

• Question 154:

  SIMULATION

  You create a table named Products.Sales by running the following Transact-SQL statement:

  

  You add the following data to the table.

  

  You are developing a report to display monthly sales data.

  You need to create a Transact-SQL query to meet the following requirements: Retrieve a column for the year followed by a column for each month from January through December. Include the total sales amount for each month. Aggregate columns by year, month, and then amount.

  Construct the query using the following guidelines: Use the MONTH keyword as the interval when using the DATANAME function. Do not modify the provided IN clause. Do not surround object names with square brackets. Do not use implicit joins. Do not use the DATEPART function.

  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.

  1. SELECT * FROM

  2. (SELECT YEAR(SalesData)) AS Year, DATENAME (MONTH, SalesDate) AS Month, SalesAmount AS Amount

  3. 4.

  ) AS MonthlySalesData

  5. FOR Month IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS MonthNamePivot

  6.

  Correct Answer. explanation
  explanation

  ---

  Please see explanation

  1 SELECT * FROM

  2 (SELECT YEAR(SalesData)) AS Year, DATENAME (MONTH, SalesDate) AS Month, SUM(SalesAmount) AS Amount

  3 FROM Products.Sales GROUP BY Year, Month

  4 ) AS MonthlySalesData

  5 PIVOT SUM(Amount)

  6 FOR Month IN (January, February, March, April, May, June, July, August, September, October, November, December))

  AS MonthNamePivot

  Note:

  Line 2: Add SUM( ) around SalesAmount

  Line 3: Add: FROM Products.Sales GROUP BY Year, Month

  Line 5: Add: PIVOT SUM(Amount)

• Question 155:

  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 questionsin this series. Information and details provided in a question apply only to that question.

  You have a database that contains tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:

  

  The tables include the data below:

  Customer_CRMSystem

  

  Customer_HRSystem

  

  Records that contain null values for CustomerCode can be uniquely identified by CustomerName.

  You need to display customers that have a proper CustomerCode and do not appear in the Customer_HRSystem table.

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

  ---

  To return records from the left table which are not found in the right table use Left outer join and filter out the rows with NULL values for the attributes from the right side of the join.

  Reference: https://stackoverflow.com/questions/25685545/how-to-return-rows-from-left-table-not-found-in-right-table

• Question 156:

  SIMULATION

  You create a table named Sales.Categories by running the following Transact-SQL statement:

  

  You add the following data to the table.

  

  You need to create a query that uses a common table expression (CTE) to show the parent category of each category. The query must meet the following requirements:

  

  Return all columns from the Categories table in the order shown.

  

  Exclude all categories that do not have a parent category.

  Construct the query using the following guidelines:

  

  Name the expression ParentCategories.

  

  Use PC as the alias for the expression.

  

  Use C as the alias for the Categories table.

  

  Use the AS keyword for all table aliases.

  

  Use individual column names for each column that the query returns.

  

  Do not use a prefix for any column name.

  

  Do not use implicit joins.

  

  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. You may check syntax as many times as needed.

  Correct Answer. explanation
  explanation

  ---

  Please see explanation

  1 WITH ParentCategories pc (CategoryID, Name, PatentCategoryID) AS (SELECT c.categoryID,c.name,c.parentcategoryid

  2 FROM sales.categories c

  3 WHERE parentcategoryid is not null

  4 )

  5 SELECT * FROM parentcategories

  Note: On Line 1 replace c with WITH ParentCategories pc (CategoryID, Name, PatentCategoryID) AS

  Note: The basic syntax structure for a CTE is:

  WITH expression_name [ ( column_name [,...n] ) ]

  AS

  ( CTE_query_definition )

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

• Question 157:

  DRAG DROP

  You have a table named HumanResources.Employee. You configure the table to use a default history table that contains 10 years of data.

  You need to write a query that retrieves the values of the BusinessEntityID and JobTitle fields. You must retrieve all historical data up to January 1, 2017 where the value of the BusinessEntityID column equals 4.

  Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate 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/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table

• Question 158:

  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. an extended stored procedure
  B. a user-defined table-valued function
  C. a user-defined stored procedure that has an OUTPUT parameter
  D. a memory-optimized table that has updated statistics
  B. a user-defined table-valued function

  ---

  References: https://www.techrepublic.com/blog/the-enterprise-cloud/understand-when-to-use-user-defined-functions-in-sql-server/

• Question 159:

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

• Question 160:

  DRAG DROP You have a database that stored information about servers and application errors. The database contains the following tables. Servers

  

  Errors

  

  You are building a webpage that shows the three most common errors for each server.

  You need to return the data for the webpage.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location. 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: