Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 261:

  You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb.

  ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions.

  Which Transact-SQL statement should you use?

  A. GRANT DELETE ON Purchases.Suppliers TO ContosoUser
  B. CREATE PROCEDURE Purchases.PurgeInactiveSu ppliers WITH EXECUTE AS USER = 'dbo' AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgelnactiveSu ppliers TO ContosoUser
  C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
  D. CREATE PROCEDURE Purchases.PurgeInactiveSu ppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgeInactiveSu ppliers TO ContosoUser
  D. CREATE PROCEDURE Purchases.PurgeInactiveSu ppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgeInactiveSu ppliers TO ContosoUser

  ---

  Incorrect syntax near the keyword `USER' in option B. http://msdn.microsoft.com/en-us/library/ms188354.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx

• Question 262:

  Which of the following are not allowed in the PIVOT operator's specification? (Chooseall that apply.)

  A. Specifying a computation as input to the aggregate function
  B. Specifying a computation as the spreading element
  C. Specifying a subquery in the IN clause
  D. Specifying multiple aggregate functions
  A. Specifying a computation as input to the aggregate function
  B. Specifying a computation as the spreading element
  C. Specifying a subquery in the IN clause
  D. Specifying multiple aggregate functions

  ---

  A. Correct: You cannot specify a computation as input to the aggregate function, rather just a name of a column from the input table.

  B. Correct: You cannot specify a computation as the spreading element, rather just a name of a column from the input table.

  C. Correct: You cannot specify a subquery in the IN clause, rather just a static list. D. Correct: You cannot specify multiple aggregate functions, rather just one.

• Question 263:

  Which directive of the FOR XML clause should you use to produce element-centric XML?

  A. ATTRIBUTES
  B. ROOT
  C. ELEMENTS
  D. XMLSCHEMA
  C. ELEMENTS

• Question 264:

  DRAG DROP

  You develop a Microsoft SQL Server database for an order processing system that contains a table named OrderCountSummary, as shown in the first exhibit. (Click the Exhibit tab.)

  

  The table stores the names of vendors and the number of orders submitted by each vendor for each food category: Dairy, Meat, Poultry, Seafood, and Vegetarian. You need to generate a report that displays the total number of orders placed for each food category, as shown in the following table.

  

  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.

  Select and Place:

  

  

  ---

  You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

  References: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot

• Question 265:

  DRAG DROP

  You develop a Microsoft SQL Server database that supports an e-commerce website. You create a table defined by the following Transact-SQL statement:

  

  You need to create a reusable function that will remove all carriage return characters from all the text values.

  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.

  Select and Place:

  

  

  ---

  Note: Parts of the question text is missing.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017

• Question 266:

  You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB. The SalesDB database is shown in the following database diagram: You create a view on the SalesDB using the following Transact-SQL code:

  

  CREATE VIEW SalesV

  WITH SCHEMABINDINGS

  AS

  SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice * OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price

  FROM OrderDetail INNER JOIN Products ON

  OrderDetail.ProductID = Products.ProducID

  INNER JOIN Invoices ON

  OrderDetail.InvoiceID = Invoices.InvoiceID GROUP BY Products.ProductID, Invoices.InvoiceDate GO

  How should you alter this view to allow users to update data through the SalesV?

  A. You should add a CHECK constraint to the SalesV view.
  B. You should add an INSTEAD OF trigger to the SalesV view.
  C. You should add a clustered index to the SalesV view.
  D. You should add an AFTER UPDATE trigger to the SalesV view.
  E. Create a columnstore index on all columns used in the SalesV view.
  B. You should add an INSTEAD OF trigger to the SalesV view.

• Question 267:

  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] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL ) GO
  B. CREATE TABLE DocumentStore ( [Id] hierarchyid, [Document] NVARCHAR NOT NULL ) GO
  C. CREATE TABLE DocumentStore AS FileTable
  D. CREATE TABLE DocumentStore ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX) FILESTREAM NULL ) GO
  A. CREATE TABLE DocumentStore ( [Id] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL ) GO

  ---

  http://msdn.microsoft.com/en-us/library/gg471497.aspx http://msdn.microsoft.com/en-us/library/ff929144.aspx

• Question 268:

  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.)

  Select and Place:

  

  

• Question 269:

  A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit.

  You need to create a report that displays the profits made by each territory for each year and its previous year. Which Transact-SQL query should you use?

  A. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits
  B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits
  C. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits
  D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS NextProfit FROM Profits
  B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits

  ---

  LAG accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2016. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

  Use ORDER BY Year, not ORDER BY Territory.

  Example: The following example uses the LAG function to return the difference in sales quotas for a specific employee over previous years. Notice that because there is no lagvalue available for the first row, the default of zero (0) is returned.

  USE AdventureWorks2012;

  GO

  SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

  LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota

  FROM Sales.SalesPersonQuotaHistory

  WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

  Incorrect Answers:

  A: Use ORDER BY Year, not ORDER BY Territory.

  C, D: LEAD accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a

  SELECT statement to compare values in the current row with values in a following row.

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

• Question 270:

  DRAG DROP

  You are developer for a Microsoft Azure SQL Database instance.

  You are creating a new stored procedure. The procedure must perform the following tasks in this order:

  1. Update a table named OrderHistory.

  2. Delete rows from a table named Orders.

  3. Delete rows from a table named Customers.

  4. Insert rows into a table named ProcessHistory.

  You need to ensure that the procedure meets the following requirements:

  If either DELETE operation fails, the rest of operation must continue.

  If either the UPDATE operation or the INSERT operation fails, the whole procedure should fail and no changes should be retained.

  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.

  Select and Place: