Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 111:

  Which SET session options are useful for query optimization? (Choose all that apply.)

  A. SET STATISTICS IO
  B. SET STATISTICS EXECUTION_DETAILS
  C. SET IDENTITY_INSERT
  D. SET STATISTICS TIME
  A. SET STATISTICS IO
  D. SET STATISTICS TIME

• Question 112:

  Which form of the proximity term defines the distance and the order?

  A. NEAR((SearchWord1, SearchWord2), 5, TRUE)
  B. NEAR((SearchWord1, SearchWord2), CLOSE, ORDER)
  C. NEAR((SearchWord1, SearchWord2), 5)
  D. NEAR(SearchWord1, SearchWord2)
  A. NEAR((SearchWord1, SearchWord2), 5, TRUE)

  ---

  you want to find documents where "dog" and "cat" appear within 4 words of each other, and where "dog" appears before "cat":

  'NEAR((dog, cat), 4, TRUE)'

• Question 113:

  You support a database structure shown in the exhibit.

  

  You need to write a query that displays the following details:

  1.

  Total sales made by sales people, year, city, and country

  2.

  Sub totals only at the city level and country level

  3.

  A grand total of the sales amount

  Which Transact-SQL query should you use?

  A. SELECT SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS TotalFROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonId = SalesPerson.SalesPersonId GROUP BY SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate)
  B. SELECT SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS TotalFROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonId = SalesPerson.SalesPersonId GROUP BY ROLLUP(SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate))
  C. SELECT SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS TotalFROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonId = SalesPerson.SalesPersonId GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate),City, Country)ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
  D. SELECT SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate), Sum(Amount)FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonId = SalesPerson.SalesPersonId GROUP BY SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate),GROUPING SETS((Country, City, DatePart(yyyy, SaleDate)),( Country, City) (Country),())
  D. SELECT SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate), Sum(Amount)FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonId = SalesPerson.SalesPersonId GROUP BY SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate),GROUPING SETS((Country, City, DatePart(yyyy, SaleDate)),( Country, City) (Country),())

  ---

  Be careful with this question, because on exam can be different options for answer. Reference: http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/ Reference: http://msdn.microsoft.com/en-us/library/ms177673.aspx

• Question 114:

  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?

  A. Value()
  B. Nodes()
  C. Query()
  D. Exist()
  B. Nodes()

  ---

  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 115:

  Which node type test can be used to retrieve all nodes of an XML instance?

  A. Asterisk (*)
  B. comment()
  C. node()
  D. text()
  C. node()

• Question 116:

  DRAG DROP

  You use a Microsoft Azure SQL Database instance named ContosoDb. ContosoDb contains a table named Products that has existing records.

  The Products table has columns named Code and QuantityOnHand.

  You need to create a new column in the Products table named Category that allows null values and sets the value of the Category column to General for all existing records.

  Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  NULL or NOT NULL specifies whether the column can accept null values. Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified or if the table is empty. NOT NULL can be specified for computed columns only if PERSISTED is also specified. If the new column allows null values and no default is specified, the new column contains a null value for each row in the table. If the new column allows null values and a default definition is added with the new column, WITH VALUES can be used to store the default value in the new column for each existing row in the table.

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

• Question 117:

  You work as a SQL Server 2012 database developer at ABC.com. You are developing a stored procedure that updates rows in several tables.

  You want the entire transaction to be rolled back should the stored procedure cause a run-time error.

  How would you accomplish this?

  A. You should make use of the SET XACT_ABORT ON statement in the stored procedure.
  B. You should have the stored procedure run in the SERIALIZABLE ISOLATION LEVEL.
  C. You should make use of a LOOP hint in the stored procedure.
  D. You should have the stored procedure run in the SNAPSHOT ISOLATION LEVEL.
  E. You should make use of an INSTEAD OF UPDATE trigger in the stored procedure.
  F. You should make use of RAISERROR in the stored procedure.
  A. You should make use of the SET XACT_ABORT ON statement in the stored procedure.

  ---

  Ref: http://msdn.microsoft.com/en-us/library/ms188792

• Question 118:

  Which of the following is an example of a scalar subquery?

  A. select max(price) from products
  B. select sum(price) from products
  C. select min(price) from products
  D. All of these
  D. All of these

• Question 119:

  HOTSPOT

  You are designing an order entry system that uses an SQL Server database. The following tables exist in the Purchasing database:

  

  You create the following trigger. Line numbers are included for reference only.

  

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  Hot Area:

  

  

• Question 120:

  How do the COMMIT and ROLLBACK commands work with nested transactions in T-SQL? (Choose all that apply.)

  A. A single COMMIT commits the entire nested transaction.
  B. A single ROLLBACK rolls back the entire nested transaction.
  C. A single COMMIT commits only one level of the nested transaction.
  D. A single ROLLBACK rolls back only one level of the nested transaction.
  B. A single ROLLBACK rolls back the entire nested transaction.
  C. A single COMMIT commits only one level of the nested transaction.

  ---

  Single COMMIT commits only the innermost level of a nested transaction.