CORRECT TEXT
You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit
button).
You need to create a query for a report. The query must meet the following requirements:
NOT use object delimiters.
Use the first initial of the table as an alias.
Return the most recent order date for each customer.
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.
Answer: See the explanation
Explanation:
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
ORDER BY is not required in text.
Anyway, it should be:
ORDER BY MostRecentOrderDate DESC
0
0
both options could be used (just checked)
0
0
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
0
0
While “ORDER BY MAX(O.OrderDate) DESC” in the last line will work, I think they are looking for the alias “MostRecentOrderDate” instead of “MAX(O.OrderDate) DESC”. Since it is on the ORDER BY line, the select statement has already completed, making the alias available for manipulation.
Something to consider…
0
0
And, part of that new 200Q 70-461 dumps for your reference:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0
Badly worded question again, they advise that “Part of the correct T-SQL statement has been provided in the answer area. Provide the complete
code.” and the “Correct” solution states
SELECT
LastName,
MAX(OrderDate) AS MostRecentOrderDate
Then add the table aliases in the solution, making this contradict.
0
0
You have to also Group by C.CustomerID, since C.LastName could return duplicates:
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, C.CustomerID
ORDER BY MAX(O.OrderDate) DESC
0
0