70-461 Free Practice Test

- (Exam Topic 2)
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit.
You need to create a report that displays the profits made by each territory for each year and its previous year. Which Transact-SQL query should you use?

1. A. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
2. B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
3. C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
4. D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits

Correct Answer: C
LAG accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2016. LAG provides access to a row at a given physical offset that comes before the current row. Usethis analytic function in a SELECT statement to compare values in the current row with values in a previous row.
Use ORDER BY Year, not ORDER BY Territory.
Example: The following example uses the LAG function to return the difference in sales quotas fora specific employee over previous years. Notice that because there is no lag value available for the first row, the default of zero (0) is returned.
USE AdventureWorks2012; GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN (\'2005\',\'2006\');

- (Exam Topic 1)
You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly.
You discover that during reads, the transaction experiences blocking from concurrent updates. You need to ensure that throughout the transaction the data maintains the original version. What should you do?

1. A. Add a HASH hint to the query.
2. B. Add a LOOP hint to the query.
3. C. Add a FORCESEEK hint to the query.
4. D. Add an INCLUDE clause to the index.
5. E. Add a FORCESCAN hint to the Attach query.
6. F. Add a columnstore index to cover the query.
7. G. Enable the optimize for ad hoc workloads option.
8. H. Cover the unique clustered index with a columnstore index.
9. I. Include a SET FORCEPLAN ON statement before you run the query.
10. J. Include a SET STATISTICS PROFILE ON statement before you run the query.
11. K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
12. L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
13. M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
14. N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Correct Answer: M

- (Exam Topic 1)
You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)
< ><>>< ><>

1. A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML RAW
2. B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS
3. C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO
4. D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO, ELEMENTS
5. E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO
6. F. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO, ELEMENTS
7. G. SELECT Name AS \'@Name\', Country AS \'@Country\', OrderId, OrderDate, Amount FROM OrdersINNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML PATH (\'Customers\')
8. H. SELECT Name AS \'Customers/Name\', Country AS \'Customers/Country\', OrderId, OrderDate, Amount FROM OrdersINNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML PATH (\'Customers\')

Correct Answer: G

- (Exam Topic 1)
You have a view that was created by using the following code:
< ><>>< > >< > >>< >
Solution:
CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE
AS RETURN ( SELECT
OrderID, OrderDate, SalesTerritoryID, TotalDue
FROM Sales.OrdersByTerritory WHERE SalesTerritoryID=@T
)

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 1)
You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named invoice. The solution must ensure that the invoice is validated by using Sales.InvoiceSchema.
The solution must ensure that the invoice variable is validated by using Sales.InvoiceSchema schema. Provide the correct code in the answer area.
Solution:
DECLARE @invoice XML(Sales.InvoiceSchema)

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A