Calculate average age of employees in PL/SQL

PL/SQL Package: Exercise-13 with Solution

Write a code in PL/SQL that create a package that contains a function to retrieve the average age of employees in a given department.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

CREATE OR REPLACE PACKAGE emp_pkg IS
  FUNCTION get_avg_age(department_id_v IN NUMBER) RETURN NUMBER;
END emp_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  FUNCTION get_avg_age(department_id_v IN NUMBER) RETURN NUMBER IS
total_age NUMBER := 0;
employee_count NUMBER := 0;
avg_age NUMBER := 0;
  BEGIN
    SELECT SUM(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM hire_date))
    INTO total_age
    FROM employees
    WHERE department_id = department_id_v;
    SELECT COUNT(*) INTO employee_count
    FROM employees
    WHERE department_id = department_id_v;
    IF employee_count> 0 THEN
avg_age := total_age / employee_count;
    END IF;
    RETURN avg_age;
  END get_avg_age;
END emp_pkg;
/

Sample Output:

Package created. Package Body created

Flowchart:

To execute the package:

DECLARE
avg_age NUMBER;
BEGIN
avg_age := emp_pkg.get_avg_age(100); -- Pass the department ID as argument
  DBMS_OUTPUT.PUT_LINE('Average Age: ' || avg_age);
END;
/

Sample Output:

Statement processed.
Average Age: 36

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that will calculate the average age based on the difference between the current year and the hire dates of all employees within the specified department and returns the result.

A function get_avg_agedealared in this package is declared and the function takes a single input parameter department_id_v, representing the department for which the average age of employees needs to be calculated and returns a NUMBER.

Inside the function, three local variables total_age, employee_count, and avg_ageare declared.

The function uses two SQL queries to obtain the required information, the first query calculates the total_age by subtracting the year of the hire_date from the current year for all employees in a specific department and the one determines the employee_count by counting the number of employees in the specified department.

If there are employees in the department, the function calculates the avg_age by dividing the total_age by the employee_count.

Finally, the calculated avg_age is returned as the result of the function.

Previous: Salary cost package in PL/SQL.
Next: Package to retrieve employees with the same manager in PL/SQL.