SQL Reference

-- Basic table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  metadata JSONB
);

-- With constraints
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  amount DECIMAL(10,2) CHECK (amount > 0),
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW()
);

-- Distributed table
CREATE TABLE events (
  id BIGSERIAL,
  timestamp TIMESTAMP,
  event_type TEXT,
  data JSONB
) DISTRIBUTED BY HASH(id);

-- B-tree index (default)
CREATE INDEX idx_users_email ON users(email);

-- Partial index
CREATE INDEX idx_active_users 
ON users(username) 
WHERE status = 'active';

-- GIN index for JSONB
CREATE INDEX idx_users_metadata 
ON users USING GIN(metadata);

-- Multi-column index
CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at DESC);

-- Expression index
CREATE INDEX idx_users_lower_email 
ON users(LOWER(email));

-- Add column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Add constraint
ALTER TABLE users 
ADD CONSTRAINT email_format 
CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Rename column
ALTER TABLE users RENAME COLUMN username TO user_name;

-- Basic select
SELECT id, username, email FROM users;

-- With WHERE clause
SELECT * FROM users 
WHERE created_at > NOW() - INTERVAL '30 days';

-- Joins
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.username
HAVING COUNT(o.id) > 5;

-- Subquery
SELECT * FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM orders
  WHERE amount > 1000
);

-- CTE (Common Table Expression)
WITH high_value_users AS (
  SELECT user_id, SUM(amount) as total
  FROM orders
  GROUP BY user_id
  HAVING SUM(amount) > 10000
)
SELECT u.*, hv.total
FROM users u
JOIN high_value_users hv ON u.id = hv.user_id;

-- Single row
INSERT INTO users (username, email)
VALUES ('john_doe', 'john@example.com');

-- Multiple rows
INSERT INTO users (username, email)
VALUES 
  ('alice', 'alice@example.com'),
  ('bob', 'bob@example.com');

-- With RETURNING
INSERT INTO users (username, email)
VALUES ('charlie', 'charlie@example.com')
RETURNING id, created_at;

-- From SELECT
INSERT INTO archive_users
SELECT * FROM users
WHERE last_login < NOW() - INTERVAL '1 year';

-- Basic update
UPDATE users 
SET email = 'newemail@example.com'
WHERE id = 1;

-- Multiple columns
UPDATE users
SET 
  status = 'active',
  last_login = NOW()
WHERE id = 1;

-- With subquery
UPDATE users
SET tier = 'premium'
WHERE id IN (
  SELECT user_id FROM orders
  GROUP BY user_id
  HAVING SUM(amount) > 5000
);

-- With RETURNING
UPDATE users
SET status = 'verified'
WHERE email_verified = true
RETURNING id, username;

-- Basic delete
DELETE FROM users WHERE id = 1;

-- With condition
DELETE FROM sessions
WHERE expires_at < NOW();

-- With RETURNING
DELETE FROM users
WHERE status = 'deleted'
RETURNING id, username;

-- Row number
SELECT 
  username,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) as rank
FROM orders;

-- Partitioned ranking
SELECT 
  user_id,
  created_at,
  amount,
  RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) as user_rank
FROM orders;

-- Moving average
SELECT 
  date,
  value,
  AVG(value) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7d
FROM metrics;

-- Query JSON fields
SELECT 
  id,
  metadata->>'name' as name,
  metadata->'preferences'->>'theme' as theme
FROM users;

-- JSON containment
SELECT * FROM users
WHERE metadata @> '{"status": "premium"}';

-- JSON array operations
SELECT * FROM products
WHERE tags @> '["featured"]';

-- Update JSON field
UPDATE users
SET metadata = jsonb_set(
  metadata,
  '{preferences,theme}',
  '"dark"'
)
WHERE id = 1;

-- Array contains
SELECT * FROM posts
WHERE tags && ARRAY['sql', 'database'];

-- Unnest array
SELECT unnest(tags) as tag
FROM posts;

-- Array aggregation
SELECT 
  category,
  array_agg(title) as titles
FROM posts
GROUP BY category;