Microsoft Querying Data with Transact-SQL - 070-761

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 Courses
  FOR JSON AUTO, INCLUDE_NULL_VALUES('Courses')
• B. SELECT CourseID AS [Course ID], Course as Name
  FROM Courses
  FOR JSON PATH('Courses')
• C. SELECT CourseID AS [Course ID], Course AS Name
  FROM Courses
  FOR JSON AUTO, ROOT('Courses')
• D. SELECT CourseID AS 'Course ID', Course AS Name
  FROM Courses
  FOR JSON ROOT('Courses')

You have a database that contains the following tables.

You need to create a query that lists the lowest-performing salespersons based on the current year-to-date sales period. The query must meet the following requirements:
- Return a column named Fullname that includes the salesperson FirstName, a space, and then LastName.
- Include the current year-to-date sales for each salesperson.
- Display only data for the three salespersons with the lowest year-to-year sales values.
- Exclude salespersons that have no value for TerritoryID.
Construct the query using the following guidelines:
- Use the first letter of a table name as the table alias.
- Use two-part column names.
- Do not surround object names with square brackets.
- Do not use implicit joins.
- Use only single quotes for literal text.
- Use aliases only if required.

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.

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:

Users report performance issues when they run the following query:

You need to improve query performance and limit results to projects that specify an end date.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

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

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 does NOT include columns.
Does this meet the goal?

• A. NO
• B. YES