5. 結合質問(集合演算を含む)#

Hide code cell source
import os

if not os.path.isfile('data/SSDSE.db'):
    os.makedirs('data', exist_ok=True)

    if not os.path.isfile('download_SSDSE.py'):
        !wget https://raw.githubusercontent.com/hontolab-courses/database-lecturenote/main/content/sql/download_SSDSE.py
        import download_SSDSE
Hide code cell source
%load_ext sql
%config SqlMagic.feedback = 0
%config SqlMagic.displaylimit = 50
%sql sqlite:///data/SSDSE.db

前講で扱ったSQL文は,1つのテーブルに対する問い合わせ(単純質問)を対象としていた. しかし,関係データベースを用いたデータ分析の現場では,問い合わせ時に参照するテーブルが1つしかないことは稀である. 所望のデータを抽出するには,複数のテーブルを組み合わせることが大半である. 本章では,関係データベース内にある2つ以上のテーブルを組み合わせてデータを抽出する結合質問を扱う.

前講同様,SQLの説明を行うために,独立行政法人統計センターが公開している教育用標準データセット(SSDSE)基本素材(SSDSE-E)および市区町村(SSDSE-A)から抜粋・加工したデータを用いる. データはelderly_population_top10テーブル,university_student_population_top10テーブル,およびactivityテーブルの3種類ある.

各テーブル内のデータは2021年度調査によるものである. elderly_population_top10テーブルには,65歳以上人口数が上位10位内であった都道府県の「地域コード」「都道府県名」「総人口」が格納されている.

SELECT * FROM elderly_population_top10; 
地域コード 都道府県 総人口
R13000 東京都 14010000
R27000 大阪府 8806000
R14000 神奈川県 9236000
R11000 埼玉県 7340000
R23000 愛知県 7517000
R12000 千葉県 6275000
R01000 北海道 5183000
R28000 兵庫県 5432000
R40000 福岡県 5124000
R22000 静岡県 3608000

university_student_population_top10テーブルには,大学学生数が上位10位内であった都道府県の「地域コード」「都道府県名」「総人口」が格納されている.

SELECT * FROM university_student_population_top10; 
地域コード 都道府県 総人口
R13000 東京都 14010000
R27000 大阪府 8806000
R23000 愛知県 7517000
R14000 神奈川県 9236000
R26000 京都府 2561000
R28000 兵庫県 5432000
R40000 福岡県 5124000
R11000 埼玉県 7340000
R12000 千葉県 6275000
R01000 北海道 5183000

activityテーブルには,「学習・自己啓発・訓練」「スポーツ」「趣味・娯楽」「ボランティア」「旅行・行楽」について過去1年以内に活動したことのある人の割合(%)について,都道府県別にまとめたものが格納されている.

