Cube Database - SQL Quick Reference

Data Definition Language (DDL)

CREATE TABLE

CREATE TABLE keyspace.table (
    column1 TYPE PRIMARY KEY,
    column2 TYPE,
    column3 TYPE
);

Example:

CREATE TABLE users.profiles (
    id TEXT PRIMARY KEY,
    name TEXT,
    email TEXT,
    age TEXT
);

DROP TABLE

DROP TABLE keyspace.table;

DESCRIBE

DESCRIBE keyspace.table;
DESC keyspace.table;  -- Short form

SHOW TABLES

SHOW TABLES;

Data Manipulation Language (DML)

SELECT

-- All columns
SELECT * FROM keyspace.table WHERE id = 'value';

-- Specific columns
SELECT col1, col2, col3 FROM keyspace.table WHERE id = 'value';

-- Multiple conditions
SELECT * FROM keyspace.table WHERE id = 'value' AND status = 'active';

INSERT

INSERT INTO keyspace.table (col1, col2, col3) 
VALUES ('val1', 'val2', 'val3');

UPDATE

-- Single column
UPDATE keyspace.table SET col1 = 'new_value' WHERE id = 'value';

-- Multiple columns
UPDATE keyspace.table 
SET col1 = 'val1', col2 = 'val2' 
WHERE id = 'value';

DELETE

DELETE FROM keyspace.table WHERE id = 'value';

REST API

Execute Single Statement

curl -X POST http://localhost:8080/api/v1/sql/execute \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM users WHERE id = '\''user-1'\''"}'

Execute Batch

curl -X POST http://localhost:8080/api/v1/sql/batch \
  -H "Content-Type: application/json" \
  -d '{
    "statements": [
      "INSERT INTO users (id, name) VALUES ('\''1'\'', '\''Alice'\'')",
      "INSERT INTO users (id, name) VALUES ('\''2'\'', '\''Bob'\'')"
    ]
  }'

Common Patterns

User CRUD

-- Create
CREATE TABLE app.users (id TEXT PRIMARY KEY, username TEXT, email TEXT);

-- Insert
INSERT INTO app.users (id, username, email) 
VALUES ('u1', 'alice', 'alice@example.com');

-- Read
SELECT * FROM app.users WHERE id = 'u1';

-- Update
UPDATE app.users SET email = 'new@example.com' WHERE id = 'u1';

-- Delete
DELETE FROM app.users WHERE id = 'u1';

Product Management

-- Create catalog
CREATE TABLE shop.products (
    sku TEXT PRIMARY KEY,
    name TEXT,
    price TEXT,
    stock TEXT
);

-- Add product
INSERT INTO shop.products (sku, name, price, stock) 
VALUES ('SKU-001', 'Widget', '19.99', '100');

-- Update price
UPDATE shop.products SET price = '17.99' WHERE sku = 'SKU-001';

-- Update stock
UPDATE shop.products SET stock = '95' WHERE sku = 'SKU-001';

Session Management

-- Create sessions
CREATE TABLE auth.sessions (
    session_id TEXT PRIMARY KEY,
    user_id TEXT,
    expires_at TEXT
);

-- Create session
INSERT INTO auth.sessions (session_id, user_id, expires_at) 
VALUES ('sess-123', 'user-1', '2024-12-31');

-- Check session
SELECT * FROM auth.sessions WHERE session_id = 'sess-123';

-- Delete expired
DELETE FROM auth.sessions WHERE session_id = 'sess-123';

Supported Data Types

• TEXT - String data (default/recommended)
• INTEGER - Integer numbers (stored as TEXT)
• FLOAT - Floating point (stored as TEXT)
• BOOLEAN - true/false (stored as TEXT)

Syntax Rules

1. Keywords - Case insensitive (SELECT = select)
2. Strings - Use single quotes: 'value' or double quotes: "value"
3. Semicolons - Optional but recommended: SELECT * FROM users;
4. Keyspace - Optional, defaults to default: users.profiles or just profiles
5. WHERE clause - Required for SELECT, UPDATE, DELETE
6. Primary Key - First column or specify PRIMARY KEY

Common Mistakes

❌ Forgot WHERE clause

SELECT * FROM users  -- Missing WHERE

✅ Always use WHERE for queries

SELECT * FROM users WHERE id = 'user-1'

❌ Unquoted values

SELECT * FROM users WHERE id = user-1  -- No quotes

✅ Quote string values

SELECT * FROM users WHERE id = 'user-1'

❌ Wrong column count

INSERT INTO users (id, name) VALUES ('1', 'Alice', 'alice@example.com')
-- 2 columns, 3 values

✅ Match columns and values

INSERT INTO users (id, name, email) VALUES ('1', 'Alice', 'alice@example.com')

Migration Cheatsheet

From MySQL/PostgreSQL

-- MySQL
SELECT * FROM users WHERE id = 1;

-- Cube (quote values)
SELECT * FROM users WHERE id = '1';

From Cassandra

-- Cassandra CQL (works as-is)
SELECT * FROM users.profiles WHERE id = 'user-1';

-- Cube SQL (identical)
SELECT * FROM users.profiles WHERE id = 'user-1';

Performance Tips

1. ✅ Always use WHERE with primary key
2. ✅ Batch related operations
3. ✅ Use specific column selection when possible
4. ✅ Keep keyspace.table naming consistent

Quick Examples

-- Simple CRUD
CREATE TABLE test.data (id TEXT PRIMARY KEY, value TEXT);
INSERT INTO test.data (id, value) VALUES ('1', 'hello');
SELECT * FROM test.data WHERE id = '1';
UPDATE test.data SET value = 'world' WHERE id = '1';
DELETE FROM test.data WHERE id = '1';

-- With multiple columns
CREATE TABLE test.users (id TEXT PRIMARY KEY, name TEXT, email TEXT, age TEXT);
INSERT INTO test.users (id, name, email, age) VALUES ('u1', 'Alice', 'alice@example.com', '30');
SELECT name, email FROM test.users WHERE id = 'u1';
UPDATE test.users SET name = 'Alice J.', age = '31' WHERE id = 'u1';

-- Batch operations
INSERT INTO test.users (id, name, email) VALUES ('u2', 'Bob', 'bob@example.com');
INSERT INTO test.users (id, name, email) VALUES ('u3', 'Charlie', 'charlie@example.com');
UPDATE test.users SET email = 'bob.new@example.com' WHERE id = 'u2';
DELETE FROM test.users WHERE id = 'u3';

Need Help?

• Full Guide: SQL_GUIDE.md
• API Docs: README.md
• Examples: Run mvn exec:java -Dexec.mainClass="com.cube.examples.SQLExamples"
• Tests: ./test-sql-api.sh

Cube Database - Now with SQL Support! 🎉