6.SQL演習(1/3)

【復習】BCNFの確認

　見学者を担当者が引率して複数のエリアを案内する。【各エリアから施設を1つ体験】する

このテーブルの主キーは　（担当者,　エリア）　の複合キー

施設と滞在時間は主キーに完全従属するので２NF

非キー属性の施設と滞在時間のあいだには従属性は無い。非キー属性に推移従属がないのでこの状態で３NF

キー属性のエリアが非キー属性の「施設」に従属しているため正規化が必要

解答

確認

・テーブル（〇施設、エリア）　と　テーブル（〇担当者、〇※施設、滞在時間）　を結合するとBCNFに分割する前のテーブルに復元できる（情報無損失分解）

・分割前のテーブルの主キー　〇担当者、〇エリア　の制約（各担当者はエリアを重複しない）は分割したテーブルには反映されない。

よって、テーブルのレコードの挿入時にはその確認が必要になる。

【復習】４NFの確認

メインとトッピングとドリンクを１品ずつセットで注文するとする。

メインによって選べるトッピングとドリンクは決きめられている。

テーブル設計が以下のとき

セット（〇※メインID、〇※トッピングID、〇※ドリンクID）

メイン（〇メインID、名称、値段）

トッピング（〇トッピングID、名称、値段）

ドリンク（〇ドリンクID、名称、値段）

多値従属性を排除して４NFにせよ

ChatGPT-4生成した例　https://chat.openai.com/share/e972b8d8-128d-4e0d-831a-d8254b3300fa

解答

メインIDを1つ定める（IDの追加や削除時も同様）とトッピングIDとドリンクIDの全組合せが対応するので多値従属性がある。

多値従属性を無くすためにセットテーブルを各品ごとのテーブルに分解する。

メイン別トッピングメニュー（〇※メインID、〇※トッピングID）

メイン別ドリンクメニュー（〇※メインID、〇※ドリンクID）

確認

・各テーブルを結合すると４NFに分割する前のテーブルに復元できる（情報無損失分解）

【復習】５NFの確認

前回の課題の回答例

　前回の課題　問4　で教員と学生の連絡方法で可能な全ての組み合わせを利用するとせずに、連絡手段として登録したものだけを利用するとする。

　 その条件では以下のテーブルは５ＮＦでありこれ以上は分解できない。

分解してしまうとどの連絡手段で連絡を取ることにしていたのかを復元できない。非自明な情報無損失分解はできないのでこの状態で５ＮＦになっている。

SQL文法資料

PaizaのSQL入門講座

参考

SQLの基礎と演算子　（２０００年ごろに作成）

関数とSELECT文の句　（２０００年ごろに作成）

自習用資料

ドットインストール　１回３分の動画によるプログラム学習サイトの講座を紹介。（ユーザ登録は各自に任せる）

 MySQL入門（全19回） 本講義の演習で利用しているMySQLの入門講座

 SQLite入門（全18回） SQLiteは、iOS、androidなどスマフォの様な小規模システムに組み込まれているDBMS

演習1/2

XAMPP のphpMyAdimからMySQLを利用してSQLコマンドの実習を行う。

select以外のSQLコマンド　insert　と　update　の練習をする。

第５回で作成したデータベース btunes の songs テーブルに対して以下の操作を ＳＱＬ で実行する。

問1

insert コマンドで、レコードを ２件追加（insert を２回実行）

コマンドで使用するテキスト値は引用記号で囲む必要がある。例） 'ぶんぶんぶん'

INSERT INTO songs(id,title,artist,year) VALUES (null,'ぶんぶんぶん','曲：ドイツ民謡 作詞：ホフマン・フォン・ファラースレーベン',1843) 

INSERT INTO songs(id,title,artist,year) VALUES (null,'めだかの学校','作詞：茶木滋、作曲：中田喜直',1951) 

実行結果をスクショする

問2

上記で追加したレコードのうちどちらか片方のデータを id 番号を指定してupdateで修正する。

修正内容の例：    year （年）のデータを 1999 年に修正

UPDATE songs SET year=1923 WHERE id=4

※ MySQLの YEAR型に関する注意 値の範囲は 1901-2155 までと0000の特殊なデータ。

実行結果をスクショする

問3

