Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 181:

  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 accepts a parameter of type geography and 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:

  

  Return the average normalized readings named AverageReading.

  

  Return the nearest mountain name named Mountain.

  

  Do not return any other columns.

  

  Exclude sensors for which no normalized reading exists.

  Construct the query using the following guidelines:

  

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

  

  Do not use parentheses unless required.

  

  Define column headings using the AS keyword.

  

  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

  ---

  Please see explanation 1 SELECT avg (normalizedreading) as AverageReading, location as Mountain 2 FROM GroundSensors 3 WHERE normalizedreading is not null

  Note: On line 1 change to AverageReading and change to Mountain.

• Question 182:

  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:

  

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

  

  uses the date format of YYYYMMDD

  

  ensures that the order has been shipped before deleting the record Construct the query using the following guidelines:

  

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

  

  do not use functions

  

  do not surround object names with square brackets

  

  do not use variables

  

  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.

  Correct Answer. explanation
  explanation

  ---

  Correct Answer: See the solution below

  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

• Question 183:

  SIMULATION

  You have a database that includes the following tables. All of the tables are in the Production schema.

  

  You need to create a query that returns a list of product names for all products in the Beverages category.

  Construct the query using the following guidelines:

  

  Use the first letter of the table name as the table alias.

  

  Use two-part column names.

  

  Do not surround object names with square brackets.

  

  Do not use implicit joins.

  

  Do not use variables.

  

  Use single quotes to surround literal values.

  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.

  A. Check the answer below
  A. Check the answer below

  ---

  Correct Answer:

  1 SELECT p.productname 2 FROM Production.categories AS c 3 inner join production.products as p on c.categoryid=p.categoryid 4 WHERE c.categoryname = 'Beverages'

  Note: On line 3 change * to =

• Question 184:

  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 table named Person that contains information about employees. Users are requesting a way to access specific columns from the Person table without specifying the Person table in the query statement. The columns that users can access will be determined when the query is running against the data. There are some records that are restricted, and a trigger will evaluate whether the request is attempting to access a restricted record.

  You need to ensure that users can access the needed columns while minimizing storage on the database server.

  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

  ---

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

• Question 185:

  SIMULATION

  You have a table named Cities that has the following two columns: CityID and CityName. The CityID column uses the int data type, and CityName uses nvarchar(max).

  You have a table named RawSurvey. Each row includes an identifier for a question and the number of persons that responded to that question from each of four cities. The table contains the following representative data:

  

  A reporting table named SurveyReport has the following columns: CityID, QuestionID, and RawCount, where RawCount is the value from the RawSurvey table. You need to write a Transact-SQL query to meet the following requirements:

  

  Retrieve data from the RawSurvey table in the format of the SurveyReport table.

  

  The CityID must contain the CityID of the city that was surveyed.

  

  The order of cities in all SELECT queries must match the order in the RawSurvey table.

  

  The order of cities in all IN statements must match the order in the RawSurvey table. Construct the query using the following guidelines:

  

  Use one-part names to reference tables and columns, except where not possible.

  

  ALL SELECT statements must specify columns.

  

  Do not use column or table aliases, except those provided.

  

  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

  ---

  Please see explanation

  

  UNPIVOT must be used to rotate columns of the Rawsurvey table into column values. References: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

• Question 186:

  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:

  

  The Task table includes the following columns:

  

  You need to find all projects that have at least one task that took more than 50 hours to complete. You must also determine the average duration of the tasks that took more that took more than 50 hours to complete for each project.

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

  Select and Place:

  

  

• Question 187:

  DRAG DROP

  You have 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 display a comma separated list of all product bugs filed by a user named User1.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. 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://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

• Question 188:

  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 calculates the highest tax rate charged for an item in a specific order.

  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:

  

  

  ---

  Box1: CREATE FUNCTION...@OrderID

  Include definition for the ...@OrderID parameter.

  Box 2: RETURNS decimal(18,2)

  The function is defined to return a scalar value.

  Box 3: AS BEGIN ...

  Declare the local variables of the function.

  Box 4: SET @CalculatedTaxRate = (..

  Calculate the tax rate.

  Box 5: RETURN @CalculatedRate END

  Return a scalar value.

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

• Question 189:

  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 named Products that stores information about products your company sells. The table has a column named ListPrice that stores retail pricing information for products.

  Some products are used only internally by the company. Records for these products are maintained in the Products table for inventory purposes. The price for each of these products is $0.00. Customers are not permitted to order these

  products.

  You need to increase the list price for products that cost less than $100 by 10 percent. You must only increase pricing for products that customers are permitted to order.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

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

• Question 190:

  HOTSPOT

  You need to develop a function that returns a list of courses grouped by the total number of students in a course.

  The function must list only courses that have more than a specific number of students. The specific number of students is defined as an input variable for the function.

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

  NOTE: Each correct selection is worth one point.

  Hot Area: