Corporate Training Videos

Objective

Create a comprehensive corporate training video management system with course tracks, completion tracking, certification management, and compliance reporting.

Step 1: Create Training Programs Table

Define training programs.

CREATE TABLE training_programs (
    id INTEGER PRIMARY KEY,
    program_code VARCHAR(50) NOT NULL UNIQUE,
    program_name VARCHAR(200) NOT NULL,
    description TEXT,
    program_type VARCHAR(50),
    department VARCHAR(100),
    target_audience TEXT,
    is_mandatory BOOLEAN DEFAULT FALSE,
    compliance_required BOOLEAN DEFAULT FALSE,
    renewal_period_months INTEGER,
    passing_score DECIMAL(5, 2) DEFAULT 80.0,
    estimated_hours DECIMAL(5, 2),
    thumbnail IMAGE(JPEG),
    status VARCHAR(50) DEFAULT 'draft',
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP
);

Step 2: Create Training Modules Table

Organize content into modules.

CREATE TABLE training_modules (
    id INTEGER PRIMARY KEY,
    program_id INTEGER NOT NULL,
    module_number INTEGER,
    module_title VARCHAR(200),
    description TEXT,
    estimated_minutes INTEGER,
    is_required BOOLEAN DEFAULT TRUE,
    prerequisite_module_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (program_id) REFERENCES training_programs(id),
    FOREIGN KEY (prerequisite_module_id) REFERENCES training_modules(id)
);

Step 3: Create Training Videos Table

Store video content.

CREATE TABLE training_videos (
    id INTEGER PRIMARY KEY,
    module_id INTEGER NOT NULL,
    video_number INTEGER,
    video_title VARCHAR(200),
    description TEXT,
    video_file VIDEO(MP4),
    thumbnail IMAGE(JPEG),
    duration_seconds INTEGER,
    file_size_mb DECIMAL(10, 2),
    transcript TEXT,
    captions_file TEXT,
    has_quiz BOOLEAN DEFAULT FALSE,
    min_watch_percent DECIMAL(5, 2) DEFAULT 80.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (module_id) REFERENCES training_modules(id)
);

Step 4: Create Employees Table

Store employee information.

CREATE TABLE training_employees (
    id INTEGER PRIMARY KEY,
    employee_id VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(200),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    department VARCHAR(100),
    job_title VARCHAR(100),
    manager_id INTEGER,
    hire_date DATE,
    employment_status VARCHAR(50) DEFAULT 'active',
    FOREIGN KEY (manager_id) REFERENCES training_employees(id)
);

Step 5: Create Enrollments Table

Track program enrollments.

CREATE TABLE training_enrollments (
    id INTEGER PRIMARY KEY,
    employee_id INTEGER NOT NULL,
    program_id INTEGER NOT NULL,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    enrolled_by VARCHAR(100),
    due_date DATE,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    progress_percent DECIMAL(5, 2) DEFAULT 0,
    status VARCHAR(50) DEFAULT 'enrolled',
    FOREIGN KEY (employee_id) REFERENCES training_employees(id),
    FOREIGN KEY (program_id) REFERENCES training_programs(id)
);

Step 6: Create Video Progress Table

Track viewing completion.

CREATE TABLE video_watch_progress (
    id INTEGER PRIMARY KEY,
    enrollment_id INTEGER NOT NULL,
    video_id INTEGER NOT NULL,
    watched_seconds INTEGER DEFAULT 0,
    total_seconds INTEGER,
    watch_percent DECIMAL(5, 2) DEFAULT 0,
    is_completed BOOLEAN DEFAULT FALSE,
    completed_at TIMESTAMP,
    last_position_seconds INTEGER DEFAULT 0,
    watch_count INTEGER DEFAULT 1,
    last_watched TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (enrollment_id) REFERENCES training_enrollments(id),
    FOREIGN KEY (video_id) REFERENCES training_videos(id)
);

Step 7: Create Quiz Results Table

Store assessment results.

