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.
Top comments (0)