Yeah they seem quite obvious really. Serious question: is this really the types of questions asked for a dev job interview? (was still interesting to see.)
>List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)
inner join seems the non obvious way to do it really IMO.
select
departments.name as "department name",
(select sum(salary) from employees where employees.departmentid = departments.departmentid) as "department total salary"
from departments
The empty department remark is the clue. They want all departments; if it's an empty department they still want to see that it's an empty department.
An inner join will hide that row because there's no equality between a set (departments) and an empty set (employees in that dept, of which there are none). A correctly structured outer join will.
>List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)
inner join seems the non obvious way to do it really IMO.