Microsoft 070-461 Actual Free Exam Questions & Community Discussion

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another.
Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.
You need to ensure that your query executes in the minimum possible time.
Which query should you use?

You plan to write a query for a new business report that will contain several nested queries.
You need to ensure that a nested query can call a table-valued function for each row in the main query.
Which query operator should you use in the nested query?

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 Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:

You need to ensure that the minimum amount of disk space is used to store the data in the Product table.
What should you do?

You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements.
There is high contention between readers and writers on several tables used by your transaction.
You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries.
Which isolation level should you use?

You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table.
You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:
* Always starts with 1.
* Starts again from 1 after it reaches 100.
Which Transact-SQL statement should you use?

Your Microsoft SQL Server database contains tables as shown below.
You have tables that were created by running the following Transact-SQL statements:

The Product table contains 10,000 records. The maximum ProductID is 11,000.
There are 12 rows in the Category table. The maximum CategoryID is 12.
The Product table contains at least one product in every category.
Data in the tables was accidently modified. To correct this, you need to make some updates directly to the tables. You issue several statements.
Which result or results will you obtain for each Transact-SQL statement? To answer, drag the appropriate results to the correct Transact-SQL statements. Each result 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 develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2.
You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases.
You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.
Which Transact-SQL statement should you use?

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.
You need to ensure that the following requirements are met:
* Students must be ranked based on their average marks.
* If one or more students have the same average, incremental ranks must be given based on the order they are created.
* Ranks must be sequential without gaps in between.
Which Transact-SQL query should you use?