How to Create an Employee Management System with Oracle APEX Using the EMP & DEPT Tables for Reporting ~ APEX Note

In this blog post, I guide you through the process of data integration in Oracle APEX, specifically using the classic DEPT and EMP tables to create dynamic reports and dashboards. This post is especially useful for developers who want to understand how to effectively integrate and utilize existing data structures in APEX.

Overview

Integrating and visualizing data represents a central challenge in the development of enterprise applications. Oracle APEX offers excellent tools not only for effectively managing data but also for generating appealing and informative reports.

Step-by-Step Guide

Preparing the Data Structure

First, a foundation is created by establishing two main tables: DEPT (Departments) and EMP (Employees). These tables are linked together and form the backbone for the subsequent reports.

SQL

xxxxxxxxxx
 
create table dept(   
  deptno     number(2,0),   
  dname      varchar2(14),   
  loc        varchar2(13),   
  constraint pk_dept primary key (deptno)   
);
​
create table emp(   
  empno    number(4,0),   
  ename    varchar2(10),   
  job      varchar2(9),   
  mgr      number(4,0),   
  hiredate date,   
  sal      number(7,2),   
  comm     number(7,2),   
  deptno   number(2,0),   
  constraint pk_emp primary key (empno),   
  constraint fk_deptno foreign key (deptno) references dept (deptno)   
);

Populating Data

Once the table structure is defined, sample data is inserted into both tables. This data will later serve as the basis for evaluations and visualizations in the reports.

SQL

xxxxxxxxxx
 
insert into DEPT (DEPTNO, DNAME, LOC) 
values(10, 'ACCOUNTING', 'NEW YORK');
​
insert into dept   
values(20, 'RESEARCH', 'DALLAS');
​
insert into dept   
values(30, 'SALES', 'CHICAGO');
​
insert into dept  
values(40, 'OPERATIONS', 'BOSTON');
​
insert into emp   
values(   
 7839, 'KING', 'PRESIDENT', null,   
 to_date('17-11-1981','dd-mm-yyyy'),   
 5000, null, 10   
);
​
insert into emp  
values(  
 7698, 'BLAKE', 'MANAGER', 7839,  
 to_date('1-5-1981','dd-mm-yyyy'),  
 2850, null, 30  
);
​
insert into emp  
values(  
 7782, 'CLARK', 'MANAGER', 7839,  
 to_date('9-6-1981','dd-mm-yyyy'),  
 2450, null, 10  
);
​
insert into emp  
values(  
 7566, 'JONES', 'MANAGER', 7839,  
 to_date('2-4-1981','dd-mm-yyyy'),  
 2975, null, 20  
);
​
insert into emp  
values(  
 7788, 'SCOTT', 'ANALYST', 7566,  
 to_date('13-JUL-87','dd-mm-rr') - 85,  
 3000, null, 20  
);
​
insert into emp  
values(  
 7902, 'FORD', 'ANALYST', 7566,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 3000, null, 20  
);
​
insert into emp  
values(  
 7369, 'SMITH', 'CLERK', 7902,  
 to_date('17-12-1980','dd-mm-yyyy'),  
 800, null, 20  
);
​
insert into emp  
values(  
 7499, 'ALLEN', 'SALESMAN', 7698,  
 to_date('20-2-1981','dd-mm-yyyy'),  
 1600, 300, 30  
);
​
insert into emp  
values(  
 7521, 'WARD', 'SALESMAN', 7698,  
 to_date('22-2-1981','dd-mm-yyyy'),  
 1250, 500, 30  
);
​
insert into emp  
values(  
 7654, 'MARTIN', 'SALESMAN', 7698,  
 to_date('28-9-1981','dd-mm-yyyy'),  
 1250, 1400, 30  
);
​
insert into emp  
values(  
 7844, 'TURNER', 'SALESMAN', 7698,  
 to_date('8-9-1981','dd-mm-yyyy'),  
 1500, 0, 30  
);
​
​
​
insert into emp  
values(  
 7876, 'ADAMS', 'CLERK', 7788,  
 to_date('13-JUL-87', 'dd-mm-rr') - 51,  
 1100, null, 20  
);
​
insert into emp  
values(  
 7900, 'JAMES', 'CLERK', 7698,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 950, null, 30  
);
​
insert into emp  
values(  
 7934, 'MILLER', 'CLERK', 7782,  
 to_date('23-1-1982','dd-mm-yyyy'),  
 1300, null, 10  
);

Creating Reports

In Oracle APEX, two regions are set up as reports:

**Employee Report**: This report displays details about the employees, such as name, position, hire date, and department affiliation.
**Department Report**: This report shows the different departments and the number of employees assigned to each.

Highlight

xxxxxxxxxx
 
select ename
     , dname
     , job
     , empno
     , hiredate
     , loc   
  from emp, dept   
 where emp.deptno = dept.deptno   
 order by ename
;
​
select dname
     , count(*) as count_of_employees 
  from dept, emp 
 where dept.deptno = emp.deptno 
 group by DNAME 
 order by 2 desc
;

Visualization and Interaction

These reports are not only visually appealing but also offer interactive features such as filters and sorting options, allowing end-users to customize the displayed information according to their needs.

Benefits and Applications

The method described in this post is ideal for organizations looking to make their internal processes more transparent and improve their data analysis capabilities. It is particularly valuable for decision-makers and IT departments that need to access detailed information quickly and efficiently.

Conclusion

Using Oracle APEX for data integration and report generation maximizes the value of your data through enhanced insights and decision-making. The easy integration and use of classic SQL data structures make APEX a powerful tool in modern data processing and analysis.

{fullWidth}

APEX Note