Microsoft 70-461 Exam Practice Test Instant Access

Question 1

You administer a Microsoft SQL Server database that contains a table named Products.

In a bit column named Discontinued, a value of 1 indicates a product is inactive, and a value of 0 indicates the product is active.

You need to create a view that returns a column with a full product status description.

Which expression should you use to define the column?

AIF(Discontinued = 0, 'Active', 'Inactive')

BCASE Discontinued
WHEN 0 THEN 'Active'
WHEN 1 THEN 'Inactive'
END

CIF Discontinued = 0
'Active'
ELSE
'Inactive'

DDECODE (Discontinued, 0, 'Active', 1, 'Inactive', 'Unknown')

Answer : B

The CASE statement evaluates a list of conditions and returns one of multiple possible result expressions.

The CASE expression has two formats:

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

CASE can be used in any statement or clause that allows a valid expression.

References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

Question 2

You develop a stored procedure for a wholesale glass manufacturing company. The stored procedure uses a cursor to read all row-based combinations of the following tables:

You observe that the stored procedure returns results very slowly.

You need to improve the execution speed of the stored procedure.

Which cursor type should you use?

AGLOBAL FORWARD_ONLY

BLOCAL FAST_FORWARD

CLOCAL STATIC READ_ONLY FORWARD_ONLY

DGLOBAL STATIC

Answer : B

FAST_FORWARD specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.

References: https://msdn.microsoft.com/en-us/library/ms180169.aspx

Question 3

You need to build a table structure for a stored procedure to support data entry from a website form. The website form must meet the following requirements:

Which two actions should you perform? Each correct answer presents part of the solution.

AAdd the CHECK constraint to the table structure.

BAdd the DATEPART function to the stored procedure.

CAdd the DEFAULT constraint to the table structure.

DAdd the SYSDATETIMEOFFSET function to the stored procedure.

EAdd the ISDATE function to the stored procedure.

Answer : D, E

SYSDATETIMEOFFSET returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running.

ISDATE returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

References:https://msdn.microsoft.com/en-us/library/bb677334.aspx

Question 4

You have a SQL Server database that contains all of the sales data for your company.

You need to create a query to update the rows in a table. The solution must write original and updated values to a separate table.

Which clause should you add to the UPDATE statement in the query?

AINSERT

BOUTPUT

CWRITETEXT

DReadText

Answer : D

Question 5

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?

AFloat

BMoney

CSmallmoney

DNumeric

Answer : D

Numeric is the only one in the list that can give a fixed precision and scale.

Question 6

You need to create a query that meets the following requirements:

* The query must return a list of salespeople ranked by amount of sales and organized by postal code.

* The salesperson who has the highest amount of sales must be ranked first.

Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within code that has been provided as well as below it.

Use the 'Check Syntax' button to verify your work. Any syntax or spelling errors will be reported by line and character position.

A1 SELECT RowNumber() OVER(PARTITION BY PostalCode ORDER BY SalesYTd DESC) AS 'Ranking',
2 p.LastName, s.SalesYTD, a.PostalCode
3 FROM Sales.SalesPerson AS a
etc
More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
SYNTAX for OVER:
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
Example: Using the OVER clause with the ROW_NUMBER function
The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName, LastName,
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Here is a partial result set.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00

B1 SELECT RowNumber() OVER(PARTITION BY PostalCode ORDER BY SalesYTd DESC) AS 'Ranking',
2 p.LastName, s.SalesYTD, a.PostalCode
3 FROM Sales.SalesPerson AS a
etc
On line 1 add: RowNumber
One line 1 add: PARTITION BY
ROW_NUMBER() numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
SYNTAX for OVER:
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
Example: Using the OVER clause with the ROW_NUMBER function
The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName, LastName,
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Here is a partial result set.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00

Answer : B

References:

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

Question 7

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

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

ASELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW

BSELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers=CustomerId = 1
FOR XML RAW, ELEMENTS

CSELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

DSELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

ESELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

FSELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

GSELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH ('Customers')

HSELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId,
OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH ('Customers')

Answer : C

http://msdn.microsoft.com/en-us/library/ms188273.aspx

Microsoft Querying Microsoft SQL Server 2012/2014 70-461 Exam Practice Test