Common Table Expression (CTE)

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

1. Temporary Result Set: CTEs exist only during the execution of the query.
2. Readability: They can make complex SQL queries easier to read and understand.
3. 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.