70-461 Free Practice Test

- (Exam Topic 1)
You use a Microsoft SQL Server database that contains a table. The table has records of web requests as shown in the exhibit. (Click the Exhibit button.)
< ><>>< > >< > >< > >>< > >< > >Which Transact-SQL query should you use?
< ><>< ><>< ><>< ><>

1. A. Option A
2. B. Option B
3. C. Option C
4. D. Option D

Correct Answer: A
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

- (Exam Topic 1)
You develop a Microsoft SQL Server database named ContosoDb. ContosoDb contains a table named Employess that was created by using the following Transact-SQL statement:
< ><>>< >

1. A. Option A
2. B. Option B
3. C. Option C
4. D. Option D

Correct Answer: C

- (Exam Topic 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?

1. A. ALTER TABLE InventoryADD TotalItems AS ItemslnStore + ItemsInWarehouse
2. B. ALTER TABLE InventoryADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
3. C. ALTER TABLE InventoryADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
4. D. ALTER TABLE InventoryADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)

Correct Answer: B
Reference: http://msdn.microsoft.com/en-us/library/ms174979.aspx

- (Exam Topic 2)
Your database contains a table named Products that has columns named ProductID and Name.
You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time. You need to view rows 31 through 45.
Which Transact-SQL query should you create?

1. A. Option A
2. B. Option B
3. C. Option C
4. D. Option D

Correct Answer: C
The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
Example: Skip first 10 rows from the sorted resultset and return next 5 rows.
SELECT First Name + \' \' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
References: https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

- (Exam Topic 2)
You have a view that was created by using the following code:
<>>< > >< > >< > >< > >>< ><>>< >
Solution:
CREATE FUNCTION Sales.fn_OrdersByTerritory (@T integer) RETURNS TABLE
AS RETURN (SELECT
OrderID,
OrderDate, SalesTerritoryID, TotalDue
FROM Sales.OrdersByTerritory WHERE SalesTerritoryID = @T)
Reference: Inline User-Defined Functions https://technet.microsoft.com/en-us/library/ms189294(v=sql.105).aspx

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A