songs テーブルの記録件数を集計して表示する SQL を実行する

集計結果の見出しは total_songs を指定する

SELECT COUNT(*) as total_songs FROM songs

実行結果をスクショする

課題提出

回答をWebclassの第6回課題の

　問１

　問２

　問３

に提出

今回から　SQLZOO　の問題に3回連続で取り組む。

SQLの文法についてはPaizaラーニングの講座　新・SQL入門編　の　新・SQL入門編3: WHERE句を理解しよう　まで予習してある前提で進める。

講義では文法解説は軽く済ませる。基本的にPaizaの講座によるアクティブラーニングとする。

演習2/2

SQLZOO（英語のメインページ）

SQLZOO（日本語のチュートリアルページ）

サイトに接続したらSQLZOOの設定を変更する

１．DBMSを　MySQL　に切り替える

２．言語を日本語に切り替えてもよい（翻訳　小橋）

以下の問題を解く

0 SELECT basics

1 SELECT name

2 SELECT from World

3 SELECT from Nobel

4番目の SELECT within SELECT は次回扱う

5 SUM and COUNT (課題範囲 5-1 ～ 5-5 まで。次回で5-6 ～ 5-8 を扱う)

回答結果はSQLZOOのWebに「保存されない」

演習のヒント

SQLZOOで不正解のときのエラーの意味

Correct Answer 正解

Wrong Answer 不正解

Show what the answer should be... 正しい答えを表示するには…

You have an error in your SQL syntax 文法エラー

Too many rows 行が多すぎる（条件の指定ミス）

Too few rows 行が少なすぎる（条件の指定ミス）

Too many columns 列が多すぎる（表示項目の指定ミス）

Too few columns 列が少なすぎる（表示項目の指定ミス）

Some of the data is incorrect データがどこか間違っている（計算式や表示形式などデータ加工のミス）

Unknown column 'xxxxxxxxx' in 'field list'  xxxxxxxxx がフィールド名に無い。スペルミスか、テーブルの指定ミス

Invalid column name 'xxxx' xxxxというフィール名が間違っている

Show correct result 正しい答えを表示する（表示内容が間違っていた場合表示される。クリックで正解の出力結果を表示）

SQLZOOの不具合による不正解

正解のSQLを実行しても　Syntax Error（文法エラー）で正解にならない場合がある。SQLZOOサーバが負荷か何かで不具合が起きる模様。
SQLを入力し直すなど様子をみて再度実行すると不具合が解消されるので試すこと。エラーが解消されなくても正解を導けたならWebclassに回答すればOK

500 Internal Server Error サーバーに負荷がかかってる場合に一時的に発生するエラー。SQLを実行し直すと解消する場合もある。

問題解説

0.SELECT basics

world 国テーブルを利用した問題

0-1 ドイツ(germany)の人口(population)を表示するように修正しなさい。

WHERE の使用法の説明の為、France の人口が表示される状態になっています。France はテキストデータなので 'と'のシングルクオートで囲んでいることに注意する。

例）SELECT population FROM world WHERE name = 'ここに記入すればよさそう'

0-2 Sweden と Norway と Denmark の 国名 と 人口を表示する

INを利用して解答する

（別解）SELECT name, population FROM world WHERE name='Sweden' or name ='Norway' or name='Denmark'

0-3  国名と面積を表示する。面積がそこそこ狭い国(200000 ～ 250000)をピックアップ。BETWEEN構文（境界値は範囲に含まれる）を使用する。

（別解）BETWEENを使わずに解けますか？ <= と >= と AND を使用します。

1.SELECT name

1-1 Y で始まる国名を見つける

1-2 y で終わる国名を見つける

1-3 x を含む国名を見つける

1-4 land で終わる国名を検索する

1-5 C で始まり ia で終わる国を見つける

1-6 oo を名前に含む国を見つける

1-7 a を３つ以上含む国名を見つける

1-8 "t" を第２文字目に持つ国名を見つける

1-9 複数の"o"が他の２文字で隔てられている国名を見つける

1-10 ちょうど４文字の国名を見つける
パターンマッチ文字 _ を利用するか、length関数で長さを求める

難問（1-10から1-15は回答してもしなくてもよい。チャレンジ推奨）

