Basic Audio Storage

Objective

Create a simple table for storing MP3 audio files. This is the foundation for building audio management applications like music libraries, podcast archives, and voice recording systems.

Step 1: Create Audio Files Table

Create a table for storing MP3 audio files.

CREATE TABLE audio_files (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    audio_file AUDIO(MP3),
    description TEXT,
    duration_seconds INTEGER,
    file_size BIGINT,
    uploaded_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Sample Audio Records

Add sample audio file records.

INSERT INTO audio_files (id, title, description, duration_seconds, file_size, uploaded_by) VALUES
    (1, 'Morning Podcast Episode 1', 'Introduction to our daily podcast series', 1800, 28800000, 'host_john'),
    (2, 'Interview with Tech Expert', 'Discussion about AI trends in 2024', 2700, 43200000, 'host_john'),
    (3, 'Music Track - Summer Vibes', 'Upbeat instrumental music', 240, 3840000, 'producer_mike'),
    (4, 'Voice Memo - Meeting Notes', 'Quick summary of project meeting', 120, 1920000, 'user_sarah'),
    (5, 'Meditation Guide - Relaxation', '10-minute guided meditation session', 600, 9600000, 'wellness_team');

Step 3: Query All Audio Files

Retrieve all audio files with basic info.

SELECT
    id,
    title,
    duration_seconds,
    file_size,
    uploaded_by,
    created_at
FROM audio_files
ORDER BY created_at DESC;

Step 4: Format Duration Display

Display duration in readable format.

SELECT
    title,
    duration_seconds,
    (duration_seconds / 60) || ':' ||
    CASE
        WHEN (duration_seconds % 60) < 10 THEN '0' || (duration_seconds % 60)
        ELSE CAST((duration_seconds % 60) AS VARCHAR)
    END as duration_formatted,
    file_size
FROM audio_files
ORDER BY duration_seconds DESC;

Step 5: Filter by Duration

Find audio files within a duration range.

-- Short audio (under 5 minutes)
SELECT title, duration_seconds
FROM audio_files
WHERE duration_seconds < 300;

-- Medium audio (5-30 minutes)
SELECT title, duration_seconds
FROM audio_files
WHERE duration_seconds BETWEEN 300 AND 1800;

-- Long audio (over 30 minutes)
SELECT title, duration_seconds
FROM audio_files
WHERE duration_seconds > 1800;

Step 6: Calculate Storage Usage

Analyze storage used by audio files.

SELECT
    COUNT(*) as total_files,
    SUM(duration_seconds) as total_duration_seconds,
    SUM(duration_seconds) / 60 as total_duration_minutes,
    SUM(file_size) as total_bytes,
    SUM(file_size) / 1048576 as total_megabytes,
    AVG(file_size) as avg_file_size
FROM audio_files;

Step 7: Search by Title

Search audio files by title.

SELECT
    title,
    description,
    duration_seconds
FROM audio_files
WHERE title LIKE '%Podcast%'
   OR title LIKE '%Episode%'
ORDER BY title;

Step 8: Audio Files by Uploader

Group audio files by who uploaded them.

SELECT
    uploaded_by,
    COUNT(*) as file_count,
    SUM(duration_seconds) as total_duration,
    SUM(file_size) as total_size
FROM audio_files
GROUP BY uploaded_by
ORDER BY file_count DESC;

Step 9: Recent Uploads

Find recently uploaded audio files.

SELECT
    title,
    uploaded_by,
    created_at
FROM audio_files
ORDER BY created_at DESC
LIMIT 10;

Step 10: Update Audio Metadata

Update audio file information.

UPDATE audio_files
SET description = 'Updated: Introduction to our popular daily podcast series',
    duration_seconds = 1850
WHERE id = 1;

SELECT title, description, duration_seconds
FROM audio_files
WHERE id = 1;

Cleanup (Optional)

DROP TABLE IF EXISTS audio_files;

Expected Outcomes

• Audio files table created with AUDIO(MP3) type
• Metadata stored alongside audio
• Duration queries filter correctly
• Storage calculations accurate
• Search and filtering functional

Key Concepts Learned

• AUDIO(MP3) type for audio storage
• Duration tracking in seconds
• File size tracking
• Basic audio file queries
• Storage usage analysis