郵便番号DB（テーブル定義）

前回はAccessにデータを取り込むところまででした．今回はSQL Server 2005でテーブルを定義します．

アップサイジングウィザードでSQL Serverにアップサイジングします．Accessではデータ型はデータ長255のテキスト型になっています．属性によって過不足があるので，以下のように定義し直します．

ALTER TABLE dbo.KEN_ALL(

	ID	int	NOT NULL CONSTRAINT PK_KEN_ALL PRIMARY KEY, 

	LGCode	nchar(5)	NOT NULL, 

	OldZIPCode	nvarchar(5)	NOT NULL, 

	ZIPCode	nchar(7)	NOT NULL, 

	PREF_kana	nvarchar(7)	NOT NULL, 

	CITY_kana	nvarchar(22)	NOT NULL, 

	AREA_kana	nvarchar(MAX)	NULL,

	PREF	nvarchar(4)	NOT NULL, 

	CITY	nvarchar(10)	NOT NULL, 

	AREA	nvarchar(MAX)	NULL, 

	Flag10	nchar(1)	NOT NULL, 

	Flag11	nchar(1)	NOT NULL, 

	Flag12	nchar(1)	NOT NULL, 

	Flag13	nchar(1)	NOT NULL, 

	Flag14	nchar(1)	NOT NULL, 

	Flag15	nchar(1)	NOT NULL);

データ長は以下のクエリで求めます．

WITH CTE(CHARA, CHARALEN) AS

(SELECT LGCode, LEN(LGCode) 

   FROM KEN_ALL 

  GROUP BY LGCode)

SELECT MIN(CHARALEN) AS MIN_LGCode, MAX(CHARALEN) AS MAX_LGCode FROM CTE;
WITH CTE(CHARA, CHARALEN) AS

(SELECT ZIPCode, LEN(ZIPCode) 

   FROM KEN_ALL 

  GROUP BY ZIPCode)

SELECT MIN(CHARALEN) AS MIN_ZIPCode, MAX(CHARALEN) AS MAX_ZIPCode FROM CTE;
WITH CTE(CHARA, CHARALEN) AS

(SELECT OldZIPCode, LEN(OldZIPCode) 

   FROM KEN_ALL 

  GROUP BY OldZIPCode)

SELECT MIN(CHARALEN) AS MIN_OldZIPCode, MAX(CHARALEN) AS MAX_OldZIPCode FROM CTE;
WITH CTE(CHARA, CHARALEN) AS

(SELECT PREF, LEN(PREF) 

   FROM KEN_ALL 

  GROUP BY PREF)

SELECT MIN(CHARALEN) AS MIN_PREF, MAX(CHARALEN) AS MAX_PREF FROM CTE;
WITH CTE(CHARA, CHARALEN) AS

(SELECT CITY, LEN(CITY) 

   FROM KEN_ALL 

  GROUP BY CITY)

SELECT MIN(CHARALEN) AS MIN_CITY, MAX(CHARALEN) AS MAX_CITY FROM CTE;
WITH CTE(CHARA, CHARALEN) AS

(SELECT PREF_kana, LEN(PREF_kana) 

   FROM KEN_ALL 

  GROUP BY PREF_kana)

SELECT MIN(CHARALEN) AS MIN_PREFkana, MAX(CHARALEN) AS MAX_PREFkana FROM CTE;
WITH CTE(CHARA, CHARALEN) AS

(SELECT CITY_kana, LEN(CITY_kana) 

   FROM KEN_ALL 

  GROUP BY CITY_kana)

SELECT MIN(CHARALEN) AS MIN_CITYkana, MAX(CHARALEN) AS MAX_CITYkana FROM CTE;
 MIN_LGCode  MAX_LGCode

 ----------  ----------

 5           5
 MIN_ZIPCode MAX_ZIPCode

 ----------- -----------

 7           7
 MIN_OldZIPCode MAX_OldZIPCode

 -------------- --------------

 3              5
 MIN_PREF    MAX_PREF

 --------    --------

 3           4
 MIN_CITY    MAX_CITY

 --------    --------

 2           10
 MIN_PREFkana MAX_PREFkana

 ------------ ------------

 4            7
 MIN_CITYkana MAX_CITYkana

 ------------ ------------

 2            22