SQL 치트시트

-- Select specific columns
SELECT name, email, created_at
FROM users
WHERE active = true;

-- Select all columns
SELECT * FROM products;

SELECT * FROM orders
WHERE status = 'shipped'
  AND total > 100
  AND created_at >= '2025-01-01';

-- OR condition
SELECT * FROM products
WHERE category = 'electronics' OR category = 'books';

-- Sort descending, get top 10
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 10;

-- Pagination with OFFSET
SELECT * FROM articles
ORDER BY published_at DESC
LIMIT 20 OFFSET 40;  -- page 3 (0-indexed)

-- Unique categories
SELECT DISTINCT category FROM products;

-- Distinct on multiple columns
SELECT DISTINCT country, city FROM customers;

-- PostgreSQL: DISTINCT ON (first row per group)
SELECT DISTINCT ON (user_id) user_id, score, created_at
FROM scores
ORDER BY user_id, created_at DESC;

-- Range
SELECT * FROM orders
WHERE total BETWEEN 100 AND 500;

-- List of values
SELECT * FROM users
WHERE role IN ('admin', 'editor', 'moderator');

-- Pattern matching
SELECT * FROM products
WHERE name LIKE '%phone%';        -- contains "phone"
SELECT * FROM users
WHERE email LIKE '%@gmail.com';   -- ends with @gmail.com
SELECT * FROM products
WHERE name ILIKE '%Phone%';       -- case-insensitive (PostgreSQL)

-- Find records with missing data
SELECT * FROM users WHERE phone IS NULL;

-- Find records with data present
SELECT * FROM orders WHERE shipped_at IS NOT NULL;

-- COALESCE: provide a default for NULL
SELECT name, COALESCE(nickname, name) AS display_name
FROM users;

SELECT
  first_name || ' ' || last_name AS full_name,
  price * quantity AS line_total,
  ROUND(price * 0.9, 2) AS discounted_price,
  EXTRACT(YEAR FROM created_at) AS signup_year
FROM orders;

SELECT u.name, o.total, o.created_at
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.total > 50;

-- All users, even those without orders
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- RIGHT JOIN: all orders, even without matching users
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

-- FULL JOIN: all rows from both tables
SELECT u.name, o.total
FROM users u
FULL JOIN orders o ON o.user_id = u.id;

-- Generate all size-color combinations
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

-- Equivalent syntax
SELECT s.size, c.color
FROM sizes s, colors c;

-- Find employees and their managers
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find users in the same city
SELECT a.name, b.name, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;

SELECT
  c.name AS customer,
  p.name AS product,
  oi.quantity,
  o.created_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= NOW() - INTERVAL '30 days';

-- Only join active subscriptions
SELECT u.name, s.plan, s.expires_at
FROM users u
LEFT JOIN subscriptions s
  ON s.user_id = u.id
  AND s.status = 'active'
  AND s.expires_at > NOW();

SELECT
  COUNT(*) AS total_orders,
  COUNT(DISTINCT user_id) AS unique_customers,
  SUM(total) AS revenue,
  AVG(total) AS avg_order_value,
  ROUND(AVG(total), 2) AS avg_rounded
FROM orders
WHERE created_at >= '2025-01-01';

SELECT
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  MAX(created_at) AS latest_order
FROM products;

-- Per-category min/max
SELECT category, MIN(price), MAX(price)
FROM products
GROUP BY category;

-- Revenue by month
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS orders,
  SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Multiple group columns
SELECT country, city, COUNT(*) AS user_count
FROM users
GROUP BY country, city
ORDER BY user_count DESC;

-- Categories with more than 10 products
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY product_count DESC;

-- Customers who spent more than $1000
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000;

-- Rank products by price within each category
SELECT
  name,
  category,
  price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rnk
FROM products;

-- Get the top 3 per category
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
) sub WHERE rn <= 3;

-- Running total of revenue
SELECT
  created_at::date AS day,
  total,
  SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;

