超電磁砲でデータベース（複数テーブル実践編）

はじめに
課題

はじめに

すでにテーブル STATUS は作成してあるものとする。今回はテーブル AFF を新たに追加する。AFF は NAME と TEAM の２属性からなる。AFF の一部を掲載する。

NAME	TEAM
御坂美琴	常盤台中
白井黒子	常盤台中
白井黒子	ジャッジメント

AFF の説明は次の通り。

NAME はテーブル STATUS と共通で、登場人物の氏名を表す。
TEAM は所属する組織を表す。
STATUS に登録された登場人物に対応するデータではあるが、全員に対応するデータがあるとは限らない。また、１人に複数のデータが対応することもある。言い換えると、所属が登録されていない人物もあれば、複数登録されている人物もある。

AFF を作成するために、次の SQL文を実行して欲しい。

CREATE TABLE AFF (
 NAME TEXT,
 TEAM TEXT,
 PRIMARY KEY ( NAME, TEAM ),
 FOREIGN KEY ( NAME ) REFERENCES STATUS
) ;

INSERT INTO AFF VALUES ( '垣根帝督', 'スクール' ) ;
INSERT INTO AFF VALUES ( '御坂美琴', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '麦野沈利', 'アイテム' ) ;
INSERT INTO AFF VALUES ( '食蜂操祈', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '食蜂操祈', '食蜂派閥' ) ;
INSERT INTO AFF VALUES ( '海原光貴', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '弓箭入鹿', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '弓箭入鹿', '食蜂派閥' ) ;
INSERT INTO AFF VALUES ( '絹旗最愛', 'アイテム' ) ;
INSERT INTO AFF VALUES ( '獄彩海美', 'スクール' ) ;
INSERT INTO AFF VALUES ( '婚后光子', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '切斑芽実', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '滝壺理后', 'アイテム' ) ;
INSERT INTO AFF VALUES ( '白井黒子', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '白井黒子', 'ジャッジメント' ) ;
INSERT INTO AFF VALUES ( '帆風潤子', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '帆風潤子', '食蜂派閥' ) ;
INSERT INTO AFF VALUES ( '誉望万化', 'スクール' ) ;
INSERT INTO AFF VALUES ( '固法美偉', 'ジャッジメント' ) ;
INSERT INTO AFF VALUES ( '口囃子早鳥', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '口囃子早鳥', '食蜂派閥' ) ;
INSERT INTO AFF VALUES ( '薄絹休味', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '泡浮万彬', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '牧上小牧', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '牧上小牧', 'ジャッジメント' ) ;
INSERT INTO AFF VALUES ( '牧上小牧', '食蜂派閥' ) ;
INSERT INTO AFF VALUES ( '湾内絹保', '常盤台中' ) ;
INSERT INTO AFF VALUES ( '雲川鞠亜', '繚乱家政女学校' ) ;
INSERT INTO AFF VALUES ( '初春飾利', '柵川中' ) ;
INSERT INTO AFF VALUES ( '初春飾利', 'ジャッジメント' ) ;
INSERT INTO AFF VALUES ( 'フレンダ', 'アイテム' ) ;
INSERT INTO AFF VALUES ( '佐天涙子', '柵川中' ) ;
INSERT INTO AFF VALUES ( '土御門舞夏', '繚乱家政女学校' ) ;
INSERT INTO AFF VALUES ( '浜面仕上', 'アイテム' ) ;

課題

実際に課題を解いて結合に慣れよう。最初に結合に特化した課題を解き、その後、グループ化も使った課題を解いてみよう。

課題 (確認しやすいように表示順序を適宜指定せよ)
(1) レベル５の人物の名前と所属組織を表示せよ。
(2) 所属先が登録されている人物の名前とレベルを表示せよ。
(3) ジャッジメント所属の人物の名前とレベルを表示せよ。
(4) 所属先が登録されていない人物の一覧を表示せよ。

解答例
(1) SELECT NAME, TEAM FROM STATUS NATURAL JOIN AFF WHERE LEVEL = 5 ;
(別解) SELECT STATUS.NAME, TEAM FROM STATUS, AFF WHERE STATUS.NAME = AFF.NAME AND LEVEL = 5 ;

