DDLs

CREATE DATABASE STATE_CAPITALS;
GO
USE STATE_CAPITALS;
GO
CREATE TABLE Country(
	Country_Code varchar(10) NOT NULL,
	Country_Name varchar(50) NOT NULL,
	Population int NOT NULL,
	Country_Size float NOT NULL,
CONSTRAINT PK_Country PRIMARY KEY (Country_Code)
)
GO
CREATE TABLE Region(
	Country_Code varchar(10) NOT NULL,
	Region_Code varchar(10) NOT NULL,
	Region_Name varchar(50) NOT NULL,
CONSTRAINT PK_Region PRIMARY KEY (Country_Code),
CONSTRAINT FK_Region FOREIGN KEY (Country_Code) references Country(Country_Code)
)
GO
CREATE TABLE State(
	Region_Code varchar(10) NOT NULL,
	State_Code char(2) NOT NULL,
	State_Name varchar(50) NOT NULL,
	Date_of_Statehood int NOT NULL,
	State_Size float NOT NULL,
	State_Population int NOT NULL,
CONSTRAINT PK_State PRIMARY KEY (State_Code),
CONSTRAINT FK_State FOREIGN KEY (Region_Code) references Region(Region_Code)
)
GO
CREATE TABLE Capital(
	State_Code char(2) NOT NULL,
	Capital_Code tinyint NOT NULL,
	Capital_Name varchar(50) NOT NULL,
	Capital_Since smallint NOT NULL,
	Land_Area float NOT NULL,
	Most_Populous_City bit NOT NULL,
	Municipal_Population int NOT NULL,
	Metropolitan_Population int NULL,
	Note varchar(300) NULL,
CONSTRAINT PK_Capital PRIMARY KEY (Capital_Code),
CONSTRAINT FK_Capital FOREIGN KEY (State_Code) references State(State_Code)
)
GO

Capital

