20 October, 2020

Best Quality Microsoft 70-461 Question Online

we provide Approved Microsoft 70-461 question which are the best for clearing 70-461 test, and to get certified by Microsoft Querying Microsoft SQL Server 2012. The 70-461 Questions & Answers covers all the knowledge points of the real 70-461 exam. Crack your Microsoft 70-461 Exam with latest dumps, guaranteed!

Microsoft 70-461 Free Dumps Questions Online, Read and Test Now.

Page: 1 / 19

Total 232 questions Full Exam Access

Question 1

- (Exam Topic 2)
Your database contains a table named Products that has columns named ProductID and Name.
You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time. You need to view rows 31 through 45.
Which Transact-SQL query should you create?
70-461 dumps exhibit

Option A

Option B

Option C

Option D

Question 2

- (Exam Topic 1)
You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

Create a SQL Server login that has VIEW SERVER STATE permission

Create an application role and a secured password for the role.

Modify the stored procedure to include the EXECUTE AS OWNER statemen

Grant VIEW SERVER STATE permissions to the owner of the stored procedure.

Create a SQL Server login that has VIEW SERVER STATE permission

Modify the stored procedure to include the EXECUTE AS {newlogin} statement.

Grant the db_owner role on the database to User1.

Grant the sysadmin role on the database to User1.

Question 3

- (Exam Topic 1)
You create the following stored procedure. (Line numbers are included for reference only.)
70-461 dumps exhibit

You need to ensure that the stored procedure performs the following tasks:
70-461 dumps exhibit

If a record exists, update the record.
70-461 dumps exhibit

If no record exists, insert a new record.
Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
70-461 dumps exhibit

Solution:
Reference: http://technet.microsoft.com/en-us/library/bb510625.aspx

Does this meet the goal?

Yes

Question 4

- (Exam Topic 1)
You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type.
You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate.
You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted.
Which Transact-SQL statement should you use?

CREATE TRIGGER TrgValidateAccountNumber ON TransactionsINSTEAD OF INSERT ASBEGININSERT INTO TransactionsSELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN(SELECT AccountNumber FROM LoanAccountsUNION SELECT AccountNumber FROM SavingAccounts) END

CREATE TRIGGER TrgValidateAccountNumber ON TransactionsFOR INSERT ASBEGININSERT INTO TransactionsSELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN(SELECT AccountNumber FROM LoanAccountsUNION SELECT AccountNumber FROM SavingAccounts) END

CREATE TRIGGER TrgValidateAccountNumberON Transactions INSTEAD OF INSERT ASBEGINIF EXISTS (SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccountsUNION SELECT AccountNumber FROM SavingAccounts)) BEGINROLLBACK TRAN ENDEND

CREATE TRIGGER TrgValidateAccountNumber ON TransactionsFOR INSERT ASBEGINIF EXISTS (SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccountsUNION SELECT AccountNumber FROM SavingAccounts)) BEGINROLLBACK TRAN ENDEND

Question 5

- (Exam Topic 2)
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:
70-461 dumps exhibit

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

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?

SELECT ERROR_LINE(), ERROR_MESSAGE()

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

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

THROW;

Question 6

- (Exam Topic 2)
You develop a stored procedure for a wholesale glass manufacturing company. The stored procedure uses a cursor to read all row-based combinations of the following tables:
70-461 dumps exhibit

You observe that the stored procedure returns results very slowly. You need to improve the execution speed of the stored procedure. Which cursor type should you use?

GLOBAL FORWARD_ONLY

LOCAL FAST_FORWARD

LOCAL STATIC READ_ONLY FORWARD_ONLY

GLOBAL STATIC

Question 7

- (Exam Topic 2)
You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?

Smallmoney

Numeric

Money

Varchar

Question 8

- (Exam Topic 1)
You support a database structure shown in the exhibit. (Click the Exhibit button.)
70-461 dumps exhibit

You need to write a query that displays the following details:
70-461 dumps exhibit

Total sales made by sales people, year, city, and country
70-461 dumps exhibit

Sub totals only at the city level and country level
70-461 dumps exhibit

A grand total of the sales amount Which Transact-SQL query should you use?

SELECT SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPersonON Sale.SalesPersonID = SalesPerson.SalesPersonIDGROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())

SELECT SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPersonON Sale.SalesPersonID = SalesPerson.SalesPersonIDGROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))

SELECT SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPersonON Sale.SalesPersonID = SalesPerson.SalesPersonIDGROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

SELECT SalesPerson.Name, Country, City,DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPersonON Sale.SalesPersonID = SalesPerson.SalesPersonIDGROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

Question 9

- (Exam Topic 1)
You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)
70-461 dumps exhibit

You write the following Transact-SQL query:
70-461 dumps exhibit

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query. What should you do?

Use a FORCESCAN hint in the query.

