Microsoft 70-761 Dumps - Real Exam Questions

Practice Our Microsoft 70-761 Dumps Exam Questions and Pass Your Exam Easily.


Microsoft 70-761 Dumps

Free Microsoft 70-761 Sample Quesiton 1:
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit.
You need to create a report that displays the profits made by each territory for each year and its previous year.
Which Transact-SQL query should you use?

A. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
B. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
C. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
D. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits

Correct Answer: C

Latest Microsoft 70-761 Practice Question 2:
You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate the unique values of the ProductName field of a tablevalued expression into multiple columns in the output.
Which Transact-SQL operator should you use?

A. CROSS JOIN
B. CROSS APPLY
C. PIVOT
D. UNPIVOT

Correct Answer: C

Free 70-761 Exam Dumps PDF Question 3:
You administer a Microsoft SQL Server database that supports a shopping application.
You need to retrieve a list of customers who live in territories that do not have a sales person.
Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

A. SELECT CustomerID FROM Customer
WHERE TerritoryID <>SOME(SELECT TerritoryID FROM Salesperson)
B. SELECT CustomerID FROM Customer
WHERE TerritoryID <>ALL(SELECT TerritoryID FROM Salesperson)
C. SELECT CustomerID FROM Customer
WHERE TerritoryID <>ANY(SELECT TerritoryID FROM Salesperson)
D. SELECT CustomerID FROM Customer
WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)

Correct Answer: BD

New 70-761 Exam Dumps Demo Question 4:
You administer a Microsoft SQL Server database that supports a banking transaction management application.
You need to retrieve a list of account holders who live in cities that do not have a branch location.
Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

A. SELECT AccountHolderID
FROM AccountHolder
WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B. SELECT AccountHolderID
FROM AccountHolder
WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
C. SELECT AccountHolderlD
FROM AccountHolder
WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
D. SELECT AccountHolderID
FROM AccountHolder
WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)

Correct Answer: AB

Download 70-761 PDF Question 5:
You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data
type should you use?

A. Float
B. Money
C. Smallmoney
D. Numeric

Correct Answer: D

Free Download Microsoft 70-761 Demo Question 6:
You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3.
You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3.
dbo.usp_InsertEvent must execute other stored procedures in the other databases.
You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.
Which Transact-SQL statement should you use?

A. USE Database2
B. EXECUTE AS OWNER
C. USE Database1
D. EXECUTE AS CALLER

Correct Answer: B

Microsoft Other Certification 70-761 Practice Question 7:
You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema.
You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the
db_denydatawriter role.
C. Deny Delete permissions on each table in the Sales schema for each user.
D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.

Correct Answer: A

Querying Data with Transact-SQL Microsoft 70-761 Online Quiz Question 8:
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a
report that displays the profits made by each territory for each year and its preceding year. Which Transact-SQL query should you use?

A. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits
B. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits
C. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits
D. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits

Correct Answer: B

Microsoft Other Certification 70-761 Sample Question 9:
You are writing a set of queries against a FILESTREAM-enabled database.
You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a runtime error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
B. SET XACT_ABORT OFF
C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
D. SET IMPLICIT_TRANSACTIONS ON
E. SET XACT_ABORT ON
F. SET IMPLICIT TRANSACTIONS OFF

Correct Answer: E

Free Microsoft 70-761 Querying Data with Transact-SQL Demo Question 10:
You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.
You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.
You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications’ ability to access data.
The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the applications.
What should you create for each application?

A. Synonyms
B. Common table expressions
C. Views
D. Temporary tables

Correct Answer: C


Free Microsoft 70-761 Sample Quesiton 1:
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

Correct Answer: AC

Latest Microsoft 70-761 Practice Question 2:
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.
Multiple processes use the data from a table named Sales and place it in other databases across the organization. Some of the processes are not completely aware of the data types in the Sales table. This leads to data type conversion errors.
You need to implement a method that returns a NULL value id data conversion fails instead of throwing an error.
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

Correct Answer: H

Free 70-761 Exam Dumps PDF 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 have a database that contains several connected tables. The tables contain sales data for customers in the United States only.
All the sales data is stored in a table named table1. You have a table named table2 that contains city names.
You need to create a query that lists only the cities that have no sales.
Which statement clause should you add to the query?

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

Correct Answer: D

New 70-761 Exam Dumps Demo Question 4:
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

