【演習ノック編】SQL50本ノック - 手を動かして定着させる

Last updated at 2026-09-20Posted at 2026-09-19

このノックについて

42日シリーズの演習編、第2弾です。本編の #5〜#9（SQL基礎・テーブル設計と正規化・JOIN・インデックス・トランザクション）まで終えてから取り組んでください。

本編で「なぜそうなるのか」は学びました。ここでやるのは反復です。SELECT が手に馴染んでいないと、JOIN やトランザクションの議論は上滑りします。50本を通して、読めば分かるを「書ける」に変えます。

レベル	問題	対応する本編
1. SELECT基礎	Q1〜Q8	#5
2. 集計とGROUP BY	Q9〜Q18	#5
3. JOIN	Q19〜Q28	#7
4. 更新系（INSERT/UPDATE/DELETE）	Q29〜Q34	#5, #10
5. 制約とテーブル設計	Q35〜Q40	#6
6. インデックスとEXPLAIN	Q41〜Q45	#8
7. トランザクション	Q46〜Q50	#9

各問題はまず自分で書いてから解答を見て、必ず手元で実行して確認してください。間違えた問題には印をつけ、後日もう一度解きます。解答の実行結果はすべて実測です。同じ手順で実行すれば同じ結果になるはずなので、答え合わせにも使ってください。

環境

いつもの pg-practice コンテナを使います（docker start pg-practice で起動。無い場合は #4 の手順で作成してください）。

docker exec -it pg-practice psql -U postgres testdb

演習用に、小さなECサイトのデータベースを作ります。顧客（customers）が注文（orders）し、注文には複数の明細（order_items）があり、明細は商品（products)を指す。#6 で学んだ正規化の形そのままです。psql に以下を貼り付けてください。

DROP TABLE IF EXISTS order_items, orders, products, customers, big_orders CASCADE;

CREATE TABLE customers (
  id         serial PRIMARY KEY,
  name       text NOT NULL,
  email      text NOT NULL UNIQUE,
  prefecture text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id       serial PRIMARY KEY,
  name     text NOT NULL,
  price    integer NOT NULL CHECK (price >= 0),
  stock    integer NOT NULL DEFAULT 0,
  category text NOT NULL
);

CREATE TABLE orders (
  id          serial PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(id),
  status      text NOT NULL DEFAULT 'pending',
  ordered_at  timestamptz NOT NULL
);

