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
The solution adds an order by clause which is not required by the question, and I think it is not correct, because if two customers have the same Lastname they will be grouped together. The grouping field must be CustomerID…..
0
0
You’re right, you cannot group by only lastName because two different customers with same LastName would be considered as one.
select LastName, MAX(OrderDate) as MostRecentOrderDate
from Customers as c join Orders as o on c.CustomerID = o.CustomerID
group by c.CustomerID, c.LastName
what about the ansi sql-99 standard?
0
0
I think there are two possible approaches here, aren’t they?
approach 1:
…
FROM Orders o
INNER JOIN Customer c ON o.CustomerID = c.CustomerID
approach 2:
…
FROM Customers c
INNER JOIN Orders o ON o.CustomerID = c.CustomerID
0
0
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:
SELECT c.CustomerID — optional
c.LastName, max(o.OrderDate) ‘MostRecentOrderDate’
FROM Customer c LEFT OUTER JOIN Orders o ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.LastName
ORDER BY 3 DESC
0
0