Multi-Format Video Library

Objective

Create a video library that supports multiple video formats. This enables serving videos optimized for different platforms, devices, and bandwidth conditions.

Step 1: Create Multi-Format Video Table

Create a table supporting various video formats.

CREATE TABLE video_library (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(50),
    video_mp4 VIDEO(MP4),
    video_webm VIDEO(WEBM),
    video_mov VIDEO(MOV),
    video_mkv VIDEO(MKV),
    video_avi VIDEO(AVI),
    primary_format VARCHAR(10) DEFAULT 'mp4',
    duration_seconds INTEGER,
    original_resolution VARCHAR(20),
    codec VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Format Metadata Table

Track format-specific details.

CREATE TABLE video_formats (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    format_type VARCHAR(10) NOT NULL,
    file_size BIGINT,
    bitrate INTEGER,
    resolution VARCHAR(20),
    codec VARCHAR(50),
    is_available BOOLEAN DEFAULT FALSE,
    conversion_status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES video_library(id)
);

Step 3: Insert Sample Videos

Add sample video records.

INSERT INTO video_library (id, title, description, category, primary_format, duration_seconds, original_resolution, codec) VALUES
    (1, 'Corporate Presentation', 'Annual company presentation video', 'corporate', 'mp4', 1200, '1920x1080', 'H.264'),
    (2, 'Product Launch Event', 'Live recording of product launch', 'marketing', 'mp4', 3600, '3840x2160', 'H.265'),
    (3, 'Training Module 1', 'Employee onboarding training', 'training', 'mp4', 900, '1920x1080', 'H.264'),
    (4, 'Customer Interview', 'Success story documentary', 'testimonial', 'mov', 600, '1920x1080', 'ProRes'),
    (5, 'Tech Demo', 'Software demonstration walkthrough', 'demo', 'mp4', 480, '1280x720', 'H.264');

Step 4: Insert Format Variants

Add format-specific records.

INSERT INTO video_formats (id, video_id, format_type, file_size, bitrate, resolution, codec, is_available, conversion_status) VALUES
    -- Video 1 formats
    (1, 1, 'mp4', 157286400, 5000000, '1920x1080', 'H.264', TRUE, 'completed'),
    (2, 1, 'webm', 125829120, 4000000, '1920x1080', 'VP9', TRUE, 'completed'),
    (3, 1, 'mov', 209715200, 7000000, '1920x1080', 'ProRes', FALSE, 'pending'),
    -- Video 2 formats
    (4, 2, 'mp4', 943718400, 8000000, '3840x2160', 'H.265', TRUE, 'completed'),
    (5, 2, 'webm', 754974720, 6500000, '3840x2160', 'VP9', TRUE, 'completed'),
    (6, 2, 'mkv', 1073741824, 9000000, '3840x2160', 'H.265', TRUE, 'completed'),
    -- Video 3 formats
    (7, 3, 'mp4', 117964800, 4000000, '1920x1080', 'H.264', TRUE, 'completed'),
    (8, 3, 'webm', 94371840, 3200000, '1920x1080', 'VP9', FALSE, 'processing'),
    -- Video 4 formats
    (9, 4, 'mov', 78643200, 4500000, '1920x1080', 'ProRes', TRUE, 'completed'),
    (10, 4, 'mp4', 62914560, 3500000, '1920x1080', 'H.264', TRUE, 'completed');

Step 5: Query Videos with Available Formats

Get videos with their available formats.

SELECT
    vl.title,
    vl.category,
    vf.format_type,
    vf.resolution,
    vf.file_size / 1048576 as size_mb,
    vf.bitrate / 1000000 as bitrate_mbps
FROM video_library vl
INNER JOIN video_formats vf ON vl.id = vf.video_id
WHERE vf.is_available = TRUE
ORDER BY vl.title, vf.format_type;

Step 6: Format Availability Summary

Check format availability for each video.

SELECT
    vl.title,
    SUM(CASE WHEN vf.format_type = 'mp4' AND vf.is_available THEN 1 ELSE 0 END) as mp4,
    SUM(CASE WHEN vf.format_type = 'webm' AND vf.is_available THEN 1 ELSE 0 END) as webm,
    SUM(CASE WHEN vf.format_type = 'mov' AND vf.is_available THEN 1 ELSE 0 END) as mov,
    SUM(CASE WHEN vf.format_type = 'mkv' AND vf.is_available THEN 1 ELSE 0 END) as mkv
FROM video_library vl
LEFT JOIN video_formats vf ON vl.id = vf.video_id
GROUP BY vl.id, vl.title;

Step 7: Find Smallest Format

Get smallest available format for each video.

SELECT
    vl.title,
    vf.format_type,
    vf.file_size / 1048576 as size_mb,
    vf.resolution
FROM video_library vl
INNER JOIN video_formats vf ON vl.id = vf.video_id
WHERE vf.is_available = TRUE
  AND vf.file_size = (
      SELECT MIN(vf2.file_size)
      FROM video_formats vf2
      WHERE vf2.video_id = vl.id AND vf2.is_available = TRUE
  );

Step 8: Storage by Format

Analyze storage usage by format.

SELECT
    format_type,
    COUNT(*) as video_count,
    SUM(file_size) / 1073741824 as total_gb,
    AVG(file_size) / 1048576 as avg_mb,
    AVG(bitrate) / 1000000 as avg_bitrate_mbps
FROM video_formats
WHERE is_available = TRUE
GROUP BY format_type
ORDER BY total_gb DESC;

Step 9: Pending Conversions

Find videos needing format conversion.

SELECT
    vl.title,
    vf.format_type,
    vf.conversion_status,
    vl.original_resolution
FROM video_library vl
INNER JOIN video_formats vf ON vl.id = vf.video_id
WHERE vf.conversion_status != 'completed'
ORDER BY vf.conversion_status, vl.title;

Step 10: Best Quality Format

Find highest quality format per video.

SELECT
    vl.title,
    vf.format_type,
    vf.resolution,
    vf.bitrate / 1000000 as bitrate_mbps,
    vf.codec
FROM video_library vl
INNER JOIN video_formats vf ON vl.id = vf.video_id
WHERE vf.is_available = TRUE
  AND vf.bitrate = (
      SELECT MAX(vf2.bitrate)
      FROM video_formats vf2
      WHERE vf2.video_id = vl.id AND vf2.is_available = TRUE
  );

Cleanup (Optional)

DROP TABLE IF EXISTS video_formats;
DROP TABLE IF EXISTS video_library;

Expected Outcomes

• Multiple video formats stored
• Format availability tracked
• Storage analyzed by format
• Conversion status monitored
• Quality comparisons work

Supported Formats

Key Concepts Learned

• Multiple VIDEO types in one table
• Format variant management
• Conversion tracking
• Storage optimization queries
• Quality comparison patterns