example.sql

---
-- @name CreateUsersTable
-- @desc Create the users table with id, username, email, and active flag
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE
);
---

---
-- @name CreateOrdersTable
-- @desc Create the orders table with a foreign key to users
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    total_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

---
-- @name InsertSampleUsers
-- @desc Seed the users table with four sample rows
INSERT INTO users (username, email) VALUES
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com'),
    ('diana', 'diana@example.com');

---
-- @name InsertSampleOrders
-- @desc Seed the orders table with a mix of completed, pending, and cancelled orders
INSERT INTO orders (user_id, total_amount, status) VALUES
    (1, 29.99, 'completed'),
    (1, 15.50, 'completed'),
    (2, 450.00, 'pending'),
    (2, 89.99, 'completed'),
    (3, 1200.00, 'completed'),
    (4, 25.00, 'cancelled');

---
-- @name GetAllUsers
-- @desc List every user, oldest first
SELECT * FROM users ORDER BY created_at;

---
-- @name GetActiveUsers
-- @desc List active users sorted by username
SELECT id, username, email
FROM users 
WHERE active = TRUE 
ORDER BY username;

---
-- @name GetLargeOrders
-- @desc Orders over $100, joined with the owning user, highest first
SELECT o.id, u.username, o.total_amount, o.status
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.total_amount > 100
ORDER BY o.total_amount DESC;

---
-- @name GetUserOrderSummary
-- @desc Per-user order count, total spend, and average order value
SELECT
    u.username,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent,
    COALESCE(AVG(o.total_amount), 0) as avg_order_value
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

---
-- @name GetRecentOrders
-- @desc Orders placed in the last 7 days, newest first
SELECT
    o.id,
    u.username,
    o.total_amount,
    o.status,
    o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > DATE('now', '-7 days')
ORDER BY o.created_at DESC;

---
-- @name CountOrdersByStatus
-- @desc Count orders grouped by status, largest bucket first
SELECT status, COUNT(*) as count
FROM orders 
GROUP BY status
ORDER BY count DESC;

---
-- @name CleanupTestData
-- @desc Drop the orders and users tables — destructive, used to reset the example
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;

---
-- @name QueryWithVariables
-- @desc Demo of @variable interpolation — selects orders for a given user/status with a row limit
SELECT *
FROM orders o, users u
WHERE u.id=@user_id 
AND u.active=@active
AND o.status=@status
AND o.user_id=u.id
LIMIT @lim;

-- Variables 

SET @user_id=2;
SET @lim=10;
SET @active=true;
SET @status="completed";