CREATE TABLE employees (
    id SERIAL PRIMARY KEY,                          -- Auto-increment PK (PostgreSQL)
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    department_id INTEGER REFERENCES departments(id),
    salary NUMERIC(10, 2) DEFAULT 0.00,
    level VARCHAR(20) CHECK (level IN ('junior', 'mid', 'senior', 'lead')),
    is_active BOOLEAN DEFAULT TRUE,
    hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- MySQL equivalent
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2) DEFAULT 0.00,
    level ENUM('junior', 'mid', 'senior', 'lead'),
    is_active BOOLEAN DEFAULT TRUE,
    hire_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    
    CONSTRAINT chk_amount_positive 
        CHECK (amount > 0),
    
    CONSTRAINT chk_valid_status 
        CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'))
);

-- If a department is deleted, set employees' department_id to NULL
FOREIGN KEY (department_id) REFERENCES departments(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE

-- Add column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees ADD COLUMN bonus NUMERIC(10,2) DEFAULT 0;

-- Remove column
ALTER TABLE employees DROP COLUMN phone;

-- Rename column
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- Change data type
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12, 2);

-- Add/drop constraint
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);
ALTER TABLE employees DROP CONSTRAINT chk_salary;

-- Add NOT NULL
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;

-- Drop NOT NULL
ALTER TABLE employees ALTER COLUMN phone DROP NOT NULL;

-- Add unique constraint
ALTER TABLE employees ADD CONSTRAINT uq_email UNIQUE (email);

-- Add index
CREATE INDEX idx_employees_department ON employees(department_id);
CREATE UNIQUE INDEX idx_employees_email ON employees(email);

-- Rename table
ALTER TABLE employees RENAME TO team_members;

-- Drop table (permanently removes table + data)
DROP TABLE employees;
DROP TABLE IF EXISTS employees;              -- No error if doesn't exist
DROP TABLE employees CASCADE;                -- Also drop dependent objects (dangerous!)

-- Truncate (delete all data, keep structure)
TRUNCATE TABLE employees;
TRUNCATE TABLE employees RESTART IDENTITY;   -- Reset auto-increment
TRUNCATE TABLE orders CASCADE;               -- Also truncate referencing tables

-- Create view
CREATE VIEW employee_directory AS
SELECT
    e.id,
    e.name,
    e.email,
    d.name AS department,
    e.hire_date
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.is_active = TRUE;

-- Use like a table
SELECT * FROM employee_directory WHERE department = 'Engineering';

-- Drop
DROP VIEW employee_directory;

CREATE MATERIALIZED VIEW monthly_stats AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Refresh when data changes
REFRESH MATERIALIZED VIEW monthly_stats;
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats;  -- No lock

-- PostgreSQL: SERIAL (legacy) or IDENTITY (modern)
CREATE TABLE products (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);

-- Manual sequence control
CREATE SEQUENCE order_number_seq START WITH 1000 INCREMENT BY 1;
SELECT nextval('order_number_seq');

-- UUID as primary key
CREATE TABLE events (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB
);

-- Session-scoped (dropped when connection closes)
CREATE TEMPORARY TABLE temp_results (
    id INTEGER,
    score NUMERIC
);

-- Use for complex multi-step queries
CREATE TEMP TABLE active_users AS
SELECT id, name FROM users WHERE last_login > NOW() - INTERVAL '30 days';

SELECT * FROM active_users;
-- Automatically gone when session ends