Course Video Library

Objective

Create a comprehensive system for managing educational course videos, including course organization, video chapters, transcriptions, and student progress tracking.

Step 1: Create Courses Table

Define course catalog.

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    course_code VARCHAR(50) NOT NULL UNIQUE,
    course_name VARCHAR(200) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    skill_level VARCHAR(50),
    instructor_id INTEGER,
    instructor_name VARCHAR(200),
    thumbnail IMAGE(JPEG),
    preview_video VIDEO(MP4),
    duration_hours DECIMAL(5, 2),
    total_videos INTEGER DEFAULT 0,
    language VARCHAR(50) DEFAULT 'English',
    has_certificate BOOLEAN DEFAULT TRUE,
    price DECIMAL(10, 2),
    status VARCHAR(50) DEFAULT 'draft',
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Course Modules Table

Organize courses into modules.

CREATE TABLE course_modules (
    id INTEGER PRIMARY KEY,
    course_id INTEGER NOT NULL,
    module_number INTEGER NOT NULL,
    module_title VARCHAR(200),
    description TEXT,
    duration_minutes INTEGER,
    is_free_preview BOOLEAN DEFAULT FALSE,
    prerequisites TEXT,
    learning_objectives TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Step 3: Create Course Videos Table

Store video content.

CREATE TABLE course_videos (
    id INTEGER PRIMARY KEY,
    module_id INTEGER NOT NULL,
    video_number INTEGER NOT NULL,
    video_title VARCHAR(200),
    description TEXT,
    video_file VIDEO(MP4),
    duration_seconds INTEGER,
    resolution VARCHAR(20),
    file_size_mb DECIMAL(10, 2),
    thumbnail IMAGE(JPEG),
    transcript TEXT,
    captions_file TEXT,
    has_quiz BOOLEAN DEFAULT FALSE,
    is_downloadable BOOLEAN DEFAULT FALSE,
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (module_id) REFERENCES course_modules(id)
);

Step 4: Create Video Chapters Table

Define video sections.

CREATE TABLE video_chapters (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    chapter_number INTEGER NOT NULL,
    chapter_title VARCHAR(200),
    start_time_seconds INTEGER NOT NULL,
    end_time_seconds INTEGER,
    description TEXT,
    thumbnail IMAGE(JPEG),
    FOREIGN KEY (video_id) REFERENCES course_videos(id)
);

Step 5: Create Student Enrollments Table

Track course enrollments.

CREATE TABLE student_enrollments (
    id INTEGER PRIMARY KEY,
    student_id VARCHAR(50) NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    progress_percent DECIMAL(5, 2) DEFAULT 0,
    last_accessed TIMESTAMP,
    certificate_issued BOOLEAN DEFAULT FALSE,
    certificate_url VARCHAR(500),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Step 6: Create Video Progress Table

Track viewing progress.

CREATE TABLE video_progress (
    id INTEGER PRIMARY KEY,
    enrollment_id INTEGER NOT NULL,
    video_id INTEGER NOT NULL,
    watched_seconds INTEGER DEFAULT 0,
    total_seconds INTEGER,
    progress_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 student_enrollments(id),
    FOREIGN KEY (video_id) REFERENCES course_videos(id)
);

Step 7: Insert Sample Courses

Add course catalog.

INSERT INTO courses (id, course_code, course_name, description, category, skill_level, instructor_id, instructor_name, duration_hours, total_videos, language, price, status, published_at) VALUES
    (1, 'WEB-101', 'Web Development Fundamentals', 'Learn HTML, CSS, and JavaScript basics', 'Web Development', 'Beginner', 1, 'John Smith', 24.5, 45, 'English', 49.99, 'published', '2023-06-01 00:00:00'),
    (2, 'PY-201', 'Python for Data Science', 'Master Python for data analysis and visualization', 'Data Science', 'Intermediate', 2, 'Sarah Johnson', 32.0, 60, 'English', 79.99, 'published', '2023-07-15 00:00:00'),
    (3, 'ML-301', 'Machine Learning Essentials', 'Build ML models from scratch', 'Machine Learning', 'Advanced', 3, 'Michael Chen', 40.0, 75, 'English', 99.99, 'published', '2023-09-01 00:00:00'),
    (4, 'DESIGN-101', 'UI/UX Design Principles', 'Create beautiful user interfaces', 'Design', 'Beginner', 4, 'Emily Brown', 18.0, 35, 'English', 39.99, 'published', '2023-10-01 00:00:00'),
    (5, 'DB-201', 'Database Design Mastery', 'Design scalable database architectures', 'Database', 'Intermediate', 5, 'David Wilson', 28.0, 52, 'English', 69.99, 'published', '2023-11-01 00:00:00');

Step 8: Insert Course Modules

Add module structure.

INSERT INTO course_modules (id, course_id, module_number, module_title, description, duration_minutes, is_free_preview) VALUES
    (1, 1, 1, 'Introduction to HTML', 'HTML basics and document structure', 180, TRUE),
    (2, 1, 2, 'CSS Styling', 'Styling web pages with CSS', 240, FALSE),
    (3, 1, 3, 'JavaScript Basics', 'Programming fundamentals with JS', 300, FALSE),
    (4, 1, 4, 'Building Projects', 'Hands-on web projects', 360, FALSE),
    (5, 2, 1, 'Python Fundamentals', 'Python syntax and data types', 200, TRUE),
    (6, 2, 2, 'NumPy and Pandas', 'Data manipulation libraries', 280, FALSE),
    (7, 2, 3, 'Data Visualization', 'Creating charts and graphs', 240, FALSE),
    (8, 3, 1, 'ML Foundations', 'Understanding machine learning concepts', 220, TRUE),
    (9, 3, 2, 'Supervised Learning', 'Classification and regression', 320, FALSE),
    (10, 3, 3, 'Neural Networks', 'Deep learning fundamentals', 380, FALSE);

Step 9: Insert Course Videos

Add video content.

INSERT INTO course_videos (id, module_id, video_number, video_title, description, duration_seconds, resolution, file_size_mb, has_quiz, view_count) VALUES
    (1, 1, 1, 'What is HTML?', 'Introduction to HyperText Markup Language', 485, '1080p', 125.5, FALSE, 15420),
    (2, 1, 2, 'HTML Document Structure', 'Understanding doctypes and basic structure', 612, '1080p', 158.2, TRUE, 14890),
    (3, 1, 3, 'HTML Tags and Elements', 'Common HTML tags explained', 728, '1080p', 188.4, TRUE, 14250),
    (4, 1, 4, 'Forms and Inputs', 'Creating interactive forms', 845, '1080p', 218.6, TRUE, 13800),
    (5, 2, 1, 'CSS Introduction', 'What is CSS and how it works', 520, '1080p', 134.5, FALSE, 13200),
    (6, 2, 2, 'Selectors and Properties', 'CSS selector syntax', 685, '1080p', 177.2, TRUE, 12800),
    (7, 2, 3, 'Box Model', 'Understanding the CSS box model', 592, '1080p', 153.1, TRUE, 12450),
    (8, 3, 1, 'JavaScript Basics', 'Variables, types, and operators', 756, '1080p', 195.6, FALSE, 12100),
    (9, 3, 2, 'Functions and Scope', 'Creating reusable code', 824, '1080p', 213.2, TRUE, 11800),
    (10, 3, 3, 'DOM Manipulation', 'Interacting with web pages', 912, '1080p', 235.9, TRUE, 11500);

Step 10: Insert Video Chapters

Add chapter markers.

INSERT INTO video_chapters (id, video_id, chapter_number, chapter_title, start_time_seconds, end_time_seconds, description) VALUES
    (1, 1, 1, 'Welcome', 0, 45, 'Course introduction'),
    (2, 1, 2, 'What is HTML?', 45, 180, 'HTML definition and history'),
    (3, 1, 3, 'How Browsers Work', 180, 320, 'Browser rendering explained'),
    (4, 1, 4, 'Your First HTML File', 320, 485, 'Creating an HTML file'),
    (5, 2, 1, 'Document Type', 0, 120, 'DOCTYPE declaration'),
    (6, 2, 2, 'Head Section', 120, 280, 'Meta tags and title'),
    (7, 2, 3, 'Body Section', 280, 450, 'Page content structure'),
    (8, 2, 4, 'Best Practices', 450, 612, 'HTML coding standards'),
    (9, 3, 1, 'Opening Tags', 0, 150, 'Tag syntax explained'),
    (10, 3, 2, 'Closing Tags', 150, 300, 'Self-closing vs paired tags'),
    (11, 3, 3, 'Attributes', 300, 500, 'Adding attributes to elements'),
    (12, 3, 4, 'Nesting Elements', 500, 728, 'Proper element nesting');

Step 11: Insert Enrollments and Progress

Add student data.

INSERT INTO student_enrollments (id, student_id, course_id, enrolled_at, started_at, progress_percent, last_accessed) VALUES
    (1, 'STU-001', 1, '2024-01-01 10:00:00', '2024-01-02 14:00:00', 45.5, '2024-01-15 16:30:00'),
    (2, 'STU-001', 2, '2024-01-05 09:00:00', '2024-01-06 10:00:00', 22.0, '2024-01-14 11:00:00'),
    (3, 'STU-002', 1, '2024-01-03 11:00:00', '2024-01-03 14:00:00', 100.0, '2024-01-12 09:00:00'),
    (4, 'STU-002', 3, '2024-01-08 15:00:00', '2024-01-09 10:00:00', 15.0, '2024-01-15 14:00:00'),
    (5, 'STU-003', 1, '2024-01-10 08:00:00', NULL, 0.0, NULL);

INSERT INTO video_progress (id, enrollment_id, video_id, watched_seconds, total_seconds, progress_percent, is_completed, last_position_seconds) VALUES
    (1, 1, 1, 485, 485, 100.0, TRUE, 485),
    (2, 1, 2, 612, 612, 100.0, TRUE, 612),
    (3, 1, 3, 450, 728, 61.8, FALSE, 450),
    (4, 3, 1, 485, 485, 100.0, TRUE, 485),
    (5, 3, 2, 612, 612, 100.0, TRUE, 612),
    (6, 3, 3, 728, 728, 100.0, TRUE, 728),
    (7, 3, 4, 845, 845, 100.0, TRUE, 845);

Step 12: Get Course Overview with Videos

View course structure.

SELECT
    c.course_name,
    c.instructor_name,
    cm.module_number,
    cm.module_title,
    COUNT(cv.id) as video_count,
    SUM(cv.duration_seconds) / 60 as total_minutes
FROM courses c
INNER JOIN course_modules cm ON c.id = cm.course_id
LEFT JOIN course_videos cv ON cm.id = cv.module_id
WHERE c.id = 1
GROUP BY c.id, c.course_name, c.instructor_name, cm.id, cm.module_number, cm.module_title
ORDER BY cm.module_number;

Step 13: Student Progress Dashboard

View learning progress.

SELECT
    c.course_name,
    se.progress_percent as course_progress,
    COUNT(CASE WHEN vp.is_completed THEN 1 END) as videos_completed,
    COUNT(vp.id) as videos_started,
    c.total_videos,
    se.enrolled_at,
    se.last_accessed
FROM student_enrollments se
INNER JOIN courses c ON se.course_id = c.id
LEFT JOIN video_progress vp ON se.id = vp.enrollment_id
WHERE se.student_id = 'STU-001'
GROUP BY se.id, c.course_name, se.progress_percent, c.total_videos, se.enrolled_at, se.last_accessed
ORDER BY se.last_accessed DESC;

Step 14: Popular Videos Report

Identify top content.

SELECT
    c.course_name,
    cm.module_title,
    cv.video_title,
    cv.duration_seconds / 60 as duration_minutes,
    cv.view_count,
    cv.has_quiz
FROM course_videos cv
INNER JOIN course_modules cm ON cv.module_id = cm.id
INNER JOIN courses c ON cm.course_id = c.id
ORDER BY cv.view_count DESC
LIMIT 10;

Step 15: Course Completion Analysis

Track completion rates.

SELECT
    c.course_name,
    c.skill_level,
    COUNT(se.id) as total_enrollments,
    COUNT(CASE WHEN se.completed_at IS NOT NULL THEN 1 END) as completions,
    AVG(se.progress_percent) as avg_progress,
    COUNT(CASE WHEN se.certificate_issued THEN 1 END) as certificates_issued
FROM courses c
LEFT JOIN student_enrollments se ON c.id = se.course_id
WHERE c.status = 'published'
GROUP BY c.id, c.course_name, c.skill_level
ORDER BY total_enrollments DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS video_progress;
DROP TABLE IF EXISTS student_enrollments;
DROP TABLE IF EXISTS video_chapters;
DROP TABLE IF EXISTS course_videos;
DROP TABLE IF EXISTS course_modules;
DROP TABLE IF EXISTS courses;

Expected Outcomes

• Course catalog organized
• Modules and videos structured
• Chapters enable navigation
• Progress tracked per student
• Analytics available

Key Concepts Learned

• Course content hierarchy
• Video chapter markers
• Progress tracking
• Enrollment management
• Learning analytics