State_Code	Capital_Code	Capital_Name	Capital_Since	Land_Area	Most_Populous_City	Municipal_Population	Metropolitan_Population	Note
AL	01	Montgomery	1846	155.40	No	205764	374536	Birmingham is the state's largest city
AK	02	Juneau	1906	2716.70	No	31275		Juneau is the largest capital by land area. Anchorage is the state's largest city.
AZ	03	Phoenix	1889	474.90	Yes	1445632	4192887	Phoenix is the most populous U.S. state capital and only capital with more than 1 million citizens
AR	04	Little Rock	1821	116.20	Yes	193524	877091
CA	05	Sacramento	1854	97.20	No	466488	2527123	The Supreme Court of California is headquartered in San Francisco. Los Angeles is the state's largest city.
CO	06	Denver	1867	153.40	Yes	600158	2552195	Denver was called Denver City until 1882.
CT	07	Hartford	1875	17.30	No	124512	1212381	Bridgeport is the state's largest city, but Hartford is the largest metro area.
DE	08	Dover	1777	22.40	No	36047	162310	Longest-serving capital in terms of statehood. Wilmington is the state's largest city.
FL	09	Tallahassee	1824	95.70	No	181412	367413	Jacksonville is the largest city, and Miami has the largest metro area.
GA	10	Atlanta	1868	131.70	Yes	420003	5268860	Atlanta is the state capital with the most populous metro area in the U.S.
HI	11	Honolulu	1845	85.70	Yes	337256	953207
ID	12	Boise	1865	63.80	Yes	205671	616561
IL	13	Springfield	1837	54.00	No	116250	208182	Chicago is the state's largest city.
IN	14	Indianapolis	1825	361.50	Yes	829718	1756221	Indianapolis is the second largest city in the Midwest.
IA	15	Des Moines	1857	75.80	Yes	203433	580255
KS	16	Topeka	1856	56.00	No	127473	230824	Wichita is the state's largest city.
KY	17	Frankfort	1792	14.70	No	25527	70758	Louisville is the state's largest city. Frankfort ranks as the twelfth most populous city in the state.
LA	18	Baton Rouge	1880	76.80	No	229553	802484	New Orleans is the state's largest city and home to the Louisiana Supreme Court. Louisiana is home to the tallest state capitol building.
ME	19	Augusta	1832	55.40	No	19136	117114	Augusta was officially made the capital 1827, but the legislature did not sit there until 1832. Portland is the state's largest city.
MD	20	Annapolis	1694	6.73	No	38394		Annapolis is the third-longest serving capital in the United States after Santa Fe and Boston. Its capitol building is the oldest still in use. It is also the smallest capital by land area. Baltimore is the state's largest city.
MA	21	Boston	1630	48.40	Yes	617594	4522858	Boston is the longest continuously serving capital in the United States. The Boston-Worcester-Manchester Combined Statistical Area encompasses the state capitals of Massachusetts, New Hampshire, and Rhode Island.
MI	22	Lansing	1847	35.00	No	114297	464036	Lansing is the only state capital that is not also the county seat of the county in which it is situated.[citation needed] Detroit is the state's largest city.
MN	23	Saint Paul	1849	52.80	No	285068	3502891	Minneapolis is the state's largest city; it and Saint Paul form the core of the state's largest metropolitan area.
MS	24	Jackson	1821	104.90	Yes	173514	539057
MO	25	Jefferson City	1826	27.30	No	43079	149807	Kansas City is the state's largest city, and Greater St. Louis is the state's largest metropolitan area.
MT	26	Helena	1875	14.00	No	28190	74801	Billings is the state's largest city.
NE	27	Lincoln	1867	74.60	No	258379	302157	Omaha is the state's largest city.
NV	28	Carson City	1861	143.40	No	55274		Las Vegas is the state's largest city.
NH	29	Concord	1808	64.30	No	42695		Manchester is the state's largest city.
NJ	30	Trenton	1784	7.66	No	84913	366513	Newark is the state's largest city. Trenton served as the US capital for a short period in the late 18th century.
NM	31	Santa Fe	1610	37.30	No	75764	183732	Santa Fe is the longest serving capital in the United States. El Paso del Norte served as the capital of the Santa Fe de Nuevo México colony-in-exile during the Pueblo Revolt of 1680–1692. Santa Fe has the highest elevation of any state capital. Albuquerque is the state's largest city.
NY	32	Albany	1797	21.40	No	97856	857592	New York City is the state's largest city.
NC	33	Raleigh	1792	114.60	No	403892	1130490	Charlotte is the state's largest city.
ND	34	Bismarck	1883	26.90	No	61272	108779	Fargo is the state's largest city.
OH	35	Columbus	1816	210.30	Yes	787033	1836536	Columbus is Ohio's largest city, and the fourth-largest state capital, but the Cincinnati and Cleveland metropolitan areas are both larger.
OK	36	Oklahoma City	1910	607.00	Yes	580000	1252987	Oklahoma City is the shortest serving current state capital in the United States.
OR	37	Salem	1855	45.70	No	154637	390738	Portland is the state's largest city.
PA	38	Harrisburg	1812	8.11	No	49528	647390	Philadelphia is the state's largest city.
RI	39	Providence	1900	18.50	Yes	178042	1630956	Providence also served as the capital 1636–1686 and 1689–1776. It was one of five co-capitals 1776–1853, and one of two co-capitals 1853–1900.
SC	40	Columbia	1786	125.20	Yes	131686	913797	Columbia is the largest city in South Carolina but second largest metro area and combined statistical area behind Greenville.
SD	41	Pierre	1889	13.00	No	13646		Sioux Falls is the state's largest city.
TN	42	Nashville	1826	473.30	No	635710	1582264	Memphis is the state's largest city, and Nashville is the largest metro area.
TX	43	Austin	1839	251.50	No	842592	1716291	Houston is the state's largest city and the previous capital, and Dallas–Fort Worth is the largest metro area. Austin is the largest state capital that is not also the state's largest city. Texas would have the tallest state capitol building if all of the underground work were above ground.
UT	44	Salt Lake City	1858	109.10	Yes	186440	1124197
VT	45	Montpelier	1805	10.20	No	7855		Montpelier is the least populous U.S. state capital. Burlington is the state's largest city.
VA	46	Richmond	1780	60.10	No	204214	1231675	Virginia Beach is the state's largest city, and Northern Virginia is the state's largest metro area.
WA	47	Olympia	1853	16.70	No	46478	234670	Seattle is the state's largest city.
WV	48	Charleston	1885	31.60	Yes	51400	304214
WI	49	Madison	1838	68.70	No	233209	561505	Milwaukee is the state's largest city.
WY	50	Cheyenne	1869	21.10	Yes	59466	91738

