Advanced Oracle SQL Query Cheat Sheet
* Recursive Hierarchical Query (Employees under a Manager)
SELECT emp_id, emp_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;
* Find Top Earner Per Department
SELECT *
FROM (
SELECT emp_name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
)
WHERE rnk = 1;
* Percentile Salary Using PERCENT_RANK()
SELECT emp_name, salary,
PERCENT_RANK() OVER (ORDER BY salary DESC) AS percentile
FROM employees;
* Correlated Subquery for Employees Above Dept Average
SELECT emp_name, salary, dept_id
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE dept_id = e.dept_id
);
* Moving Average of Salary
SELECT emp_name, salary,
AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS
moving_avg
FROM employees;
* Dynamic Row to Column Pivoting
SELECT dept_id,
MAX(CASE WHEN gender = 'M' THEN salary END) AS male_salary,
MAX(CASE WHEN gender = 'F' THEN salary END) AS female_salary
FROM employees
GROUP BY dept_id;
* Find Gaps in Date Ranges
SELECT hire_date,
LEAD(hire_date) OVER (ORDER BY hire_date) AS next_date,
LEAD(hire_date) OVER (ORDER BY hire_date) - hire_date AS gap
FROM employees;
* Cumulative Salary by Department
SELECT dept_id, emp_name, salary,
SUM(salary) OVER (PARTITION BY dept_id ORDER BY salary) AS dept_running_total
FROM employees;
* Departments With Increasing Salary Trend
SELECT * FROM (
SELECT emp_name, dept_id, salary,
LAG(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) AS prev_salary
FROM employees
)
WHERE salary > prev_salary;
* Multiple Aggregate Functions in a Single Query
SELECT dept_id,
COUNT(*) AS emp_count,
MAX(salary) AS max_sal,
MIN(salary) AS min_sal,
ROUND(AVG(salary), 2) AS avg_sal
FROM employees
GROUP BY dept_id;
* Find Manager and Count of Subordinates
SELECT e.manager_id, m.emp_name AS manager_name, COUNT(*) AS team_size
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
GROUP BY e.manager_id, m.emp_name;
* Find Employees Joining in Same Month
SELECT emp_name, TO_CHAR(hire_date, 'YYYY-MM') AS month
FROM employees
WHERE TO_CHAR(hire_date, 'MM') IN (
SELECT TO_CHAR(hire_date, 'MM')
FROM employees
GROUP BY TO_CHAR(hire_date, 'MM')
HAVING COUNT(*) > 1
);
* Find Department With Highest Average Salary
SELECT dept_id
FROM (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
ORDER BY avg_sal DESC
)
WHERE ROWNUM = 1;
* Nth Highest Salary per Department (e.g., 2nd Highest)
SELECT *
FROM (
SELECT emp_name, dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
)
WHERE rnk = 2;
* Employees Hired Consecutively
SELECT emp_name, hire_date,
LAG(emp_name) OVER (ORDER BY hire_date) AS prev_emp,
LAG(hire_date) OVER (ORDER BY hire_date) AS prev_hire_date
FROM employees;