SELECT * FROM activity; 
地域コード 都道府県 学習・自己啓発・訓練 スポーツ 趣味娯楽 ボランティア 旅行・行楽
R01000 北海道 35.0 62.2 85.4 16.3 51.0
R02000 青森県 25.4 52.1 78.6 14.0 36.6
R03000 岩手県 28.9 59.1 82.9 24.4 45.3
R04000 宮城県 37.5 64.4 87.0 20.7 52.2
R05000 秋田県 29.2 57.1 82.1 20.9 44.8
R06000 山形県 31.1 58.4 82.4 23.6 44.3
R07000 福島県 31.5 59.5 81.5 19.7 44.4
R08000 茨城県 36.5 65.8 85.3 17.1 45.0
R09000 栃木県 32.8 62.6 83.9 16.5 47.3
R10000 群馬県 35.2 67.0 85.2 19.2 50.8
R11000 埼玉県 40.0 69.3 88.4 15.6 51.5
R12000 千葉県 42.0 67.4 87.0 14.9 47.9
R13000 東京都 52.8 74.5 91.4 15.5 55.5
R14000 神奈川県 46.7 71.8 89.2 17.0 54.5
R15000 新潟県 34.5 59.0 84.0 19.3 47.6
R16000 富山県 33.4 59.7 82.6 19.5 41.1
R17000 石川県 35.6 63.4 86.2 20.7 44.7
R18000 福井県 35.8 61.5 85.2 21.7 44.3
R19000 山梨県 36.5 62.2 83.1 21.1 46.7
R20000 長野県 36.6 64.0 84.8 22.0 46.9
R21000 岐阜県 36.1 64.1 84.9 21.7 48.6
R22000 静岡県 36.5 66.3 85.1 18.9 41.3
R23000 愛知県 39.0 68.8 89.3 16.6 57.6
R24000 三重県 34.4 64.3 84.2 19.3 47.0
R25000 滋賀県 39.7 67.2 86.4 24.2 52.2
R27000 大阪府 41.3 66.1 86.7 14.5 51.9
R28000 兵庫県 39.5 66.4 85.4 17.7 51.6
R29000 奈良県 39.8 64.8 86.2 18.5 50.4
R30000 和歌山県 31.3 59.8 83.3 18.2 44.1
R31000 鳥取県 34.1 61.2 81.7 24.1 38.4
R32000 島根県 33.3 61.4 82.5 25.6 39.8
R33000 岡山県 37.1 64.8 85.0 23.2 43.0
R34000 広島県 38.4 65.3 85.1 18.6 47.1
R35000 山口県 34.8 63.2 83.9 20.1 43.5
R36000 徳島県 34.1 60.9 80.6 17.4 32.2
R37000 香川県 34.1 61.8 82.9 18.1 39.0
R38000 愛媛県 34.5 64.0 84.3 19.6 37.4
R39000 高知県 30.3 59.4 81.7 18.9 39.2
R40000 福岡県 37.7 67.0 85.8 19.1 52.3
R41000 佐賀県 37.2 62.8 82.1 24.8 47.5
R42000 長崎県 31.2 57.8 79.7 21.0 36.1
R43000 熊本県 35.7 66.6 84.1 22.1 50.5
R44000 大分県 33.2 63.1 82.3 20.3 45.0
R45000 宮崎県 31.3 65.8 81.6 20.1 42.8
R46000 鹿児島県 35.2 63.5 81.0 22.3 43.3
R47000 沖縄県 36.6 65.8 80.0 17.4 31.1

以下,これら3つのテーブルが格納された関係データベースが手元にあると想定して,結合質問について説明する.

5.1. 集合演算#

高校数学で学習した「集合と論理」を覚えておられるだろうか(忘れた人はコチラを確認). そのときに

\(A=\{1, 2, 3, 4, 9\}\)\(B=\{3, 4, 7\}\)のとき,AとBの共通部分\(A \cap B\)\(\{3, 4\}\)となる

みたいなことを勉強したと思う. 集合演算とは,上記のように2つの集合から別の集合を作り出す演算である. 高校数学で習った集合演算では主として数値の集合を対象としていたと思うが, 関係データベースの集合演算において対象となるのはレコード(行)の集合である. 関係データベースにおける集合演算は行を増やしたり減らしたりする,テーブルを縦方向にいじる操作ともいえる. 関係データベースにおける集合演算の振る舞いは,直感的には高校数学の集合演算と変わらない.

Warning

関係データベースにおける集合演算の注意点

関係データベースで集合演算を行う際には,演算対象となる2つのレコード集合が和両立(unicon compatible, 合併可能と呼ぶこともある)という条件を満たしている必要がある. これは,演算対象となる2つのレコード集合が

  1. 同じ列(属性)で構成されている,かつ

  2. 同じ列については,その定義域も同じである

という条件である. この条件を満たしていないレコード集合の集合演算を行おうとすると,SQL文を実行してもエラーが出力される.

5.1.1. 和集合演算(UNION)#

和集合演算は,2つのレコード集合のいずれかに含まれるレコードの集合(和集合または合併,union)を求めるものである. 高校数学で例えると,

\(A=\{1, 2, 3, 4, 9\}\)\(B=\{3, 4, 7\}\)のとき,AとBの和集合\(A \cup B\)\(\{1, 2, 3, 4, 7, 9\}\)

となるような演算の関係データベース版である.

