Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 201:

  You need to create a table named MiscellaneousPayment that meets the following requirements:

  

  Which Transact-SQL statement should you run?

  

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

  ---

  Incorrect Answers:

  A: For column Reason we must use nvarchar, not varchar, as multilingual values must be supported.

  B: We cannot use INT for the Id column as new values must be automatically generated.

  C: For column Reason we must use nvarchar, not varchar, as multilingual values must be supported.

  Note: Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is the choice. Varchar stores ASCII data and should be your data type of choice for normal use.

  References: https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql

• Question 202:

  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.

  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

• Question 203:

  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:

  

  

  When running an operation, you updated a column named EndTime for several records in the Project table, but updates to the corresponding task records in the Task table failed.

  You need to synchronize the value of the EndTime column in the Task table with the value of the EndTime column in the project table. The solution must meet the following requirements:

  If the EndTime column has a value, make no changes to the record.

  If the value of the EndTime column is null and the corresponding project record is marked as completed, update the record with the project finish time.

  Which four 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: UPDATE T SET T.EndTime = P.EndTime

  We are updating the EndTime column in the Task table.

  Box 2: FROM Task AS T

  Where are updating the task table.

  Box 3:INNER JOIN Project AS P on T.ProjectID = P.ProjectID

  We join with the Project table (on the ProjectID columnID column).

  Box 4: WHERE P.EndTime is NOT NULL AND T.EndTime is NULL

  We select the columns in the Task Table where the EndTime column in the Project table has a value (NOT NULL),but where it is NULL in the Task Table.

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

• Question 204:

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

  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 design an indexed view to return customer information.

  What are the requirements for creating an indexed view? Use the drop-down menus to select the answer choice that completes each statement.

  Hot Area:

  

  

  ---

  Box 1: Deterministic The definition of an indexed view must be deterministic. A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic.

  Box 2: SCHEMABINDING Create the view by using the WITH SCHEMABINDING option.

  Box 3: unique clustered The first index created on a view must be a unique clustered index.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views

• Question 206:

  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

  ---

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

• Question 207:

  You have a database that includes the following tables.

  HumanResources.Employee

  

  Sales.SalesPerson

  

  The HumanResources.Employee table has 2,500 rows, and the Sales.SalesPerson table has 2,000 rows. You review the following Transact-SQL statement:

  

  You need to determine the performance impact of the query.

  How many times will a lookup occur on the primary key index on the Sales.SalesPerson table?

  A. 200
  B. 2,000
  C. 2,500
  D. 5,500
  C. 2,500

• Question 208:

  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

  ---

  https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

• Question 209:

  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

• Question 210:

  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
  C. GROUP BY ROLLUP

  ---

  In the result sets that are generated by the GROUP BY operators, NULL has the following uses:

  

  If a grouping column contains NULL, all null values are considered equal, and they are put into one NULL group.

  

  When a column is aggregated in a row, the value of the column is shown as NULL.

  Example of GROUP BY ROLLUP result set:

  

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