SQL 実践 + 応用情報午後対策

対象読者: ORMの利用経験を、生SQLの理解とデータベース問題の解決へつなげたい人。

1. Room の知識を生 SQL に対応させる

// Room（Kotlin）でやっていたこと
@Query("SELECT * FROM books WHERE status = :status ORDER BY updatedAt DESC")
fun getBooksByStatus(status: String): Flow<List<BookEntity>>

// 生 SQL で書くと
SELECT * FROM books 
WHERE status = 'reading' 
ORDER BY updated_at DESC;

2. 応用情報頻出の SQL パターン

GROUP BY + 集計関数

-- 各ステータスの書籍数と平均評価を取得
SELECT 
    status,
    COUNT(*)        AS book_count,
    AVG(rating)     AS avg_rating,
    MAX(updated_at) AS last_updated
FROM books
GROUP BY status
HAVING COUNT(*) >= 5  -- HAVING: GROUP BY後のフィルタ（WHERE とは違う）
ORDER BY book_count DESC;

JOIN（応用情報で最頻出）

-- 本・棚・タグを扱うサンプルアプリの構造をSQLで表現
-- BookShelfCrossRef, BookTagCrossRef は中間テーブル

-- INNER JOIN: 両方に存在するもの
SELECT b.title, s.shelf_name
FROM books b
INNER JOIN book_shelf_cross_ref bsc ON b.id = bsc.book_id
INNER JOIN shelves s                ON bsc.shelf_id = s.id;

-- LEFT JOIN: 左テーブルは全件（棚未登録の本も表示）
SELECT b.title, s.shelf_name
FROM books b
LEFT JOIN book_shelf_cross_ref bsc ON b.id = bsc.book_id
LEFT JOIN shelves s                ON bsc.shelf_id = s.id;

-- 棚に登録されていない本を探す（NOT EXISTS）
SELECT b.title
FROM books b
WHERE NOT EXISTS (
    SELECT 1 FROM book_shelf_cross_ref bsc 
    WHERE bsc.book_id = b.id
);

サブクエリ

-- 平均評価より高い本の一覧
SELECT title, rating
FROM books
WHERE rating > (SELECT AVG(rating) FROM books)
ORDER BY rating DESC;

-- 各プラットフォームで最も最近更新された本
SELECT b.*
FROM books b
WHERE b.updated_at = (
    SELECT MAX(updated_at) 
    FROM books b2 
    WHERE b2.platform = b.platform
);

-- WITH句（CTE）で可読性を高める
WITH monthly_stats AS (
    SELECT 
        DATE_TRUNC('month', completed_at) AS month,
        COUNT(*) AS completed_count
    FROM books
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', completed_at)
)
SELECT month, completed_count,
       SUM(completed_count) OVER (ORDER BY month) AS cumulative  -- ウィンドウ関数
FROM monthly_stats;

3. トランザクションと整合性

-- ACID特性を保証するトランザクション
BEGIN;

-- 本の移動（棚Aから棚Bへ）
DELETE FROM book_shelf_cross_ref 
WHERE book_id = 1 AND shelf_id = 10;

INSERT INTO book_shelf_cross_ref (book_id, shelf_id) 
VALUES (1, 20);

-- エラーがなければコミット
COMMIT;
-- エラー時: ROLLBACK;

分離レベル（応用情報頻出）

• ダーティリード: コミット前のデータを読む
• 反復不能読み取り: 同一トランザクション内で同じ行が変わる
• ファントムリード: 同一トランザクション内で行数が変わる

4. インデックス設計

-- 通常のBツリーインデックス
CREATE INDEX idx_books_status ON books(status);
CREATE INDEX idx_books_platform_status ON books(platform, status);  -- 複合インデックス

-- 検索がどのインデックスを使っているか確認
EXPLAIN ANALYZE
SELECT * FROM books WHERE platform = 'kindle' AND status = 'reading';

-- インデックスを使わない書き方（注意）
SELECT * FROM books WHERE LOWER(title) = 'clean code';  -- ❌ 関数でラップするとインデックス不使用
SELECT * FROM books WHERE title LIKE '%clean%';          -- ❌ 前方一致でないとBツリーは使えない
SELECT * FROM books WHERE title LIKE 'Clean%';           -- ✅ 前方一致はBツリー使用可能

5. 正規化（応用情報必須）

第1〜第3正規形

【非正規形（問題あり）】
books: id, title, author1, author2, tag1, tag2, tag3  ← 繰り返しグループ

【第1正規形（繰り返しを排除）】
books: id, title
book_authors: book_id, author_name
book_tags: book_id, tag_name

【第2正規形（部分関数従属を排除）】
複合キー(book_id, tag_id) がある場合、
tag_nameがtag_idのみに従属 → tagsテーブルに分離

【第3正規形（推移的関数従属を排除）】
book_id → publisher_id → publisher_name の推移的従属
→ publishersテーブルに分離

6. 応用情報 午後 DB 問題の解き方

よく出るパターン

1. E-R図を読んでSQL を書く
   → リレーションシップを JOIN 条件に変換する練習

2. 依存関係を見てテーブル設計の問題点を指摘する
   → 正規化の第2・第3正規形の違いを即答できるようにする

3. トランザクションの問題（デッドロック等）
   → ロック順序を統一することで防止できると答えられるようにする

4. インデックスの効果を答える
   → ORDER BY, WHERE, JOIN の条件列にインデックスが効く

練習問題例

-- 問: 以下のテーブルで「2026年に完読した本の著者ごとの冊数を多い順に表示」するSQL

-- テーブル: books(id, title, status, completed_at)
--           book_authors(book_id, author_id)  
--           authors(id, name)

SELECT 
    a.name AS author_name,
    COUNT(*) AS completed_count
FROM books b
INNER JOIN book_authors ba ON b.id = ba.book_id
INNER JOIN authors a ON ba.author_id = a.id
WHERE b.status = 'completed'
  AND EXTRACT(YEAR FROM b.completed_at) = 2026
GROUP BY a.id, a.name
ORDER BY completed_count DESC;

7. PostgreSQL / MySQL 実践（RDSで使う）

-- PostgreSQL でよく使う機能
-- JSONB 型（半構造データ）
ALTER TABLE books ADD COLUMN metadata JSONB;
SELECT * FROM books WHERE metadata->>'isbn' = '9784873115658';
CREATE INDEX idx_metadata_isbn ON books ((metadata->>'isbn'));

-- 全文検索
CREATE INDEX idx_books_fts ON books USING gin(to_tsvector('japanese', title));
SELECT * FROM books WHERE to_tsvector('japanese', title) @@ to_tsquery('japanese', 'クリーン');

-- パーティショニング（大規模テーブル）
CREATE TABLE logs (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    message TEXT
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2026 PARTITION OF logs
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

🔗 次のステップ

• 02_RDB設計パターン.md — スキーマ設計・パフォーマンスチューニング
• DBセキュリティ — SQLインジェクション対策（今後追加予定）
• AWSデータベースサービス — RDS・Aurora の運用