Name: Microsoft 70-461 Exam
Brand: Pass4Success
SKU: 70-461
Price: 69.00 USD
Availability: InStock
Rating: 4.8 (165 reviews)

Disscuss Microsoft 70-461 Topics, Questions or Ask Anything Related

Submit Cancel

Currently there are no comments in this discussion, be the first to comment!

Free Microsoft 70-461 Exam Actual Questions

Note: Premium Questions for 70-461 were last updated On 01-01-1970 (see below)

Question #1

You generate a daily report according to the following query:

The Sales.ufn.GetLastOrderDate user-defined function (UDF) is defined as follows:

You need to improve the performance of the query.

What should you do?

ARewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE (SELECT OrderDate FROM Sales.ufnGetLastOrderDate(c.CustomerID)) <DATEADD(DAY, -90, GETDATE())
Rewrite the UDF as follows:
CREATE FUNCTION Sales.ufnGetLastOrderDate(@CustomerID int)
RETURNS @OrderTable TABLE (OrderDate datetime)
AS
BEGIN
INSERT @OrderTable
SELECT MAX(s.OrderDate) As OrderDate
FROM Sales.SalesOrder s
WHERE s.CustomerID = @CustomerID
RETURN
END

BDrop the UDF and rewrite the report query as follows:
WITH cte (CustomerID, LastOrderDate) AS (
SELECT CustomerID, MAX (OrderDate) AS [LastOrderDate]
FROM Sales.SalesOrder
GROUP BY CustomerID
)
SELECT c.CustomerName
FROM cte
INNER JOIN Sales.Customer c ON cte.CustomerID = c.CustomerID
WHERE cte.LastOrderDate < DATEADD(DAY, -90 GETDATE())

CDrop the UDF and rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (
SELECT s.OrderDate
FROM Sales.SalesOrder s
WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())
AND s.CustomerID = c.CustomerID)

DRewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE (SELECT OrderDate FROM Sales.ufnGetLastOrderDate(c.CustomerID)) < DATEADD(DAY, -90, GETDATE())
Rewrite the UDF as follows:
CREATE FUNCTION Sales.ufnGetLastOrderDate(@CustomerID int)
RETURNS @OrderTable TABLE (OrderDate datetime)
AS
BEGIN
INSERT @OrderTable
SELECT MAX(s.OrderDate) As OrderDate
FROM Sales.SalesOrder s
WHERE s.CustomerID = @CustomerID
RETURN
END

Reveal Solution

Correct Answer: B

We use a Common Table Expression (TBE) to optimize the query with a predicate pushdown.

Predicate pushdown is an optimization that applies conditions (predicates) as early as possible --- ideally preventing any loading of unneeded rows.

References:

https://modern-sql.com/feature/with/performance

Question #2

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

CIF Discontinued = 0

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

Reveal Solution

Correct 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 #3

You develop a Microsoft SQL Server database. The database contains a table defined by the following

Transact-SQL statement:

All regions have more than one employee.

You need to write a query to find the employee with the highest SalesYTD in each region with the following result set:

* First Name

* Last Name

* Region

* Sales YTD

Which Transact-SQL query should you run?

AOption

BOption

COption

DOption

Reveal Solution

Correct Answer: A

Use TOP 1 and RANK() OVER (PARTITION BY).

References: https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-2017

Question #4

You are working with a table that has an XML column that contains information about books. Each book may have an associated price.

You need to write a query that returns each author on a separate row in XML format.

Which XML method should you use?

AValue()

BNodes()

CQuery()

DExist()

Reveal Solution

Correct Answer: B

The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.

The result of the nodes() method is a rowset that contains logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes identified with the query expression, so that subsequent queries can navigate relative to these context nodes.

Incorrect Answers:

A: The Value() method performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value.

C: The Query() method specifies an XQuery against an instance of the xml data type. The result is of xml type. The method returns an instance of untyped XML.

D: The Exists() method returns a bit that represents one of the following conditions:

1, representing True, if the XQuery expression in a query returns a nonempty result. That is, it returns at least one XML node.

0, representing False, if it returns an empty result.

NULL if the xml data type instance against which the query was executed contains NULL.

References: https://docs.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type?view=sql-server-2017

Question #5

You administer a Microsoft SQL Server instance that will support several databases.

You need to ensure that every new database created has a data type named postalcode that contains the

same attributes.

What should you do?

ACreate a user-defined type on the model database.

BCreate a user-defined type on the master database.

CCreate a user-defined data type on the master database.

DCreate a user-defined data type on the model database.

Reveal Solution

Correct Answer: D

One option is to create SQL Server user defined data types.

One trick with new databases is to create the objects in the model database, so as new databases are created the user defined data types will automatically be available.

References: https://www.mssqltips.com/sqlservertip/1628/sql-server-user-defined-data-types-rules-and-defaults/

Unlock Premium 70-461 Exam Questions with Advanced Practice Test Features:

Select Question Types you want
Set your Desired Pass Percentage
Allocate Time (Hours : Minutes)
Create Multiple Practice tests with Limited Questions
Customer Support

Get Full Access Now

Microsoft 70-461 Exam Questions

Free Microsoft 70-461 Exam Actual Questions

Note: Premium Questions for 70-461 were last updated On 01-01-1970 (see below)