Online Access Free 070-461 Practice Test

You develop a database application for Microsoft SQL Server and Microsoft Azure SQL Database.
You need to raise an exception and transfer execution to a CATCH block.
You need to ensure that the exception returns output in the following format:
Msg 51000, Level 16, State 1, Line 1
The record does not exist.
Which Transact-SQL statement should you run?

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

You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named Invoice. The solution must ensure that the Invoice variable is validated against the Sales.InvoiceSchema schema.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-SQL segment that has been provided as well as below it.


Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

• A.DECLARE %Invoice xml (Sales.InvoiceSchema).
• B.DECLARE #Invoice xml (Sales.InvoiceSchema)
• C.DECLARE @Invoice xml (Sales.InvoiceSchema).

You use Microsoft SQL Server 2012 to develop a database application.
You need to create an object that meets the following requirements:
* Takes an input variable
* Returns a table of values
* Cannot be referenced within a view
Which object should you use?

• A.Scalar-valued function
• B.Stored procedure
• C.User-defined data type
• D.Inline function

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 OrderId, OrderDate, Amount, Name, Country FROM Orders
  INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML RAW, ELEMENTS
• 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 AUTO
• F.SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
  INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML AUTO
• G.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')
• 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 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 OrderId, OrderDate, Amount, Name, Country FROM Orders
  INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML RAW, ELEMENTS
• D.SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
  INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML AUTO, ELEMENTS
• E.SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
  INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML RAW
• 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')