Microsoft 070-761 Value Pack (Frequently Bought Together)

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. Yes
• B. No

You have a database that includes the following tables:

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.

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 memory-optimized table that has updated statistics
• B. an extended stored procedure
• C. a user-defined table-valued function
• D. a user-defined stored procedure that has an OUTPUT parameter

You are creating a database solution to track sales achievements of your training courses. You run the following statements:

You plan to add courses to a table named HighlightedCourses. You must add courses that have been delivered to more than 100 participants only.
If the total number of participants for a course is lower than 100, the course must not be added to the HighlightedCourses table. In addition, an error message must be displayed and remaining Transact-SQL code must not run.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.

You are building a stored procedure that will update data in a table named Table1 by using a complex query as the data source.
You need to ensure that the SELECT statement in the stored procedure meets the following requirements:
* Data being processed must be usable in several statements in the stored procedure.
* Data being processed must contain statistics.
What should you do?

• A. Insert the data into a table variable, and then update Table1 from the table variable.
• B. Insert the data into a temporary table, and then update Table1 from the temporary table.
• C. Update Table1 by using a common table expression (CTE).
• D. Place the SELECT statement in a derived table, and then update Table1 by using a JOIN to the derived table.