In Oracle SQL, a Common Table Expression (CTE) is a powerful tool that allows you to define a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs can help simplify complex queries, improve readability, and enable recursive queries.

Basic Syntax of a CTE

WITH cte_name AS (
    -- CTE query
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

Key Features of CTEs

Temporary Result Set: CTEs exist only during the execution of the query.
Readability: They can make complex SQL queries easier to read and understand.
Recursion: CTEs can be recursive, allowing you to work with hierarchical data.

Example of Using CTE in Oracle

Step 1: Create a Sample Table

Let's create an Employees table.

CREATE TABLE Employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    salary NUMBER,
    department_id NUMBER
);

Step 2: Insert Sample Data

Insert some sample data into the Employees table.

INSERT INTO Employees (id, name, salary, department_id) 
VALUES (1, 'Alice', 70000, 1);
INSERT INTO Employees (id, name, salary, department_id) 
VALUES (2, 'Bob', 60000, 1);
INSERT INTO Employees (id, name, salary, department_id) 
VALUES (3, 'Charlie', 80000, 2);
INSERT INTO Employees (id, name, salary, department_id) 
VALUES (4, 'David', 50000, 2);
INSERT INTO Employees (id, name, salary, department_id) 
VALUES (5, 'Eve', 90000, 3);

Step 3: Use a CTE to Calculate Average Salary by Department

Here’s an example of how to use a CTE to calculate the average salary of employees by department.

WITH AverageSalary AS (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM 
        Employees
    GROUP BY 
        department_id
)
SELECT 
    department_id,
    avg_salary
FROM 
    AverageSalary
WHERE 
    avg_salary > 60000;

Explanation of the CTE Query

1. CTE Definition:

The CTE is defined using the WITH clause.
It calculates the average salary for each department by grouping the results based on department_id.

2. Main Query:

The main query selects department_id and avg_salary from the CTE
It filters the results to show only those departments where the average salary is greater than 60,000.

Expected Output

When you run the CTE query, the output should look something like this:

DEPARTMENT_ID   AVG_SALARY
1               65000
2               65000
3               90000

Common Table Expressions (CTEs) are a powerful feature in SQL that provide a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs make it easier to read and maintain complex queries.

Certainly! Let’s go into detail for each of the topics mentioned in the interview questions, providing a clearer understanding of Common Table Expressions (CTEs) in PL/SQL or SQL in general.

1. What is a Common Table Expression (CTE)?

A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH keyword and is useful for breaking complex queries into more understandable parts.

Unlike a regular subquery (which is embedded within the main query), a CTE can be referenced multiple times within the query. Once the query execution is complete, the CTE's result set disappears.

Benefits of CTE:

Readability: Simplifies complex queries.
Reusability: Can reference the result multiple times.
Recursive Capability: Recursive CTEs allow hierarchical or recursive queries, such as fetching an employee hierarchy.

2. How is a CTE different from a subquery or a derived table?

Subqueries: A query inside another query, typically used for filtering or aggregation. It is nested directly within a SELECT, INSERT, UPDATE, or DELETE statement.

Example of a subquery:

SELECT employee_id, name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR');

Derived Tables: Derived tables are also subqueries but are often used in the FROM clause to act as a table.

Example of a derived table:

SELECT temp.employee_id, temp.salary
FROM (SELECT employee_id, salary FROM employees WHERE salary > 5000) temp;

CTE: A CTE is defined using the WITH clause and can be referenced just like a temporary table. Unlike subqueries, CTEs can be recursive and can be used to simplify complex operations.

Example:

WITH high_earners AS (
    SELECT employee_id, salary
    FROM employees
    WHERE salary > 5000
)
SELECT * FROM high_earners;

Key Difference:

CTEs allow recursion and multiple references, while subqueries and derived tables do not.
CTEs are more readable for breaking down complex queries.

3. Types of CTEs: Non-Recursive vs Recursive CTEs

Non-Recursive CTEs:

These are standard CTEs that do not reference themselves. They are primarily used to simplify complex queries or to compute intermediate results that are used in the main query.

Example:


WITH department_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.name, e.salary
FROM employees e
JOIN department_avg_salary d
ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

Here, the CTE department_avg_salary calculates the average salary for each department. The main query then selects employees whose salaries are greater than their department's average.

Recursive CTEs:

Recursive CTEs reference themselves and are used for querying hierarchical data like organizational charts, part trees, etc. They work by defining an anchor member (the initial rowset) and a recursive member (the rows that reference the CTE itself).

Example:

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor Member: Select the top-level manager
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive Member: Find employees managed by the previous level
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Anchor Member: The initial result set (top-level managers with manager_id IS NULL).

Recursive Member: Fetches employees managed by the employees from the previous level.

4. CTE Syntax Explanation

The basic syntax for a CTE is as follows:


WITH cte_name AS (
    -- CTE Query
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE condition;

WITH: Keyword to start defining the CTE.
cte_name: Temporary result set (acts like a table)
CTE Query: The query inside the CTE that defines its result.
Main Query: The main query references the CTE as if it were a table.

5. Writing a Simple Query Using a CTE

Here’s an example using a CTE:
Problem:

Find employees earning more than the average salary in their department.

WITH department_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.name, e.salary
FROM employees e
JOIN department_avg_salary d
ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

Explanation:

The CTE department_avg_salary calculates the average salary per department.
The main query selects employees whose salary is greater than the average salary for their department.

6. Recursive CTE Example

Problem:

You want to list all employees reporting to a specific manager in a hierarchical organization.

WITH RECURSIVE employee_hierarchy AS (
    -- Base case: Select the top-level manager
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id = :manager_id

    UNION ALL

    -- Recursive case: Select employees reporting to previous level employees
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

The anchor member starts the recursion by finding the manager.

The recursive member fetches employees reporting to the manager and continues the process recursively.

CTE Performance Considerations

CTE improves readability, not necessarily performance.

CTEs are not indexed, so complex CTE queries may not perform better than using regular subqueries or derived tables.

Recursive CTEs may lead to performance degradation if the recursion is too deep or involves too many rows.

9. Use Cases for Recursive CTEs

Hierarchical Data Queries: For example, an organizational chart where each employee has a manager, forming a hierarchy.

Bill of Materials (BOM): To list parts and sub-parts in a manufacturing assembly.

Pathfinding: For finding relationships between entities in a network (like friend connections on social media).

11. Limitations of CTEs

Not Cached: CTE results are not cached in memory, so if you reference them multiple times, the database may recompute the result each time.

No Indexing: CTEs cannot be indexed, which might make queries slower compared to using indexed tables or materialized views.

Performance: Recursive CTEs can degrade performance, especially with deep recursion or large data sets.

This detailed explanation should give you a solid understanding of CTEs and how they are used in SQL and PL/SQL!

Common Table Expression (CTE) in PLSQL | Part 1