Microsoft 70-761 Querying Data with Transact-SQL Exam Practice Test

A company produces and ships concrete blocks. You store information about factory and shipping centers in tables that were created by running the following Transact-SQL statements:

You must create a report that shows the regions that have a factory but do not have a shipping center.

You need to create the query for the report.

Which two Transact-SQL statements can you use? Each correct answer presents a complete solution.

NOTE: Each correct selection is worth one point.

You are building a stored procedure named sp1 that calls a stored procedure named SP2.

SP2 calls another stored procedure named SP3 that returns a Recordset. The Recordset is stored in a temporary table.

You need to ensure that SP2 returns a text value to sp1.

What should you do?

You are developing a database to track employee progress relative to training goals. You run the following Transact-SQL statements:

You must build a report that shows all Employees and the courses that they have taken. Employees that have not taken training courses must still appear in the report. The report must display NULL in the course column for these employees.

You need to create a query for the report.

A)

B)

C)

D)

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

You are developing a mobile app to manage meetups. The app allows for users to view the 25 closest people with similar interests. You have a table that contains records for approximately two million people. You create the table by running the following Transact-SQL statement:

You create the following table valued function to generate lists of people:

You need to build a report that shows meetings with at least two people only.

What should you use?

You are building a stored procedure named SP1 that calls a stored procedure named SP2.

SP2 calls another stored procedure named SP3 that returns a Recordset. The Recordset is stored in a

temporary table.

You need to ensure that SP2 returns a text value to SP1.

What should you do?

You have a project management application. The application uses a Microsoft SQL Server database to store data. You are developing a software bug tracking add-on for the application.

The add-on must meet the following requirements:

* Allow case sensitive searches for product.

* Filter search results based on exact text in the description.

* Support multibyte Unicode characters.

You run the following Transact-SQL statement:

Users connect to an instance of the bug tracking application that is hosted in New York City. Users in Seattle must be able to display the local date and time for any bugs that they create.

You need to ensure that the DateCreated column displays correctly.

Which Transact-SQL statement should you run?