Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 301:

  You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.)

  

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

  References columns by using one-part names only.

  Groups aggregates only by SalesTerritoryID, and then by ProductID.

  Orders the results in descending order by SalesTerritoryID and then by ProductID in descending order for both.

  Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

  

  Check the answer below

  SELECT SalesTerritoryID,

  ProductID,

  AVG(UnitPrice),

  MAX(OrderQty)

  MAX(DiscountAmount)

  FROM Sales.Details

  GROUP BY SalesTerritoryID, ProductID

  ORDER BY SalesTerritoryID DESC, ProductID DESC

• Question 302:

  You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

  You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements:

  1. Use one-part names to reference columns.

  2. Sort the order of the results from OrderId.

  3. NOT depend on the default schema of a user.

  4. Use an alias of TotalSales for the calculated ExtendedAmount.

  5. Display only the OrderId column and the calculated TotalSales column.

  Which code segment should you use?

  To answer, type the correct code in the answer area.

  Check the answer below

  SELECT OrderID, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details GROUP BY OrderID ORDER BY OrderID

• Question 303:

  Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You plan to build a single process for each employee that will execute the stored procedure based on the country of residence. Which approach should you use?

  A. a recursive stored procedure
  B. Trigger
  C. An UPDATE statement that includes CASE
  D. Cursor
  E. The foreach SQLCLR statement
  D. Cursor

• Question 304:

  Why is it important to prefer set-based solutions for querying tasks instead of iterative ones? (Choose all that apply.)

  A. Because set-based solutions are based on the relational model, which is the foundation of T-SQL
  B. Because set-based solutions always provide better performance than iterative solutions
  C. Because set-based solutions usually involve less code than iterative solutions
  D. Because set-based solutions enable you to rely on the order of data
  A. Because set-based solutions are based on the relational model, which is the foundation of T-SQL
  C. Because set-based solutions usually involve less code than iterative solutions

  ---

  A. Correct: Set-based solutions are based on principles from the relational model, and this model is the foundation of SQL (the standard language) and T-SQL (the dialect in SQL Server).

  B. Incorrect: Although it is not common, sometimes iterative solutions are faster than set-based ones.

  C. Correct: Because set-based solutions are declarative and iterative solutions are imperative, set-based solutions tend to involve less code.

  D. Incorrect: Set-based solutions cannot make any assumptions regarding the order of the data because sets are unordered.

• Question 305:

  Which of the following functions can be used in a CATCH block to return information about the error? (Choose all that apply.)

  A. @@ERROR
  B. ERROR_NUMBER()
  C. ERROR_MESSAGE()
  D. XACT_STATE()
  A. @@ERROR
  B. ERROR_NUMBER()
  C. ERROR_MESSAGE()
  D. XACT_STATE()

  ---

  XACT_STATE() : indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.

  1 The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.

  0 There is no active user transaction for the current request. -1 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request

  cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations

  until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

• Question 306:

  You plan to write a query for a new business report that will contain several nested queries.

  You need to ensure that a nested query can call a table-valued function for each row in the main query.

  Which query operator should you use in the nested query?

  A. CROSS APPLY
  B. INNER JOIN
  C. OUTER JOIN
  D. PIVOT
  A. CROSS APPLY

  ---

  The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

  There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSSAPPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

  References: https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

• Question 307:

  Which of the following operators work in T-SQL views? (Choose all that apply.)

  A. The WHERE clause
  B. The ORDER BY clause
  C. The UNION or UNION ALL operators
  D. The GROUP BY clause
  A. The WHERE clause
  C. The UNION or UNION ALL operators
  D. The GROUP BY clause

• Question 308:

  Which of the following is not a part of the CONTAINS predicate?

  A. FORMSOF
  B. THESAURUS
  C. NEAR
  D. PROPERTY
  E. TEMPORARY
  E. TEMPORARY

  ---

  -

  Word A single word without spaces or other punctuation. Double quotation marks are not necessary.

  ...WHERE CONTAINS ('computer')

  Phrase Multiple words or included spaces.

  ...WHERE CONTAINS

  ('"computer software"')

  Or, to use a double quote mark:

  ... WHERE CONTAINS

  ('"computer ""science"" "')

  -

  Wildcard Words or phrases with the asterisk (*) added to the end. For more information, see Using Wildcards in the CONTAINS Predicate.

  ...WHERE CONTAINS

  ('"compu*"')

  Matches "computer", "computers", "computation", and "compulsory"

  -

  Boolean Words, phrases, and wildcard strings combined by using the Boolean operators AND, OR, or NOT. Enclose the Boolean terms in double quotation marks. ...WHERE CONTAINS ('"computer monitor" AND "software program" AND "install component"')

  ... WHERE CONTAINS (' "computer" AND "software" AND "install" ' )

  ...WHERE CONTAINS ('"computer software install"')

  -

  Near Words, phrases, or wildcards separated by the function NEAR. For more information, see NEAR Term.

  ...WHERE CONTAINS

  ('"computer" NEAR "software"')

  FormsOf Matches a word and the inflectional versions of that word. For more information, see FORMSOF Term.

  ...WHERE CONTAINS

  ('FORMSOF

  (INFLECTIONAL, "happy"))

  Matches "happy", "happier",

  "happiest", "happily", and so on.

  -

  IsAbout Combines matching results over multiple word, phrase, or wildcard search terms. Each search term can optionally be weighted. You can optionally specify the rank calculation method, which combines the weights and how many of

  the items the document matches. For more information, see ISABOUT Term.

  ...WHERE CONTAINS

  ('ISABOUT ( "computer" WEIGHT (0.5) ,

  "software" WEIGHT (2.0) ,

  "development" WEIGHT (10.0)

  ) RANKMETHOD INNER PRODUCT

  ')

• Question 309:

  You maintain a SQL Server database that is used by a retailer to track sales and salespeople. The database includes two tables and two triggers that is defined by the following Transact-SQL statements:

  

  During days with a large sales volume, some new sales transaction fail and report the following error: Arithmetic overflow error converting expression to data type int.

  You need to ensure that the two triggers are applied once per sale, and that they do not interfere with each other.

  How should you complete the relevant Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location or locations. Each Transact-SQL segment may be used once, more than once, or not at all. You

  may need to drag the split bar between panes or scroll to view content.

  Select and Place:

  

  

  ---

  Box 1: OFF Box 2: 0 Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0. Note: Both DML and DDL triggers are nested whena trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. If nested triggers are allowed and a trigger in the chain starts an infinite loop, the nesting level is exceeded and the trigger terminates. References:https://msdn.microsoft.com/en-us/library/ms190739.aspx

• Question 310:

  You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions:

  

  Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders. Which Transact-SQL query do you use?

  A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1
  B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk FROM Customer c INNER JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
  C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY OrderAmount DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1
  D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, COUNT(OrderAmount) DESC) AS OrderAmount FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID ORDER BY OrderAmount DESC
  A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1