✍️ 필사 모드: The Complete Guide to Database Fundamentals — SQL, Normalization, Indexes, Transactions, NoSQL

-- Basic query
SELECT name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 10;

-- Using aliases
SELECT
    u.name AS user_name,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- INNER JOIN: only matching rows from both sides
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: all rows from left table + matching right
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN: all rows from right table + matching left
SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

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

-- Departments where average salary exceeds 50,000
SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) >= 50000
ORDER BY avg_salary DESC;

-- Employees with salary above average
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

-- Subquery with EXISTS
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
    AND o.created_at >= '2026-01-01'
);

-- Subquery with IN
SELECT name, department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING COUNT(*) > 10
);

-- 1NF violation: multiple values in phone_numbers
-- name: 'John Doe', phone_numbers: '555-1234, 555-5678'

-- 1NF compliant: separate table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE phone_numbers (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    phone VARCHAR(20) NOT NULL
);

-- 2NF violation: student_name depends only on student_id
-- (student_id, course_id) -> grade  (OK)
-- student_id -> student_name  (partial dependency!)

-- 2NF compliant
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(student_id),
    course_id INTEGER REFERENCES courses(course_id),
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

-- 3NF violation: department_name is transitively dependent via department_id
-- employee_id -> department_id -> department_name

-- 3NF compliant
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(department_id)
);

-- Denormalization example: storing user name in orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    user_name VARCHAR(100),  -- Denormalized: eliminates JOIN on read
    total_amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Query examples with index usage
SELECT * FROM users WHERE email = 'test@example.com';  -- Uses index
SELECT * FROM users WHERE email LIKE 'test%';           -- Uses index
SELECT * FROM users WHERE email LIKE '%test';           -- No index (leading wildcard)

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- GIN index on JSONB column
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- GIN index for full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (
    to_tsvector('english', title || ' ' || content)
);

-- Spatial data index
CREATE INDEX idx_locations_coords ON locations USING GIST (coordinates);

-- Composite index: matches from leading column
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Queries where this index is effective:
SELECT * FROM orders WHERE user_id = 1;                          -- Used
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2026-01-01'; -- Used
SELECT * FROM orders WHERE created_at > '2026-01-01';            -- Not used (missing leading column)

-- Covering index
CREATE INDEX idx_orders_covering ON orders(user_id, created_at, total_amount);

-- Can be processed using index only (Index Only Scan)
SELECT user_id, created_at, total_amount
FROM orders
WHERE user_id = 1 AND created_at > '2026-01-01';

-- Bank transfer transaction example
BEGIN;

UPDATE accounts SET balance = balance - 1000
WHERE account_id = 'A001';

UPDATE accounts SET balance = balance + 1000
WHERE account_id = 'B001';

-- Verify balance
SELECT balance FROM accounts WHERE account_id = 'A001';

COMMIT;  -- or ROLLBACK if there is an issue;

-- Setting isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT balance FROM accounts WHERE account_id = 'A001';
-- Changes from other transactions are not visible during this transaction
COMMIT;

-- Deadlock scenario
-- Transaction 1: locks A then B
-- Transaction 2: locks B then A

-- Solutions:
-- 1. Unify lock ordering (always lock the lower ID first)
-- 2. Set timeouts
-- 3. Minimize transaction scope

SET lock_timeout = '5s';

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

-- N+1 problem occurring
-- 1 query: SELECT * FROM users LIMIT 100;
-- 100 queries: SELECT * FROM orders WHERE user_id = ?;  (for each user)

-- Solution: retrieve in one query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (SELECT id FROM users LIMIT 100);

-- Or use a subquery
SELECT u.*, (
    SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id
) AS order_count
FROM users u
LIMIT 100;

-- Bad example: index invalidated
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- Good example: index can be utilized
SELECT * FROM users
WHERE created_at >= '2026-01-01'
  AND created_at < '2027-01-01';

-- Creating and using JSONB columns
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    attributes JSONB
);

-- Inserting data
INSERT INTO products (name, attributes)
VALUES ('Laptop', '{"brand": "Samsung", "ram": 16, "storage": "512GB", "tags": ["electronics", "portable"]}');

