Download and unzip the SQL create script. dbstatecapital.zip
Open the .sql file in Notepad. Logon to PHPMyAdmin. Open your database. Click on the SQL tab.
Copy and paste the SQL script into the SQL window and click Go. It should create a table
containing data about States and Capitals. Paste a screen captures of the table into
your Word document.
Perform the following (aggregate) queries. Paste into your Word document the corresponding SQL (text
not screen capture, SQL not result).
Write a query that determines the average (AVG) of the CapitalArea.
Write a query that determines the minimum (MIN) of the CapitalArea.
Write a query that determines the maximum (MAX) of the CapitalArea.
CapitalPopRank is 1 if the capital is the most populated city in a state, etc.
Write a query that counts (COUNT) the number of state whose capital is their most
populated city.
Write a query that counts the number of states established in various years.
Sort the list so that the years in which the most states were established appear first.
(COUNT, GROUP BY)
Write a query that determines the average population density of a state capital. Take
population density to be CapitalMunicPop / CapitalArea and average them.
Write a query that finds the total (SUM) of all of the Capital Munic. Populations.
Write a query that finds the total Area of the Capitals. (A different way to think about
average capital density would be total population divided by total area.)
