最新的Microsoft 70-461免費考試真題

問題1

You use Microsoft SQL Server 2012 database to develop a shopping cart application.
You need to invoke a table-valued function for each row returned by a query.
Which Transact-SQL operator should you use?

A. CROSS APPLY

B. UNPIVOT

C. PIVOT

D. CROSS JOIN

正確答案: A

說明：（僅 Fast2test 成員可見）

問題2

You use a Microsoft SQL Server 2012 database.
You want to create a table to store Microsoft Word documents.
You need to ensure that the documents must only be accessible via Transact-SQL queries.
Which Transact-SQL statement should you use?

A. CREATE TABLE DocumentStore
(
[Id] hierarchyid,
[Document] NVARCHAR NOT NULL
)
GO

B. CREATE TABLE DocumentStore
(
[Id] INT NOT NULL PRIMARY KEY,
[Document] VARBINARY(MAX) NULL
)
GO

C. CREATE TABLE DocumentStore
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[Document] VARBINARY(MAX) FILESTREAM NULL
)
GO

D. CREATE TABLE DocumentStore AS FileTable

正確答案: B

說明：（僅 Fast2test 成員可見）

問題3

You create a view based on the following statement:

You grant the Select permission to User1.
You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1.
Which Transact-SQL statement should you use?

A. Option D

B. Option B

C. Option C

D. Option A

正確答案: C

問題4

You administer a SQL Server database that tracks sales that are made by sales persons. The database contains a table that is defined by the following Transact-SQL statements:

You have the following requirements:
* accept a datetime value for the query month
* return a list of salespeople IDs who have sales in the query month or before the query month
* compare sales with sales quota for salespeople who have a sales quota
* display year-to-date sales for salespeople that do not have a sales quota How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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:
Box 1: IIF ..
The IIF logical function returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.
Syntax: IIF ( boolean_expression, true_value, false_value )
Box 2: [LastSzale] < EOMONTH(@queryMonth)
The EOMONTH function returns the last day of the month containing a specified date, with an optional offset.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2017

問題5

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

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:
* UnitPrice must be returned in descending order.
* The query must use two-part names to reference the table.
* The query must use the RANK function to calculate the results.
* The query must return the ranking of rows in a column named PriceRank.
* The list must display the columns in the order that they are defined in the table. PriceRank must appear last.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

A. SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice, RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog ORDER BY ProductCatalog.UnitPrice DESC

B. SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice, RANK() OVER (ORDER BY ProductCatalog FROM Sales.ProductCatalog ORDER BY ProductCatalog.UnitPrice DESC

正確答案: A

問題6

You are developing a Microsoft SQL Server 2012 database for a company. The database contains a table that is defined by the following Transact-SQL statement:

You use the following Transact-SQL script to insert new employee data into the table. Line numbers are included for reference only.

If an error occurs, you must report the error message and line number at which the error occurred and continue processing errors.
You need to complete the Transact-SQL script.
Which Transact-SQL segment should you insert at line 06?

A. THROW;

B. DECLARE @message NVARCHAR(1000),@severity INT, @state INT;
SELECT @message = ERROR_MESSAGE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE(); THROW (@message, @severity, @state);

C. SELECT ERROR_LINE(), ERROR_MESSAGE()

D. DECLARE @message NVARCHAR(1000),@severity INT, @state INT;
SELECT @message = ERROR_MESSAGE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE(); RAISERROR (@message, @severity, @state);

正確答案: D

說明：（僅 Fast2test 成員可見）

問題7

You use Microsoft SQL Server 2012 to develop a database application.
You create two tables by using the following table definitions.

Which six Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

正確答案:

問題8

You have a SQL Server database named CUSTOMERS.
You need to sign a stored procedure named SelectCustomers in the CUSTOMERS database.
Which four statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

正確答案:

Explanation:
There are four steps involved in signing a module:
References:https://msdn.microsoft.com/en-us/library/bb669102(v=vs.110).aspx

問題9

You administer a Microsoft SQL Server database that supports a shopping application.
You need to retrieve a list of customers who live in territories that do not have a sales person.
Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

A. SELECT CustomerID FROM Customer
WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)

B. SELECT CustomerID FROM Customer
WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)

C. SELECT CustomerID FROM Customer
WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)

D. SELECT CustomerID FROM Customer
WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)

正確答案: B,C

問題10

You develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure that meets the following requirements:
* Accepts a single input parameter for CustomerID.
* Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

A. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN @Result
GO

B. DECLARE @CustomerRatingByCustomer INT
DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745,
@CustomerRatingByCustomer

C. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT
AS
DECLARE @Result INT
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURNS @Result
GO

D. EXECUTE dbo.GetCustomerRating 1745

E. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN
GO

F. DECLARE @CustomerRatingByCustomer INT
EXECUTE dbo.GetCustomerRating
@CustomerID = 1745,
@CustomerRating = @CustomerRatingByCustomer OUTPUT

正確答案: E,F

問題11

You are developing an SQL Server database. The database contains two tables and a function that are defined by the following Transact-SQL statements.

You need to create a query to determine the total number of products that are sold each day for the live top-selling products on that particular day.
How should you complete the relevant Transact-SQL script? To answer, select the appropriate Transact-SQL statements from each list in the answer area.

正確答案:

Explanation:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. T There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
Reference:
https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

問題12

You use a Microsoft SQL Server database.
You want to create a table to store files.
You need to ensure that the following requirements are met:
* The files must include information about the directory structure.
* The files must be accessible in SQL Server.
* The files must be in a folder that is accessible directly by using Windows Explorer.
Which Transact-SQL statement should you run?

A. Option

B. Option

C. Option

D. Option

正確答案: C

說明：（僅 Fast2test 成員可見）

問題13

You develop a Microsoft SQL Server database. The database contains a table defined by the following Transact-SQL statement:

All regions have more than one employee.
You need to write a query to find the employee with the highest SalesYTD in each region with the following result set:
* First Name
* Last Name
* Region
* Sales YTD
Which Transact-SQL query should you run?

A. Option

B. Option

C. Option

D. Option

正確答案: D

說明：（僅 Fast2test 成員可見）

最新的Microsoft Querying Microsoft SQL Server 2012/2014 - 70-461免費考試真題

聯系我們

站內鏈接

最新更新