Basic Subqueries
1. Simple Subquery in SELECT
Description: This query uses a scalar subquery in the SELECT list to display the department name for each employee.
SELECT employee_id, (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name
FROM employees;2. Simple Subquery in WHERE Clause
Description: This query filters employees who belong to the Sales department.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');3. Subquery with IN Operator
Description: This query returns employees whose department is located in location 1400.
SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1400);4. Subquery with NOT IN Operator
Description: This query returns employees who are not in the Marketing department.
SELECT employee_id, employee_name
FROM employees
WHERE department_id NOT IN (SELECT department_id FROM departments WHERE department_name = 'Marketing');5. Subquery with EXISTS Operator
Description: This query checks whether an employee belongs to the IT department and returns matching employee rows.
SELECT employee_id, employee_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'IT');6. Subquery with ALL Operator
Description: This query returns employees whose salary is greater than every salary in department 50.
SELECT employee_id, employee_name
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 50);7. Subquery with ANY Operator
Description: This query returns employees whose salary is less than at least one salary in department 60.
SELECT employee_id, employee_name
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE department_id = 60);8. Subquery with Aggregate Function
Description: This query returns employees earning more than the average salary in department 70.
SELECT employee_id, employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 70);9. Correlated Subquery in SELECT
Description: This query shows each employee along with the maximum salary in that employee's department.
SELECT employee_id, employee_name, (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) AS max_salary
FROM employees e1;10. Correlated Subquery in WHERE
Description: This query returns the highest-paid employee in each department.
SELECT employee_id, employee_name
FROM employees e1
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = e1.department_id);Subqueries with Joins
11. Subquery in JOIN
Description: This query joins employees to a derived department list built from a subquery.
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN (SELECT department_id, department_name FROM departments) d
ON e.department_id = d.department_id;12. Subquery in LEFT JOIN
Description: This query returns all employees and their department names, including employees without a matching department.
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
LEFT JOIN (SELECT department_id, department_name FROM departments) d
ON e.department_id = d.department_id;13. Subquery in RIGHT JOIN
Description: This query returns all departments, even if no employee belongs to them.
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
RIGHT JOIN (SELECT department_id, department_name FROM departments) d
ON e.department_id = d.department_id;14. Subquery in FULL OUTER JOIN
Description: This query returns all employees and all departments, matched where possible.
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
FULL OUTER JOIN (SELECT department_id, department_name FROM departments) d
ON e.department_id = d.department_id;15. Subquery in CROSS JOIN
Description: This query produces a Cartesian product between employees and the subquery result set.
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
CROSS JOIN (SELECT department_id, department_name FROM departments) d;Subqueries with Aggregate Functions
16. Subquery with COUNT
Description: This query returns departments that have more than 10 employees.
SELECT department_id, department_name
FROM departments
WHERE (SELECT COUNT(*) FROM employees WHERE employees.department_id = departments.department_id) > 10;17. Subquery with SUM
Description: This query returns departments whose total salary cost is above 100000.
SELECT department_id, department_name
FROM departments
WHERE (SELECT SUM(salary) FROM employees WHERE employees.department_id = departments.department_id) > 100000;18. Subquery with AVG
Description: This query returns departments where the average salary is below 50000.
SELECT department_id, department_name
FROM departments
WHERE (SELECT AVG(salary) FROM employees WHERE employees.department_id = departments.department_id) < 50000;19. Subquery with MAX
Description: This query returns departments where the highest salary is greater than 80000.
SELECT department_id, department_name
FROM departments
WHERE (SELECT MAX(salary) FROM employees WHERE employees.department_id = departments.department_id) > 80000;20. Subquery with MIN
Description: This query returns departments where the lowest salary is less than 30000.
SELECT department_id, department_name
FROM departments
WHERE (SELECT MIN(salary) FROM employees WHERE employees.department_id = departments.department_id) < 30000;Subqueries with DML Operations
21. Subquery in INSERT Statement
Description: This insert copies Finance employees into a backup table.
INSERT INTO backup_employees (employee_id, employee_name, department_id, salary)
SELECT employee_id, employee_name, department_id, salary
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Finance');22. Subquery in UPDATE Statement
Description: This update gives a 10% raise to employees in the HR department.
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR');23. Subquery in DELETE Statement
Description: This delete removes employees from the Operations department.
DELETE FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Operations');24. Subquery in MERGE Statement
Description: This merge synchronizes employee salaries from a temporary table.
MERGE INTO employees e
USING (SELECT employee_id, salary FROM temp_employees) t
ON (e.employee_id = t.employee_id)
WHEN MATCHED THEN
UPDATE SET e.salary = t.salary
WHEN NOT MATCHED THEN
INSERT (employee_id, salary) VALUES (t.employee_id, t.salary);Nested Subqueries
25. Subquery within a Subquery
Description: This query finds employees whose department is determined by another subquery.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = (SELECT department_name FROM departments WHERE location_id = 1500));26. Subquery with Multiple Levels
Description: This query returns the maximum salary among employees in departments at location 1600.
SELECT employee_id, employee_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1600));27. Subquery with EXISTS and IN
Description: This query checks for employees in departments whose names appear in departments at location 1700.
SELECT employee_id, employee_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name IN (SELECT department_name FROM departments WHERE location_id = 1700));28. Subquery in SELECT List
Description: This query displays each employee with the matching department name in the SELECT list.
SELECT employee_id, employee_name, (SELECT department_name FROM departments WHERE department_id = e.department_id) AS dept_name
FROM employees e;29. Subquery with a Self-Join
Description: This query returns employees who have a manager recorded in the same employees table.
SELECT e1.employee_id, e1.employee_name
FROM employees e1
WHERE EXISTS (SELECT 1 FROM employees e2 WHERE e1.manager_id = e2.employee_id);30. Subquery with a UNION
Description: This query combines employees from one location with employees earning above the overall average salary.
SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1800)
UNION
SELECT employee_id, employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);Subqueries with ROWID
31. Subquery with ROWID
Description: This query returns employees from department 1900 using ROWID-based filtering.
SELECT employee_id, employee_name
FROM employees
WHERE ROWID IN (SELECT ROWID FROM employees WHERE department_id = 1900);32. Subquery with ROWID in DELETE
Description: This delete removes rows in department 2000 using ROWID.
DELETE FROM employees
WHERE ROWID IN (SELECT ROWID FROM employees WHERE department_id = 2000);33. Subquery with ROWID in UPDATE
Description: This update increases salary for employees in department 2100 using ROWID.
UPDATE employees
SET salary = salary * 1.2
WHERE ROWID IN (SELECT ROWID FROM employees WHERE department_id = 2100);Subqueries with GROUP BY
34. Subquery with GROUP BY
Description: This query finds departments that have more than five employees.
SELECT department_id, department_name
FROM departments
WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 5);35. Subquery with HAVING
Description: This query returns a department whose total salary is greater than 200000.
SELECT department_id, department_name
FROM departments
WHERE department_id = (SELECT department_id FROM employees GROUP BY department_id HAVING SUM(salary) > 200000);36. Subquery with GROUP BY and Aggregate Function
Description: This query returns departments where the average salary is above 60000.
SELECT department_id, department_name
FROM departments
WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) > 60000);37. Subquery with GROUP BY and JOIN
Description: This query joins employees and departments, then filters departments with more than 10 employees.
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10);38. Subquery with GROUP BY and COUNT
Description: This query returns departments that have more than eight employees.
SELECT department_id, department_name
FROM departments
WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(employee_id) > 8);Subqueries with LIMIT and OFFSET
39. Subquery with LIMIT and OFFSET
Description: This query shows employees ranked in the top 10 salary positions.
SELECT * FROM (
SELECT employee_id, employee_name, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
)
WHERE rank BETWEEN 1 AND 10;40. Subquery with RANK
Description: This query returns the highest-paid employees using a rank-based filter.
SELECT employee_id, employee_name
FROM (
SELECT employee_id, employee_name, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
)
WHERE rank = 1;41. Subquery with ROW_NUMBER
Description: This query returns the top-paid employee from each department.
SELECT employee_id, employee_name
FROM (
SELECT employee_id, employee_name, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn = 1;Subqueries with Conditional Logic
42. Subquery with CASE
Description: This query labels employees as above average or below average salary earners.
SELECT employee_id, employee_name,
CASE
WHEN salary > (SELECT AVG(salary) FROM employees) THEN 'Above Average'
ELSE 'Below Average'
END AS salary_comparison
FROM employees;43. Subquery with DECODE
Description: This query compares each employee salary to the overall average salary using DECODE.
SELECT employee_id, employee_name,
DECODE((SELECT AVG(salary) FROM employees), salary, 'Average', 'Not Average') AS salary_status
FROM employees;Subqueries in Data Analysis
44. Subquery for Data Comparison
Description: This query returns employees earning more than the maximum salary in department 2200.
SELECT employee_id, employee_name
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 2200);45. Subquery for Ranking
Description: This query returns the top salary employee in each department using ranking logic.
SELECT employee_id, employee_name
FROM (
SELECT employee_id, employee_name, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
)
WHERE rnk = 1;46. Subquery for Top-N Query
Description: This query returns the top 5 highest-paid employees.
SELECT * FROM (
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 5;47. Subquery for Bottom-N Query
Description: This query returns the 5 lowest-paid employees.
SELECT * FROM (
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary ASC
)
WHERE ROWNUM <= 5;48. Subquery with Multiple Columns
Description: This query finds employees whose department and salary match the maximum salary in each department.
SELECT employee_id, employee_name
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);49. Subquery with Multiple Joins
Description: This query returns employees from departments where at least one employee earns above average salary.
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IN (SELECT department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees));50. Subquery with Self-Join
Description: This query finds employees who have a manager in the same table and earn above the department average.
SELECT e1.employee_id, e1.employee_name
FROM employees e1
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department_id = e2.department_id);Advanced Subqueries
51. Subquery with EXISTS and Aggregate Function
Description: This query returns departments that have at least one employee earning above the overall average salary.
SELECT department_id, department_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary > (SELECT AVG(salary) FROM employees));52. Subquery with CASE and Aggregate Function
Description: This query categorizes each employee salary as high or low based on the company average.
SELECT employee_id, employee_name,
CASE
WHEN salary > (SELECT AVG(salary) FROM employees) THEN 'High Salary'
ELSE 'Low Salary'
END AS salary_category
FROM employees;53. Subquery with JOIN and Aggregation
Description: This query returns departments with employee counts higher than the average department size.
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
HAVING COUNT(e.employee_id) > (SELECT AVG(employee_count) FROM (SELECT COUNT(employee_id) AS employee_count FROM employees GROUP BY department_id));54. Subquery with Union All
Description: This query combines employees from one location with employees whose salary is above the company average.
SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2300)
UNION ALL
SELECT employee_id, employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);55. Subquery with LATERAL Join
Description: This query fetches each employee with related department details using a lateral join.
SELECT employee_id, employee_name, dept.*
FROM employees e
CROSS JOIN LATERAL (
SELECT department_id, department_name
FROM departments d
WHERE d.department_id = e.department_id
) dept;56. Subquery with PIVOT
Description: This query summarizes employee counts into low and high salary categories by department.
SELECT department_name, MAX(CASE WHEN salary_range = 'Low' THEN employee_count ELSE 0 END) AS low_salary_count,
MAX(CASE WHEN salary_range = 'High' THEN employee_count ELSE 0 END) AS high_salary_count
FROM (
SELECT d.department_name,
CASE WHEN e.salary < (SELECT AVG(salary) FROM employees) THEN 'Low' ELSE 'High' END AS salary_range,
COUNT(e.employee_id) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name, salary_range
)
GROUP BY department_name;57. Subquery with Recursive Query
Description: This query builds an employee hierarchy using recursive logic.
WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, manager_id, employee_name, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;58. Subquery with Hierarchical Query
Description: This query uses Oracle hierarchical syntax to display the employee tree.
SELECT employee_id, employee_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;59. Subquery with OUTER JOIN and Aggregate
Description: This query returns departments whose employee count is below the average department count.
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
HAVING COUNT(e.employee_id) < (SELECT AVG(employee_count) FROM (SELECT COUNT(employee_id) AS employee_count FROM employees GROUP BY department_id));60. Subquery with GROUP BY and JOIN
Description: This query returns departments whose average salary is greater than the company average.
SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees);Subqueries with Data Retrieval
61. Subquery with WHERE Clause
Description: This query returns employees who belong to the Sales department.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');62. Subquery in WHERE with Multiple Conditions
Description: This query returns employees in HR at location 2500.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR' AND location_id = 2500);63. Subquery with DISTINCT
Description: This query returns distinct employees working in departments at location 2600.
SELECT DISTINCT employee_id, employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2600);64. Subquery for Filtering
Description: This query returns employees earning more than the minimum salary in department 2700.
SELECT employee_id, employee_name
FROM employees
WHERE salary > (SELECT MIN(salary) FROM employees WHERE department_id = 2700);65. Subquery with OR Operator
Description: This query returns employees in IT or employees who earn above average salary.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT')
OR salary > (SELECT AVG(salary) FROM employees);66. Subquery for Specific Records
Description: This query returns the employee with the highest salary.
SELECT employee_id, employee_name
FROM employees
WHERE employee_id = (SELECT employee_id FROM employees WHERE salary = (SELECT MAX(salary) FROM employees));67. Subquery for Department-wise Analysis
Description: This query shows departments with employee counts above the overall average department count.
SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > (SELECT AVG(employee_count) FROM (SELECT COUNT(employee_id) AS employee_count FROM employees GROUP BY department_id));68. Subquery for Employee Status
Description: This query labels employees in department 2800 as high or average earners.
SELECT employee_id, employee_name,
CASE
WHEN salary > (SELECT AVG(salary) FROM employees WHERE department_id = 2800) THEN 'High Earner'
ELSE 'Average Earner'
END AS salary_status
FROM employees
WHERE department_id = 2800;69. Subquery with GROUP BY and Subquery
Description: This query counts employees by department where salaries are above the department average.
SELECT department_id, COUNT(employee_id)
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 2900)
GROUP BY department_id;70. Subquery for Filtering with Aggregation
Description: This query returns Finance employees who earn above Finance average salary.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Finance')
AND salary > (SELECT AVG(salary) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Finance'));Complex Subqueries
71. Subquery for Yearly Comparison
Description: This query returns employees earning more than last year's average salary.
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE EXTRACT(YEAR FROM hire_date) = EXTRACT(YEAR FROM SYSDATE) - 1);72. Subquery with Aggregation and Join
Description: This query returns employees who earn more than the average salary in their own department.
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = d.department_id);73. Subquery for Department-Specific Salaries
Description: This query returns departments with average salary higher than department 3000.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees WHERE department_id = 3000);74. Subquery for Last Month's Data
Description: This query returns employees hired in the last month who also earn above the recent average salary.
SELECT employee_id, employee_name
FROM employees
WHERE hire_date BETWEEN ADD_MONTHS(SYSDATE, -1) AND SYSDATE
AND salary > (SELECT AVG(salary) FROM employees WHERE hire_date BETWEEN ADD_MONTHS(SYSDATE, -1) AND SYSDATE);75. Subquery for Department and Salary
Description: This query returns Engineering employees who earn above the Engineering average salary.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Engineering')
AND salary > (SELECT AVG(salary) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Engineering'));76. Subquery for Department-wise Ranking
Description: This query returns the highest-paid employee in every department.
SELECT department_id, employee_id, employee_name, salary
FROM (
SELECT department_id, employee_id, employee_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
)
WHERE rank = 1;77. Subquery for High Earner Identification
Description: This query returns employees who earn more than the highest salary in Legal.
SELECT employee_id, employee_name
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Legal'));78. Subquery with UNION and Aggregation
Description: This query compares department salary totals with the average department total.
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
UNION ALL
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > (SELECT AVG(total_salary) FROM (SELECT SUM(salary) AS total_salary FROM employees GROUP BY department_id));79. Subquery for Salary Threshold
Description: This query returns employees whose salary is above the minimum salary in department 3100.
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT MIN(salary) FROM employees WHERE department_id = 3100);80. Subquery for Historical Data
Description: This query returns employees hired before the earliest hire date in department 3200.
SELECT employee_id, employee_name, salary
FROM employees
WHERE hire_date < (SELECT MIN(hire_date) FROM employees WHERE department_id = 3200);Advanced Analytical Subqueries
81. Subquery for Running Total
Description: This query calculates a running total of salary by hire date.
SELECT employee_id, employee_name, salary,
(SELECT SUM(salary) FROM employees e2 WHERE e2.hire_date <= e1.hire_date) AS running_total
FROM employees e1;82. Subquery with LEAD Function
Description: This query shows the next salary in descending salary order.
SELECT employee_id, employee_name, salary,
LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;83. Subquery with LAG Function
Description: This query shows the previous salary in descending salary order.
SELECT employee_id, employee_name, salary,
LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary
FROM employees;84. Subquery for Cumulative Salary
Description: This query calculates cumulative salary within each department ordered by hire date.
SELECT employee_id, employee_name, salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cumulative_salary
FROM employees;85. Subquery for Percentile Rank
Description: This query shows each employee's percentile position by salary.
SELECT employee_id, employee_name, salary,
PERCENT_RANK() OVER (ORDER BY salary DESC) AS percentile_rank
FROM employees;86. Subquery for Moving Average
Description: This query calculates a 30-day moving average of salary.
SELECT employee_id, employee_name, salary,
AVG(salary) OVER (ORDER BY hire_date RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;87. Subquery with NTILE Function
Description: This query divides employees into four salary quartiles.
SELECT employee_id, employee_name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;88. Subquery with FIRST_VALUE
Description: This query returns the highest salary in each department for every employee row.
SELECT employee_id, employee_name, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary_in_dept
FROM employees;89. Subquery with LAST_VALUE
Description: This query returns the lowest salary in each department using a window frame.
SELECT employee_id, employee_name, salary,
LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary_in_dept
FROM employees;90. Subquery with GROUPING SETS
Description: This query produces salary averages at multiple grouping levels in one result set.
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id, job_id), (department_id), (job_id), ());Subqueries with Temporal Data
91. Subquery for Latest Record
Description: This query returns employees with the most recent hire date in department 3300.
SELECT employee_id, employee_name, hire_date
FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees WHERE department_id = 3300);92. Subquery for Changes Over Time
Description: This query returns employees hired between the first hire in department 3400 and today.
SELECT employee_id, employee_name, salary, hire_date
FROM employees
WHERE hire_date BETWEEN (SELECT MIN(hire_date) FROM employees WHERE department_id = 3400) AND SYSDATE;93. Subquery for Historical Comparison
Description: This query returns employees earning more than the average salary of employees hired more than 12 months ago.
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE hire_date < ADD_MONTHS(SYSDATE, -12));94. Subquery for Monthly Comparison
Description: This query returns employees hired in the previous month range.
SELECT employee_id, employee_name, salary
FROM employees
WHERE hire_date BETWEEN TRUNC(SYSDATE, 'MM') - INTERVAL '1' MONTH AND TRUNC(SYSDATE, 'MM');95. Subquery for Yearly Comparison
Description: This query returns employees hired within the last year boundary.
SELECT employee_id, employee_name, salary
FROM employees
WHERE hire_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -12) AND TRUNC(SYSDATE, 'YYYY');Subqueries with Recursive Data
96. Subquery for Hierarchical Data
Description: This query builds a recursive employee hierarchy starting from top-level managers.
WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, manager_id, employee_name, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;97. Subquery with Recursive Aggregation
Description: This query calculates total salary by manager using recursive hierarchy data.
WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, manager_id, salary, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.salary, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT manager_id, SUM(salary) AS total_salary
FROM employee_hierarchy
GROUP BY manager_id;98. Subquery for Managerial Hierarchy
Description: This query displays the full hierarchy of employees and their reporting levels.
WITH RECURSIVE hierarchy AS (
SELECT employee_id, manager_id, employee_name, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.employee_name, h.level + 1
FROM employees e
JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM hierarchy;99. Subquery for Historical Data Aggregation
Description: This query calculates average salary per employee from historical salary records.
WITH historical_salaries AS (
SELECT employee_id, salary, hire_date
FROM employees
WHERE hire_date < TRUNC(SYSDATE, 'YYYY')
)
SELECT employee_id, AVG(salary) AS avg_salary
FROM historical_salaries
GROUP BY employee_id;100. Subquery for Aggregation with Multiple Levels
Description: This query summarizes salary by manager and keeps only managers whose total salary is above the average manager total.
WITH hierarchy AS (
SELECT employee_id, manager_id, salary, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.salary, h.level + 1
FROM employees e
JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT manager_id, SUM(salary) AS total_salary
FROM hierarchy
GROUP BY manager_id
HAVING SUM(salary) > (SELECT AVG(total_salary) FROM (SELECT manager_id, SUM(salary) AS total_salary FROM hierarchy GROUP BY manager_id));
No comments:
Post a Comment