Objective

Learn how to store MP4 video files in SynapCores with essential metadata. This foundation enables building video libraries, streaming platforms, and media management systems.

Step 1: Create Video Table

Create a table for storing MP4 videos with metadata.

CREATE TABLE videos (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    video_file VIDEO(MP4),
    duration_seconds INTEGER,
    file_size BIGINT,
    resolution VARCHAR(20),
    frame_rate DECIMAL(5, 2),
    bitrate INTEGER,
    uploaded_by VARCHAR(100),
    is_public BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Sample Videos

Add sample video records.

INSERT INTO videos (id, title, description, duration_seconds, file_size, resolution, frame_rate, bitrate, uploaded_by) VALUES
    (1, 'Product Introduction', 'Overview of our flagship product features', 180, 52428800, '1920x1080', 30.00, 5000000, 'marketing'),
    (2, 'Tutorial: Getting Started', 'Step-by-step guide for new users', 600, 157286400, '1920x1080', 30.00, 4500000, 'training'),
    (3, 'Company Overview', 'About our company and mission', 240, 78643200, '1920x1080', 24.00, 4000000, 'corporate'),
    (4, 'Customer Testimonial', 'Client success story interview', 300, 94371840, '1920x1080', 30.00, 4200000, 'marketing'),
    (5, 'Feature Demo', 'Live demonstration of key features', 420, 131072000, '1920x1080', 30.00, 4800000, 'product');

Step 3: Query All Videos

Retrieve all videos with formatted information.

SELECT
    title,
    description,
    duration_seconds / 60 as duration_minutes,
    file_size / 1048576 as size_mb,
    resolution,
    uploaded_by
FROM videos
ORDER BY created_at DESC;

Step 4: Filter by Duration

Find videos within a duration range.

SELECT
    title,
    duration_seconds,
    resolution
FROM videos
WHERE duration_seconds BETWEEN 180 AND 400
ORDER BY duration_seconds;

Step 5: Search by Uploader

Find videos uploaded by a specific user.

SELECT
    title,
    description,
    duration_seconds / 60 as minutes,
    created_at
FROM videos
WHERE uploaded_by = 'marketing'
ORDER BY created_at DESC;

Step 6: Calculate Storage Statistics

Get storage usage statistics.

SELECT
    COUNT(*) as total_videos,
    SUM(file_size) / 1073741824 as total_gb,
    AVG(file_size) / 1048576 as avg_size_mb,
    SUM(duration_seconds) / 3600 as total_hours,
    AVG(duration_seconds) / 60 as avg_duration_minutes
FROM videos;

Step 7: Filter Public Videos

Get only public videos.

SELECT
    title,
    description,
    resolution,
    duration_seconds / 60 as minutes
FROM videos
WHERE is_public = TRUE
ORDER BY title;

Step 8: Videos by Resolution

Group videos by resolution.

SELECT
    resolution,
    COUNT(*) as video_count,
    SUM(file_size) / 1048576 as total_mb,
    AVG(duration_seconds) as avg_duration
FROM videos
GROUP BY resolution
ORDER BY video_count DESC;

Step 9: Recent Uploads

Find recently uploaded videos.

SELECT
    title,
    uploaded_by,
    created_at,
    file_size / 1048576 as size_mb
FROM videos
ORDER BY created_at DESC
LIMIT 5;

Step 10: Update Video Metadata

Update video information.

UPDATE videos
SET description = 'Updated: Comprehensive product feature overview',
    is_public = TRUE
WHERE id = 1;

SELECT title, description, is_public FROM videos WHERE id = 1;

Cleanup (Optional)

DROP TABLE IF EXISTS videos;

Expected Outcomes

Videos stored with metadata
Duration and size queries work
Filtering by attributes functions
Storage statistics calculated
Updates applied correctly

Key Concepts Learned

VIDEO(MP4) data type usage
Video metadata storage
Duration calculations
File size handling
Basic video queries

Basic Video Storage - MP4