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
.. Group by ..id..
with Orderdate (customerid,MostRecentOrderDate)
as(select customerid,max(orderdate)
from
orders as o
–Use the first initial of the table as an alias.
join
customers as c
–Use the first initial of the table as an alias.
on o.customerid=c.customerid
–Return the most recent order date for each customer.
group by customerid)
select
c.lastname as Lastname
–Retrieve the last name of the person who placed the order.
o.MostRecentOrderDate
–Return the order date in a column named MostRecentOrderDate that appears as the last column in
–the report
from
orderdate as o
join
customerid as c
order by o.MostRecentOrderDate desc
–Return the most recent orders first.
GROUP BY C.lastname, O.orderdate
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 O.OrderDate DESC
declare @customers table (
customerid int identity (1,1),
lastname varchar(50)
)
declare @orders table (
orderid int identity (1,1),
orderdate datetime,
customerid int
)
insert into @customers (lastname) values(‘smith’)
insert into @customers (lastname) values(‘smith’)
insert into @orders (orderdate, customerid) values (‘1/10/2017’, 1)
insert into @orders (orderdate, customerid) values (‘1/05/2017’, 1)
insert into @orders (orderdate, customerid) values (‘1/09/2017’, 2)
select c.lastname, max(orderdate) as mostrecentorderdate
from @customers c
inner join @orders o on c.customerid = o.customerid
group by c.customerid, c.lastname
order by mostrecentorderdate desc
SELECT c.LastName, Max(o.OrderDate) AS MostRecentOrderDate
FROM CUSTOMERS c
INNER JOIN ORDERS o
ON c.CustomerID = o.CustomerID
–Return the most recent order date for each customer.
GROUP BY c.CustomerID, c.LastName
ORDER BY MostRecentOrderDate DESC
You have to Group by the CustomerID. Otherwise different Customers with the same Lastname are grouped together
then you have to add CustomerID in SELECT, if you are going to use it in ORDER BY