Correct Answer: C

Download 70-761 PDF Question 5:
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 are building a stored procedure that will be used by hundreds of users concurrently.
You need to store rows that will be processed later by the stored procedure. The object that stores the rows must meet the following requirements:
Be indexable
Contain up-to-date statistics
Be able to scale between 10 and 100,000 rows
The solution must prevent users from accessing one another’s data.
Solution: You create a table variable in the stored procedure.
Does this meet the goal?

A. Yes
B. No

Correct Answer: B

Free Download Microsoft 70-761 Demo Question 6:
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 are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a hash index on the primary key column.
Does this meet the goal?

A. Yes
B. No

Correct Answer: B

Microsoft Certification 70-761 Practice Question 7:
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 are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a clustered index on the primary key column.
Does this meet the goal?

A. Yes
B. No

Correct Answer: A

Querying Data with Transact-SQL Microsoft 70-761 Online Quiz Question 8:
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 are creating indexes in a data warehouse.
You have a dimension table named Table1 that has 10,000 rows. The rows are used to generate several reports.
The reports join a column that is the primary key.
The execution plan contains bookmark lookups for Table1.
You discover that the reports run slower than expected.
You need to reduce the amount of time it takes to run the reports.
Solution: You create a nonclustered index on the primary key column that includes the bookmark lookup columns.
Does this meet the goal?

A. Yes
B. No

Correct Answer: B

Microsoft Certification 70-761 Sample Question 9:
You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.

Correct Answer: E

Free Microsoft 70-761 Querying Data with Transact-SQL Demo Question 10:
You develop a database for a travel application. You need to design tables and other database objects.
You create the Airline_Schedules table.
You need 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 DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.

Correct Answer: I


Free Microsoft 70-761 Sample Quesiton 1:
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 create a table named Products by running the following Transact-SQL statement:

You have the following stored procedure:

You need to modify the stored procedure to meet the following new requirements:

  • Insert product records as a single unit of work.
  • Return error number 51000 when a product fails to insert into the database.
  • If a product record insert operation fails, the product information must not be permanently written to the database.
    Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

A. Yes
B. No

Answer: B

Latest Microsoft 70-761 Practice Question 2:
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 create a table named Products by running the following Transact-SQL statement:

You have the following stored procedure:

You need to modify the stored procedure to meet the following new requirements:

  • Insert product records as a single unit of work.
  • Return error number 51000 when a product fails to insert into the database.
  • If a product record insert operation fails, the product information must not be permanently written to the database.
    Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

A. Yes
B. No

Answer: B

Free 70-761 Exam Dumps PDF Question 3:
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 create a table named Products by running the following Transact-SQL statement:

 

You have the following stored procedure:

 

You need to modify the stored procedure to meet the following new requirements:

  • Insert product records as a single unit of work.
  • Return error number 51000 when a product fails to insert into the database.
  • If a product record insert operation fails, the product information must not be permanently written to the database.
    Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

A. Yes
B. No

Answer: A

 

New 70-761 Exam Dumps Demo 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 create a table named Customer by running the following Transact-SQL statement:

You must insert the following data into the Customer table:

 

You need to ensure that both records are inserted or neither record is inserted.
Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

A. Yes
B. No

Answer: B

Download 70-761 PDF Question 5:
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 create a table named Customer by running the following Transact-SQL statement:

You must insert the following data into the Customer table:

You need to ensure that both records are inserted or neither record is inserted.
Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

A. Yes
B. No

Answer: B

Free Download Microsoft 70-761 Demo Question 6:
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 create a table named Customer by running the following Transact-SQL statement:

You must insert the following data into the Customer table:

You need to ensure that both records are inserted or neither record is inserted.
Solution: You run the following Transact-SQL statement:

 

Does the solution meet the goal?

A. Yes
B. No

Answer: A

Microsoft Other Certification 70-761 Practice Question 7:
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 Americ
a. 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

Answer: A

Querying Data with Transact-SQL Microsoft 70-761 Online Quiz Question 8:
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 Americ
a. 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

Answer: B

Microsoft Other Certification 70-761 Sample Question 9:
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 Americ
a. 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

Answer: B

Free Microsoft 70-761 Querying Data with Transact-SQL Demo Question 10:
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:

You plan to run the following query to update tasks that are not yet started:

 

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

Answer: B


page 1 from 1

Recent Post