SQL Reference

-- MySQL syntax (works in Marmot)
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    balance DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Transpiled to SQLite
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT UNIQUE,
    balance NUMERIC,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Fully supported
SELECT * FROM users;
SELECT id, name, email FROM users WHERE status = 'active';
SELECT DISTINCT category FROM products;
SELECT ALL name FROM users;  -- ALL is default

-- These MySQL hints are parsed but removed during transpilation
SELECT HIGH_PRIORITY * FROM users;           -- → SELECT * FROM users
SELECT SQL_NO_CACHE * FROM users;            -- → SELECT * FROM users
SELECT SQL_CALC_FOUND_ROWS * FROM users;     -- → SELECT * FROM users

-- Aliases
SELECT name AS user_name, email AS contact FROM users;
 
-- Expressions
SELECT id, price * quantity AS total FROM orders;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
 
-- Aggregate functions
SELECT COUNT(*), SUM(amount), AVG(price), MIN(id), MAX(id) FROM orders;
 
-- Scalar subqueries
SELECT id, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count FROM users;

-- ON clause
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;
 
-- USING clause
SELECT * FROM users u JOIN orders o USING(user_id);
SELECT * FROM t1 JOIN t2 USING(col1, col2);
 
-- Multiple conditions
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id AND o.status = 'completed';
 
-- OR in JOIN
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id OR u.email = o.email;

-- Chain of JOINs
SELECT u.name, o.total, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;
 
-- Mixed JOIN types
SELECT u.name, o.total, r.rating
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
INNER JOIN reviews r ON o.id = r.order_id;
 
-- Self JOIN
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Derived table with alias (required)
SELECT * FROM (SELECT id, name FROM users WHERE active = 1) AS active_users;
 
-- JOIN with derived table
SELECT u.name, stats.total_orders
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS total_orders
    FROM orders
    GROUP BY user_id
) AS stats ON u.id = stats.user_id;

-- Supported
SELECT id FROM users UNION SELECT id FROM admins;
SELECT id FROM t1 UNION ALL SELECT id FROM t2;
SELECT id FROM t1 UNION SELECT id FROM t2 UNION SELECT id FROM t3;
 
-- With ORDER BY and LIMIT (applied to final result)
SELECT id FROM users UNION SELECT id FROM admins ORDER BY id LIMIT 10;
 
-- Not supported (use UNION instead)
-- SELECT id FROM t1 UNION DISTINCT SELECT id FROM t2;  -- Error
-- SELECT id FROM t1 INTERSECT SELECT id FROM t2;       -- Error
-- SELECT id FROM t1 EXCEPT SELECT id FROM t2;          -- Error

-- Scalar subqueries
SELECT id, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count FROM users;
SELECT * FROM users WHERE id = (SELECT MAX(user_id) FROM orders);
 
-- IN / NOT IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);
 
-- EXISTS / NOT EXISTS
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
 
-- Correlated subqueries
SELECT * FROM users u WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 5;
SELECT u.name, (SELECT MAX(o.total) FROM orders o WHERE o.user_id = u.id) AS max_order FROM users u;
 
-- Subquery in HAVING
SELECT user_id, COUNT(*) AS cnt FROM orders
GROUP BY user_id
HAVING COUNT(*) > (SELECT AVG(order_count) FROM (SELECT COUNT(*) AS order_count FROM orders GROUP BY user_id) t);

-- Running totals and aggregates
SELECT id, amount,
    SUM(amount) OVER (ORDER BY id) AS running_total,
    AVG(amount) OVER (PARTITION BY category) AS category_avg,
    COUNT(*) OVER (PARTITION BY user_id) AS user_order_count
FROM orders;

-- Single partition column
SELECT id, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rnk FROM products;
 
-- Multiple partition columns
SELECT id, ROW_NUMBER() OVER (PARTITION BY year, month ORDER BY day) AS day_of_month FROM calendar;

-- Supported frame specifications
SELECT id,
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_sum,
    SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS cumulative,
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total
FROM data;

-- Supported: single named window
SELECT id,
    ROW_NUMBER() OVER w AS rn,
    SUM(amount) OVER w AS running_total
FROM orders
WINDOW w AS (ORDER BY created_at);
 
-- Not supported: multiple named windows
-- SELECT id, ROW_NUMBER() OVER w1, SUM(amount) OVER w2
-- FROM orders WINDOW w1 AS (ORDER BY id), w2 AS (PARTITION BY category);

-- Basic CTE
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users;
 
-- CTE with explicit column list
WITH user_stats(user_id, total_orders) AS (
    SELECT user_id, COUNT(*) FROM orders GROUP BY user_id
)
SELECT * FROM user_stats;
 
-- Multiple CTEs
WITH
    a AS (SELECT 1 AS x),
    b AS (SELECT 2 AS y),
    c AS (SELECT 3 AS z)
SELECT * FROM a, b, c;
 
-- CTEs referencing each other
WITH
    first_cte AS (SELECT id FROM users),
    second_cte AS (SELECT id FROM first_cte WHERE id > 10)
SELECT * FROM second_cte;

-- Generate number sequence
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;
 
