Online Access Free 70-461 Exam Questions

You create a table by using the following Transact-SQL Statement:

You need to return a result set that has a single column named DisplayInformation. The result set must contain the Name value if the Name value is NOT NULL, otherwise the result set must contain the SubName value.
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 the 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.

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

You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements:
* Use one-part names to reference columns.
* Sort the order of the results from OrderId.
* NOT depend on the default schema of a user.
* Use an alias of TotalSales for the calculated ExtendedAmount.
* Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use?
To answer, type the correct code in the answer area.

• A.SELECT OrderID, SUM(ExtendedAmount) AS TotalSales
  FROM Sales.Details
  ORDER BY OrderID
• B.SELECT OrderID, SUM(ExtendedAmount) AS TotalSales
  FROM Sales.Details
  GROUP BY OrderID
  ORDER BY OrderID

A local bank uses a SQL Server database to manage accounts. You are developing a stored procedure that contains multiple Transact-SQL INSERT statements.
The stored procedure must use transaction management to handle errors.
You need to ensure that the stored procedure rolls back the entire transaction if a run-time occurs.
Which Transact-SQL statement should you add to the stored procedure?

• A.SET XACT_ABORT ON
• B.SET TRANSACTION ISOLATION LEVEL ON
• C.SET NOEXEC ON
• D.SET ARITHABORT ON

You administer a Microsoft SQL Server database named ContosoDb. ContosoDb contains a table named Suppliers and an indexed view named VWLocalSuppliers, both of which were created by using the following Transact-SQL statement:

You need to change the data type of the Code column in the Suppliers table to nvarchar(50).
Which four 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.

Correct Answer:

You have a view named Person.vAdditionalContactInfo that uses data from a table named Customers. The Customers table was created by running the following Transact-SQL statement:

You need to create a query that returns the first and last names of a customer from the view. If the CustomerID column does not have a value defined, the following message must be added to the query result: "Customer ID Does Not Exist." Construct the query using the following guidelines:
* Do not use aliases for column or table names.
* Display the columns in the same order as they are defined in the Customers table.
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 SELECT FirstName, LastName, CustomerID =
  2 CASE
  3 WHEN CustomerID IS NULL THEN "Customer ID Does Not Exist"
  4 ELSE CustomerID
  5 END
  6 FROM Person.vAdditionalContactInfo;
  7
  To line 1 add: CustomerID =
  To line 5 add: END
• B.1 SELECT FirstName, LastName, CustomerID =
  2 CASE
  3 WHEN CustomerID IS NULL THEN "Customer ID Does Not Exist"
  4 ELSE CustomerID
  5 END
  6 FROM Person.vAdditionalContactInfo;
  7
  To line 1 add: CustomerID =
  To line 5 add: END