Function Part-2 Queries.sql

ALTER TABLE Employees
DROP CONSTRAINT employees_department_id_fkey;


-- Departments table
CREATE TABLE Departments (
    Dept_id INT PRIMARY KEY,
    Dept_Name VARCHAR(50),
    Budget DECIMAL(10,2)
);

-- Employees table
CREATE TABLE Employees (
    Emp_id INT PRIMARY KEY,
    Emp_Name VARCHAR(50),
    Department_id INT,
    Salary DECIMAL(10,2),
    Hire_Date DATE,
    Manager_id INT NULL,
    FOREIGN KEY (Department_id) REFERENCES Departments(Dept_id)
);


INSERT INTO Departments (Dept_id, Dept_Name, Budget) VALUES
(1, 'HR', 500000),
(2, 'Finance', 800000),
(3, 'IT', 1200000),
(4, 'Sales', 1000000),
(5, 'Marketing', 600000);


INSERT INTO Employees (Emp_id, Emp_Name, Department_id, Salary, Hire_Date, Manager_id) VALUES
(1, 'Amit Sharma', 1, 45000, '2020-01-15', NULL),
(2, 'Priya Singh', 2, 55000, '2019-03-10', 1),
(3, 'Ravi Kumar', 3, 70000, '2021-07-20', 2),
(4, 'Neha Verma', 4, 65000, '2018-11-05', 2),
(5, 'Arjun Mehta', 5, 48000, '2022-02-12', 3),
(100, 'Zoya Khan', 3, 72000, '2023-09-01', 4);

5️⃣ Advanced: Department-wise hierarchy with recursion
Question: Write a recursive function that, for each department, shows employees along 
with their reporting chain inside that department.
👉 Example: In IT, Ravi Kumar reports to Priya Singh, who reports to Amit Sharma.
5️⃣ Advanced: Department-wise hierarchy with recursion
Question: Write a recursive function that, for each department, shows employees along 
with their reporting chain inside that department.
👉 Example: In IT, Ravi Kumar reports to Priya Singh, who reports to Amit Sharma.

select * from Employees

create function emp_earning( min_salary  decimal(10,2))
returns table (Emp_id int, Emp_Name varchar(50), salary decimal(10,2)) 
as $$
Begin
return query
select Emp_id , Emp_Name ,Salary 
from Employees 
where Salary > min_salary;
end;
$$ language plpgsql;
select * from emp_earning(50000.00)

drop function emp_earning


create function emp_hired_after(Hiring_date date)
returns table (Emp_id int, Emp_Name varchar(50), salary decimal(10,2), hire_date date) 
as $$
Begin
return query
select e.Emp_id , e.Emp_Name , e.Salary, e.hire_date 
from Employees e
where e.hire_date > Hiring_date;
end;
$$ language plpgsql;
select * from emp_hired_after('2021-07-20')



create function Name_start_with(start_letter varchar(50))
returns table (Emp_id int, Emp_Name varchar(50), salary decimal(10,2)) 
as $$
Begin
return query
select e.Emp_id , e.Emp_Name, e.salary
from Employees e
where e.Emp_Name LIKE start_letter || '%';
end;
$$ language plpgsql;
select * from Name_start_with('A')



create function salary_between(salary_bet_two_values1 decimal(10,2), salary_bet_two_values2 decimal(10,2))
returns table(Emp_id int, Emp_name varchar(50), salary decimal(10,2))
as $$
begin
return query
select e.Emp_id, e.Emp_name, e.salary
from Employees e
where e.salary between salary_bet_two_values1 and salary_bet_two_values2;
end;
$$ language plpgsql;
select * from salary_between(48000, 75000)
drop function salary_between


create or replace function emp_dept(specific_Dept_Name varchar(50))
returns table(Emp_id int, Emp_name varchar(50), Dept_id int, Dept_Name varchar(50))
as $$
begin
return query
select e.Emp_id, e.Emp_name,d.Dept_id, d.Dept_Name
from Employees e
join Departments d on
e.Department_id = d.Dept_id
where d.Dept_Name = specific_Dept_Name;
end;
$$ language plpgsql;
select * from emp_dept('IT')
drop function emp_dept



