Free Microsoft 70-761 Practice Test & Real Exam Questions

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:

The variable @custID is set to a valid customer.
Does the solution meet the goal?

A company's sales team is divided in two different regions, North and South. You create tables named SalesNorth and SalesSouth. The SalesNorth table stores sales data from the North region. The SalesSouth table stores sales data from the South region. Both tables use the following structure:

You need to create a consolidated result set that includes all records from both tables.
Which Transact-SQL statement should you run?

You have a database that stored information about servers and application errors. The database contains the following tables.
Servers

Errors

You need to return all error log messages and the server where the error occurs most often.
Which Transact-SQL statement should you run?

You need to develop a function that returns a list of courses grouped by the total number of students in a course.
The function must list only courses that have more than a specific number of students. The specific number of students is defined as an input variable for the function.
How should you complete the function? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.

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 contains a single table named tblVehicleRegistration. The table is defined as follows:

You run the following query:

The query output window displays the following error message: "Conversion failed when converting the varchar value 'AB012' to data type int." You need to resolve the error.
Solution: You modify the Transact-SQL statement as follows:

Does the solution meet the goal?

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 SalesOrders.
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 2016.
Solution: You recommend the following query:

Does this meet the goal?

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.
Start of repeated scenario
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.

End of repeated scenario
You need to create a common table expression (CTE) that returns the total number of orders per year for each customer.
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.

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 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 Transact-SQL 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.

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?