- The Texas vote is close! Add 2 new voters, and fabricate a vote for each of the 2 incumbent senators of Texas. Make up their names and info.
INSERT INTO voters
VALUES(
5001, 'Jon', 'Rain', 'male', 'democrat', '3', 24, 0, 0, 0, 0, DATETIME('now'), DATETIME('now')
);
INSERT INTO voters
VALUES(
5002, 'Son', 'Rnow', 'male', 'democrat', '3', 24, 0, 0, 0, 0, DATETIME('now'), DATETIME('now')
);`
- Insert another politician, "Donald Trump". Add suitable attributes and delete one of the senators from New Jersey. Add Trump there instead. Give Trump all the votes from the deleted politician.
Get the id of politicians from New Jersey:
sqlite> SELECT id FROM congress_members
...> WHERE location = 'NJ';
DELETE
FROM congress_members
WHERE id = 102;
INSERT INTO
congress_members
VALUES(
102, 'Donald Trump', 'Republican', 'NJ', '1', '1', '0', '0', DATETIME('now'), DATETIME('now'));`
- Find all the voters that are not registered as republican or democrat (AND only voted once), and delete them.
sqlite> DELETE FROM
...> voters
...> WHERE party <> 'democrat' AND party <> 'republican';
- Delete all the voters (and their votes) that are homeowners, employed, have no children, and have been with their party for less than 3 years AND have voted for politicians that speak at a grade level higher than 12.
sqlite> DELETE *
...> FROM votes
...> WHERE votes.voter_id IN (
...> SELECT id FROM
...> voters
...> WHERE homeowner = 1 AND employed = 1 AND children_count = 0 AND party_duration < 3
...> LIMIT 10)
...> AND votes.politician_id IN (
...> SELECT id FROM congress_members
...> WHERE grade_current > 12
...> );
| id | voter_id | politician_id |
|---|---|---|
| 336 | 196 | 419 |
| 430 | 196 | 387 |
| 2195 | 196 | 427 |
| 2443 | 2038 | 118 |
| 3357 | 3004 | 447 |
| 8988 | 196 | 372 |
| 9145 | 4210 | 432 |
- Update the votes for all the men over 80 that have no children. Change their vote to be for the secret politician with ID 346.
Visualising(?) the data first
sqlite> SELECT first_name, last_name, gender, voter_id, politician_id
...> FROM votes
...> JOIN voters ON voters.id = votes.voter_id
...> WHERE gender = 'male' AND age > 80 AND children_count = 0
...> GROUP BY voter_id
...> ORDER BY politician_id;
| first_name | last_name | gender | voter_id | politician_id |
|---|---|---|---|---|
| Kaley | Ziemann | male | 1126 | 10 |
| Kale | Rodriguez | male | 2393 | 11 |
| Russell | Ullrich | male | 1637 | 14 |
| Idell | Hintz | male | 200 | 36 |
| Kiana | Hilll | male | 3995 | 64 |
| Oda | MacGyver | male | 460 | 73 |
| Nella | Rogahn | male | 4819 | 113 |
| Sandy | Wisozk | male | 4887 | 119 |
| Erick | Herzog | male | 1082 | 138 |
| Genoveva | Lehner | male | 2796 | 189 |
| Markus | O'Keefe | male | 3038 | 189 |
| Monique | Bechtelar | male | 2481 | 201 |
| Lourdes | Farrell | male | 4514 | 208 |
| Rhianna | Kuvalis | male | 2371 | 214 |
| Jarred | Smith | male | 3051 | 219 |
| Maynard | Weimann | male | 1249 | 220 |
| Jewel | Brakus | male | 453 | 238 |
| Bernardo | Mertz | male | 2232 | 258 |
| Sidney | Cummerata | male | 1147 | 276 |
| Julianne | Hauck | male | 3962 | 298 |
| Jalon | Keebler | male | 3801 | 312 |
| Otto | Kilback | male | 295 | 344 |
| Damion | Hegmann | male | 2189 | 345 |
| Marshall | Kozey | male | 4476 | 348 |
| Kristina | Shields | male | 2683 | 400 |
| Otto | Turner | male | 277 | 441 |
| Ervin | Robel | male | 3151 | 455 |
| Brody | Kris | male | 2556 | 515 |
Before fudging the votes
sqlite> SELECT voter_id
...> FROM votes
...> WHERE politician_id = 346;
| voter_id |
|---|
| 2479 |
| 2543 |
| 1000 |
| 2422 |
| 2604 |
| 3176 |
| 4046 |
| 91 |
| 2703 |
| 657 |
| 817 |
| 2737 |
| 654 |
| 372 |
| 26 |
| 1866 |
| 391 |
| 1856 |
| 2018 |
| 2378 |
| 2040 |
| 1292 |
| 1630 |
| 1499 |
| 2331 |
Fudging the votes
sqlite> UPDATE votes
...> SET politician_id = 346
...> WHERE voter_id IN (
...> SELECT voter_id
...> FROM votes
...> JOIN voters ON voters.id = votes.voter_id
...> WHERE gender = 'male' AND age > 80 AND children_count = 0
...> GROUP BY voter_id
...> );
After fudging the votes
sqlite> SELECT voter_id
...> FROM votes
...> WHERE politician_id = 346;
| voter_id |
|---|
| 200 |
| 2479 |
| 3051 |
| 2543 |
| 460 |
| 2683 |
| 2189 |
| 1637 |
| 3962 |
| 3995 |
| 295 |
| 1000 |
| 2393 |
| 4514 |
| 277 |
| 1147 |
| 2481 |
| 4819 |
| 2683 |
| 3995 |
| 2422 |
| 2796 |
| 3151 |
| 4476 |
| 1126 |
| 3038 |
| 2393 |
| 3051 |
| 2604 |
| 1082 |
| 2556 |
| 2481 |
| 460 |
| 3995 |
| 3176 |
| 4046 |
| 91 |
| 2703 |
| 3801 |
| 3995 |
| 2796 |
| 657 |
| 2232 |
| 4514 |
| 1147 |
| 2371 |
| 817 |
| 2481 |
| 2189 |
| 2737 |
| 295 |
| 4514 |
| 654 |
| 372 |
| 26 |
| 4887 |
| 2796 |
| 460 |
| 4476 |
| 1866 |
| 2556 |
| 391 |
| 3038 |
| 1856 |
| 2018 |
| 200 |
| 2378 |
| 2040 |
| 4476 |
| 1292 |
| 1630 |
| 453 |
| 1249 |
| 3995 |
| 2556 |
| 2796 |
| 1499 |
| 4819 |
| 1249 |
| 3151 |
| 2331 |
| 2189 |
- Update the votes for top smarty pants politician (based on their speaking level - grade_1996). Shift the votes instead to the congress person that speaks at the lowest grade level.
sqlite> SELECT id, MAX(grade_1996)
...> FROM congress_members;
| id | MAX(grade_1996) |
|---|---|
| 530 | 16.01390263 |
sqlite> SELECT id, MIN(grade_1996)
...> FROM congress_members;
| id | MIN(grade_1996) |
|---|---|
| 102 | 1.0 |
sqlite> UPDATE votes
...> SET politician_id = 102
...> WHERE politician_id = 530;