Pass exam with 070-461 Top Exam Collection for sure one-shot

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server.
The database contains two tables that have the following definitions:

Global customers place orders from several countries.
You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?

• A. SELECT CustomerID, CustomerName, ShippingCountry
  FROM
  (SELECT c.CustomerID, c.CustomerName,
  o. ShippingCountry,
  RANK() OVER (PARTITION BY c. CustomerID
  ORDER BY o. OrderAmount DESC) AS Rnk
  FROM Customer c
  INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID
  GROUP BY c.CustomerID, c.CustomerName,
  o.ShippingCountry) cs
  WHERE Rnk = 1
• B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
  FROM Customer c
  INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  RANK() OVER (PARTITION BY CustomerID
  ORDER BY COUNT(OrderAmount) DESC) AS Rnk
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  Where o.Rnk = 1
• C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry
  FROM Customer c
  INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  COUNT(OrderAmount) AS OrderAmount
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  ORDER BY OrderAmount DESC
• D. SELECT CustomerID, CustomerName, ShippingCountry
  FROM
  (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry,
  RANK() OVER (PARTITION BY c.CustomerID
  ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
  FROM Customer c
  INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID
  GROUP BY c.CustomerID, c.CustomerName,
  o.ShippingCountry) cs
  WHERE Rnk = 1

You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements:
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
Which four 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.)

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

You write the following Transact-SQL query:

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?

• A. Add a clustered index on SalesOrderId in SalesOrderHeader.
• B. Update statistics on SalesOrderId on both tables.
• C. Use a FORCESCAN hint in the query.
• D. Use a FORCESEEK hint in the query.

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.

You have a SQL Server database that contains all of the sales data for your company.
You need to create a query to update the rows in a table. The solution must write original and updated values to a separate table.
Which clause should you add to the UPDATE statement in the query?

• A. INSERT
• B. ReadText
• C. WRITETEXT
• D. OUTPUT