(2) SELECT DISTINCT NAME, LEVEL FROM STATUS NATURAL JOIN AFF ORDER BY LEVEL DESC ;
(別解) SELECT DISTINCT STATUS.NAME, LEVEL FROM STATUS, AFF WHERE STATUS.NAME = AFF.NAME ORDER BY LEVEL DESC ;

(3) SELECT NAME, LEVEL FROM STATUS NATURAL JOIN AFF WHERE TEAM = ‘ジャッジメント’ ORDER BY LEVEL DESC ;
(別解) SELECT STATUS.NAME, LEVEL FROM STATUS, AFF WHERE STATUS.NAME = AFF.NAME AND TEAM = ‘ジャッジメント’ ORDER BY LEVEL DESC ;

(4) SELECT STATUS.NAME FROM STATUS LEFT OUTER JOIN AFF ON STATUS.NAME = AFF.NAME WHERE AFF.NAME IS NULL ;

課題 (確認しやすいように表示順序を適宜指定せよ)
(1) 組織別の人数を表示せよ。
(2) ４人以上所属する組織を求め、組織別の人数を表示せよ。
(3) 複数の所属が登録されている人物の名前とレベルを表示せよ。
(4) 組織別に構成員の最も高いレベルを表示せよ。
(5) レベル４以上の構成員がいる組織の人数を表示せよ。
(6) 組織別に能力名が登録されている人数を表示せよ。

解答例
(1) SELECT TEAM, COUNT(*) AS COUNT FROM STATUS NATURAL JOIN AFF GROUP BY TEAM ORDER BY COUNT DESC ;
(別解) SELECT TEAM, COUNT(*) AS COUNT FROM STATUS, AFF WHERE STATUS.NAME = AFF.NAME GROUP BY TEAM ORDER BY COUNT DESC ;

(2) SELECT TEAM, COUNT(*) AS COUNT FROM STATUS NATURAL JOIN AFF GROUP BY TEAM HAVING COUNT > 3 ORDER BY COUNT DESC ;
(別解) SELECT TEAM, COUNT(*) AS COUNT FROM STATUS, AFF WHERE STATUS.NAME = AFF.NAME GROUP BY TEAM HAVING COUNT > 3 ORDER BY COUNT DESC ;

(3) SELECT NAME, LEVEL FROM STATUS NATURAL JOIN AFF GROUP BY NAME HAVING COUNT(TEAM) > 1 ORDER BY LEVEL DESC ;
(別解) SELECT STATUS.NAME, LEVEL FROM STATUS, AFF WHERE STATUS.NAME = AFF.NAME GROUP BY STATUS.NAME HAVING COUNT(TEAM) > 1 ORDER BY LEVEL DESC ;

(4) SELECT TEAM, MAX( LEVEL ) AS MAX_LEVEL FROM STATUS NATURAL JOIN AFF GROUP BY TEAM ORDER BY MAX_LEVEL DESC ;
(別解) SELECT TEAM, MAX( LEVEL ) AS MAX_LEVEL FROM STATUS, AFF WHERE STATUS.NAME = AFF.NAME GROUP BY TEAM ORDER BY MAX_LEVEL DESC ;

(5) SELECT TEAM, COUNT(*) AS COUNT FROM STATUS NATURAL JOIN AFF GROUP BY TEAM HAVING MAX( LEVEL ) > 3 ORDER BY COUNT DESC ;
(別解) SELECT TEAM, COUNT(*) AS COUNT FROM STATUS, AFF WHERE STATUS.NAME = AFF.NAME GROUP BY TEAM HAVING MAX( LEVEL ) > 3 ORDER BY COUNT DESC ;

(6) SELECT TEAM, COUNT(*) AS COUNT FROM STATUS NATURAL JOIN AFF WHERE SKILL IS NOT NULL GROUP BY TEAM ORDER BY COUNT DESC ;
(別解) SELECT TEAM, COUNT(*) AS COUNT FROM STATUS, AFF WHERE STATUS.NAME = AFF.NAME AND SKILL IS NOT NULL GROUP BY TEAM ORDER BY COUNT DESC ;