Data Types in SQL

CREATE TABLE products (
    product_id INT,              -- Regular ID (up to 2 billion)
    barcode BIGINT,              -- Large numbers (like UPC codes)
    stock_quantity SMALLINT      -- Small numbers (won't exceed 32k)
);

CREATE TABLE financial (
    price DECIMAL(10,2),    -- $99,999,999.99 max, EXACT
    measurement FLOAT       -- Approximate, for science
);

INSERT INTO financial VALUES (19.99, 19.99);
SELECT * FROM financial;

CREATE TABLE users (
    country_code CHAR(2),          -- Always 2 chars: 'US', 'UK'
    username VARCHAR(50),           -- Up to 50 chars
    bio TEXT                        -- Large description
);

INSERT INTO users VALUES ('US', 'alice', 'Software engineer who loves databases');
SELECT * FROM users;

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,                    -- Just the date
    event_time TIME,                    -- Just the time
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Auto-timestamp
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO events (event_id, event_name, event_date, event_time)
VALUES (1, 'Team Meeting', '2024-06-15', '14:30:00');

SELECT * FROM events;

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    in_stock BOOLEAN,           -- TRUE or FALSE
    is_featured BOOL,           -- Same as BOOLEAN
    is_on_sale TINYINT(1)       -- 0 or 1
);

INSERT INTO products VALUES
    (1, 'Laptop', TRUE, FALSE, 1),
    (2, 'Mouse', 1, 0, FALSE);  -- You can use TRUE/FALSE or 1/0

SELECT * FROM products;

SELECT product_name, in_stock
FROM products
WHERE in_stock = TRUE;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,     -- Cannot be NULL
    email VARCHAR(100),              -- Can be NULL
    salary DECIMAL(10,2) NOT NULL,   -- Cannot be NULL
    manager_id INT                   -- Can be NULL (CEO has no manager)
);

INSERT INTO employees (id, name, email, salary, manager_id)
VALUES
    (1, 'Alice', 'alice@company.com', 75000, NULL),  -- No manager (CEO)
    (2, 'Bob', NULL, 65000, 1);                      -- No email provided

SELECT * FROM employees;

CREATE TABLE customers (
    -- Primary key
    customer_id INT PRIMARY KEY AUTO_INCREMENT,

    -- String types
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),

    -- Numeric types
    age SMALLINT,
    account_balance DECIMAL(12,2) DEFAULT 0.00,

    -- Boolean types
    is_active BOOLEAN DEFAULT TRUE,
    email_verified BOOLEAN DEFAULT FALSE,

    -- Date/Time types
    birth_date DATE,
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,

    -- Text types
    address TEXT,
    notes TEXT
);

INSERT INTO customers
    (first_name, last_name, email, phone, age, birth_date)
VALUES
    ('Alice', 'Johnson', 'alice@email.com', '555-1234', 30, '1994-03-15'),
    ('Bob', 'Smith', 'bob@email.com', NULL, 28, '1996-07-22');

SELECT customer_id, first_name, last_name, email, age, is_active
FROM customers;