SQL Joins · Intermediate

SELECT d.name, COUNT(e.id) AS staff
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.name
ORDER BY d.name;

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

SELECT d.name AS dept, e.name
FROM departments d
LEFT JOIN employees e
  ON e.dept_id = d.id AND e.salary > 60000
ORDER BY d.id, e.id;

SELECT d.name FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

SELECT COUNT(*) AS total FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees);

SELECT d.name AS dept, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.salary > 60000
ORDER BY d.id, e.id;

SELECT name FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 20)
ORDER BY name;

SELECT e.name FROM employees e
WHERE EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.id)
ORDER BY e.id;

SELECT d.name, COALESCE(SUM(e.salary), 0) AS total
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY d.id;

SELECT MAX(total) AS top_total
FROM (
  SELECT dept_id, SUM(salary) AS total
  FROM employees WHERE dept_id IS NOT NULL
  GROUP BY dept_id
) t;

SELECT MAX(total)
FROM (SELECT SUM(salary) AS total FROM employees GROUP BY dept_id);

SELECT COUNT(*) AS total
FROM employees a
JOIN employees b ON a.dept_id = b.dept_id;