I found this screen a little confusing too. However, I think your suggestions might be a little confusing as well…or maybe it’s just me?
Since employees have multiple customers but customers don’t have multiple employees assigned to them, the answer to your suggestion here would always be = 1, regardless of the conditions you place on the customer. Also, the use of
OR here is confusing because that implies we should be using
UNION when in fact we should be using
This suggestion is confusing to me because every employee is actually located in Canada and employees do not spend money in the store…that’s what customers do! Perhaps what you meant was that these two agents are the only agents with customers who live in the USA that spent over $90. This is true. Therefore, any agents other than these two will have a big fat 0 next to their name in the final result because we are asked to find the total number (a
COUNT) of customers who fit a particular criteria for each sales agent. So, for all the other agents, the number of customers that they have is 0.
INNER JOIN will only return rows that appear in both tables, we will lose rows from
employee for agents who do not have any customers that meet our criteria. As mentioned above, that’s not what we want. The question is really asking for summary statistics on the employees who have customers with particular properties and NOT about the customers themselves. I guess this is what DQ meant by learning to “think in SQL!”
I think it just takes some time to wrap your head around the question which is actually very clear:
Write a query that works out how many customers that are in the USA and have purchased more than $90 are assigned to each sales support agent.
Since there are three
Sales Support Agents, our final result should have three rows, even if that sales agent doesn’t have customers in the USA who spend over $90…it just means that the answer to “how many customers” will be zero for that agent.
It finally made sense to me when I put parenthesis around the customer condition part of the question and just read it without that text:
Write a query that works out how many customers (that are in the USA and have purchased more than $90) are assigned to each sales support agent.
So, in the end, I believe this screen shouldn’t be rephrased for clarity but rather re-read for clarity.
I know this is quite a late response to your original post but since SQL is a popular topic for generating questions, others mind find this discussion helpful…or at least I can hope it is!