Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 131:

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

• Question 132:

  HOTSPOT

  You are creating a training management application. You run the following Transact-SQL statement:

  

  You must build a report that returns course identifiers and the average evaluation score for each course. The result set must include only one score for each employee for each course.

  You need to create a query that returns the required data.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

• Question 133:

  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 create a table by running the following Transact-SQL statement:

  

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

  

  Output data by using a tree-like structure.

  

  Allow mixed content types.

  

  Use custom metadata attributes.

  Which Transact-SQL statement should you run?

  

  A. B. C. D. E. F.
  G. H.
  F

• Question 134:

  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 plan to run the following query to update tasks that are not yet started:

  UPDATE Task SET StartTime = GETDATE() WHERE StartTime IS NULL

  You need to return the total count of tasks that are impacted by this UPDATE operation, but are not associated with a project.

  What set of Transact-SQL statements should you run?

  

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

  ---

  The WHERE clause of the third line should be WHERE ProjectID IS NULL, as we want to count the tasks that are not associated with a project.

• Question 135:

  SIMULATION

  You have a database that contains the following tables.

  

  You need to create a query that lists the lowest-performing salespersons based on the current year-to-date sales period. The query must meet the following requirements:

  

  Return a column named Fullname that includes the salesperson FirstName, a space, and then LastName.

  

  Include the current year-to-date sales for each salesperson.

  

  Display only data for the three salespersons with the lowest year-to-year sales values.

  

  Exclude salespersons that have no value for TerritoryID. Construct the query using the following guidelines:

  

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

  

  Use two-part column names.

  

  Do not surround object names with square brackets.

  

  Do not use implicit joins.

  

  Use only single quotes for literal text.

  

  Use aliases only if required.

  

  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

  

  On ordering: ASC | DESC

  Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values. References: https://msdn.microsoft.com/en-us/library/ms189463.aspx

• Question 136:

  You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)

  

  You need to create a list of all customers and the date that the customer placed their last order. For customers who have not placed orders, you must substitute a zero for the order ID and 01/01/1990 for the date. Which Transact-SQL statement should you run?

  

  

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

  ---

  COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

• Question 137:

  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.

  A database has two tables as shown in the following database diagram:

  

  You need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents. The query must return the following columns: tblProvince.ProvinceId tblProvince.ProvinceName a derived column named LargeCityCount that presents the total count of large cities for the province

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  The SQL CROSS JOIN produces a result set which is the number of rowsin the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian

  Product.

  This is not what is required in this scenario.

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

• Question 138:

  You have a database that includes the tables shown in the exhibit. (Click the exhibit button.)

  

  You need to create a list of all customers and the date that the customer placed their last order. For customers who have not placed orders, you must substitute 01/01/1990 for the date. Which Transact-SQL statement should you run?

  

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

• Question 139:

  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

  

  The company's development team is designing a customer directory application. The application must list customers by the area code of their phone number. The area code is defined as the first three characters of the phone number.

  The main page of the application will be based on an indexed view that contains the area and phone number for all customers.

  You need to return the area code from the PhoneNumber field.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  As the result of the function will be used in an indexed view we should use schemabinding. References: https://sqlstudies.com/2014/08/06/schemabinding-what-why/

• Question 140:

  You have a database named MyDb. You run the following Transact-SQL statements:

  

  A value of 1 in the IsActive column indicates that a user is active.

  You need to create a count for active users in each role. If a role has no active users. You must display a zero as the active users count.

  Which Transact-SQL statement should you run?

  

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