SQLで和集合を求めるにはUNION演算子を用いる. レコード集合Aとレコード集合Bの和集合を求めるには,A UNION BのようなSQL文を書く. \(A\)\(B\)はレコード集合であるので,実際にSQL文でUNIONを使う際には,AやBの箇所にSELECT文が入る

以下は,elderly_population_top10テーブルとuniversity_student_population_top10テーブルの和集合を求めるSQL文である.

SELECT * FROM elderly_population_top10
UNION
SELECT * FROM university_student_population_top10; 
地域コード 都道府県 総人口
R01000 北海道 5183000
R11000 埼玉県 7340000
R12000 千葉県 6275000
R13000 東京都 14010000
R14000 神奈川県 9236000
R22000 静岡県 3608000
R23000 愛知県 7517000
R26000 京都府 2561000
R27000 大阪府 8806000
R28000 兵庫県 5432000
R40000 福岡県 5124000

なお,(後述する積集合,差集合も同様であるが)和集合演算子UNIONの前後にはSELECT文であればよく,当然SELECT文の中にはWHERE句などを含んでいてもよい. 以下は,elderly_population_top10テーブルとuniversity_student_population_top10テーブルのレコードのうち,総人口が700万以上のレコードに限定して和集合を求めるSQL文である.

SELECT
    *
FROM
    elderly_population_top10
WHERE
    総人口 >= 7000000
UNION
SELECT
    *
FROM
    university_student_population_top10
WHERE
    総人口 >= 7000000; 
地域コード 都道府県 総人口
R11000 埼玉県 7340000
R13000 東京都 14010000
R14000 神奈川県 9236000
R23000 愛知県 7517000
R27000 大阪府 8806000

5.1.2. 積集合演算(INTERSECT)#

積集合演算は,2つのレコード集合に共通して含まれるレコードの集合(積集合または共通集合,intersection)を求めるものである. 高校数学で例えると,

\(A=\{1, 2, 3, 4, 9\}\)\(B=\{3, 4, 7\}\)のとき,AとBの積集合\(A \cap B\)\(\{3, 4\}\)

となるような演算の関係データベース版である.

SQLで積集合を求めるにはINTERSECT演算子を用いる. 以下は,elderly_population_top10テーブルとuniversity_student_population_top10テーブルの積集合を求めるSQL文である(★Quiz1★★Quiz2★).

SELECT * FROM elderly_population_top10
INTERSECT
SELECT * FROM university_student_population_top10; 
地域コード 都道府県 総人口
R01000 北海道 5183000
R11000 埼玉県 7340000
R12000 千葉県 6275000
R13000 東京都 14010000
R14000 神奈川県 9236000
R23000 愛知県 7517000
R27000 大阪府 8806000
R28000 兵庫県 5432000
R40000 福岡県 5124000

5.1.3. 差集合演算(EXCEPT)#

差集合演算は,あるレコード集合から別のレコード集合に含まれるレコードを除いたレコードの集合(差集合あるいは補集合,set difference)を求めるものである. 高校数学の範囲で例えると,

\(A=\{1, 2, 3, 4, 9\}\)\(B=\{3, 4, 7\}\)のとき,AとBの積集合\(A \setminus B\)\(\{1, 2, 9\}\)

となるような演算の関係データベース版である. 差集合演算は,片側の集合にしか現れない要素の集合を求めたいときに用いる.

SQLで積集合を求めるにはEXCEPT演算子を用いる. 以下は,elderly_population_top10テーブルからuniversity_student_population_top10テーブルを引いた差集合を求めるSQL文である.

SELECT * FROM elderly_population_top10
EXCEPT
SELECT * FROM university_student_population_top10; 
地域コード 都道府県 総人口
R22000 静岡県 3608000

和集合演算や積集合演算とは異なり,差集合演算は演算の項の順序を入れ替えても結果は変わらない「交換法則」は成立しない. つまり,\(A \setminus B\)\(B \setminus A\)はイコールになるとは限らない.

