-- Find the names of candidates who ran for an office ending in '19A'. + ------------------- + | candidate_name | + ------------------- + | KIM SPEARS | | CLARK JOHNSON | + ------------------- + 2 rows -- Provide an alphabetical list of the parties' full names. + --------------- + | party_name | + --------------- + | Constitution Party | | Democratic-Farmer-Labor | | Grassroots - Legalize Cannabis | | Green Party | | Independence | | Legal Marijuana Now | | Libertarian Party | | Nonpartisan | | Republican | | Write-In | + --------------- + 10 rows -- Find the candidates who ran for the office of 'U.S. Senator'. For each, list the candidate's and party's full names. -- Hint: join tables using the party abbreviation, not the party ID. Later you will see why. + ------------------- + --------------- + | candidate_name | party_name | + ------------------- + --------------- + | STEVE CARLSON | Independence | | MIKE MCFADDEN | Republican | | AL FRANKEN | Democratic-Farmer-Labor | | HEATHER JOHNSON | Libertarian Party | + ------------------- + --------------- + 4 rows -- Find a list of candidates' names for those candidates for US office who received a majority of the votes in at least one -- precinct in 'Nicollet' county. + ------------------- + | candidate_name | + ------------------- + | TIM WALZ | | JIM HAGEDORN | | MIKE MCFADDEN | | AL FRANKEN | + ------------------- + 4 rows -- In which precinct (or precincts) did 'CLARK JOHNSON' receive his largest state House vote count? -- Give your answer as a precinct name (or names, if there is a tie). + ------------------ + | precinct_name | + ------------------ + | ST PETER W-1 P-2 | + ------------------ + 1 rows -- In which counties did 'CLARK JOHNSON' have state House election results? Give your result as an alphabetic list of county names. + ---------------- + | county_name | + ---------------- + | Blue Earth | | Le Sueur | | Nicollet | + ---------------- + 3 rows -- In how many precincts did 'TIM WALZ' have US House results? + ----------- + | answer | + ----------- + | 696 | + ----------- + 1 rows -- In how many precincts did 'TIM WALZ' win a majority of the US House votes? + ----------- + | answer | + ----------- + | 398 | + ----------- + 1 rows -- Find any rows of the candidates table that contain a party ID number not present in the table of parties. -- For each, show the candidate's name and party ID number. + ------------------- + ------------- + | candidate_name | party_id | + ------------------- + ------------- + | HEATHER JOHNSON | 11 | | TIM UTZ | 10 | | CHRIS HOLBROOK AND CHRIS DOCK | 11 | | BOB ODDEN | 11 | | KEEGAN IVERSEN | 11 | | MARY O'CONNOR | 11 | | DAN R. VACEK | 100 | + ------------------- + ------------- + 7 rows -- Find any candidates with a party ID that does not match the one shown in the parties table for the candidate's party abbreviation. -- For each, show the candidate's name, candidate ID, party ID number, party abbreviation, and the party ID number that is listed in -- the parties table as corresponding to the abbreviation. + ------------------- + ----------------- + ------------- + ----------------------- + ------------- + | candidate_name | candidate_id | party_id | party_abbreviation | party_id | + ------------------- + ----------------- + ------------- + ----------------------- + ------------- + | HEATHER JOHNSON | 01020601 | 11 | LIB | 06 | | RAY SKIP SANDMAN | 01110501 | 07 | GP | 05 | | KRISTINE OSBAKKEN | 02000501 | 07 | GP | 05 | | TIM UTZ | 02690801 | 10 | CP | 08 | | LENA BUGGS | 03160501 | 07 | GP | 05 | | CHRIS HOLBROOK AND CHRIS DOCK | 03310601 | 11 | LIB | 06 | | BOB ODDEN | 03320601 | 11 | LIB | 06 | | KEEGAN IVERSEN | 03330601 | 11 | LIB | 06 | | ANDY DAWKINS | 03350501 | 07 | GP | 05 | | MARY O'CONNOR | 03350601 | 11 | LIB | 06 | | DAN R. VACEK | 03350701 | 100 | LMN | 07 | + ------------------- + ----------------- + ------------- + ----------------------- + ------------- + 11 rows -- Sometimes it happens that two distinct municipalities (identified by different MCD codes) -- happen to have the same name. List the cases where this occurs, for each providing the -- municipality name, the two MCD codes, and the two county names. List each pair only once, -- not also in reversed order. The list should be in alphabetical order by municipality name. + ---------------------- + ------------- + ------------- + ---------------- + ---------------- + | municipality_name | mcd_code | mcd_code | county_name | county_name | + ---------------------- + ------------- + ------------- + ---------------- + ---------------- + | Beaver Twp | 04384 | 04420 | Aitkin | Roseau | | Becker Twp | 04600 | 04636 | Cass | Sherburne | | Clover Twp | 12232 | 12250 | Hubbard | Mahnomen | | Cornish Twp | 13330 | 13348 | Aitkin | Sibley | | Deerwood Twp | 15364 | 15382 | Crow Wing | Kittson | | Fairview Twp | 20366 | 20384 | Cass | Lyon | | Hillman Twp | 29132 | 29168 | Kanabec | Morrison | | Hubbard Twp | 30338 | 30356 | Hubbard | Polk | | Lake George Twp | 34388 | 34406 | Hubbard | Stearns | | Lawrence Twp | 35882 | 35900 | Grant | Itasca | | Lincoln Twp | 37124 | 37142 | Blue Earth | Marshall | | Long Lake Twp | 37988 | 38024 | Crow Wing | Watonwan | | Louisville Twp | 38276 | 38294 | Red Lake | Scott | | Moose Lake Twp | 43972 | 43990 | Carlton | Cass | | Nordland Twp | 46546 | 46564 | Aitkin | Lyon | | Pelican Twp | 50092 | 50110 | Crow Wing | Otter Tail | | Pine Lake Twp | 51172 | 51226 | Cass | Pine | | Roseville Twp | 55816 | 55834 | Grant | Kandiyohi | | Stokes Twp | 62914 | 62932 | Itasca | Roseau | | Twin Lakes Twp | 65902 | 65929 | Carlton | Mahnomen | + ---------------------- + ------------- + ------------- + ---------------- + ---------------- + 20 rows -- How many counties are there for which the number of precincts shown in the counties table doesn't -- match how many are in the precincts table? + ----------- + | answer | + ----------- + | 0 | + ----------- + 1 rows -- Produce a table of state-wide votes for the US Senate race, showing in each row the candidate's name -- and the corresponding number of votes. The table should be in descending order of votes. + ------------------- + ---------- + | candidate_name | total | + ------------------- + ---------- + | AL FRANKEN | 1053205 | | MIKE MCFADDEN | 850227 | | STEVE CARLSON | 47530 | | HEATHER JOHNSON | 29685 | | WRITE-IN** | 881 | + ------------------- + ---------- + 5 rows -- What percentage of the votes for Governor in each county were for the DFL candidate? -- List the county names with their percentages (which should be labeled as such). -- Give the list in ascending order of percentage. -- Hint: The percentage column in the governor_results table is not helpful for this. + ---------------- + --------------- + | county_name | percentage | + ---------------- + --------------- + | Pipestone | 34.2495 | | Sherburne | 35.5052 | | Wright | 35.6525 | | Roseau | 35.8206 | | Carver | 36.2026 | | Rock | 36.2911 | | Martin | 36.4777 | | Mcleod | 36.7138 | | Wadena | 36.8111 | | Redwood | 36.8920 | | Sibley | 37.4562 | | Dodge | 38.0029 | | Scott | 38.0224 | | Otter Tail | 38.4309 | | Morrison | 38.8439 | | Douglas | 39.4296 | | Becker | 39.5625 | | Isanti | 39.5710 | | Lake Of The Woods | 40.0531 | | Brown | 40.1137 | | Benton | 40.1193 | | Clearwater | 40.3539 | | Todd | 40.4226 | | Meeker | 40.4707 | | Jackson | 40.7609 | | Lyon | 41.3925 | | Chisago | 41.5263 | | Stearns | 41.6371 | | Cass | 41.7198 | | Steele | 41.8228 | | Crow Wing | 42.1112 | | Hubbard | 42.1125 | | Faribault | 42.4192 | | Cottonwood | 42.5140 | | Murray | 42.6915 | | Wilkin | 42.7336 | | Mille Lacs | 43.2744 | | Wabasha | 43.4809 | | Goodhue | 43.5660 | | Nobles | 43.5991 | | Kanabec | 43.6472 | | Waseca | 44.0885 | | Le Sueur | 44.1592 | | Anoka | 44.2373 | | Fillmore | 44.7203 | | Renville | 44.8312 | | Lincoln | 45.1293 | | Olmsted | 45.1731 | | Kandiyohi | 45.2404 | | Polk | 45.9124 | | Pennington | 46.3141 | | Washington | 46.4726 | | Yellow Medicine | 46.8679 | | Houston | 47.0649 | | Dakota | 47.3599 | | Marshall | 47.4140 | | Pope | 47.7167 | | Pine | 47.7658 | | Watonwan | 47.7919 | | Stevens | 47.8744 | | Aitkin | 48.8523 | | Chippewa | 49.0761 | | Winona | 49.4505 | | Red Lake | 49.7253 | | Freeborn | 50.1009 | | Blue Earth | 50.3989 | | Grant | 50.4213 | | Big Stone | 50.4261 | | Lac Qui Parle | 50.7889 | | Beltrami | 50.8818 | | Mower | 50.9287 | | Nicollet | 51.3509 | | Rice | 51.9916 | | Swift | 53.3122 | | Traverse | 53.3202 | | Clay | 53.7326 | | Kittson | 54.6392 | | Itasca | 55.0116 | | Mahnomen | 55.6805 | | Koochiching | 57.6821 | | Carlton | 59.1307 | | Lake | 59.1566 | | Hennepin | 59.2275 | | Norman | 59.4921 | | Cook | 61.0843 | | St. Louis | 62.0875 | | Ramsey | 62.8231 | + ---------------- + --------------- + 87 rows -- Although each MCD code in the municipalities table identifies a unique municipality, the same code -- can appear in more than one row if a municipality spans multiple counties. Find all the cases where -- this appears, listing for each the MCD code, municipality name and number of counties. The list should -- be organized primarily in decreasing order of number of counties and for any given number of counties -- in alphabetical order by municipality name. + ------------- + ---------------------- + ------------- + | mcd_code | municipality_name | counties | + ------------- + ---------------------- + ------------- + | 39878 | Mankato | 3 | | 56896 | St. Cloud | 3 | | 04798 | Bellechester | 2 | | 06382 | Blaine | 2 | | 07282 | Braham | 2 | | 08092 | Brooten | 2 | | 09154 | Byron | 2 | | 10918 | Chanhassen | 2 | | 11008 | Chatfield | 2 | | 11800 | Clearwater | 2 | | 12772 | Comfrey | 2 | | 15022 | Dayton | 2 | | 15706 | Dennison | 2 | | 18134 | Eden Valley | 2 | | 19160 | Elysian | 2 | | 25280 | Granite Falls | 2 | | 26990 | Hanover | 2 | | 27530 | Hastings | 2 | | 31760 | Jasper | 2 | | 33866 | La Crescent | 2 | | 34172 | Lake City | 2 | | 43036 | Minneiska | 2 | | 43198 | Minnesota Lake | 2 | | 44422 | Motley | 2 | | 45808 | New Prague | 2 | | 47068 | North Mankato | 2 | | 46924 | Northfield | 2 | | 48562 | Ormsby | 2 | | 48796 | Osakis | 2 | | 51136 | Pine Island | 2 | | 52522 | Princeton | 2 | | 53656 | Redwood Falls | 2 | | 55006 | Rockford | 2 | | 55438 | Roosevelt | 2 | | 56014 | Rothsay | 2 | | 56176 | Royalton | 2 | | 58612 | Sartell | 2 | | 61996 | Spring Lake Park | 2 | | 56950 | St. Francis | 2 | | 62446 | Staples | 2 | | 63778 | Swanville | 2 | | 67504 | Wadena | 2 | + ------------- + ---------------------- + ------------- + 42 rows