CREATE TABLE training_quiz_results (
    id INTEGER PRIMARY KEY,
    enrollment_id INTEGER NOT NULL,
    video_id INTEGER NOT NULL,
    attempt_number INTEGER DEFAULT 1,
    score DECIMAL(5, 2),
    passed BOOLEAN,
    time_spent_seconds INTEGER,
    answers_data TEXT,
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (enrollment_id) REFERENCES training_enrollments(id),
    FOREIGN KEY (video_id) REFERENCES training_videos(id)
);

Step 8: Create Certificates Table

Manage certifications.

CREATE TABLE training_certificates (
    id INTEGER PRIMARY KEY,
    enrollment_id INTEGER NOT NULL,
    certificate_number VARCHAR(100) UNIQUE,
    certificate_file PDF,
    issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    issued_by VARCHAR(100),
    digital_signature TEXT,
    verification_url VARCHAR(500),
    FOREIGN KEY (enrollment_id) REFERENCES training_enrollments(id)
);

Step 9: Insert Sample Programs

Add training programs.

INSERT INTO training_programs (id, program_code, program_name, description, program_type, department, is_mandatory, compliance_required, renewal_period_months, passing_score, estimated_hours, status, published_at) VALUES
    (1, 'SEC-101', 'Information Security Awareness', 'Annual security training for all employees', 'compliance', 'All', TRUE, TRUE, 12, 80.0, 2.0, 'published', '2023-01-01 00:00:00'),
    (2, 'HAR-101', 'Workplace Harassment Prevention', 'Required harassment prevention training', 'compliance', 'All', TRUE, TRUE, 24, 80.0, 1.5, 'published', '2023-01-01 00:00:00'),
    (3, 'SAFE-101', 'Workplace Safety', 'General safety and emergency procedures', 'compliance', 'All', TRUE, TRUE, 12, 90.0, 1.0, 'published', '2023-01-01 00:00:00'),
    (4, 'LEAD-201', 'Leadership Fundamentals', 'Essential skills for new managers', 'professional', 'Management', FALSE, FALSE, NULL, 70.0, 8.0, 'published', '2023-06-01 00:00:00'),
    (5, 'SALES-101', 'Sales Excellence', 'Product knowledge and sales techniques', 'professional', 'Sales', FALSE, FALSE, NULL, 75.0, 6.0, 'published', '2023-07-01 00:00:00'),
    (6, 'ONBOARD-101', 'New Employee Orientation', 'Company overview and policies', 'onboarding', 'All', TRUE, FALSE, NULL, 70.0, 4.0, 'published', '2023-01-01 00:00:00');

Step 10: Insert Modules and Videos

Add content structure.

INSERT INTO training_modules (id, program_id, module_number, module_title, description, estimated_minutes, is_required) VALUES
    (1, 1, 1, 'Introduction to Security', 'Why security matters', 20, TRUE),
    (2, 1, 2, 'Password Security', 'Creating and managing secure passwords', 25, TRUE),
    (3, 1, 3, 'Phishing Awareness', 'Recognizing and avoiding phishing', 30, TRUE),
    (4, 1, 4, 'Data Protection', 'Handling sensitive information', 25, TRUE),
    (5, 2, 1, 'Understanding Harassment', 'Types and definitions', 30, TRUE),
    (6, 2, 2, 'Reporting Procedures', 'How to report incidents', 20, TRUE),
    (7, 2, 3, 'Creating Safe Workplace', 'Prevention strategies', 25, TRUE),
    (8, 6, 1, 'Welcome to Company', 'Company history and values', 30, TRUE),
    (9, 6, 2, 'HR Policies', 'Benefits and policies overview', 45, TRUE),
    (10, 6, 3, 'IT Systems', 'Tools and systems training', 60, TRUE);

