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

Basic Audio Storage