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

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) DESC) AS OrderAmount
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  ORDER BY OrderAmount DESC
• B.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
• C.SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
  FROM Customer c
  INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  RANK() OVER (PARTITION BY CustomerID
  ORDER BY OrderAmount DESC) AS Rnk
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  WHERE o.Rnk = 1
• D.SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
  FROM
  (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,
  RANK() OVER (PARTITION BY 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

You are maintaining a Microsoft SQL Server database that stores order information for an online store website. The database contains a table that is defined by the following Transact-SQL statement:

You need to ensure that purchase order numbers are used only for a single order.
What should you do?

• A.Create a new PRIMARY constraint on the PurchaseOrderNumber column.
• B.Create a new FOREIGN KEY constraint on the PurchaseOrderNumber column.
• C.Create a new CLUSTERED constraint on the PurchaseOrderNumber column.
• D.Create a new UNIQUE constraint on the PurchaseOrderNumber column.

You need to create a stored procedure that enters values into multiple tables. The solution must ensure that if a single insert fails, none of the values are inserted into the tables.
How should you complete the stored procedure? To answer, drag the appropriate values to the correct locations. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

正解：

Explanation:
References:https://msdn.microsoft.com/en-us/library/ms188378.aspx

You develop a database application for Microsoft SQL Server 2012 and Microsoft Azure SQL Database.
You create a table named Purchasing.vVendorWithAddresses as shown in the following table.

You write the following Transact-SQL (Line numbers are included for reference only.)
01 CREATE PROCEDURE

You need to add Transact-SQL statements at line 08 to ensure that GetVendorInStateNeighbors returns the names of vendors that are located in all states where the vendor specified in the @vendorname parameter has a location.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

正解：

Explanation:
The IN statement determines whether a specified value matches any value in a subquery or a list.
Incorrect:
The EXISTS command specifies a subquery to test for the existence of rows.
References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-2017

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

You need to create a view named uv_CustomerFullName to meet the following requirements:
* The code must NOT include object delimiters.
* The view must be created in the Sales schema.
* Columns must only be referenced by using one-part names.
* The view must return the first name and the last name of all customers.
* The view must prevent the underlying structure of the customer table from being changed.
* The view must be able to resolve all referenced objects, regardless of the user's default schema.
Which code segment should you use?
To answer, type the correct code in the answer area.

• A.CREATE VIEW Sales.uv_CustomerFullName
  WITH SCHEMABINDING
  AS
  SELECT FirstName, LastName
  FROM Sales.Customers
• B.CREATE VIEW Sales.uv_CustomerFullName
  WITH SCHEMABINDING
  AS
  SELECT FirstName, LastName