2013 Latest MCSA 70-461 Exam Questions 76-80

Ensurepass

QUESTION 31
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 applications.
What should you create for each application? A. Synonyms
B. Common table expressions
C. Views
D. Temporary tables
Correct Answer: C
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms190174.aspx

QUESTION 32
You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:
Takes an input variable
Returns a table of values
Cannot be referenced within a view
Which object should you use? A. Scalar-valued function
B. Inline function
C. User-defined data type
D. Stored procedure
Correct Answer: D
Explanation/Reference:

QUESTION 33
You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions:
Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders. Which Transact-SQL query do you use?
A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(OrderAmount) DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM
(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs
WHERE Rnk = 1
C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY OrderAmount DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
COUNT(OrderAmount) DESC) AS OrderAmount
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC
Correct Answer: A
Explanation/Reference:
Verified answer as correct.

QUESTION 34
You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains
data. BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. You need to ensure that the new column is assigned a GUID for existing rows. Which four 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:
Correct Answer:

Explanation/Reference:
Verified answer as correct. Actually, in the real world, you don’t have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it.
Reference: http://msdn.microsoft.com/en-us/library/ms190273.aspx

QUESTION 35
You create a view based on the following statement:
You grant the Select permission to User1 for this view. You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1. Which four 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:
Correct Answer:

Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/hh213020.aspx Reference: http://msdn.microsoft.com/en-us/library/ms186819.aspx Reference: http://msdn.microsoft.com/en-us/library/ms173846.aspx

Download Ensurepass Latest 2013 MCSA 70-461 Real Exam Questions , help you to pass exam 100%.