CORRECT TEXT
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
select lastname, max(Orderdate)as MostRecentOrderDate
from Customer c
inner join Orders o on o.customerID=c.customerID
group by orderID
order by MostRecentOrderDate
We need to group by OrderID, because if we group by last name then two customers with the same Last name will be shown only one time
0
0
You’re only partially right.
You cannot group by orderID, because in select list you’ve got lastname not contained in any aggregation function. You need to group by both c.CustomerID and c.LastName. Lastly the statement says: “return the most recent orders first”, so you need to order by MostRecentOrderDate DESC:
select LastName, MAX(OrderDate) as MostRecentOrderDate
from Customer as c inner join Orders as o on c.CustomerID = o.CustomerID
group by c.CustomerID, c.LastName
order by MostRecentOrderDate desc
0
0
Can you explain, why you are grouping by two columns (LastName, CustomerId)? Does this query produce same result?
select LastName, MAX(OrderDate) as MostRecentOrderDate
from Customer as c inner join Orders as o on c.CustomerID = o.CustomerID
group by c.LastName
order by MostRecentOrderDate desc
0
0
select C.LastName,max(O.OrderDate) as MostRecentOrderDate
from Customers C inner join Orders O
on C.CustomerID = O.CustomerID
group by C.CustomerID
0
0
BTW, part of that new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0