例えば,以下は上で求めた例とは演算の項を入れ替えた,university_student_population_top10テーブルからelderly_population_top10テーブルを引いた差集合を求めるSQL文である. このSQL文と先のSQL文とで結果が異なることが分かる.

SELECT * FROM university_student_population_top10
EXCEPT
SELECT * FROM elderly_population_top10; 
地域コード 都道府県 総人口
R26000 京都府 2561000

5.2. 結合(JOIN)#

結合(JOIN) とは,2つのテーブルからデータを特定の条件でデータを組み合わせて新たなレコード集合を得る操作である. 前節で扱ったUNIONINTERSECTEXCEPTといった集合演算がテーブルを縦方向(行方向)に操作するのに対して,結合操作はテーブルを横方向(列方向) に操作する. データをあえて複数のテーブルに分けて管理する思想をもつ関係データベースにおいては,結合操作を使いこなすことが多様かつ膨大なデータの管理・検索・分析を行う上で鍵となる.

以下,直積,内部結合,外部結合の3つの結合について説明する.

5.2.1. 直積(CROSS JOIN)#

集合論において,集合AとBの直積(デカルト積とも呼ぶ) とは,集合Aと集合Bのそれぞれの要素のすべての組み合わせ(の集合)を意味する. 例えば,集合\(A=\{1, 2, 3\}\),集合\(B=\{a, b\}\)のとき,集合Aと集合Bの直積である\(A \times B\)

\(A \times B = \{(1, a), (1, b), (2, a), (2, b), (3, a), (3, b)\}\)

となる.

関係データベースにおいてもテーブル同士の直積が定義されている. 関係データベースにおける直積(交差結合; cross joinと呼ぶこともある) は,2つのテーブル\(R_1\)\(R_2\)が与えられたとき,テーブル\(R_1\)のレコードと\(R_2\)のレコードのすべての組み合わせを求める操作となる.

2つのテーブルAとBの直積を求めるSQL文は,

SELECT * FROM A CROSS JOIN B; 

と書く. 以下のように,上記SQL文はCROSS JOINをカンマ(,)を省略しても書くこともできる.

SELECT * FROM A, B;  -- CROSS JOINをカンマで代替

具体例を使って直積の結果を確認してみよう. 例えば,以下のような「科目」テーブルと「教員」テーブルの2つがあるとする.

科目名 開講時期
データベース 2年前期
機械学習 3年前期
統計モデリング 2年後期
氏名 職階
山畑 教授
桜山 教授
川澄 准教授
田辺 講師

「科目」テーブルには3レコード,「教員」テーブルには4レコード格納されている. 上記2つのテーブルのデータを元に,科目を担当する教員について考えられうるすべての組み合わせを求めるには, 以下のように2つのテーブルの直積を求めれるSQL文を書けばよい.

SELECT
    *
FROM
    科目, 教員;
科目名 開講時期 氏名 職階
データベース 2年前期 山畑 教授
データベース 2年前期 桜山 教授
データベース 2年前期 川澄 准教授
データベース 2年前期 田辺 講師
機械学習 3年前期 山畑 教授
機械学習 3年前期 桜山 教授
機械学習 3年前期 川澄 准教授
機械学習 3年前期 田辺 講師
統計モデリング 2年後期 山畑 教授
統計モデリング 2年後期 桜山 教授
統計モデリング 2年後期 川澄 准教授
統計モデリング 2年後期 田辺 講師

2つのテーブルの直積を求めると,合計\(3 \times 4=12\)レコードの組み合わせ(レコード集合)が得られる(★Quiz3★).

直積を求めるSQL文では,WHERE句を使って抽出結果を選択(絞り込む)ことができる.

  • 大学学生数が多い上位10の都道府県名が格納されたuniversity_student_population_top10テーブル,

  • 「学習・自己啓発・訓練」など,いくつかの活動について過去1年以内に活動したことのある人の割合(%)が都道府県別にまとめられているactivityテーブル

の直積を求めた後,都道府県名が一致しているものだけを残して表示してみよう. 以下は所望の結果を得るためのSQL文である.

Hide code cell source
%sql sqlite:///data/SSDSE.db
SELECT
    *
