- Count the votes for Sen. Olympia Snowe, whose id is 524.
sqlite> SELECT COUNT(id) FROM votes WHERE politician_id = 524;
23
- Now do that query with a JOIN statement without hard-coding the id 524 explicitly, querying both the votes and congress_members table.
sqlite> SELECT COUNT(votes.id) FROM votes JOIN congress_members
...> ON votes.politician_id = congress_members.id
...> WHERE congress_members.name = 'Sen. Olympia Snowe';
23
- How about Rep. Erik Paulsen? How many votes did he get?
sqlite> SELECT COUNT(votes.id) FROM votes
JOIN congress_members
...> ON votes.politician_id = congress_members.id
...> WHERE congress_members.name = 'Rep. Erik Paulsen';
21
- Make a list of Congress members that got the most votes, in descending order. Exclude the create_at and updated_at columns.
sqlite> SELECT name, party, location, grade_1996, grade_current, years_in_congress, COUNT(votes.id)
...> FROM congress_members JOIN votes ON(congress_members.id = votes.politician_id)
...> GROUP BY congress_members.name
...> ORDER BY COUNT(votes.id) DESC LIMIT 20;
| name | party | location | grade_1996 | grade_current | years_in_congress | COUNT(votes.id) |
|---|---|---|---|---|---|---|
| Rep. Dan Benishek | R | MI | 11.29829673 | 11.29829673 | 1 | 32 |
| Rep. Dale Kildee | D | MI | 11.92746368 | 12.04087167 | 35 | 31 |
| Rep. Frank Pallon | D | NJ | 12.73500724 | 11.24205814 | 25 | 31 |
| Rep. Jim Costa | D | CA | 12.73091018 | 13.20082289 | 5 | 31 |
| Rep. Tammy Baldwi | D | WI | 11.75013228 | 12.92146101 | 13 | 31 |
| Sen. Maria Cantwe | D | WA | 12.79192238 | 12.07724527 | 9 | 30 |
| Rep. Marcia Fudge | D | OH | 11.19069793 | 10.58342866 | 3 | 29 |
| Rep. Mike Rogers | R | MI | 11.27219414 | 11.90823499 | 11 | 29 |
| Rep. Ruben Hinojo | D | TX | 11.86145244 | 11.56849308 | 15 | 29 |
| Rep. Devin Nunes | R | CA | 11.01959668 | 13.1721979 | 9 | 28 |
| Rep. Henry Cuella | D | TX | 12.60664745 | 12.3747415 | 5 | 28 |
| Rep. Scott DesJar | R | TN | 9.871864437 | 9.871864437 | 1 | 28 |
| Rep. Tom Latham | R | IA | 11.83917637 | 12.59157797 | 17 | 28 |
| Rep. Zoe Lofgren | D | CA | 11.89184982 | 12.76643582 | 17 | 28 |
| Sen. Jerry Moran | R | KS | 11.92120214 | 12.76575221 | 1 | 28 |
| Sen. Joseph Liebe | I | CT | 13.46825345 | 12.95536381 | 21 | 28 |
| Sen. Kay Hagan | D | NC | 12.59279723 | 13.05756534 | 3 | 28 |
| Sen. Ron Johnson | R | WI | 8.62470174 | 8.62470174 | 1 | 28 |
| Rep. Gary Ackerma | D | NY | 12.0739639 | 12.33593222 | 29 | 27 |
| Rep. Kevin McCart | R | CA | 11.16752671 | 9.905327689 | 5 | 27 |
- Now make a list of all the Congress members that got the least number of votes, in ascending order. Again, skip the date columns.
sqlite> SELECT name, party, location, grade_1996, grade_current, years_in_congress, COUNT(votes.id)
...> FROM congress_members JOIN votes ON (congress_members.id = votes.politician_id)
...> GROUP BY congress_members.name
...> ORDER BY COUNT(votes.id) ASC LIMIT 20;
| name | party | location | grade_1996 | grade_current | years_in_congress | COUNT(votes.id) |
|---|---|---|---|---|---|---|
| Rep. Bill Cassidy | R | LA | 9.385173006 | 9.285165569 | 3 | 7 |
| Rep. Jim Sensenbr | R | WI | 13.69396284 | 11.56066479 | 33 | 7 |
| Rep. Ileana Ros-L | R | FL | 13.14271644 | 12.93132331 | 23 | 8 |
| Rep. Tim Huelskam | R | KS | 10.23278515 | 10.23278515 | 1 | 8 |
| Rep. Alan Nunnele | R | MS | 10.33893365 | 10.33893365 | 1 | 9 |
| Rep. Robert Dold | R | IL | 10.43953638 | 10.43953638 | 1 | 9 |
| Rep. Betty McColl | D | MN | 12.71695941 | 12.10592099 | 11 | 10 |
| Rep. Doris Matsui | D | CA | 12.1809007 | 13.62931563 | 5 | 10 |
| Rep. Heath Shuler | D | NC | 12.04854088 | 12.84471939 | 5 | 10 |
| Rep. John Kline | R | MN | 11.09042733 | 10.41051711 | 9 | 10 |
| Rep. Steven Guthr | R | KY | 11.69094614 | 10.32590976 | 3 | 10 |
| Sen. Orrin Hatch | R | UT | 11.70935336 | 10.82183029 | 33 | 10 |
| Rep. David McKinl | R | WV | 11.29332705 | 11.29332705 | 1 | 11 |
| Rep. Frank Guinta | R | NH | 10.94131093 | 10.94131093 | 1 | 11 |
| Rep. Gene Green | D | TX | 11.56577067 | 10.90283608 | 19 | 11 |
| Rep. Lee Terry | R | NE | 11.29466453 | 9.816832242 | 13 | 11 |
| Rep. Randy Forbes | R | VA | 11.82234079 | 9.982090615 | 11 | 11 |
| Rep. Rodney Freli | R | NJ | 12.43219658 | 11.16204314 | 17 | 11 |
| Rep. Steven LaTou | R | OH | 10.70217756 | 9.577295426 | 17 | 11 |
| Sen. Sheldon Whit | D | RI | 11.25923664 | 11.40970245 | 3 | 11 |
- Which Congress member received the most votes? List their name and a count of their votes. Who were the people that voted for that politician? List their names, gender and party.
sqlite> SELECT name, COUNT(votes.id)
...> FROM votes JOIN congress_members ON (votes.politician_id = congress_members.id)
...> GROUP BY name
...> ORDER BY COUNT(votes.id) DESC LIMIT 1;
| name | COUNT(votes.id) |
|---|---|
| Rep. Dan Benishek | 32 |
- How many votes were received by Congress members whose communication grade average was less than 9 (this number can be found in the grade_current field)? List their name, location, grade since 1996, and the vote count.
sqlite> SELECT name, location, grade_1996, COUNT(politician_id)
...> FROM votes JOIN congress_members ON (votes.politician_id = congress_members.id)
...> WHERE congress_members.grade_current <9
...> GROUP BY politician_id
...> ORDER BY name ASC;
| name | location | grade_1996 | COUNT(politician_id) |
|---|---|---|---|
| Rep. Adam Kinzinger | IL | 8.995621754 | 18 |
| Rep. Ander Crenshaw | FL | 11.22521629 | 23 |
| Rep. Collin Peterso | MN | 10.41898905 | 19 |
| Rep. Connie Mack | FL | 12.03584092 | 14 |
| Rep. David Schweike | AZ | 8.606102568 | 17 |
| Rep. Don Young | AK | 12.33638826 | 13 |
| Rep. Jack Kingston | GA | 9.245243859 | 18 |
| Rep. Jason Chaffetz | UT | 9.339565934 | 23 |
| Rep. Jeff Flake | AZ | 9.140565697 | 23 |
| Rep. Jeff Landry | LA | 8.644177038 | 16 |
| Rep. Joe Walsh | IL | 8.907015863 | 17 |
| Rep. John Campbell | CA | 8.873045579 | 18 |
| Rep. John Garamendi | CA | 8.923919506 | 18 |
| Rep. John Lewis | GA | 8.938711508 | 22 |
| Rep. Keith Ellison | MN | 9.300842411 | 19 |
| Rep. Mick Mulvaney | SC | 7.945704026 | 15 |
| Rep. Peter DeFazio | OR | 9.83226533 | 21 |
| Rep. Phil Roe | TN | 8.642390533 | 19 |
| Rep. Rob Woodall | GA | 8.0191087 | 23 |
| Rep. Roscoe Bartlet | MD | 8.928747175 | 20 |
| Rep. Sean Duffy | WI | 8.088706095 | 12 |
| Rep. Spencer Bachus | AL | 10.60047552 | 14 |
| Rep. Steve Pearce | NM | 10.40568385 | 15 |
| Rep. Ted Poe | TX | 9.656611339 | 15 |
| Rep. Tim Griffin | AR | 8.129156251 | 20 |
| Rep. Tom Graves | GA | 8.600184256 | 21 |
| Rep. Vicky Hartzler | MO | 8.44408234 | 20 |
| Rep. W. Todd Akin | MO | 8.140423512 | 23 |
| Sen. Barbara Boxer | CA | 9.406367725 | 14 |
| Sen. Barbara Mikuls | MD | 9.805853241 | 16 |
| Sen. Frank Lautenbe | NJ | 10.46638173 | 17 |
| Sen. Jeff Sessions | AL | 9.352769181 | 23 |
| Sen. Jim Inhofe | OK | 10.10777643 | 13 |
| Sen. Rand Paul | KY | 8.039832522 | 14 |
| Sen. Ron Johnson | WI | 8.62470174 | 28 |
- What 10 states had the most voters turnout? (Does this correspond to the population of those states?) List the people that voted in the top state's elections. (It will be a big list, and you can use the results from your first query to help simplify this next query.)
sqlite> SELECT location, COUNT(politician_id)
FROM votes JOIN congress_members ON votes.politician_id = congress_members.id
GROUP BY location ORDER BY COUNT(politician_id) DESC LIMIT 10;
| location | COUNT(politician_id) |
|---|---|
| CA | 1025 |
| TX | 643 |
| NY | 582 |
| FL | 497 |
| PA | 400 |
| IL | 395 |
| OH | 380 |
| MI | 333 |
| NC | 297 |
| GA | 296 |
sqlite> SELECT first_name, last_name, location FROM voters
...> JOIN votes ON voter_id = voters.id
...> JOIN congress_members cm ON cm.id = votes.politician_id
...> WHERE location IN (
...> SELECT location
...> FROM votes JOIN congress_members cm ON votes.politician_id = cm.id
...> GROUP BY location
...> ORDER BY count(\*) desc
...> LIMIT 2
...> )
...> LIMIT 20;
| first_name | last_name | location |
|---|---|---|
| Trevor | Braun | CA |
| Fritz | Wiegand | CA |
| Elenor | Davis | CA |
| Candace | Torphy | TX |
| Marian | Cormier | TX |
| Jeanne | Price | CA |
| Juvenal | Hane | CA |
| Irma | Vandervort | TX |
| Joanny | Carroll | TX |
| Thad | Ratke | CA |
| Shaylee | Yundt | TX |
| Deangelo | Klein | CA |
| Keaton | Ortiz | CA |
| Shania | Monahan | TX |
| Milford | Schimmel | CA |
| Mara | Green | CA |
| Krystina | Ebert | CA |
| Moriah | Terry | CA |
| Conner | Zulauf | CA |
| Wilhelmine | Bradtke | TX |
- List the people that voted more than 2 times? (It should only be once for their Senator and once for their representative!) Ay Caramba! We have some serious ballot stuffing! Report this to the Election Board!
sqlite> SELECT voters.id, first_name, last_name, COUNT(votes.voter_id) c
...> FROM votes JOIN voters ON (votes.voter_id = voters.id)
...> GROUP BY voters.id
...> HAVING c > 2
...> ORDER BY voters.id LIMIT 10;
| id | first_name | last_name | c |
|---|---|---|---|
| 1 | Aiden | Kuhlman | 3 |
| 5 | Marshall | Osinski | 3 |
| 11 | Carmine | Ankunding | 3 |
| 14 | Caden | Greenfelde | 4 |
| 17 | Luz | Collins | 4 |
| 18 | Elenor | Davis | 3 |
| 20 | Norval | Hilpert | 3 |
| 22 | Alexzander | Hickle | 3 |
| 26 | Geo | Christians | 3 |
| 28 | Verla | Ondricka | 5 |
Validating that the entries are correct:
Count the number of unique votes
sqlite> SELECT COUNT(DISTINCT(voter_id))
> > ...> FROM votes
> > ...> WHERE voter_id IN(
> > ...> SELECT voter_id
> > ...> FROM votes JOIN voters ON (votes.voter_id = voters.id)
> > ...> GROUP BY voter_id
> > ...> HAVING COUNT(votes.voter_id) > 2);
> > 1633
- Did anyone vote for the same politician twice? What was the name of the voter and the politician they voted for? Pretty sneaky...
sqlite> SELECT voters.first_name, voters.last_name, congress_members.name, COUNT(\*) c
...> FROM votes
...> JOIN congress_members ON votes.politician_id = congress_members.id
...> JOIN voters ON votes.voter_id = voters.id
...> GROUP BY votes.voter_id, votes.politician_id
...> HAVING c = 2;
| first_name | last_name | name | c |
|---|---|---|---|
| Dallas | Padberg | Rep. Andy Harris | 2 |
| Mckayla | Kilback | Rep. Joe Wilson | 2 |
| Kayley | Rohan | Rep. Dale Kildee | 2 |
| Amara | Wyman | Rep. Steve Scali | 2 |
| Jakayla | Wintheiser | Rep. Donna Edwar | 2 |
| Glennie | Raynor | Rep. Stephen Fin | 2 |
| Nayeli | Ryan | Sen. Sherrod Bro | 2 |
| Armand | Effertz | Rep. Todd Platts | 2 |
| Adah | Flatley | Rep. Jim Costa | 2 |
| Christiana | Schamberge | Rep. Jeff Forten | 2 |
| Jules | Larkin | Rep. Dennis Ross | 2 |
| Cynthia | Bruen | Rep. Mike Simpso | 2 |
| Efrain | Collier | Sen. Ron Johnson | 2 |
| Aubree | Mohr | Rep. Collin Pete | 2 |
| Madalyn | Feil | Rep. Kurt Schrad | 2 |
| Ronaldo | Douglas | Rep. Jeff Forten | 2 |
| Cyrus | Feil | Rep. Mike McInty | 2 |
| Brett | Pollich | Rep. Joe Baca | 2 |
| Maryse | Moen | Sen. Bernie Sand | 2 |