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
Shouldn’t the ORDER BY reference MostRecentOrderDate i/o MAX(O.OrderDate)?
0
0
It does not matter MostRecentOrderDate or MAX(O.OrderDate).
However, ORDER BY is redundant since the question does not ask for it.
1
0
No you have to put it cause it asks “• Return the most recent order date for each customer.”
And the redundant thing is the group by clause
0
0
without “Group BY” you cannot use aggregate functions in result table..
0
0
What’s the meaning with “The solution must support the ANSI SQL-99 standard.” Is there an other solution without supporting ANSI SQL-99 standard?
0
0
He should say to use ‘FROM Customers AS C INNER JOIN Orders AS O
ON C.CustomerID=O.CustomerID’ instead of ‘FROM Customers as c, Orders AS O
where C.CustomerID=O.CustomerID’
0
0
I think the above query is not the right answer, suppose we have two customers with the same last name, the above query will select the one that have the most recent order.
I propose the below answer
Select C.LastName,Max(O.orderDate) as MostRecentOrderDate from Customers C
inner join Orders O on c.CustomerID=O.CustomerID
group by O.CustomerID, C.lastName
0
0
Mohamed, you are right when it comes to grouping. The only thing to add is order by MostRecentOrderDate desc
0
0
Sites of interest we have a link to.
0
0
Here are some of the web sites we recommend for our visitors.
0
0
Here are a number of the web-sites we advise for our visitors.
0
0
Every as soon as in a whilst we decide on blogs that we read. Listed beneath would be the newest websites that we choose.
0
0
Wonderful story, reckoned we could combine a few unrelated information, nonetheless definitely really worth taking a search, whoa did a single study about Mid East has got additional problerms at the same time
0
0
here are some hyperlinks to internet sites that we link to for the reason that we think they’re worth visiting
0
0
that may be the end of this article. Here youll find some internet sites that we assume youll enjoy, just click the links over
0
0
usually posts some quite fascinating stuff like this. If youre new to this site
0
0