Q0: 復習
Q. 2021年度と2020年度の調査結果が含まれる
populationテーブルを⽤いて，都道府県別の
⼤学学⽣数の平均値を算出するSQL⽂を書け．
SELECT
地域コード,
都道府県,
AVG(⼤学学⽣数) AS 平均学⽣数
FROM
population
GROUP BY
地域コード;
A.
Slide 7
Slide 7 text
本⽇のお題
この結果を
平均大学生数
で並び替えて
上位10位を
知りたい…
Slide 8
Slide 8 text
結果がテーブルなら
population_avgテーブル
SELECT
*
FROM
population_avg
ORDER BY DESC
平均⼤学⽣数
LIMIT 10;
population_avgテーブルがあればいいのに…
Slide 9
Slide 9 text
副問い合わせ (1/2)
SELECT *
FROM
ORDER BY DESC 平均⼤学⽣数
LIMIT 10;
population_avg
これを求めるSQL⽂に
置き換える
SQL文の中で別のSQL文の実行結果を参照
（別のSQL⽂の結果を⼀時的に保持しておき後で参照する）
Slide 10
Slide 10 text
副問い合わせ (2/2)
SQL文の中で別のSQL文の実行結果を参照
（別のSQL⽂の結果を⼀時的に保持しておき後で参照する）
SELECT *
FROM
ORDER BY DESC 平均⼤学⽣数
LIMIT 10;
カッコで包んだ別のSQLで置き換えた
( SELECT
地域コード, 都道府県,
AVG(⼤学学⽣数) AS 平均学⽣数
FROM population
GROUP BY 地域コード )
Slide 11
Slide 11 text
結果例
元のテーブルからSQL⼀発で
複雑な処理結果を得られる
Slide 12
Slide 12 text
副問い合わせ (2/2)
SQL文の中で別のSQL文の実行結果を参照
（別のSQL⽂の結果を⼀時的に保持しておき後で参照する）
SELECT *
FROM
ORDER BY DESC 平均⼤学⽣数
LIMIT 10;
( SELECT
地域コード, 都道府県,
AVG(⼤学学⽣数) AS 平均学⽣数
FROM population
GROUP BY 地域コード )
SELECT⽂の中に埋め込まれると読みづらい…
Slide 13
Slide 13 text
WITH句
WITH句を使って副問い合わせを外出しできる
SELECT *
FROM
( SELECT
地域コード,
都道府県,
AVG(⼤学学⽣数)
AS 平均学⽣数
FROM population
GROUP BY
地域コード )
ORDER BY DESC
平均⼤学⽣数
LIMIT 10;
SELECT *
FROM population_avg
ORDER BY DESC
平均⼤学⽣数
LIMIT 10;
=
WITH句を使えば副問い合わせSQLの可読性UP
WITH population_avg AS
( SELECT
地域コード, 都道府県,
AVG(⼤学学⽣数)
AS 平均学⽣数
FROM population
GROUP BY 地域コード
)
Q1: 副問い合わせ（1/2）
Q. populationテーブルにおいて調査年度が2021時点
の⼩学校児童数上位20件のレコードを求め，それら
とactivityテーブルの内容を結合した内容を表⽰す
るSQL⽂を書け．
WITH top20 AS (
SELECT
地域コード
FROM population
WHERE 調査年度 = 2021
ORDER BY ⼩学校児童数 DESC
LIMIT 20
)
SELECT * FROM activity
INNER JOIN
top20 USING(地域コード);
A.
IN演算⼦ & NOT IN演算⼦
SELECT * FROM activity
WHERE 都道府県 IN ( “愛知県”, “岐⾩県”) ;
IN演算子
リストアップした要素集合に値が含まれていればTRUE
SELECT * FROM activity
WHERE 都道府県 NOT IN ( “愛知県”, “岐⾩県”) ;
NOT IN演算子
リストアップした要素集合に値が含まれていなければTRUE
Slide 22
Slide 22 text
副問い合わせをWHERE句で使う (1/2)
SELECT
activity.都道府県,
activity.旅⾏・⾏楽
FROM activity
activity.都道府県 NOT IN
(65歳以上⼈⼝数がTOP10の都道府県リスト)
AND activity.旅⾏・⾏楽 >= 50;
elderly_population_top10テーブルにおいて65歳
以上⼈⼝数の上位10件に「⼊っていない」都道府県
について，activityテーブルで過去1年以内に
「旅⾏・⾏楽」活動をしたことのある⼈の割合が
5割を超える都道府県と割合を表⽰するSQL⽂は？
Slide 23
Slide 23 text
副問い合わせをWHERE句で使う (2/2)
SELECT
activity.都道府県,
activity.旅⾏・⾏楽
FROM activity
activity.都道府県 NOT IN
( SELECT 都道府県
FROM elderly_population_top10 )
AND activity.旅⾏・⾏楽 >= 50;
elderly_population_top10テーブルにおいて65歳
以上⼈⼝数の上位10件に「⼊っていない」都道府県
について，activityテーブルで過去1年以内に
「旅⾏・⾏楽」活動をしたことのある⼈の割合が
5割を超える都道府県と割合を表⽰するSQL⽂は？
副問い合わせ
Q1: 副問い合わせ（1/2）
WITH top20 AS (
SELECT
地域コード
FROM population
WHERE 調査年度 = 2021
ORDER BY ⼩学校児童数 DESC
LIMIT 20
)
SELECT
*
FROM
activity
WHERE
activity.地域コード IN top20;
A.
Q2: 副問い合わせ（2/2）
WITH top10 AS (
SELECT 地域コード
FROM activity
ORDER BY ボランティア DESC
LIMIT 10
)
SELECT
都道府県,
AVG(総⼈⼝) AS 平均総⼈⼝
FROM
population
JOIN
top10 USING(地域コード)
GROUP BY
都道府県;
A.
Slide 28
Slide 28 text
これまでに学んだデータ問い合わせのためのSQL
lSELECT
lWHERE
lORDER BY
lGROUP BY
l集合演算 (UNION, INTERSECTIONなど)
l結合演算 (直積, INNER JOIN, OUTER JOIN)
l副問い合わせ
lWITH句
lAS修飾句，IN述語