FROM
    university_student_population_top10, activity
WHERE
    --- 「テーブル名.都道府県」とすることで,どのテーブルの「都道府県」を参照したいかを明示
    university_student_population_top10.都道府県 = activity.都道府県; 
地域コード 都道府県 総人口 地域コード_1 都道府県_1 学習・自己啓発・訓練 スポーツ 趣味娯楽 ボランティア 旅行・行楽
R13000 東京都 14010000 R13000 東京都 52.8 74.5 91.4 15.5 55.5
R27000 大阪府 8806000 R27000 大阪府 41.3 66.1 86.7 14.5 51.9
R23000 愛知県 7517000 R23000 愛知県 39.0 68.8 89.3 16.6 57.6
R14000 神奈川県 9236000 R14000 神奈川県 46.7 71.8 89.2 17.0 54.5
R28000 兵庫県 5432000 R28000 兵庫県 39.5 66.4 85.4 17.7 51.6
R40000 福岡県 5124000 R40000 福岡県 37.7 67.0 85.8 19.1 52.3
R11000 埼玉県 7340000 R11000 埼玉県 40.0 69.3 88.4 15.6 51.5
R12000 千葉県 6275000 R12000 千葉県 42.0 67.4 87.0 14.9 47.9
R01000 北海道 5183000 R01000 北海道 35.0 62.2 85.4 16.3 51.0

Warning

複数のテーブルを参照するときの注意点

複数のテーブルに同一の列名が存在する際,SQL文内で列名だけを指定すると,どのテーブルの列を参照しているのかが明確でないためエラーが発生する. これを防ぐために,複数のテーブルを参照するときには,列名の前にテーブル名.をつけて列を参照するテーブル名を明確化すること.

5.2.2. 内部結合(INNER JOIN)#

内部結合(inner join) は,2つのテーブルにおいて指定した列の値が条件を満たすレコードのみを結合して抽出する操作である. 通常,結合あるいはジョインという時は内部結合を指す. テーブルAにテーブルBを内部結合するためのSQL文は

  1. FROM Aの後でINNER JOIN B(あるいは省略してJOIN B)と書き,

  2. INNER JOIN Bの後に2つのテーブルの結合条件をON句で指定する

というフォーマットになる.

例えば,前節「直積」で扱った,

  • 大学学生数が多い上位10の都道府県名が格納されたuniversity_student_population_top10テーブル,

  • 「学習・自己啓発・訓練」など,いくつかの活動について過去1年以内に活動したことのある人の割合(%)が都道府県別にまとめられているactivityテーブル

の2テーブルについて「都道府県名」で内部結合をしてみよう.

SELECT
    --- 「テーブル名.*」 で「そのテーブルにあるすべての列」を意味する
    university_student_population_top10.*, 
    --- 「テーブル名.*」でなく,「テーブル名.列名」と書くと,
    --- 該当テーブルの特定の列の情報だけを参照可能
    activity.学習・自己啓発・訓練
FROM
    university_student_population_top10
    INNER JOIN
        activity
    ON
        university_student_population_top10.都道府県 = activity.都道府県; 
地域コード 都道府県 総人口 学習・自己啓発・訓練
R13000 東京都 14010000 52.8
R27000 大阪府 8806000 41.3
R23000 愛知県 7517000 39.0
R14000 神奈川県 9236000 46.7
R28000 兵庫県 5432000 39.5
R40000 福岡県 5124000 37.7
R11000 埼玉県 7340000 40.0
R12000 千葉県 6275000 42.0
R01000 北海道 5183000 35.0

前節のSQL文と似たような結果が出力されたが,処理手順は異なる. 前節のSQL文はいったん直積ですべての組み合わせを求めた後,都道府県名が一致するものを調べて結果を表示している. これに対し,内部結合を用いた上記SQL文では,INNER JOIN句で指定した条件を満たす組み合わせのみを抽出し,その後それをすべて選択(表示)するという順で処理を行っている(★Quiz4★★Quiz5★).

Tip

USING句を使った省略記法

