-- Basic table creation
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    author_id INTEGER,
    published BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

-- Table with constraints
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    category_id INTEGER,
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Multiple table joins
SELECT 
    p.id,
    p.title,
    p.content,
    u.name as author_name,
    c.name as category_name,
    COUNT(com.id) as comment_count
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN comments com ON p.id = com.post_id
WHERE p.published = 1
GROUP BY p.id, p.title, p.content, u.name, c.name
ORDER BY p.created_at DESC;

-- Self-join example
SELECT 
    e.name as employee_name,
    m.name as manager_name,
    e.department
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.department, e.name;

-- Subquery in WHERE clause
SELECT * FROM users 
WHERE id IN (
    SELECT DISTINCT author_id 
    FROM posts 
    WHERE published = 1
);

-- Subquery in SELECT clause
SELECT 
    name,
    email,
    (SELECT COUNT(*) FROM posts WHERE author_id = users.id) as post_count
FROM users;

-- Correlated subquery
SELECT 
    p.title,
    p.created_at,
    (SELECT name FROM users WHERE id = p.author_id) as author_name
FROM posts p
WHERE p.created_at > (
    SELECT AVG(created_at) 
    FROM posts 
    WHERE author_id = p.author_id
);

-- Row numbering
SELECT 
    name,
    email,
    ROW_NUMBER() OVER (ORDER BY created_at) as row_num
FROM users;

-- Ranking
SELECT 
    name,
    age,
    RANK() OVER (ORDER BY age DESC) as age_rank,
    DENSE_RANK() OVER (ORDER BY age DESC) as dense_age_rank
FROM users;

-- Partitioned ranking
SELECT 
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

-- Running totals
SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- Numeric types
CREATE TABLE numeric_examples (
    id INTEGER PRIMARY KEY,
    small_int SMALLINT,
    big_int BIGINT,
    real_num REAL,
    decimal_num DECIMAL(10,2),
    numeric_num NUMERIC(8,4)
);

-- Text types
CREATE TABLE text_examples (
    id INTEGER PRIMARY KEY,
    short_text VARCHAR(50),
    long_text TEXT,
    char_text CHAR(10),
    clob_text CLOB
);

-- Date and time types
CREATE TABLE datetime_examples (
    id INTEGER PRIMARY KEY,
    date_col DATE,
    time_col TIME,
    datetime_col DATETIME,
    timestamp_col TIMESTAMP
);

-- Binary types
CREATE TABLE binary_examples (
    id INTEGER PRIMARY KEY,
    blob_data BLOB,
    binary_data BINARY(16)
);

-- JSON type
CREATE TABLE json_examples (
    id INTEGER PRIMARY KEY,
    json_data JSON,
    jsonb_data JSONB
);

-- Primary key constraint
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- Foreign key constraint
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Check constraint
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0),
    status TEXT CHECK (status IN ('active', 'inactive', 'discontinued'))
);

-- Unique constraint
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    username TEXT UNIQUE NOT NULL
);

-- Default values
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    published BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_posts_author_date ON posts(author_id, created_at);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Covering index
CREATE INDEX idx_posts_covering ON posts(author_id, published, created_at) 
INCLUDE (title, content);