70-461 試験問題を無料オンラインアクセス

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You develop a database for a travel application. You need to design tables and other database objects.
You create the Historical_Events table.
You need to store dates by using the smallest possible storage size.
What should you do?

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

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server.
The database contains two tables that have the following definitions:

Global customers place orders from several countries.
You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?

• A.SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
  FROM Customer c
  INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  COUNT(OrderAmount) AS OrderAmount
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  ORDER BY OrderAmount DESC
• B.SELECT CustomerID, CustomerName, ShippingCountry
  FROM
  (SELECT c.CustomerID, c.CustomerName,
  o. ShippingCountry,
  RANK() OVER (PARTITION BY c. CustomerID
  ORDER BY o. OrderAmount DESC) AS Rnk
  FROM Customer c
  INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID
  GROUP BY c.CustomerID, c.CustomerName,
  o.ShippingCountry) cs
  WHERE Rnk = 1
• C.SELECT CustomerID, CustomerName, ShippingCountry
  FROM
  (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,
  RANK() OVER (PARTITION BY c.CustomerID
  ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
  FROM Customer c
  INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID
  GROUP BY c.CustomerID, c.CustomerName,
  o.ShippingCountry) cs
  WHERE Rnk = 1
• D.SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
  FROM Customer c
  INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  RANK() OVER (PARTITION BY CustomerID
  ORDER BY COUNT(OrderAmount) DESC) AS Rnk
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  Where o.Rnk = 1

You administer a Microsoft SQL Server database that supports a banking transaction management application.
You need to retrieve a list of account holders who live in cities that do not have a branch location.
Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

• A.SELECT AccountHolderID
  FROM AccountHolder
  WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)
• B.SELECT AccountHolderID
  FROM AccountHolder
  WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
• C.SELECT AccountHolderID
  FROM AccountHolder
  WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
• D.SELECT AccountHolderID
  FROM AccountHolder
  WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)

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

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter.
You plan to build a single process for each employee that will execute the stored procedure based on the country of residence.
Which approach should you use?

• A.An UPDATE statement that includes CASE
• B.Cursor
• C.A recursive stored procedure
• D.The foreach SQLCLR statement
• E.Trigger