Exam 70-761 topic 1 question 23 discussion

Actual exam question from Microsoft's 70-761

Question #: 23
Topic #: 1

DRAG DROP -
You have a database that includes the following tables:

You need to create a list of all customer IDs and the date of the last order that each customer placed. If the customer has not placed any orders, you must return the date January 1, 1900. The column names must be CustomerID and LastOrderDate.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-
SQL segments to the answer area and arrange them in the correct order.
Select and Place:

Show Suggested Answer

Suggested Answer:

Box 1: SELECT..COALESCE"¦
The COALESCE function evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
Box 2: ..LEFT OUTER JOIN..
The LEFT JOIN (LEFT OUTER JOIN) keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. A customer might have no orders so the right table must be allowed have a NULL value.

Box 3: ON c.custid = o.custid -
We JOIN on the custID column, which is available in both tables.

Box 4: GROUP BY c.custid -
References:
https://technet.microsoft.com/en-us/library/ms189499(v=sql.110).aspx http://www.w3schools.com/sql/sql_join_left.asp

by fabzo at Oct. 29, 2019, 1 p.m.

Comments

Submit Cancel

Robintang0924

Highly Voted 5 years, 3 months ago

2 issues here for Fabzo's question: 1. From data meaning and group by function perspective: we group by only custid column and use aggregate function on last order date means: for those records with same custid(in the same group), find me (only one) max date so our final result would be only one max date for each custid. If we group by both custid and orderdate column then for every custid we will get many order/date back which is not what we wanted -- we only need last order of customer. 2. From SQL syntax perspective, we can't refer to an alias(like LastOrderDate) in group by function in whatever situation because alias was evaluated/assigned value AFTER group by clause thus it will error out regardless if it makes sense from data meaning perspective or not.

upvoted 5 times

...