What is SQL? What is the main purpose of the structured query language? What are SQL comments? Body: Use the database State_Capitals’, construct five SQL statements

  • Introduction: What is SQL? What is the main purpose of the structured query language? What are SQL comments?
  • Body: Use the database ‘State_Capitals’, construct five SQL statements for the following:
  • What is Country Name and Country Population?
  • What is the capital city of California?
  • What is the capital with the least population? What is the one with the most population?
  • What is the size of the largest state? What is the size of the smallest state?
  • List the region name, state name and capital name for every state in the United States of America.
  • *** Only include the SQL statements, not data ***
  • You must use either single line or block comments to document each of your query. At a minimum, you need:
  • Author Info,
  • Create Date,
  • Description,
  • Updated by,
  • Last updated date.
  • Example: What is Country Code and Country Name?
  • Conclusion: Summarize how joins have been used in the above SQL statements to deliver the right results. Summarize how you would use comments to document your codes.

Submit your project in a word document format. Your assignment should be at least two pages in length. APA Style is not required for this assignment.

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

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.

Related Posts

All Rights Reserved Terms and Conditions
College pals.com Privacy Policy 2010-2018