Microsoft Querying Microsoft SQL Server 2012/2014 - 070-461 Exam Practice Test

You are working with a table that has an XML column that contains information about books. Each book may have an associated price.
You need to write a query that returns each author on a separate row in XML format.
Which XML method should you use?

You create a view based on the following statement:

You grant the Select permission to User1 for this view.
You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

You have a SQL Server database that contains all of the sales data for your company.
You need to create a query that returns the customers who represent the top five percent of the total actual sales.
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.

You are developing a database application by using Microsoft SQL Server 2012.
You have a query that runs slower than expected.
You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer.
What should you do?

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical.
You write the following Transact-SQL query:
* INSERT INTO OrdersHistorical
* SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?

You have a view that was created by using the following code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory, which must meet the following requirements:
* Accept the @T integer parameter.
* Use one-part names to reference columns.
* Filter the query results by SalesTerritoryID.
* Return the columns in the same order as the order used in OrdersByTerritoryView.
Which code segment should you use?
To answer, type the correct code in the answer area.

You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:

Id is the Primary Key.
You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime.
Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

You develop a Microsoft SQL Server database. You design a table to store product information. The table is defined as follows:

You need to meet the following requirements:
* If a product has a product category value "REGULAR", the product should have a sub-category with a length of two characters.
* If a product has the product category other than "REGULAR", the product may or may not have a sub-category.
Which Transact-SQL statement should you use?

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 run-time error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

You administer a SQL Server database that tracks sales that are made by sales persons. The database contains a table that is defined by the following Transact-SQL statements:

You have the following requirements:
* accept a datetime value for the query month
* return a list of salespeople IDs who have sales in the query month or before the query month
* compare sales with sales quota for salespeople who have a sales quota
* display year-to-date sales for salespeople that do not have a sales quota How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each 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.

You have a database application that uses Microsoft SQL Server 2012. You have a query named Query1 that returns four columns from a frequently updated table that has a clustered index. Three of the columns are referenced in the WHERE clause of the query. The three columns are part of a non-clustered index. The fourth column is not referenced in the WHERE clause.
Users report that the application begins to run slowly. You determine that the root cause for the performance issue is Query1.
You need to optimize the statement.
What should you do?