Mastering SQL for Oracle Fusion HCM – Chapter 11. SQL Subqueries and Nested Queries in Fusion HCM

 

✅ Chapter 11. SQL Subqueries and Nested Queries in Fusion HCM

 SQL Subqueries and Nested Queries in Oracle Fusion HCM – Advanced Querying Techniques

 Master SQL subqueries and nested queries for Oracle Fusion HCM. Learn how to use subqueries in SELECT, WHERE, and HAVING clauses with practical examples.

🔹 Introduction

Subqueries and nested queries are powerful SQL techniques that allow you to embed one query within another. These techniques are invaluable when you need to:

  • Retrieve complex data from multiple tables.

  • Perform calculations or comparisons that require results from other queries.

  • Filter or aggregate data dynamically.

In Oracle Fusion HCM, subqueries are often used in scenarios like filtering employee data based on criteria from multiple sources or comparing salaries across different departments.

This chapter covers the theory, syntax, and real-world examples of subqueries and nested queries, with a focus on Fusion HCM applications.

🔹 Theoretical Concepts

📌 What is a Subquery?

A subquery is a query embedded within another query. Subqueries can appear in the SELECT, WHERE, and HAVING clauses. They are executed once for each row in the outer query.

📌 Types of Subqueries

  1. Single-row Subquery: Returns a single value.

  2. Multi-row Subquery: Returns multiple rows (used with IN, ANY, ALL).

  3. Correlated Subquery: Refers to columns in the outer query, making it dependent on the outer query's values.

📌 Syntax of a Subquery


SELECT column_name
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

📌 Subquery in SELECT Clause

  • Returns a value that will be used in the main query.

📌 Subquery in WHERE Clause

  • Filters rows based on the results of a subquery.

📌 Correlated Subqueries

  • A correlated subquery references columns from the outer query. It is executed for each row selected by the outer query.

🔹 Using Subqueries in SQL for Fusion HCM

✅ Example 1: Subquery in WHERE Clause

Scenario: Find employees whose salary is higher than the average salary for their job title.


SELECT person_number,
       job_title,
       salary
FROM   per_all_assignments_m paam
WHERE  salary > (SELECT AVG(salary)
                 FROM   per_all_assignments_m
                 WHERE  job_title = paam.job_title) 

✅ Example 2: Subquery in SELECT Clause

Scenario: Retrieve employee names along with the department they belong to and the maximum salary in their department.


SELECT papf.full_name,
       hd.department_name,
       (SELECT MAX(salary)
        FROM   per_all_assignments_m paam
        WHERE  paam.department_id = hd.department_id) AS max_salary
FROM   per_all_people_f papf,
       per_all_assignments_m paam,
       hr_all_departments hd
WHERE  1 = 1
       AND papf.person_id = paam.person_id
       AND paam.department_id = hd.department_id 

✅ Example 3: Subquery in HAVING Clause

Scenario: Show departments where the total salary is greater than the average salary across all departments.


SELECT hd.department_name,
       SUM(paam.salary) AS total_salary
FROM   per_all_assignments_m paam,
       hr_all_departments hd
WHERE  paam.department_id = hd.department_id
GROUP  BY hd.department_name
HAVING SUM(paam.salary) > (SELECT AVG(salary)
                           FROM   per_all_assignments_m)

🔹 Correlated Subqueries in Fusion HCM

A correlated subquery is one where the inner query refers to columns from the outer query. This type of query is re-executed for each row processed by the outer query.

✅ Example 4: Correlated Subquery to Compare Salaries

Scenario: List employees with a salary higher than the average salary in their department.


SELECT person_number,
       salary,
       department_id
FROM   per_all_assignments_m paam
WHERE  salary > (SELECT AVG(salary)
                 FROM   per_all_assignments_m
                 WHERE  department_id = paam.department_id) 

✅ Example 5: Correlated Subquery for Employee Promotion

Scenario: Find employees who have a salary greater than the average salary for employees in their same job title.


SELECT person_number,
       job_title,
       salary
FROM   per_all_assignments_m paam
WHERE  salary > (SELECT AVG(salary)
                 FROM   per_all_assignments_m
                 WHERE  job_title = paam.job_title) 

🔹 Real-Time Scenario (Fusion HCM Reporting)

🧾 Scenario:

“Create a report showing employees who earn more than the highest salary in any department that has fewer than 5 employees.”

✅ SQL Query:


SELECT person_number,
       salary,
       department_id
FROM   per_all_assignments_m paam
WHERE  salary > (SELECT MAX(salary)
                 FROM   per_all_assignments_m
                 WHERE  department_id = paam.department_id)
       AND department_id IN (SELECT department_id
                             FROM   per_all_assignments_m
                             GROUP  BY department_id
                             HAVING COUNT(*) < 5) 

🔹 Best Practices for Subqueries

  • Use subqueries in WHERE, SELECT, and HAVING clauses when you need to filter or calculate values based on another query.

  • Avoid using subqueries in the SELECT clause when you can achieve the same result using JOINs.

  • Correlated subqueries are powerful but can impact performance since they are executed once for each row in the outer query.

  • Always test performance when using subqueries with large datasets in Fusion HCM, as they can be resource-intensive.

🔹 Summary

✅ Subqueries are queries within a query and are useful for comparing and filtering results dynamically in Fusion HCM.
✅ Common types of subqueries are single-row, multi-row, and correlated subqueries.
✅ Correlated subqueries depend on the outer query, making them more flexible but slower in some cases.
✅ Use subqueries in WHERE, SELECT, and HAVING clauses for better data analysis in Fusion HCM.

🔹 Next Steps

Tags: #SQLSubqueries, #FusionHCM, #CorrelatedSubqueries, #AdvancedSQL, #NestedQueries, #DataAnalysis

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)