In this lab, you will use the Oracle database to try out basic SQL queries on tables I have downloaded from the Minnesota Secretary of State's election results. (In case that web site changes, I've also got local copies of the files I downloaded. The data tables came from CandTbl.txt, CntyTbl.txt, PartyTbl.txt, Prcttbl.txt, USPresPct.txt, mcdtbl.txt, sthousepct.txt, ushousepct.txt, and ussenatepct.txt; the format is described in FileFormat.txt.) I closely modeled my database tables on the files; the only exception is that in the results tables, I omitted two columns that were identified as "not used".
All the work for this lab can be most easily done on one of the MCS lab PCs running Linux, since we have the Oracle SQL Developer client software installed on those machines. However, you could also download and install this software on your own machine and still connect to our server, thebe.gac.edu
You can run SQL Developer from the Programming submenu of the main Fedora menu. (I'll demonstrate this in class.) In the following instructions, I'll pretend your username is jstudent; substitute your real one. You should create a connection called jstudent-thebe, specify the username jstudent and the password I give you, and set the hostname to thebe.gac.edu.
Because the password I issue you isn't very secure, you should probably start by changing your password, which you can do with an SQL command like
alter user jstudent identified by myNewSecretPassword;
To find the tables you will be using in SQL Developer, in the lefthand pane expand out Other Users, under it SOS, and under that Tables. The tables are named CANDIDATES, COUNTIES, MCDS, PARTIES, PRECINCTS, STATE_HOUSE_RESULTS, US_HOUSE_RESULTS, US_PRESIDENT_RESULTS, and US_SENATE_RESULTS. In your SQL queries, you can refer to these with names like SOS.CANDIDATES, which tell Oracle to look for the table in the SOS schema. (When we discuss granting of privileges, you will see how I granted you all permission to select data from these tables but not modify them.) You can also type the names in lower case. I am attaching the CREATE TABLE statements for your reference.
For this lab, you need not write a true lab report. Instead, just turn in your SQL queries and the results they produced. For each one, give my English statement of the problem, your SQL query, and the resulting table, in that order. If you save out SQL Developer's SQL Worksheet and Script Output tabs, you'll need to interleave these two files in your editing process. If in any case you have reason to doubt that the result is correct, but haven't been able to track down what is wrong with your SQL query or fix it, you should indicate what is wrong with the result.
Be sure to answer each question using a single SQL query that directly produces the desired result. It is not acceptable to issue one query, manually extract some information from the result, and use that to type in a second query, which produces the desired result.
The desired result should also be immediately apparent; what follows are two examples of how you could fail to meet this expectation. Example 1: if I ask you how many of something exists, your output should be a single row showing the number, not a bunch of rows, where the number of rows is the answer. Example 2: if I ask you what is the largest value of something, your output should just be that largest value, not an ordered list from which the largest value can be read off.
Your queries should not produce results with duplicate rows.
Try not to make your queries unnecessarily complex. In particular, don't mention a table if it isn't needed to find the result.
Find the names of candidates who ran for an office ending with '23A'.
Provide an alphabetical list of the parties' full names.
Find the candidates who ran for the office of 'US SENATOR'. For each, list the candidate's and party's full names.
Find a list of candidates' names for those candidates who received a majority of the votes in at least one precinct in 'NICOLLET' county. You should take into account the results for State House, US House, US President, and US Senate.
In which precinct (or precincts) did 'TERRY MORROW' receive his largest vote count? Give your answer as a precinct name (or names, if there is a tie).
In what cities did 'TERRY MORROW' have election results? A city is a Minor Civil Division (MCD) with a name that starts with 'City of'.
The MCD numbers are supposed to be unique statewide. It is worth checking whether this is the case. Find all cases where the same MCD number is associated with more than one MCD name. In each case, list the MCD number and the two MCD names. Don't list each pair in two different orders. For context, also include the two county names.
In how many precincts did 'TERRY MORROW' have results?
In how many precincts did 'TERRY MORROW' receive a majority of the votes?
How many precincts are there, totaled over all counties?
If you concatenate together the county and precinct numbers for each US Senate result, how many combinations are there, counting each one only once?
Find any rows of the US Senate results that contain a county number not present in the table of counties.
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.
Produce a second 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 again be in descending order of votes. This time, exclude any results that don't have a corresponding entry in the table of counties.
What percentage of the votes for his office did 'TERRY MORROW' receive in each county where he had results? List the county names with their percentages (which should be labeled as such). Hint 1: the vote percentages given in the results table are not very helpful for this. Hint 2: The TOTAL_VOTES column in the result table is the total over all candidates for the same office in the same precinct.