NOA Labs

Example Project: Employee Management

A worked mini-project: schema design, business queries, and a validated transfer procedure.

This page pulls together everything covered so far into one realistic worked project: a small employee/department/project management system, built from scratch — schema, seed data, a set of business queries a real manager or HR system would actually ask, and a stored procedure that performs a validated, audited multi-step operation.

Designing the schema

Four tables, each with a clear relationship to the others: departments and employees extend the running schema used throughout this track, and projects/employee_projects add the project-tracking side of the system — employee_projects is a many-to-many junction table, since one employee can work on several projects and one project has several employees assigned to it.

SQL
CREATE TABLE departments (
    department_id   NUMBER(4) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    department_name VARCHAR2(50)  NOT NULL,
    location        VARCHAR2(50)
);

CREATE TABLE employees (
    employee_id     NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name      VARCHAR2(30)  NOT NULL,
    last_name       VARCHAR2(30)  NOT NULL,
    email           VARCHAR2(100) NOT NULL UNIQUE,
    hire_date       DATE          DEFAULT SYSDATE NOT NULL,
    job_title       VARCHAR2(50),
    salary          NUMBER(8,2)   CHECK (salary > 0),
    department_id   NUMBER(4)     REFERENCES departments (department_id),
    manager_id      NUMBER(6)     REFERENCES employees (employee_id)
);

CREATE TABLE projects (
    project_id      NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    project_name    VARCHAR2(100) NOT NULL,
    department_id   NUMBER(4)     REFERENCES departments (department_id),
    start_date      DATE          NOT NULL,
    end_date        DATE,
    budget          NUMBER(10,2)  CHECK (budget >= 0)
);

CREATE TABLE employee_projects (
    employee_id        NUMBER(6) REFERENCES employees (employee_id),
    project_id         NUMBER(6) REFERENCES projects (project_id),
    role               VARCHAR2(50),
    allocation_percent NUMBER(3) CHECK (allocation_percent BETWEEN 1 AND 100),
    assigned_date      DATE DEFAULT SYSDATE NOT NULL,
    CONSTRAINT pk_employee_projects PRIMARY KEY (employee_id, project_id)
);

Note the schema uses GENERATED ALWAYS AS IDENTITY throughout (the modern, 12c+ approach from the transactions/sequences page) rather than a separate sequence and trigger — every INSERT below simply omits the ID columns and lets Oracle assign them.

Seed data

SQL
INSERT INTO departments (department_name, location) VALUES ('Engineering', 'Austin');
INSERT INTO departments (department_name, location) VALUES ('Sales',       'Chicago');
INSERT INTO departments (department_name, location) VALUES ('Finance',    'New York');
-- department_ids assigned: 1 = Engineering, 2 = Sales, 3 = Finance
-- (a 4th department, 4 = Legal, is intentionally left with no employees for the queries below)
INSERT INTO departments (department_name, location) VALUES ('Legal', 'Boston');

INSERT INTO employees (first_name, last_name, email, hire_date, job_title, salary, department_id, manager_id)
VALUES ('Amara', 'Okafor', 'amara.okafor@example.com', DATE '2018-02-01', 'VP Engineering', 15000, 1, NULL);

INSERT INTO employees (first_name, last_name, email, hire_date, job_title, salary, department_id, manager_id)
VALUES ('Erik', 'Lindgren', 'erik.lindgren@example.com', DATE '2019-06-15', 'Engineering Manager', 11000, 1, 1);

INSERT INTO employees (first_name, last_name, email, hire_date, job_title, salary, department_id, manager_id)
VALUES ('Sofia', 'Bianchi', 'sofia.bianchi@example.com', DATE '2021-03-10', 'Software Engineer', 8700, 1, 2);

INSERT INTO employees (first_name, last_name, email, hire_date, job_title, salary, department_id, manager_id)
VALUES ('Kenji', 'Suzuki', 'kenji.suzuki@example.com', DATE '2022-09-01', 'Software Engineer', 8200, 1, 2);

INSERT INTO employees (first_name, last_name, email, hire_date, job_title, salary, department_id, manager_id)
VALUES ('Priya', 'Nair', 'priya.nair@example.com', DATE '2020-01-20', 'Sales Director', 10500, 2, 1);

