Build a Training Management App with Oracle APEX — SQL Workshop Tutorial

By /

Welcome Oracle APEX training!
In this article, you will learn how to use SQL Workshop features and build a complete mini-application using APEX App Builder.

By the end of this documentation, you will have created:

  • Database tables (via Object Browser / Quick SQL)
  • Imported data using Data Workshop
  • Executed scripts
  • Built a Training Management App
  • Created reports, forms, dashboard, and master-detail pages

Let’s begin.

🚀 1. Introduction to SQL Workshop

Oracle APEX SQL Workshop allows you to manage your database objects quickly and easily.
It includes:

  • Object Browser – view/create tables
  • SQL Commands – run SQL queries instantly
  • SQL Scripts – upload and run SQL files
  • Data Workshop – import CSV/Excel files
  • Utilities → Quick SQL – generate tables quickly
  • Utilities → ER Diagram – visualize table relationships

📌 2. Create Required Tables

We will build 3 tables:

  1. TRAINERS
  2. TRAINING_PROGRAMS
  3. TRAINING_ASSIGNMENTS

You may create them via Object Browser or Quick SQL.

🧱 2.1 Creating Tables Using Object Browser

Step 1:
Navigate to:
SQL Workshop → Object Browser → Create Table

Table-1: TRAINERS

Column NameData TypeConstraints
TRAINER_IDNumber (Identity)Primary Key
TRAINER_NAMEVarchar2(100)Not Null
EMAILVarchar2(150)
EXPERIENCE_YEARSNumber
SKILL_CATEGORYVarchar2(50)

Repeat similar steps to create:

Table-2: TRAINING_PROGRAMS

Column NameData TypeConstraints
PROGRAM_IDNumber (Identity)Primary Key
PROGRAM_NAMEVarchar2(100)Not Null
DESCRIPTIONVarchar2(500)
PROGRAM_DURATIONNumber
SKILL_CATEGORYVarchar2(50)

Table-3: TRAINING_ASSIGNMENTS

Column NameData TypeConstraints
ASSIGNMENT_IDNumber (Identity)Primary Key
TRAINER_IDNumberForeign Key → Trainers
PROGRAM_IDNumberForeign Key → Training_Programs
SCHEDULE_DATEDate

Add Foreign Keys using:

Object Browser → TRAINING_ASSIGNMENTS → Constraints → Add

Or. you can create these tables using SQL Commands-

— TRAINER MASTER TABLE
CREATE TABLE TRAINERS (
TRAINER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
TRAINER_NAME VARCHAR2(100) NOT NULL,
EMAIL VARCHAR2(150),
EXPERIENCE_YEARS NUMBER,
SKILL_CATEGORY VARCHAR2(50)
);

— TRAINING PROGRAMS TABLE
CREATE TABLE TRAINING_PROGRAMS (
PROGRAM_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
PROGRAM_NAME VARCHAR2(100) NOT NULL,
DESCRIPTION VARCHAR2(500),
PROGRAM_DURATION NUMBER,
SKILL_CATEGORY VARCHAR2(50)
);

— TRAINING ASSIGNMENTS TABLE
CREATE TABLE TRAINING_ASSIGNMENTS (
ASSIGNMENT_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
TRAINER_ID NUMBER NOT NULL,
PROGRAM_ID NUMBER NOT NULL,
SCHEDULE_DATE DATE,
CONSTRAINT TA_TRAINER_FK FOREIGN KEY (TRAINER_ID)
REFERENCES TRAINERS(TRAINER_ID),
CONSTRAINT TA_PROGRAM_FK FOREIGN KEY (PROGRAM_ID)
REFERENCES TRAINING_PROGRAMS(PROGRAM_ID)
);

🔥 3. Insert Sample Data Using SQL Commands

Navigate to:

SQL Workshop → SQL Commands

Paste and run:

INSERT INTO TRAINERS (TRAINER_NAME, EMAIL, EXPERIENCE_YEARS, SKILL_CATEGORY) VALUES
('Amit Sharma', 'amit.sharma@example.com', 5, 'Database'),
('Riya Sinha', 'riya.sinha@example.com', 3, 'Frontend'),
('Karan Patel', 'karan.patel@example.com', 8, 'Cloud'),
('Sneha Rao', 'sneha.rao@example.com', 6, 'Backend');

INSERT INTO TRAINING_PROGRAMS (PROGRAM_NAME, DESCRIPTION, PROGRAM_DURATION, SKILL_CATEGORY) VALUES
('SQL Fundamentals', 'Beginner SQL concepts', 3, 'Database'),
('Advanced SQL Queries', 'Complex SQL & joins', 2, 'Database'),
('HTML/CSS Basics', 'Web designing basics', 4, 'Frontend'),
('JavaScript Essentials', 'Core JS programming', 5, 'Frontend'),
('Cloud Basics', 'Intro to cloud services', 3, 'Cloud');

