70-461 Querying Microsoft SQL Server 2012

QUESTION 1
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.)
Build List and Reorder:

Answer:

---

QUESTION 2
You use Microsoft SQL Server 2012 to develop a database that has two tables named Div1Cust and
Div2Cust. Each table has columns named DivisionID and CustomerId . None of the rows in Div1Cust
exist in Div2Cust. You need to write a query that meets the following requirements:
* The rows in Div1Cust must be combined with the rows in Div2Cust.
* The result set must have columns named Division and Customer.
* Duplicates must be retained.
Which three Transact-SQL statements should you use? (To answer, move the appropriate
statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:

Answer:

---

QUESTION 3
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

---

QUESTION 4
You develop a Microsoft SQL Server 2012 database. The database is used by two web applications
that access a table named Products. You want to create an object that will prevent the applications
from accessing the table directly while still providing access to the required data. You need to
ensure that the following requirements are met:
* Future modifications to the table definition will not affect the applications’ ability to access
data.
* The new object can accommodate data retrieval and data modification.
* You need to achieve this goal by using the minimum amount of changes to the existing
applications.
What should you create for each application?

A. views
B. table partitions
C. table-valued functions
D. stored procedures

Answer: A

---

QUESTION 5
You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets
the following requirements:
* Returns a result set based on supplied parameters.
* Enables the returned result set to perform a join with a table.
Which object should you use?

A. Inline user-defined function
B. Stored procedure
C. Table-valued user-defined function
D. Scalar user-defined function

Answer: C

Best Microsoft MCSE: Business Intelligence Certification, Microsoft 70-461 Training at certkingdom.com