SQL exam 70-461 questions and answers part 1

MS SQL exam 70-461 questions and answers dumps

QUESTION 1

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. The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?

A. ALTER TABLE Inventory

ADD TotalItems AS ItemslnStore + ItemsInWarehouse

B. ALTER TABLE Inventory

ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED

C. ALTER TABLE Inventory

ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED

D. ALTER TABLE Inventory

ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)

QUESTION 2

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 = TotalItems

C. ALTER TABLE Inventory

ADD TotalItems = ItemsInStore + ItemsInWarehouse

D. ALTER TABLE Inventory

ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);

QUESTION 3

You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks:

Ÿ Joins 8 tables that contain up to 500,000 records each.

Ÿ Performs aggregations on 5 fields.

The view is frequently used in several reports. You need to improve the performance of the reports.

What should you do?

A. Convert the view into a table-valued function.

B. Convert the view into a Common Table Expression (CTE).

C. Convert the view into an indexed view.

D. Convert the view into a stored procedure and retrieve the result from the stored procedure

into a temporary table.

QUESTION 4

You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of

the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema. Which code segment should you use?

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

Correct Answer:

DECLARE @XML1 XML(Sales.InvoiceSchema)

QUESTION 5

You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to invoke a table-valued function for each row returned by a query.

Which Transact-SQL operator should you use?

A. CROSS JOIN

B. UNPIVOT

C. PIVOT

D. CROSS APPLY

QUESTION 6

You are developing a database that will contain price information. You need to store the prices that Include a fixed precision and a scale of six digits. Which data type should you use?

A. Float

B. Money

C. Smallmoney

D. Numeric

 

QUESTION 7

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?

A. USE Database2

B. EXECUTE AS OWNER

C. USE Database1

D. EXECUTE AS CALLER

QUESTION 8

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

QUESTION 9

You have 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 include customers who have not placed any orders.

Which Transact-SQL query should you use?

A. SELECT CustomerName, OrderDate

FROM Customers

RIGHT OUTER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID

B. SELECT CustomerName, CrderDate

FROM Customers

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

LEFT OUTER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID

QUESTION 10

The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails.
Which code segment should you add to line 14?

A. IF @@TRANCOUNT = 0

B. IF (XACT_STATE ( ) ) = 0

C. IF (XACT_STATE ( ) ) = 1

D. IF @@TRANCOUNT = l

 ===========================================================================

Correct Answer:

1	2	3	4	5	6	7	8	9	10
B	A	C		D	D	B	D	D	C