郵便番号テーブルを用いた SQL 演習

演習で行うこと

SQL の中で日本語を使う．

郵便番号データベース (Japanese ZIP code database)

郵便番号データベースは zips, kens, shichosons の 3 つのテーブルから構成される．

郵便番号データベースの作成手順については別の Web ページで説明している．

Sqliteman で既存のデータベースを開く (Open an existing database using Sqliteman)

すでに作成済みのデータベースを，下記の手順で開くことができる．

以下の手順で，既存のデータベースファイルを開く． (Open an existing database file)

Sqliteman を起動する
「File」→ 「Open」
データベースファイルを開く (Open Database File)
* Ubuntu での実行例（「SQLite/mydb」を開く場合）
データベースファイル SQLite/mydb を選び，「開く」をクリック (Click '開く' after choosing the database file "SQLite/mydb")
データベースの中身が表示されるので確認する (Database appears)
「Tables」を展開すると，テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears by clicking 'Tables')
zips, kens, shichosons テーブルの中身を表示してみる． zips, kens, shichosons テーブルの表示ができないというときは，授業の場で個別対応します

郵便番号に関する条件検索

テーブル zips を使用．住所(jusho)の取得などを行う．

テーブル zips を使用．郵便番号(zipcode)が「8120053」であるタップルを選択し，住所(jusho)を得る．
```
SELECT *
FROM zips
WHERE zipcode=8120053;
```
テーブル zips を使用．郵便番号(zipcode)が「10000」より大きく、かつ「20000」未満であるタップルを選択し，住所(ken_kanji, shichoson_kanji, choiki_kanji)を得る．
```
SELECT *
FROM zips
WHERE zipcode>10000 and zipcode<20000;
```

都道府県名に関する条件検索

テーブル kens, shichosons を使用．都道府県名(ken_kanji)による条件検索を行う．

テーブル kens にある都道府県名(ken_kanji)を全て表示
```
SELECT ken_kanji
FROM kens;
```
都道府県名(kenn_kanji)が「福岡県」であるようなタップルをテーブル kens から選択し，都道府県名(ken_kanji)とその読み仮名(ken_kana)を得る．
```
SELECT ken_kanji, ken_kana
FROM kens
WHERE ken_kanji='福岡県';
```
都道府県名(kenn_kanji)が「京都府」であるようなタップルをテーブル kens から選択し，都道府県名(ken_kanji)とその読み仮名(ken_kana)を得る．
```
SELECT ken_kanji, ken_kana
FROM kens
WHERE ken_kanji='京都府';
```
都道府県名が「福岡県」であるようなタップルをテーブル shichosons から選択し，都道府県名, 市町村名を得る．
```
SELECT ken_kanji, shichoson_kanji
FROM shichosons
WHERE ken_kanji='福岡県';
```

市町村名に関する条件検索

テーブル shichosons を使用．市町村名(shichoson_kanji)による条件検索を行う．

市町村名(shichoson_kanji)が「福岡市西区」であるようなタップルをテーブル shichosons から選択し，都道府県名(shichoson_kanji)とその読み仮名(shichoson_kana)を得る．
```
SELECT shichoson_kanji, shichoson_kana
FROM shichosons
WHERE shichoson_kanji='福岡市西区';
```

町域名に関する条件検索

テーブル zips の属性 choiki_kanji （これは文字列属性）に関する条件検索を行う．文字列属性に対して「=」を使う．

町域名(choiki_kanji)が「箱崎」であるタップルをテーブル zips から選択し，その郵便番号(zipcode)などを得る．
```
SELECT *
FROM zips
WHERE choiki_kanji='箱崎';
```
町域名(choiki_kanji)が「箱」であるタップルをテーブル zips から選択し，その郵便番号(zipcode)などを得る．
```
SELECT *
FROM zips
WHERE choiki_kanji='箱';
```

文字列による郵便番号の検索（LIKE と % を用いた部分一致）

テーブル zips の属性 ken_kanji, shichoson_kanji, choiki_kanji （これは文字列属性）に関する条件検索を行う．文字列属性に対して「LIKE」を使う．

「泉原町」の郵便番号を得る(市の名前が分からない)
```
SELECT *
FROM zips
WHERE choiki_kanji = '泉原町';
```
町域名 (choiki_kanji) に，「空港」という文字列を含むタップルを選択し，郵便番号(zipcode) などを得る．
```
SELECT *
FROM zips
WHERE choiki_kanji LIKE '%空港%';
```
町域名 (choiki_kanji) に，「箱崎」という文字列を含むタプルを選択し，郵便番号(zipcode) などを得る．
```
SELECT *
FROM zips
WHERE choiki_kanji LIKE '%箱崎%';
```
市町村名 (shichoson_kanji) に，「福岡市西区」という文字列を含むタプルを選択し，JIS コード(jiscode) などを得る．
```
SELECT *
FROM shichosons
WHERE shichoson_kanji LIKE '%福岡市西区%';
```

２つのテーブルの結合（Join; ジョイン）を行う例

テーブル zips とテーブル shichosons を，jiscode属性によって結合(join)し，条件で絞り込む

