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

You need to create a query that calculates the total sales of each OrderID from a table named Sales.Details. The table contains two columns named OrderID and ExtendedAmount.
The solution must meet the following requirements:
* Use one-part names to reference columns.
* Order the results by OrderID with the smallest value first.
* 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.
Provide the correct code in the answer area.
Key Words:

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

You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.
The view has the following definition:

You need to ensure that users can update only the phone numbers by using this view.
What should you do?

• A.Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
• B.Create an INSTEAD OF UPDATE trigger on the view.
• C.Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
• D.Create an AFTER UPDATE trigger on the view.

You administer a Microsoft Azure SQL Database instance.
You are writing a new stored procedure that uses explicit transactions.
You need to ensure that no other process can affect the objects being used by the stored procedure while it is running.
Which Transact-SQL statement should you run?

• A.SET XACT_ABORT ON
• B.SET IMPLICIT_TRANSACTIONS ON
• C.SET CURSOR_CLOSE_ON_COMMIT ON
• D.SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

You use a Microsoft Azure SQL Database instance named ContosoDb. ContosoDb contains a table named Products that has existing records.
The Products table has columns named Code and QuantityOnHand.
You need to create a new column in the Products table named Category that allows null values and sets the value of the Category column to General for all existing records.
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:
NULL or NOT NULL specifies whether the column can accept null values. Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified or if the table is empty. NOT NULL can be specified for computed columns only if PERSISTED is also specified. If the new column allows null values and no default is specified, the new column contains a null value for each row in the table. If the new column allows null values and a default definition is added with the new column, WITH VALUES can be used to store the default value in the new column for each existing row in the table.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history.
The Production.Product table contains the following two columns:
The Sales.SalesOrderDetail table contains the following three columns:
You need to create a query listing all of the products that were never ordered.
Which statements should you execute?

• A.Option A
• B.Option D
• C.Option C
• D.Option B