This content originally appeared on DEV Community and was authored by Pranav Bakare
Let's go step by step through creating a table, inserting data, and performing CTE operations (both non-recursive and recursive) in Oracle PL/SQL.
Step 1: Create Tables and Insert Data
- Creating employees Table
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(50),
salary NUMBER,
department_id NUMBER
);
- Inserting Data into employees Table
INSERT INTO employees (employee_id, name, salary, department_id) VALUES (1, 'Alice', 7000, 10);
INSERT INTO employees (employee_id, name, salary, department_id) VALUES (2, 'Bob', 4000, 10);
INSERT INTO employees (employee_id, name, salary, department_id) VALUES (3, 'Charlie', 6000, 20);
INSERT INTO employees (employee_id, name, salary, department_id) VALUES (4, 'David', 5000, 20);
- Creating departments Table
CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50),
parent_department_id NUMBER
);
- Inserting Data into departments Table
INSERT INTO departments (department_id, department_name, parent_department_id) VALUES (1, 'Sales', NULL);
INSERT INTO departments (department_id, department_name, parent_department_id) VALUES (2, 'North America', 1);
INSERT INTO departments (department_id, department_name, parent_department_id) VALUES (3, 'Europe', 1);
INSERT INTO departments (department_id, department_name, parent_department_id) VALUES (4, 'Germany', 3);
Step 2: Non-Recursive CTE Operation (Average Salary by Department)
We will now perform a CTE query to find employees who earn more than the average salary in their department.
Non-Recursive CTE Query:
WITH avg_salary_dept AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.employee_id, e.name, e.salary, a.avg_salary
FROM employees e
JOIN avg_salary_dept a
ON e.department_id = a.department_id
WHERE e.salary > a.avg_salary;
Explanation:
The CTE avg_salary_dept calculates the average salary per department.
The main query retrieves employees whose salary is higher than the average for their department.
Expected Output:
Step 3: Recursive CTE Operation (Department Hierarchy)
Now, let's perform a recursive query to find the hierarchy of departments starting from department 1.
Recursive CTE Query:
WITH department_hierarchy (department_id, department_name, parent_department_id) AS (
SELECT department_id, department_name, parent_department_id
FROM departments
WHERE department_id = 1 -- Anchor member: start from the root department
UNION ALL
-- Recursive member: join to find child departments recursively
SELECT d.department_id, d.department_name, d.parent_department_id
FROM departments d
JOIN department_hierarchy dh
ON d.parent_department_id = dh.department_id
)
SELECT * FROM department_hierarchy;
Explanation:
The anchor member retrieves the root department (Sales).
The recursive member recursively retrieves all child departments based on parent_department_id.
Expected Output:
Step 4: Test the Queries
After running these queries, you will be able to:
Find employees who earn more than the average salary in their department.
Retrieve hierarchical relationships in the departments.
These operations demonstrate both non-recursive and recursive CTE capabilities within PL/SQL.
Important Notes:
Non-Recursive CTEs simplify complex queries by breaking them into logical parts.
Recursive CTEs are useful for traversing hierarchical or tree-structured data.
You can execute these CTE queries in SQL*Plus, SQL Developer, or any Oracle PL/SQL IDE.
This content originally appeared on DEV Community and was authored by Pranav Bakare
Pranav Bakare | Sciencx (2024-09-28T17:36:27+00:00) CTE. Retrieved from https://www.scien.cx/2024/09/28/cte/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.