You have a database 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.
• Return the most recent orders first.
• 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.
• Return the order date in a column named MostRecentOrderDate that appears as the
last column in the report.
The solution must support the ANSI SQL-99 standard.
Which code segment should you use?
To answer, type the correct code in the answer area.
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 MostRecentOrderDate DESC
The requirement is to return the most recent order for EACH customer. What if customers have the same LastName?
We should use GROUP BY C.CustomerID instead of C.LastName.
0
0
Oh crap, I forgot that the columns appearing in the SELECT (except aggregation columns) must be in GROUP BY.
In this case we have to accept C.LastName…
0
0
The requirement is to have at least all non-aggregated columns listed in GROUP BY. But GROUP BY may also include columns not present in SELECT list. So this would be correct:
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 MostRecentOrderDate DESC
And indeed it will cover situations when customers have the same LastName.
But i guess this is too deep answer for this exam question and we are not supposed to think that far 🙂
0
0