2つのテーブルを内部結合する際,結合するテーブルの列名が等しい(同じ列名で結合する)かつ結合条件が等号である場合は,以下のようにON句の代わりにUSING句を使って簡単に書くことができる. 以下は上記SQL文をUSING句を使って書き換えたものである.

SELECT
    university_student_population_top10.*, 
    activity.学習・自己啓発・訓練
FROM
    university_student_population_top10
    INNER JOIN
        activity
    USING(都道府県); 

(後述する外部結合も含めて)JOIN句とWHERE句を組み合わせる際には,JOINが行われる順序を意識することが重要となる. 例えば,先の内部結合の例題において「総人口が700万以上の都道府県に限定」したいとしよう. そのような場合,以下のようにJOIN句の後にWHERE句で選択条件を指定する. こうすることで,JOIN句で特定の条件でテーブルを結合した後,WHERE句で結合結果を絞り込むことができる.

SELECT
    university_student_population_top10.*, 
    activity.学習・自己啓発・訓練
FROM
    university_student_population_top10
    INNER JOIN
        activity
    ON
        university_student_population_top10.都道府県 = activity.都道府県
WHERE
    university_student_population_top10.総人口 >= 7000000; 
地域コード 都道府県 総人口 学習・自己啓発・訓練
R13000 東京都 14010000 52.8
R27000 大阪府 8806000 41.3
R23000 愛知県 7517000 39.0
R14000 神奈川県 9236000 46.7
R11000 埼玉県 7340000 40.0

Tip

AS修飾句

長たらしいテーブル名,列名を繰り返し使うのは面倒である. また紛らわしいテーブル名,列名は混乱の元である. AS修飾句を使うことで,テーブル名や列名に別名を与えることができる.

SELECT
    university_student_population_top10.*,
    --- 列名に別名「学習・自己鍛錬」を与える
    activity.学習・自己啓発・訓練 AS 学習・自己鍛錬
FROM
    ---テーブルに別名populationを与える
    university_student_population_top10 AS u_population 
    INNER JOIN
        activity
    ON
        u_population.都道府県 = activity.都道府県 --- 別名でテーブルを参照
WHERE
    u_population.総人口 >= 7000000; ---  別名でテーブルを参照

5.2.3. 外部結合(OUTER JOIN)#

内部結合は,片方のテーブル\(R_1\)にあるレコード\(r\)に対して,もう片方のテーブル\(R_2\)のレコードに結合条件を満たす列データがあるときに限ってレコード同士を結合し,その結果を表示するものであった. このことは,結合条件を満たすレコードがもう片方のテーブル\(R_2\)にない場合は\(r\)無視されることを意味する.

実は例題に用いたactivityテーブルには「京都府」に関するレコードがない(授業用にデータを用意するときに含めないようにしていた). そのため,内部結合の例題ではuniversity_student_population_top10テーブルとactivityテーブルを内部結合すると,「京都府」に関するレコードが結合結果に残らない(表示されない)のである.

このような挙動をする内部結合に対して,結合条件を満たさないレコードがあった時でもそのことを示す情報を付与したレコードを残しつつ,テーブル同士を結合する演算が外部結合(outer join) である. 外部結合では,結合条件を満たさないレコードがあった場合,値が存在しない列にNULL値(ヌル値) と呼ばれる「値がないこと」を示す情報を入れて結果を出力する.

テーブルAにテーブルBを外部結合するためのSQL文は

  1. FROM Aの後でLEFT OUTER JOIN Bと書き,

  2. LEFT OUTER JOIN Bの後に2つのテーブルの結合条件をON句で指定する

というフォーマットになる.

例を見てみよう. 以下は,内部結合の節で導入した1つ目のSQL文を外部結合に書き換えたものである.

SELECT
    university_student_population_top10.*, 
    activity.学習・自己啓発・訓練
FROM
    university_student_population_top10
    LEFT OUTER JOIN --- (左)外部結合に書き換え
        activity
    ON
        university_student_population_top10.都道府県 = activity.都道府県; 