CREATE TABLE order_items (
  order_id   integer NOT NULL REFERENCES orders(id),
  product_id integer NOT NULL REFERENCES products(id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers (name, email, prefecture, created_at) VALUES
  ('alice',  'alice@example.com',  '東京都',   '2026-08-01 10:00+09'),
  ('bob',    'bob@example.com',    '大阪府',   '2026-08-02 11:00+09'),
  ('carol',  'carol@example.com',  '東京都',   '2026-08-05 09:30+09'),
  ('dave',   'dave@example.com',   '福岡県',   '2026-08-10 18:00+09'),
  ('erin',   'erin@example.com',   '大阪府',   '2026-08-15 12:00+09');

INSERT INTO products (name, price, stock, category) VALUES
  ('Goで学ぶバックエンド入門', 3200, 12, 'book'),
  ('SQLアンチパターン攻略',    2800,  5, 'book'),
  ('メカニカルキーボード',     15800, 3, 'gadget'),
  ('USB-Cハブ',               4500, 20, 'gadget'),
  ('ドリップコーヒー30袋',     1980, 50, 'food'),
  ('プロテインバー12本',       1450,  0, 'food'),
  ('モニターライト',           6980,  8, 'gadget'),
  ('データベース設計の教科書', 3600,  7, 'book');

INSERT INTO orders (customer_id, status, ordered_at) VALUES
  (1, 'shipped',  '2026-09-01 10:15+09'),
  (2, 'shipped',  '2026-09-02 14:00+09'),
  (1, 'pending',  '2026-09-05 09:00+09'),
  (3, 'shipped',  '2026-09-06 20:30+09'),
  (2, 'canceled', '2026-09-08 08:45+09'),
  (4, 'pending',  '2026-09-10 16:20+09'),
  (1, 'shipped',  '2026-09-12 11:00+09');

INSERT INTO order_items (order_id, product_id, quantity) VALUES
  (1, 1, 1), (1, 5, 2),
  (2, 3, 1),
  (3, 8, 1), (3, 2, 1),
  (4, 4, 2), (4, 5, 1),
  (5, 5, 3),
  (6, 1, 1), (6, 7, 1),
  (7, 5, 4);

問題は上から順に解いてください。途中の問題でデータを変更するので、順番を飛ばすと結果が解答と一致しないことがあります。

レベル1: SELECT基礎（Q1〜Q8）

Q1

products テーブルの全行・全列を表示してください。

解答を見る

SELECT * FROM products;

 id |           name           | price | stock | category
----+--------------------------+-------+-------+----------
  1 | Goで学ぶバックエンド入門 |  3200 |    12 | book
  2 | SQLアンチパターン攻略    |  2800 |     5 | book
  3 | メカニカルキーボード     | 15800 |     3 | gadget
  4 | USB-Cハブ                |  4500 |    20 | gadget
  5 | ドリップコーヒー30袋     |  1980 |    50 | food
  6 | プロテインバー12本       |  1450 |     0 | food
  7 | モニターライト           |  6980 |     8 | gadget
  8 | データベース設計の教科書 |  3600 |     7 | book
(8 rows)

* は全列の意味です。動作確認には便利ですが、アプリのコードでは列を明示します（#5）。以降の問題でこの8商品を何度も使うので、ここで一覧を眺めておいてください。

Q2

products から name と price の2列だけを表示してください。

解答を見る

SELECT name, price FROM products;

           name           | price
--------------------------+-------
 Goで学ぶバックエンド入門 |  3200
 SQLアンチパターン攻略    |  2800
 メカニカルキーボード     | 15800
 USB-Cハブ                |  4500
 ドリップコーヒー30袋     |  1980
 プロテインバー12本       |  1450
 モニターライト           |  6980
 データベース設計の教科書 |  3600
(8 rows)

Q3

category が 'book' の商品の name と price を表示してください。

解答を見る

SELECT name, price FROM products WHERE category = 'book';

           name           | price
--------------------------+-------
 Goで学ぶバックエンド入門 |  3200
 SQLアンチパターン攻略    |  2800
 データベース設計の教科書 |  3600
(3 rows)

文字列の比較はシングルクォートです。ダブルクォートは列名の引用なので、"book" と書くと「book という列」を探してエラーになります。

Q4

price が 3000 以上の商品の name と price を表示してください。

解答を見る

SELECT name, price FROM products WHERE price >= 3000;

           name           | price
--------------------------+-------
 Goで学ぶバックエンド入門 |  3200
 メカニカルキーボード     | 15800
 USB-Cハブ                |  4500
 モニターライト           |  6980
 データベース設計の教科書 |  3600
(5 rows)

Q5

全商品の name と price を、価格の高い順に表示してください。

解答を見る

SELECT name, price FROM products ORDER BY price DESC;

           name           | price
--------------------------+-------
 メカニカルキーボード     | 15800
 モニターライト           |  6980
 USB-Cハブ                |  4500
 データベース設計の教科書 |  3600
 Goで学ぶバックエンド入門 |  3200
 SQLアンチパターン攻略    |  2800
 ドリップコーヒー30袋     |  1980
 プロテインバー12本       |  1450
(8 rows)

ORDER BY を付けない SELECT の並び順は保証されません。「なんとなくID順に見える」に頼ったコードは、いつか順番が変わって壊れます。

Q6

価格の高い順に上位3件だけ表示してください。

解答を見る

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

         name         | price
----------------------+-------
 メカニカルキーボード | 15800
 モニターライト       |  6980
 USB-Cハブ            |  4500
(3 rows)

LIMIT は「並べてから切る」ので、ORDER BY とセットで使って初めて意味が確定します。

Q7

商品のカテゴリ一覧を、重複なしで表示してください。

解答を見る

SELECT DISTINCT category FROM products;

 category
----------
 food
 gadget
 book
(3 rows)

Q8

名前に「データベース」を含む商品の name を表示してください。

解答を見る

SELECT name FROM products WHERE name LIKE '%データベース%';

           name
--------------------------
 データベース設計の教科書
(1 row)

% は「任意の0文字以上」。この中間一致がインデックスとどう付き合うかは、Q44で実際に確かめます。

レベル2: 集計とGROUP BY（Q9〜Q18）

Q9

顧客は全部で何人いますか。SQLで数えてください。

解答を見る

SELECT count(*) FROM customers;

 count
-------
     5
(1 row)

Q10

商品の平均価格を、四捨五入した整数で表示してください（列名は avg_price）。

解答を見る

SELECT round(avg(price)) AS avg_price FROM products;

 avg_price
-----------
      5039
(1 row)

avg の結果は割り切れない小数になるので、表示用には round を添えます。AS で列名を付けるのはアプリ側で受け取るときの作法でもあります。

Q11

カテゴリごとの商品数を表示してください。

解答を見る

SELECT category, count(*) FROM products GROUP BY category;

 category | count
----------+-------
 food     |     2
 gadget   |     3
 book     |     3
(3 rows)

Q12

カテゴリごとの平均価格（四捨五入・列名 avg_price）を、平均価格の高い順に表示してください。

解答を見る

SELECT category, round(avg(price)) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

 category | avg_price
----------+-----------
 gadget   |      9093
 book     |      3200
 food     |      1715
(3 rows)

Q13

商品数が3つ以上あるカテゴリだけを、商品数つきで表示してください。

解答を見る

SELECT category, count(*) FROM products GROUP BY category HAVING count(*) >= 3;

 category | count
----------+-------
 gadget   |     3
 book     |     3
(2 rows)

行を絞るのが WHERE、グループを絞るのが HAVING です。「集計した結果に対する条件」は WHERE には書けません。

Q14

最高価格と最低価格を、1回のクエリで表示してください（列名は max_price と min_price）。

解答を見る

SELECT max(price) AS max_price, min(price) AS min_price FROM products;

 max_price | min_price
-----------+-----------
     15800 |      1450
(1 row)

Q15

都道府県ごとの顧客数を、多い順（同数なら都道府県名順）に表示してください。

解答を見る

SELECT prefecture, count(*) FROM customers
GROUP BY prefecture
ORDER BY count(*) DESC, prefecture;

 prefecture | count
------------+-------
 大阪府     |     2
 東京都     |     2
 福岡県     |     1
(3 rows)

同数のときの順序も ORDER BY で明示するのがポイントです。指定しないと同数グループ内の並びは実行のたびに変わり得ます。

Q16

category が 'gadget' の商品の在庫数の合計を表示してください。

解答を見る

SELECT sum(stock) FROM products WHERE category = 'gadget';

 sum
-----
  31
(1 row)

Q17

status が 'shipped' ではない注文の件数を数えてください。

解答を見る

SELECT count(*) FROM orders WHERE status <> 'shipped';

 count
-------
     3
(1 row)

<> が標準の「等しくない」です（!= も PostgreSQL では同じ意味）。pending 2件と canceled 1件で3件です。

Q18

価格が3000円未満を「お手頃」、3000円以上を「高価格」として、それぞれの商品数を表示してください（CASE式を使います。分類の列名は price_range）。

解答を見る

SELECT CASE WHEN price < 3000 THEN 'お手頃' ELSE '高価格' END AS price_range,
       count(*)
FROM products
GROUP BY price_range;

 price_range | count
-------------+-------
 高価格      |     5
 お手頃      |     3
(2 rows)

CASE式は「値を条件で変換する」道具で、SELECT にも GROUP BY にも置けます。集計の軸を自分で作れるようになると、レポート系のSQLが一気に書きやすくなります。

レベル3: JOIN（Q19〜Q28）

Q19

注文の一覧を「注文ID・顧客名・ステータス・注文日時」の形で、注文ID順に表示してください。

解答を見る

SELECT o.id, c.name, o.status, o.ordered_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

 id | name  |  status  |       ordered_at
----+-------+----------+------------------------
  1 | alice | shipped  | 2026-09-01 01:15:00+00
  2 | bob   | shipped  | 2026-09-02 05:00:00+00
  3 | alice | pending  | 2026-09-05 00:00:00+00
  4 | carol | shipped  | 2026-09-06 11:30:00+00
  5 | bob   | canceled | 2026-09-07 23:45:00+00
  6 | dave  | pending  | 2026-09-10 07:20:00+00
  7 | alice | shipped  | 2026-09-12 02:00:00+00
(7 rows)

orders には customer_id（数字）しか無いので、名前が欲しければ customers と結合します。o や c の別名は #7 で学んだとおり、長いクエリを読みやすくする定石です。

Q20

注文明細を「注文ID・商品名・数量」の形で、注文ID順（同一注文内は商品ID順）に表示してください。

解答を見る

SELECT o.id AS order_id, p.name, oi.quantity
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
ORDER BY o.id, p.id;

 order_id |           name           | quantity
----------+--------------------------+----------
        1 | Goで学ぶバックエンド入門 |        1
        1 | ドリップコーヒー30袋     |        2
        2 | メカニカルキーボード     |        1
        3 | SQLアンチパターン攻略    |        1
        3 | データベース設計の教科書 |        1
        4 | USB-Cハブ                |        2
        4 | ドリップコーヒー30袋     |        1
        5 | ドリップコーヒー30袋     |        3
        6 | Goで学ぶバックエンド入門 |        1
        6 | モニターライト           |        1
        7 | ドリップコーヒー30袋     |        4
(11 rows)

3テーブルのJOINも、2テーブルの繰り返しです。「明細を軸に、注文と商品をぶら下げる」と考えると迷いません。

Q21

注文ごとの合計金額（価格×数量の合計。列名 total）を、注文ID順に表示してください。

解答を見る

SELECT o.id AS order_id, sum(p.price * oi.quantity) AS total
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
GROUP BY o.id
ORDER BY o.id;

 order_id | total
----------+-------
        1 |  7160
        2 | 15800
        3 |  6400
        4 | 10980
        5 |  5940
        6 | 10180
        7 |  7920
(7 rows)

JOINで広げてからGROUP BYで畳む。この「広げて畳む」がSQLの集計の基本形です。

Q22

顧客ごとの総購入額を、金額の大きい順に表示してください。一度も注文していない顧客は出なくて構いません。

解答を見る

SELECT c.name, sum(p.price * oi.quantity) AS total
FROM order_items oi
JOIN orders o    ON o.id = oi.order_id
JOIN products p  ON p.id = oi.product_id
JOIN customers c ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total DESC;

 name  | total
-------+-------
 bob   | 21740
 alice | 21480
 carol | 10980
 dave  | 10180
(4 rows)

GROUP BY を c.name だけにせず c.id を入れているのは、同姓同名の顧客がいても別人として集計されるようにするためです。名前でグループ化すると別人が合算されます。

Q23

一度も注文していない顧客の名前を表示してください。

解答を見る

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

 name
------
 erin
(1 row)

「存在しないものを探す」はLEFT JOINの代表的な使い方です。内部結合では注文の無い顧客は行ごと消えるので、この問いには答えられません。

Q24

customers と orders を「内部結合した場合」と「LEFT JOINした場合」で、結果の行数がどう違うかをそれぞれ数えて確かめてください。

解答を見る

SELECT count(*) FROM customers c JOIN orders o ON o.customer_id = c.id;
SELECT count(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

 count
-------
     7
(1 row)

 count
-------
     8
(1 row)

内部結合は「注文7件ぶんの行」だけ。LEFT JOINは注文の無い erin の行が右側NULLで残るので1行増えます。行数の差 = 左側だけに存在する行の数、という読み方は実務のデータ調査で頻繁に使います。

Q25

一度も売れていない（どの注文明細にも登場しない）商品の名前を表示してください。

解答を見る

SELECT p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.order_id IS NULL;

        name
--------------------
 プロテインバー12本
(1 row)

Q23と同じ型です。「LEFT JOIN して右側が NULL の行を探す」は1つの決まり文句として手に入れてください。

Q26

都道府県ごとの売上合計を、金額の大きい順に表示してください。

解答を見る

SELECT c.prefecture, sum(p.price * oi.quantity) AS total
FROM order_items oi
JOIN orders o    ON o.id = oi.order_id
JOIN products p  ON p.id = oi.product_id
JOIN customers c ON c.id = o.customer_id
GROUP BY c.prefecture
ORDER BY total DESC;

 prefecture | total
------------+-------
 東京都     | 32460
 大阪府     | 21740
 福岡県     | 10180
(3 rows)

Q22と同じJOINで、畳む軸を顧客から都道府県に変えただけです。JOINの形とGROUP BYの軸は独立に選べる、と分かるとSQLの見通しが良くなります。

Q27

注文したことのある顧客について、顧客ごとの最新注文日時を新しい順に表示してください。

解答を見る

SELECT c.name, max(o.ordered_at) AS last_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY last_ordered DESC;

 name  |      last_ordered
-------+------------------------
 alice | 2026-09-12 02:00:00+00
 dave  | 2026-09-10 07:20:00+00
 bob   | 2026-09-07 23:45:00+00
 carol | 2026-09-06 11:30:00+00
(4 rows)

Q28

平均価格より高い商品の name と price を、価格の高い順に表示してください（サブクエリを使います）。

解答を見る

SELECT name, price
FROM products
WHERE price > (SELECT avg(price) FROM products)
ORDER BY price DESC;

         name         | price
----------------------+-------
 メカニカルキーボード | 15800
 モニターライト       |  6980
(2 rows)

括弧の中が先に評価されて1つの値（平均5039円）になり、外側のWHEREで使われます。「平均より上」のように、条件そのものをデータから作りたいときがサブクエリの出番です。

レベル4: 更新系（Q29〜Q34）

Q29

顧客 frank（email: frank@example.com、北海道）を追加し、追加されたことを SELECT で確認してください。

解答を見る

INSERT INTO customers (name, email, prefecture) VALUES ('frank', 'frank@example.com', '北海道');
SELECT id, name, prefecture FROM customers WHERE name = 'frank';

INSERT 0 1
 id | name  | prefecture
----+-------+------------
  6 | frank | 北海道
(1 row)

id と created_at は指定していません。serial と DEFAULT が埋めてくれる列は、INSERTで触らないのが基本です。

Q30

商品「ワイヤレスマウス」（3980円、在庫15、gadget）を追加してください。ただし INSERT と同時に、採番された id を返してください。

解答を見る

INSERT INTO products (name, price, stock, category)
VALUES ('ワイヤレスマウス', 3980, 15, 'gadget')
RETURNING id;

 id
----
  9
(1 row)

INSERT 0 1

RETURNING はPostgreSQLの便利機能で、INSERTした行の値をその場で返せます。#10 でGoから使った「採番されたidを受け取る」の正体がこれです。INSERTしてからSELECTで探し直すより速く、確実です。

Q31

id = 3 の商品の在庫を1減らし、結果を確認してください。

解答を見る

UPDATE products SET stock = stock - 1 WHERE id = 3;
SELECT name, stock FROM products WHERE id = 3;

UPDATE 1
         name         | stock
----------------------+-------
 メカニカルキーボード |     2
(1 row)

stock = stock - 1 のように「今の値を使って更新」と書くのが大事です。アプリ側で読んでから引き算して書き戻す方式は、同時アクセスで数が合わなくなります（Q48で続きをやります）。

Q32

category が 'food' の商品を100円値上げします。UPDATE を打つ前に、何行が変更対象になるかを確認する SQL を打ってから、UPDATE してください。

解答を見る

SELECT count(*) FROM products WHERE category = 'food';

 count
-------
     2
(1 row)

UPDATE products SET price = price + 100 WHERE category = 'food';

UPDATE 2

事前のSELECTの件数（2）とUPDATEの結果（UPDATE 2）が一致していることを確認します。同じWHERE句でまずSELECTしてからUPDATEする。この一手間が、WHERE忘れで全行更新する事故（#10で触れた定番事故）への一番簡単な保険です。

Q33

id = 5 の注文（キャンセル済み）をデータベースから物理削除してください。そのまま DELETE FROM orders WHERE id = 5; を打つとどうなるかを考えてから、正しい順序で消してください。

解答を見る

先に DELETE FROM orders WHERE id = 5; を打つと、order_items の外部キーがこの注文を参照しているため、次のエラーで拒否されます（Q37と同じ仕組みが削除側にも効いています）。子から先に消します。

DELETE FROM order_items WHERE order_id = 5;
DELETE FROM orders WHERE id = 5;
SELECT count(*) FROM orders;

DELETE 1
DELETE 1
 count
-------
     6
(1 row)

「親を消すには先に子を消す」。外部キーがある世界の削除の基本です（ON DELETE CASCADE という自動化もありますが、意図しない連鎖削除が怖いので、明示的に消すほうが事故が少ないです）。

Q34

products に論理削除用の列 deleted_at（timestamptz）を追加し、id = 6 の商品を論理削除してください。そのうえで「削除されていない商品」の数を数えてください。

解答を見る

ALTER TABLE products ADD COLUMN deleted_at timestamptz;
UPDATE products SET deleted_at = '2026-09-18 00:00+09' WHERE id = 6;
SELECT count(*) FROM products WHERE deleted_at IS NULL;

ALTER TABLE
UPDATE 1
 count
-------
     8
(1 row)

行は9つあるのに、「生きている商品」は8つ。論理削除は「消えたことにする」だけでデータは残るので、後から説明を求められるデータに向きます（#10の判断基準）。そのかわり、すべての参照クエリに deleted_at IS NULL を付けて回る運用コストを引き受けることになります。

レベル5: 制約とテーブル設計（Q35〜Q40）

Q35

すでに存在する email（alice@example.com）で顧客を追加しようとすると何が起きますか。実際に打って、エラーメッセージを確認してください。

解答を見る

INSERT INTO customers (name, email, prefecture) VALUES ('alice2', 'alice@example.com', '東京都');

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(alice@example.com) already exists.

UNIQUE制約が二重登録を止めました。「アプリ側で重複チェックしているから大丈夫」は同時リクエストですり抜けます。最後の砦は常にDBの制約です（#6）。

Q36

価格が -100 円の商品を追加しようとすると何が起きますか。実際に確かめてください。

解答を見る

INSERT INTO products (name, price, stock, category) VALUES ('不正な商品', -100, 1, 'book');

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (10, 不正な商品, -100, 1, book, null).

CHECK制約は「この列が満たすべき条件」を宣言する仕組みです。ありえない値はテーブルの定義で門前払いします。

Q37

存在しない顧客（customer_id = 999）の注文を作ろうとすると何が起きますか。実際に確かめてください。

解答を見る

INSERT INTO orders (customer_id, status, ordered_at) VALUES (999, 'pending', now());

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

外部キー制約により、存在しない顧客への注文は作れません。孤児レコード（親のいない子）を作らせないのが役目です。

Q38

名前が NULL の商品を追加しようとすると何が起きますか。実際に確かめてください。

解答を見る

INSERT INTO products (name, price, stock, category) VALUES (NULL, 1000, 1, 'book');

ERROR:  null value in column "name" of relation "products" violates not-null constraint
DETAIL:  Failing row contains (11, null, 1000, 1, book, null).

なお、Q36〜Q38の失敗したINSERTも serial の連番は消費しています（DETAILの行に id 10 や 11 が見えています）。連番には欠番ができるのが正常で、この伏線はQ46で回収します。

Q39

【知識問題】order_items テーブルに「商品名」の列を持たせない設計になっているのはなぜですか。#6 の言葉で説明してください。

解答を見る

order_items に商品名を持たせると、同じ商品名がテーブルのあちこちに重複して保存されます（#6 で学んだ正規化違反）。その状態で商品名を変更すると、全部の明細を更新して回る必要があり、1つでも漏れると「同じ商品なのに名前が違う」矛盾したデータが生まれます。事実は1か所にだけ置き、必要なときはJOINで引く。これが order_items が product_id だけを持つ理由です。

Q40

order_items の主キーは (order_id, product_id) の複合主キーです。同じ注文に同じ商品の明細をもう1行入れようとすると何が起きるか、実際に確かめてください。

解答を見る

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 5);

ERROR:  duplicate key value violates unique constraint "order_items_pkey"
DETAIL:  Key (order_id, product_id)=(1, 1) already exists.

複合主キー (order_id, product_id) は「同じ注文の同じ商品の明細は1行だけ」という業務ルールをテーブル定義で表現したものです。同じ商品を2個買うなら行を増やすのではなく quantity を増やす、という設計判断がキーの形に現れています。

レベル6: インデックスとEXPLAIN（Q41〜Q45）

Q41

検証用に大きなテーブルを作ります。以下を実行して、10万件入ったことを確認してください。

CREATE TABLE big_orders (
  id          serial PRIMARY KEY,
  customer_id integer NOT NULL,
  amount      integer NOT NULL,
  memo        text NOT NULL,
  ordered_on  date NOT NULL
);
INSERT INTO big_orders (customer_id, amount, memo, ordered_on)
SELECT (i % 1000) + 1, (i * 37) % 50000, 'memo-' || i, DATE '2025-01-01' + (i % 600)
FROM generate_series(1, 100000) AS i;

解答を見る

CREATE TABLE
INSERT 0 100000
 count
--------
 100000
(1 row)

generate_series は連番を生成する関数で、テスト用の大量データ作りの定番です。#8 で学んだとおり、件数が少ないとDBは全件走査を選ぶため、インデックスの実験には数万件以上が必要です。

Q42

customer_id = 42 の行を探すクエリの実行計画を EXPLAIN ANALYZE で確認してください。どんなスキャンになっていますか。

解答を見る

EXPLAIN ANALYZE SELECT * FROM big_orders WHERE customer_id = 42;

                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on big_orders  (cost=0.00..1720.40 rows=394 width=48) (actual time=0.005..3.325 rows=100 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 99900
 Planning Time: 0.032 ms
 Execution Time: 3.335 ms
(5 rows)

Seq Scan、つまり10万行の全件走査です。「99900行を読んで捨てた」と正直に書いてあります。

Q43

customer_id にインデックスを貼り、Q42と同じクエリの実行計画がどう変わったか確認してください。

解答を見る

CREATE INDEX idx_big_orders_customer ON big_orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM big_orders WHERE customer_id = 42;

 Bitmap Heap Scan on big_orders  (cost=8.17..710.60 rows=500 width=48) (actual time=0.023..0.105 rows=100 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=100
   ->  Bitmap Index Scan on idx_big_orders_customer  (cost=0.00..8.04 rows=500 width=0) (actual time=0.013..0.013 rows=100 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.103 ms
 Execution Time: 0.119 ms
(7 rows)

実行時間が 3.335ms → 0.119ms、約28分の1になりました。インデックスで対象行の場所を特定してから、本体を読みに行く形（Bitmap Scan）です。

Q44

memo 列に普通のインデックスを貼り、前方一致（LIKE 'memo-1234%'）と中間一致（LIKE '%1234%'）の実行計画を確認してください。予想と違う結果が出たら、その理由を調べてください。

解答を見る

CREATE INDEX idx_big_orders_memo ON big_orders (memo);
EXPLAIN ANALYZE SELECT * FROM big_orders WHERE memo LIKE 'memo-1234%';

 Seq Scan on big_orders  (cost=0.00..1986.00 rows=500 width=48) (actual time=0.073..5.229 rows=11 loops=1)
   Filter: (memo ~~ 'memo-1234%'::text)
   Rows Removed by Filter: 99989
 Planning Time: 0.207 ms
 Execution Time: 5.240 ms
(5 rows)

予想が外れた人が多いはずです。#8 では「前方一致はインデックスが効く」と学んだのに、Seq Scan のままです。これはこのデータベースのロケール（文字の並び順の規則）が C ではないためで、通常のB-Treeインデックスでは LIKE の前方一致に使えません。演算子クラス text_pattern_ops を指定した専用のインデックスを貼ると効くようになります。

CREATE INDEX idx_big_orders_memo_pattern ON big_orders (memo text_pattern_ops);
EXPLAIN ANALYZE SELECT * FROM big_orders WHERE memo LIKE 'memo-1234%';

 Bitmap Heap Scan on big_orders  (cost=13.54..715.98 rows=500 width=48) (actual time=0.027..0.030 rows=11 loops=1)
   Filter: (memo ~~ 'memo-1234%'::text)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on idx_big_orders_memo_pattern  (cost=0.00..13.42 rows=500 width=0) (actual time=0.021..0.021 rows=11 loops=1)
         Index Cond: ((memo ~>=~ 'memo-1234'::text) AND (memo ~<~ 'memo-1235'::text))
   Planning Time: 0.161 ms
 Execution Time: 0.043 ms
(7 rows)

中間一致（LIKE '%1234%'）はどちらのインデックスでも Seq Scan のままです（実測 6.850ms）。先頭が決まっていないと辞書は引けない、という #8 の理屈どおりです。「知識どおりのはずなのに効かない」ときに EXPLAIN で確かめて理由を掘る。この体験こそが実務の型です。

Q45

(customer_id, ordered_on) の複合インデックスを貼り、「両方を指定した検索」と「ordered_on だけの検索」で実行計画とコストがどう違うか確認してください。

解答を見る

CREATE INDEX idx_big_orders_cust_date ON big_orders (customer_id, ordered_on);
EXPLAIN ANALYZE SELECT * FROM big_orders WHERE customer_id = 42 AND ordered_on = '2025-02-10';

 Bitmap Heap Scan on big_orders  (cost=4.31..12.03 rows=2 width=48) (actual time=0.010..0.010 rows=0 loops=1)
   Recheck Cond: ((customer_id = 42) AND (ordered_on = '2025-02-10'::date))
   ->  Bitmap Index Scan on idx_big_orders_cust_date  (cost=0.00..4.31 rows=2 width=0)
(6 rows)

EXPLAIN ANALYZE SELECT * FROM big_orders WHERE ordered_on = '2025-02-10';

 Bitmap Heap Scan on big_orders  (cost=1122.42..1824.85 rows=500 width=48) (actual time=0.290..0.408 rows=167 loops=1)
   Recheck Cond: (ordered_on = '2025-02-10'::date)
   ->  Bitmap Index Scan on idx_big_orders_cust_date  (cost=0.00..1122.29 rows=500 width=0)
(7 rows)

面白いのはコストの差です。左端（customer_id）を含む検索はコスト 4.31。ordered_on だけの検索も一応インデックスを使っていますが、コストは 1122 と260倍です。左端が決まらないと索引の絞り込みが効かず、索引全体をなめる「実質全走査」になっているためです。#8 の左端プレフィックス規則は「まったく使えない」ではなく「絞り込みが効かず桁違いに損」と覚えるのが実測に忠実です。

レベル7: トランザクション（Q46〜Q50）

Q46

「erin が USB-Cハブを1個注文する」処理を1つのトランザクションで書いてください。orders への追加、order_items への追加、在庫を1減らす、の3つが全部成功するか全部失敗するかのどちらかになるようにします。

解答を見る

BEGIN;
WITH new_order AS (
  INSERT INTO orders (customer_id, status, ordered_at)
  VALUES (5, 'pending', '2026-09-18 09:00+09')
  RETURNING id
)
INSERT INTO order_items (order_id, product_id, quantity)
SELECT id, 4, 1 FROM new_order;
UPDATE products SET stock = stock - 1 WHERE id = 4;
COMMIT;

BEGIN
INSERT 0 1
UPDATE 1
COMMIT

確認します。

SELECT o.id, c.name, p.name AS product, oi.quantity
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
ORDER BY o.id DESC LIMIT 1;

 id | name |  product  | quantity
----+------+-----------+----------
  9 | erin | USB-Cハブ |        1
(1 row)

ポイント

注文IDが 8 ではなく 9 なのは、Q37の失敗INSERTが連番8を消費したからです（Q38の伏線回収）。「次のIDは8のはず」と決め打ちで書くと、まさにこの欠番で壊れます
そこで採番されたIDは RETURNING で受け取り、WITH（CTE）でそのまま order_items のINSERTに渡しています。IDを手で書き写さないのが正解です
3つの文がBEGINとCOMMITで囲まれているので、途中で失敗すれば全部無かったことになります。「注文はあるのに明細が無い」中途半端な状態が残らないのがトランザクションの価値です（#9の銀行振込と同じ）

Q47

トランザクションの中で order_items を全行 DELETE し、消えたことを確認してから、ROLLBACK で無かったことにしてください。

解答を見る

BEGIN;
DELETE FROM order_items;
SELECT count(*) FROM order_items;
ROLLBACK;
SELECT count(*) FROM order_items;

BEGIN
DELETE 11
 count
-------
     0
(1 row)

ROLLBACK
 count
-------
    11
(1 row)

トランザクションの中では確かに0件になり、ROLLBACKで11件に戻りました。逆に言うと、COMMITしてしまったDELETEはROLLBACKでは戻せません。戻せるのはトランザクションが開いている間だけです。

Q48

在庫の引き当てで「読んでから更新するまでの間に他の人が更新する」のを防ぎたいとします。id = 4 の商品の在庫を、行ロックを取ってから1減らすトランザクションを書いてください。

解答を見る

BEGIN;
SELECT stock FROM products WHERE id = 4 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 4;
COMMIT;
SELECT name, stock FROM products WHERE id = 4;

BEGIN
 stock
-------
    19
(1 row)

UPDATE 1
COMMIT
   name    | stock
-----------+-------
 USB-Cハブ |    18
(1 row)

FOR UPDATE は「この行をこれから更新するので、他の人は触らずに待て」という行ロックです。在庫の確認と更新の間に他のトランザクションが割り込むと売り越しが起きるため、読んだ時点でロックを取ります。2つ目のターミナルで同じ SELECT ... FOR UPDATE を打つと、こちらがCOMMITするまで待たされるのを観察できます。

Q49

今のセッションのデフォルトのトランザクション分離レベルを確認してください。

解答を見る

SHOW default_transaction_isolation;

 default_transaction_isolation
-------------------------------
 read committed
(1 row)

PostgreSQLのデフォルトは read committed（コミット済みのデータだけが見える）です。#9 で学んだとおり、多くのアプリはこのままで問題ありませんが、「自分のトランザクション中に他人のコミットが見えることがある」性質だけは覚えておいてください。

Q50

【知識問題】「注文確定のトランザクションの中で、決済代行の外部APIを呼ぶ」実装はなぜ危険ですか。#9 の言葉で説明してください。

解答を見る

外部APIの呼び出しは何秒かかるか分からず、失敗もします。それをトランザクションの中で呼ぶと、応答を待っている間ずっとDBのロックと接続を握り続け、他の処理を巻き添えにします。さらに「APIは成功したのにDBはROLLBACKされた」が起きると、決済だけ通って注文が無い、という取り返しのつかない不整合になります。トランザクションは短く、中に入れてよいのはDB操作だけ。外部APIは外で呼び、結果をあらためて記録します（#9 の🔴ルールそのものです）。

## まとめ

50本おつかれさまでした。間違えた問題は数日おいてもう一周してください。「見れば分かる」と「書ける」の差は、この反復でしか埋まりません
特にQ23・Q25（LEFT JOINでの不在探し）、Q32（UPDATE前のSELECT）、Q46（RETURNINGとトランザクション）は実務でそのまま使う型です
Q44・Q45のように「教科書どおりにならない」場面に出会ったら、EXPLAIN ANALYZEで確かめる。この習慣がある人とない人の差は、現場で大きく開きます

後片付けは次の1行です（演習用テーブルを全部消します）。

DROP TABLE IF EXISTS order_items, orders, products, customers, big_orders CASCADE;

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up