Photo by
1st_note1
『実践Pythonによるデータベース入門』の第3章演習問題
今日は、『実践Pythonによるデータベース入門』の第3章 "SQL―基本的な問い合わせ―" の演習問題で学んだことをアウトプットします。
【演習問題3.1】
テーブルtodoufukenについて、SQL命令文を用いて以下の要求を実現せよ。
＊データ参照サイト：ウィキペディア「日本の地域」
仕様要求
（1）テーブルtodoufukenに地方（chihou）の列名を追加して、各県データにさらにchihouの値を指定する。そして、todoufukenテーブルの全件を表示する
（2）地方別の人口と面積合計を表示する
（3）地方別の県の数を表示する
（4）地方別の県の数が最大となる地方名の県の数を表示する
（1）テーブルtodoufukenに地方（chihou）の列名を追加して、各県データにさらにchihouの値を指定する。そして、todoufukenテーブルの全件を表示する
テーブルにカラムを追加するには、テーブルの構造を変更する「ALTER TABLE」を使います。
ALTER TABLE 表名 ADD 新規列名 型情報 [オプション];
mysql> USE todoufukenbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> ALTER TABLE todoufuken ADD chihou CHAR(30);
Query OK, 0 rows affected (2.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW fields FROM todoufuken;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| kenmei | char(20) | YES | | NULL | |
| kenchou | char(20) | YES | | NULL | |
| jinkou | int | YES | | NULL | |
| menseki | float | YES | | NULL | |
| chihou | char(30) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+次に、各県データにchihouの値を指定していきます。
データを更新することになるので、UPDATE文を使えばいいのかと考えました。
UPDATE 表名 SET 列名 WHERE 条件式;
北海道のchihouを北海道というようにデータを更新してみました。
確認すると、NULLだったchihou列が「北海道」に更新されてますね。
mysql> UPDATE todoufuken SET chihou='北海道' WHERE kenmei='北海道';
Query OK, 1 row affected (0.38 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM todoufuken WHERE kenmei='北海道';
+-----------+-----------+---------+---------+-----------+
| kenmei | kenchou | jinkou | menseki | chihou |
+-----------+-----------+---------+---------+-----------+
| 北海道 | 札幌市 | 5212462 | 83424.4 | 北海道 |
+-----------+-----------+---------+---------+-----------+
1 row in set (0.00 sec)同様にして、今度は東北地方の値を指定していきます。
mysql> UPDATE todoufuken SET chihou='東北' WHERE kenmei='青森県' OR kenmei='岩手県' OR kenmei='秋田県' OR kenmei='宮城県' OR kenmei= '山形県'
OR kenmei='福島県';
Query OK, 5 rows affected (0.10 sec)
Rows matched: 6 Changed: 5 Warnings: 0
mysql> SELECT * FROM todoufuken WHERE chihou='東北';
+-----------+-----------+---------+---------+--------+
| kenmei | kenchou | jinkou | menseki | chihou |
+-----------+-----------+---------+---------+--------+
| 青森県 | 青森市 | 1230535 | 9645.64 | 東北 |
| 岩手県 | 盛岡市 | 1212201 | 15275 | 東北 |
| 宮城県 | 仙台市 | 2292690 | 7282.29 | 東北 |
| 秋田県 | 秋田市 | 952069 | 11637.5 | 東北 |
| 山形県 | 山形市 | 1064954 | 9323.15 | 東北 |
| 福島県 | 福島市 | 1830114 | 13784.1 | 東北 |
+-----------+-----------+---------+---------+--------+
6 rows in set (0.00 sec)以下、各県のデータにchihouの値を指定していきます。
mysql> UPDATE todoufuken SET chihou='関東' WHERE kenmei='茨城県' OR kenmei='栃木県' OR kenmei='群馬県' OR kenmei='埼玉県' OR kenmei='千葉県' OR kenmei='東京都' OR kenmei='神奈川県';
Query OK, 7 rows affected (0.14 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> UPDATE todoufuken SET chihou='中部' WHERE kenmei='山梨県' OR kenmei='長野県' OR kenmei='新潟県' OR kenmei='富山県' OR kenmei='石川県' OR kenmei='福井県' OR kenmei='静岡県' OR kenmei='愛知県' OR kenmei='岐阜県' OR kenmei='三重県';
Query OK, 10 rows affected (0.16 sec)
Rows matched: 10 Changed: 10 Warnings: 0
mysql> UPDATE todoufuken SET chihou='近畿' WHERE kenmei='三重県' OR kenmei='滋賀県' OR kenmei='京都府' OR kenmei='大阪府' OR kenmei='兵庫県' OR kenmei='奈良県' OR kenmei='和歌山県';
Query OK, 7 rows affected (0.12 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> UPDATE todoufuken SET chihou='中国' WHERE kenmei='鳥取県' OR kenmei='島根県' OR kenmei='岡山県' OR kenmei='広島県' OR kenmei='山口県';
Query OK, 5 rows affected (0.12 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> UPDATE todoufuken SET chihou='四国' WHERE kenmei='香川県' OR kenmei='愛媛県' OR kenmei='徳島県' OR kenmei='高知県';
Query OK, 4 rows affected (0.17 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> UPDATE todoufuken SET chihou='九州' WHERE kenmei='福岡県' OR kenmei='佐賀県' OR kenmei='長崎県' OR kenmei='熊本県' OR kenmei='大分県' OR kenmei='宮崎県' OR kenmei='鹿児島県' OR kenmei='沖縄県';
Query OK, 8 rows affected (0.16 sec)
Rows matched: 8 Changed: 8 Warnings: 0最後に、todoufukenテーブルの全件を表示します。
mysql> SELECT * FROM todoufuken;
+--------------+-----------------+----------+---------+-----------+
| kenmei | kenchou | jinkou | menseki | chihou |
+--------------+-----------------+----------+---------+-----------+
| 北海道 | 札幌市 | 5212462 | 83424.4 | 北海道 |
| 青森県 | 青森市 | 1230535 | 9645.64 | 東北 |
| 岩手県 | 盛岡市 | 1212201 | 15275 | 東北 |
| 宮城県 | 仙台市 | 2292690 | 7282.29 | 東北 |
| 秋田県 | 秋田市 | 952069 | 11637.5 | 東北 |
| 山形県 | 山形市 | 1064954 | 9323.15 | 東北 |
| 福島県 | 福島市 | 1830114 | 13784.1 | 東北 |
| 茨城県 | 水戸市 | 2854131 | 6097.39 | 関東 |
| 栃木県 | 宇都宮市 | 1932091 | 6408.09 | 関東 |
| 群馬県 | 前橋市 | 1926370 | 6362.28 | 関東 |
| 埼玉県 | さいたま市 | 7343453 | 3797.75 | 関東 |
| 千葉県 | 千葉市 | 6281394 | 5157.57 | 関東 |
| 東京都 | 新宿区 | 13971109 | 2194.03 | 関東 |
| 神奈川県 | 横浜市 | 9214151 | 2416.11 | 関東 |
| 新潟県 | 新潟市 | 2199746 | 12584 | 中部 |
| 富山県 | 富山市 | 1034670 | 4247.58 | 中部 |
| 石川県 | 金沢市 | 1130159 | 4186.21 | 中部 |
| 福井県 | 福井市 | 762679 | 4190.52 | 中部 |
| 山梨県 | 甲府市 | 806210 | 4465.27 | 中部 |
| 長野県 | 長野市 | 2034145 | 13561.6 | 中部 |
| 岐阜県 | 岐阜市 | 1974142 | 10621.3 | 中部 |
| 静岡県 | 静岡市 | 3618972 | 7777.35 | 中部 |
| 愛知県 | 名古屋市 | 7541123 | 5173.07 | 中部 |
| 三重県 | 津市 | 1767615 | 5774.49 | 近畿 |
| 滋賀県 | 大津市 | 1412415 | 4017.38 | 近畿 |
| 京都府 | 京都市 | 2568427 | 4612.2 | 近畿 |
| 大阪府 | 大阪市 | 8817372 | 1905.32 | 近畿 |
| 兵庫県 | 神戸市 | 5438891 | 8401.02 | 近畿 |
| 奈良県 | 奈良市 | 1322970 | 3690.94 | 近畿 |
| 和歌山県 | 和歌山市 | 914055 | 4724.65 | 近畿 |
| 鳥取県 | 鳥取市 | 551402 | 3507.14 | 中国 |
| 島根県 | 松江市 | 666941 | 6707.89 | 中国 |
| 岡山県 | 岡山市 | 1882356 | 7114.33 | 中国 |
| 広島県 | 広島市 | 2794862 | 8479.65 | 中国 |
| 山口県 | 山口市 | 1341506 | 6112.54 | 中国 |
| 徳島県 | 徳島市 | 721269 | 4146.75 | 四国 |
| 香川県 | 高松市 | 948801 | 1876.78 | 四国 |
| 愛媛県 | 松山市 | 1326487 | 5676.19 | 四国 |
| 高知県 | 高知市 | 689785 | 7103.63 | 四国 |
| 福岡県 | 福岡市 | 5106774 | 4986.51 | 九州 |
| 佐賀県 | 佐賀市 | 808821 | 2440.69 | 九州 |
| 長崎県 | 長崎市 | 1310660 | 4130.98 | 九州 |
| 熊本県 | 熊本市 | 1735901 | 7409.46 | 九州 |
| 大分県 | 大分市 | 1124983 | 6340.76 | 九州 |
| 宮崎県 | 宮崎市 | 1063759 | 7735.22 | 九州 |
| 鹿児島県 | 鹿児島市 | 1587342 | 9187.06 | 九州 |
| 沖縄県 | 那覇市 | 1458839 | 2282.59 | 九州 |
+--------------+-----------------+----------+---------+-----------+（2）地方別の人口と面積合計を表示する
mysql> SELECT chihou, SUM(jinkou), SUM(menseki) FROM todoufuken GROUP BY chihou;
+-----------+-------------+--------------------+
| chihou | SUM(jinkou) | SUM(menseki) |
+-----------+-------------+--------------------+
| 北海道 | 5212462 | 83424.4375 |
| 東北 | 8582563 | 66947.7490234375 |
| 関東 | 43522699 | 32433.2197265625 |
| 中部 | 21101846 | 66806.8095703125 |
| 近畿 | 22241745 | 33125.99963378906 |
| 中国 | 7237067 | 31921.550537109375 |
| 四国 | 3686342 | 18803.349853515625 |
| 九州 | 14197079 | 44513.269287109375 |
+-----------+-------------+--------------------+
8 rows in set (0.00 sec)（3）地方別の県の数を表示する
mysql> SELECT chihou, COUNT(kenmei) FROM todoufuken GROUP BY chihou;
+-----------+---------------+
| chihou | COUNT(kenmei) |
+-----------+---------------+
| 北海道 | 1 |
| 東北 | 6 |
| 関東 | 7 |
| 中部 | 9 |
| 近畿 | 7 |
| 中国 | 5 |
| 四国 | 4 |
| 九州 | 8 |
+-----------+---------------+
8 rows in set (0.02 sec)（4）地方別の県の数が最大となる地方名の県の数を表示する
上記（3）で、県の数の最大値が9となることがわかったので、以下のようなSELECT文で表示させてみました。
mysql> SELECT chihou, COUNT(kenmei) AS kensuu FROM todoufuken GROUP BY chihou HAVING COUNT(kenmei)=9;
+--------+--------+
| chihou | kensuu |
+--------+--------+
| 中部 | 9 |
+--------+--------+
1 row in set (0.01 sec)【演習問題3.2】
テーブルtodoufukenについて、SQL命令文を作成して以下の要求を実現せよ。
仕様要求
（1）各都道府県の人口密度（単位：人/km2）をjinkoumitudoとして計算し、県名（kenmei）と人口密度（jinkoumitudo）を表示する。
（2）todoufukenテーブルに人口密度（jinkoumitudo）の列名を追加して、各県データからjinkoumitudoの値を算出して更新する。
（3）todoufukenテーブルの全件を表示する。
（1）各都道府県の人口密度（単位：人/km2）をjinkoumitudoとして計算し、県名（kenmei）と人口密度（jinkoumitudo）を表示する。
mysql> SELECT kenmei, jinkou/menseki AS jinkoumitudo FROM todoufuken;
+--------------+--------------------+
| kenmei | jinkoumitudo |
+--------------+--------------------+
| 北海道 | 62.48123638831847 |
| 青森県 | 127.57422471192304 |
| 岩手県 | 79.3584435361833 |
| 宮城県 | 314.83091001620613 |
| 秋田県 | 81.81030308421207 |
| 山形県 | 114.22683914558287 |
| 福島県 | 132.76954867527422 |
| 茨城県 | 468.09059876492046 |
| 栃木県 | 301.50810102708306 |
| 群馬県 | 302.7798312948117 |
| 埼玉県 | 1933.6325455862025 |
| 千葉県 | 1217.898005084494 |
| 東京都 | 6367.783855938083 |
| 神奈川県 | 3813.6304184547353 |
| 新潟県 | 174.8055466505251 |
| 富山県 | 243.5904635038057 |
| 石川県 | 269.9718863950391 |
| 福井県 | 182.00103959539442 |
| 山梨県 | 180.55123127461692 |
| 長野県 | 149.99344208559393 |
| 岐阜県 | 185.866499524972 |
| 静岡県 | 465.32198686678623 |
| 愛知県 | 1457.7655543512558 |
| 三重県 | 306.1075399309801 |
| 滋賀県 | 351.57616187672846 |
| 京都府 | 556.8767380501739 |
| 大阪府 | 4627.764495497643 |
| 兵庫県 | 647.4084460544921 |
| 奈良県 | 358.4371517830625 |
| 和歌山県 | 193.4651283995807 |
| 鳥取県 | 157.2226990907569 |
| 島根県 | 99.42634515571281 |
| 岡山県 | 264.5865428408826 |
| 広島県 | 329.5963714600741 |
| 山口県 | 219.46784666064144 |
| 徳島県 | 173.93597395550734 |
| 香川県 | 505.547259236056 |
| 愛媛県 | 233.69320154768621 |
| 高知県 | 97.10317279746806 |
| 福岡県 | 1024.1179181486925 |
| 佐賀県 | 331.3903115173992 |
| 長崎県 | 317.27580530450234 |
| 熊本県 | 234.28171677175254 |
| 大分県 | 177.42085200875167 |
| 宮崎県 | 137.52148878174992 |
| 鹿児島県 | 172.78020109169773 |
| 沖縄県 | 639.1156291001572 |
+--------------+--------------------+
47 rows in set (0.04 sec)（2）todoufukenテーブルに人口密度（jinkoumitudo）の列名を追加して、各県データからjinkoumitudoの値を算出して更新する。
ALTER文を用いて、新規列名「jinkoumitudo」を追加します。
mysql> ALTER TABLE todoufuken ADD jinkoumitudo FLOAT;
Query OK, 0 rows affected (0.76 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW fields FROM todoufuken;
+--------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| kenmei | char(20) | YES | | NULL | |
| kenchou | char(20) | YES | | NULL | |
| jinkou | int | YES | | NULL | |
| menseki | float | YES | | NULL | |
| chihou | char(30) | YES | | NULL | |
| jinkoumitudo | float | YES | | NULL | |
+--------------+----------+------+-----+---------+-------+次に、UPDATE文でNULLになっている各県の人口密度の値を更新していきます。
mysql> UPDATE todoufuken SET jinkoumitudo=jinkou/menseki;
Query OK, 47 rows affected (0.23 sec)
Rows matched: 47 Changed: 47 Warnings: 0（3）todoufukenテーブルの全件を表示する。
mysql> SELECT * FROM todoufuken;
+--------------+-----------------+----------+---------+-----------+--------------+
| kenmei | kenchou | jinkou | menseki | chihou | jinkoumitudo |
+--------------+-----------------+----------+---------+-----------+--------------+
| 北海道 | 札幌市 | 5212462 | 83424.4 | 北海道 | 62.4812 |
| 青森県 | 青森市 | 1230535 | 9645.64 | 東北 | 127.574 |
| 岩手県 | 盛岡市 | 1212201 | 15275 | 東北 | 79.3584 |
| 宮城県 | 仙台市 | 2292690 | 7282.29 | 東北 | 314.831 |
| 秋田県 | 秋田市 | 952069 | 11637.5 | 東北 | 81.8103 |
| 山形県 | 山形市 | 1064954 | 9323.15 | 東北 | 114.227 |
| 福島県 | 福島市 | 1830114 | 13784.1 | 東北 | 132.77 |
| 茨城県 | 水戸市 | 2854131 | 6097.39 | 関東 | 468.091 |
| 栃木県 | 宇都宮市 | 1932091 | 6408.09 | 関東 | 301.508 |
| 群馬県 | 前橋市 | 1926370 | 6362.28 | 関東 | 302.78 |
| 埼玉県 | さいたま市 | 7343453 | 3797.75 | 関東 | 1933.63 |
| 千葉県 | 千葉市 | 6281394 | 5157.57 | 関東 | 1217.9 |
| 東京都 | 新宿区 | 13971109 | 2194.03 | 関東 | 6367.78 |
| 神奈川県 | 横浜市 | 9214151 | 2416.11 | 関東 | 3813.63 |
| 新潟県 | 新潟市 | 2199746 | 12584 | 中部 | 174.806 |
| 富山県 | 富山市 | 1034670 | 4247.58 | 中部 | 243.59 |
| 石川県 | 金沢市 | 1130159 | 4186.21 | 中部 | 269.972 |
| 福井県 | 福井市 | 762679 | 4190.52 | 中部 | 182.001 |
| 山梨県 | 甲府市 | 806210 | 4465.27 | 中部 | 180.551 |
| 長野県 | 長野市 | 2034145 | 13561.6 | 中部 | 149.993 |
| 岐阜県 | 岐阜市 | 1974142 | 10621.3 | 中部 | 185.867 |
| 静岡県 | 静岡市 | 3618972 | 7777.35 | 中部 | 465.322 |
| 愛知県 | 名古屋市 | 7541123 | 5173.07 | 中部 | 1457.77 |
| 三重県 | 津市 | 1767615 | 5774.49 | 近畿 | 306.108 |
| 滋賀県 | 大津市 | 1412415 | 4017.38 | 近畿 | 351.576 |
| 京都府 | 京都市 | 2568427 | 4612.2 | 近畿 | 556.877 |
| 大阪府 | 大阪市 | 8817372 | 1905.32 | 近畿 | 4627.76 |
| 兵庫県 | 神戸市 | 5438891 | 8401.02 | 近畿 | 647.408 |
| 奈良県 | 奈良市 | 1322970 | 3690.94 | 近畿 | 358.437 |
| 和歌山県 | 和歌山市 | 914055 | 4724.65 | 近畿 | 193.465 |
| 鳥取県 | 鳥取市 | 551402 | 3507.14 | 中国 | 157.223 |
| 島根県 | 松江市 | 666941 | 6707.89 | 中国 | 99.4263 |
| 岡山県 | 岡山市 | 1882356 | 7114.33 | 中国 | 264.587 |
| 広島県 | 広島市 | 2794862 | 8479.65 | 中国 | 329.596 |
| 山口県 | 山口市 | 1341506 | 6112.54 | 中国 | 219.468 |
| 徳島県 | 徳島市 | 721269 | 4146.75 | 四国 | 173.936 |
| 香川県 | 高松市 | 948801 | 1876.78 | 四国 | 505.547 |
| 愛媛県 | 松山市 | 1326487 | 5676.19 | 四国 | 233.693 |
| 高知県 | 高知市 | 689785 | 7103.63 | 四国 | 97.1032 |
| 福岡県 | 福岡市 | 5106774 | 4986.51 | 九州 | 1024.12 |
| 佐賀県 | 佐賀市 | 808821 | 2440.69 | 九州 | 331.39 |
| 長崎県 | 長崎市 | 1310660 | 4130.98 | 九州 | 317.276 |
| 熊本県 | 熊本市 | 1735901 | 7409.46 | 九州 | 234.282 |
| 大分県 | 大分市 | 1124983 | 6340.76 | 九州 | 177.421 |
| 宮崎県 | 宮崎市 | 1063759 | 7735.22 | 九州 | 137.521 |
| 鹿児島県 | 鹿児島市 | 1587342 | 9187.06 | 九州 | 172.78 |
| 沖縄県 | 那覇市 | 1458839 | 2282.59 | 九州 | 639.116 |
+--------------+-----------------+----------+---------+-----------+--------------+
47 rows in set (0.00 sec)【演習問題3.3】
テーブルtodoufukenについて、SQL命令文を作成して以下の要求を実現せよ。
仕様要求
（1）各地方別に人口、面積のビューを作成する
（2）（1）で作成したビューを用いて、日本の全人口と全面積を算出して表示する
（1）各地方別に人口、面積のビューを作成する
地方別の人口をchihoujinkou、地方別の面積をchihoumensekiとして、集約関数SUM( )を用いて算出した値を指定します。
mysql> CREATE VIEW chihouview AS SELECT chihou, SUM(jinkou) AS chihoujinkou, SUM(menseki) AS chihoumenseki FROM todoufuken GROUP BY chihou;
Query OK, 0 rows affected (0.23 sec)（2）（1）で作成したビューを用いて、日本の全人口と全面積を算出して表示する
mysql> SELECT SUM(chihoujinkou) AS zenjinkou, SUM(chihoumenseki) AS zenmenseki FROM chihouview;
+-----------+--------------------+
| zenjinkou | zenmenseki |
+-----------+--------------------+
| 125781803 | 377976.38513183594 |
+-----------+--------------------+
1 row in set (0.09 sec)サポート、本当にありがとうございます。サポートしていただいた金額は、知的サイドハッスルとして取り組んでいる、個人研究の費用に充てさせていただきますね♪