Lab Cycle 3 — Set Operations, Advanced/Recursive CTEs & TCL

-- Setup Employee Table
CREATE TABLE IF NOT EXISTS Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    salary DECIMAL(10,2),
    dept_id INT,
    dept_name VARCHAR(50),
    manager_id INT
);

-- 1. Find 5th highest earning employee details using DENSE_RANK()
WITH RankedEmployees AS (
    SELECT 
        emp_id, 
        emp_name, 
        salary, 
        dept_name,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_num
    FROM Employee
)
SELECT emp_id, emp_name, salary, dept_name
FROM RankedEmployees
WHERE rank_num = 5;

-- 2. Department with highest count of employees with salary > 80,000 & percentage
WITH DeptStats AS (
    SELECT 
        dept_name,
        COUNT(*) AS total_emp_in_dept,
        SUM(CASE WHEN salary > 80000 THEN 1 ELSE 0 END) AS high_earners_count,
        ROUND(100.0 * SUM(CASE WHEN salary > 80000 THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_above_80k
    FROM Employee
    GROUP BY dept_name
)
SELECT dept_name, high_earners_count, total_emp_in_dept, pct_above_80k
FROM DeptStats
ORDER BY high_earners_count DESC
LIMIT 1;

-- 3. Average salary for employees in top 2 departments with highest average salary
WITH TopDepts AS (
    SELECT dept_name, AVG(salary) AS dept_avg_salary
    FROM Employee
    GROUP BY dept_name
    ORDER BY dept_avg_salary DESC
    LIMIT 2
)
SELECT dept_name, ROUND(dept_avg_salary, 2) AS avg_salary
FROM TopDepts;