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

【概要】

zips，kens，shichosons の3つのテーブルを用いて，日本語を含むデータに対する SQL の条件検索，結合（join），集計（COUNT，group by，HAVING）などを SQLite 3 で学ぶ．

【目次】

演習で行うこと
郵便番号データベース (Japanese ZIP code database)
Sqliteman で既存のデータベースを開く
郵便番号に関する条件検索
都道府県名に関する条件検索
市町村名に関する条件検索
町域名に関する条件検索
文字列による郵便番号の検索（LIKE と % を用いた部分一致）
２つのテーブルの結合（Join；ジョイン）を行う例
タプルの総数 (COUNT)
ORDER BY の例
group by と COUNT の組み合わせ
HAVING の例

演習で行うこと

SQLデータベースにおける日本語の活用について学習する．

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

郵便番号データベースは，zips，kens，shichosonsの3つのテーブルで構成されている．

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

Sqliteman で既存のデータベースを開く

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

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

Sqliteman を起動する
「File」→ 「Open」
データベースファイルを開く
* Windows での実行例（「C:\SQLiteDB\mydb」を開く場合）
データベースファイル C:\SQLiteDB\mydb を選択し，「開く」をクリックする (Click '開く' after choosing the database file "C:\SQLiteDB\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;
```
都道府県名(ken_kanji)が「福岡県」であるタプルをテーブル kens から選択し，都道府県名(ken_kanji)とその読み仮名(ken_kana)を取得する．
```
SELECT ken_kanji, ken_kana
FROM kens
WHERE ken_kanji='福岡県';
```
都道府県名(ken_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_kanji が空文字でないものだけを計数する．
```
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以下のものだけを選択する（市町村数が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;
```