総合演習2

問題1

日本国内の地方公共団体（市町村および東京23区）に関するデータを保持するテーブル municipality_list の構造は以下の通りである。

カラム名	型	意味
id	VARCHAR(5)	地方公共団体コード（5桁の数字）
name	VARCHAR(10)	地方公共団体名（漢字）
ruby	VARCHAR(20)	地方公共団体名（フリガナ）
prefecture_id	VARCHAR(2)	所属する都道府県のコード（2桁の数字）
prefecutre_name	VARCHAR(5)	所属する都道府県の名称（漢字）
area	FLOAT	面積（平方km）
population	INT	人口（人）

ただし、主キーはidである。

このテーブルについて、以下の操作を行うSQLを記述しなさい。

(１) 神奈川県内の地方公共団体（市町村）のレコードを抽出する。すべてのカラムを表示すること。

(２) 全国の地方公共団体を名前の50音順（rubyの昇順）に並べ替える。すべてのカラムを表示すること。

(３) 全国の地方公共団体のうち、人口（population）が30万人以上のレコードを抽出する。すべてのカラムを表示すること。

(４) 全国の地方公共団体のうち、町または村（nameが『～町』または『～村』）であるレコードを抽出し、地方公共団体コード（id）順に並べる。すべてのカラムを表示すること。

(５) 全国の地方公共団体のうち、市（nameが『～市』）の数を求める。数のカラム名はnumberとすること。

(６) 全国の地方公共団体の人口密度（平方kmあたりの人口）を求め、人口密度の降順に並べる。人口密度は小数第2位まで求め、カラム名はdensity とすること。表示カラムは地方公共団体コード（id）、地方公共団体名（name）、人口密度（density）とすること。

(７) 都道府県ごとの面積を求める。都道府県コード（prefecture_id）、都道府県名（prefecture_name）、都道府県の面積を表示し、面積の降順に並べること。都道府県の面積のカラム名はprefecture_area とすること。表示カラムは都道府県コード（prefectgure_id）、都道府県名（prefecture_name）、面積（prefecture_area）とすること。

※ヒント：都道府県でグループ化し、面積（area）の合計を求める

（８） 人口が100万人未満の都道府県を抽出し、都道府県コードの昇順に表示する。表示カラムは都道府県コード（prefecture_id）、都道府県名（prefecture_name）、都道府県人口（カラム名はprefecture_populationとする）とすること。

※ヒント：都道府県でグループ化し、人口（population）の合計を求めてから合計が100万未満のグループを抽出する。

（９） 全国の地方公共団体のうち、同一名称の『市』は２組ある（『府中市』が東京都と広島県に、『伊達市』が北海道と福島県にある）。このような、同一名称の地方公共団体を抽出する。なお、漢字表記（name）が同一ならば読み方（ruby）が異なっても同一名称とする。

問題2

都道府県の情報を保持するテーブル prefecture_list を、下記ように設計した。

カラム名	型	意味
id	VARCHAR(2)	都道府県コード（2桁の数字）
name	VARCHAR(5)	都道府県名（漢字）
ruby	VARCHAR(10)	都道府県名（フリガナ）
capital_id	VARCHAR(5)	県庁所在地の地方公共団体コード（5桁の数字）

ただし、主キーはidである。

（１） 上記のテーブルを作成するSQLを記述しなさい。

（２）テーブル prefecture_listに、下記のレコードを挿入するSQLを記述しなさい。