INSERT INTO employees (first_name, last_name, email, hire_date, job_title, salary, department_id, manager_id)
VALUES ('Diego', 'Fernandez', 'diego.fernandez@example.com', DATE '2023-04-11', 'Account Executive', 6800, 2, 5);

INSERT INTO projects (project_name, department_id, start_date, end_date, budget)
VALUES ('Checkout Redesign', 1, DATE '2025-01-01', NULL, 250000);

INSERT INTO projects (project_name, department_id, start_date, end_date, budget)
VALUES ('Internal Analytics Platform', 1, DATE '2024-06-01', DATE '2025-05-01', 180000);

INSERT INTO projects (project_name, department_id, start_date, end_date, budget)
VALUES ('Regional Expansion', 2, DATE '2025-03-01', NULL, 120000);

INSERT INTO employee_projects (employee_id, project_id, role, allocation_percent)
VALUES (3, 1, 'Lead Developer', 80);

INSERT INTO employee_projects (employee_id, project_id, role, allocation_percent)
VALUES (4, 1, 'Developer', 60);

INSERT INTO employee_projects (employee_id, project_id, role, allocation_percent)
VALUES (4, 2, 'Developer', 40);

INSERT INTO employee_projects (employee_id, project_id, role, allocation_percent)
VALUES (6, 3, 'Account Lead', 100);

COMMIT;

