Jitbit's SQL interview questions

Interview-Exercises.sql

CREATE TABLE `department` (
   `id` INT UNSIGNED NOT NULL PRIMARY KEY, 
   `name` VARCHAR(30)
);

CREATE TABLE `employee` (
   `id` INT UNSIGNED NOT NULL PRIMARY KEY, 
   `name` VARCHAR(30), 
   `salary` INT UNSIGNED,
   `boss_id` INT UNSIGNED DEFAULT NULL,
   `department_id` INT UNSIGNED NOT NULL,
   INDEX `employee_boss` (`boss_id`),
   CONSTRAINT `employee_boss` FOREIGN KEY (`boss_id`) REFERENCES `employee` (`id`),
   INDEX `employee_department` (`department_id`),
   CONSTRAINT `employee_department` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
);

INSERT INTO department (id, name) 
VALUES 
(1, "tech"), 
(2, "marketing"), 
(3, "sales"), 
(4, "hr");
INSERT INTO employee (id, name, salary, boss_id, department_id) 
VALUES 
(1, "Jim", 120000, NULL, 1), 
(2, "Joe", 130000, 1   , 1), 
(3, "Tim", 100000, NULL, 2), 
(4, "Bill", 30000, 3   , 2),
(5, "Bob",  90000, 1   , 3),
(6, "Sam",  90000, 1   , 3),
(7, "Jen",  80000, 1   , 3)
;

/*
  === Exercises ===
  1. Select employees (names) who have a bigger salary than their boss
  2. Select employees who have the biggest salary in their departments
  3. Select departments that have less than 3 people in it
  4. Select all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)
  5. Select employees that don't have a boss in the same department
  6. Select all departments along with the total salary there
*/

/*
  ===== Q1 ======
  === Steps and Assumptions ===
  1. Join employee to their boss
  1.1 Assume every employee must have one boss
  1.2 Assume every boss must have at least one employee
  1.3 Assume every boss may not have a boss
  2. Compare salaries of the two
  2.1 Assume that every employee has a salary
  3. Output the employee name only where their salary is greater than their boss
  3.1 Assume that the comparison is valid and useful if and only if the employee has a boss
  4. Verify the output
  Note: The key is to understand that the self join occurs 
  with the employee's boss ID joining itself with the employee ID
  to return the relationship between the employee and their boss
*/

SELECT
  emp.name
FROM employee emp
INNER JOIN employee boss
ON emp.boss_id = boss.id
WHERE 
  1 = 1
  AND emp.salary > boss.salary
ORDER BY emp.name ASC
;

-- Let's validate the output
SELECT
  *
  , CASE
    WHEN emp.salary > boss.salary THEN "TRUE"
    ELSE "FALSE"
    END AS employee_salary_higher_than_boss
FROM employee emp
INNER JOIN employee boss
ON emp.boss_id = boss.id
WHERE
  1 = 1
  -- AND emp.salary > boss.salary
ORDER BY emp.name ASC
;

-- What if we wanted to ignore whether an employee had a boss?
SELECT
  emp.*
  , COALESCE(boss.salary, 0) as boss_salary
  , CASE
    WHEN emp.salary > COALESCE(boss.salary, 0) THEN "TRUE"
    ELSE "FALSE"
    END AS employee_salary_higher_than_boss
FROM employee emp
LEFT JOIN employee boss
ON emp.boss_id = boss.id
WHERE 
  1 = 1
  AND emp.salary > COALESCE(boss.salary, 0)
ORDER BY emp.name ASC
;

-- What if we wanted to find the employees that made more than the average boss salary?
SELECT
  emp.*
  , COALESCE(boss.salary, AVG(boss.salary) OVER (PARTITION BY boss.department_id)) as boss_salary
  , CASE
    WHEN emp.salary > COALESCE(boss.salary, AVG(boss.salary) OVER (PARTITION BY boss.department_id)) THEN "TRUE"
    ELSE "FALSE"
    END AS employee_salary_higher_than_average_boss
FROM employee emp
LEFT JOIN employee boss
ON emp.boss_id = boss.id
WHERE 
  1 = 1
  AND emp.salary > COALESCE(boss.salary, AVG(boss.salary) OVER (PARTITION BY boss.department_id))
ORDER BY emp.name ASC
;

/*
  ===== Q2 ======
  === Steps and Assumptions ===
  1. Join employee to their department
  1.1 Assume every employee must have only one department
  2. Compare salaries of the employees in each department
  3. Output the department and the name of the highest paid employee/s
  3.1 Assume that the comparison is valid and useful even if there are no employees in the department
  4. Verify the output
*/

SELECT
  dept.name AS department_name
  , emp.name AS employee_name
  , emp.salary AS employee_salary
