CMIS 320
Name:________________

Create a database with one table whose attributes are as described in
the 'world_bankfields.txt' file in the DBs directory of the class web
site.  Load the data from the 'world_bankinputfile.txt' file, whose fields
are terminated by space and lines terminated by '\r\n'.

Paste your Create Table statement here:

Paste your Load Data statement here:

Execute these queries.  Paste the SQL and the resultset.
Some of these you might be able to do right away, i.e. with your first attempt,
right off the bat with a query.
Others might need to be built up from simpler partial answers, i.e start with something
broad, then successively narrow in to the answer query. Develop the query.

1- number of records
2- totals and averages of population, area, GNI (one query to get these 6 values)
3- name, population and GNI of records with null surface_areas
4- number of records with null GNI fields
5- the names and GNI of the 10 states with the largest GNI in descending order
6- the five poorest states (lowest GNI) for which there is data
7- number of states with negative GDP growth rates
8- the names and areas of the 10 biggest states in land area
9- the sum of the areas of the 10 biggest states (using a datum from the previous query)
10- names of states containing two p's
11- names of states containing two consecutive same vowels, e.g. aa,ee,ii,oo, or uu
12- names of states whose names have 'land'
13- number of states whose names end in 'ia'
 13b- names of states whose names contain 'ia' but do not end in 'ia'
14- names of the states with the shortest names (you can use 2 queries to do this)
15- the names,population,area and density of the 10 most densely
 populated states.  density in people per sq km is derived from population/area*1000.
 order by density.  use the round() function to round the density values.
15b- United States' area in km^2, area in mile^2, 
 density per sq. km and density per sq. mile
16- the names,population,area and density of the 10 least densely
 populated states for which there is appropriate data, in order by density
17- name, population, percapita GNI of the 10 states with the highest
  percapita GNI in dollars per person(derived from GNI/POP*1000) 
  in order of percapita GNI.  I.e. the richest per capita nations.
18- name, population, percapita GNI of the 10 states with the lowest
  percapita GNI for which there is data, in order of percapita GNI.
  I.e. the poorest per capita nations.
19- names,GNI,PPP_GNI, and difference between these two GNI values of the
  states whose GNI is larger than its PPP_GNI.
  I.e. nations whose GNI is higher than its PPP and thus not really as
  rich as they seem.
20- names,GNI,PPP_GNI, and the ratio of PPP_GNI to GNI of states
 whose PPP_GNI is more than 5 times its GNI). Order descending by this ratio.
 I.e. nations that are better off than their GNI indicates.
21- frequency count of the number of names of different
  lengths. I.e. number of names of length 22, number of length 21
  etc. Hint: group by length of name
22- frequency count of the number of names that start with each
  letter. I.e. number of names that start with A, number of names
  that start with B  etc. Hint: group by leftmost 1 character of name
  using the left() function.  in descending frequency order.

SKIP:
convert sq km to sq mile: *.3721
convert sq mile to sq km: /.3721