Microsoft 70-761 Online Practice Questions and Exam Preparation

Microsoft 70-761 Online Questions & Answers

• Question 91:

  SIMULATION

  You have a database that contains the following tables.

  

  You need to create a query that returns each complaint, the names of the employees handling the complaint, and the notes on each interaction. The Complaint field must be displayed first, followed by the employee's name and the notes.

  Complaints must be returned even if no interaction has occurred. Construct the query using the following guidelines:

  

  Use two-part column names.

  

  Use one-part table names.

  

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

  

  Do not Transact-SQL functions.

  

  Do not use implicit joins.

  

  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.

  

  1 SELECT c.Complaint, e.Name, i.Notes 2 FROM Complaints c 3 JOIN __________________ 4 JOIN __________________

  

  Correct Answer. explanation
  explanation

  ---

  Please see explanation

  1 SELECT c.Complaint, e.Name, i.Notes 2 FROM Complaints c 3 JOIN Interactions i ON c.ComplaintID = i.ComplaintID 4 JOIN Employees e ON i.EmployeeID = E.EmployeeID

• Question 92:

  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

  ---

  Products with a price between $0.00 and $100 will be increased, while products with a price of $0.00 would not be increased.

• Question 93:

  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.

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

  ---

  Correct Answer:

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

  You have a database named DB1 that contains a temporal table named Sales.Customers.

  You need to create a query that returns the credit limit that was available to each customer in DB1 at the beginning of 2017.

  Which query should you execute?

  

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

• Question 95:

  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 stores sales and order information.

  Users must be able to extract information from the tables on an ad hoc basis. They must also be able to reference the extracted information as a single table.

  You need to implement a solution that allows users to retrieve the data required, based on variables defined at the time of the query.

  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
  C. a table-valued function

  ---

  User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are

  allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

  A table-valued user-defined function can also replace stored procedures that return a single result set.

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

• Question 96:

  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 following records exist in the tables: Customer_CRMSystem

  

  

  Customer_HRSystem

  

  Records that contain null values for CustomerCode can be uniquely identified by CustomerName.

  You need to display distinct customers that appear in both tables.

  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
  H. Option H

  ---

  To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. SQL Server provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

  Incorrect Answers:

  A: Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table.

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

  D: EXCEPT returns distinct rows from the left input query that aren't output by the right input query.

  E: UNION specifies that multiple result sets are to be combined and returned as a single result set, but this will not work here as the CustomerID column values do not match.

  F: UNION ALL incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

  G: A cross join would produce the Cartesian product of the two tables.

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

• Question 97:

  SIMULATION

  You have a database that contains the following tables.

  

  You need to create a query that returns each complaint, the names of the employees handling the complaint, and the notes on each interaction. The Complaint field must be displayed first, followed by the employee's name and the notes. Complaints must be returned even if no interaction has occurred.

  Construct the query using the following guidelines:

  

  Use two-part column names.

  

  Use one-part table names.

  

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

  

  Do not Transact-SQL functions.

  

  Do not use implicit joins.

  

  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.

  

  1 SELECT c.Complaint, e.Name, i.Notes 2 FROM Complaints c 3 JOIN __________________ 4 JOIN __________________

  

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

  ---

  Correct Answer:

  1 SELECT c.Complaint, e.Name, i.Notes 2 FROM Complaints c 3 JOIN Interactions i ON c.ComplaintID = i.ComplaintID 4 JOIN Employees e ON i.EmployeeID = E.EmployeeID

• Question 98:

  You need to create an indexed view that requires logic statements to manipulate the data that the view displays. Which two database objects should you use? Each correct answer presents a complete solution.

  A. a user-defined table-valued function
  B. a CRL function
  C. a stored procedure
  D. a user-defined scalar function
  A. a user-defined table-valued function
  C. a stored procedure

  ---

  You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.

• Question 99:

  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 data below: Customer_CRMSystem

  

  Customer_HRSystem

  

  Records that contain null values for CustomerCode can be uniquely identified by CustomerName. You need to display customers who appear in both tables and have a non-null CustomerCode. 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
  B. Option B

  ---

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

  A: INNER JOIN returns records that have matching values in both tables but it returns duplicate records.

  C: LEFT OUTER JOIN returns ALL records from the left table, and the matched records from the right table.

  D: EXCEPT returns distinct rows from the left input query that are not output by the right input query.

  E, F: UNION and UNION ALL combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

  G: CROSS JOIN returns all possible combinations of data from both tables.

  H: FULL OUTER JOIN returns all records when there is a match in either left or right table

  Note: NULL values are treated as distinct values in join operations.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sqlserver-2017 https://www.w3schools.com/sql/sql_join.asp

• Question 100:

  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 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 are creating a report to measure the impact of advertising efforts that were designed to attract new customers. The report must show the number of new customers per day for each customer category, but only if the number of new customers is greater than five.

  You need to write the query to return data for the report.

  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: