congress_db_answers.md

3.1_sql_congress_db_ANSWERS.md

Build Schema

CREATE TABLE congress_members (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(64) NOT NULL,
  party VARCHAR(64) NOT NULL,
  location VARCHAR(64) NOT NULL,
  grade_1996 REAL,
  grade_current REAL,
  years_in_congress INTEGER,
  dw1_score REAL
, created_at DATETIME, updated_at DATETIME);

CREATE TABLE voters (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(64) NOT NULL,
    last_name  VARCHAR(64) NOT NULL,
    gender VARCHAR(64) NOT NULL,
    party VARCHAR(64) NOT NULL,
    party_duration INTEGER,
    age INTEGER,
    married INTEGER,
    children_count INTEGER,
    homeowner INTEGER,
    employed INTEGER,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
  );

CREATE TABLE votes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    voter_id INTEGER,
    politician_id INTEGER,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    FOREIGN KEY(voter_id) REFERENCES voters(id),
    FOREIGN KEY(politician_id) REFERENCES congress_members(id)
  );

Queries

Release 1, Problem 1:

SELECT count(politician_id) FROM votes
WHERE politician_id = 524;

Release 1, Problem 2:

SELECT count(*), votes.politician_id, congress_members.name FROM votes
INNER JOIN congress_members
ON votes.politician_id = congress_members.id
WHERE name LIKE "%Snow%";

or

SELECT count(politician_id) FROM votes
INNER JOIN congress_members
ON votes.politician_id = congress_members.id
WHERE congress_members.name LIKE '%Olympia%';

Release 1, Problem 3:

SELECT count(politician_id) FROM votes
INNER JOIN congress_members
ON votes.politician_id = congress_members.id
WHERE congress_members.name LIKE '%Erik%';

Release 1, problem 4:

SELECT congress_members.name, count(*) FROM votes
INNER JOIN congress_members
WHERE congress_members.id = votes.politician_id
GROUP BY congress_members.name
ORDER BY count(*) DESC;

Release 1, problem 5:

SELECT congress_members.name, count(*) FROM votes
INNER JOIN congress_members
WHERE congress_members.id = votes.politician_id
GROUP BY congress_members.name
ORDER BY count(*) ASC;

Release 2, Problem 1:

SELECT * FROM voters
INNER JOIN votes
ON voters.id = votes.voter_id
WHERE votes.politician_id = 224;

Release 2, Problem 2:

SELECT name, location, grade_1996, grade_current, count(votes.politician_id) AS vote_count FROM congress_members
INNER JOIN votes
ON votes.politician_id = congress_members.id
WHERE grade_current < 9
GROUP BY name
ORDER BY count(votes.id) DESC;

Release 2, Problem 3:

SELECT location, count(voters.id) FROM votes
INNER JOIN congress_members
ON congress_members.id = politician_id
INNER JOIN voters
ON voter_id = voters.id
GROUP BY location
ORDER BY count(voters.id) DESC
LIMIT 10;

Release 2, Problem 4:

SELECT votes.voter_id, voters.first_name, voters.last_name FROM votes
JOIN voters ON votes.voter_id = voters.id
GROUP BY votes.voter_id
HAVING COUNT(votes.voter_id) > 2
ORDER BY voters.last_name;

Release 2, Problem 5:

SELECT votes.voter_id, voters.first_name, voters.last_name, congress_members.name, COUNT(votes.id)
FROM votes
JOIN voters ON votes.voter_id = voters.id
JOIN congress_members ON votes.politician_id = congress_members.id
GROUP BY congress_members.name, votes.voter_id
HAVING COUNT(votes.voter_id) > 2
ORDER BY voters.last_name;