Example queries against this table

1. LATERAL JOIN: employee with last salary change

   SQLCopy

   SELECT
     e.first_name,
     e.last_name,
     e.salary AS current_salary,
     lsh.old_salary,
     lsh.new_salary,
     lsh.changed_at,
     lsh.reason
   FROM employees e
   LEFT JOIN LATERAL (
     SELECT *
     FROM salary_history sh
     WHERE sh.emp_id = e.emp_id
     ORDER BY sh.changed_at DESC
     LIMIT 1
   ) lsh ON TRUE;

2. Stored Functions: salary raise with validation

   SQLCopy

   CREATE OR REPLACE FUNCTION give_raise(
     p_emp_id    INT,
     p_pct       NUMERIC,
     p_reason    TEXT DEFAULT 'Annual review'
   )
   RETURNS TABLE(emp_id INT, old_salary NUMERIC, new_salary NUMERIC) AS $$
   DECLARE
     v_old_sal NUMERIC;
     v_new_sal NUMERIC;
   BEGIN
     IF p_pct <= 0 OR p_pct > 50 THEN
       RAISE EXCEPTION 'Raise percentage must be between 0 and 50. Got: %', p_pct;
     END IF;
   
     SELECT salary INTO v_old_sal FROM employees WHERE employees.emp_id = p_emp_id;
   
     IF NOT FOUND THEN
       RAISE EXCEPTION 'Employee % not found', p_emp_id;
     END IF;
   
     v_new_sal := ROUND(v_old_sal * (1 + p_pct/100), 2);
   
     UPDATE employees SET salary = v_new_sal WHERE employees.emp_id = p_emp_id;
   
     INSERT INTO salary_history(emp_id, old_salary, new_salary, reason)
     VALUES (p_emp_id, v_old_sal, v_new_sal, p_reason);
   
     RETURN QUERY SELECT p_emp_id, v_old_sal, v_new_sal;
   END;
   $$ LANGUAGE plpgsql;
   
   -- Usage:
   SELECT * FROM give_raise(101, 10.5, 'Promotion');

3. INSERT FROM SELECT: copy data between tables

   SQLCopy

   INSERT INTO salary_history (emp_id, old_salary, new_salary, reason)
   SELECT
     emp_id,
     salary AS old_salary,
     salary * 1.10 AS new_salary,
     'Annual increment 2024'
   FROM employees
   WHERE status = 'active'
     AND hire_date < '2024-01-01';

4. Find employees with consecutive salary increases

   SQLCopy

   WITH salary_changes AS (
     SELECT
       emp_id,
       changed_at,
       new_salary,
       LAG(new_salary) OVER (PARTITION BY emp_id ORDER BY changed_at) AS prev_salary
     FROM salary_history
   ),
   increases AS (
     SELECT
       emp_id,
       COUNT(*) AS increase_count,
       BOOL_AND(new_salary > prev_salary) AS all_increases
     FROM salary_changes
     WHERE prev_salary IS NOT NULL
     GROUP BY emp_id
   )
   SELECT
     e.first_name,
     e.last_name,
     i.increase_count
   FROM increases i
   JOIN employees e ON i.emp_id = e.emp_id
   WHERE i.all_increases = TRUE
   ORDER BY i.increase_count DESC;