2017 April Microsoft Official New Released 70-761 Dumps in Lead2pass.com!
100% Free Download! 100% Pass Guaranteed!
Are you struggling for the 70-761 exam? Good news, Lead2pass Microsoft technical experts have collected all the questions and answers which are updated to cover the knowledge points and enhance candidates’ abilities. We offer the latest 70-761 PDF and VCE dumps with new version VCE player for free download, and the new 70-761 dump ensures your 70-761 exam 100% pass.
Following questions and answers are all new published by Microsoft Official Exam Center: http://www.lead2pass.com/70-761.html
QUESTION 46
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 following records:
Customer_CRMSystem
Customer_HRSystem
Records that contain null values for CustomerCode can be uniquely identified by CustomerName.
You need to display a list of customers that do not appear in the Customer_HRSystem table.
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
Answer: D
Explanation:
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/ms188055.aspx
QUESTION 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 have a database that contains tables named Customer_CRMSystem and Customer_ HRSystem.
Both tables use the following structure:
The tables include the following records:
Customer_ CRMSystem
Customer_ HRSystem
Records that contain null values for CustomerCode can be uniquely identified by Customer Name. You need to display customers who appear in both tables and have a proper CustomerCode.
Which Transact-SQL statement should you run?
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
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 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:
The tables include the following records:
Customer_ CRMSystem
Customer_ HRSystem
Records that contain null values for CustomerCode can be uniquely identified by Customer Name. You need to display a Cartesian product, combining 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
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
QUESTION 49
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 following records:
Customer_ CRMSystem
Customer_ HRSystem
Records that contain null values for CustomerCode can be uniquely identified by Customer Name. You need to create a list of all unique customers that appear in either table.
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
Answer: E
Explanation:
UNION 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. The UNION operation is different from using joins that combine columns from two tables.
QUESTION 50
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.
Drag and Drop Question
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 accepts a CustomerID as a parameter and returns the following information:
– all customer information for the customer
– the total number of orders for the customer
– the total price of all orders for the customer
– the average quantity of items per order
How should you complete the function definition? To answer, drag the appropriate TransactSQL segment to the correct locations. 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.
Answer:
Explanation:
Box1: RETURNS TABLE
The function should return the following information:
– all customer information for the customer
– the total number of orders for the customer
– the total price of all orders for the customer
– the average quantity of items per order
Box 2: COUNT
The function should return the total number of orders for the customer.
Box 3: SUM
The function should return the total price of all orders for the customer.
Box 3. AVG
The function should return the average quantity of items per order.
Box 4: GROUP BY
Need to use GROUP BY for the aggregate functions.
References: https://msdn.microsoft.com/en-us/library/ms186755.aspx
QUESTION 51
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.
Hotspot Question
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 database object that calculates the total price of an order including the sales tax. The database object must meet the following requirements:
– Reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated execution.
– Return a value.
– Be callable from a SELECT statement.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
Answer:
Explanation:
Box 1: FUNCTION
To be able to return a value we should use a scalar function.
CREATE FUNCTION creates a user-defined function in SQL Server and Azure SQL Database. The return value can either be a scalar (single) value or a table.
Box 2: RETURNS decimal(18,2)
Use the same data format as used in the UnitPrice column.
Box 3: BEGIN
Transact-SQL Scalar Function Syntax include the BEGIN ..END construct.
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] }
[ ,…n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,…n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
Box 4: @OrderPrice * @CalculatedTaxRate
Calculate the price including tax.
Box 5: END
Transact-SQL Scalar Function Syntax include the BEGIN ..END construct.
References: https://msdn.microsoft.com/en-us/library/ms186755.aspx
QUESTION 52
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.
Drag and Drop Question
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 stored procedure that inserts data into the Customers table.
The stored procedure must meet the following requirements:
– Data changes occur as a single unit of work.
– Data modifications that are successful are committed and a value of O is returned.
– Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a value of -1 is returned.
– The stored procedure uses a built-it scalar function to evaluate the current condition of data modifications.
– The entire unit of work is terminated and rolled back if a run-time error occurs during execution of the stored procedure.
How should complete the stored procedure definition? To answer, drag the appropriate Transact-SQL segments to the correct targets. 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.
Answer:
Explanation:
Box 1: XACT_ABORT
XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
Box 2: COMMIT
Commit the transaction.
Box 3: XACT_STATE
Box 4: ROLLBACK
Rollback the transaction
Box 5: THROW
THROW raises an exception and the severity is set to 16. Requirement: Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a value of -1 is returned.
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ee677615.aspx
QUESTION 53
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.
Drag and Drop Question
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.
Answer:
Explanation:
References: https://msdn.microsoft.com/en-us/library/ms186755.aspx
QUESTION 54
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 audit all customer data.
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
Answer: B
Explanation:
The FOR SYSTEM_TIME ALL clause returns all the row versions from both the Temporal and History table.
Note: A system-versioned temporal table defined through is a new type of user table in SQL Server 2016, here defined on the last line WITH (SYSTEM_VERSIONING = ON…, is designed to keep a full history of data changes and allow easy point in time analysis.
To query temporal data, the SELECT statement FROM<table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables.
References: https://msdn.microsoft.com/en-us/library/dn935015.aspx
QUESTION 55
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 return normalized data for all customers that were added in the year 2014.
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
Answer: G
Explanation:
The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):
SELECT * FROM Employee
FOR SYSTEM_TIME
BETWEEN ‘2014-01-01 00:00:00.0000000’ AND ‘2015-01-01 00:00:00.0000000’ WHERE EmployeeID = 1000 ORDER BY ValidFrom;
References: https://msdn.microsoft.com/en-us/library/dn935015.aspx
QUESTION 56
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 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?
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
F. Option F
G. Option G
H. Option H
Answer: E
Explanation:
Calculate aggregate column through AVG function and GROUP BY clause.
QUESTION 57
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 named Customers. Data stored in the table must be exchanged between web pages and web servers by using AJAX calls that use REST endpoint.
You need to return all customer information by using a data exchange format that is text- based and lightweight.
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
Answer: C
Explanation:
JSON can be used to pass AJAX updates between the client and the server.
Export data from SQL Server as JSON, or format query results as JSON, by adding the FOR JSON clause to a SELECT statement.
When you use the FOR JSON clause, you can specify the structure of the output explicitly, or let the structure of the SELECT statement determine the output.
References: https://msdn.microsoft.com/en-us/library/dn921882.aspx
QUESTION 58
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 are developing a report that aggregates customer data only for the year 2014.
The report requires that the data be denormalized.
You need to return the data for the report.
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
Answer: G
QUESTION 59
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. Option A
B. Option B
C. Option C
D. Option D
E. Option E
F. Option F
G. Option G
H. Option H
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 60
Your team is developing a database for a new online travel application.
You need to design tables and other database objects to support the application.
One particular table called Airline_Schedules needs to store the departure and arrival dates and times of flights along with time zone information.
What should you do?
A. Use the CAST function
B. Use the DATETIMEOFFSET data type
C. Use a user-defined table type
D. Use the DATETIME2 data type
Answer: B
Explanation:
Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.
We ensure our new version 70-761 PDF and VCE dumps are 100% valid for passing exam, because Lead2pass is the top IT certification study training materials vendor. Many candidates have passed exam with the help of Lead2pass’s VCE or PDF dumps. Lead2pass will update the study materials timely to make them be consistent with the current exam. Download the free demo on Lead2pass, you can pass the exam easily.
70-761 new questions on Google Drive: https://drive.google.com/open?id=0B3Syig5i8gpDX0NzUC12eEl3VGc
2017 Microsoft 70-761 exam dumps (All 74 Q&As) from Lead2pass:
http://www.lead2pass.com/70-761.html [100% Exam Pass Guaranteed]