JitBit SQL Interview Questions Answered

JitBitSQLAnswers.sql

-- JitBit SQL Interview Questions (Posted on Hacker News)
-- http://www.jitbit.com/news/181-jitbits-sql-interview-questions/?utm_source=hackernewsletter&utm_medium=email

-- Schema:
-- employees table
  -- EmployeeID
  -- DepartmentID
  -- BossID
  -- Name
  -- Salary
-- departments table
  -- DepartmentID
  -- Name

-- List employees (names) who have a bigger salary than their boss
SELECT *
  FROM employees
  JOIN employees AS bosses
    ON employees.BossID = bosses.id
 WHERE employees.salary > bosses.salary

-- List employees who have the biggest salary in their departments
SELECT *
  FROM employees
  JOIN employees AS colleagues
    ON employees.DepartmentID = colleagues.DepartmentID
 WHERE employees.salary >= colleagues.salary

-- List departments that have less than 3 people in it
  SELECT *,
         COUNT(employees.DepartmentID) AS employeeCount
    FROM departments
    JOIN employees
      ON employees.DepartmentID = departments.DepartmentID
GROUP BY departments.DepartmentID
  HAVING employeeCount < 3

-- List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)
   SELECT *,
          COUNT(employees.DepartmentID) AS employeeCount
     FROM departments
LEFT JOIN employees
       ON employees.DepartmentID = departments.DepartmentID
 GROUP BY departments.DepartmentID

-- List employees that don't have a boss in the same department
SELECT *
  FROM employees
  JOIN employees AS bosses
    ON employees.id = bosses.BossID
 WHERE employees.DepartmentID != bosses.DepartmentID

-- List all departments along with the total salary there
   SELECT *,
          SUM(employees.Salary) AS total_salary
     FROM departments
LEFT JOIN employees
       ON departments.DepartmentID = employees.DepartmentID
 GROUP BY departments.DepartmentID