Multi-Format Audio Library

Objective

Create an audio library that supports multiple audio formats. AIDB natively supports MP3, WAV, FLAC, AAC, and OGG formats with explicit type declarations.

Step 1: Create Multi-Format Audio Table

Create a table that can store audio in different formats.

CREATE TABLE audio_library (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    artist VARCHAR(100),
    format VARCHAR(10) NOT NULL,
    mp3_file AUDIO(MP3),
    wav_file AUDIO(WAV),
    flac_file AUDIO(FLAC),
    aac_file AUDIO(AAC),
    ogg_file AUDIO(OGG),
    duration_seconds INTEGER,
    bitrate INTEGER,
    sample_rate INTEGER,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Format-Specific Tables

Create separate tables for each audio format.

-- MP3 files (compressed, most common)
CREATE TABLE mp3_audio (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    audio AUDIO(MP3) NOT NULL,
    bitrate INTEGER DEFAULT 320,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- WAV files (uncompressed, high quality)
CREATE TABLE wav_audio (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    audio AUDIO(WAV) NOT NULL,
    sample_rate INTEGER DEFAULT 44100,
    bit_depth INTEGER DEFAULT 16,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- FLAC files (lossless compression)
CREATE TABLE flac_audio (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    audio AUDIO(FLAC) NOT NULL,
    compression_level INTEGER DEFAULT 5,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- AAC files (modern compressed)
CREATE TABLE aac_audio (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    audio AUDIO(AAC) NOT NULL,
    bitrate INTEGER DEFAULT 256,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- OGG files (open source format)
CREATE TABLE ogg_audio (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    audio AUDIO(OGG) NOT NULL,
    quality INTEGER DEFAULT 8,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Insert Sample Audio Records

Add sample records for each format.

INSERT INTO mp3_audio (id, title, bitrate, file_size) VALUES
    (1, 'Pop Song Master', 320, 8500000),
    (2, 'Podcast Episode 15', 128, 28000000),
    (3, 'Ambient Background', 192, 4200000);

INSERT INTO wav_audio (id, title, sample_rate, bit_depth, file_size) VALUES
    (1, 'Studio Recording Raw', 48000, 24, 125000000),
    (2, 'Voice Over Take 1', 44100, 16, 45000000);

INSERT INTO flac_audio (id, title, compression_level, file_size) VALUES
    (1, 'Symphony Orchestra', 8, 85000000),
    (2, 'Jazz Album Track', 5, 42000000);

INSERT INTO aac_audio (id, title, bitrate, file_size) VALUES
    (1, 'Streaming Version', 256, 7200000),
    (2, 'Mobile Podcast', 128, 15000000);

INSERT INTO ogg_audio (id, title, quality, file_size) VALUES
    (1, 'Game Soundtrack', 8, 6500000),
    (2, 'Podcast Archive', 5, 22000000);

Step 4: Create Unified Audio Index

Track all audio across formats in one table.

INSERT INTO audio_library (id, title, artist, format, duration_seconds, bitrate, file_size) VALUES
    (1, 'Pop Song Master', 'Studio Band', 'MP3', 240, 320, 8500000),
    (2, 'Symphony Orchestra', 'City Philharmonic', 'FLAC', 1800, 1411, 85000000),
    (3, 'Studio Recording Raw', 'Voice Artist', 'WAV', 180, 1536, 125000000),
    (4, 'Streaming Version', 'Podcast Host', 'AAC', 2400, 256, 7200000),
    (5, 'Game Soundtrack', 'Composer X', 'OGG', 300, 320, 6500000);

Step 5: Query by Format

Filter audio by format type.

SELECT
    title,
    artist,
    format,
    duration_seconds,
    file_size
FROM audio_library
WHERE format = 'MP3'
ORDER BY title;

Step 6: Compare Formats by Size

Analyze file sizes across formats.

SELECT
    format,
    COUNT(*) as file_count,
    AVG(file_size) as avg_size,
    SUM(file_size) as total_size
FROM audio_library
GROUP BY format
ORDER BY avg_size DESC;

Step 7: Lossless vs Lossy Query

Separate lossless and lossy formats.

SELECT
    title,
    format,
    file_size,
    CASE
        WHEN format IN ('WAV', 'FLAC') THEN 'Lossless'
        ELSE 'Lossy'
    END as compression_type
FROM audio_library
ORDER BY compression_type, format;

Step 8: High Quality Audio

Find high-quality audio files.

SELECT
    title,
    format,
    bitrate,
    sample_rate
FROM audio_library
WHERE bitrate >= 256
   OR format IN ('WAV', 'FLAC')
ORDER BY bitrate DESC;

Step 9: Format Statistics

Generate format usage statistics.

SELECT
    format,
    COUNT(*) as count,
    SUM(duration_seconds) / 60 as total_minutes,
    SUM(file_size) / 1048576 as total_mb,
    AVG(bitrate) as avg_bitrate
FROM audio_library
GROUP BY format
ORDER BY count DESC;

Step 10: Storage Efficiency Analysis

Compare storage efficiency by format.

SELECT
    format,
    AVG(file_size / duration_seconds) as bytes_per_second,
    AVG(file_size / duration_seconds / 1024) as kb_per_second
FROM audio_library
WHERE duration_seconds > 0
GROUP BY format
ORDER BY bytes_per_second;

Cleanup (Optional)

DROP TABLE IF EXISTS audio_library;
DROP TABLE IF EXISTS mp3_audio;
DROP TABLE IF EXISTS wav_audio;
DROP TABLE IF EXISTS flac_audio;
DROP TABLE IF EXISTS aac_audio;
DROP TABLE IF EXISTS ogg_audio;

Expected Outcomes

• Multiple audio format tables created
• Format-specific metadata tracked
• Queries filter by format
• Size comparisons reveal efficiency
• Lossless/lossy categorization works

Audio Format Comparison

Key Concepts Learned

• Format-specific AUDIO types
• Multiple format support
• Quality vs size trade-offs
• Format categorization queries
• Storage efficiency analysis