1-11 首都と国名が同じ国を見つける
like で判定

1-12 国名 ＋ "City" が首都の国を見つける
concat(name + ' City') 空白文字を含めて結合すること。likeで判定

1-13 国名を首都名に含む国の、首都と国名を表示する
like '%name%'ではできない。concatで '%'とnameを結合する。更に2重にconcatを用いて末尾に'%'を追加

1-14 国名を拡張した首都名を持つ国の、首都と国名を見つける
1-13を修正してwhereに name と capitalが同じものを除外する条件を追加

1-15 国名と首都の拡張部分を見つける
1-14を修正してselectでmid関数とlength関数で拡張部分を切り出すように書く
mid(capital,切り出し先頭位置,切り出す長さ)
先頭位置 length(name) + 1
長さ length(capital) - length(name)

2.SELECT from world

2-1  ここで利用している表の内容を確認する。(とにかくうごかして、実行結果を確認するだけ)

2-2 人口が200百万人（2億人） （200000000 ゼロが８個ある）以上の国の名前を表示

2-3  国名 と per capita GDP（国民一人あたりの国内総生産の計算式は gdp/population ） を表示。条件 人口200百万人（2億人） 以上の国

2-4  大陸(continent)が South America である国の 国名 と 人口 を 百万人単位 に変換して、表示（1000000 で割る）

2-5 'France', 'Germany', 'Italy'の名前と、人口を表示。ヒント （where で in ( ....) のパターンを使うと楽）

2-6  国名に United を含む国名を特定する。

(ヒント) Like 検索パターンは？ '%あああ%' とすると、中間に あああ を含むパターンになる。%を使用する数と場所を検討

2-7 大きな国を表示する。大きな国とは、面積が 3000000 平方キロ以上 または 人口が250000000 以上の国である。 国名 人口 面積 を表示

難問（2-8から2-13は回答してもしなくてもよい。チャレンジしてみたい人は是非）

2-8 (2-7)の問題で、面積か人口のどちらか だけ が大きな国を表示する。
　　論理演算でXORが使えない場合工夫(AND OR NOTだけの論理式でXORを表現)が必要。

 国名 人口 面積 を表示する。

2-8 の問題の補足：

　　排他的論理和 が where の条件に指定できれば簡単だが、 DBMSによって対応状況に差がある　http://cs.hatenablog.jp/entry/2013/07/19/154215
　　SQLZOOでは設定メニューからDBMSを変更できる。標準のDBMSはSQLServer　他に　MySQL　も使用できる。

2-9 南アメリカ大陸にある国の、国名、人口（100万人単位）、GDP（10億ドル単位）を表示する。

人口 population と 国内総生産 GDP は両方とも、ROUND 関数を使って小数点以下の数値を２桁に丸める。

(ROUNDの説明は、リンク先を参照)

2-10 GDP per capita(国民１人あたりのＧＤＰ)が１兆ドル以上（０が１２個）の国を表示する。
単位は千＄単位にまとめる。

        千単位にまとめるテクニック： 1000で割って、roundで端数を除去した後で 1000倍する。または、ROUND関数で桁数指定を -3 とする。

2-11 国名と首都が同じ文字数

ギリシャ（Greece） の首都はアテネ（Athens）。 GreeceとAthensの綴りはどちらも同じ６文字。

国名と首都が同じ長さの国の、国名と首都を表示する。

・LENGTH関数で文字列の文字数を求める。例） LENGTH(capital) は 首都の文字数を求める式

2-12 国名と首都の一致

Swedenの首都はStockholm。どちらもSから始まる。

国名と首都の先頭の文字が同じである国の、国名と首都名を表示する。ただし、国名と首都名が同じ場合は除く。

・文字列の先頭一文字を切り取るには、LEFT関数を使う。

2-13  Equatorial Guinea （エクアトリアル ギニア）と Dominican Republic（ドミニカ共和国）はどちらも全母音（a e i o u）を国名に含んでいる。 これらの国は、名前に単語が２つ以上あるので数えないものとする。

国名に全母音を含む国で、空白を含まず１つの単語の国を検索する。
・name NOT LIKE '%a%' で特定の文字を含む国を除外できる
・入力済みのSQLはBから始まる国名を表示するが、Bahamasは少なくとも１つ以上のaを含むので表示されない。