State

Region_Code	State_Code	State_Name	Date_of_Statehood	State_Size	State_Population
W	AL	Alabama	1819	50750.00	4887871
SE	AK	Alaska	1959	570641.00	737438
SE	AZ	Arizona	1912	113642.00	7171646
W	AR	Arkansas	1836	52075.00	3013825
W	CA	California	1850	155973.00	39557045
W	CO	Colorado	1876	103730.00	5695564
NE	CT	Connecticut	1788	4845.00	3572665
NE	DE	Delaware	1787	1955.00	967171
SE	FL	Florida	1845	53997.00	21299325
SE	GA	Georgia	1788	57919.00	10519475
W	HI	Hawaii	1959	6423.00	1420491
MW	ID	Idaho	1890	82751.00	1754208
W	IL	Illinois	1818	55593.00	12741080
MW	IN	Indiana	1816	35870.00	6691878
MW	IA	Iowa	1846	55875.00	3156145
MW	KS	Kansas	1861	81823.00	2911505
SE	KY	Kentucky	1792	39732.00	4468402
SE	LA	Louisiana	1812	43566.00	4659978
NE	ME	Maine	1820	30865.00	1338404
NE	MD	Maryland	1788	9775.00	6042718
NE	MA	Massachusetts	1788	7838.00	6902149
MW	MI	Michigan	1837	56539.00	9995915
MW	MN	Minnesota	1858	79617.00	5611179
MW	MS	Mississippi	1817	46914.00	2986530
SE	MO	Missouri	1821	68898.00	6126452
W	MT	Montana	1889	145556.00	1062305
SE	NE	Nebraska	1867	76878.00	1929268
MW	NV	Nevada	1864	109806.00	3034392
MW	NH	New Hampshire	1788	8969.00	1356458
NE	NJ	New Jersey	1787	7419.00	8908520
NE	NM	New Mexico	1912	121365.00	2095428
W	NY	New York	1788	47224.00	19542209
W	NC	North Carolina	1789	48718.00	10383620
NE	ND	North Dakota	1889	68994.00	760077
MW	OH	Ohio	1803	40953.00	11689442
S	OK	Oklahoma	1907	68679.00	3943079
W	OR	Oregon	1859	96003.00	4190713
NE	PA	Pennsylvania	1787	44820.00	12807060
NE	RI	Rhode Island	1790	1034.00	1057315
SE	SC	South Carolina	1788	30111.00	5084127
MW	SD	South Dakota	1889	75898.00	882235
SE	TN	Tennessee	1796	41220.00	6770010
S	TX	Texas	1845	261914.00	28701845
W	UT	Utah	1896	82168.00	3161105
SE	VT	Vermont	1791	9249.00	626299
NE	VA	Virginia	1788	39598.00	8517685
W	WA	Washington	1889	66582.00	7535591
MW	WV	West Virginia	1863	24087.00	1805832
SE	WI	Wisconsin	1848	54314.00	5813568
W	WY	Wyoming	1890	97105.00	577737

Region

Region_Code	Region_Name
MW	Midwest
NE	Northeast
NE	Northest
S	South
S	South
SE	Southeast
W	West

Country

Country_Code	Country_Name	Population	Country_Size
USA	United States	298444215	9631420.00