SQLite Guide — Embedded Database for Applications

# Create (or open) a database
sqlite3 inventory.db

# Expected output:
# SQLite version 3.43.2 2026-04-01 12:00:00
# Enter ".help" for usage hints.

-- Inside the sqlite3 prompt, create tables
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    price REAL NOT NULL CHECK(price > 0),
    quantity INTEGER NOT NULL DEFAULT 0,
    category TEXT NOT NULL DEFAULT 'General',
    created_at TEXT DEFAULT (datetime('now'))
);

-- Insert sample products
INSERT INTO products (name, description, price, quantity, category) VALUES
    ('Wireless Mouse', 'Ergonomic wireless mouse with USB receiver', 29.99, 150, 'Electronics'),
    ('Notebook Set', 'Pack of 5 A5 ruled notebooks', 12.50, 300, 'Stationery'),
    ('Desk Lamp', 'LED desk lamp with adjustable brightness', 39.99, 75, 'Office'),
    ('USB-C Cable', '2m braided USB-C charging cable', 9.99, 500, 'Electronics'),
    ('Whiteboard Markers', 'Set of 8 assorted colors', 7.99, 200, 'Office');

-- Verify data
SELECT * FROM products;

-- Products with low stock (less than 100 units)
SELECT name, quantity, price
FROM products
WHERE quantity < 100
ORDER BY quantity;

-- Output:
-- name         | quantity | price
-- Desk Lamp    | 75       | 39.99

-- Total inventory value per category
SELECT
    category,
    COUNT(*) AS product_count,
    SUM(quantity) AS total_units,
    ROUND(SUM(price * quantity), 2) AS total_value
FROM products
GROUP BY category
ORDER BY total_value DESC;

-- Output:
-- category    | product_count | total_units | total_value
-- Electronics | 2             | 650         | 9496.50
-- Office      | 2             | 275         | 2999.25
-- Stationery  | 1             | 300         | 3750.00

-- Search by name (LIKE is case-insensitive by default in SQLite)
SELECT name, price FROM products WHERE name LIKE '%cable%';

-- Output:
-- name         | price
-- USB-C Cable  | 9.99

# Dump entire database to SQL script
sqlite3 inventory.db .dump > inventory_backup.sql

# The output file contains all CREATE TABLE + INSERT statements
# You can restore it anytime:
sqlite3 restored_inventory.db < inventory_backup.sql

# Or from within sqlite3:
sqlite3 inventory.db
sqlite> .output backup.sql
sqlite> .dump
sqlite> .exit

# Restore
sqlite3 inventory.db
sqlite> .read backup.sql

-- Attach another database
ATTACH DATABASE 'users.db' AS users_db;

-- Query across databases
SELECT p.name, u.username
FROM products p
JOIN users_db.users u ON p.product_id = u.favorite_product_id;

-- SQLite has built-in JSON functions (since 3.9.0)
SELECT
    name,
    json_object('price', price, 'in_stock', json(quantity > 0)) AS product_json
FROM products
WHERE category = 'Electronics';

import sqlite3

# Connect to database (creates if not exists)
conn = sqlite3.connect("inventory.db")
cursor = conn.cursor()

# Create a sales table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS sales (
        sale_id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_id INTEGER NOT NULL,
        quantity_sold INTEGER NOT NULL,
        sale_date TEXT DEFAULT (datetime('now')),
        FOREIGN KEY (product_id) REFERENCES products(product_id)
    )
""")

# Record a sale
cursor.execute(
    "INSERT INTO sales (product_id, quantity_sold) VALUES (?, ?)",
    (1, 2)
)

# Query with JOIN
cursor.execute("""
    SELECT
        p.name,
        SUM(s.quantity_sold) AS total_sold,
        ROUND(SUM(s.quantity_sold * p.price), 2) AS revenue
    FROM sales s
    JOIN products p ON s.product_id = p.product_id
    GROUP BY p.product_id
    ORDER BY revenue DESC
""")

for row in cursor.fetchall():
    print(f"{row[0]}: sold {row[1]} units, revenue ${row[2]}")

conn.commit()
conn.close()

-- Enable Write-Ahead Logging for better concurrency
PRAGMA journal_mode=WAL;
-- Output: WAL

-- If product_id is PRIMARY KEY AUTOINCREMENT, this fails:
INSERT INTO products (product_id, name) VALUES (1, 'Duplicate');
-- UNIQUE constraint failed: products.product_id

SELECT * FROM non_existent_table;
-- Error: no such table: non_existent_table

PRAGMA foreign_keys = ON;

CREATE TABLE strict_products (
    id INTEGER PRIMARY KEY,
    price REAL NOT NULL
) STRICT;

INSERT INTO strict_products VALUES (1, 'not_a_number');
-- Error: datatype mismatch

-- Create a virtual FTS table
CREATE VIRTUAL TABLE products_fts USING fts5(name, description, content='products');

-- Populate from the source table
INSERT INTO products_fts SELECT name, description FROM products;

-- Search with ranking
SELECT name, rank
FROM products_fts
WHERE products_fts MATCH 'wireless OR mouse'
ORDER BY rank;

CREATE TABLE notes (
    note_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    category TEXT DEFAULT 'General',
    is_pinned INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE tags (
    tag_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE note_tags (
    note_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (note_id, tag_id),
    FOREIGN KEY (note_id) REFERENCES notes(note_id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE
);

-- Enable foreign keys for this session
PRAGMA foreign_keys = ON;

-- Insert sample data
INSERT INTO notes (title, content, category) VALUES
    ('Meeting Notes', 'Discuss Q3 roadmap and sprint planning', 'Work'),
    ('Shopping List', 'Milk, eggs, bread, coffee, chicken', 'Personal');

INSERT INTO tags (name) VALUES ('important'), ('urgent'), ('reference');

INSERT INTO note_tags (note_id, tag_id) VALUES (1, 1), (1, 2);

-- Query: Get all notes with their tags
SELECT
    n.title,
    n.category,
    GROUP_CONCAT(t.name, ', ') AS tags
FROM notes n
LEFT JOIN note_tags nt ON n.note_id = nt.note_id
LEFT JOIN tags t ON nt.tag_id = t.tag_id
GROUP BY n.note_id
ORDER BY n.updated_at DESC;

-- Products with no sales (use LEFT JOIN)
SELECT p.name, COUNT(s.sale_id) AS times_sold
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id
HAVING times_sold = 0;

-- Average price per category
SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;

-- Database file size
PRAGMA page_count;
PRAGMA page_size;
-- Total = page_count * page_size bytes