Querying the State Capital Database

1. Querying the State Capital Database

2. Click on the Query tab. Use the Column drop-downs to select the column/field names in your query, whether to show them, how to sort them, etc. Click the Update Query button to see the SQL for your query.

3. Notice the tick mark ` in the SQL. It is important to distinguish it from the single quote

4. Then scroll down (if necessary) and click Submit Query to see the results.

5. Give the query a name and bookmark it

6. We can ask which states are the most populous cities in their states by setting the Criterion to =1 for the CapitalPopRank field

7. We can add a column using the Add/Delete Columns feature. We can use a filed (here for ordering) but not display it

8. Start with a query that shows state, capital, Munic. Population and Area

9. This can be turned into a Population density query by replacing the comma between Pop and Area with a division sign /

10. Results of Population Density query

11. Naming the derived field so you can sort on it

12. Result of sorting on Population Density

13. Rank the years by the number of states that were created that year SELECT count(`tbstatecapital`.`StateDate`) as stateCount, `tbstatecapital`.`StateDate` FROM `tbstatecapital` GROUP BY `tbstatecapital`.`StateDate` ORDER BY stateCount DESC

14. Queries for you to perform: submit a document with the SQL and a screen capture of the first few results • Display the State, capital, and year for that state sorted on the last field. • Display the State, capital, and year for that capital sorted on the last field. • Display all of the information for Florida. • Display all of the information for states starting with the letter A. (Use SQL’s LIKE) • Display states and capitals in which the capital date precedes the state date (it was the capital of the territory before the territory was a state).