Tuesday, November 8


Election Predictions with SQL
Today is Election Day. Learn new SQL statement, compete with your classmates, and win the prediction pool by copying Nate Silver.
The Midterm Datasets
The data for the midterm in database form.


From Dan

Hey, get going on this Election Predictions in SQL assignment, which has to be done by 5 because of that election thing. Sorry for the late notice!

Winner gets a prize. Usually it's been me.

I'll show up to class at around 1:50PM. Please complete at least these steps, and then practice the long list of queries I provided) as they are relevant to the midterm.

If you can solve the queries rated 4, you're good to go.

Make sure you can complete these steps for the homework:

  1. Download this database: election2016.sqlite
  2. Open it.
  3. Look at the predictions table with SELECT * from predictions
  4. You have to fill that table yourself, so delete everything in it: DELETE FROM predictions
  5. Insert a row that represents what you think the national race will turn out: INSERT INTO predictions(sunetid, state, Trump, Clinton) VALUES('leland', 'Nationwide', 40.5, 56.7)

Now think about how to do that for every row for the 51 states.


  • How do we select a list of state names and popular vote for Obama and Romney in the 2012 race (check the results table)
  • How do we get a list of the top 10 state names with the highest Hispanic population? (check acs_1yr table)
  • How do we get the average polling for the Republican and Democratic presidential candidates for Ohio, using only polls from 300 days into the race?