Building an Employee Management App with Oracle APEX Using EMP & DEPT Tables

In this blog post, we’ll walk through the process of creating an employee management application in Oracle APEX. Using the classic DEPT and EMP tables as examples, we’ll demonstrate how to build dynamic reports and dashboards. This guide is particularly useful for developers seeking to integrate existing data structures effectively into Oracle APEX.

Overview

Integrating and visualizing data is a cornerstone of enterprise application development. Oracle APEX offers powerful tools not only to manage data but also to generate compelling and informative reports. By leveraging APEX’s capabilities, you can create interactive applications that enhance decision-making and streamline processes.

Step-by-Step Guide

1. Preparing the Data Structure

The foundation of this application lies in two primary tables: DEPT (departments) and EMP (employees). These tables are interconnected and serve as the backbone for subsequent reports.

SQL Code to Create the Tables

 

SQL

xxxxxxxxxx

18

 

1

CREATE TABLE dept(   

2

  deptno     NUMBER(2,0),   

3

  dname      VARCHAR2(14),   

4

  loc        VARCHAR2(13),   

5

  CONSTRAINT pk_dept PRIMARY KEY (deptno)   

6

);

7

CREATE TABLE emp(   

8

  empno      NUMBER(4,0),   

9

  ename      VARCHAR2(10),   

10

  job        VARCHAR2(9),   

11

  mgr        NUMBER(4,0),   

12

  hiredate   DATE,   

13

  sal        NUMBER(7,2),   

14

  comm       NUMBER(7,2),   

15

  deptno     NUMBER(2,0),   

16

  CONSTRAINT pk_emp PRIMARY KEY (empno),   

17

  CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)   

18

);

2. Populating the Tables

Once the table structures are defined, insert sample data into both tables. This data will serve as the basis for analysis and visualization in the reports.

SQL Code to Insert Sample Data

 

SQL

xxxxxxxxxx

 

1

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');

2

INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');

3

INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');

4

INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

5

INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, NULL, 10);

6

INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1-5-1981', 'DD-MM-YYYY'), 2850, NULL, 30);

7

INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('9-6-1981', 'DD-MM-YYYY'), 2450, NULL, 10);

8

-- (Additional employee records follow, as provided in the original text)

3. Creating Reports

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

• Employee Report: Displays employee details, such as name, job position, hire date, and department.
• Department Report: Lists all departments along with the number of employees in each.

SQL Queries for Reports

• Employee Report

 

SQL

xxxxxxxxxx

13

 

1

SELECT 

2

  ename,

3

  dname,

4

  job,

5

  empno,

6

  hiredate,

7

  loc   

8

FROM 

9

  emp, dept   

10

WHERE 

11

  emp.deptno = dept.deptno   

12

ORDER BY 

13

  ename;

• Department Report

 

SQL

xxxxxxxxxx

11

 

1

SELECT 

2

  dname,

3

  COUNT(*) AS count_of_employees 

4

FROM 

5

  dept, emp 

6

WHERE 

7

  dept.deptno = emp.deptno 

8

GROUP BY 

9

  dname 

10

ORDER BY 

11

  2 DESC;

4. Visualization and Interaction

The reports are designed to be visually engaging and interactive. Users can apply filters, sort data, and customize the presentation to suit their needs. These features make it easier to analyze and act on the data.

Benefits and Use Cases

This method is ideal for organizations seeking to improve transparency in internal processes and enhance their data analysis capabilities. Decision-makers and IT teams can quickly and efficiently access detailed information, leading to more informed decisions and better operational efficiency.

Conclusion

By using Oracle APEX to integrate and visualize data from classic SQL structures like EMP and DEPT, you can unlock the full potential of your data. The platform’s ease of use, combined with its robust reporting and analysis tools, makes it a valuable asset in modern data processing and decision-making.

 {fullWidth}