地域コード 都道府県 総人口 学習・自己啓発・訓練
R13000 東京都 14010000 52.8
R27000 大阪府 8806000 41.3
R23000 愛知県 7517000 39.0
R14000 神奈川県 9236000 46.7
R26000 京都府 2561000 None
R28000 兵庫県 5432000 39.5
R40000 福岡県 5124000 37.7
R11000 埼玉県 7340000 40.0
R12000 千葉県 6275000 42.0
R01000 北海道 5183000 35.0

内部結合の例題とは異なり,「京都府」に関するレコードが表示され,「学習・自己啓発・訓練」列の値がNULLになっていることが分かる(上記結果ではNULLがNoneと表示されているが,NULLと読み替えてほしい). 外部結合は結合条件を満たすレコードがなかったとしても,それを把握したいときなどに利用できる.

なお,上記の例題からも分かるように,外部結合には左外部結合(left outer join)右外部結合(right outer join),および完全外部結合(full outer join) の3種類が存在する[1]. 左外部結合が結合対象の左テーブルのレコードを全て残すのに対して,

  • 右外部結合は(結合条件を満たさなかった場合でも)結合対象の右テーブルのレコードを全て残し,

  • 完全外部結合は(結合条件を満たさなかった場合でも)左右両方のテーブルのレコードを全て残す

演算となる. まずは左外部結合をマスターしよう(★Quiz6★★Quiz7★).

Note

NULLと空文字は異なる

NULLは値が存在しないことを意味する情報である. 文字列が入ることが想定される列にNULL値が入っている場合は「その列には何らかの理由で値が設定されなかった」ことを意味する. 一方,空文字('')は長さが0の文字列である. もし文字列が入ることが想定される列に空文字が入っている場合は「意図を持って文字がないことを示すために空文字が入れられた」ことを意味する.SQLではNULLと空文字と厳格に区別しているため,扱いには注意しよう.


5.3. クイズ#

本クイズでは,独立行政法人統計センターが公開している教育用標準データセット(SSDSE)の基本素材SSDSE-E(データの解説はこちら)および市区町村(SSDSE-A)から抜粋・加工したデータを用いる.

  • populationテーブルの内容については前章

  • university_student_population_top10テーブルの内容についてはコチラ

  • activityテーブルの内容についてはコチラ

を参照せよ.

なお,データをダウンロードしていない者は,以下のコードを実行してデータをダウンロードしてからクイズに取り組むこと.

import os

if not os.path.isfile('data/SSDSE.db'):
    os.makedirs('data', exist_ok=True)

    if not os.path.isfile('download_SSDSE.py'):
        !wget https://raw.githubusercontent.com/hontolab-courses/database-lecturenote/main/content/sql/download_SSDSE.py
        import download_SSDSE

%load_ext sql
%config SqlMagic.feedback = 0
%sql sqlite:///data/SSDSE.db

5.3.1. Q1. 復習#

前講で用いたpopulationテーブルにあるレコードのうち,大学学生数が7万以上かつ小学校児童数が30万以上の都道府県名を表示するSQL文を書け. なお,表示の際には都道府県名の重複は除くこと.

%%sql

5.3.2. Q2. 集合演算#

上記Q1と同様の内容を表示するSQL文をINTERSECT句を用いて書け.

%%sql

5.3.3. Q3. 直積#

populationテーブルとactivityテーブルの直積を求めるSQL文を書け.

%%sql

5.3.4. Q4. 内部結合#

populationテーブルとactivityテーブルをカラム「地域コード」が等しいという条件で内部結合を行うSQL文を書け. ただし,結果表示時には重複するフィールド(すなわち「地域コード」「都道府県名」)は除くこと.

%%sql

5.3.5. Q5. 直積再び#

上記Q4と同様の内容を表示するSQL文をJOINを用いずに書け.

%%sql

5.3.6. Q6. 外部結合(1/2)#

university_student_population_top10テーブルに対して「地域コード」が等しいという条件でactivityテーブルを左外部結合せよ.

%%sql

5.3.7. Q7. 外部結合(2/2)#

university_student_population_top10テーブルに対して「地域コード」が等しいという条件でactivityテーブルを右外部結合せよ.

%%sql