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

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

You deploy a new server that has SQL Server 2012 installed. You need to create a table named Sales. OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:
Write the results to a disk.
Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment
should you use?
To answer, type the correct code in the answer area.

• A.CREATE TABLE Sales.OrderDetails ( ListPrice money not null, Quantity int not null, LineItemTotal as (ListPrice * Quantity))
• B.CREATE TABLE Sales.OrderDetails ( ListPrice money not null, Quantity int not null, LineItemTotal as (ListPrice * Quantity) PERSISTED)

You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
Returns a result set based on supplied parameters.
Enables the returned result set to perform a join with a table.
Which object should you use?

• A.Stored procedure
• B.Inline user-defined function
• C.Table-valued user-defined function
• D.Scalar user-defined function

You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.)

The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?

• A.IF (XACT_STATE ( ) ) = 0
• B.IF @@TRANCOUNT = 0
• C.IF (XACT_STATE ( ) ) = 1
• D.IF @@TRANCOUNT = 1

You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table. Which Transact-SQL statement should you use?

• A.ALTER TABLE Orders ADD CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)
• B.ALTER TABLE Customer ADD OrderID INT NOT NULL;
  ALTER TABLE Customer ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);
• C.ALTER TABLE Customer ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY (CustomerID) REFERENCES
  Orders (CustomerID)
• D.ALTER TABLE Orders ADD CONSTRAINT CK_Orders_CustomerID CHECK (CustomerID IN (SELECT CustomerId FROM Customer))
• E.ALTER TABLE Orders ADD CONSTRAINT PK_Orders_CustomerID PRIMARY KEY (CustomerID)

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

You have the following query:

You need to recreate the query to meet the following requirements:
Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.
Which code segment should you use?
To answer, type the correct code in the answer area.

• A.SELECT SalesTerritoryID,
  ProductID,
  AVG(UnitPrice),
  MAX(OrderQty),
  MAX(DiscountAmount)
  FROM Sales.Details
  ORDER BY SalesTerritoryID DESC, ProductID DESC
• B.SELECT SalesTerritoryID, ProductID, AVG(UnitPrice), MAX(OrderQty), MAX(DiscountAmount) FROM Sales.Details GROUP BY SalesTerritoryID,ProductID ORDER BY SalesTerritoryID DESC, ProductID DESC