INSERT INTO training_videos (id, module_id, video_number, video_title, description, duration_seconds, has_quiz, min_watch_percent) VALUES
    (1, 1, 1, 'Why Security Matters', 'Introduction to corporate security', 480, TRUE, 90),
    (2, 1, 2, 'Security Threats Overview', 'Common threats we face', 600, FALSE, 80),
    (3, 2, 1, 'Password Best Practices', 'Creating strong passwords', 720, TRUE, 90),
    (4, 2, 2, 'Multi-Factor Authentication', 'Using MFA effectively', 540, TRUE, 85),
    (5, 3, 1, 'What is Phishing?', 'Understanding phishing attacks', 660, TRUE, 90),
    (6, 3, 2, 'Spotting Fake Emails', 'Red flags to watch for', 780, TRUE, 90),
    (7, 5, 1, 'Types of Harassment', 'Definitions and examples', 840, TRUE, 90),
    (8, 6, 1, 'Reporting Process', 'Step-by-step reporting guide', 600, TRUE, 95),
    (9, 8, 1, 'Our Company Story', 'History and milestones', 720, FALSE, 70),
    (10, 8, 2, 'Mission and Values', 'What we stand for', 540, FALSE, 70);

Step 11: Insert Employees and Enrollments

Add employee data.

INSERT INTO training_employees (id, employee_id, email, first_name, last_name, department, job_title, manager_id, hire_date, employment_status) VALUES
    (1, 'EMP-001', 'john.smith@company.com', 'John', 'Smith', 'Engineering', 'Software Engineer', 3, '2022-03-15', 'active'),
    (2, 'EMP-002', 'jane.doe@company.com', 'Jane', 'Doe', 'Sales', 'Sales Representative', 4, '2023-01-10', 'active'),
    (3, 'EMP-003', 'mike.johnson@company.com', 'Mike', 'Johnson', 'Engineering', 'Engineering Manager', NULL, '2020-06-01', 'active'),
    (4, 'EMP-004', 'sarah.wilson@company.com', 'Sarah', 'Wilson', 'Sales', 'Sales Manager', NULL, '2019-09-15', 'active'),
    (5, 'EMP-005', 'emily.brown@company.com', 'Emily', 'Brown', 'HR', 'HR Specialist', NULL, '2021-04-20', 'active'),
    (6, 'EMP-006', 'david.lee@company.com', 'David', 'Lee', 'Engineering', 'DevOps Engineer', 3, '2023-11-01', 'active');

INSERT INTO training_enrollments (id, employee_id, program_id, enrolled_at, enrolled_by, due_date, started_at, completed_at, progress_percent, status) VALUES
    (1, 1, 1, '2024-01-01 00:00:00', 'System', '2024-01-31', '2024-01-05 09:00:00', '2024-01-10 14:30:00', 100.0, 'completed'),
    (2, 1, 2, '2024-01-01 00:00:00', 'System', '2024-02-28', '2024-01-15 10:00:00', NULL, 45.0, 'in_progress'),
    (3, 2, 1, '2024-01-01 00:00:00', 'System', '2024-01-31', '2024-01-08 11:00:00', '2024-01-12 16:00:00', 100.0, 'completed'),
    (4, 2, 6, '2023-01-10 00:00:00', 'System', '2023-01-24', '2023-01-10 09:00:00', '2023-01-12 15:00:00', 100.0, 'completed'),
    (5, 6, 1, '2024-01-01 00:00:00', 'System', '2024-01-31', NULL, NULL, 0.0, 'enrolled'),
    (6, 6, 6, '2023-11-01 00:00:00', 'System', '2023-11-15', '2023-11-01 10:00:00', '2023-11-03 14:00:00', 100.0, 'completed');

Step 12: Insert Progress and Results

Add completion data.

INSERT INTO video_watch_progress (id, enrollment_id, video_id, watched_seconds, total_seconds, watch_percent, is_completed, completed_at) VALUES
    (1, 1, 1, 480, 480, 100.0, TRUE, '2024-01-05 09:30:00'),
    (2, 1, 2, 600, 600, 100.0, TRUE, '2024-01-05 10:00:00'),
    (3, 1, 3, 720, 720, 100.0, TRUE, '2024-01-06 09:30:00'),
    (4, 1, 4, 540, 540, 100.0, TRUE, '2024-01-06 10:15:00'),
    (5, 1, 5, 660, 660, 100.0, TRUE, '2024-01-08 14:00:00'),
    (6, 1, 6, 780, 780, 100.0, TRUE, '2024-01-10 14:00:00'),
    (7, 2, 7, 450, 840, 53.6, FALSE, NULL),
    (8, 3, 1, 480, 480, 100.0, TRUE, '2024-01-09 11:00:00'),
    (9, 3, 2, 600, 600, 100.0, TRUE, '2024-01-09 11:45:00');

