Online Access Free 070-461 Exam Questions

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.

• A.SELECT SalesTerritoryID,
  ProductID,
  AVG(UnitPrice),
  MAX(OrderQty)
  MAX(DiscountAmount)
  FROM Sales.Details
  GROUP BY SalesTerritoryID, ProductID
  ORDER BY SalesTerritoryID DESC, ProductID DESC
• B.SELECT SalesTerritoryID,
  ProductID,
  AVG(UnitPrice),
  MAX(OrderQty)
  FROM Sales.Details
  GROUP BY SalesTerritoryID, ProductID
  ORDER BY SalesTerritoryID DESC, ProductID DESC

A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history.
The Production.Product table contains the following two columns:
The Sales.SalesOrderDetail table contains the following three columns:
You need to create a query listing all of the products that were never ordered.
Which statements should you execute?

• A.Option C
• B.Option B
• C.Option A
• D.Option D

You use Microsoft SQL Server 2012 database to develop a shopping cart application.
You need to invoke a table-valued function for each row returned by a query.
Which Transact-SQL operator should you use?

• A.CROSS APPLY
• B.PIVOT
• C.UNPIVOT
• D.CROSS JOIN

You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

• A.Use the TODATETIMEOFFSET function.
• B.Use the DATETIMEOFFSET data type.
• C.Use the CAST function.
• D.Use the VARBINARY data type.
• E.Use the FORMAT function.
• F.Use a user-defined table type.
• G.Use an appropriate collation.
• H.Use the DATE data type.
• I.Use the DATETIME2 data type.
• J.Use the DATETIME data type.

You develop a Microsoft SQL Server database that contains a two tables named OrdersHistorical and CompletedOrders.
Both tables have the same schema that has the following definition:

You write the following Transact-SQL statement:

You need to ensure that data that falls into the date range of the SELECT statement cannot be inserted into CompletedOrders while the INSERT statement is running. You also need to ensure that other operations are unaffected.
Which table hint should you use?

• A.ROWLOCK
• B.TABLOCK
• C.XLOCK
• D.HOLDLOCK
• E.UPDLOCK