SQL(4) 関係代数演算

Image from Gyazo

テーブルのレストア

前回までに作成したテーブルを .restore でレストアしましょう． また，.tableで【学生リスト】，【学部リスト】，【テニス部】，【サッカー部】，【商品リスト】があることを確認しましょう．

sqlite> .restore db3.sqlite
sqlite> .table
サッカー部  商品リスト  学部リスト
テニス部     学生リスト
学籍番号 氏名 学年 学部 よみがな
A001 岩城隼人 2 工学部 いわきはやと
A002 岩村優 1 工学部 いわむらゆう
B003 杉江弘子 3 人文学部 すぎえひろこ
C004 仙波あすか 1 国際関係学部 せんばあすか

【学生リスト】

学部 キャンパス 教員数
工学部 春日井 30
人文学部 春日井 20
国際関係学部 名古屋 25

【学部リスト】

学籍番号 氏名 学年 学部 よみがな
A002 岩村優 1 工学部 いわむらゆう
B003 杉江弘子 3 人文学部 すぎえひろこ
C004 仙波あすか 1 国際関係学部 せんばあすか

【テニス部】

学籍番号 氏名 学年 学部 よみがな
A001 岩城隼人 2 工学部 いわきはやと
B003 杉江弘子 3 人文学部 すぎえひろこ
A005 内田弘 3 工学部 うちだひろし

【サッカー部】

商品ID 商品名 仕入れ値 売値
C01 テレビ 50000 65000
C02 エアコン 60000 70000
C03 冷蔵庫 80000 55000
C04 電卓 3000 2000
C05 炊飯器 20000 25000

【商品リスト】

集合演算

リレーションに対する4つの集合演算がSQLで表現可能であることを確認していきましょう．

まずは，.headerでヘッダーを表示する設定にしておきます （加えて.modeを利用することで，様々な表示モードに変更できるという情報を頂きました！）．

sqlite> .header on
sqlite> select * from 学生リスト;
学籍番号|氏名|学年|学部|よみがな
A001|岩城隼人|2|工学部|いわきはやと
A002|岩村優|1|工学部|いわむらゆう
B003|杉江弘子|3|人文学部|すぎえひろこ
C004|仙波あすか|1|国際関係学部|せんばあすか

和集合演算

和集合演算が適用可能であるためにはリレーションが 和両立 である必要があります． ここでは，和両立の条件を満たしている【テニス部】と【サッカー部】を対象に考えていきます．

和集合演算にはUNIONを用います． テーブル名1とテーブル名2は対象のテーブルの名前です．

/* 和集合演算 */
SELECT * FROM テーブル名1 UNION SELECT * FROM テーブル名2;

それでは，【テニス部】と【サッカー部】の和集合を求めてみましょう． ここで，注意して欲しいのは， いずれのリレーションにも共通の$(B003,杉江弘子,3,人文学部,すぎえひろこ)$です． 和集合の結果には，該当のレコードは一つだけとなります．

sqlite> select * from テニス部 union select * from サッカー部;
学籍番号|氏名|学年|学部|よみがな
A001|岩城隼人|2|工学部|いわきはやと
A002|岩村優|1|工学部|いわむらゆう
A005|内田弘|3|工学部|うちだひろし
B003|杉江弘子|3|人文学部|すぎえひろこ
C004|仙波あすか|1|国際関係学部|せんばあすか

差集合演算

差集合演算にはEXCEPTを用います． テーブル名1とテーブル名2は対象のテーブルの名前です．

/* 差集合演算 */
SELECT * FROM テーブル名1 EXCEPT SELECT * FROM テーブル名2;

それでは，【テニス部】と【サッカー部】の差集合を求めてみましょう． 【テニス部】から共通のレコードが取り除かれていることがわかります．

sqlite> select * from テニス部 except select * from サッカー部;
学籍番号|氏名|学年|学部|よみがな
A002|岩村優|1|工学部|いわむらゆう
C004|仙波あすか|1|国際関係学部|せんばあすか

共通集合演算

共通集合演算にはINTERSECTを用います． テーブル名1とテーブル名2は対象のテーブルの名前です．

/* 共通集合演算 */
SELECT * FROM テーブル名1 INTERSECT SELECT * FROM テーブル名2;

それでは，【テニス部】と【サッカー部】の共通集合を求めてみましょう． 共通のレコードが結果に含まれていることがわかります．

sqlite> select * from テニス部 intersect select * from サッカー部;
学籍番号|氏名|学年|学部|よみがな
B003|杉江弘子|3|人文学部|すぎえひろこ

直積演算

直積演算にはCROSS JOINを用います． テーブル名1とテーブル名2は対象のテーブルの名前です． これまでとは異なりCROSS JOINの後にSELECT文は不要です．

/* 直積演算 */
SELECT * FROM テーブル名1 CROSS JOIN テーブル名2;

それでは，【テニス部】と【サッカー部】の直積を求めてみましょう． 全ての組み合わせでレコードが構成されていることがわかります．

