- Date format will be yyyymmdd (if not, assume that we converted in Oracle or Sybase)
SELECT a.name, SUM(s.price) FROM authors a
INNER JOIN books b ON (a.id=b.author_id)
LEFT OUTER JOIN sales s ON (b.id = s.book_id)
GROUP BY a.name
HAVING (s.date >= '20131101' AND s.date <= '20131130')
In SQL, this would be an issue due to the date column not existing in the group by. Additionally, because of the having statement, the left outer join is being treated as an inner join with respect to the results given.
Something like this could work (without going for an alternate approach such as Unions or sub-selects):
SELECT a.name, ISNULL(SUM(s.price), 0.0) FROM authors a
INNER JOIN books b ON (a.id=b.author_id)
LEFT OUTER JOIN sales s ON (b.id = s.book_id)
GROUP BY a.name, s.date
HAVING (ISNULL(s.date, '20131101') >= '20131101' AND ISNULL(s.date, '20131101') <= '20131130')
Edit: Actually might be better to keep the null in the results like the original query. It's definitely better to know whether a book sold 1000 copies for $0.00 each or if no copies were sold.
Instead of the Left Outer Join, couldn't you also make it an Inner Join to S.BOOK_ID? The question, if I read it correctly, only requires you to return sales in Nov 2013. If there is no data for a book_id, you want to exclude it from your returned data, no?
I would imagine it is up to interpretation. It would probably be a good thing to ask the interviewer about to show that you are thinking about edge cases.
If it were me, I'd prefer to see those that made no sales based on the question. I guess if I didn't want to see them, I would ask for "where sales are greater than $0.00".
- price could be 0.0 for a book
- Total sales means the sum and not count
- Date format will be yyyymmdd (if not, assume that we converted in Oracle or Sybase)