Microsoft 070-457 valid study dumps : Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

You have a database that contains the tables as shown below:

You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements:
The code must NOT use aliases.
The code must NOT use object delimiters.
The objects called in Procedure1 must be able to be resolved by all users.
OrderDate must be compared to Parameter1 after the data type of Parameter1 is changed to datetime.
Which SELECT statement should you use?
To answer, type the correct code in the answer area.

• A. SELECT Orders.OrderID FROM Orders WHERE Orders.OrderDate>CONVERT(datetime,@Parameter1)
• B. SELECT OrderID FROM Orders WHERE OrderDate>CONVERT(datetime,@Parameter1)

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

• A. An UPDATE statement that includes CASE
• B. The foreach SQLCLR statement
• C. Trigger
• D. a recursive stored procedure
• E. Cursor

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. You need to retrieve the students who scored the highest marks for each subject along with the marks. Which Transact-SQL query should you use?

• A. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• B. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
• C. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• D. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• E. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks
• F. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• G. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
• H. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode

You administer all the deployments of Microsoft SQL Server 2012 in your company. You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail. You also need to minimize the amount of storage used by the database. Which configuration should you use?

• A. * Two servers configured in a Windows Failover Cluster in the same data center * SQL Server configured as a clustered instance
• B. * Two servers configured in different data centers * SQL Server Availability Group configured in Synchronous-Commit Availability Mode * One server configured as an Active Secondary
• C. * Two servers configured in the same data center * SQL Server Availability Group configured in Asynchronous-Commit Availability Mode * One server configured as an Active Secondary
• D. * Two servers configured in the same data center * A primary server configured to perform log-shipping every 10 minutes * A backup server configured as a warm standby
• E. * SQL Server that includes an application database configured to perform snapshot replication
• F. * SQL Server that includes an application database configured to perform transactional replication
• G. * Two servers configured in different data centers * SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• H. * Two servers configured on the same subnet * SQL Server Availability Group configured in Synchronous-Commit Availability Mode

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 c.CustomerID, c.CustomerName, o.ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY 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
• B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  COUNT(OrderAmount) DESC) AS OrderAmount
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  ORDER BY OrderAmount DESC
• C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  RANK() OVER (PARTITION BY CustomerID
  ORDER BY OrderAmount DESC) AS Rnk
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  WHERE o.Rnk = 1
• D. 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