1 / 14

Querying the State Capital Database

Learn how to query the State Capital Database effectively using SQL commands. Explore sorting options, adding columns, and deriving data. Bookmark your queries for easy access.

bardwell
Download Presentation

Querying the State Capital Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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).

More Related