Tag: 70-461 MCTS

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]