つくる DDL一式完成
設計が動くことを確かめる
新しいことは出てきません。第5章までに決めたすべて、つまり構造・制約・名前・型・監査列を反映した DDL 一式が用意してあります。今回やるのは、そこにサンプルデータを流し込んで、設計どおりに動くことを確かめることです。
設計書の上では正しく見えても、実際にデータを入れてみると初めて気づくことがあります。
このテーブル、注文と一緒に入れる値が足りない。 この制約、正しいデータまで弾いてしまう。
これを本番の直前に見つけるか、設計の途中で見つけるかで、直す手間は桁違いです。サンプルデータを流すのは、設計のテストです。
流す順番も依存で決まる
作成順と同じ理由で、INSERT にも順番があります。参照先の行が無ければ、外部キーに弾かれます。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
INSERT INTO loans VALUES (1, 1, 1, DATE '2026-04-01');
-- ERROR insert or update on table "loans" violates foreign key constraintこのエラーが出たら、設計が間違っているのではなく流す順番が間違っているだけです。マスタから先に、明細を最後に、と覚えます。
監査列は書かない
監査列には DEFAULT now() が付いているので、INSERT で値を渡す必要がありません。渡さない列がある場合は、列名を明示した書き方を使います。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
INSERT INTO branches (id, name, pref) VALUES (1, '西館', '東京都');列名を省いた INSERT INTO stores VALUES (...) はすべての列を定義順に渡すという意味なので、監査列まで渡さなければなりません。列を増やすたびに壊れるので、本番の INSERT では列名を必ず書きます。
確かめ方は集計で
データを入れたら、業務で実際に使う集計を1本流します。行が入ったことより、欲しい答えが出せることのほうが重要です。店舗別の売上が出せなければ、そもそも何のための設計だったのかという話になります。
結合した明細を集計する
図書館の例で、貸出先と料金明細をつなぐと図書館ごとの料金が求まります。
SQL クエリ
SELECT l.name AS library, sum(f.days * f.daily_fee) AS fee
FROM libraries l
JOIN borrowings b ON b.library_id = l.id
JOIN rental_fees f ON f.loan_id = b.id
GROUP BY l.name
ORDER BY fee DESC;JOIN の土台で明細を並べ、GROUP BY で図書館ごとにまとめます。sum の中で各明細の金額を計算し、その合計で並べ替えます。演習では投入と結合を用意し、合計とまとめ方を補います。
手を動かす
サンプルデータを順番に流し、店舗別の売上を出します。
完成した DDL に対して、用意済みのサンプルデータを依存の順番で流します。列名を明示した INSERT はひな形にあります。
prefectures (pref, region)—('東京都', '関東'),('大阪府', '関西')categories (id, name)—(1, 'PC周辺'),(2, '家具')customers (id, name, email, joined_on)—(1, '田中あかり', 'akari@example.com', DATE '2026-01-10'),(2, '佐藤けんじ', 'kenji@example.com', DATE '2026-02-20')stores (id, name, pref)—(1, '渋谷店', '東京都'),(2, '梅田店', '大阪府')products (id, name, price, is_active, category_id)—(1, 'ノートPC', 128000, true, 1),(2, 'マウス', 3200, true, 1),(3, 'デスク', 24000, true, 2)orders (id, store_id, customer_id, ordered_on)—(1, 1, 1, DATE '2026-04-01'),(2, 2, 2, DATE '2026-04-02')order_items (order_id, product_id, quantity, unit_price)—(1, 1, 1, 128000),(1, 2, 2, 3200),(2, 3, 1, 24000)
仕上げに、店舗ごとの売上を出してください。stores と orders と order_items を繋ぎ、店舗名 store と quantity * unit_price の合計 total の2列を、total の大きい順に取り出します。
サンプル投入、結合、列の順番は用意済みです。明細金額の合計を補い、店舗ごとにまとめ、売上の大きい順に並べます。 ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 3 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE prefectures (
pref text PRIMARY KEY,
region text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE categories (
id integer PRIMARY KEY,
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL,
joined_on date NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT customers_email_uk UNIQUE (email)
);
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL,
pref text NOT NULL REFERENCES prefectures (pref),
opened_on date,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price integer NOT NULL,
is_active boolean NOT NULL,
category_id integer NOT NULL REFERENCES categories (id),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT products_price_ck CHECK (price > 0)
);
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL REFERENCES stores (id),
customer_id integer NOT NULL REFERENCES customers (id),
ordered_on date NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders (id),
product_id integer NOT NULL REFERENCES products (id),
quantity integer NOT NULL,
unit_price integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (order_id, product_id),
CONSTRAINT order_items_qty_ck CHECK (quantity > 0)
);
期待される出力
| store | total |
|---|---|
| 渋谷店 | 134400 |
| 梅田店 | 24000 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い