Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you use?

• A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
  FROM (
  SELECT SalesOrderId, SalesAmount
  FROM DomesticSalesOrders
  UNION ALL
  SELECT SalesOrderId, SalesAmount
  FROM InternationalSalesOrders
  ) AS p
• B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
  FROM (
  SELECT SalesOrderId, SalesAmount
  FROM DomesticSalesOrders
  UNION
  SELECT SalesOrderId, SalesAmount
  FROM InternationalSalesOrders
  ) AS p
• C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders
• D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format:
<row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00"
Name="Customer A" Country="Australia" />
<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00"
Name="Customer A" Country="Australia" />
Which Transact-SQL query should you use?

• A. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')
• B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
• C. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
• D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
• E. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId,
  OrderDate, Amount
  FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.
  CustomerId
  WHERE Customers.CustomerId = 1
  FOR XML PATH ('Customers')
• F. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
• G. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
• H. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO

You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)

You have the following query:

You need to recreate the query to meet the following requirements:
Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.
Which code segment should you use?
To answer, type the correct code in the answer area.

• A. SELECT SalesTerritoryID,
  ProductID,
  AVG(UnitPrice),
  MAX(OrderQty),
  MAX(DiscountAmount)
  FROM Sales.Details
  ORDER BY SalesTerritoryID DESC, ProductID DESC
• B. SELECT SalesTerritoryID, ProductID, AVG(UnitPrice), MAX(OrderQty), MAX(DiscountAmount) FROM Sales.Details GROUP BY SalesTerritoryID,ProductID ORDER BY SalesTerritoryID DESC, ProductID DESC

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 ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2. Which Transact-SQL query should you use?

• A. 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
• B. 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
• C. SELECT StudentCode as Code,
  NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value
  FROM StudentMarks
  GROUP BY StudentCode
• D. SELECT Id, Name, Marks,
  DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank
  FROM StudentMarks
• E. SELECT StudentCode as Code,
  RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value
  FROM StudentMarks
  GROUP BY StudentCode
• F. SELECT StudentCode as Code,
  DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value
  FROM StudentMarks
  GROUP BY StudentCode
• G. 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
• H. 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