Microsoft 70-761 Exam Dumps 2021

NEW QUESTION 1
You have a database named DB1 that contains two tables named Sales.Customers and Sales.CustomerTransaction. Sales.CustomerTransactions has a foreign key relationship to column named CustomerID in Sales.Customers.
You need to recommend a query that returns the number of customers who never completed a transaction. Which query should you recommend?

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

Answer: A

NEW QUESTION 2
You have a date related query that would benefit from an indexed view. You need to create the indexed view.
Which two Transact-SQL functions can you use? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point

• A. DATEADD
• B. AT TIME ZONE
• C. GETUTCDATE
• D. DATEDIFF

Answer: A

NEW QUESTION 3
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

NEW QUESTION 4
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

Your company is developing a new social application that connects customers to each other based on the distance between their delivery locations.
You need to write a query that returns the nearest customer. Solution: You run the following Transact-SQL statement:
SELECT TOP 1 B.CustomerID, A.DeliveryLocation.STDistance(B.DeliveryLocation) AS Dist FROM Sales.Customers AS A
CROSS JOIN Sales.Customers AS B
WHERE A.CustomerID = @custID AND A.CustomerID <> B.CustomerID ORDER BY Dist
The variable @custID is set to a valid customer. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 5
You have a table that was created by running the following Transact-SQL statement:

You need to query the Courses table and return the result set as JSON. The output from the query must resemble the following format:

• A. SELECT CourseID AS [Course ID], Course as Name FROM CoursesFOR JSON PATH('Courses')
• B. SELECT CourseID AS 'Course ID', Course AS Name FROM CoursesFOR JSON ROOT('Courses')
• C. SELECT CourseID AS [Course ID], Course AS Name FROM CoursesFOR JSON AUTO, ROOT('Courses')
• D. SELECT CourseID AS 'Course ID', Course AS Name FROM CoursesFOR JSON AUTO, INCLUDE_NULL_VALUES('Courses')

Answer: D

Explanation: References:
https://docs.microsoft.com/en-us/sql/relational-databases/json/include-null-values-in-json-include-null-values-op

NEW QUESTION 6
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 is denormalized. Users make frequent changes to data in a primary table.
You need to ensure that users cannot change the tables directly, and that changes made to the primary table also update any related tables.
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

Answer: B

Explanation: Using an Indexed View would allow you to keep your base data in properly normalized tables and maintain data-integrity while giving you the denormalized "view" of that data.
References:
http://stackoverflow.com/questions/4789091/updating-redundant-denormalized-data-automatically-in-sql-server

NEW QUESTION 7
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

Answer: A

NEW QUESTION 8
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.

Answer:

Explanation:

NEW QUESTION 9
You create a table named Sales.Orders by running the following Transact-SQL statement:

You need to write a query that removes orders from the table that have a Status of Canceled. Construct the query using the following guidelines:


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.

Answer:

Explanation: 1. DELETE from sales.orders where status='Canceled' Note: On line 1 change calceled to Canceled
Example: Using the WHERE clause to delete a set of rows
The following example deletes all rows from the ProductCostHistory table in the AdventureWorks2012 database in which the value in the StandardCost column is more than 1000.00.
DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00;
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql

NEW QUESTION 10
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 2021.
Which query should you execute?

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

Answer: C

Explanation: AS OF: Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.

NEW QUESTION 11
You run the following Transact SQL statement:

You use the table to store data about training courses: when they finished, the location, and the number of participants in the courses. You need to display a result set that shows aggregates for all possible combination of the number of participants. Which Transact-SQL statement should you run?
A)

B)

C)

D)

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

Answer: A

NEW QUESTION 12
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 G

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 2021, 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

NEW QUESTION 13
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 several connected tables. The tables contain sales data for customers in the United States only.
You need to create a query that generates sample data for a sales table in the database. The query must include every product in the inventory for each customer.
Which statement clause should you use?

• A. GROUP BY
• B. MERGE
• C. GROUP BY ROLLUP
• D. LEFT JOIN
• E. GROUP BY CUBE
• F. CROSS JOIN
• G. PIVOT
• H. UNPIVOT

Answer: C

NEW QUESTION 14
You need to create a database object that meets the following requirements:
accepts a product identifies as input
calculates the total quantity of a specific product, including quantity on hand and quantity on order
caches and reuses execution plan
returns a value
can be called from within a SELECT statement
can be used in a JOIN clause
What should you create?

• A. a temporary table that has a columnstore index
• B. a user-defined table-valued function
• C. a memory-optimized table that has updated statistics
• D. a natively-complied stored procedure that has an OUTPUT parameter

Answer: B

Explanation: A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.
References: https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

NEW QUESTION 15
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 named DB1 that contains two tables named Sales.Customers and Sales.Orders. Sales.Customers has a foreign key relationship to a column named CustomerID in Sales.Orders.
You need to recommend a query that returns all the customers. The query must also return the number of orders that each customer placed in 2021.
Solution: You recommend the following query:

Does this meet the goal?

• A. Yes
• B. No

Answer: A

NEW QUESTION 16
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

NEW QUESTION 17
You run the following Transact-SQL statement:

You need to ensure that you can insert data into the table.
What are the characteristics of the data? To answer, select the appropriate options in the answer area.

Answer:

Explanation: Box 1: custid
IDENTITY indicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column. Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table.
Box 2: postalcode
postalcode is declared as NOT NULL, which means that a value must be inserted. Box 3: region
Fax is also a correct answer. Both these two columns are declared as NULL, which means that data entry is optional.
References: https://msdn.microsoft.com/en-us/library/ms174979.aspx

NEW QUESTION 18
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