Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 171:

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

  ---

  Would list the customers with duplicates, which would equal the number of accounts. Incorrect Answers:

  A: INTERSECT returns distinct rows that are output by both the left and right input queries operator.

  B: Would list the customers without duplicates.

  D: Number of customers.

  F: 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/ms180026.aspx

• Question 172:

  DRAG DROP

  You have two tables named UserLogin and Employee respectively.

  You need to create a Transact-SQL script that meets the following requirements:

  The script must update the value of theIsDeleted column for the UserLogin table to 1 if the value of the Id column for the UserLogin table is equal to 1.

  The script must update the value of the IsDeleted column of the Employee table to 1 if the value of the Id column is equal to 1 for the Employee table when an update to the UserLogin table throws an error.

  The error message “No tables updated!” must be produced when an update to the Employee table throws an error.

  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:

  

  

• Question 173:

  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:

  

  You need to create a query that meets the following requirements:

  

  For customers that are not on a credit hold, return the CustomerID and the latest recorded population for the delivery city that is associated with the customer.

  

  For customers that are on a credit hold, return the CustomerID and the latest recorded population for the postal city that is associated with the customer.

  Which two Transact-SQL queries will achieve the goal? Each correct answer presents a complete solution.

  

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

  ---

  Using Cross Joins

  A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the

  second table.

  However, if a WHERE clause is added, the cross join behaves as an inner join.

  B: You can use the IIF in the ON-statement.

  IIF returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.

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

• Question 174:

  DRAG DROP You have a database containing the following tables: Servers

  

  Errors

  

  You have a user-defined, scalar function named IPLookup that takes a DNS name as a parameter and returns the IP address of the server. You have an additional user-defined, scalar function named DNSLookup, that takes an IP address as a parameter and returns a DNS name.

  You create a view named vwErrors by running the following Transact-SQL statement:

  

  You need to insert data by using the view.

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

  

  

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

• Question 175:

  HOTSPOT

  You have a table named HumanResources.Department that was created with the query shown in the exhibit. (Click the Exhibit button.)

  

  You need to query temporal data in the table.

  In the table below, identify the Transact-SQL segments that must be used to retrieve the appropriate data.

  NOTE: Make only one selection in each column.

  Hot Area:

  

  

  ---

  AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.

  CONTAINED IN: If you search for non-current row versions only, we recommend you to use CONTAINED IN as it works only with the history table and will yield the best query performance.

  Incorrect Answers:

  Not ALL: Returns the union of rows that belong to the current and the history table.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table

• Question 176:

  DRAG DROP

  You need to create a stored procedure to update a table named Sales.Customers. The structure of the table is shown in the exhibit. (Click the exhibit button.)

  

  The stored procedure must meet the following requirements:

  Accept two input parameters.

  Update the company name if the customer exists.

  Return a custom error message if the customer does not exist.

  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.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  

• Question 177:

  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:

  

  You need to ensure that users can perform searches of descriptions. Which Transact-SQL statement should you run?

  

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

  ---

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

• Question 178:

  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 clustered index on the primary key column.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 179:

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

  

  Include the average normalized readings and nearest mountain name.

  

  Exclude sensors for which no normalized reading exists.

  

  Exclude those sensors with value of zero for tremor.

  Construct the query using the following guidelines:

  

  Use one part names to reference tables, columns and functions.

  

  Do not use parentheses unless required.

  

  Do not use aliases for column names and table names.

  

  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.

  Correct Answer. explanation
  explanation

  ---

  Correct Answer: Please see explanation

  SELECT Average(NormalizedReading), NearestMountain(SensorID)

  FROM GroundSensors

  GROUP BY NearestMountain(SensorID)

  WHERE TREMOR IS NOT 0 AND NormalizedReading IS NOT NULL

  GROUP BY is a SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group. Reference: https://msdn.microsoft.com/en-us/library/ms177673.aspx

• Question 180:

  SIMULATION

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

  

  You need to write a query that meets the following requirements:

  1. removes orders from the table that were placed before January 1, 2012

  2. uses the date format of YYYYMMDD

  3. ensures that the order has been shipped before deleting the record

  Construct the query using the following guidelines:

  1. use one-part column names and two-part table names

  2. do not use functions

  3. do not surround object names with square brackets

  4. do not use variables

  5. do not use aliases for column names and table names

  

  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:

  DELETE FROM Sales.Orders WHERE OrderDate < '2012-01-01' AND ShippedDate NOT NULL

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