6.SQL演習(1/3)
復習：
基礎編：
データベース言語SQLについて、基本コマンドを理解する。
キーワード：
データの追加・更新・削除、テーブルの定義とアクセス制御など。
ＤＤＬ ＤＭＬ ＤＣＬ
・Webclassの以下の資料を参照
>>新基本情報技術者試験学習教材 ※設計概念、ＳＱＬ など、システム制作の内容を含む
>>テクノロジ系対策コース２
>>第3章データベース
3.7 データ操作（１）～ 3.10 データ操作（４）
上記テキストに関する細かい突っ込み：
表Ｂが複数のレコードの場合、そのレコードすべてを含む表Ａのレコードが求める商となる。
・データ操作（３）DCL(Data Control Language / データ制御言語)の記載が無い。 GRANT,REVOKE など権限に関するコマンドは、データベースによってはDDLに分類される。・データ操作（４）比較演算子に >= 以上 と <= 以下 を追加。ＩＮＳＥＲＴ文で、数値データの記録には、引用符'' は不要。ただし、引用符で'123'と文字列を指定したデータを数値型のフィールドに記録するＩＮＳＥＲＴ文では 「暗黙的なデータ型変換」により、文字列データが数値データに変換されるデータベースも有る為、エラーにならない場合が有る。 ＵＰＤＡＴＥ文で、WHERE条件を省略した場合、全レコードが修正対象になる。>>データベース入門
第６章 データベース言語SQL
■データベース実習の技術学習資料
１回３分の動画によるプログラム学習サイトの講座を紹介。（ユーザ登録は、各自で判断。自習用）
レッスン一覧から、 データベース を参照。ついでに、データベースの種類なども確認。
MySQL入門（全19回） ＭｙＳＱＬは本講義の後半で利用するＤＢＭS
SQLite入門（全18回） SQLiteは、iOS、androidなどスマフォの様な小規模システムに組み込まれているＤＢＭS
ＳＱＬの練習がブラウザから出来る、英語サイトの紹介記事。 世界各国に関するデータを元にした問題をＳＱＬで回答。
補足：
SQLの基礎と演算子（旧資料）
関数とSELECT文の句（旧資料）
エロゲーマーのためのSQL SQLの解説サイト。ＳＱＬでゲームの情報を検索できる。
演習１：
SQLZOO の練習問題 Tutorial Section から
1.SELECT basics
2.SELECT from World
3.SELECT from Nobel
5.SUM and COUNT (GROUP BY と HAVING を使用しないと解けない問題については、予習して回答、もしくは飛ばしてもＯＫです。 )
に取り組む。
※ 問題の回答は、メモ帳などに 問題番号（ 1-1 の様な形式）とＳＱＬ文をコピーして記録しておく。
3113999.txt のようなファイル名のテキストファイルに記録して、WebClassの第6回課題から提出。
問題文 と エラーメッセージ の訳文：
■問題文1.SELECT basicsworld 国テーブルを利用した問題1-1 ドイツ(germany)の人口(population)を表示するように修正しなさい。WHERE の使用法の説明の為、France の人口が表示される状態になっています。Frace はテキストデータなので 'と'のシングルクオートで囲んでいることに注意する。例）SELECT population FROM world WHERE name = 'ここになんとかくでしょうか？'1-2 Sweden と Norway と Denmark の 国名 と 人口を表示する
INを利用して解答する
（別解）SELECT name, population FROM world WHERE name='Sweden' or name ='Norway' or name='Denmark'1-3 国名と面積を表示する。面積がそこそこ狭い国(200000 ～ 250000)をピックアップ。BETWEEN構文（境界値は範囲に含まれる）を使用する。
（別解）BETWEENを使わずに解けますか？ <= と >= と AND を使用します。過去問：
1-x 国名(name)と大陸(continent）を表示する。面積<2000 かつ gdp > 50000000000
1-x Gから始まる国名を表示
1-x 国名(name) と gdp/poulation （per capita gdpは国民一人当たりの国内総生産）を表示する。ただし面積(Area)が 5000000 より大きな国だけでよい。
1-x 国名と人口を表示、対象国はアイルランド、アイスランド、デンマークとする。
1-x 国名と面積を表示する。面積がそこそこ狭い国(200000 ～ 250000)をピックアップ。BETWEEN構文（境界値は範囲に含まれる）を使用する。
2.SELECT from world2-1 ここで利用している表の内容を確認する。(とにかくうごかして、実行結果を確認するだけ)2-2 人口が200百万人（2億人） （200000000 ゼロが８個ある）以上の国の名前を表示2-3 国名 と 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-7)の問題で、面積か人口のどちらか だけ が大きな国を表示する。論理演算でXORが使えない場合工夫が必要。 国名 人口 面積 を表示する。2-8 の問題の補足：排他的論理和 が where の条件に指定できれば簡単だが、 DBMSによって対応状況に差がある。 http://cs.hatenablog.jp/entry/2013/07/19/154215 参照。2-9 南アメリカ大陸にある国の、国名、人口（100万人単位）、GDP（10億ドル単位）を表示する。GDP は、ROUND 関数を使って小数点以下の数値を２桁に丸める。(ROUNDの説明は、リンク先を参照)2-10 GDP per capita(国民１人あたりのＧＤＰ)が１兆ドル以上（０が１２個）の国を表示する。単位は千＄単位にまとめる。
千単位にまとめるテクニック： 1000で割って、roundで端数を除去した後で 1000倍する。難問（2-11から2-13は回答してもしなくてもよい。チャレンジしてみたい人は是非）
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を含むので表示されない。
過去問：
2-x CASE 構文を利用して、北アメリカの国の表示をカリブの置き換えた例が示してある。これを元に、Ｎから始まる国の国名と大陸を表示する。 だだし、大陸をはオセアニアからオーストララシアに変えて表示 2-x 国名がＡかＢで始まる国について、国名と大陸を表示する。ただし、ヨーロッパとアジアを ユーラシア、北アメリカ 南アメリカ カリブ は合わせてアメリカ として表示する。2-x 大陸名を置き換えて表示する。国名、元の大陸名、新しい大陸名 を表示。置き換え内容は、 オセアニア は オーストラシア ユーラシアとトルコは ヨーロッパ/アジアＢで始まるカリブの島 は 北アメリカその他のカリブの島 は 南アメリカ表示は、国名の昇順 とする。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年の受賞者 を 分野 名前で表示する。ただし、化学と物理学は 下の方に表示する。
（ヒント）
subject in ('Physics' , Chemistry') の実行結果は 0 か 1 になるので、これを順序指定に利用する。
select の表示項目名を 式 as フィールド名 の書式を利用してフィールド名使って並べ替える
4-x の問題は来週の講義で解説後に取り組む。
5.SUM and COUNT5-1 世界の総人口を表示。（各国の人口を合計）5-2 大陸名を重複しないように表示。 （ヒント） group by でも同様の事が出来ますが、ここでは重複レコードを非表示の手法で。5-3 アフリカの諸国のＧＤＰの合計を求める。5-4 面積が少なくとも 1000000 以上の国の数を求める。 （ヒント）個数を数える集計関数を使う。
5-5 'Estonia', 'Latvia', 'Lithuania' の人口合計を求める。 （ヒント） whereの条件指定で in を利用すると簡単。
group by と having を利用
ここから先の問題は、Using GROUP BY and HAVING.のリンク先を参照しておくとよい。
5-6 各大陸ごとに大陸名とそこにある国の数を表示する。
5-7 人口が10000000 人以上の各大陸の国の数を表示する。
5-8 各大陸の人口（大陸内の各国の合計）で１00000000 以上の大陸を表示 （ヒント） having で集計関数を利用した条件を指定する。
■エラーメッセージなど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 columnss 列が多すぎる（表示項目の指定ミス）Too few columnss 列が少なすぎる（表示項目の指定ミス）Some of the data is incorrect. データがどこか間違っている（計算式や表示形式などデータ加工のミス）Unknown column 'xxxxxxxxx' in 'field list' (xxxxxxxxx がフィールド名に無い。スペルミスか、テーブルの指定ミス)Show correct result 正しい答えを表示する（表示内容が間違っていた場合表示される。クリックで正解の出力結果を表示）演習２：
XAMPP のphpMyAdimから、MySQLを利用して、SQLコマンドの実習を行う。
演習課題：
第５回で作成した、データベース btunes の songs テーブルに対して、以下の操作を ＳＱＬ で実行する。
2-1.
insert コマンドで、レコードを ２件追加（insert を２回実行）
追加するレコードの数値フィールドは 数値を直接記述する。
テキストフィールドは、引用記号で囲む必要があることに注意する。例） 'ぶんぶんぶん'
2-2.
上記で追加したレコードのうち、どちらか片方のデータを id 番号を指定して修正する。
修正内容： year （年）のデータを 1999 年に修正
2-3.
songs テーブルの記録件数を集計して表示する SQL を実行する
集計結果の見出しは、total_songs を指定する
提出方法、WebClassの問題２に提出。 2-1と 2-2と 2-3で実行した SQLをテキストボックスに記入。