Databases of the States

For this test I have a database with the following tables

States
Column
 Meaning
Name
 Name of the State
Abrev
 Two letter abreviation for the state (California is 'ca')
Pop
 Population of the state

Cities
Column
 Meaning
City
 Name of the city
State
 Two letter abreviation for the state containing this city
Pop
 Population of the city

Voting
Column
 Meaning
State
 Two letter abreviation for the state containing this county
City
 Name of the county
Dem
 Number of people who voted democratic
Rep
 Number of people who voted republican

1) What SQL statement would create the table 'Voting'?


2) What SQL statement would list every city that voted Democratic?


3) What SQL statement would tell how many people of California voted Republican?


4) What SQL statement would tell which cities in states containing at least 2,000,000 people had a majority of the voters choosing the Democrats?


5) There are ten cities in the database with a population of over 2 million people.  Two of them are in california.  There are 4000 cities in the database as well as 51 states.  How many answers will this querry produce?
    select * from states, cities where cities.pop > 2000000 and states.abrev = 'ca'


6) Suppose that Bill Clinton is adding the latest voting records to the database, including data about New York.  Meanwhile Hillary is running a query checking on voting
patterns among New York voters.  What might happen?
    a) Bill's update fails to complete since it conflicts with Hillary's querry.
    b) Hillary's querry might produce an error, since it conflicts with Bills update.
    c) Either A or B depending
    d) None of these lousy answers


7) Suppose that as Bill is doing his update and before Bills update is complete the power fails.  When the power is restored what will the database contain?
    a) Some records updated but not others
    b) No records updated
    c) All the records updated
    d) None of these horrible answers


8) When Bush Jr. takes over the database, he moves it from MySQL to Oracle. He keeps all database tables and columns and contents the same, changing only the vendor of the database software.  How much of the Perl-DBI-CGI code must change to restore everything to working order?
    a) Nothing.  No lines.
    b) Probably less than three lines per CGI script, give or take
    c) Pretty much most of the database statements, though the algorithms remain the same
    d) The database and the CGI statements, but the HTML remains the same
    e) Complete rewrite.  Eeck!



9) I have loaded an array %states with the population of each city.  For example, $states{"Detroit-MI"} is the population of Detroit.  Write a while loop that iterates through the states printing the population of each city.  It really should be a loop with a 'while', 'for', or 'do' somewhere.


10) The federal election commission sends a text file of data to be loaded into the database.  Each line is a city.  All columns are sperated by commas.  For example, there might be a line like below, saying that 6421 people voted democratic and 18652 people voted republican.  How would you open the file for reading?
    Marquette, MI, 4621, 18652


11) When can you set a cookie using a CGI script?
    a) Anytime
    b) Only when printing the header
    c) Only after printing the form and before the CGI script exits
    d) None of the stinky answers

12-16)  Write me a class 'Voter' that can be used by this main class.
#!/usr/bin/perl -w
use Voter;
$v1 = Voter->new();
$v2 = Voter->new();
$v1->repub(10);
$v1->demo(20);
$v2->repub(30);
$v2->demo(3);
print "The Winner is " . $v1->winner() . "\n";  # should say "Dem"
print "The Winner is " . $v2->winner() . "\n";  # should say "Rep"