Far out 70-461 book Guide

70-461 Exam

Exam Code: 70-461 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Querying Microsoft SQL Server 2012
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-461 Exam.

2021 Apr 70-461 test preparation

Q21. 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 REORGANIZE

C. ALTER INDEX ALL ON OrderDetail REBUILD

D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Answer: B

Q22. You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.

The tables have the following definitions:

Users are able to use single INSERT statements or INSERT...SELECT statements into this view.

You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.

Which Transact-SQL statement should you use?

A. CREATE TRIGGER TrgVwEmployee ON VwEmployee FOR INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END

B. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END

C. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15) SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, @EmployeeNumber = EmployeeNumber FROM inserted INSERT INTO Person(Id, FirstName, LastName) VALUES(@ID, @FirstName, @LastName) INSERT INTO Employee(PersonID, EmployeeNumber) VALUES(@PersonID, @EmployeeNumber End

D. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName FROM VwEmployee INSERT INTO Employee(PersonID, EmployeeNumber) SELECT Id, EmployeeNumber FROM VwEmployee End

Answer: B

Q23. DRAG DROP

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

Answer:

Q24. Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice.

The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.

You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table.

Which Transact-SQL query should you use?

A. UPDATE Products SET Price = Price * 1.05

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

B. UPDATE Products SET Price = Price * 1.05

OUTPUT inserted.ProductCode, inserted.Price, deleted.Price

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

C. UPDATE Products SET Price = Price * 1.05

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price *

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

D. UPDATE Products SET Price = Price * 1.05

INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice;

SELECT ProductCode, Price, Price * 1.05 FROM Products

Answer: A

Q25. You use Microsoft SQL Server 2012 database to develop a shopping cart application.

You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output.

Which Transact-SQL operator should you use?

A. CROSS JOIN

B. CROSS APPLY

C. PIVOT

D. UNPIVOT

Answer: C

Explanation:

http://technet.microsoft.com/en-us/library/ms177634.aspx

Most recent 70-461 real exam:

Q26. CORRECT TEXT

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements:

Use one-part names to reference columns.

Sort the order of the results from OrderId.

NOT depend on the default schema of a user.

Use an alias of TotalSales for the calculated ExtendedAmount.

Display only the OrderId column and the calculated TotalSales column.

.....

Which code segment should you use?

To answer, type the correct code in the answer area.

Answer:

Q27. You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition:

CREATE TABLE Inventory

(ItemID int NOT NULL PRIMARY KEY,

ItemsInStore int NOT NULL,

ItemsInWarehouse int NOT NULL)

You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row.

Which Transact-SQL statement should you use?

A. ALTER TABLE Inventory

ADD TotalItems AS ItemsInStore + ItemsInWarehouse

B. ALTER TABLE Inventory

ADD ItemsInStore - ItemsInWarehouse = TotalItemss

C. ALTER TABLE Inventory

ADD TotalItems = ItemsInStore + ItemsInWarehouse

D. ALTER TABLE Inventory

ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);

Answer: A

Q28. DRAG DROP

You use Microsoft SQL Server 2012 to develop a database application.

You create a table by using the following definition:

CREATE TABLE Prices (

PriceId int IDENTITY(1,1) PRIMARY KEY,

ActualPrice NUMERIC(16,9),

PredictedPrice NUMERIC(16,9)

)

You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index.

Which three 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.)

Answer:

Q29. You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.

You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?

A. Perform a file restore.

B. Perform a transaction log restore.

C. Perform a restore from a full backup.

D. Perform a filegroup restore.

Answer: A

Q30. You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.

The view has the following definition:

You need to ensure that users can update only the phone numbers by using this view.

What should you do?

A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.

B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.

C. Create an AFTER UPDATE trigger on the view.

D. Create an INSTEAD OF UPDATE trigger on the view.

Answer: D