Add a clustered index on SalesOrderId in SalesOrderHeader.

Use a FORCESEEK hint in the query.

Update statistics on SalesOrderId on both tables.

Question 10

- (Exam Topic 1)
Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice.
The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.
You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table.
Which Transact-SQL query should you use?

UPDATE Products SET Price = Price * 1.05OUTPUT inserted.ProductCode, deleted.Price, inserted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

UPDATE Products SET Price = Price * 1.05OUTPUT inserted.ProductCode, inserted.Price, deleted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

UPDATE Products SET Price = Price * 1.05OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 1.05INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

UPDATE Products SET Price = Price * 1.05INSERT INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) SELECT ProductCode, Price, Price * 1.05 FROM Products

Question 11

- (Exam Topic 1)
You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.)
70-461 dumps exhibit

You need to create a query that meets the following requirements:
70-461 dumps exhibit

References columns by using one-part names only.
70-461 dumps exhibit

Groups aggregates only by SalesTerritoryID, and then by ProductID.
70-461 dumps exhibit

Orders the results in descending order by SalesTerritoryID and then by ProductID in descending order for both.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
70-461 dumps exhibit

Solution:
SELECT SalesTerritoryID, ProductID,
AVG(UnitPrice), MAX(OrderQty) MAX(DiscountAmount) FROM Sales.Details
GROUP BY SalesTerritoryID, ProductID
ORDER BY SalesTerritoryID DESC, ProductID DESC

Does this meet the goal?

Yes

Question 12

- (Exam Topic 2)
You develop a Microsoft SQL Server database that contains a table named Products. The Products table has columns named ProductId, CategoryId, Name, and Price.
Product prices are often updated as follows:
70-461 dumps exhibit

For a single product by a specific value by using the ProductId column
70-461 dumps exhibit

For an entire category by a percentage by using the CategoryId column
You need to log the change price to a new table named PriceChange by using the ProductId’ ChangedValue, and ChangedDate columns. You also need to ensure that increases in price are recorded as positive values and decreases in price as negative values.
Which Transact-SQL query should you use?
70-461 dumps exhibit

Option A

Option B

Option C

Option D

Question 13

- (Exam Topic 2)
You develop a Microsoft SQL Server database. You design a table to store product information. The table is defined as follows:
70-461 dumps exhibit

You need to meet the following requirements:
70-461 dumps exhibit

If a product has a product category value other than “REGULAR”, the product should have a sub-category with a length of two characters.
70-461 dumps exhibit

If a product has the product category “REGULAR”, the product may or may not have a sub-category. Which Transact-SQL statement should you use?
70-461 dumps exhibit

Option A

Option B

Option C

Option D

Question 14

- (Exam Topic 2)
Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter.
You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence.
Which approach should you use?

A SELECT statement that includes CASE

Cursor

BULK INSERT

View

A user-defined function

Question 15

- (Exam Topic 1)
You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING

ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

ALTER INDEX ALL ON OrderDetail REBUILD

ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Question 16

- (Exam Topic 1)
You use Microsoft SQL Server 2012 to develop a database application.
You create a stored procedure named dbo.ModifyData that can modify rows.
You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:
70-461 dumps exhibit

Does not return an error
70-461 dumps exhibit

Closes all opened transactions
Which Transact-SQL statement should you use?

BEGIN TRANSACTION BEGIN TRYEXEC dbo.ModifyData COMMIT TRANSACTION END TRYBEGIN CATCHIF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH

BEGIN TRANSACTION BEGIN TRYEXEC dbo.ModifyData COMMIT TRANSACTION END TRYBEGIN CATCHIF @@ERROR != 0 ROLLBACK TRANSACTION; THROW;END CATCH

BEGIN TRANSACTION BEGIN TRYEXEC dbo.ModifyData COMMIT TRANSACTION END TRYBEGIN CATCHIF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW;END CATCH

BEGIN TRANSACTION BEGIN TRYEXEC dbo.ModifyData COMMIT TRANSACTION END TRYBEGIN CATCHIF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH

Question 17

- (Exam Topic 2)
You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.)
70-461 dumps exhibit

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:
70-461 dumps exhibit

Return rows ordered by descending values in the UnitPrice column.
70-461 dumps exhibit

Use the Rank function to calculate the results based on the UnitPrice column.
70-461 dumps exhibit

Return the ranking of rows in a column that uses the alias PriceRank.
70-461 dumps exhibit

Use two-part names to reference tables.
70-461 dumps exhibit

Display the columns in the order that they are defined in the table. The PriceRank column must appear last.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
70-461 dumps exhibit