-- 7-day moving average
SELECT
  day,
  revenue,
  AVG(revenue) OVER (
    ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_revenue;

SELECT
  COALESCE(country, 'TOTAL') AS country,
  COALESCE(city, 'Subtotal') AS city,
  COUNT(*) AS users,
  SUM(revenue) AS total_revenue
FROM user_revenue
GROUP BY ROLLUP(country, city)
ORDER BY country, city;

-- Single row
INSERT INTO users (name, email, role)
VALUES ('Alice', 'alice@example.com', 'admin');

-- Multiple rows
INSERT INTO products (name, price, category) VALUES
  ('Keyboard', 79.99, 'electronics'),
  ('Mouse', 29.99, 'electronics'),
  ('Desk Lamp', 45.00, 'office');

-- Update specific rows
UPDATE users
SET role = 'admin', updated_at = NOW()
WHERE email = 'alice@example.com';

-- Update with expression
UPDATE products
SET price = price * 1.1
WHERE category = 'electronics';

-- Update from another table
UPDATE orders o
SET status = 'archived'
FROM users u
WHERE o.user_id = u.id AND u.deleted_at IS NOT NULL;

-- Delete specific rows
DELETE FROM sessions
WHERE expires_at < NOW();

-- Delete with subquery
DELETE FROM notifications
WHERE user_id IN (
  SELECT id FROM users WHERE deleted_at IS NOT NULL
);

-- Insert or update on unique constraint
INSERT INTO user_settings (user_id, key, value)
VALUES (1, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = NOW();

-- Insert or do nothing
INSERT INTO page_views (user_id, page, viewed_at)
VALUES (1, '/home', NOW())
ON CONFLICT DO NOTHING;

-- Get the inserted row back
INSERT INTO users (name, email)
VALUES ('Bob', 'bob@example.com')
RETURNING id, name, created_at;

-- Get updated rows
UPDATE orders SET status = 'shipped'
WHERE status = 'processing' AND packed_at < NOW() - INTERVAL '1 day'
RETURNING id, user_id, total;

-- Get deleted rows
DELETE FROM sessions WHERE expires_at < NOW()
RETURNING id, user_id;

-- Copy data between tables
INSERT INTO archived_orders (id, user_id, total, created_at)
SELECT id, user_id, total, created_at
FROM orders
WHERE created_at < '2024-01-01';

-- Create summary table
INSERT INTO daily_stats (day, orders, revenue)
SELECT
  created_at::date,
  COUNT(*),
  SUM(total)
FROM orders
GROUP BY created_at::date;

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  role VARCHAR(20) DEFAULT 'user',
  active BOOLEAN DEFAULT true,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add column with default (safe in PostgreSQL 11+)
ALTER TABLE users ADD COLUMN is_verified BOOLEAN DEFAULT false;

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Change column type
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12, 2);

-- Drop table (structure + data gone)
DROP TABLE IF EXISTS temp_imports;

-- Drop with cascade (removes dependent objects)
DROP TABLE IF EXISTS orders CASCADE;

-- Truncate: delete all rows but keep structure
TRUNCATE TABLE logs;

-- Truncate with cascade + restart sequences
TRUNCATE TABLE orders, order_items RESTART IDENTITY CASCADE;

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

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

-- Partial index (only index active users)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Concurrent (no table lock, production-safe)
CREATE INDEX CONCURRENTLY idx_products_name ON products(name);

CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INT NOT NULL CHECK (quantity > 0),
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  UNIQUE (order_id, product_id)
);

-- Add constraint to existing table
ALTER TABLE users ADD CONSTRAINT chk_role
CHECK (role IN ('admin', 'editor', 'viewer', 'user'));

-- Create temp table (auto-dropped at session end)
CREATE TEMP TABLE tmp_import (
  raw_name TEXT,
  raw_email TEXT,
  processed BOOLEAN DEFAULT false
);

-- Create from query
CREATE TEMP TABLE active_users AS
SELECT id, name, email FROM users WHERE active = true;

-- Scalar subquery
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- IN subquery
SELECT * FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM orders
  WHERE total > 500
);

-- Users who have placed at least one order
SELECT u.name, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Users with NO orders (anti-pattern)
SELECT u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Top spending users
SELECT sub.name, sub.total_spent
FROM (
  SELECT u.name, SUM(o.total) AS total_spent
  FROM users u
  JOIN orders o ON o.user_id = u.id
  GROUP BY u.id, u.name
) sub
WHERE sub.total_spent > 1000
ORDER BY sub.total_spent DESC;

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
  FROM orders
  GROUP BY 1
),
monthly_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND(
      (revenue - LAG(revenue) OVER (ORDER BY month))
      / LAG(revenue) OVER (ORDER BY month) * 100, 1
    ) AS growth_pct
  FROM monthly_revenue
)
SELECT * FROM monthly_growth ORDER BY month;

-- Organization hierarchy (employee -> manager tree)
WITH RECURSIVE org_tree AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: reports
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON t.id = e.manager_id
)
SELECT * FROM org_tree ORDER BY depth, name;

-- Each user's latest order
SELECT u.name,
  (SELECT MAX(o.created_at) FROM orders o WHERE o.user_id = u.id) AS last_order
FROM users u;

-- Products priced above their category average
SELECT p.name, p.price, p.category
FROM products p
WHERE p.price > (
  SELECT AVG(p2.price) FROM products p2 WHERE p2.category = p.category
);

-- First non-NULL value
SELECT COALESCE(nickname, username, 'Anonymous') AS display_name
FROM users;

-- Avoid division by zero
SELECT total / NULLIF(quantity, 0) AS unit_price
FROM order_items;

-- Default for missing JSON field
SELECT COALESCE(metadata->>'theme', 'light') AS theme
FROM user_settings;

SELECT name, price,
  CASE
    WHEN price >= 100 THEN 'premium'
    WHEN price >= 50 THEN 'standard'
    ELSE 'budget'
  END AS tier
FROM products;

-- In aggregation
SELECT
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count
FROM users;

-- Standard SQL CAST
SELECT CAST('42' AS INTEGER);
SELECT CAST(price AS TEXT) FROM products;

