-- Answers: Practice Questions on Joins in SQL (fireblazeaischool.in/blogs/sql-joins/)
-- Load fireblaze_sql_practice.sql first.

-- @q1 Department headcount, including empty departments (5 rows, Research = 0)
SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

-- @q2 Employees with no department (1 row: Mayur Raut)
SELECT e.emp_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

-- @q3 Every customer with delivered revenue, zero if none (6 rows)
SELECT c.customer_name, COALESCE(SUM(o.amount), 0) AS delivered_revenue
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id AND o.status = 'Delivered'
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

-- @q4 Employees who earn more than their manager (1 row: Neha Bhosale)
SELECT e.emp_name, e.salary, m.emp_name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

-- @q5 Every employee and department, matched or not, on MySQL (12 rows)
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION ALL
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;
