Multi-Type Table

Objective

Create a table demonstrating all standard SQL data types supported by AIDB. Understanding data types is fundamental to effective database design.

Step 1: Create a Comprehensive Table

Create a table with all standard SQL types.

CREATE TABLE data_types_demo (
    id INTEGER PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE,
    small_number SMALLINT,
    regular_number INTEGER,
    big_number BIGINT,
    decimal_value DECIMAL(10, 2),
    float_value REAL,
    double_value DOUBLE,
    fixed_text CHAR(10),
    variable_text VARCHAR(255),
    long_text TEXT,
    binary_data BYTEA,
    unique_id UUID,
    birth_date DATE,
    start_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Sample Data

Insert records demonstrating each data type.

INSERT INTO data_types_demo (
    id, is_active, small_number, regular_number, big_number,
    decimal_value, float_value, double_value,
    fixed_text, variable_text, long_text,
    unique_id, birth_date, start_time
) VALUES (
    1,
    TRUE,
    32767,
    2147483647,
    9223372036854775807,
    12345.67,
    3.14159,
    3.141592653589793,
    'FIXED     ',
    'Variable length string',
    'This is a longer text field that can contain much more content than VARCHAR',
    '550e8400-e29b-41d4-a716-446655440000',
    '1990-05-15',
    '09:30:00'
);

Step 3: Insert Additional Records

Add more sample data with different values.

INSERT INTO data_types_demo (
    id, is_active, small_number, regular_number, big_number,
    decimal_value, float_value, double_value,
    fixed_text, variable_text, long_text,
    unique_id, birth_date, start_time
) VALUES
(
    2, FALSE, -100, -50000, 1000000000000,
    99999.99, 2.71828, 2.718281828459045,
    'ABC       ', 'Short text', 'Another long text entry',
    '6ba7b810-9dad-11d1-80b4-00c04fd430c8',
    '2000-12-25', '14:45:30'
),
(
    3, TRUE, 0, 12345, 987654321,
    0.01, 1.0, 0.123456789012345,
    'XYZ       ', 'Medium length variable text here', 'Long form content for text field',
    '6ba7b811-9dad-11d1-80b4-00c04fd430c8',
    '2023-01-01', '00:00:00'
);

Step 4: Query by Different Types

Demonstrate querying with various data types.

-- Query by boolean
SELECT id, variable_text FROM data_types_demo WHERE is_active = TRUE;

-- Query by numeric comparison
SELECT id, decimal_value FROM data_types_demo WHERE decimal_value > 100.00;

-- Query by date
SELECT id, birth_date FROM data_types_demo WHERE birth_date > '1995-01-01';

-- Query by text pattern
SELECT id, variable_text FROM data_types_demo WHERE variable_text LIKE '%text%';

Step 5: Aggregate Numeric Values

Perform calculations on numeric columns.

SELECT
    COUNT(*) as total_records,
    SUM(decimal_value) as sum_decimal,
    AVG(float_value) as avg_float,
    MAX(big_number) as max_bigint,
    MIN(small_number) as min_smallint
FROM data_types_demo;

Cleanup (Optional)

DROP TABLE IF EXISTS data_types_demo;

Expected Outcomes

• All data types store and retrieve correctly
• Boolean filtering works with TRUE/FALSE
• Numeric precision maintained for DECIMAL
• Date/Time values formatted correctly
• UUID values stored as unique identifiers

Key Concepts Learned

• AIDB supports all standard SQL data types
• Numeric types: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE
• Text types: CHAR, VARCHAR, TEXT
• Date/Time types: DATE, TIME, TIMESTAMP
• Special types: BOOLEAN, UUID, BYTEA
• Type-appropriate comparisons and aggregations