[Free] 2019(Nov) EnsurePass Microsoft 70-761 Dumps with VCE and PDF 41-50

Get Full Version of the Exam
http://www.EnsurePass.com/70-761.html

Question No.41

DRAG DROP

You have a database that includes the following tables:

image

You need to create a list of all customer IDs and the date of the last order that each customer placed. If the customer has not placed any orders, you must return the date January 1, 1900. The column names must be CustomerID and LastOrderDate.

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.

image

Correct Answer:

image

Question No.42

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

image

You need to create a list of all customers, the order ID for the last order that the customer placed,

and the date that the order was placed. 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?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: A

Explanation:

ISNULL Syntax: ISNULL ( check_expression , replacement_value ) author:quot;Luxemburg, Rosaquot;

The ISNULL function replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression.

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

Question No.43

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:

image

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

image

image

Output data by using a tree-like structure. Allow mixed content types.

image

Use custom metadata attributes.

Which Transact-SQL statement should you run?

image

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

  5. Option E

  6. Option F

  7. Option G

  8. Option H

Correct Answer: F

Explanation:

In a FOR XML clause, you specify one of these modes: RAW, AUTO, EXPLICIT, and PATH.

The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape. The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space- separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.

The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

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

Question No.44

CORRECT TEXT

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:

image

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:

image

image

Include the average normalized readings and nearest mountain name. Exclude sensors for which no normalized reading exists.

image

Exclude those sensors with value of zero for tremor. Construct the query using the following guidelines:

image

image

Use one part names to reference tables, columns and functions. Do not use parentheses unless required.

image

image

Do not use aliases for column names and table names. Do not surround object names with square brackets.

image

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.

image

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

Correct Answer:

SELECT SensorID, NearestMountain(Location) FROM GroundSensors

WHERE TREMOR amp;lt;amp;gt; 0 AND NormalizedReading IS NOT NULL GROUP BY SensorID, NearestMountain(Location)

Explanation:

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.

SELECT SensorID, NearestMountain(Location) FROM GroundSensors

WHERE TREMOR amp;lt;amp;gt; 0 AND NormalizedReading IS NOT NULL GROUP BY SensorID, NearestMountain(Location)

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

Question No.45

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

image

Application.Cities

image

Sales.CustomerCategories

image

The company#39;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:

image

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

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

HOTSPOT

You have the following Transact-SQL query:

image

What type of functions are used in the query?

To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.47

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:

image

You are developing a report that displays customer information. The report must contain a grand total column.

You need to write a query that returns the data for the report. Which Transact-SQL statement should you run?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

  5. Option E

  6. Option F

  7. Option G

  8. Option H

Correct Answer: E

Explanation:

Calculate aggregate column through AVG function and GROUP BY clause.

Question No.48

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:

image

The tables include the following records:

Customer_CRMSystem

image

Customer_HRSystem

image

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 proper CustomerCode.

Which Transact-SQL statement should you run?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

  5. Option E

Correct Answer: A

Explanation:

When there are null values in the columns of the tables being joined, the null values do not match each other. The presence of null values in a column from one of the tables being joined can be returned only by using an outer join (unless the WHERE clause excludes null values).

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

Question No.49

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:

image

Details for the Application.Cities table are shown in the following table:

image

Details for the Sales.CustomerCategories table are shown in the following table:

image

You are preparing a promotional mailing. The mailing must only be sent to customers in good standing that live in medium and large cities.

You need to write a query that returns all customers that are not on credit hold who live in cities with a population greater than 10,000.

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.

image

Correct Answer:

image

Question No.50

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:

image

The tables include the following records:

Customer_CRMSystem

image

Customer_HRSystem

image

Records that contain null values for CustomerCode can be uniquely identified by CustomerName. You need to display a Cartesian product, combining both tables.

Which Transact-SQL statement should you run?

image

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

  5. Option E

  6. Option F

  7. Option G

  8. Option H

Correct Answer: G

Explanation:

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.

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

Get Full Version of the Exam
70-761 Dumps
70-761 VCE and PDF

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.