Data Types & Tables

-- Small integers (-32,768 to 32,767)
SMALLINT

-- Standard integers (-2,147,483,648 to 2,147,483,647)
INT or INTEGER

-- Large integers (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
BIGINT

-- Tiny integers (0 to 255 or -128 to 127)
TINYINT

-- Exact decimal numbers
DECIMAL(precision, scale)  -- DECIMAL(10,2) = 12345678.99
NUMERIC(precision, scale)  -- Same as DECIMAL

-- Floating point (approximate)
FLOAT      -- Single precision
DOUBLE     -- Double precision
REAL       -- Implementation-specific precision

CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2),    -- $99999999.99
    weight FLOAT,           -- 123.456789
    quantity SMALLINT       -- 32767 max
);

-- Fixed length (padded with spaces)
CHAR(n)        -- CHAR(10) always uses 10 characters

-- Variable length (up to n characters)
VARCHAR(n)     -- VARCHAR(255) uses only what's needed

-- Large text objects
TEXT           -- For longer text content
LONGTEXT       -- For very large text (MySQL)
CLOB           -- Character Large Object (Oracle, DB2)

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password_hash CHAR(64),     -- Fixed length hash
    bio TEXT,                   -- Variable length bio
    country_code CHAR(2)        -- Always 2 characters: 'US', 'UK'
);

-- Date only (YYYY-MM-DD)
DATE           -- '2024-03-15'

-- Time only (HH:MM:SS)
TIME           -- '14:30:00'

-- Date and time
DATETIME       -- '2024-03-15 14:30:00'
TIMESTAMP      -- Similar to DATETIME but with timezone awareness

-- Year only
YEAR           -- 2024

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    birth_year YEAR
);

-- Inserting date/time values
INSERT INTO events (event_name, event_date, start_time) 
VALUES ('Conference', '2024-06-15', '09:00:00');

-- Boolean (TRUE/FALSE or 1/0)
BOOLEAN        -- TRUE, FALSE, or NULL
BOOL           -- Alias for BOOLEAN

-- Binary data
BINARY(n)      -- Fixed length binary
VARBINARY(n)   -- Variable length binary
BLOB           -- Binary Large Object

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    table_constraints
);

CREATE TABLE employees (
    -- Primary key with auto increment
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Required text fields
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    
    -- Optional fields with defaults
    phone VARCHAR(20),
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10,2) DEFAULT 50000.00,
    
    -- Boolean with default
    is_active BOOLEAN DEFAULT TRUE,
    
    -- Foreign key reference
    department_id INT,
    
    -- Constraints
    CONSTRAINT fk_department 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id),
        
    CONSTRAINT chk_salary 
        CHECK (salary >= 0)
);

-- Single column primary key
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);

-- Auto-incrementing primary key
CREATE TABLE posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200)
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE,
    
    -- Foreign key constraint
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- NOT NULL: field must have a value
    product_name VARCHAR(100) NOT NULL,
    
    -- UNIQUE: no duplicate values allowed
    sku VARCHAR(50) UNIQUE,
    
    -- DEFAULT: default value if none provided
    price DECIMAL(10,2) DEFAULT 0.00,
    
    -- CHECK: custom validation rule
    price DECIMAL(10,2) CHECK (price >= 0),
    
    -- Multiple constraints
    category VARCHAR(50) NOT NULL DEFAULT 'General'
);

-- Add single column
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20);

-- Add multiple columns
ALTER TABLE users 
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Add column with constraints
ALTER TABLE products 
ADD COLUMN weight DECIMAL(8,2) NOT NULL DEFAULT 0.00;

-- Change data type
ALTER TABLE users 
MODIFY COLUMN phone VARCHAR(25);

-- Change column name and type (MySQL)
ALTER TABLE users 
CHANGE COLUMN phone phone_number VARCHAR(25);

-- Add constraint to existing column
ALTER TABLE users 
ADD CONSTRAINT uk_email UNIQUE (email);

-- Drop column
ALTER TABLE users 
DROP COLUMN phone;

-- Drop constraint
ALTER TABLE users 
DROP CONSTRAINT uk_email;

-- Drop index
ALTER TABLE users 
DROP INDEX idx_username;

-- Create index on single column
CREATE INDEX idx_lastname ON users(last_name);

-- Create composite index
CREATE INDEX idx_name ON users(last_name, first_name);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Drop index
DROP INDEX idx_lastname ON users;

-- Show table structure (MySQL)
DESCRIBE users;
SHOW COLUMNS FROM users;

-- Show create statement
SHOW CREATE TABLE users;

-- List all tables
SHOW TABLES;

-- Drop single table
DROP TABLE users;

-- Drop multiple tables
DROP TABLE users, products, orders;

-- Drop if exists (no error if table doesn't exist)
DROP TABLE IF EXISTS temp_data;

-- Good: Specific size for known limits
username VARCHAR(50)           -- Not VARCHAR(255) if 50 is enough
age TINYINT                   -- Not INT for values 0-120
price DECIMAL(10,2)           -- Exact for money, not FLOAT

-- Good: Use appropriate types
is_active BOOLEAN             -- Not CHAR(1) or INT
created_at TIMESTAMP          -- Not VARCHAR for dates

-- Use descriptive, consistent names
CREATE TABLE user_profiles (    -- table: plural, snake_case
    user_id INT,               -- column: singular, snake_case
    profile_picture_url VARCHAR(255),
    date_of_birth DATE,
    created_at TIMESTAMP
);

-- Prefix constraints clearly
CONSTRAINT pk_user_profiles PRIMARY KEY (user_id),
CONSTRAINT fk_user_profiles_user FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT chk_user_profiles_age CHECK (DATEDIFF(CURDATE(), date_of_birth) >= 0)