-- Tree traversal (hierarchical data)
WITH RECURSIVE tree AS (
    -- Anchor: root nodes
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL
 
    UNION ALL
 
    -- Recursive: children
    SELECT c.id, c.name, c.parent_id, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

-- Basic GROUP BY
SELECT category, COUNT(*) FROM products GROUP BY category;
 
-- Multiple columns
SELECT year, month, SUM(sales) FROM revenue GROUP BY year, month;
 
-- With HAVING
SELECT category, COUNT(*) AS cnt FROM products
GROUP BY category
HAVING cnt > 5;
 
-- Multiple aggregates
SELECT category, COUNT(*), SUM(price), AVG(price), MIN(price), MAX(price)
FROM products
GROUP BY category;
 
-- With ORDER BY and LIMIT
SELECT category, COUNT(*) AS cnt FROM products
GROUP BY category
ORDER BY cnt DESC
LIMIT 10;

-- Standard syntax
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;
 
-- MySQL comma syntax (transpiled)
SELECT * FROM users LIMIT 20, 10;
-- Transpiled to: SELECT * FROM users LIMIT 10 OFFSET 20

-- Supported: explicit column list (REQUIRED for CDC)
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
 
-- Supported: multi-row insert
INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
 
-- Supported: INSERT IGNORE (MySQL syntax)
INSERT IGNORE INTO users (id, name) VALUES (1, 'Alice');
 
-- Supported: INSERT OR IGNORE (SQLite syntax - auto-detected)
INSERT OR IGNORE INTO users (id, name) VALUES (1, 'Alice');
 
-- Supported: INSERT OR REPLACE (SQLite upsert - auto-detected)
INSERT OR REPLACE INTO users (id, name, email) VALUES (1, 'Alice', 'alice@new.com');
 
-- NOT supported: no column list
-- INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');  -- Error: CDC requires columns
 
-- NOT supported: INSERT ... SELECT
-- INSERT INTO archive SELECT * FROM users WHERE status = 'inactive';  -- Error
 
-- NOT supported: ON DUPLICATE KEY UPDATE
-- INSERT INTO users (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE name = 'Alice';  -- Error

-- Supported: explicit column list
REPLACE INTO users (id, name) VALUES (1, 'Alice');
 
-- NOT supported: no column list
-- REPLACE INTO users VALUES (1, 'Alice');  -- Error

-- Supported: simple UPDATE with WHERE
UPDATE users SET name = 'Bob' WHERE id = 1;
 
-- Supported: multiple columns
UPDATE users SET name = 'Bob', email = 'bob@example.com', updated_at = NOW() WHERE id = 1;
 
-- Supported: expression in SET
UPDATE products SET stock = stock - 1 WHERE id = 100;
 
-- NOT supported: no WHERE clause
-- UPDATE users SET status = 'inactive';  -- Error: unsafe multi-row update
 
-- NOT supported: subquery in WHERE
-- UPDATE users SET status = 'premium' WHERE id IN (SELECT user_id FROM orders);  -- Error

-- Supported: simple DELETE with WHERE
DELETE FROM users WHERE id = 1;
 
-- NOT supported: no WHERE clause
-- DELETE FROM temp_table;  -- Error: unsafe multi-row delete
 
-- NOT supported: subquery in WHERE
-- DELETE FROM users WHERE id IN (SELECT user_id FROM banned_users);  -- Error
 
-- Use TRUNCATE for deleting all rows (DDL operation)
TRUNCATE TABLE temp_table;

-- Basic table
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE
);
 
-- With IF NOT EXISTS
CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY);
 
-- With constraints
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user (user_id)
);

ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users MODIFY COLUMN name VARCHAR(200);
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users RENAME TO customers;

DROP TABLE users;
DROP TABLE IF EXISTS users;
DROP TABLE users, orders, products;  -- Multiple tables

CREATE INDEX idx_name ON users (name);
CREATE UNIQUE INDEX idx_email ON users (email);
DROP INDEX idx_name ON users;

CREATE DATABASE mydb;
CREATE DATABASE IF NOT EXISTS mydb;
DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;

TRUNCATE TABLE logs;
RENAME TABLE users TO customers;

-- Supported
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
 
-- NOT supported
-- SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;
-- SELECT * FROM queue WHERE processed = 0 FOR UPDATE SKIP LOCKED LIMIT 10;

-- Parsed and removed (SQLite doesn't use hints)
SELECT * FROM users USE INDEX (idx_name) WHERE name = 'Alice';
SELECT * FROM users FORCE INDEX (idx_name) WHERE name LIKE 'A%';
SELECT * FROM users IGNORE INDEX (idx_name) WHERE name = 'Alice';

SELECT @@version;           -- Returns Marmot version
SELECT @@sql_mode;          -- Returns empty string
SELECT @@autocommit;        -- Returns 1
SELECT DATABASE();          -- Returns current database name
SELECT SCHEMA();            -- Alias for DATABASE()

SELECT @@version AS ver, @@sql_mode AS mode;
SELECT DATABASE() AS db, @@autocommit AS auto;

SELECT * FROM MARMOT_CLUSTER_NODES;
SELECT * FROM MARMOT.CLUSTER_NODES;
SELECT * FROM marmot_cluster_nodes;  -- Case-insensitive
 
-- Returns columns:
-- node_id, address, state, last_seen

-- List tables
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mydb';
 
-- List columns
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users';
 
-- List databases
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
 
-- List indexes
SELECT INDEX_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users';