-- JSONB queries
SELECT name, attributes->>'brand' AS brand
FROM products
WHERE attributes->>'ram' = '16';

-- JSONB containment operator
SELECT * FROM products
WHERE attributes @> '{"brand": "Samsung"}';

-- JSONB array query
SELECT * FROM products
WHERE attributes->'tags' ? 'electronics';

-- Recursive CTE: traversing an org chart
WITH RECURSIVE org_tree AS (
    -- Base case: top-level manager
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

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

-- Salary ranking by department
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
    RANK() OVER (ORDER BY salary DESC) AS overall_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS diff_from_prev
FROM employees;

-- Running total
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM daily_sales;

-- Range partitioning
CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- Creating monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Indexes are automatically created per partition
CREATE INDEX idx_events_type ON events(event_type);

// MongoDB query examples
db.users.insertOne({
  name: "John Doe",
  email: "john@example.com",
  address: {
    city: "New York",
    state: "NY"
  },
  tags: ["premium", "active"]
});

// Aggregation pipeline
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: {
      _id: "$user_id",
      totalSpent: { $sum: "$amount" },
      orderCount: { $sum: 1 }
  }},
  { $sort: { totalSpent: -1 } },
  { $limit: 10 }
]);

-- Cassandra CQL
CREATE TABLE sensor_data (
    sensor_id UUID,
    event_time TIMESTAMP,
    temperature DOUBLE,
    humidity DOUBLE,
    PRIMARY KEY (sensor_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

// Neo4j Cypher query
// Finding friends of friends (2-degree relationship)
MATCH (me:Person)-[:FRIEND]->(friend)-[:FRIEND]->(fof)
WHERE me.name = 'John Doe'
  AND NOT (me)-[:FRIEND]->(fof)
  AND fof <> me
RETURN DISTINCT fof.name AS recommendation
LIMIT 10;

# Set cache (TTL 3600 seconds)
SET user:1001:profile '{"name":"John Doe","email":"john@example.com"}' EX 3600

# Read cache
GET user:1001:profile

# Invalidate cache
DEL user:1001:profile

# Store session (30-minute expiry)
HSET session:abc123 user_id 1001 role admin login_time "2026-04-12T10:00:00"
EXPIRE session:abc123 1800

# Retrieve session
HGETALL session:abc123

# Add scores
ZADD game:leaderboard 1500 "player:001"
ZADD game:leaderboard 2300 "player:002"
ZADD game:leaderboard 1800 "player:003"

# Get top 10 (highest score first)
ZREVRANGE game:leaderboard 0 9 WITHSCORES

# Get a specific player rank
ZREVRANK game:leaderboard "player:001"

# Subscribe to channel
SUBSCRIBE notifications:user:1001

# Publish message
PUBLISH notifications:user:1001 '{"type":"order","message":"Your order has been completed"}'

# Acquire lock (NX: only if not exists, EX: expiry time)
SET lock:order:process "worker-1" NX EX 30

# Release lock (Lua script for atomic operation)
# Only release the lock if the value matches (to ensure we own it)
EVAL "if redis.call('get', KEYS[1]) == ARGV[1] then return redis.call('del', KEYS[1]) else return 0 end" 1 lock:order:process "worker-1"

-- User and profile: 1:1
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),
    bio TEXT,
    avatar_url VARCHAR(500)
);

-- User and posts: 1:N
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Posts and tags: M:N (junction table required)
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER REFERENCES posts(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

-- V1__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- V2__add_status_to_users.sql
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
CREATE INDEX idx_users_status ON users(status);

-- V3__create_orders_table.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

# alembic/versions/001_create_users.py
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('email', sa.String(255), nullable=False, unique=True),
        sa.Column('name', sa.String(100), nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
    )

def downgrade():
    op.drop_table('users')

-- Add without NOT NULL (no impact on existing rows)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

ALTER TABLE users DROP COLUMN phone;

-- CONCURRENTLY option: create index without table lock
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);