Embedded Files
2021年度解説動画（表の結合）
SQLによるテーブルの結合、ビューについて扱う。
SQLによるテーブルの結合、ビューについて扱う。
旧講義資料 表の結合とサブクエリー （表の結合の解説部分を参照。課題は無視してください。）
学生の名簿とクラブの名簿を1つのテーブルで記録するには無理がある。
メインのメニューとドリンクのメニューを１つのテーブルで記録するには無理がある。
※ここではランチ単品とドリンク単品の注文はできないと考えておく。
正規化と結合 （復習）
正規化と結合 （復習）
質問： 表の結合はなぜ必要なのか？
・テーブルへレコードを追加・修正・削除した際に不都合が生じないようにテーブル設計を工夫する
不都合の例
必要なデータまで削除される
不要なデータの削除漏れ
全てのデータが揃うまでレコードを追加できない
あるレコードを修正すると他のレコードも連鎖的に修正が必要になる
複数のレコードを一揃い追加する必要がある
↓
・テーブルを正規化する。（正規化のルールを適用。第1正規形、第2正規形、第3正規形など）
↓
・テーブルを第2正規形以上の正規化ではテーブルは複数に分解される
↓
・分解されたテーブルは主キーと外部キーの値によって元のテーブルに組み立てなおすことが出来る
RDBMSでは、正規化されたテーブルから必要に応じてテーブルを組み立て直して利用する。
テーブルの結合（組み立て直し）はSQL で指示する。
SQLの SELECT 文で作成したテーブルは一時的に作成されて使い捨てにされるテーブルである。
このテーブルをビューと呼ぶ。
（おまけ）
SQLZOOの チュートリアル6 はテーブルを3個使用した問題なので少し複雑である。
テーブルを2個だけ使用したチュートリアル 音楽チュートリアル に取り組むのもお勧め。
練習問題
練習問題
SQLをそのまま実行して結果を確認してください。
SQLZOO の練習問題 Tutorial Section から 6.JOIN のテーブルを利用した復習問題。
・参加チームのデータを一覧表示
select * from eteam
・国名のみ表示
select teamname from eteam
・参加している国の数を表示
select count(*) from eteam
・この大会での総ゴール数を求める
select count(*) from goal
・チームごとの総ゴール数を求める
select count(*) from goal group by teamid
・チームごとの総ゴール数を求める。表を結合して チーム名 得点数 を表示する。
select e.teamname,count(*) FROM goal g join eteam e on g.teamid=e.id group by teamid
このSQLは標準仕様ではエラーになる。group by でteamidで集約しているのでselect のフィールドにはteamidまたは集約関数を使用する必要がある。
対処方法：
select e.teamname,count(*) FROM goal g join eteam e on g.teamid=e.id group by teamid,teamname
もしくは
select e.teamname,count(*) FROM goal g join eteam e on g.teamid=e.id group by teamname
ところが、SQLZOO の使用しているデータベースエンジン MySQL ではGroup By の拡張機能によりエラーにならずに動作する場合がある。この記事参照。
SQLZOOのサイトの歯車アイコンから、データベースエンジンを MySQL から SQL Server に変更して、上記のSQLを試してみよう。SQL Server では最初の例はエラーになる事が確認できる 2025.6.5 利用可能なDBMSはMySQLのみになった。
・得点記録がある人を名前の重複を除去して表示
select distinct player from goal
・得点記録がある人の人数を数える
select count(distinct player) from goal
↑と同じことをサブクエリ―で書くと
select count(a.player) from (select distinct player from goal) AS a
※sqlzooでは サブクエリーでselectで作成した表に名前を付ける必要がある。
※上記の例では、(select distinct player from goal) の出力結果を a という名前を付けた。
・得点王（最多得点のプレイヤー）を探す
select player,count(*) pg from goal group by player order by pg desc
演習1/2
演習1/2
問1 6 JOIN
問2 7 More JOIN
を参考にして自作の問題を作成して自分で回答する。
提出方法
提出方法
SQLZOOのSQL入力欄を利用する
「#」記号に続けて【自作の問題文】を記入する
自作問題の【解答SQL】を記入して Submit SQLで実行する
問題とSQLと実行結果をスクリーンショットに撮る
複数の問題を考えた場合は1枚の画像にまとめて提出してよい
（自作の問題を実行しても正解判定のアイコンは表示されない）
配点： 問題の難易度に応じて点数を与える
学習した内容をなるべく多く反映するように工夫するとよい
新しく登場したキーワードやテクニック、または復習となるような問題でもよい
解答例
2021年度解説動画
問題文とヒント：
UEFA欧州選手権 UEFA EURO 2012 の試合データを使った演習問題
(サッカーの大会、ポーランドとウクライナで開催)
game ゲームテーブル 試合の日時、参加チームを記録
id 試合のid
mdate 試合の日
stadium スタジアム
team1 チーム１
team2 チーム２
goal ゴールテーブル ゴールの記録
matchid 試合id
teamid チームid
player プレイヤー
gtime ゴール時間
eteam 参加チームテーブル
id チームのid（国）
teamname チーム名前
coach コーチ
6-1
最初の例として Bender のゴール記録が示されている。
ドイツの matchid と player名 を表示
（ヒント） where の条件は teamid = 'GER'
SELECT ????????????? FROM goal
WHERE teamid = 'GER'
6-2
上記のクエリ―では、Lars Benderのゴールは ゲーム 1012 で確認できる。
goalテーブルで試合番号は matchid だが、gameテーブルでは id となっていることに注意する。
game 1012 の id, stadium, team1, team2 を表示する。
（ヒント）
SELECT id,stadium,team1,team2
FROM game
WHERE ????????????????
6-3
JOIN を利用して、２ステップで単独のクエリ―を組み合わせる事が出来る。
gameとgoalの詳細を得るには、
（ＳＱＬの例文）
問題： player, teamid ,stadium, mdate を全てのドイツのゴールについて表示する。
表の結合の条件 と データの検索の条件 の２つのタイプの条件が必要。
and で記述しても on と where に分けて記述してもよい。
（ヒント１）
SELECT ????????????????
FROM game JOIN goal ON (id=matchid and ??????)
（ヒント２）
SELECT ????????????????
FROM game JOIN goal ON (id=matchid)
where ??????
6-4
上の問題と同様に JOIN を利用してMarioという名前の選手のゴールについてteam1, team2 , player を表示する。
（ヒント）3の問題が解けたらコピーして条件の部分を修正
6-5
eteam には各参加国のコーチが記載されている。
JOIN で goal を eteam に結合する。
goal JOIN eteam on teamid=id を条件に使用する。
最初の１０分間でゴールしたという条件で、 player, teamid, coach, gtime を表示。
6-6
team1のコーチが 'Fernando Santos' となる試合日とチーム名を表示。
6-7
'National Stadium, Warsaw' スタジアムで開催された試合でゴールした選手を表示する。
ここより先、オプションの選択課題とする。未回答でも減点はなし。回答者には 加点 する。
6-8
ドイツと対戦してゴールした選手の名前を全て表示する。
（ヒント）例としてドイツ―ギリシャ戦の全試合を表示するクエリーが入力してある。
6-9
チーム名とゴール数の合計を表示する。
（ヒント）select の項目で count(*)を使用し、 GROUP BY teamname を利用する。
6-10
スタジアムの名前と、そのスタジアムでのゴール数を各スタジアムごとに表示する。
6-11
ポーランド（POL）が参戦している全試合の matchid と 日程 とその試合のゴール数 を表示する。
6-12
ドイツが得点した試合の matchid と 日程 と ドイツの得点 を表示する。
6-13
この問題は、ここまでまだ未解説のSQL構文「CASE WHEN」 を使用する。
下に示す様に、各試合ごとに各チームの得点を表示する。
mdate team1 score1 team2 score2 日程 チーム名１ 得点１ チーム名２ 得点２
注意） 全得点状況が、記録されている。もし、チーム名が goal に記録されていれば、
その時点でチームが１得点していることにななり、チーム名が記載されていなければ、得点は０点である。
チーム名有り → １
チーム名なし → ０
この、得点状況を １と０ に CASE WHEN で変換した結果をSUM で集計すれば、そのチームの得点を集計できる。
結果は、日程順で並べ替えて出力する(日程が同じなら、idの順番)。
※難しいので長文で解説
例文のＳＱＬ：
SELECT mdate, team1,
CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1
FROM game JOIN goal ON matchid = id
ＳＱＬの解説
SELECT mdate, game.team1, goal.teamid
FROM game JOIN goal ON goal.matchid = game.id
で game と goal が結合できる。
この結合方法では team1 が参加した試合について、自国の得点と対戦国の得点の両方が出力される。
そこで出力結果が自国の場合は １点、 他国の場合は ０点に 出力を変換して表示する。
その為に以下の様に selectの表示項目で、CASE WHEN を利用する。
CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1
上記の条件部分で、 goal.teamid が game.team1 に一致するかを判定して、 THEN 1 で一致の場合は １ に ELSE 0 で不一致の場合は 0 に変換している。
ENDの後ろの score1 で、出力結果の見出しに score1 という名前を付けている。（AS score1 の省略形）
(ヒント)
自国の得点状況を集計するには、
SELECT mdate,
team1,
sum(
CASE WHEN teamid=team1 THEN 1 ELSE 0 END
) as score1
FROM game
LEFT JOIN goal ON goal.matchid = game.id
GROUP BY game.id,mdate,team1,team2
これに、team2 の国名と 得点の集計結果を同様に付け加える。
JOINではなく、 LEFT JOIN を使用する必要が有る。試合で両国とも無得点 0-0 の場合は、 goalには、記録がない。
eteamにチーム名が記載されていても、goalにゴールの記録がないチームは、JOIN でテーブルを結合すると、結合結果には残らない（inner join 内部結合のため）。
そのためLEFT JOINで結合して得点の欄は空欄(NULL)となるように結合する。
sumで集計した際に得点の無い試合の集計結果は 0点になる。
集計結果は最終的に日程順に並べ替える。 order by を使う。
映画のデータベース
このチュートリアルで join の理解を深める。データベースは、3つのテーブルで構成される。
movie（映画） と actor（役者） と casting（出演） である。
テーブルの詳細は以下を参照
※ SQL の表記で id は movie.id と actor.id の2種類あるので注意
※ movie.id = actor.id のようには使われない（映画のid と 役者のid なので 参照キーと外部キーの関係にはならない）
※ movie.id = casting.movieid や actor.id = casting.actorid の関係がテーブル間にある
映画データベースの詳細は以下のリンクから表示
（ヒント） movie と casting と actor(出演者として結合) の3つの表を結合する2通りの書き方
join on による結合
select * from movie m
join casting c on m.id=c.movieid
join actor a on a.id=c.actorid
whereによる結合
select * from movie m,casting c,actor a
where
m.id = c.movieid
and
c.actorid = a.id
（ヒント） movie とactor（監督として結合）と casting と actor(出演者として結合) の3つの表を結合する
select * from movie m
join actor a on m.director=a.id
join casting c on m.id=c.movieid
join actor a2 on a2.id=c.actorid
さぁ、とりかかろう。
柔軟体操（準備練習）
7-1. 1962 年の映画のリストを表示（ id と title を表示）
（ヒント） 実行するだけ。文法の確認。
7-2. 「市民ケーン」の上映年は？
（ヒント） 1. を修正。 文字列の表記は ' と'で囲む必要に注意。
7-3. スタートレック（'Star Trek'）というシリーズ映画のリストを表示（ id title yr ）。 年の順に掲載。
Star Trek という語句を含む映画を where の条件に指定。
7-4. 女優 'Glenn Close' の id ナンバーは何ですか？（select で表示して調べる）
7-5. 映画 'Casablanca' カサブランカの id は何ですか？
過去問：
7-x. 11768, 11955, 21191 の id の映画の タイトル（title） を表示。
(ヒント) where 条件で in を利用
Get to the point（要点） のリンク先にテーブルの結合のSQLの例が掲載されているので確認。
↑に２つのテーブルの結合例（casting と actor） と ３つの全テーブルの結合例が載っているので、回答に利用するとよい。
7-6. 映画カサブランカの出演者リスト(name)を出力する。
（ヒント） 上の問題で、カサブランカの id が分かっているので、単純なクエリーで回答してもよい。 casting と actor を結合 where で カサブランカの id を指定。
（別解１） カサブランカの id を調べるサブクエリーを利用する。
（別解２）全テーブルを結合してから、条件指定で検索結果を絞り込む。
7-7. 映画「エイリアン」 の出演者リストを表示。
（ヒント） 7-6の別解を修正して回答。
7-8. 'Harrison Ford' ハリソン＝フォードが出演した映画のリストを表示する。
（ヒント） 全テーブルを結合し、条件指定。
7-9. 'Harrison Ford' ハリソン＝フォードが出演した映画で、彼が主演していない(ord <> 1） のリストを表示。
（ヒント） ord は、映画の出演リスト順、1 が主演を意味する。 ord<>1 で 出演順が1ではないという条件になる。
7-10. 1962年の全映画を、そのタイトルと主演と併記してリスト表示。
より難しい問題。 ここより先、オプションの選択課題とする。未回答でも 減点はなし 。 回答者には 加点 する。
7-11. 'John Travolta' ジョン＝トラボルタが最も忙しかった年はいつですか？ その年と出演した映画の本数を表示する。
彼が２本より多く出演した各年について表示する。
（ヒント） 実行するだけで正解が出てくる。SQLを解釈して、なぜそのような結果になるか考えること。
サブクエリの部分について解説。
(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr) AS t
)
上記クエリの解釈。
全テーブルを内部結合。
WHERE name='John Travolta' ジョン＝トラボルタのレコードだけを抽出
group by yr で年ごとにグループ化し COUNT(title) AS c で、出演したタイトルの本数を数え、項目名を c とする。
外側の select 文で、 c の欄の最大値を求めている。
※ 末尾の AS t はサブクエリーの実行結果は複数値を含みテーブルとして利用するので、仮の名前 t を指定している。名前が必要となるのは mysqlの仕様のため。
上記クエリをサブクエリとして、 7-12 の HAVING の条件に使用する。
7-12. 'Julie Andrews' ジュリー＝アンドリューズが出演した映画について、主演した役者の名前を調べ、その全てについて タイトル と 主演 を表示する。
（ヒント）
入力済みのSQLをまず実行してみる。'Julie Andrews' が出演した映画のidのリストを得ることができるので、これをサブクエリーとして、さらに外側の
select文（全テーブルを結合）を追加して組み合わせて回答する。
7-13. 少なくとも３０タイトル以上に主演した役者の名前をアルファベット順に掲載。
（ヒント） actor.id でグループ化
7-14. 1978年の映画を、出演者数が多い順に、タイトルを表示。
（ヒント） movie.id でグループ化
7-15. 'Art Garfunkel' アート＝ガーファンクルと一緒に仕事をした人々をすべて表示。
（ヒント） 一緒に出演したか彼が監督した映画に出演した という条件で検索する
（ヒント）役者以外に監督の名前とid もactor テーブルに記録されている。
演習2/2
演習2/2
最初の6 JOINのサッカーの試合のテーブルの問題で
select teamid,player,count(*) from goal group by teamid,player
をSQLZOOで実行して結果を確認する。
すると複数回得点したプレイヤーの名前やチーム名が繰り返し記録されていることが分かる。
この無駄を解決するためにテーブルの正規化を行う。
課題：テーブル設計の変更
変更内容
1. 得点者として playerid だけを記録してプレイヤー名や所属チームは記録しない
2. game , goal , eteam の３つのテーブルに加えて
players(〇playerid ,name ,※teamid)
を追加する
回答には テーブル名、フィールド名、主キー（先頭に〇）、外部キー（先頭に※）を以下のように記述すること。
テーブル名１（〇フィールド名１，※フィールド名２）
テーブル名２（〇フィールド名２，フィールド名３）
Webclassの問題３に回答する。
補足
補足
SQLZOOの問題６と７で扱うテーブルの結合は 単なる JOIN （つまりINNER JOIN）が主である。
に 左外部結合LEFT JOIN と右外部結合RIGHT JOIN の問題が有るので取り組んでおくとよい。
Page updated
Google Sites
Report abuse