INSERT INTO training_quiz_results (id, enrollment_id, video_id, attempt_number, score, passed, time_spent_seconds) VALUES
    (1, 1, 1, 1, 90.0, TRUE, 180),
    (2, 1, 3, 1, 85.0, TRUE, 240),
    (3, 1, 4, 1, 80.0, TRUE, 200),
    (4, 1, 5, 1, 75.0, FALSE, 220),
    (5, 1, 5, 2, 95.0, TRUE, 180),
    (6, 1, 6, 1, 100.0, TRUE, 150),
    (7, 3, 1, 1, 95.0, TRUE, 160);

Step 13: Insert Certificates

Add certification records.

INSERT INTO training_certificates (id, enrollment_id, certificate_number, issued_at, expires_at, issued_by, verification_url) VALUES
    (1, 1, 'CERT-SEC-2024-00001', '2024-01-10 14:30:00', '2025-01-10 00:00:00', 'HR System', 'https://training.company.com/verify/CERT-SEC-2024-00001'),
    (2, 3, 'CERT-SEC-2024-00002', '2024-01-12 16:00:00', '2025-01-12 00:00:00', 'HR System', 'https://training.company.com/verify/CERT-SEC-2024-00002'),
    (3, 4, 'CERT-ONB-2023-00045', '2023-01-12 15:00:00', NULL, 'HR System', 'https://training.company.com/verify/CERT-ONB-2023-00045'),
    (4, 6, 'CERT-ONB-2023-00089', '2023-11-03 14:00:00', NULL, 'HR System', 'https://training.company.com/verify/CERT-ONB-2023-00089');

Step 14: Compliance Status Report

Track mandatory training.

SELECT
    te.first_name || ' ' || te.last_name as employee_name,
    te.department,
    tp.program_name,
    tn.due_date,
    tn.progress_percent,
    tn.status,
    tc.expires_at as certification_expires
FROM training_employees te
CROSS JOIN training_programs tp
LEFT JOIN training_enrollments tn ON te.id = tn.employee_id AND tp.id = tn.program_id
LEFT JOIN training_certificates tc ON tn.id = tc.enrollment_id
WHERE tp.compliance_required = TRUE
  AND te.employment_status = 'active'
ORDER BY te.department, te.last_name, tp.program_name;

Step 15: Training Analytics Dashboard

View overall statistics.

SELECT
    tp.program_name,
    tp.program_type,
    COUNT(tn.id) as total_enrollments,
    COUNT(CASE WHEN tn.status = 'completed' THEN 1 END) as completions,
    COUNT(CASE WHEN tn.status = 'in_progress' THEN 1 END) as in_progress,
    COUNT(CASE WHEN tn.status = 'enrolled' AND tn.started_at IS NULL THEN 1 END) as not_started,
    AVG(CASE WHEN tn.status = 'completed' THEN tn.progress_percent END) as avg_completion,
    COUNT(tc.id) as certificates_issued
FROM training_programs tp
LEFT JOIN training_enrollments tn ON tp.id = tn.program_id
LEFT JOIN training_certificates tc ON tn.id = tc.enrollment_id
WHERE tp.status = 'published'
GROUP BY tp.id, tp.program_name, tp.program_type
ORDER BY total_enrollments DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS training_certificates;
DROP TABLE IF EXISTS training_quiz_results;
DROP TABLE IF EXISTS video_watch_progress;
DROP TABLE IF EXISTS training_enrollments;
DROP TABLE IF EXISTS training_employees;
DROP TABLE IF EXISTS training_videos;
DROP TABLE IF EXISTS training_modules;
DROP TABLE IF EXISTS training_programs;

Expected Outcomes

• Training programs organized
• Videos tracked by completion
• Quizzes validated learning
• Certificates generated
• Compliance monitored

Key Concepts Learned

• Training program structure
• Video completion tracking
• Quiz and assessment management
• Certification workflow
• Compliance reporting