2013 Latest MCSA 70-461 Exam Questions 41-45

Ensurepass QUESTION 41 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 Read more [...]

2013 Latest MCSA 70-461 Exam Questions 36-40

Ensurepass QUESTION 36 A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its preceding year. Which Transact-SQL query should you use? A. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits B. SELECT Territory, Year, Profit, LAG(Profit, Read more [...]

2013 Latest MCSA 70-461 Exam Questions 31-35

Ensurepass QUESTION 31 You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements: Status information must be logged to a status table. If the status table does not exist at the beginning of the batch, it must be created. Which object should you use? A. Scalar user-defined function B. Inline user-defined function C. Table-valued user-defined function D. Stored procedure Correct Answer: D Explanation/Reference: Verified answer Read more [...]

2013 Latest MCSA 70-461 Exam Questions 26-30

Ensurepass QUESTION 26 You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) EmTmp " Column Nam Condensed Type EmployeeIDint EmployeeNumchar{lO) LastNamenv har(21 ) FirstNamenvarchar(2 }_ MiddlNamnvarchar(200) DatHireddate DepartmentIDint JobTrtJevarchar(200) RportsToJD nt Col umn name Oescriptjon EmployeeID(pk) uniquely identifies the employee record in the table Used Read more [...]

2013 Latest MCSA 70-461 Exam Questions 21-25

Ensurepass QUESTION 21 You have a database that contains the tables as shown below: You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column Read more [...]

2013 Latest MCSA 70-461 Exam Questions 16-20

Ensurepass QUESTION 16 You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.) You have the following query: You need to recreate the query to meet the following requirements: Reference columns by using one-part names only. Sort aggregates by SalesTerritoryID, and then by ProductID. Order the results in descending order from SalesTerritoryID to ProductID. The solution must use the existing SELECT clause and FROM clause. Which code segment should you use? To Read more [...]

2013 Latest MCSA 70-461 Exam Questions 11-15

Ensurepass QUESTION 11 You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. You need to ensure that users are able to modify data by using the view. What should you do? A. Create an AFTER trigger on the view. B. Modify the view to use the WITH VIEW_METADATA clause. C. Create an INSTEAD OF trigger on the view. D. Modify the view to an indexed view. Correct Answer: C Explanation/Reference: Verified answer Read more [...]

2013 Latest MCSA 70-461 Exam Questions 96-199

Ensurepass QUESTION 1 You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use? A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID Read more [...]

2013 Latest MCSA 70-461 Exam Questions 6-10

Ensurepass QUESTION 6 You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition: You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use? A. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber) - - Create Audit Records B. CREATE TRIGGER TrgPhoneNumberChange ON Customers Read more [...]

2013 Latest MCSA 70-461 Exam Questions 91-95

Ensurepass QUESTION 6 You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements: Displays the details of only students from Canada. Allows insertion of details of only students from Canada. 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.) Build List and Reorder: Correct Answer: Explanation/Reference: Verified Read more [...]

2013 Latest MCSA 70-461 Exam Questions 86-90

Ensurepass QUESTION 1 You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you Read more [...]

2013 Latest MCSA 70-461 Exam Questions 81-85

Ensurepass QUESTION 36 You use a Microsoft SQL Server 2012 database. You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer. Which four T-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.) Build List and Reorder: Correct Answer: Explanation/Reference: Reference: http://msdn.microsoft.com/en-us/library/ms191432.aspx QUESTION Read more [...]
1 2