sqlite3_cheat_sheet.txt

#1. Count the votes for Sen. Olympia Snowe, whose id is 524.
SELECT COUNT(*) FROM votes WHERE politician_id = 524 ;
 
#2. Now do that query with a JOIN statement without hard-coding the id 524 explicitly, querying both the votes and congress_members table.
SELECT COUNT(*) FROM congress_members INNER JOIN votes ON congress_members.id = votes.politician_id WHERE congress_members.name = "Sen. Olympia Snowe" ;
 
#3. How about Rep. Erik Paulsen? How many votes did he get?
SELECT COUNT(*) FROM congress_members INNER JOIN votes ON congress_members.id = votes.politician_id WHERE congress_members.name = "Rep. Erik Paulsen" ;
 
#4. Make a list of Congress members that got the most votes, in descending order. Exclude the create_at and updated_at columns.
SELECT congress_members.name, COUNT(votes.politician_id) FROM congress_members INNER JOIN votes ON congress_members.id = votes.politician_id GROUP BY congress_members.name ORDER BY COUNT(votes.politician_id) DESC);
 
#5. Now make a list of all the Congress members that got the least number of votes, in ascending order. Again, skip the date columns.
SELECT congress_members.name, COUNT(votes.politician_id) FROM congress_members INNER JOIN votes ON congress_members.id = votes.politician_id GROUP BY congress_members.name ORDER BY COUNT(votes.politician_id) ASC;
 
 
#---------------------------- ADVANCED ----------------------------
#1. 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.

SELECT 
	name, COUNT(*)
FROM 
	congress_members INNER JOIN votes 
ON congress_members.id = votes.politician_id 
GROUP BY congress_members.name 
ORDER BY COUNT(votes.politician_id) DESC LIMIT 1;
===> Rep. Dan Benishek  

SELECT
  first_name,
  last_name,
  gender,
  party
FROM
  voters INNER JOIN votes 
  ON voters.id = votes.voter_id 
WHERE votes.politician_id 
IN 
(SELECT 
	votes.politician_id
FROM 
	congress_members INNER JOIN votes 
ON congress_members.id = votes.politician_id 
GROUP BY congress_members.name 
ORDER BY COUNT(votes.politician_id) DESC LIMIT 1);
===> print first name, last name, gender & party of voters for politician 224
 
 
#2. 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.
SELECT congress_members.name, congress_members.location, congress_members.grade_1996, COUNT(*)
FROM congress_members INNER JOIN votes ON congress_members.id = votes.politician_id
WHERE congress_members.grade_current <= 9 
GROUP BY congress_members.name; 
===> prints name, location, grade_1996, vote count
 
#3. 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.)
SELECT congress_members.location, COUNT(*)
FROM congress_members INNER JOIN votes ON congress_members.id = votes.politician_id 
GROUP BY congress_members.location 
ORDER BY COUNT(congress_members.location) DESC LIMIT 10 ; 
===> prints top 10 states, top state, CA 1025 votes
 
SELECT first_name, last_name, gender, voters.party
FROM votes
  LEFT JOIN voters ON voters.id = votes.voter_id
  LEFT JOIN congress_members ON votes.politician_id = congress_members.id
WHERE
  location IN
    (SELECT location
     FROM congress_members
       LEFT JOIN votes ON votes.politician_id = congress_members.id
     GROUP BY
       location
     ORDER BY
       COUNT(*) DESC
     LIMIT
       1
     );
 
 
#4. 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!
SELECT voters.first_name, voters.last_name 
FROM votes INNER JOIN voters ON voters.id = votes.voter_id
INNER JOIN congress_members ON congress_members.id = votes.politician_id
GROUP BY voters.id, congress_members.id HAVING COUNT(*) > 1;
 
#5. Did anyone vote for the same politician twice? What was the name of the voter and the politician they voted for? Pretty sneaky...
SELECT voters.first_name, voters.last_name, congress_members.name, COUNT(*) c 
FROM votes INNER JOIN voters ON voters.id = votes.voter_id
INNER JOIN congress_members ON congress_members.id = votes.politician_id
GROUP BY voters.id, congress_members.id HAVING c > 1;