Note: This question is part of a series of questions that present the same scenario. Each question in
the series contains a unique solution that might meet the stated goals. Some question sets might have
more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these
questions will not appear in the review screen.
You have a database that includes the tables shown in the exhibit (Click the Exhibit button.)
You need to create a Transact-SQL query that returns the following information:
the customer number
the customer contact name
the date the order was placed, with a name of DateofOrder
a column named Salesperson, formatted with the employee first name, a space, and the employee last
name
orders for customers where the employee identifier equals 4
The output must be sorted by order date, with the newest orders first.
The solution must return only the most recent order for each customer.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A.
Yes
B.
No
Explanation:
We need a GROUP BY statement as we want to return an order for each customer.
Alias is missing on c.contactname
Group by c.custid, c.contactname, e.firstname+ ‘ ‘ +e.lastname
8
0
a simple GROUP BY will not solve the requirement
“The solution must return only the most recent order for each customer.”
Doing a simple group by will show all orders related to employee 4. And for all orders the same DateofOrder per customer will be shown (depending on the group by condition).
0
5
the correct answer is NO.falta agrupar (GROUP BY )
SELECT c.custid,contactname,max(orderdate)as dateOrder ,
e.firstname+”+e.lastname as salesPerson
FROM SALES.CUSTOMERS as c
INNER JOIN SALES.ORDERS AS o
ON C.custid=O.custid
INNER JOIN SALES.EMPLOYEES AS e
ON O.empid=E.empid
where o.empid=4
group by c.custid,contactname,e.firstname,e.lastname
ORDER BY dateOrder DESC
OR
SELECT c.custid,c.contactname,max(orderdate)as dateOrder ,
e.firstname+”+e.lastname as salesPerson
FROM SALES.CUSTOMERS as c
INNER JOIN SALES.ORDERS AS o
ON C.custid=O.custid
INNER JOIN SALES.EMPLOYEES AS e
ON O.empid=E.empid
where o.empid=4
group by c.custid,c.contactname,e.firstname,e.lastname
ORDER BY dateOrder DESC
IN PERFORMANCE IS EQUALS
2
4