Free Microsoft 70-461 Exam Dumps Questions & Answers

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

• A.SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')
• B.SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
  INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML AUTO, ELEMENTS
• C.SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
  INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML AUTO, ELEMENTS
• D.SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
  INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML RAW
• E.SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
  INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML RAW, ELEMENTS
• F.SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
  INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML AUTO
• G.SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
  INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML AUTO
• H.SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

You use Microsoft SQL Server 2012 to develop a database application.
Your application sends data to an NVARCHAR(MAX) variable named @var.
You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9).
Which code segment should you use?

• A.BEGIN TRY
  SELECT
  convert (decimal(36,9), @var) as Value,
  'True' As BadCast
  END TRY
  BEGIN CATCH
  SELECT
  convert (decimal(36,9), @var) as Value,
  'False' As BadCast
  END CATCH
• B.SELECT
  IF(TRY_PARSE(@var AS decimal(36,9)) IS NULL,
  'True',
  'False'
  )
  AS BadCast
• C.TRY(
  SELECT convert (decimal(36,9), @var)
  SELECT 'True' As BadCast
  )
  CATCH(
  SELECT 'False' As BadCast
  )
• D.SELECT
  CASE
  WHEN convert (decimal(36,9), @var) IS NULL
  THEN 'True'
  ELSE 'False'
  END
  AS BadCast

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

You need to create a query for a report. The query must meet the following requirements:
* NOT use object delimiters.
* Use the first initial of the table as an alias.
* Return the most recent order date for each customer.
* Retrieve the last name of the person who placed the order.
The solution must support the ANSI SQL-99 standard.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

• A.SELECT C .LastName,
  MAX(O.OrderDate) AS MostRecentOrderDate
  FROM Customers AS C INNER JOIN Orders AS O
  ON C .CustomerID=O.CustomerID
  GROUP BY C .Lastname
  ORDER BY MAX (O.OrderDate) DESC
• B.SELECT C .LastName,
  MAX(O.OrderDate) AS MostRecentOrderDate
  FROM Customers AS C INNER JOIN Orders AS O
  ON C .CustomerID
  GROUP BY C .Lastname
  ORDER BY MAX (O.OrderDate) DESC

You administer a Microsoft SQL Server database named Orders. Orders is highly active OLTP system used for e-commerce.
Performance on the database has degraded over the past few months as the volume of data has increased, and now users report the application is unusable.

You need to identify the cause of the performance problem.
Which Transact-SQL statement should you run?

• A.SET STATISTICS TIME ON
• B.DBCC CHECKCONTRAINTS
• C.SET STATISTICS IO ON
• D.SET FORCEPLAN ON

You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.
Which code segment should you use?
To answer, type the correct code in the answer area.