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