create or replace function ret_emp_dept()
returns table(Emp_id int, Emp_name varchar(50), salary decimal(10,2), Dept_id int, Dept_Name varchar(50))
as $$
begin
return query
select e.Emp_id, e.Emp_name,e.salary , d.Dept_id, d.Dept_Name
from Employees e
join Departments d on
e.Department_id = d.Dept_id;
end;
$$ language plpgsql;
select * from ret_emp_dept()
drop function emp_dept



create function manager_emp_are()
returns table (Emp_Name varchar(50), manager_name varchar(50))
as $$ 
begin 
return query
select e.Emp_Name , m.Emp_Name as manager_name
from Employees e
join Employees m
on e.emp_id = m.Manager_id;
end;
$$ language plpgsql;
select * from manager_emp_are()
drop function manager_emp_are()


create function emp_with_dept_budget()
returns table(Emp_Name varchar(50), Budget decimal(10,2))
as $$
begin return query
select  e.Emp_Name, d.Budget
from Employees e
join Departments d
on e.Department_id = d.dept_id;
end;
$$ language plpgsql;
select * from emp_with_dept_budget()



-- 1. Average salary per department
SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;

-- 2. Maximum salary per department
SELECT d.dept_name, MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;

-- 3. Count employees per department
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;

-- 4. Total salary expenditure per department
SELECT d.dept_name, SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;

-- 5. Minimum salary across all employees
SELECT MIN(salary) AS min_salary
FROM employees;



create function factorial(n int)
returns int
as $$
begin
if n = 0 then
return 1;
else 
return n * factorial(n - 1)  ;  
end if;
end;
$$ language plpgsql;
select factorial(5)





create function fibonacci(n int)
returns int
as $$
begin
if n = 0 then
return 0;
elsif n = 1 then
return 1;
else 
return fibonacci(n - 1) + fibonacci(n - 2)  ;  
end if;
end;
$$ language plpgsql;
select fibonacci(9)


create or replace function get_emps_above_manager(emp INT)
returns table(level int, Emp_id int, Emp_Name varchar(50), Manager_id int)
as $$
begin
return query
with recursive Manager_hierarchy as (
select 0 as level, e.Emp_id, e.Emp_Name, e.Manager_id
from Employees e
where e.Emp_id = emp
union all
select mh.level+1, e.Emp_id, e.Emp_Name, e.Manager_id
from Employees e
join Manager_hierarchy mh
on e.Emp_id = mh.Manager_id
)
select * from Manager_hierarchy
order by level;
end;
$$ language plpgsql;
select * from get_emps_above_manager(5)
drop function get_emps_above_manager




CREATE OR REPLACE FUNCTION get_emp_under_manager(manager INT)
RETURNS TABLE (
    level INT,
    Emp_id INT,
    Emp_Name VARCHAR(50),
    Manager_id INT
) AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE subordinate_chain AS (
        -- Anchor: start with the given manager
        SELECT 0 AS level, e.Emp_id, e.Emp_Name, e.Manager_id
        FROM Employees e 
        WHERE e.Emp_id = manager

        UNION ALL

        -- Recursive step: find employees reporting to this manager
        SELECT mc.level+1, e.Emp_id, e.Emp_Name, e.Manager_id
        FROM Employees e
        INNER JOIN subordinate_chain mc
            ON e.Manager_id = mc.Emp_id   -- ✅ correct downward join
    )
    SELECT * FROM subordinate_chain ORDER BY level;
END;
$$ LANGUAGE plpgsql;

select * from get_emp_under_manager(3)
drop function get_emp_nder_manager



CREATE OR REPLACE FUNCTION get_department_hierarchy1(p_dept_name VARCHAR)
RETURNS TABLE(
    level INT,
    emp_id INT,
    emp_name VARCHAR(50),
    manager_id INT
)
AS $$
BEGIN

RETURN QUERY