INSERT INTO TRAINING_ASSIGNMENTS (TRAINER_ID, PROGRAM_ID, SCHEDULE_DATE) VALUES
(1, 1, DATE '2025-01-10'),
(1, 2, DATE '2025-01-15'),
(2, 3, DATE '2025-01-12'),
(2, 4, DATE '2025-01-18'),
(3, 5, DATE '2025-01-20');

📤 4. Import CSV Data Using Data Workshop

You can directly upload the data through csv file also.

4.1 Trainers.csv

TRAINER_NAME,EMAIL,EXPERIENCE_YEARS,SKILL_CATEGORY
Amit Sharma,amit.sharma@example.com,5,Database
Riya Sinha,riya.sinha@example.com,3,Frontend
Karan Patel,karan.patel@example.com,8,Cloud
Sneha Rao,sneha.rao@example.com,6,Backend

4.2 TrainingPrograms.csv

PROGRAM_NAME,DESCRIPTION,PROGRAM_DURATION,SKILL_CATEGORY
SQL Fundamentals,Beginner SQL concepts,3,Database
Advanced SQL Queries,Complex SQL & joins,2,Database
HTML/CSS Basics,Web designing basics,4,Frontend
JavaScript Essentials,Core JS programming,5,Frontend
Cloud Basics,Intro to cloud services,3,Cloud

4.3 Assignments.csv

TRAINER_ID,PROGRAM_ID,SCHEDULE_DATE
1,1,2025-01-10
1,2,2025-01-15
2,3,2025-01-12
2,4,2025-01-18
3,5,2025-01-20

✔ Steps to Upload CSV Using Data Workshop

  1. Navigate to: SQL Workshop → Data Workshop
  2. Click Load Data
  3. Select CSV file
  4. Choose Existing Table or Create New Table
  5. Map columns
  6. Click Load Data
  7. View load results

🗂️ 5. Using SQL Scripts

Demonstrate:

  1. Go to SQL Workshop → SQL Scripts
  2. Click Upload
  3. Select a .sql file
  4. Run the script
  5. View execution logs

🏗️ 7. Build APEX Application

Now we use these tables to build a real APEX app.

7.1 Create New App

  1. Go to App Builder → Create App
  2. App Name: Training Management System
  3. Choose Features:
    • Dashboard
    • Reports (Interactive)
  4. Click Create Application

7.2 Add Pages

Page-1: Trainers Report

  • Page Type: Interactive Report
  • Table: TRAINERS

Page-2: Trainers Form

  • Create Page → Form → Form on Table
  • Table: TRAINERS
  • Automatically adds Create / Edit pages

Page-3: Programs Report

  • Page: Interactive Report
  • Table: TRAINING_PROGRAMS

Page-4: Assignments Report

  • Page: Interactive Report
  • Table: TRAINING_ASSIGNMENTS

7.3 Create Master-Detail Page

  1. Create Page → Master Detail
  2. Master Table: TRAINERS
  3. Detail Table: TRAINING_ASSIGNMENTS

7.4 Add Dashboard Cards

  1. Open Dashboard Page
  2. Add Region → Card
  3. SQL:
SELECT COUNT(*) CNT FROM TRAINERS

Repeat for:

  • TRAINING_PROGRAMS
  • TRAINING_ASSIGNMENTS

7.5 Add Chart: Programs by Skill Category

  1. Create Page → Chart → Pie/Bar
  2. SQL Query:
SELECT SKILL_CATEGORY, COUNT(*) 
FROM TRAINING_PROGRAMS 
GROUP BY SKILL_CATEGORY;

🚀 8. Run & Test the Application

Test:

  • Add new Trainer
  • Add new Program
  • Assign Trainer to Program
  • Check dashboard
  • Check charts
  • Edit / Delete data

🧩 9. Publish & Customize App

Optional enhancements:

  • Add APEX icons
  • Use Theme Roller
  • Improve page navigation
  • Add validations

📝 10. Hands-On Exercises for Practice

Exercise 1: Add a New Table “TRAINING_VENUES”

Columns:

  • Venue_ID (Identity, PK)
  • Venue_Name
  • City
  • Capacity

Create a form + report page for this table.

Exercise 2: Create a Foreign Key Relationship

Add column VENUE_ID in TRAINING_ASSIGNMENTS table
Link it with TRAINING_VENUES table.

Exercise 3: Import a New CSV File

Add more programs using CSV import.

Exercise 4: Create a Dashboard Chart

Chart showing:

Number of Trainers per Skill Category

Exercise 5: Create Interactive Report Features

Add filters:

  • Skill Category
  • Experience (greater than 3 years)
  • City (if Venue table is added)

Save these as Default Report.

Exercise 6: Modify the Theme Using Theme Roller

Change:

  • Colors
  • Font
  • Button styles

Exercise 7: Add Validation

On Trainer Form:

  • Email must contain “@”
  • Experience must be between 1 and 30

Thank You!!!

Scroll to Top