サブクエリ・ビュー演習

• 日程: Day 2 / セッション 8
• 時間: 14:00-14:30
• 形式: 実習
• ゴール: 「平均給与より高い社員」のクエリと、よく使う集計を呼び出せるビュー1つを作成できる
• 学習形式: ハンズオン実習（AIペアプログラミング）

前のセッションで「サブクエリは値・表・真偽の3つを返す道具」「ビューは名前付きのSELECT」と整理しました。ここからは実際に手を動かします。

これは、いわばスポーツでフォームを教わった後の素振りの時間です。座学で頭に入れた構文は、自分の手で打ち込んで初めて身につきます。エラーが出たら大歓迎、AIに聞きながらどんどん試してみましょう。

ターミナルで psql に接続して始めます。

docker exec -it postgres-sandbox psql -U postgres -d sandbox

全社員の平均給与より給与が高い社員を、姓・名・給与を表示してください。

• 平均給与は SELECT AVG(salary) FROM employees; で出る
• それを WHERE 句に ( ) で埋め込む

SELECT last_name, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

実行したら、結果の件数と「平均給与より上の人」の名前を1〜2人記憶しておくと、後で検算しやすいです。

注文を1件以上している顧客の customer_name だけを取り出してください。

• EXISTS (SELECT 1 FROM orders WHERE ...) を使う
• 外側の customers と内側の orders を customer_id で対応させる（相関サブクエリ）

SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
ORDER BY customer_name;

同じ結果をJOINでも書けます。書いて結果を比べてみましょう。

SELECT DISTINCT c.customer_name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_name;

「件数は同じ？」「どちらが読みやすい？」をAIと議論してみてください。

部署名と平均給与、所属人数を返すビュー v_dept_avg_salary を作成し、平均給与の降順で表示してください。

• CREATE VIEW ビュー名 AS SELECT ...; の形
• departments と employees を department_id でJOIN
• GROUP BY は部署名で

CREATE VIEW v_dept_avg_salary AS
SELECT
    d.department_name,
    AVG(e.salary) AS avg_salary,
    COUNT(*) AS member_count
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

SELECT * FROM v_dept_avg_salary ORDER BY avg_salary DESC;

作り直したいときは DROP VIEW v_dept_avg_salary; で消してから再作成できます。

各部署の平均給与を出して、その中で「全社員平均」よりも高い部署だけを表示してください。

SELECT department_name, avg_salary
FROM (
    SELECT d.department_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    GROUP BY d.department_name
) AS dept_avg
WHERE avg_salary > (SELECT AVG(salary) FROM employees)
ORDER BY avg_salary DESC;

スカラーサブクエリとインラインビューが1つの文の中に同居しています。「カッコの中から先に読む」を意識すると流れがつかめます。

• 課題1〜3のSQLと実行結果（4はチャレンジ）
• 作成したビュー v_dept_avg_salary
• 「サブクエリで書く／JOINで書く／ビューにする」の使い分けを、自分の言葉で2〜3行メモ

サブクエリは「カッコの中身が値か表か真偽か」を意識すると迷いません。ビューは作るのも壊すのも簡単で、よく使う集計に名前を付けておくだけで、後の自分や同僚が助かります。

エラーで詰まったら遠慮なくAIに「このSQLを実行したらこういうエラーが出た。理由と直し方を教えて」と聞いてみてください。エラー文を貼るのが一番早いコツです。

• 課題1のクエリを、明日見ても自分で再現できますか？
• ビューを DROP して作り直す手順は説明できますか？
• 「JOINでもサブクエリでも書ける」場合、自分はどちらを選ぶかの基準を持てましたか？

• 参考リンク: PostgreSQL公式 - CREATE VIEW
• 発展課題: 「注文金額が10万円以上の注文」を返すビュー v_high_value_orders を作る。さらにそれを使って「高額注文を出した顧客の社名一覧」を取り出してみる