Example queries against this table

1. 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;

2. 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

3. CTE with MATERIALIZED hint for performance

   SQLCopy

   WITH MATERIALIZED expensive_calc AS (
     -- This CTE will be computed once and stored
     SELECT
       emp_id,
       SUM(hours) AS total_hours,
       COUNT(DISTINCT project_id) AS project_count
     FROM project_assignments
     GROUP BY emp_id
   )
   SELECT
     e.first_name,
     e.last_name,
     ec.total_hours,
     ec.project_count,
     ROUND(e.salary / NULLIF(ec.total_hours, 0), 2) AS hourly_cost
   FROM employees e
   JOIN expensive_calc ec ON e.emp_id = ec.emp_id
   ORDER BY hourly_cost DESC;

4. Employee project summary with total hours

   SQLCopy

   SELECT
     e.first_name || ' ' || e.last_name AS employee,
     e.job_title,
     COUNT(DISTINCT pa.project_id) AS projects_count,
     SUM(pa.hours) AS total_hours,
     MAX(pa.assigned_on) AS latest_assignment
   FROM employees e
   LEFT JOIN project_assignments pa ON e.emp_id = pa.emp_id
   GROUP BY e.emp_id, e.first_name, e.last_name, e.job_title
   ORDER BY total_hours DESC NULLS LAST;