-- PostgreSQL shorthand ::
SELECT '2025-01-15'::DATE;
SELECT total::NUMERIC(10,2) FROM orders;
SELECT created_at::DATE AS day FROM orders;
SELECT '{"key":"val"}'::JSONB;

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  UPPER(email) AS email_upper,
  LOWER(name) AS name_lower,
  LENGTH(description) AS desc_length,
  TRIM('  hello  ') AS trimmed,
  SUBSTRING(phone FROM 1 FOR 3) AS area_code,
  REPLACE(url, 'http://', 'https://') AS secure_url,
  LEFT(title, 50) || '...' AS truncated,
  SPLIT_PART('a.b.c', '.', 2) AS second_part  -- 'b'
FROM articles;

SELECT
  NOW() AS current_timestamp,
  CURRENT_DATE AS today,
  DATE_TRUNC('month', NOW()) AS month_start,
  EXTRACT(YEAR FROM created_at) AS year,
  EXTRACT(DOW FROM created_at) AS day_of_week,  -- 0=Sun
  created_at + INTERVAL '30 days' AS expires_at,
  AGE(NOW(), created_at) AS account_age,
  TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI') AS formatted
FROM users;

-- Access JSON fields
SELECT
  metadata->>'name' AS name,           -- text
  metadata->'address'->>'city' AS city, -- nested text
  metadata->'tags' AS tags_json,        -- jsonb
  jsonb_array_length(metadata->'tags') AS tag_count
FROM users;

-- Filter by JSON field
SELECT * FROM users
WHERE metadata->>'role' = 'admin';

-- Update JSONB field
UPDATE users
SET metadata = metadata || '{"verified": true}'::jsonb
WHERE id = 1;

-- Build JSON from columns
SELECT jsonb_build_object('id', id, 'name', name) FROM users;

-- Array contains
SELECT * FROM posts WHERE 'react' = ANY(tags);

-- Array overlap
SELECT * FROM posts WHERE tags && ARRAY['react', 'vue'];

-- Unnest array to rows
SELECT id, UNNEST(tags) AS tag FROM posts;

-- Aggregate to array
SELECT user_id, ARRAY_AGG(DISTINCT tag ORDER BY tag) AS all_tags
FROM post_tags
GROUP BY user_id;

-- Show execution plan with actual timing
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = true
GROUP BY u.id, u.name;

-- Common plan nodes to watch:
-- Seq Scan     → full table scan (may need index)
-- Index Scan   → using index (good)
-- Hash Join    → joining with hash table
-- Sort         → explicit sorting (check if index can help)
-- Nested Loop  → for each outer row, scan inner (watch for large sets)

-- B-tree (default): equality, range, sorting
CREATE INDEX idx_date ON orders(created_at);

-- GIN: arrays, JSONB, full-text search
CREATE INDEX idx_tags ON posts USING GIN(tags);
CREATE INDEX idx_meta ON users USING GIN(metadata);

-- GiST: geometric, range types, full-text
CREATE INDEX idx_location ON places USING GIST(point);

-- BRIN: large tables with natural ordering (timestamps)
CREATE INDEX idx_log_time ON logs USING BRIN(created_at);

-- 1. Use specific columns instead of SELECT *
SELECT id, name, email FROM users;  -- not SELECT *

-- 2. Use EXISTS instead of COUNT for existence checks
-- Bad
SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END FROM orders WHERE user_id = 1;
-- Good
SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = 1);

-- 3. Use LIMIT for sampling
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

-- 4. Avoid functions on indexed columns in WHERE
-- Bad (can't use index)
WHERE LOWER(email) = 'alice@example.com'
-- Good (use expression index or store lowercase)
WHERE email = 'alice@example.com'

BEGIN;

-- Debit from one account
UPDATE accounts SET balance = balance - 100
WHERE id = 1 AND balance >= 100;

-- Credit to another account
UPDATE accounts SET balance = balance + 100
WHERE id = 2;

-- Verify
SELECT id, balance FROM accounts WHERE id IN (1, 2);

COMMIT;  -- or ROLLBACK if something went wrong

-- Savepoints for partial rollback
BEGIN;
SAVEPOINT sp1;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT sp2;
INSERT INTO order_items (...) VALUES (...);  -- fails
ROLLBACK TO sp2;  -- undo only order_items
COMMIT;  -- order still committed

-- Update statistics for query planner
ANALYZE users;

-- Reclaim dead tuple space
VACUUM users;

-- Full vacuum (exclusive lock, reclaims disk space)
VACUUM FULL large_table;

-- Check table bloat
SELECT
  relname AS table,
  n_dead_tup AS dead_tuples,
  n_live_tup AS live_tuples,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Create partitioned table (PostgreSQL 10+)
CREATE TABLE events (
  id BIGSERIAL,
  event_type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Query automatically prunes irrelevant partitions
SELECT * FROM events
WHERE created_at >= '2025-01-15' AND created_at < '2025-02-01';

-- Table sizes
SELECT relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- Active queries
SELECT pid, state, query, NOW() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid();

-- Index usage
SELECT relname AS table, indexrelname AS index,
  idx_scan AS scans, idx_tup_read AS rows_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC LIMIT 10;