CMIS 320
Name:_______________

In the worldbank data file/table, country name is unique and so could serve as a
primary key but to avoid the possiblity of duplicate names 
(e.g. Koreas, Yemens, Chinas etc.)
make a new version of the worldbank table with an autoincrement primary key column.
Call it 'id' or something similar; int unsigned.
Paste the description of the new worldbank table:

Changing the data file to prepend \N at the beginning of every line would
be trivial in Unix but we can instead still use the original data file
and tell the LOAD DATA ... give it a parenthesized list of the columns to load
into from the file, the other column(s) being null:
 LOAD DATA LOCAL INFILE 'filename' INTO TABLE t1  
   FIELDS TERMINATED BY ' ' 
   LINES TERMINATED BY '\r\n'
   (f2,f6,f3);
(Yours will have the names of all your fields except the auto_increment first field.)
Paste the result of the LOAD DATA... command:


Answer these queries:
-the min and max ids and the number of records.
-what is Sweden's id.
-result of 
 Insert into worldbank values(100,'Gagaland',1,1,1.1,1,1.1,1.1);
(or whatever your table's name is)


****************************************************************************

Back to the counties table.
Answer these queries:  Hint: some will need a HAVING clause, others won't.
1 states with more than 100 counties.
2 states with less than 10 counties, in order by the number of counties.
3 the state with 56 counties.
4 number of counties that begin with each letter of the alphabet.
5 most frequent letter that starts a county name.  
  i.e. more county names start with this letter than any other letter.
6 the counties whose name starts with a Q.
7 states whose population is more than 10,000,000 (use Having, not Order By and Limit 
  [which couldn't precisely answer this anyway])
9 the first letter of Iowa's county names whose first letter starts 10 or more Iowa county names.
10 the number of different county names. Hint: distinct
-SKIP (can't do it yet):of those distinct names, how many occur only once.
10b the names that are unique (only occur once). Use the  #rows in set to answer this,
  do not paste the long list of counties!
11 the states that have more than 11 counties of 4-letter names.
11b the states that have only one 4-letter named county.
12 the top 10 states with the most number of 4-letter named counties.
13 the shortest county names are 3 letters.  What are those names (no dups). Listed alphabetically.
14 "money density" is the income per area (and income is population times incomePerCapita);
  i.e. $ per square mile
  List the top 12 highest "money density" counties. Use an alias and rounding.