3.SELECT from nobel

 ノーベル賞の表（nobel）からいろいろ検索（yr年 subject分野 winner受賞者）

3-1 1950年のノーベル賞受賞者を表示するようにクエリー（SQL）を変更する。

3-2 ノーベル文学賞（Literature）を1962年に受賞した人を表示

3-3 アルバート・アインシュタイン(Albert Einstein)がノーベル賞を受賞した年と分野を表示

3-4 ノーベル平和賞( subject が peace ）の ２０００年以降（２０００を含む）の受賞者名を表示

3-5 詳細表示（全フィールドを表示）1980から1989年の間のノーベル文学賞について表示する

3-6 大統領（テオドール＝ルーズベルト、ウッドロウ＝ウィルソン、ジミー＝カーター、バラク＝オバマ）の受賞内容詳細を表示する

（ヒント） IN で条件指定すると楽

3-7 ファーストネームが John の受賞者を表示（ヒント： Like で条件検索）

3-8 １９８０年のノーベル物理賞の受賞者 と １９８４年の化学賞の受賞者を共に表示する

3-9 1980年の 化学と医学 以外で、賞の年度、分野、名前を表示

3-10 1910年以前（1910は含まず）の 初期の医学の受賞者 と2004年以降（2004は含む）の 最近の文学の受賞者 を共に表示

難問（3-11から3-14は回答してもしなくてもよい。チャレンジしてみたい人は是非）

3-11 PETER GRÜNBERG の受賞内容詳細を検索する

ウムラウトの入力方法（Ü の入力方法）を調べ検索キーワードで機能させる方法を調べる

3-12 EUGENE O'NEILL の受賞内容詳細を検索する

文字列で クオート ' 記号の試用する方法について

3-13 騎士の受賞者リストを表示する。

Sir. で始まる受賞者を 受賞者、年、分野 で表示する。 年が新しい順で、次に名前順に表示する。

3-14 1984年の受賞者と分野を表示する。ただし化学 Chemistry と物理学 Physics は最後の方に表示

（ヒント）

subject in ('Physics' , Chemistry') の実行結果は 0 か 1 になるので、順序指定に利用できる。

5.SUM and COUNT

5-1 世界の総人口を表示。（各国の人口を合計）

5-2 大陸名を重複しないように表示。 （ヒント） group by でも同様の事が出来ますが、ここでは重複レコードを非表示の手法で。

5-3 アフリカの諸国のＧＤＰの合計を求める。

5-4 面積が少なくとも 1000000 以上の国の数を求める。 （ヒント）個数を数える集計関数を使う。

5-5 'Estonia', 'Latvia', 'Lithuania'  の人口合計を求める。 （ヒント） whereの条件指定で in を利用すると簡単。

次回で扱う。予習用

ここから先の問題は、Using GROUP BY and HAVING.のリンク先を参照しておくとよい。

5-6 各大陸ごとに大陸名とそこにある国の数を表示する。

5-7 人口が10000000 人以上の各大陸の国の数を表示する。

5-8 各大陸の人口（大陸内の各国の合計）で１00000000 以上の大陸を表示 （ヒント） having で集計関数を利用した条件を指定する。

課題提出

Webclassの第6回課題に提出

自作のSQL問題を考えてSQLZooで実行する

問題文もSQLに記述する。問題文の先頭に#を記入してコメント化しておくこと。

SQLZooの問題を適当に選んで自作問題と解答のSQLを入力して実行結果を含めてスクリーンショットする。

　問４

from world を指定

select where の使用必須

in as between and or not like distinct round order by desc 式　は任意で利用

　問５

from nobel を指定

select where の使用必須

in as between and or not like distinct round order by desc 式　は任意で利用

　問６

sum count avg max min など集計関数を使用する

回答例

注）自作問題なのでSQLZooで実行しても不正解（Wrong answer）となる。

■予習

SQLの文法についてはPaizaラーニングの講座　新・SQL入門編　の　新・SQL入門編5: GROUP BY 句を理解しよう　まで予習してある前提で次回は進める。

新・SQL入門編4: テーブルの結合を理解しよう　の内容は次々回に扱う。後回しにして先にGROUP BYを学習に進んでもよい。