WITH RECURSIVE dept_chain AS (

    -- Step 1: Start with employees of the given department
    SELECT
        0 AS level,
        e.emp_id,
        e.emp_name,
        e.manager_id
    FROM Employees e
    JOIN Departments d
        ON e.department_id = d.dept_id
    WHERE d.dept_name = p_dept_name

    UNION ALL

    -- Step 2: Keep moving to each employee's manager
    SELECT
        dc.level + 1,
        e.emp_id,
        e.emp_name,
        e.manager_id
    FROM Employees e
    JOIN dept_chain dc
        ON e.emp_id = dc.manager_id

)

SELECT *
FROM dept_chain
ORDER BY level;

END;
$$
LANGUAGE plpgsql;
select * from get_department_hierarchy1('IT')
drop function get_department_hierarchy1



CREATE OR REPLACE FUNCTION get_department_hierarchy(p_dept_name VARCHAR)
RETURNS TABLE(
    level INT,
    emp_id INT,
    emp_name VARCHAR(50),
    manager_id INT,
    department VARCHAR(50)
)
AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE dept_chain AS (
        -- Anchor: employees in the department whose manager is outside or NULL
        SELECT 
            0 AS level,
            e.emp_id,
            e.emp_name,
            e.manager_id,
            d.dept_name
        FROM Employees e
        JOIN Departments d ON e.department_id = d.dept_id
        WHERE d.dept_name = p_dept_name
          AND (e.manager_id IS NULL 
               OR NOT EXISTS (
                   SELECT 1 
                   FROM Employees m
                   WHERE m.emp_id = e.manager_id 
                     AND m.department_id = d.dept_id
               ))

        UNION ALL

        -- Recursive step: find subordinates in same department
        SELECT 
            dc.level + 1,
            e.emp_id,
            e.emp_name,
            e.manager_id,
            d.dept_name
        FROM Employees e
        JOIN Departments d ON e.department_id = d.dept_id
        INNER JOIN dept_chain dc ON e.manager_id = dc.emp_id
        WHERE d.dept_name = p_dept_name
    )
    SELECT * FROM dept_chain ORDER BY level;
END;
$$ LANGUAGE plpgsql;


🟢 Table-Valued Functions (TVF)
Create a TVF that returns employees earning more than a given salary.

Write a TVF that returns employees hired after a given date.

Create a TVF that returns employees whose names start with a given letter.

Write a TVF that returns employees with salary between two values.

Create a TVF that returns employees belonging to a specific department.

🟡 Multi-Table-Valued Functions
Create a multi-table function that returns employee name with department name.

Write a multi-table function that returns employee name with manager name.

Write a multi-table function that returns employee salary with department budget.


🔵 Aggregation Functions
Write a query to find the average salary per department.

Write a query to find the maximum salary per department.

Write a query to count employees per department.

Write a query to find the total salary expenditure per department.

Write a query to find the minimum salary across all employees.

🔴 Recursive Functions
:

🟢 Beginner → Advanced Recursive Function Questions
1️⃣ Beginner: Factorial of a number
Question: Write a recursive function in PL/pgSQL to calculate the factorial of a given number n.
👉 This builds your recursion basics before applying it to tables.

2️⃣ Beginner: Fibonacci sequence
Question: Write a recursive function to return the nth Fibonacci number.
👉 Another math recursion example to strengthen your foundation.

3️⃣ Intermediate: Find all managers above an employee
Question: Using the Employees table, write a recursive function that, given an employee ID,
returns the chain of managers above them until the top boss.
👉 Example: For Arjun Mehta, output → Ravi Kumar → Priya Singh → Amit Sharma.

4️⃣ Intermediate: Find all subordinates under a manager
Question: Using the Employees table, write a recursive function that, given a manager ID,
returns all employees who report directly or indirectly to them.
👉 Example: For Priya Singh, output → Ravi Kumar, Neha Verma, Arjun Mehta, Zoya Khan.

5️⃣ Advanced: Department-wise hierarchy with recursion
Question: Write a recursive function that, for each department, shows employees along 
with their reporting chain inside that department.
👉 Example: In IT, Ravi Kumar reports to Priya Singh, who reports to Amit Sharma.