都道府県名(ken_kanji)が「福岡県」であるタップルを選択し，住所(ken_kanji, shichoson_kanji, choiki_kanji)と，その郵便番号(zipcode)を得る．
```
SELECT *
FROM zips, shichosons
WHERE zips.jiscode = shichosons.jiscode
      AND shichosons.ken_kanji = '福岡県';
```
市町村の読み(shichoson_kanji)が「太宰府市」であるタップルを選択し，住所(ken_kanji, shichoson_kanji, choiki_kanji)，郵便番号(zipcode)などを得る．
```
SELECT *
FROM zips, shichosons
WHERE zips.jiscode = shichosons.jiscode
      AND shichoson_kanji='太宰府市';
```
NATURAL JOIN を使って同じ結果が得られる
```
SELECT *
FROM zips NATURAL JOIN shichosons
WHERE shichoson_kanji='太宰府市';
```

テーブル zips とテーブル shichosons とテーブル kens の結合．

郵便番号(zipcode)が 8120053 であるタップルを選択し，都道府県名 (ken_kanji)と，そのよみ仮名(ken_kana)などを得る．


SELECT *
FROM zips, shichosons, kens
WHERE zips.jiscode = shichosons.jiscode
      AND shichosons.ken_kanji=kens.ken_kanji
      AND zipcode=8120053;

住所(ken_kanji, shichoson_kanji)が「福岡県,福岡市東区」であるタップルを，テーブル zips から選択し，他のテーブルとの結合によって，都道府県と市町村と町域の読み仮名(ken_kana と shichoson_kana と choiki_kana )などを得る．
```
SELECT *
FROM zips, kens, shichosons
WHERE zips.jiscode = shichosons.jiscode
      AND shichosons.ken_kanji=kens.ken_kanji
      AND kens.ken_kanji = '福岡県'
      AND shichosons.shichoson_kanji = '福岡市東区';
```

タップルの総数 (COUNT)

COUNT(*) でタップル数を数える。

テーブル zips のタップル数を得る．
```
select count(*)
FROM zips;
```
テーブル zips において，choiki_kana が空文字で無いものだけを数える。
```
select count(*)
FROM zips
WHERE choiki_kanji != '';
```
テーブル zips について，郵便番号(zipcode)が10000以上 20000以下のタップルのタップル数を得る．
```
select count(distinct zipcode)
FROM zips
WHERE zipcode>=10000 and zipcode<=20000;
```

ORDER BY の例

テーブル zips について，町域名が「田原」である住所(ken_kanji, shichoson_kanji, choiki_kanji)とその郵便番号(zipcode)などを，郵便番号順に表示


SELECT *
FROM zips
WHERE choiki_kanji='田原'
ORDER BY zipcode;


SELECT *
FROM zips, kens, shichosons
WHERE zips.jiscode = shichosons.jiscode
      AND shichosons.ken_kanji=kens.ken_kanji
      AND choiki_kanji='田原'
ORDER BY zipcode;

group by と COUNT の組み合わせ

group by による集約の後、COUNT を使って、タップル数を数える。

テーブル zips において，町域漢字(choiki_kanji)に，「箱崎」という文字列を含むタップルを選択し，それをテーブル shichosons と結合した後，都道府県名(ken_kanji)でグループ化．都道府県ごとにタップル数を求める．
```
select count(*), *
FROM zips, shichosons
WHERE zips.jiscode = shichosons.jiscode
      AND zips.choiki_kanji LIKE '%箱崎%'
      group by shichosons.ken_kanji;
```
（参考）　group by, COUNT の無い場合
```
SELECT *
FROM zips, shichosons
WHERE zips.jiscode = shichosons.jiscode
      AND zips.choiki_kanji LIKE '%箱崎%'
```
テーブル shichosons について，都道府県名(ken_kanji)でグループ化，都道府県ごとにタップル数を求める（都道府県ごとの市町村数が得られる）．
```
SELECT ken_kanji, COUNT(*)
FROM shichosons
group by ken_kanji;
```
テーブル zips, shichosons, kens を使用。結合の後，都道府県名(ken_kanji)でグループ化．都道府県ごとのタップル数を得る（都道府県ごとの郵便番号数が得られる）．
```
select count(*), *
FROM zips, shichosons, kens
WHERE zips.jiscode = shichosons.jiscode
      AND shichosons.ken_kanji = kens.ken_kanji
group by kens.ken_kanji;
```

HAVING の例

COUNT で数えたタップル数に関して条件を指定し，タップルを絞り込む．

テーブル shichosons について，都道府県名(ken_kanji)でグループ化，都道府県ごとに市町村(shichoson)の数を求め，２０以下のものだけを選択．（市町村数が20以下であるような都道府県を得る）．
```
SELECT ken_kanji, COUNT(*)
FROM shichosons
group by ken_kanji
HAVING COUNT(*) <= 20;
```
テーブル zips, shichosons, kens を使用。結合の後，都道府県名 (ken_kanji)でグループ化．都道府県ごとのタップル数が 3000 以上のものを得る（都道府県ごとの郵便番号数が得られる）．
```
SELECT kens.ken_kanji, COUNT(*)
FROM zips, shichosons, kens
WHERE zips.jiscode = shichosons.jiscode
      AND shichosons.ken_kanji = kens.ken_kanji
group by kens.ken_kanji
HAVING COUNT(*)>3000;
```