FROM employee emp
RIGHT JOIN department dept
ON emp.department_id = dept.id
WHERE
  1 = 1
  AND (emp.department_id, emp.salary) IN
    (SELECT 
       department_id
       , MAX(salary)
     FROM employee
     GROUP BY department_id
     )

-- What if we wanted to still return departments without employees?
SELECT
  dept.name AS department_name
  , emp.name AS employee_name
  , emp.salary AS employee_salary
FROM employee emp
RIGHT JOIN department dept
ON emp.department_id = dept.id
WHERE
  1 = 1
  AND (dept.id, emp.salary) IN
    (SELECT 
       dept.id
       , MAX(emp.salary)
     FROM employee emp
     RIGHT JOIN department dept
     ON emp.department_id = dept.id
     GROUP BY dept.id
     )
  OR dept.id IS NULL 
  OR emp.salary IS NULL 
;

/*
  ===== Q3 ======
  === Steps and Assumptions ===
  1. Join employee to their department
  1.1 Assume every employee must have only one department
  2. Count the number of employees in each department
  3. Output the count with less than 3 people
  3.1 Assume that the comparison is valid and useful even if there are no employees in the department
  4. Verify the output
  Note: Do not use COUNT(*) as it will include the null row for HR
*/

SELECT
  dept.name
FROM department dept
LEFT JOIN employee emp
ON dept.id = emp.department_id
GROUP BY dept.id
HAVING COUNT(emp.id) < 3
;

/*
  ===== Q4 ======
  === Steps and Assumptions ===
  1. Join employee to their department
  1.1 Assume every employee must have only one department
  2. Count the number of employees in each department
  3. Output the headcounts for each department
  3.1 Assume that the comparison is valid and useful even if there are no employees in the department
  4. Verify the output
  Note: Do not use COUNT(*) as it will count the null row for HR
*/

SELECT
  dept.name
  , COUNT(emp.id)
FROM department dept
LEFT JOIN employee emp
ON dept.id = emp.department_id
GROUP BY dept.id
;

/*
  ===== Q5 ======
  === Steps and Assumptions ===
  1. Join employee to their boss
  1.1 Assume every employee must have one boss
  1.2 Assume every boss must have at least one employee
  1.3 Assume every boss may not have a boss
  2. Compare the departments of the two
  2.1 Assume that every employee must have only one department
  3. Output the employee name only where the departments are different
  3.1 Assume that the comparison is valid and useful if and only if the employee has a boss
  4. Verify the output
  Note: The key is to understand that the self join occurs 
  with the employee's boss ID joining itself with the employee ID
  to return the relationship between the employee and their boss
*/

SELECT
  emp.name
FROM employee emp
INNER JOIN employee boss
ON emp.boss_id = boss.id
WHERE 
  1 = 1
  AND emp.department_id <> boss.department_id
ORDER BY emp.name ASC
;

-- Let's validate the output
SELECT
  *
  , CASE
    WHEN emp.department_id <> boss.department_id THEN "TRUE"
    ELSE "FALSE"
    END AS employee_department_not_equal_boss
FROM employee emp
INNER JOIN employee boss
ON emp.boss_id = boss.id
WHERE
  1 = 1
--  AND emp.department_id <> boss.department_id
ORDER BY emp.name ASC
;

-- What if we wanted to ignore whether an employee had a boss?
SELECT
  emp.*
  , boss.*
  , CASE
    WHEN COALESCE(emp.boss_id, "N/A") <> COALESCE(boss.boss_id, "N/A") THEN "TRUE"
    ELSE "FALSE"
    END AS employee_department_not_equal_boss
FROM employee emp
LEFT JOIN employee boss
ON emp.boss_id = boss.id
WHERE 
  1 = 1
  AND COALESCE(emp.department_id, "N/A") <> COALESCE(boss.department_id, "N/A")
ORDER BY emp.name ASC
;

/*
  ===== Q6 ======
  === Steps and Assumptions ===
  1. Join employee to their department
  1.1 Assume every employee must have only one department
  2. Sum the salaries of the employees in each department
  3. Output the department and the sum
  3.1 Assume that the comparison is valid and useful even if there are no employees in the department
  4. Verify the output
*/

SELECT
  dept.name AS department_name
  , SUM(emp.salary) AS department_salary_total
FROM employee emp
RIGHT JOIN department dept
ON emp.department_id = dept.id
GROUP BY dept.id
ORDER BY department_salary_total DESC
;

-- Let's validate the output

SELECT
  dept.name AS department_name
  , emp.name AS employee_name
  , emp.salary AS employee_salary
  , SUM(emp.salary) OVER(PARTITION BY dept.id ORDER BY emp.id) AS department_salary_running_total
FROM employee emp
RIGHT JOIN department dept
ON emp.department_id = dept.id
;

SQLFiddle can be found here using MySQL 5.6 and DBFiddle can be found here using MySQL 8.0