In this lab, you and an assigned partner will continue working with the election data from lab 1.
For each of the following, just turn in your SQL statements.
Create your own copies of the SOS's counties
and us_senate_results
tables using the following two commands:
create table counties as select * from SOS.counties; create table us_senate_results as select * from SOS.us_senate_results;
Now give the appropriate ALTER TABLE statements to add primary key and foreign key constraints such that every county_id
that appears in a row of us_senate_results
must also appear in a row of counties
. Beyond ensuring referential integrity, this may improve the efficiency of joining the two tables. (Think about why.)
Create a view called us_senate_results_by_county
that is based on the above two tables and provides the total votes for each candidate in each county. The heading of this view and a sample row follow:
COUNTY_NAME CANDIDATE_NAME VOTES ----------------- ---------------- ----- Aitkin AMY KLOBUCHAR 5628
As a further test that you've correctly created the view, the following query ought to show you the two counties in far southwestern Minnesota that Amy Klobuchar lost, as well as to whom she lost them:
select t2.county_name, t2.candidate_name from us_senate_results_by_county t1, us_senate_results_by_county t2 where t1.county_name = t2.county_name and t2.votes > t1.votes and t1.candidate_name = 'AMY KLOBUCHAR';
Create an index on your copy of the us_senate_results
table that could be used to speed up queries that refer to a specific candidate name, such as the following example:
select count(*) from us_senate_results where candidate_name = 'AMY KLOBUCHAR';