Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 11:

  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 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 function that accepts a CustomerID as a parameter and returns the following information:

  all customer information for the customer

  the total number of orders for the customer

  the total price of all orders for the customer

  the average quantity of items per order How should you complete the function definition? To answer, drag the appropriate Transact-SQL segment to the correct locations. 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:

  

  

  ---

  Box1: RETURNS TABLE

  The function should return the following information: all customer information for the customer the total number of orders for the customer the total price of all orders for the customer the average quantity of items per order

  Box 2: COUNT

  The function should return the total number of orders for the customer.

  Box 3: SUM

  The function should return the total price of all orders for the customer.

  Box 3. AVG

  The function should return the average quantity of items per order.

  Box 4: GROUP BY

  Need to use GROUP BY for the aggregate functions.

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

• Question 12:

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

  HOTSPOT

  You develop and deploy a project management application. The application uses a Microsoft SQL Server database to store data. You are developing a software bug tracking add-on for the application.

  The add-on must meet the following requirements:

  Allow case sensitive searches for product.

  Filter search results based on exact text in the description.

  Support multibyte Unicode characters.

  You run the following Transact-SQL statement:

  

  Users report that searches for the product Salt also return results for the product salt.

  You need to ensure that the query returns the correct results.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  Hot Area:

  

  

• Question 14:

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

  ---

  ShortestLineTo (geometry Data Type) Returns a LineString instance with two points that represent the shortest distance between the two geometry instances. The length of the LineString instance returned is the distance between the two

  geometry instances.

  STLength (geometry Data Type) returns the total length of the elements in a geometry instance.

  References: https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/shortestlineto-geometry-data-type

• Question 15:

  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 to that question.

  You have a database for a banking system. The database has two tables named tblDepositAcct and tblLoanAcct that store deposit and loan accounts, respectively. Both tables contain the following columns: You need to determine the total number of customers who have only loan accounts. 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
  E. Option E

  ---

  The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match. References: https://www.w3schools.com/sql/sql_join_right.asp

• Question 16:

  You have a date related query that would benefit from an indexed view.

  You need to create the indexed view.

  Which two Transact-SQL functions can you use? Each correct answer presents a complete solution.

  NOTE: Each correct selection is worth one point.

  A. DATEADD
  B. AT TIME ZONE
  C. GETUTCDATE
  D. DATEDIFF
  C. GETUTCDATE
  D. DATEDIFF

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017#DateandTimeFunctions

• Question 17:

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

• Question 18:

  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 tables named Customer_CRMSystem and Customer_HRSystem. Both tables use the following structure:

  

  The tables include the following records: Customer_CRMSystem

  

  Customer_HRSystem

  

  Records that contain null values for CustomerCode can be uniquely identified by CustomerName. You need to display a list of customers that 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
  D. Option D

  ---

  EXCEPT returns distinct rows from the left input query that aren't output by the right input query. References:https://msdn.microsoft.com/en-us/library/ms188055.aspx

• Question 19:

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

  ---

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

• Question 20:

  You have a table named Table1 that contains 200 million rows. Table1 contains a column named SaleDate that has a data type of DateTime2(3). Users report that the following query runs slowly.

  

  You need to reduce the amount of time it takes to run the query.

  What should you use to replace the WHERE statement?

  A. WHERE SaleDate >= '2017-01-01' AND SaleDate < '2018-01-01'
  B. WHERE cast(SaleDate as varchar(10)) BETWEEN '2017-01-01' AND '2017-12-31'
  C. WHERE cast(SaleDate as date) BETWEEN '2017-01-01' AND '2017-12-31'
  D. WHERE 2017 = year(SaleDate)
  C. WHERE cast(SaleDate as date) BETWEEN '2017-01-01' AND '2017-12-31'

  ---

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