It is impossible to pass Microsoft mcsa 70 461 exam without any help in the short term. Come to Ucertify soon and find the most advanced, correct and guaranteed Microsoft exam 70 461 practice questions. You will get a surprising result by our Improved Querying Microsoft SQL Server 2012 practice guides.

Q21. You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products table has the following definition: 

You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated. 

Which Transact-SQL query should you use? 

A. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice) - - Create Audit Records 

B. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR EXISTS (SELECT WholeSalePnce FROM inserted) - - Create Audit Records 

C. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED(RetailPrice, WholesalePrice) - - Create Audit Records 

D. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice) - - Create Audit Records 

Answer:


Q22. You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. 

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? 

A. HOLDLOCK 

B. ROWLOCK 

C. XLOCK 

D. UPDLOCK 

E. TABLOCK 

Answer:


Q23. 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.) 

Unless stated above, no columns in the Employee table reference other tables. 

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. 

You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. 

On which column in the Employee table should you create a Foreign Key constraint that references a different table in the database? 

A. DateHired 

B. Departments 

C. EmployeeID 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

Answer:

Explanation: 

Use the EmployeeID, which would be used as a primary key in the Employee table, when defining a foreign key constraint from another table in the database. 


Q24. You develop a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerId. 

You need to create a query that meets the following requirements: 

. Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. . Results must not include customers who have not placed any orders. 

Which Transact-SQL query should you use? 

A. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CuscomerlD = Orders.CustomerId 

B. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerId 

C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerId = Orders.CustomerId 

D. SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId 

Answer:


Q25. 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? 

A. SET XACT_ABORT ON 

B. SET ARITHABORT ON 

C. TRY 

D. BEGIN 

E. SET ARITHABORT OFF 

F. SET XACT_ABORT OFF 

Answer:


Q26. Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. 

The business team wants a report that displays the total number of orders placed on the current day. 

You need to write a query that will return the correct results in the most efficient manner. 

Which Transact-SQL query should you use? 

A. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime = CONVERT(DATE, GETDATE()) 

B. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime = GETDATE() 

C. SELECT COUNT(*) FROM SalesOrders 

WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 

112)) 

D. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime >= CONVERT(DATE, GETDATE()) 

AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) 

Answer:


Q27. CORRECT TEXT 

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 answer, type the correct code in the answer area. 

Answer: 


Q28. Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmplD parameter. 

You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence. 

Which approach should you use? 

A. A SELECT statement that includes CASE 

B. Cursor 

C. BULK INSERT 

D. View 

E. A user-defined function 

Answer:

Explanation: SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. 


Q29. CORRECT TEXT 

You need to create a query that calculates the total sales of each OrderlD from a table named Sales.Details. The table contains two columns named OrderlD and ExtendedAmount. 

The solution must meet the following requirements: 

Use one-part names to reference columns. 

Start the order of the results from OrderlD. 

NOT depend on the default schema of a user. 

Use an alias of TotalSales for the calculated ExtendedAmount. 

Display only the OrderlD column and the calculated TotalSales column. 

Provide the correct code in the answer area. 

Answer: 


Q30. You are developing a database application by using Microsoft SQL Server 2012. 

An application that uses a database begins to run slowly. 

Your investigation shows the root cause is a query against a read-only table that has a 

clustered index. 

The query returns the following six columns: 

... 

One column in its WHERE clause contained in a non-clustered index · Four additional columns One COUNT (*) column based on a grouping of the four additional columns 

You need to optimize the statement. 

What should you do? 

A. Add a HASH hint to the query. 

B. Add a LOOP hint to the query. 

C. Add a FORCESEEK hint to the query. 

D. Add an INCLUDE clause to the index. 

E. Add a FORCESCAN hint to the Attach query. 

F. Add a columnstore index to cover the query. 

G. Enable the optimize for ad hoc workloads option. 

H. Cover the unique clustered index with a columnstore index. 

I. Include a SET FORCEPLAN ON statement before you run the query. 

J. Include a SET STATISTICS PROFILE ON statement before you run the query. 

K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query. 

L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query. 

M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query. 

N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query. 

Answer: