Online Access Free 70-461 Practice Test

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

You develop a database for a travel application. You need to design tables and other database objects.
You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats.
What should you do?

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

You have a SQL Server database that contains all of the sales data for your company.
You need to create a query to update the rows in a table. The solution must write original and updated values to a separate table.
Which clause should you add to the UPDATE statement in the query?

• A.OUTPUT
• B.INSERT
• C.ReadText
• D.WRITETEXT

You have a data warehouse that contains the data for all the customers of your company.
You need to create a stored procedure that generates a list of customer addresses. The list must meet the following requirements:
* The procedure must accept a string value.
* The procedure should return all customers who reside in a city whose name begins with the provided value.
* If a user provides a blank value, the procedure must execute, and then return a blank result set.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within code 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.1 CREATE PROCEDURE uspGetAddress @City nvarchar(50) = ""
  2 AS
  3 SELECT *
  5 FROM Person.Address
  6 WHERE City LIKE @City *
  7 GO
  On line 5 add LIKE (use string comparison)
• B.1 CREATE PROCEDURE uspGetAddress @City nvarchar(50) = ""
  2 AS
  3 SELECT *
  4 FROM Person.Address
  5 WHERE City LIKE @City *
  6 GO
  On line 1 add "" (set the default value)
  On line 5 add LIKE (use string comparison)

You develop a SQL Server database for an online theater sales company. The database includes the following four tables:

The following diagram shows the tables and their relationships:

Correct Answer: