Free Microsoft 070-461 Exam Dumps Questions & Answers

You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime.
The table contains a unique constraint on the combination of ProductName and CreatedDateTime.
You need to modify the Products table to meet the following requirements:
* Remove all duplicates of the Products table based on the ProductName column.
* Retain only the newest Products row.
Which Transact-SQL query should you use?

• A.WITH CTEDupRecords
  AS
  (
  SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
  FROM Products
  GROUP BY ProductName
  HAVING COUNT(*) > 1
  )
  DELETE p
  FROM Products p
  JOIN CTEDupRecords cte ON
  p.ProductName = cte.ProductName
• B.WITH CTEDupRecords
  AS
  (
  SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
  FROM Products
  GROUP BY ProductName
  HAVING COUNT(*) > 1
  )
  DELETE p
  FROM Products p
  JOIN CTEDupRecords cte ON
  cte.ProductName = p.ProductName
  AND cte.CreatedDateTime > p.CreatedDateTime
• C.WITH CTEDupRecords
  AS
  (
  SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
  FROM Products
  GROUP BY ProductName
  )
  DELETE p
  FROM Products p
  JOIN CTEDupRecords cte ON
  p.ProductName = cte.ProductName
• D.WITH CTEDupRecords
  AS
  (
  SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
  FROM Products
  GROUP BY ProductName
  HAVING COUNT(*) > 1
  )
  DELETE p
  FROM Products p
  JOIN CTEDupRecords cte ON
  p.ProductName = cte.ProductName
  AND p.CreatedDateTime > cte.CreatedDateTime

Your database contains tables that were created by running the following Transact-SQL statements:

Data from the Ticket and Action production tables needs to be archived to the TicketArchive and ActionArchive tables, respectively. When a ticket is archived, data is copied from the production tables and then deleted from the production tables.
You need to create statements to archive all the tickets that:
were opened one year ago or earlier,
were closed six months ago or earlier, and
have a TicketStatusID of 10.
Which Transact-SQL segment or segments should you use to complete the Transact-SQL statement? 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.

Correct Answer:

Explanation:
Box 1: DECLARE..
Declare a table variable
Box 2: OUTPUT..
Box 3: DELETE FROM dbo.Ticket
Remove from the Ticket table.
Box 4: DELETE FROM dbo.Action
Remove from the Action table.
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

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?

• A.UPDATE Products SET Price = Price * 1.05
  INSERT INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
  SELECT ProductCode, Price, Price * 1.05 FROM Products
• B.UPDATE Products SET Price = Price * 1.05
  OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 1.05
  INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
• C.UPDATE Products SET Price = Price * 1.05
  OUTPUT inserted.ProductCode, inserted.Price, deleted.Price
  INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
• D.UPDATE Products SET Price = Price * 1.05
  OUTPUT inserted.ProductCode, deleted.Price, inserted.Price
  INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

You are developing an SQL Server database for an automobile manufacturer. The manufacturer maintains the list of vehicles sold and vehicles that have been recalled. The tables are shown below:

You have the following Transact-SQL code. Line numbers are included for reference only.

You must update the VehiclesToRecall table with the list of vehicles that were recalled in 2014. You must maximize the performance of the operation.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Correct Answer:

Reference:
MERGE (Transact-SQL)
https://msdn.microsoft.com/en-us/library/bb510625.aspx

You have a SQL Server database that contains all of the sales data for your company.
You need to create a query that returns the customers who represent the top five percent of the total actual sales.
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.

• A.1 SELECT CustomerID, N
  2 ,Top5= TOP (0,95) PERCENT Sales (ORDER BY N) OVER (PARTITION BY Sales)
  3 FROM Customers;
  Add TOP and PERCENT Sales on line 2.
  Syntax:
  TOP (expression) [PERCENT]
  [ WITH TIES ]
  ]
  expression
  Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.
  PERCENT
  Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.
• B.1 SELECT CustomerID, N
  2 ,Top5= TOP (0,95) PERCENT Sales (ORDER BY N) OVER (PARTITION BY Sales)
  3 FROM Customers;
  Add TOP and PERCENT Sales on line 2.
  Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.
  PERCENT
  Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.