Deal of The Day! Hurry Up, Grab the Special Discount - Save 25% - Ends In 00:00:00 Coupon code: SAVE25
Welcome to Pass4Success

- Free Preparation Discussions

Microsoft 70-461 Exam

Status: RETIRED
Certification Provider: Microsoft
Exam Name: Querying Microsoft SQL Server 2012/2014
Duration: 90 Minutes
Number of questions in our database: 251
Exam Version: 01-01-1970
70-461 Exam Official Topics:
  • Topic 1: Evaluate the use of row-based operations vs. set-based operations
  • Topic 2: Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries
  • Topic 3: Difference between UNION and UNION all; case versus isnull versus coalesce
  • Topic 4: Determine the output of a setof DDL; know which SQL statements are best to solve common requirements
  • Topic 5: Understanddeterministic, non-deterministic functions/ new analytic functions; grouping sets; spatial aggregates; apply ranking functions
  • Topic 6: Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement
  • Topic 7: Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints
  • Topic 8: Create indexed views; create views without using the built in tools
Disscuss Microsoft 70-461 Topics, Questions or Ask Anything Related

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

Free Microsoft 70-461 Exam Actual Questions

The questions for 70-461 were last updated On 01-01-1970

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?

Reveal Solution Hide 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?

Reveal Solution Hide 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?

Reveal Solution Hide Solution
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?

Reveal Solution Hide 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?

Reveal Solution Hide 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 all 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

Save Cancel