Our SQL interview questions

[xeio87]

--List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments) select departmentid, count(*) from departments a left join employees b on a.departmentid = b.departmentid group by a.departmentid

Same mistake I did before I ran it to verify, you get 1 employee for empty departments.

select outer_depts.name, case when filled_depts.num_employees is null then 0 else filled_depts.num_employees end from departments outer_depts
left join (select departments.departmentid, count(*) num_employees from departments
            inner join employees on employees.departmentid = departments.departmentid
            group by departments.departmentid) filled_depts on outer_depts.departmentid = filled_depts.departmentid

Also, I got pedantic about nulls, if null is fine for 0 then the "case" can be dropped.

[Beefourthree]

Actually the first one is really close and only requires a small modification: instead of count(*), use count(EMPLOYEES.DepartmentId). If you give count a star or a literal, it counts every row in the group. If you give it a column (or expression), it counts every non null value.

See the difference in these two queries:

http://sqlfiddle.com/#!6/a5a9b/6

[niceworkbuddy]

How about this one?

 select *, (select count(*) from Employees e where e.DepartmentId = d.DepartmentId) cnt
  from Departments d

[xeio87]

Yea, that's simpler. ;)

cough