🧭 DB 입문 & 로드맵 완전 가이드

CREATE TABLE members (
  id         BIGSERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  member_id  BIGINT  NOT NULL REFERENCES members(id),   -- 1:N 관계
  amount     INTEGER NOT NULL CHECK (amount > 0),
  ordered_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_member ON orders(member_id);   -- JOIN·조회 성능

INSERT INTO members (email, name) VALUES ('kim@example.com', '김개발'), ('lee@example.com', '이디비');
INSERT INTO orders (member_id, amount) VALUES (1, 30000), (1, 12000), (2, 8000);

-- 회원별 주문 건수와 총액 (많이 산 순)
SELECT m.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total
FROM members m
JOIN orders o ON o.member_id = m.id
GROUP BY m.id, m.name
ORDER BY total DESC;