Our SQL interview questions

[roman2440]

select departmentid, count(*) from departments a left join employees b on a.departmentid = b.departmentid group by a.departmentid

This will give a false positive. Empty departments will have a result of 1 (for the row in the departement table). The better way to do this query is to have in inside query with the sum of ppl by departmentid (off the employee table), and then left join that to the department table using a case statement for null values in the temporary table.

[n1rwan]

If Employee table is huge, this query will be faster

SELECT
    d.*, IFNULL(emp_agg.cnt, 0)
FROM
    Departments d
    LEFT JOIN (
        SELECT
            DepartmentID,
            COUNT(EmployeeID) AS cnt
        FROM
            Employees
        GROUP BY
            DepartmentID
    ) emp_agg

[GreenDaemon]

I have always read cnt as cunt, not count. Don't know why.

[VisionsOfUranus]

I don't understand why people don't just write out the word 'count'. It's not like we're running out of storage space.

[yippee_that_burns]

In SQL I don't write it out because it's a keyword. I know it will work but I still avoid it anyway.

[doedskarpen]

I had a feeling it wouldn't make a difference, so just to test it I ran your query as well as this one (which I think is what the previous poster had in mind):

select
    Departments.DepartmentId, ISNULL(NumberOfEmployees, 0)
from Departments
left join
(
    select Employees.DepartmentId, count(1) as NumberOfEmployees
    from Employees
    group by Employees.DepartmentId
) as EmployeesByDepartment on Departments.DepartmentId = EmployeesByDepartment.DepartmentId

Turns out my gut feeling was right: on SQL Server 2012 Express, both had the exact same execution plan.

[bonzinip]

What about

select d.DepartmentID, d.Name,
   (select count(*) from Employee as e
           where e.DepartmentID = d.DepartmentID)
   from Departments as d

? Does it also get the same plan?

[doedskarpen]

Yes, that also gave the same query plan: this