Exercises

Here are some exercises for you to try on your own. Solutions are available at http://pragprog.com/titles/gwpy3/practical-programming.

1. In this exercise, you will create a table to store the population and land area of the Canadian provinces and territories according to the 2001 census. Our data is taken from http://www12.statcan.ca/english/census01/home/index.cfm.

   ---

   Table 34. 2001 Canadian Census Data

   Province/Territory	Population	Land Area
   Newfoundland and Labrador	512930	370501.69
   Prince Edward Island	135294	5684.39
   Nova Scotia	908007	52917.43
   New Brunswick	729498	71355.67
   Quebec	7237479	1357743.08
   Ontario	11410046	907655.59
   Manitoba	1119583	551937.87
   Saskatchewan	978933	586561.35
   Alberta	2974807	639987.12
   British Columbia	3907738	926492.48
   Yukon Territory	28674	474706.97
   Northwest Territories	37360	1141108.37
   Nunavut	26745	1925460.18

   ---

   Write Python code that does the following:

   1. Creates a new database called census.db

   2. Makes a database table called Density that will hold the name of the province or territory (TEXT), the population (INTEGER), and the land area (REAL)

   3. Inserts the data from Table 34, ​2001 Canadian Census Data​

   4. Retrieves the contents of the table

   5. Retrieves the populations

   6. Retrieves the provinces that have populations of less than one million

   7. Retrieves the provinces that have populations of less than one million or greater than five million

   8. Retrieves the provinces that do not have populations of less than one million or greater than five million

   9. Retrieves the populations of provinces that have a land area greater than 200,000 square kilometers

   10. Retrieves the provinces along with their population densities (population divided by land area)

2. For this exercise, add a new table called Capitals to the database. Capitals has three columns—province/territory (TEXT), capital (TEXT), and population (INTEGER)—and it holds the data shown here:

   ---

   Table 35. 2001 Canadian Census Data: Capital City Populations

   Province/Territory	Capital	Population
   Newfoundland and Labrador	St. John’s	172918
   Prince Edward Island	Charlottetown	58358
   Nova Scotia	Halifax	359183
   New Brunswick	Fredericton	81346
   Quebec	Quebec City	682757
   Ontario	Toronto	4682897
   Manitoba	Winnipeg	671274
   Saskatchewan	Regina	192800
   Alberta	Edmonton	937845
   British Columbia	Victoria	311902
   Yukon Territory	Whitehorse	21405
   Northwest Territories	Yellowknife	16541
   Nunavut	Iqaluit	5236

   ---

   Write SQL queries that do the following:

   1. Retrieve the contents of the table

   2. Retrieve the populations of the provinces and capitals (in a list of tuples of the form [province population, capital population])

   3. Retrieve the land area of the provinces whose capitals have populations greater than 100,000

   4. Retrieve the provinces with land densities less than two people per square kilometer and capital city populations more than 500,000

   5. Retrieve the total land area of Canada

   6. Retrieve the average capital city population

   7. Retrieve the lowest capital city population

   8. Retrieve the highest province/territory population

   9. Retrieve the provinces that have land densities within 0.5 persons per square kilometer of on another—have each pair of provinces reported only once

3. Write a Python program that creates a new database and executes the following SQL statements. How do the results of the SELECT statements differ from what you would expect Python itself to do? Why?

   ​	​CREATE​ ​TABLE​ Numbers(Val ​INTEGER​)
   ​	​INSERT​ ​INTO​ Numbers ​Values​(1)
   ​	​INSERT​ ​INTO​ Numbers ​Values​(2)
   ​	​SELECT​ * ​FROM​ Numbers ​WHERE​ 1/0
   ​	​SELECT​ * ​FROM​ Numbers ​WHERE​ 1/0 ​AND​ Val > 0
   ​	​SELECT​ * ​FROM​ Numbers ​WHERE​ Val > 0 ​AND​ 1/0