My quick answers for Jitbit's SQL interview questions. http://www.jitbit.com/news/181-jitbits-sql-interview-questions/ Discussion here: https://news.ycombinator.com/item?id=5779406 Sample data here: https://gist.github.com/abkr/5662615

gistfile1.sql

1. List employees (names) who have a bigger salary than their boss

  	SELECT
	e.`name`
	FROM
	employees AS e
	INNER JOIN employees AS b ON e.bossid = b.employeeid
	WHERE
	e.salary > b.salary

2. List employees who have the biggest salary in their departments

	SELECT
	emain.`name`,
	emain.salary
	FROM
	employees AS emain
	WHERE
		emain.employeeid = (
			SELECT
			esub.employeeid
			FROM
			employees AS esub
			WHERE
			esub.departmentid = emain.departmentid
			ORDER BY
			esub.salary DESC
			LIMIT 0, 1
		)
	GROUP BY
	emain.departmentid

3. List departments that have less than 3 people in it

	SELECT
	departments.`name`
	FROM
	departments
	LEFT JOIN employees ON employees.departmentid = departments.departmentid
	GROUP BY
	departments.departmentid
	HAVING
	COUNT(*) < 3

4. List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)

	SELECT
	departments.`name`,
	Count(employees.employeeid) as count
	FROM
	departments
	LEFT JOIN employees ON employees.departmentid = departments.departmentid
	GROUP BY
	departments.departmentid

5. List employees that don't have a boss in the same department

	SELECT
	e.`name`
	FROM
	employees AS e
	INNER JOIN employees AS b ON e.bossid = b.employeeid
	WHERE
	e.departmentid <> b.departmentid

6. List all departments along with the total salary there

	SELECT
	departments.`name`,
	Sum(employees.salary)
	FROM
	departments
	LEFT JOIN employees ON employees.departmentid = departments.departmentid
	GROUP BY
	departments.`name`

You aren't catching all departments by left joining on employee.department_id

@johnklehm: Which problem are you referring to?

Second answer is not correct. If you use GROUP BY without an aggregate function it will just take the first row of each group.

SELECT e.Name AS 'Employee', d.Name AS 'Department', MAX(e.Salary) AS 'Salary' FROM Departments d INNER JOIN Employees e ON e.DepartmentID = d.DepartmentID GROUP BY d.DepartmentID;

Thanks for pointing that out @miguelangelnieto. I don't think I've ever come across that before (or I've just never noticed before!).

In fact I think we were both wrong. See https://news.ycombinator.com/item?id=5779842 for more info.

I've updated answer 2 now.