-- Create a new database
.open mydatabase.db

-- Create a table
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create an index
CREATE INDEX idx_username ON users(username)

-- Insert data
INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com');

-- Query data
SELECT * FROM users WHERE username LIKE 'john%';

-- Update records
UPDATE users
SET email = 'new.john@example.com'
WHERE username = 'john_doe'

-- View table schema
.schema users

-- List all tables
.tables

-- Table information
PRAGMA table_info(users)

-- Browse with limits
SELECT * FROM users
LIMIT 100;

-- Export results
.mode csv
.output users_export.csv
SELECT * FROM users;
.output

-- Execute multiple statements
BEGIN TRANSACTION;
    CREATE TABLE temp_users AS
    SELECT * FROM users WHERE created_at < DATE('now', '-1 year');
    DELETE FROM users WHERE created_at < DATE('now', '-1 year');
COMMIT

-- Create FTS table
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    content,
    tags
);

-- Search in FTS table
SELECT * FROM articles_fts
WHERE articles_fts MATCH 'sqlite database'

-- Custom aggregate function
CREATE TABLE sales (
    date TEXT,
    amount DECIMAL
);

SELECT
    strftime('%Y-%m', date) as month,
    COUNT(*) as transactions,
    SUM(amount) as total_sales,
    AVG(amount) as avg_sale,
    MIN(amount) as min_sale,
    MAX(amount) as max_sale
FROM sales
GROUP BY month
ORDER BY month DESC

-- Running totals and rankings
SELECT
    date,
    amount,
    SUM(amount) OVER (
        ORDER BY date
    ) as running_total,
    RANK() OVER (
        ORDER BY amount DESC
    ) as amount_rank
FROM

-- Backup database
.backup 'backup.db'

-- Restore from backup
.restore 'backup.db'

-- Analyze database
ANALYZE;

-- Vacuum database
VACUUM;

-- Check integrity

-- Enable WAL mode
PRAGMA journal_mode = WAL;

-- Optimize cache
PRAGMA cache_size = -2000; -- 2MB cache

-- Check indexes
SELECT * FROM sqlite_master WHERE type = 'index'

-- Create settings table
CREATE TABLE app_settings (
    key TEXT PRIMARY KEY,
    value TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Store settings
INSERT INTO app_settings (key, value)
VALUES
('theme', 'dark'),
('language', 'en'),
('notifications', 'enabled')
ON CONFLICT(key) DO UPDATE SET
    value = excluded.value,
    updated_at = CURRENT_TIMESTAMP

-- Time series analysis
WITH RECURSIVE dates(date) AS (
    SELECT DATE('now', '-30 days')
    UNION ALL
    SELECT DATE(date, '+1 day')
    FROM dates
    WHERE date < DATE('now')
)
SELECT
    dates.date,
    COUNT(sales.id) as sales_count,
    COALESCE(SUM(sales.amount), 0) as daily_total
FROM dates
LEFT JOIN sales ON DATE(sales.date) = dates.date
GROUP BY dates.date
ORDER BY