Solution:
SELECT CatID, CatName, ProductID, ProdName, UnitPrice, RANK (ORDER BY UnitPrice DESC) OVER () AS PriceRank
FROM Sales.ProductCatalog ORDER BY PriceRank
Reference: RANK (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms176102.aspx

Does this meet the goal?

Yes

Question 18

- (Exam Topic 1)
You administer a Microsoft SQL Server database that contains a table named Products.
In a bit column named Discontinued, a value of 1 indicates a product is inactive, and a value of 0 indicates the product is active.
You need to create a view that returns a column with a full product status description. Which expression should you use to define the column?

IF(Discontinued = 0, ‘Active’, ‘Inactive’)

CASE Discontinued WHEN 0 THEN ‘Active’WHEN 1 THEN ‘Inactive’ END

IF Discontinued = 0 ‘Active’ELSE‘Inactive’

DECODE (Discontinued, 0, ‘Active’, 1, ‘Inactive’, ‘Unknown’)

Question 20

- (Exam Topic 1)
You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button.)
70-461 dumps exhibit

You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure.
You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN, END, or DECLARE statements.
Part of the correct Transact-SQL statement has been provided in the answer area. Complete the Transact-SQL statement
70-461 dumps exhibit

Solution:
CREATE PROCEDURE usp_Customers @Count int SELECT TOP(@Count)
Customers.LastName FROM Customers
ORDER BY Customers.LastName

Does this meet the goal?

Yes

Question 21

- (Exam Topic 1)
You administer a Microsoft SQL Server database that supports a banking transaction management application. You need to retrieve a list of account holders who live in cities that do not have a branch location.
Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

SELECT AccountHolderID FROM AccountHolderWHERE CityID NOT IN (SELECT CityID FROM BranchMaster)

SELECT AccountHolderID FROM AccountHolderWHERE CityID <> ALL (SELECT CityID FROM BranchMaster)

SELECT AccountHolderID FROM AccountHolderWHERE CityID <> SOME (SELECT CityID FROM BranchMaster)

SELECT AccountHolderID FROM AccountHolderWHERE CityID <> ANY (SELECT CityID FROM BranchMaster)

Question 22

- (Exam Topic 1)
You use a Microsoft Azure SQL DataBase instance. The instance contains a table named Customers that has columns named Id, Name, and IsPriority.
You need to create a view named VwPriorityCustomers that:
70-461 dumps exhibit

returns rows from Customer that have a value of True in the IsPriority column, and
70-461 dumps exhibit

does not allow columns to be altered or dropped in the underlying table.
Which Transact-SQL statement shoul you run?

CREATE VIEW VwPriorityCustomers ASSELECT Id, Name FROM dbo.Customers WHERE IsPriority=1 WITH CHECK OPTION

CREATE VIEW VwPriorityCustomers WITH VIEW_METADATAASSELECT Id, Name FROM dbo.Customers WHERE IsPriority=1

CREATE VIEW VwPriorityCustomers WITH ENCRYPTIONASSELECT Id, Name FROM dbo.Customers WHERE IsPriority=1

CREATE VIEW VwPriorityCustomers WITH SCHEMABINDINGASSELECT Id, Name FROM dbo.Customers WHERE IsPriority=1

Question 23

- (Exam Topic 1)
You need to create a cursor that meets the following requirements:
70-461 dumps exhibit

Executes as quickly as possible.
70-461 dumps exhibit

Reflects all data changes made to the table while scrolling.
Which five Transact-SQL statements 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 are and arrange them in the correct order.
70-461 dumps exhibit

Solution:
Step 1: Declare variables. Step 2: Open the cursor
Step 3: Fetch the first instance. Step 4: Loop
Step 5: Close and deallocate the cursor
References:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-2017

Does this meet the goal?

Yes

Question 24

- (Exam Topic 1)
You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button).
70-461 dumps exhibit

You need to create a query for a report. The query must meet the following requirements:
70-461 dumps exhibit

NOT use object delimiters.
70-461 dumps exhibit

Use the first initial of the table as an alias.
70-461 dumps exhibit

Return the most recent order date for each customer.
70-461 dumps exhibit

Retrieve the last name of the person who placed the order. The solution must support the ANSI SQL-99 standard.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.
70-461 dumps exhibit

Solution:
SELECT C.LastName,
MAX(O.OrderDate) AS MostRecentOrderDate FROM Customers AS C INNER JOIN Orders AS O ON C.CustomerID=O.CustomerID
GROUP BY C.Lastname
ORDER BY MAX (O.OrderDate) DESC

Does this meet the goal?

Yes

Question 26

- (Exam Topic 1)
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
70-461 dumps exhibit

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:
70-461 dumps exhibit

Write the results to a disk.
70-461 dumps exhibit

Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
70-461 dumps exhibit

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.
Solution:
CREATE TABLE Sales.OrderDetails ( ListPrice money not null,
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)
Reference: http://msdn.microsoft.com/en-us/library/ms174979.aspx
Reference: http://technet.microsoft.com/en-us/library/ms188300.aspx

Does this meet the goal?

Yes

Page: 1 / 19

Total 232 questions Full Exam Access