(Employee IDs are shown above as plain integers purely for readability of the seed script — with GENERATED ALWAYS AS IDENTITY, the actual assigned values come from Oracle's internal sequence in insert order, starting at 1 in a fresh schema.)

Business queries

Employees earning above their department's average

A correlated subquery (see the SQL track's subqueries page) recomputes the average per row's own department, comparing each employee only against their direct peers rather than the company-wide average:

SQL
SELECT e.first_name, e.last_name, d.department_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
)
ORDER BY d.department_name, e.salary DESC;

Departments with no employees

The Legal department was seeded deliberately with zero employees — exactly the kind of row a plain JOIN would silently drop. NOT EXISTS (the safest correlated pattern for "no matching row at all," see this app's SQL subqueries page) finds it directly:

SQL
SELECT d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);
Plaintext
DEPARTMENT_NAME
Legal

The management chain for a given employee

Reusing the CONNECT BY PRIOR technique from the tricky-queries page — every manager above Sofia Bianchi, walking up to the top of the org:

SQL
SELECT employee_id, first_name, last_name, manager_id, LEVEL AS depth
FROM employees
START WITH last_name = 'Bianchi'
CONNECT BY PRIOR manager_id = employee_id
ORDER BY depth;
Plaintext
EMPLOYEE_ID  FIRST_NAME  LAST_NAME  MANAGER_ID  DEPTH
3            Sofia       Bianchi    2           1
2            Erik        Lindgren   1           2
1            Amara       Okafor     (null)      3

Employees assigned to more than one active project

An "active" project is one with no end_date yet (still ongoing). Grouping employee_projects joined against only the active projects, then keeping groups with more than one row, surfaces anyone spread across multiple concurrent projects — useful for spotting over-allocation:

SQL
SELECT e.first_name, e.last_name, COUNT(*) AS active_project_count,
       SUM(ep.allocation_percent) AS total_allocation_percent
FROM employees e
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id
WHERE p.end_date IS NULL
GROUP BY e.first_name, e.last_name
HAVING COUNT(*) > 1;
Plaintext
FIRST_NAME  LAST_NAME  ACTIVE_PROJECT_COUNT  TOTAL_ALLOCATION_PERCENT
Kenji       Suzuki     1                      40

(Only one row qualifies in this seed data — Kenji is on two employee_projects rows total, but only one points at a still-active project once Internal Analytics Platform's end_date is accounted for. Widening the WHERE to drop the active-only filter would surface him with a count of 2 instead — worth trying against the seed data above to see the difference directly.)

Department budget exposure

A quick aggregate rollup — total committed project budget per department, alongside current headcount, is the kind of at-a-glance summary that's a strong candidate for a materialized view (see the views/materialized-views page) once the underlying tables grow large:

SQL
SELECT d.department_name,
       COUNT(DISTINCT e.employee_id) AS headcount,
       NVL(SUM(DISTINCT p.budget), 0) AS total_project_budget
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
LEFT JOIN projects p ON p.department_id = d.department_id
GROUP BY d.department_name;

A stored procedure: transferring an employee between departments

Moving an employee to a different department is a real multi-step operation, not a bare UPDATE: it needs to confirm both the employee and the target department actually exist, reject a meaningless no-op transfer, and — because of the audit trigger defined below — leave a clear record of exactly what changed and when.

First, the audit table and the trigger that populates it automatically on every department change:

SQL
CREATE TABLE employee_department_audit (
    audit_id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id     NUMBER NOT NULL,
    old_department_id NUMBER,
    new_department_id NUMBER,
    changed_by      VARCHAR2(30),
    changed_at      TIMESTAMP
);

CREATE OR REPLACE TRIGGER trg_employees_audit_department
AFTER UPDATE OF department_id ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_department_audit (
        employee_id, old_department_id, new_department_id, changed_by, changed_at
    ) VALUES (
        :OLD.employee_id, :OLD.department_id, :NEW.department_id, USER, SYSTIMESTAMP
    );
END;
/

Now the procedure itself:

SQL
CREATE OR REPLACE PROCEDURE transfer_employee (
    p_employee_id      IN employees.employee_id%TYPE,
    p_new_department_id IN employees.department_id%TYPE
) AS
    v_current_department_id employees.department_id%TYPE;
    v_department_exists      NUMBER;
BEGIN
    -- 1. Confirm the employee exists, and capture their current department
    SELECT department_id INTO v_current_department_id
    FROM employees
    WHERE employee_id = p_employee_id;

    -- 2. Confirm the target department exists
    SELECT COUNT(*) INTO v_department_exists
    FROM departments
    WHERE department_id = p_new_department_id;

    IF v_department_exists = 0 THEN
        RAISE_APPLICATION_ERROR(-20010, 'Department ' || p_new_department_id || ' does not exist.');
    END IF;

    -- 3. Reject a no-op transfer
    IF v_current_department_id = p_new_department_id THEN
        RAISE_APPLICATION_ERROR(-20011, 'Employee ' || p_employee_id || ' is already in that department.');
    END IF;

    -- 4. Perform the transfer -- the AFTER UPDATE trigger above fires automatically and logs it
    UPDATE employees
    SET department_id = p_new_department_id
    WHERE employee_id = p_employee_id;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20012, 'Employee ' || p_employee_id || ' does not exist.');
END transfer_employee;
/

Calling it, then checking that the audit trigger fired as expected:

SQL
BEGIN
    transfer_employee(p_employee_id => 6, p_new_department_id => 1);
    COMMIT;
END;
/

SELECT * FROM employee_department_audit WHERE employee_id = 6;
Plaintext
AUDIT_ID  EMPLOYEE_ID  OLD_DEPARTMENT_ID  NEW_DEPARTMENT_ID  CHANGED_BY  CHANGED_AT
1         6            2                  1                  HR_ADMIN    2026-08-26 11:02:47

Notice the procedure itself never calls COMMIT — that decision is deliberately left to the caller (the design note from the stored-procedures page): transfer_employee might reasonably be called as one step inside a larger transaction that also updates a payroll record or a project assignment, and it shouldn't unilaterally decide when that larger unit of work is considered final. The three validation steps run before any data actually changes, so a rejected transfer (an unknown employee, an unknown department, or a same-department no-op) never touches a row or fires the trigger at all — exactly the behavior you'd want from a procedure other code is going to call in production without re-checking its work.

What this project demonstrates

Every piece from earlier in this track shows up here doing real work: GENERATED AS IDENTITY surrogate keys, foreign keys enforcing the department/employee/project relationships, a correlated subquery and NOT EXISTS for two of the business queries, CONNECT BY PRIOR for the management chain, a JOIN-and-GROUP BY/HAVING combination for the over-allocation check, and a stored procedure with real input validation, custom exceptions, and an audit trigger firing as a side effect of a plain UPDATE — a small but genuinely representative slice of what a production Oracle-backed application looks like end to end.