-- Create simple table
-- Status values: 0=deleted, 1=inactive, 2=active, >=2 activate and different use cases
CREATE TABLE `students` (
    `id` int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `status` tinyint(1) NOT NULL DEFAULT 2,
    `registered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated` timestamp NULL DEFAULT NULL,
    `first_name` varchar(255) NOT NULL,
    `last_name` varchar(255) NOT NULL,
    `description` text
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Create another simple table
-- Status values: 0=deleted, 1=inactive, 2=active, >=2 activate and different use cases
CREATE TABLE `courses` (
    `id` int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `status` tinyint(1) NOT NULL DEFAULT 2,
    `code` VARCHAR(32) NOT NULL DEFAULT '',
    `title` VARCHAR(255) NOT NULL,
    `description` text
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Create ManyToMany connection between the tables
CREATE TABLE `course_membership` (
`id_student` INT UNSIGNED NOT NULL,
`id_course` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id_student`, `id_course`),
CONSTRAINT `constr_course_membership_student_fk`
    FOREIGN KEY `student_fk` (`id_student`) REFERENCES `students` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `constr_course_membership_course_fk`
    FOREIGN KEY `course_fk` (`id_course`) REFERENCES `courses` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Create a test database
CREATE DATABASE IF NOT EXISTS all_types_demo;
USE all_types_demo;
-- Drop the table if it exists
DROP TABLE IF EXISTS all_data_types;
-- Create a table with all MySQL 8.0 data types
CREATE TABLE all_data_types (
    -- Numeric Types
    id                INT AUTO_INCREMENT PRIMARY KEY,
    tiny_int_col      TINYINT,                    -- 1 byte signed integer (-128 to 127)
    small_int_col     SMALLINT,                   -- 2 bytes
    medium_int_col    MEDIUMINT,                  -- 3 bytes
    int_col           INT,                        -- 4 bytes
    big_int_col       BIGINT,                     -- 8 bytes
    decimal_col       DECIMAL(10,2),              -- Exact fixed-point number
    numeric_col       NUMERIC(8,3),               -- Alias for DECIMAL
    float_col         FLOAT(7,4),                 -- Approximate single precision
    double_col        DOUBLE(16,8),               -- Approximate double precision
    real_col          REAL,                       -- Alias for DOUBLE
    bit_col           BIT(8),                     -- Bit field
    -- Date and Time Types
    date_col          DATE,                       -- 'YYYY-MM-DD'
    datetime_col      DATETIME(6),                -- 'YYYY-MM-DD HH:MM:SS[.fraction]'
    timestamp_col     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
    time_col          TIME(3),                    -- 'HH:MM:SS[.fraction]'
    year_col          YEAR,                       -- 'YYYY'
    -- String Types
    char_col          CHAR(10),                   -- Fixed-length string (0–255)
    varchar_col       VARCHAR(255),               -- Variable-length string
    binary_col        BINARY(16),                 -- Fixed-length binary
    varbinary_col     VARBINARY(255),             -- Variable-length binary
    tiny_blob_col     TINYBLOB,                   -- Binary large object (max 255 bytes)
    blob_col          BLOB,                       -- Max 65,535 bytes
    medium_blob_col   MEDIUMBLOB,                 -- Max 16,777,215 bytes
    long_blob_col     LONGBLOB,                   -- Max 4GB
    tiny_text_col     TINYTEXT,                   -- Max 255 chars
    text_col          TEXT,                       -- Max 65,535 chars
    medium_text_col   MEDIUMTEXT,                 -- Max 16,777,215 chars
    long_text_col     LONGTEXT,                   -- Max 4GB
    enum_col          ENUM('small','medium','large'),
    set_col           SET('a','b','c','d'),       -- Multiple values
    -- JSON Type
    json_col          JSON,                       -- Native JSON storage
    -- Spatial/Geometry Types (require spatial support)
    geom_col          GEOMETRY,                   -- Any geometry
    point_col         POINT,                      -- Single coordinate pair
    linestring_col    LINESTRING,
    polygon_col       POLYGON
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
-- INSERT
INSERT INTO all_data_types (
    tiny_int_col, small_int_col, medium_int_col, int_col, big_int_col,
    decimal_col, numeric_col, float_col, double_col, real_col, bit_col,
    date_col, datetime_col, time_col, year_col,
    char_col, varchar_col, binary_col, varbinary_col,
    tiny_blob_col, blob_col, medium_blob_col, long_blob_col,
    tiny_text_col, text_col, medium_text_col, long_text_col,
    enum_col, set_col, json_col, geom_col, point_col, linestring_col, polygon_col
) VALUES (
    1, 32000, 8000000, 2147483647, 9223372036854775807,
    12345.67, 9876.543, 123.4567, 987654.12345678, 111.11, b'10101010',
    '2025-09-27', '2025-09-27 12:34:56.123456', '12:34:56.789', 2025,
    'fixed', 'variable string', x'00112233445566778899AABBCCDDEEFF', x'DEADBEEF',
    'a', 'binary blob', 'medium blob', 'long blob',
    'short text', 'normal text', 'medium text', 'long text',
    'medium', 'a,b,c', '{"key":"value"}',
    ST_GeomFromText('POINT(1 1)'), ST_GeomFromText('POINT(1 1)'),
    ST_GeomFromText('LINESTRING(0 0,1 1,2 2)'),
    ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))')
);