CREATE DATABASE MyDB;
GO

USE MyDB;
GO

CREATE TABLE [Country] (
    [Code] 	char(2) NOT NULL,
    [Name] 	varchar(128) NOT NULL,
    PRIMARY KEY (Code)
);
GO

INSERT INTO Country VALUES('cn','China');
INSERT INTO Country VALUES('jp','Japan');
INSERT INTO Country VALUES('kr','South Korea');
INSERT INTO Country VALUES('my','Malaysia');
INSERT INTO Country VALUES('om','Oman');
INSERT INTO Country VALUES('sa','Saudi Arabia');

SELECT * FROM Country;
GO

CREATE TABLE [State] (
    [ID] 	int NOT NULL IDENTITY,
    [Country] 	char(2) NOT NULL,
    [Name] 	varchar(128),
    [LocalName] nvarchar(128),
	[Population] int,
    PRIMARY KEY (ID)	
);
GO

INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Selangor',		'Selangor',			7063200);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Pahang',			'Pahang',			1599300);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Sabah',			'Sabah',			3400800);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Sarawak',		'Sarawak', 			2492000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Perlis',			'Perlis',			287200);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Kedah',			'Kedah',			2157100);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Negeri Sembilan','Negeri Sembilan', 	1202300);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Johor',			'Johor', 			4022100);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Penang',			'Pulau Pinang',		1755300);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Terengganu',		'Terengganu', 		1173200);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Kelantan',		'Kelantan', 		1811700);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Perak',			'Perak',			2504000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Malacca',		'Melaka', 			1007700);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('my','Federal Territories','Wilayah Persekutuan',2173000);

INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Anhui',		N'安徽',61270000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Beijing',	N'北京',21840000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Chongqing',	N'重庆',32130000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Fujian',		N'福建',41880000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Gansu',		N'甘肃',24920000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Guangdong',	N'广东',127060000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Guangxi',	N'广西',50470000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Guizhou',	N'贵州',38560000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Hainan',		N'海南',10270000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Hebei',		N'河北',74200000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Heilongjiang',	N'黑龙江',30990000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Hong Kong',	N'香港',7472600);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Henan',		N'河南',101113000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Hubei',		N'湖北',58440000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Hunan',		N'湖南',66040000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Inner Mongolia',N'內蒙古',24010000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Jiangsu',	N'江苏',85150000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Jiangxi',	N'江西',45280000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Jilin',		N'吉林',23480000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Liaoning',	N'辽宁',41970000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Macau',		N'澳门',677300);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Ningxia',	N'宁夏',7280000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Qinghai',	N'青海',5950000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Sichuan',	N'四川',83740000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Shaanxi',	N'陕西',39560000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Shandong',	N'山东',101630000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Shanghai',	N'上海',24750000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Shanxi',		N'山西',34810000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Taiwan',		N'台湾',23264640);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Tianjin',	N'天津',13630000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Tibet',		N'西藏',3640000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Xinjiang',	N'新疆',25870000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Yunnan',		N'云南',46930000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('cn','Zhejiang',	N'浙江',65770000);

INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Busan',		   N'부산시',3330000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','North Chungcheong',N'충청북도',1590000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','South Chungcheong',N'충청남도',2120000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Daegu',		   N'대구시',2380000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Daejeon',	   N'대전시',1440000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Gangwon',	   N'강원도',1530000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Gwangju',	   N'광주시',1430000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Gyeonggi',	   N'경기도',13580000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','North Gyeongsang', N'경상북도',2600000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','South Gyeongsang', N'경상남도',3300000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Incheon',	   	N'인천시',2990000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Jeju',		   	N'제주도',678000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','North Jeolla',	N'전라북도',1770000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','South Jeolla',	N'전라남도',1750000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Sejong',	   		N'세종시',383000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Seoul',		   	N'서울시',9420000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('kr','Ulsan',		   	N'울산시',1110000);

INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Asir',	  		N'عسير',2024285);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Al-Baha',	  	N'الباحة',339174);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Eastern Province',	N'الشرقية',5125254);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Hail',	  		N'حائل',746406);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Al-Jouf',		N'الجوف',595822);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Jazan',		  	N'جازان',1404997);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Madinah',		N' المدينة المنورة',2137983);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Makkah',	  		N'مكة',8021463);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Najran',	  		N'نجران',592300);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Northern Borders',N'الحدود الشمالية',373577);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Al-Qassim',	  	N'القصيم',1336179);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Riyadh',	  		N'الرياض',8591748);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('sa','Tabuk',		  	N'تبوك',886036);

INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Aichi',			N'愛知県',7542000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Akita',			N'秋田県',930000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Aomori',			N'青森県',1208000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Chiba',			N'千葉県',6261000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Ehime',			N'愛媛県',1302000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Fukui',			N'福井県',754000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Fukuoka',		N'福岡県',5116000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Fukushima',		N'福島県',1801000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Gifu',			N'岐阜県',1952000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Gunma',			N'群馬県',1913000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Hiroshima',		N'広島県',	2763000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Hokkaido',		N'北海道',5140000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Hyogo',			N'兵庫県',5410000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Ibaraki',		N'茨城県',2841000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Ishikawa',		N'石川県',1118000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Iwate',			N'岩手県',1181000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Kagawa',			N'香川県',936000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Kagoshima',		N'鹿児島県',1568000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Kanagawa',		N'神奈川県',9212000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Kochi',			N'高知県',676000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Kumamoto',		N'熊本県',1722000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Kyoto',			N'京都府',2544000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Mie',			N'三重県',1748000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Miyagi',			N'宮城県',2281000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Miyazaki',		N'宮崎県',1054000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Nagano',			N'長野県',2022000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Nagasaki',		N'長崎県',1284000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Nara',			N'奈良県',1304000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Niigata',		N'新潟県',2162000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Oita',			N'大分県',1105000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Okayama',		N'岡山県',1864000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Okinawa',		N'沖縄県',1468000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Osaka',			N'大阪府',8784000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Saga',			N'佐賀県',800000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Saitama',		N'埼玉県',7340000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Shiga',			N'滋賀県',1411000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Shimane',		N'島根県',658000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Shizuoka',		N'静岡県',3598000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Tochigi',		N'栃木県',1910000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Tokushima',		N'徳島県',704000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Tokyo',			N'東京都',13842000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Tottori',		N'鳥取県',547000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Toyama',			N'富山県',1017000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Wakayama',		N'和歌山県',903000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Yamagata',		N'山形県',1041000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Yamaguchi',		N'山口県',1317000);
INSERT INTO State (Country,Name,LocalName,Population) VALUES('jp','Yamanashi',		N'山梨県',799000);
	
SELECT * FROM State;
GO