sqlite> select * from テニス部 cross join サッカー部;
学籍番号|氏名|学年|学部|よみがな|学籍番号|氏名|学年|学部|よみがな
A002|岩村優|1|工学部|いわむらゆう|A001|岩城隼人|2|工学部|いわきはやと
A002|岩村優|1|工学部|いわむらゆう|B003|杉江弘子|3|人文学部|すぎえひろこ
A002|岩村優|1|工学部|いわむらゆう|A005|内田弘|3|工学部|うちだひろし
B003|杉江弘子|3|人文学部|すぎえひろこ|A001|岩城隼人|2|工学部|いわきはやと
B003|杉江弘子|3|人文学部|すぎえひろこ|B003|杉江弘子|3|人文学部|すぎえひろこ
B003|杉江弘子|3|人文学部|すぎえひろこ|A005|内田弘|3|工学部|うちだひろし
C004|仙波あすか|1|国際関係学部|せんばあすか|A001|岩城隼人|2|工学部|いわきはやと
C004|仙波あすか|1|国際関係学部|せんばあすか|B003|杉江弘子|3|人文学部|すぎえひろこ
C004|仙波あすか|1|国際関係学部|せんばあすか|A005|内田弘|3|工学部|うちだひろし

課題1

下記の【八百屋】と【果物屋】を新規に作成しなさい． また，和集合，差集合，共通集合，直積集合を求め，その出力結果を提出しなさい．

商品番号 商品名 価格
G1 ニンジン 100
G2 トマト 200
G3 スイカ 500
【八百屋】
商品番号 商品名 価格
G4 イチゴ 300
G5 ブドウ 200
G3 スイカ 500
【果物屋】

関係代数演算

リレーションに対する3つの関係代数演算（リレーショナル代数演算）が SQLで表現可能であることを確認していきましょう（商演算は割愛） ．

射影演算

射影演算は SELECT文 を用いるだけです．

/* 射影演算 */
SELECT 属性名 FROM テーブル名;

それでは，【学生リスト】を氏名とよみがなで射影してみましょう．

sqlite> select 氏名,よみがな from 学生リスト;
氏名|よみがな
岩城隼人|いわきはやと
岩村優|いわむらゆう
杉江弘子|すぎえひろこ
仙波あすか|せんばあすか

選択演算

選択演算は SELECT文 と WHERE句 を組み合わせるだけです．

/* 選択演算 */
SELECT 属性名 FROM テーブル名 WHERE 条件式;

それでは，【学生リスト】から工学部のレコードを選択しましょう．

sqlite> select * from 学生リスト where 学部="工学部";
学籍番号|氏名|学年|学部|よみがな
A001|岩城隼人|2|工学部|いわきはやと
A002|岩村優|1|工学部|いわむらゆう

結合演算

結合演算は，条件を指定して結合する方法（内部結合と外部結合）と， 一致するドメインを条件として結合する方法（自然結合）があります．

条件を指定して結合（内部結合）するには INNER JOIN を用います． テーブル名1とテーブル名2は対象のテーブルの名前です． ONの後に，結合の条件となる各テーブルの属性を指定します．

/* 内部結合 */
SELECT * FROM テーブル名1 INNER JOIN テーブル名2 ON テーブル名1.属性名1 = テーブル名2.属性名2;

それでは，【学生リスト】と【学部リスト】を「学部」を条件に結合してみましょう． 学部の属性値が一致するレコードが結合されていることがわかります．

sqlite> select * from 学生リスト inner join 学部リスト on 学生リスト.学部 = 学部リスト.学部;
学籍番号|氏名|学年|学部|よみがな|学部|キャンパス|教員数
A001|岩城隼人|2|工学部|いわきはやと|工学部|春日井|30
A002|岩村優|1|工学部|いわむらゆう|工学部|春日井|30
B003|杉江弘子|3|人文学部|すぎえひろこ|人文学部|春日井|20
C004|仙波あすか|1|国際関係学部|せんばあすか|国際関係学部|名古屋|25

一致するドメインを条件として結合(自然結合)するには NATURAL INNER JOIN を用います． 上述の方法とは異なり，具体的に条件となる属性は指定しません．

/* 自然結合 */
SELECT * FROM テーブル名1 NATURAL INNER JOIN テーブル名2;

それでは，【学生リスト】と【学部リスト】を自然結合してみましょう． 自動的に「学部」を条件として結合されていることがわかります． また，冗長な属性は取り除かれています．

sqlite> select * from 学生リスト natural inner join 学部リスト;
学籍番号|氏名|学年|学部|よみがな|キャンパス|教員数
A001|岩城隼人|2|工学部|いわきはやと|春日井|30
A002|岩村優|1|工学部|いわむらゆう|春日井|30
B003|杉江弘子|3|人文学部|すぎえひろこ|春日井|20
C004|仙波あすか|1|国際関係学部|せんばあすか|名古屋|25

課題2

【学生リスト】に新たに下記のレコードを追加しなさい．

学籍番号 氏名 学年 学部 よみがな
D006 山下隆 4 経営情報学部 やましたたかし
その後で，学部リストとの内部結合(INNER JOIN)，左外部結合(LEFT OUTER JOIN)を行い， その差を考察しなさい．

テーブルのバックアップ

作成したテーブルをファイルにバックアップするには，.backupを用います． バックアップするファイル名は db4.sqlite としましょう．

sqlite> .backup db4.sqlite

愛知県名古屋市にある椙山女学園大学 文化情報学部 向研究室の公式サイトです． 専門は情報科学であり，人工知能やデータベースなどの技術要素を指導しています． この公式サイトでは，授業で使用している教材を公開すると共に， ベールに包まれた女子大教員のミステリアスな日常を４コマ漫画でお伝えしていきます． サイトに関するご意見やご質問はFacebookまたはTwitterでお問い合わせください．