Example queries against this table

1. CROSS JOIN: all employee-project combinations

   SQLCopy

   SELECT
     e.first_name,
     e.last_name,
     p.name AS project
   FROM employees e
   CROSS JOIN projects p
   LIMIT 50;

2. Find employees assigned to projects with JOIN

   SQLCopy

   SELECT
     e.first_name,
     e.last_name,
     p.name AS project_name,
     pa.role,
     pa.hours,
     d.dept_name
   FROM employees e
   JOIN project_assignments pa ON e.emp_id = pa.emp_id
   JOIN projects p ON pa.project_id = p.project_id
   JOIN departments d ON e.dept_id = d.dept_id
   ORDER BY p.name, e.last_name;

3. Find departments with their total project budget

   SQLCopy

   SELECT
     d.dept_name,
     d.budget AS dept_budget,
     COUNT(p.project_id) AS project_count,
     COALESCE(SUM(p.budget), 0) AS total_project_budget
   FROM departments d
   LEFT JOIN projects p ON d.dept_id = p.dept_id
   GROUP BY d.dept_id, d.dept_name, d.budget
   ORDER BY total_project_budget DESC;

4. Employees NOT assigned to any project (anti-join)

   SQLCopy

   SELECT e.first_name, e.last_name, e.job_title
   FROM employees e
   LEFT JOIN project_assignments pa ON e.emp_id = pa.emp_id
   WHERE pa.assign_id IS NULL; -- Unmatched = no projects